For episode 4 of our From Excel Zero to Excel Hero series! Lynsey reminds us of key tips from previous episodes and gives us the insider knowledge on quick-fixes to solve the most common Excel issues!
Over the last weeks we have been exploring formulas and functions. During our Microsoft Excel courses I talk about creating a troubleshooting list. If your function or formula is returning an error or the incorrect answer what are the steps you can take to resolve it? Don’t just delete it and start again, it may be a simple mistake. Here are my troubleshooting tips.
Correct cells selected
If your function or formula is not returning the correct result, check to ensure you have selected the correct cells. There are a few ways Microsoft Excel can help you do this.
Double Click in Excel
If your calculation refers to cells that are on the same worksheet, double-click on the cell containing the calculation. All references used in the calculation will be highlighted in different colours. If you have selected the wrong cell you can move the coloured cell. In the example below the formula should be =B2+C2 but =A2+B2 has been entered accidentally resulting in a #VALUE error, as you cannot add up text! Double click on cell D2. Notice that cell A2 is blue, as is the reference A2 in the formula, B2 is red. Drag the blue border from A2 to C2 and press Enter or click the tick on the Formula Bar. The formula now shows the correct result (see below).
Microsoft Excel provides a handy button to trace the precedents of your calculation. Precedents are the cell references used in your calculation. Find it on the Formulas tab in the Formula Auditing group or come along to one of our Excel Advanced courses to find out more.
Dollars in Excel
Omission of dollars is one of the most common reasons why calculations go wrong. Remember you only need to add dollars to a reference if you are copying the calculation and you don’t want the reference to change. Discover more in this article.
Syntax in Excel
I always recommend using the Function Argument dialog box to write your functions. Most syntax is done for you, so you don’t need to worry about it, but you will need to write formulas yourself so explore this article to learn when to use a comma, semicolon, exclamation mark etc.
BODMAS in Excel
The order in which a function or formula is calculated is determined by BODMAS (Brackets, Orders, Divide, Multiply, Add and Subtract. Read this article to understand how this affects how you write your calculations.
Excel also provides a tool to evaluate your formula or function in order. On the Formulas tab in the Formula Auditing group find Evaluate Formula. The Evaluate Formula dialog box will step through the calculation in order each time you click Evaluate
Dodgy Data in Excel
Sometimes the problem lies with the data and not the formula or function. This is a common issue in vlookups. For example, look up a code from one workbook to return information about the code stored in another workbook. The code must appear in both workbooks and it must be exactly the same. A code followed by a space is not the same. If you think there may be spaces or other hidden data in your workbook then copy and paste some cells into Microsoft Word. Switch on the Show/Hide button in the Paragraph group on the Home tab. It will show you spaces, tabs, returns etc. The example below is data copied from Excel to Word and shown in a Word table. The dots at the end of each code are spaces.
You can use Flash Fill or functions such as Trim and Clean to get rid of unnecessary spaces or other hidden data. If you haven’t heard about Flash Fill, find out more on one of our Excel courses.
By default, Microsoft Excel only recalculates formulas and functions when a change is made to the precedents of those calculations however it is possible to change the calculation mode.
If the workbook contains a lot of calculations it can slow down operations like opening and saving. If this is a significant problem, you can change the calculation method to Manual. This means calculations wont update until you force them to. You can make this change on the Formulas tab, Calculation group, Calculation Options.
Beware Manual Calculation
If you change the calculation mode to manual in one workbook it will affect all other open workbooks. Excel can only use one calculation mode. Imagine this scenario, open the manual calculation workbook then open another workbook. The second one will switch to manual mode. Its not that common to need manual calculation any more but if your formula seems to be correctly written but is returning the wrong answer have a quick check and make sure the calculation mode is Automatic.
From Excel Zero to Excel Hero – EXCEL courses in Aberdeen
Solab have been offering businesses IT Application Training since 1994. We consistently receive positive feedback and excellent evaluation from clients and individual delegates. We’re flexible, too – not only in the diversity of our course programme, but in shaping a training solution that precisely matches you and your business requirements. We can host your training at our training school in Aberdeen city centre, at your premises or in the cloud (for course delivery methods and options, please visit: https://www.solab.co.uk/training/delivery-methods/).