Unlock 14 expert tips for Excel formulas

Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals brought to you by CPA Australia.

Neale Blackwood:
Welcome to the Excel Tips podcast. My name is Neale Blackwood and in this episode we are going to look at some formula-tips and tricks. Now, let's get some terminology sorted out. So formulas and functions are often used interchangeably, but to be honest, formulas are sort of the overarching description and functions, which are things like SUM and VLOOKUP. They're a subset of formulas because you can have formulas that don't have any functions in them. In general, formulas are the description you want to use when you're describing things in Excel, and a function is just a subset of those formulas.

The Formula Bar:
Okay, the formula bar, so this is the place where you should be editing and creating your formulas that sits above the grid, so above the column letters is the formula bar. Now you can do your editing and creating in a cell, but to be honest, it's much easier and there's a lot more room to do it in the formula bar. So that's what I recommend you do.

Starting formulas:
Now, starting formulas, most times you'll be told you have to start a formula with the equal sign [=], which is true, but when you are creating the formula, you can actually start it with the plus sign [+]. Now the reason you do that is because there's this nice big plus sign [+] on a full-size keyboard, and so that's really easy to hit. So you can start the formula with the plus sign [+] in Excel. Once you hit enter, it will actually insert the equal sign [=] in front of that plus sign [+]. So technically you can start formulas with the plus sign [+]. Excel does the work in the background and it will put the equal sign [=] in there for you.

The equal sign [=] is sort of tucked away in the middle of the keyboard, so that's why I tend to use the plus sign [+] on the right-hand side on the numeric keypad. That's one of the reasons why I do recommend that if you're on a laptop, for example, that you use a separate full-size keyboard because that numeric keypad has the plus [+], the minus [-], the divide [/] and the asterisk [*]. So most of your formulas can be created using that numeric keypad on the right-hand side, and it's also got a nice big enter key as well.

Using functions:
When you are using functions in Excel, one of the things that can speed up your work is the tab key. When you start typing, for example, if you type in =VL, you can hit the tab key and Excel will put the VLOOKUP function with the opening bracket in for you. That's because no other function starts with VL. You can also use Excel for XLOOKUP and that puts the whole function name in there with the opening bracket, so you don't need to hunt around on your keyboard for the opening bracket. That's one of the things I recommend is you use that tab key.

When you start typing, you'll see a list of functions that begin with that letter, and as you type more letters, that list will get shorter and shorter. So depending on the function that you're after, it might be the second or the third option on the list and to move down the list of functions, just use your arrow keys. Once it's selected, so highlighted in blue, you can just hit your tab key and that will insert the function as I mentioned with that opening bracket.

Function arguments:
Now, function arguments. Now in between the brackets of most functions, you can insert arguments. Now these are typically ranges or cells. Some functions don't have arguments. The main ones are NOW and TODAY. The NOW function puts in today's date with the time. The TODAY function, so it's just =TODAY(), puts in today's date and it's dynamic. So tomorrow, it'll have tomorrow's date in it. It has whatever the date is on that day, it automatically updates. So that's the TODAY function. So it doesn't have any arguments between the brackets, but most functions do have arguments between the brackets.

Now the argument is sort of a part of the function. They're separated by the commas and you build up the function using these various arguments. A lot of functions, say the SUM function, typically just has a range between the brackets. Not many people realise, you can put commas between the ranges in the SUM function. So if you've ever seen a formula that's got [+ SUM() + SUM() + SUM()] in it, you can actually get rid of all those sums and just have one and then just have a range with a comma, another range with a comma, and then another range [+SUM(A1:A2,B2:B3,C2:C3)]. So you can have as many ranges pretty much as you like separated by commas within the single SUM function. Typically, when you have a function, the commas separate the parts of the function. For example, the XLOOKUP function, it has six separate arguments within the brackets.

The first three are required and the last three are optional. In the companion video, I went through an example with XLOOKUP. As soon as you start typing XLOOKUP and have the opening bracket there, underneath the formula bar, you'll have the argument list. Now as I mentioned, there are six separate arguments, all separated by commas. The first three you have to have, so they are mandatory and the last three are optional. Now in that list, those optional ones are enclosed in square brackets [ ]. That sort of tells you that they are optional arguments. Optional arguments are always listed last. The first are mandatory and then all of the optional arguments are listed last in that argument list. When you are building it, it sort of shows you what you're up to. So when you hit the comma, it will bold the next argument name in that list that's going across the page. That's to help you understand where you are within the function because as I mentioned, XLOOKUP has six argument. So that's quite a few arguments to be figuring out where you are in that argument list. Keep an eye out on that bold argument because that's the one you are up to.

Relative and absolute cell and range references:
Cell references and range references when you are putting those into formulas, one of the things you need to be aware of is using the dollar signs. I've covered this in a few of the recent podcast. I'll go through it again quickly. When you enter the cell reference, if you press the F4 function key, that's going to place the dollar sign in front of the letter, so the column letter and in front of the row number, and that's called a fixed or an absolute reference. That reference within the formula will never change no matter where you copy it from and to. So that's a fixed reference.

If you press the F4 function key again, you'll see that the dollar sign is now just in front of the row number. So whatever it's in front of, that's what it's fixing, it won't change. Press the F4 key again and it goes in front of the column letter, you press it again, and then it goes back to what's called the relative reference. So relative references change relative to where you copy it from and to. The F4 key follows that same sequence. You'll get used to how many times you need to press it. You can remember the F4 function key does the dollar signs because the number four key has the dollar sign. That's a way to remember the F4 function key. Look at the number four, which has the dollar sign and so the F4 actually puts the dollar signs into your formulas.

Now, something that I demonstrated in the video, so the F4 function key works a little bit differently if you are editing a formula compared to creating a formula. When you insert a range in a formula, when you're building the formula from scratch, when you press F4, it actually put the dollar signs on both references, the start cell and the end cell. If you are editing a formula and you are on the end of a range reference, when you press the F4 key, it only works on the second or the end reference. Basically whatever reference you are next to, that's the reference that the F4 key will update with the dollar signs. To put the dollar signs on the whole range reference, you actually have to select it in the formula bar and then press F4. So just a heads-up, there is that slight difference between how the F4 function key works when you are creating a formula compared to editing a formula.

The calculation sequence:
Now something again that I've covered in previous podcasts is the calculation sequence. It's an important thing to get your head around. You might've learned it at school known as BIMDAS. So brackets, indices, multiplication, division, addition and subtraction. That's the sequence that Excel is going to go through when it calculates your formulas. So you do need to make sure that you've got the calculations in the right sequence or you use the brackets around sections of your formula to control the sequence because when you put brackets around a calculation, that's what's calculated first. And it can take a little bit of trial and error to get it right, but just remember that the brackets can override the calculation sequence because they're done first.

Parentheses (brackets):
Speaking of brackets or parentheses. So the technical term for brackets is parentheses, but that's too much of a mouthful, so I'll keep using the term brackets and bracket colour is important. When you're building a formula that has multiple functions in it, the typical one is an IF function, which then uses maybe an XLOOKUP or a VLOOKUP, you'll find that the IF function, which is the first function has a black bracket, and then when you use the XLOOKUP for example, it will have a red bracket on it.

So Excel's got a hierarchy of colours when it comes to the brackets. The first bracket is always black, and then the next level down is red, and I think the next level down is green, and then there's other colours as you go down extra levels. That means at the end of your function, the last bracket should always be black. And then depending on the sequence that you are using, you need to make sure that you have matching coloured brackets for the function that you are using. Now, one of the common errors in Excel is missing a bracket, missing a comma, and the other one is when you're working with text is to miss a quotation mark. So brackets and quotation marks are a little bit similar in that you have an opening one and a closing one. So you do need to make sure that you've got them matched and the colours on the brackets help you match those colours for each function that you are using within a formula.

Jacqueline Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show, INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.

Neale Blackwood:
Colours in formulas:
Now speaking of colours, Excel uses colours to also tell you what part of the formula is on the sheet. In the example on the companion video, I've got a SUM function that's adding up two ranges and one of them is blue and one of them is red. You can manipulate the colours to actually change the formula. If you drag the colours, so what you do is you point to the border of the colour and it will go bold. You can click and hold with your mouse and drag it, and in moving it on the sheet, you will update automatically the formula as well. That can be really useful because you can do your editing without having to type anything. You can just use the mouse to move a range around or a cell or even extend a range by dragging. There's a little icon on the bottom right-hand corner, double-headed arrow. You can drag that to extend a range. And so as I said, you can update a formula without having to type anything. You can just use the mouse to do that.

Entering cell and Range references:
Now, entering cell references and range references, there's three ways you can do it. You can type it in, =A1. You don't have to do it in capitals, Excel will capitalise it for you. So you can use a lowercase A. Now that's okay if you know what the references are and they're fairly short. However, if they're longer or you're doing a range reference, there is a possibility of putting a typo into your formula. In general, I don't use the typing method to enter cell references or range references. I will typically use either the arrow keys. So you can type in equals [=] or the plus [+]. If you then press your up, down, left, or right arrows, that will insert that cell reference into your formula. That's the way I typically do it. If you are creating formulas that are using values that are close to your cell, the arrow keys are the best way to do it. If you need to select a range however, I do recommend using the mouse to do that because it's a lot easier than typing the range reference, putting the colon in, that sort of thing.

So range reference is much easier to use than mouse. If you need to refer to a cell on another sheet or even range on another sheet, then that's when you pretty much always use the mouse as well. So you can type it in, but I don't recommend doing that. You can use the arrow keys. I do recommend doing that for cells that are close. Any other references cells or ranges, it's much easier just to use the mouse. That avoids typos. Sometimes you might get an Excel dialogue popup telling you that you've got a typo in a formula. When that happens, always check it because it does try and correct it, but it doesn't always do it correctly. So always check that. You can click "no" on that dialogue too, by the way, and actually go back into your formula and make sure that you've got your brackets, your commas, or your quotation marks in the correct place.

The Function Wizard:
When you first start using a function, you might like to use something that's called the Function Wizard. You can type in the function, so let's say SUMIFS. Now if you're not sure how to use SUMIFS, you can click in the little FX button that sits right next to the formula bar. So it's just the letter F and X and it will open up the what's called the Function Arguments, it's typically called the Function Wizard, and this will break the function down into parts in a dialogue, and you can then enter ranges and cell references into the various boxes to build your formula and Excel will put the commas and things like that in there for you. Each of the boxes has a description as well, so you can sort of learn what the function does by reading those explanations. So the Function Wizard, again, using that FX button can help you build the formula as well as understand what the formula does. That's also demonstrated in the companion video

Formula auditing:
Formula auditing. If you've got formulas that you want to sort of understand a little bit better, there is a section on the formulas tab. So the formulas tab pretty much gives you everything you need to know for formulas. It's got categorised functions on the left-hand side, then it's got the defined names in the middle, there's a formulas auditing section on the right-hand side that has a lot of options that can help you understand your formulas. So you can trace precedents, trace dependence. There's what's called an evaluate formula button that can take you through if you've got, let's say, a complex formula that uses multiple functions, this can sort of step you through the various parts, again to help you understand the formulas.

Now, in terms of understanding formulas, one of the things that can be helpful is seeing what a particular formula or function does within a larger formula. What you used to do, and what I've been suggesting over the years is that you can select part of the formula and press the F9 function key, and what that does, it converts the formula into its result. Now, the latest version of Excel has updated that so that if you select part of a formula in the function bar, it will show you slot just above it what it's going to return. So you don't need to press that F9 function key. That's a new feature that has been released. So it's really easy now to see what the formula or function is returning. The only thing you need to keep in mind is what you select in the formula bar must be independently calculatable, if that's a word. It's got to include the brackets, both brackets, for example, if you're selecting a function. And then as soon as you select enough of it to calculate, it will put the result above the part that you've selected in that formula bar. Just look slightly above it and you'll see there what it's returning there. So that can help you understand formulas, see what the values are, maybe coming from other sheets, things like that.

IF error:
Excel functions return lots of different errors like the REF error, the N/A error, the NAME error, things like that. One function can handle nearly all of them. It's the IFERROR function. Basically, you put IFERROR(), then you put your formula or calculation, then you put a comma, and then you put whatever you want to do if an error is encountered. Now that may be a zero, that may be two quotation marks for a blank. It may be a text entry that might say, "Error," something like that. Just remember anything in text has the quotation marks around it. So IFERROR is very useful, you can avoid errors. The downside with IFERROR is it may mask some errors that you need to handle differently.

REF error:
For example, the REF error. So REF errors are a little bit scary because there's no way really to figure out what they should have been and they will sort of cascade through your file if you are referring to ranges that have REF errors. And as I mentioned, the IFERROR function can sort of mask those. So that's the downside of IFERROR.

Dynamic arrays and the SPILL error:
IFERROR also doesn't handle one of the new errors, which is associated with what I wanted to finish off with, which is dynamic arrays. So dynamic arrays have been covered in previous podcasts. They're a new way of calculating in Excel. They're only in the subscription version and also Excel 2021. They allow you pretty much to do a calculation, which you used to be able to only do on a cell, you can now do on a range, which then gives you a lot of flexibility. It's sort of going to change the way you create spreadsheets. As I mentioned, I've covered that in detail, because it is a very big topic, in previous podcasts and also articles and videos. So check out the INTHEBLACK website and just do a search on dynamic arrays. You'll be able to pick up that. That's the new way of calculating in Excel.

Oh, by the way, the error that can't be handled by IFERROR is the SPILL error. Now, dynamic arrays, they actually automatically extend, it's called spilling. So they can spill down, they can spill across, and they can spill down and across to create a two-dimensional range. When they do that, they have to have blank cells around them. If there's something in there, you'll get what's called a SPILL error. That is not handled by IFERROR. So you do have to fix or resolve any SPILL errors in your files. Now two new symbols associated with dynamic arrays and formulas. The first is what's called the hash symbol, so it's above the number three. Some people call it the pound symbol. I'm not sure why. I've always called it the hash symbol, but it's above the number three on your keyboard, and that's the one that will allow you to refer to a spill range. When you refer to a spill range, your formula will spill to match that range, which is a really powerful technique. The other symbol that you might see, especially if you open a file from previous versions, is the @ symbol placed in front of a function. What that means is it's basically using Excel as it used to be used, and it's not using the dynamic array functionality. So you can sort of turn that off by putting the at symbol in front of a function. The at symbol, again, is the one above the number 2 on your keyboard. Okay, I've covered quite a few formula and function tips and tricks. Hopefully you've picked up a few useful shortcuts and techniques that you can apply to your Excel files. If you have enjoyed the podcast, please subscribe, and if you can, please leave a review. Thanks for listening.

Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode, and we hope you can join us next time for another episode of Excel Tips.