Struggle building Excel formulae quickly and efficiently? Our 10 Excel Formula-Building Tips will get you building Excel formulae like a pro. Let’s get started!
Double-clicking on cells in order to enter a formula? There’s actually no need to do this. Simply navigate to the cell using the arrows keys on the keyboard, then hit “=”. Voila! We’re in formula-editing mode.
2. Tab key to complete formula
Tired of misspelling Excel formulae and the inevitable #NAME? error? That’s Excel saying ‘I don’t recognise this formula!’ Eliminate these errors by using the tab key on the Windows PC to autopopulate the formula name. Oh, Excel even puts in the first bracket ((), too, saving more time.
3. Use the escape key to exit formula-building mode
Got in a mess building a formula? It happens! And it’s even worse if you can’t quickly extricate yourself from a formula tangle. Just hit the ‘Esc’ key on the Windows PC to get out of formula-editing mode. Then dust yourself off and try again …
4. Use F2 to switch between formula edit and cell navigation
It took me years to work this one out. You might know that the arrow keys can be used when building a formula to navigate to the cell(s) you want to reference. But, how would you move the cursor up and down the formula to allow you to tweak it? The F2 key toggles between cell navigation and 'edit in the formula editor' modes.
5. Understand the colours help!
Excel actually supplies an impressive range of tools to support formula building. It even colour-codes the components of the formula and cells on the worksheet to underline the relationship between them. Double-click in the formula (or navigate to the cell and hit ‘F2’) to make the colours appear.
6. Use comma trick when building formulae across sheets
Formula-building across sheets is a key skill in Excel modelling, but it’s complicated. So complicated, in fact, that some people avoid it all together, leading to workbook structures that you might describe as ‘sub-optimal’, with everything crammed onto one sheet. My first tip for formula building across sheets: be intentional about entering the commas, noting which component of the formula is highlighted bold which shows what Excel is expecting next. Take your time!
7. Use two windows when building formulae across sheets
You’ll love this tip if you’re struggling to build formulae across sheets. Did you know you can see multiple views of the same file at the same time? Head over to the View tab and hit ‘New Window’. Yes, you now have two views of the same file, and can use BOTH windows when formula-building. Download the download file and give it a try yourself …
Really struggling to build formulae across sheets? Ok! Let’s find a workaround. A named range avoids the need to click across sheets at all. Locate the range you want to work with and create a name for it in the Name Manager. Note how it appears in the formula editor … just when you need it most.
9. Use absolute references
Understanding absolute references (more commonly know as ‘the dollar signs’ $$) is a milestone in Excel learning. They are fundamental to formula building, so make sure you are confident with them. Oh, you’re not telling me you’re typing them in manually? Use the F4 key!
10. Use partial absolute references
Ok, so you thought you had references all worked out? So, what does a single dollar sign ($) in a formula reference mean? This is called a ‘partial absolute reference’ and gives us even more options for flexible formula building in Excel. It can be particularly useful when populating tables with Excel formulae. Check out the example in the video for an illustration.
11. Fragment long formulae across cells
“Fragment long formulae across cells” might be my epitaph. Yes, getting a long formula to work can bring a sense of accomplishment. And, it will build your confidence. Ultimately, however, it’s better to use multiple formulae and columns rather than a single long formula. What, you can’t insert columns in the file? Did you not plan the layout beforehand …? :-)
