Powerful and complex, Microsoft Excel comes packed with so many tools that it’s often hard to know which tool can solve a particular problem. Ever feel like it’s easier to just keep doing things the slow way simply because it works? But you deserve better than that, so we’ve gathered five essential Excel tools that save you time and effort. If you’re not currently using them, it’s time to up your game.
- Five Excel tools you should use
Five Excel tools you should use
1. Flash fill
Excel’s Flash Fill feature makes it easy to fill data in an Excel worksheet based on adjacent data patterns. Flash Fill looks at the surrounding data, and then it fills a range based on what it finds. You might, for example, use Flash Fill to separate names (or to join them together) when you have a column or columns of name data in the wrong format.
When you need to arrange existing data differently, Flash Fill can do in seconds what might otherwise take hours of manual work.
How to use Flash Fill: Create one or more empty columns alongside the existing data. Start typing the data that you want to see in the column. As you proceed, Excel looks for a pattern and, when it sees it, it enters the remaining data automatically for you. If Excel gets it right, all is good.
If it doesn’t, then continue; edit the entries it didn’t get right, and Excel will adjust the filled data according to the new pattern. You can also access Flash Fill from the Data tab on the Ribbon. Flash Fill can save you hours of manual work and avoid the need to write custom macro solutions.
2. Filter and calculate with tables
When you’re working with lists of data in Excel, the Format-as-Table option (in the Styles area of the Home tab) does a lot more than simply format your data. When you click in a list of data and apply a table format to the data using this tool, Excel also adds a filter to each column.
The Format-as-Table option does much more than apply simple formatting to list data.
You can use this to sort and filter the data. From the Table Tools > Design Tab you can set special formatting for columns and rows within the table, and then click Total Row to add a total row. When you then click in a cell in the total row, a dropdown list appears from which you can select a function to apply to that column of data, such as sum, average, count and so on.
Excel creates these calculations using a subtotal function, which means they show results for visible data only. And when you create a chart based on the table data, that chart is dynamic and will expand to include any data that you add to the table in future.
3. Dropdown lists
When you’re typing data into a list—and if one or more cells above contain that same data—you can enter it using a dropdown list. Simply click in the cell and press Alt + Down Arrow and a list of contents from the cells above the current cell will appear. Click the entry you want to use and press Enter. This speeds up the data-entering process, but it also ensures data entry consistency.
You can use existing data to speed up data entry or to create custom dropdown lists.
You can also create a custom list by entering the list on a second worksheet. Select the range and name it using the Name box at the left of the formula bar. Now select the column to which you want the list added, choose Data > Data Validation, set it to List, and select the named range as the Source. In the future all the selected cells will contain a dropdown list from which you can select the desired cell entry.
4. Isolate variable data in a data area
When your worksheet uses data—such as tax rates—that may or may not change over time, it’s best to place it in a separate area on the worksheet. You can then refer to those cells in your formulas.
Contain variable data—such as tax rates—in a separate area so that you can easily verify and update values should they change.
For example: place the state tax rate in a cell above your worksheet data and label it clearly. Then refer to that cell in your calculations instead of placing the value inside the calculation. Using a data area ensures that anyone can determine the tax rate by referring to the labeled cell. If tax rates change, you then need only change one cell and not all the calculations in the worksheet. People will understand your worksheets more easily, and that they can be quickly audited for accuracy.
5. Exception reporting with conditional formatting
Most managers who review data in worksheets look for limited amounts of data that does not conform to expectations. When reviewing a debtor list, for example, you’ll be more interested in seeing out-of-the ordinary and excessively high results than values that fall within normal ranges.
Conditional formatting is a handy way to draw attention to exceptional values that require further attention.
You can draw attention to this type of data using conditional formatting. First determine what exceptions you want to highlight, and then write a rule or rules to isolate this data in your workbook. Conditional formatting is dynamic; if the data changes, the formatting will update automatically. In the example above, we created a conditional format to highlight outstanding amounts that are more than twice the average.
Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her website, HelenBradley.com
This article was originally published on September 11, 2015. It was updated by Tamara Scott.
Read next: Best Excel Add Ons & Tools You Need in 2021