Unlock 7 underused Excel features you still need today

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 I thought I'd cover a number of underused features in Excel. So, these are features that you might've missed along the way. Excel has lots of new features, but these are old features that have been around for a while and are quite useful. The first one I'd like to cover is Windows. Now, not the operating system, but the fact that you can open multiple windows in Excel. And when you do open the window, you can actually move that window over to another screen. So, that allows you to look at different sheets in different windows on the same file, which makes it easy to copy, to compare.

So, the icons you need to use are all in the view ribbon tab, and they are all under the window section. So, there's a new window icon that will open them up. The arrange all icon will allow you to sort of display them all on one screen fairly easily. So, if you've got a really large screen, you can have multiple windows open on that one. This does come with a warning. Each time you open up a new window, you'll get a number in the title bar at the top. It's not very big, it's not in your face at all, but that number has an important meaning. So, all of the other numbers apart from one, so one is the main screen. So, all of the other windows that you open do not have any view settings or print settings in them.

Now the danger here, so the warning is when you are closing the extra windows, always close them in reverse numerical order. So, you should be closing three and then two, and then leaving the last one there and then save the file. So, if you close the number one window, the main window and then save the file, you'll actually lose your print and view settings. So, just be careful. It doesn't affect any data. It's a good idea to close all of the windows as well before you save the file. Because if you don't, if you close the file with the windows open, when you open the file the next time, those windows will still be there. There'll be separate windows, which is okay for you because you did it.

But if someone else opens the file and they're not used to seeing multiple windows, then it could be a bit confusing for them. So, in general, it's a good idea to close all of the extra windows before you save. And as I mentioned, close them in reverse numerical order so that the number one screen is the last screen that's there and you save that one. There's some useful options in the window section on the view tab. You can view things side by side. There's also the ability to synchronise scrolling. So, let's say, you've got two separate sheets that you might want to compare. There is a synchronised scrolling option to allow you to do that. So, as you scroll in one window, you'll also scroll in the second window.

Okay, so that's windows. So, it is useful can save time when you're copying and pasting between the sheets. Also, you could use it if you've got a very large sheet, and you want to look at something at the top of the sheet and also at the bottom of the sheet at the same time. You can do that using separate windows. Okay, another feature that's very useful and I think underused is the grouping feature. Now, unfortunately in Excel, grouping is probably the most overused term because you can group a whole lot of things. What I wanted to talk about was grouping rows and columns. So, when you group rows and columns, you can hide and unhide those rows and columns very easily.

And it puts an icon above the column letters and to the left of the row numbers. So, the beauty of that is it has a visual clue that there are hidden rows also that you can hide the rows. So, I recommend if you are hiding rows and columns to always use grouping to do it because it puts that visual clue on the oscreen, as well as making it really easy to unhide and hide just clicking buttons. There's another section that also has some numbers in it. So, this is in the top left of the grid. There will be some numbers created once you create the grouping, and those numbers allow you to hide and unhide even quicker. So, if you've got multiple sections that you've hidden, you can hide and unhide those sections in one go.

Just clicking those little numbers, they will appear in the top left corner of the grid. Now there's some keyboard shortcuts to apply grouping. So, to apply grouping, you select the columns or the rows and you press shift, alt, and the right arrow, and that will apply the grouping. And if you wanted to remove the grouping, you can use shift alt and the left arrow. Now a feature that's related to this is called auto outline. And if you've got an existing report and, let's say, that report has subtotals or some functions built into it, then Excel can use those formulas to determine the rows that you would like to hide.

So, basically if there's a sum function on a row, Excel will determine that that row should be visible and that the detail rows should be hidden. So, if you select a report and then go to the data ribbon and on the far right-hand side, there is a group drop down. If you click that, there's an auto outline option. And what that will do is that will automatically add the grouping buttons for you in one step. You don't have to do anything. All of the buttons will be created and it also creates a bit of a hierarchy. So, those numbers that I mentioned that are in the top left corner allow you to quickly hide and unhide the subtotals or the detail as well.

Now, one downside of using that auto outline is that the undo button doesn't work, so you can't undo it that way. What you have to do is there's an ungroup icon right next to the group icon, and if you click that, you'll see there is a clear outline. So, undo doesn't work, but there is a clear outline option that you can use. So, the default location for the grouping icon is always on the right of the columns being hidden. And in terms of rows, it's always at the bottom of the rows being hidden. Now that's a normal layout, pretty standard layout, but with the advent of dynamic arrays where we might have our totals at the top and some of our totals on the left, you do have the ability to change the location of those grouping icons.

Now it's likely they've tried to hide this. Okay. So, to make that change, and this change applies to the whole sheet. So, you can change the location of the grouping icons on a whole sheet. And what you do is there is an outline section. It's the last section in the data ribbon tab, and on the bottom right-hand corner of that section, there is a little arrow icon. Now, that's a special icon that's used to show that there are extra options available. Now, the outline one is the only section that has that icon in the data tab. But when you click it, you will see that there is a tick box for where the rows shown below the detail and the columns are shown to the right of the detail.

If you untick those tick boxes, then that changes the location to the other side and to above. So, that's all you need to do. Click that and then click okay, and that will change the location of the grouping icons. Now I mentioned subtotals earlier. There is a subtotal function in Excel and it's quite good at creating the subtotals in a list. And the beauty of it is there is an automated way to insert the subtotals. So, the idea is you have a list and you've got that list sorted by a column in the list. And what you can do is based on that sorting, you can create automated subtotals where Excel will put them in. So, basically click on the list, go to the data ribbon tab.

And again, on the far right-hand side, there's a subtotal button. And if you click on that, you then have the ability to specify the column that you've sorted by. So, that's the column it's going to use to figure out where to insert the subtotals. You can then choose the calculation to perform. 99 times out of a hundred, it's going to be the sum function. And then you can then tick the columns that you want to add up basically. Once you click okay, it will insert all of the subtotal functions for you. It'll put a subtotal at the bottom as well. And it also automatically inserts the grouping icons, so it does all of the work for you. So, if you want to get rid of the subtotals, there is a remove all button in the same dialogue.

So, just go back to the subtotal icon and there's a remove all option as well. Now, when I show people this, they like it, but there is one thing that they always pick up. When the subtotal is inserted, it inserts rows to put the subtotals, so it adds a row with the subtotal. And there's also a description in bold, but the subtotal values are not bold, and so people like to have that consistency. So, there is a technique that I demonstrate in the companion video that you can convert the subtotal values into bold fairly easily. So, the way you do it is you use the grouping icons to only show the subtotal rows.

You select the whole range, and then you press the F5 function key, click the special button. And then there is a option for visible cells only, which we'll also look at in a minute. So, tick that option and then click okay. And what that does is that only selects the visible cells. So, at the moment, that's just the subtotals. If you click the bold or go control B, that will only format the visible cells. All the hidden rows are ignored. So, speaking of visible cells only, it is a useful feature as well. So, sometimes once you've got those subtotals visible and the detail is hidden, you want to capture those subtotals.

So, again, in the same way, you could use that F5 and special option, but there is also a keyboard shortcut that will select just the visible cells. So, select the range, hold the alt key down and press the semicolon, and that will select just the visible cells. So, if you then go control C to copy it, go somewhere else and control V to paste it, you will only paste the visible cells. Now, the downside with that is that you will only paste the values. It doesn't paste any formulas. So, just a heads-up on that. Okay, something else that's been around for many, many versions and is quite powerful is the custom lists option.

Now, you've probably already used custom lists if you've dragged a cell that has January in it and then it'll put February, March, et cetera. That's a custom list. Now, there's some built-in ones for the months and the days of the week, but you can create your own custom lists, and that's really useful for things like maybe states or departments or even people's names, categories, divisions, all these things that you use regularly. If you've got a specific sequence there that you like to maintain, you can create a custom list for it. And when you do, you can then type in the first name of the list and use your fill handle. So, that's that little plus sign in the bottom right-hand corner.

And you can drag that down with the mouse and you will populate all of the other names in the list, just like January and February. Now again, it's like they've tried to hide this. So, what you can do, if you've already got your list, which you probably do somewhere, just select it and then go file, options, and then advanced. And then you have to scroll, use the scroll bar on the right-hand side all the way to the bottom, and there's a button for edit custom lists. So, click that and that will show you the existing ones. There's an import button there that will import your list, and that's it. Just click okay and okay again, and then you can type in your name, hit enter.

And then drag that cell down and you'll get all of the other names. And it's a sequence, so if you keep dragging, it'll keep repeating it. Depending on what you're doing, that may or may not be useful. The other thing that custom lists allow you to do as well is to sort in a specific sequence. So, if you need your categories, for example, in a specific sequence that's not alphabetic, if you create a custom list for that sequence, you can use that custom list as a sort option. So, when you right click sort, there's a custom sort option. And then when you click on the drop-down, that might have A to Z in it for the alphabetic, you'll see there's a custom sort option there as well, and you can select your list.

So, that gets around any issues you have. If you do need to sort in a non-alpha order, then you can use a custom list to do that. Now the last one I'd like to share, it's not going to really improve your productivity, but it might make you smile because it will fix the spelling in Excel. So, Excel uses American spelling for its buttons and menu options. I would prefer it to use Australian spelling or English spelling, rather than the US spelling. So, you can change that. So, here's you do it. You click on the file menu, click options, and then click language. And in the top section on the right-hand side, you have the ability to install another language and that will control the menus and the buttons.

And you want to look for English UK and install that, and that will then change the buttons. You might need to close Excel and get back in again. I've done that, so it now uses the English spelling, rather than the US spelling. Just a heads-up, depending on your organization's security settings, you might not be able to do it because there is a download required to get the language into Excel. So, hopefully, fingers crossed, you can do that. And then you can get your icons and menu items all spelling correctly and not using the US spelling. Okay, so I've shared quite a few different underused features in Excel.

Hopefully, some of those can improve your productivity and get the spelling right in your Excel. Hope you enjoyed the podcast. Thanks for listening.

Jacquie 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.