Save time with Microsoft Excel!
In part 1 of this blog, we looked at the first three tips for making you more efficient:
- Get to know the shortcuts
- Name your ranges
- Get to know your options
If you missed the article, you can find it here – Top 10 Microsoft Excel Tips (Part 1)
In this part, we look specifically at tools to make your use of formulas more effective…
Tip 4 – Use the Insert Function button to get help with functions
If you’re using Excel 2007 or later, Excel provides a certain amount of help with functions on screen as you write them. For example, when writing the VLOOKUP function, Excel will prompt you as to the meanings of the various parameters, and provide a drop-down list with the values (and meanings) available for some of these parameters.
But there’s more help available, and this help is available in all versions of Microsoft Excel. By clicking the Insert Function button (this used to be called the “Paste Function” button in earlier versions) you can select your function from a list, helping you to discover new functions, and Excel will explain the value of each of the function parameters.
Tip 5: Use the Trace Precedents / Trace Dependents buttons
This is a really useful tip for anyone who deals with spreadsheets created by their colleagues. You want to see how a particular value is arrived at, but the function refers to a whole host of other cells. It can be hard to keep track of which cells are which.
Conversely, you may be wanting to change a value in a particular cell, but you’re not sure how this will affect other values within the workbook.
There is a pair of buttons designed just for this: Trace precedents and trace dependents. These buttons are easy to find on Excel 2007 / 2010 – they are on the Formulas tab on the ribbon. But they also exist in the earlier versions of Excel – if you know where to look! In Excel 2003 and earlier, click on the View menu, then select Toolbars, and from the list, select the Formula Auditing toolbar. The trace precedents / dependents buttons are buttons 2 to 5 on this toolbar.
Once you know where the tools are, they’re pretty straightforward to use.
Select a cell containing a calculation, and the trace precedents button will draw a series of arrows to show you which cells are directly referred to in that calculation. It may be, of course, that some of these cells are themselves calculations – so clicking the button again will draw more arrows to how which cells these precedents themselves depend on… and so on.
Selecting a cell and clicking the Trace Dependents button will show you which cells would change if your currently selected value were to change. Clicking it again will show which further cells would change if those values changed. Clicking the Remove Arrows button does just what you’d expect.
Tip 6: Use the Evaluate Formulas button
This tip is for a tool which does a similar job to the trace precedents tool mentioned above – and it’s found in the same place.
Also on the Formulas tab on the ribbon (Excel 2007 / 2010) or on the Formula Auditing toolbar (earlier versions) is the Evaluate Formula button. Once again, this is designed to show you how a given cell gets its value.
However, rather than simply showing you which cells contain values referred to by your selected cell, the Evaluate Formulas button actually walks you through the steps required to get to your answer.
The underlined part of the formula will be the next element to be calculated. In the example here, that’s the average of the range from D13 to G13. Clicking the Evaluate button will show the result of this calculation, and the next part to be calculated will be underlined, and so on until the final answer is calculated.
Making more of Microsoft Excel…
Formulas in Excel can be incredibly powerful, and deliver real insights into your data. But picking your way through them, working out how parts of a spreadsheet are inter-related, can be tough – especially when the formulas are not yours. But getting to know the tools described above can really make your life easier.
In the last part of our Top 10 Microsoft Excel tips, we’ll look at some handy hints for the other side of Excel – list and data management.