originnally posted from my blog with SteemPress : http://theexcelclub.com/3-excel-hacks-you-need-to-know/
There are so many Excel Hacks, picking 3 for this article was hard. Over the last 5 years, Excel has been developing at a rapid pace. New releases have seen some lovely goodies for everyone. Microsoft has added new features and functions. While they have also improved on some others. If you are a beginner or advanced Excel user, I’m sure you have been as impressed as me with the changes.
In this article, we are going to look at 3 Excel hacks that can improve your efficiency and you should know. Introduced over the last few years some of these you may have noticed and some of them you may not. I have tried to pick something for each level of Excel experience. I do hope that you enjoy (and don’t forget to share!)
Tell me what you want to do - Excel natural language search
For those of us fortunate enough to be using Excel 365 you may have noticed a new field up beside the ribbon names. This field is Tell me what you want to do. This is a natural language search that will allow you to search for help on any excel topic. It will also allow you to quickly access commands just by entering words or phrases. Natural Language search is the first of three Excel hacks.
F1 Help in Excel
As an Excel user, I am sure you are familiar with F1 to bring up help. Help is a very useful feature to new Excel users and can also be of benefit to more experienced users. When you bring up help, a new pane will open with a predefined list of common help topics.
Clicking on any of the help topics will expand it to a list of articles and videos where you can get further help. You can also use the search bar to type in what you need help with.
Natural Language Search in Excel
Excel natural language search is like help on steroids. Not only can you get help, but you can also access commands fast, and use smart lookup. To use natural language search, type into the box whatever it is that you are looking for.
If you have a business subscription, typing a colleague’s name into the natural language search will return contact details for that person.
If you are using Onedrive or Sharepoint, entering the name of aa file to the search will allow you to open the file. Or you can even enter it into the current workbook.
Typing in a command, such as Save, will search through Excel and return the best match. We then use the arrow keys to select the command we want to perform.
Selecting Help will bring up the help feature that we are already familiar with.
Selecting Smart Lookup will use Bing. It will search the internet for more information and explanations on your search topic
How to quickly deselect extraneous cells
Another amazing new feature rolled out is the ability to deselect extraneous cells, and, the ability to multi-select cells or ranges. These additions were much-needed abilities that have been lacking for so long. Your life is about to get easier. Deselecting cells is our second of three excel hacks.
How to select a cell or range of cells
Selecting a cell is easy. Just click on the cell that you want. For example, to start in cell A1, click on it. The keyboard shortcut CTRL+ Shift Down arrow will highlight all the consecutive cells down column A that contains content.
If we then press CTRL + Right arrow we will also select all the to the right of the currently selected cells.
How to Deselect Cells
Before this new feature, you were unable to deselect individual cells. Now, when you have selected cells, by pressing CTRL and right-click on the mouse, you can deselect any cells you want.
This right-click trick not only deselects cells. It also allows you to select multiple ranges of cells. You can even select overlapping cells and ranges.
To do this, select the first range. Then, press and hold control while you right-click and drag the mouse around the second range. Release the right click before you move on to the third range.
If you need to select overlapping ranges, the trick is to first start in a cell not already selected. Otherwise, you will deselect the currently selected cell.
How to unpivot data in Excel
Pivot tables have been around for a long time. Often used by accountants, pivot tables summarise data in a layout that is easy to read. Yet a pivoted format is not always the best. Especially if you need to do further analysis. So, it is often necessary to hold your data in tabular format and sometimes unpivot, pivoted data.
Data presented in columns with source values placed in rows is known as pivoted data. It is common for accountants to use pivoted data for reports and analysis. Looking at this table, we have pivoted the monthly sales against the products. We refer to the column name, which in the image is the Month, as the attribute.
Tabular data contains the attributes in rows. It is often more difficult to read tabular data over pivot data. So, it would be common to pivot tabular data making it is easier to understand, read and summarize. We can do this, in Excel, with pivot and power pivot tables.
When analyzing data within Pivot tables and further modeling with DAX, the tabular format has its benefits. As it confines the values to rows, aggregation and other calculations happen with ease. We can then use these calculations and analyze against the attributes.
To unpivot data, using Get and transform from the data ribbon select From Table. This opens the power query editor. Power Query is an amazing Excel Tool and part of the Power tools suite.
Next, select the columns you wish to unpivot. To do this we can select the first column we want to unpivot, hold down the Shift key, and then click the last column.
From the transform ribbon select Unpivot columns.
Now the data unpivoted, we need to get it back into our workbook. To do so, we use the Close & Load command found on the Home tab. Excel then loads the unpivoted data back into our workbook.
Wrapping Up
There is so much going on with Excel is was hard to pick only 3 Excel hacks to showcase. I selected 3 things I thought would suit all skill levels. I hope what I covered, you can use, and you enjoyed reading about. As I mentioned earlier, there are new goodies for everyone. There is stuff for the newbie starting out in Excel and there is stuff for the power user. There is always something new to learn when it comes to Excel.
In the comments below, tell me, which of these hacks was most useful to you and why? Do you have an Excel hack to share? Drop it in the comments below and you could earn some steem rewards.
Enjoyed these Excel Hacks? Before you leave, it's only fair that you share
Want more awesome Excel stuff?
- New to Excel? Check out our Ultimate beginner Excel Guide here.
- New to Excel Power Tools? Check out these amazing Power Pivot, Power Query and Power view demonstrations
- New to DAX for Power Pivot and Power BI? Let us help you get started
Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox with Earn and Learn activities.SIGN UP FOR NEWSLETTER NOW
Now there is value in Learning with The Excel Club and our Learn and Earn STEEM activities.
We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn.
To Find out more now and start earning while you are learning Excel and Power BI
originnally posted from my blog with SteemPress : http://theexcelclub.com/3-excel-hacks-you-need-to-know/