Excel Mini Episode: Data Ribbon

Neale Blackwood CPA:
Hello, and welcome to the CPA Australia Podcast, your weekly source of business, leadership and public practise accounting information.

With all the talk of Big data, and data analytics, and everything associated with those, it's probably worth looking at the data ribbon in Excel. Now, on the left had side of the data ribbon is the get and transform data. Now, I am working with the latest version of Excel, so there will be some differences with versions. Basically, the get and transform data section is what is commonly called, Power Query. I've written a few articles on Power Query, so you can check out the INTHEBLACK website for that, and also the last podcast covered Power Query. I highly recommend that if you use any sort of data, that you check out Power Query, because there's a lot of time saving features involved with it.

Okay, refresh all, that's a useful one. So refresh all allows you to refresh everything in the file, and there is a keyboard shortcut for refresh all, and that's Control, Alt, F5. So hold the Control and the Alt key down and press the F5 function key, and that's the shortcut for refresh all. If you're on a pivot table or something like that and you just want to refresh that, you can just go Alt F5, and that's the refresh if you've got a pivot table selected.

There is a section on sort and filter in the data ribbon, and there's a few shortcuts that are useful to know for sorting and filtering. Also, a couple of things that a lot of people aren't aware of. Now, filtering allows you to display certain rows that match the filter that you have in place. Now, one thing to note is that, when you apply a filter to one column, so let's say we've got a standard type list and we've got state in there, and you filter by WA for example, if you had postcode in that data as well, if you went then to filter on postcode, you would only see the postcodes starting with the number six, because that's the postcodes for WA. So even though there might be a whole lot of other postcodes in there, because you've applied a filter to one column, that can affect the filter in other columns. So they're sort of... It's a bit of a technical term, but they're AND filters. So what it's saying is, that you want to filter by WA and something else.

There is another type of filter that you can apply, and that's an OR filter. So let's say you wanted to filter by WA or a postcode of 2000 for example, well, in Excel standard filtering, you can't do that. Because, when you choose 2000 for example, you're only going to have New South Wales as a state. Or, if you click WA as the state, then you're only going to have six numbers... sorry, numbers that start with six as an option to select. But if you do want to use what's called an OR filter, so one thing or another, so these are separate columns, you can use the advanced filter to do that. So advanced filter, I think I've done an article on that, so you can check it out. So the advanced filter in Excel allows you to do all filters between columns. So if something's in state column, or somethings in the postcode column. So you could do both of those, so it is possible.

So filtering, something else people aren't aware of is that, filtering can be done by colour. Excel allows you to apply background colours, you can use conditional formatting, and you can use those colours to filter. The easiest way to do it, is just a right click on a cell that has the colour you want, and then choose filter, and then you'll be able to say, filter by selected cells colour. If you use the filter dropdown, then you'll see at the top, then you have the ability to filter by colour. And that's also the background colour, or the fill colour, and you could use the font colour as well if you're using different font colours. You can also if you use conditional formatting, and use things like ticks, and crosses, and other icons, you can filter by icon as well. So that's something not a lot of people are aware of, filtering by colour.

A couple of shortcuts that are useful. If you've got a list and you might not have used format as table, I recommend you check out format as table. I did an article on format as table last year, it's well worth looking at if you do use a lot of tables in your files. But if you've got a table that's not formatted as a table, and you want to apply the filter icons to it, to the header, then all you need to do is hold Control, and Shift, and press the letter L, L for Larry. So Control, Shift, L. It's L by the way, because in the old days in Excel 2003 and earlier, they were called lists, and hence you used the L. So Control, Shift, L will add the drop downs for filtering. If you press it again, it will remove them. So it sort of turns them off, turns them on.

If you wanted to remove all of the filters in one go, so if you applied a number of filters to multiple columns and you just want to start again, the easy way to do that, and this is pressed in sequence, is Alt, A, C. So you just press the Alt key, which is on either side of the space bar, the letter A and then the letter C, in sequence, you don't hold them down. So Alt, A, C, and that clears any filters. It still leaves the filter drop downs there, but all of the filters will be removed.

Just a reminder, filtering never changes the sequence of the data. So when you apply a filter, the sequence is unchanged, the row numbers go blue. So if you look at the row numbers in a sheet and they're blue, the actual numbers themselves, then that means that there is a filter in place somewhere in that sheet. So that's sort of the visual clue that there is a filter in place. An easy way to do filtering is just by right clicking. So if you've got a list, and let's say we want to filter by WA again, all you do is, in the column that you want to filter, you just right click on a cell that has WA in it, and there's the filter option, and then you can say filter by selected cells value. So right click, filter, and then filter by selected cells value. That's a really quick way to apply a simple filter.

Something else that's worth knowing, is that the subtotal function, if you have a filtered list, if you use a subtotal on that list, it will only add up the visible cells. That's quite a powerful feature. So if you wanted to add up all of a range that had a yellow background for example, what you could do is, filter it by the yellow cell background, and then add it up using the subtotal. And the subtotal will only add up the visible cells. So once you filter by it, you will only see the visible cells being added up. If you unfilter it, then it just obviously goes back to the full amount. So subtotal is quite powerful, and that's just a standard subtotal.

So subtotal sort of has a double life. The subtotal function can use number nine, which is summing, that's the very first argument in the subtotal. There is also a 109 that you can use, and that works on hidden rows that are hidden not with filters. So if you actually hide a row, hide a column, things like that, then you can use the 109 to actually add up just the visible cells. So subtotal is quite powerful, so you can check that out. There's a subtotal icon on the far right hand side of the data ribbon, and that can automate inserting subtotals in a list.

Sorting. So sorting does change the sequence of your table. Just a little word of warning, it doesn't happen as much as it used to, but it's a good idea if you are going to start sorting, to put some sort of sequential number in your table, if you don't already have it. If you do a sort, and then say save, and close the file, and then come back to the file and you want to go back to the sequence that it was in, there's no real way to do that unless you've got some sort of sequential number that you can use to resort it. So it's always a good idea, if the sequence that you're looking at is important, then you should put a sequential number in there to sort of capture that sequence so you can return to it if you have to.

In the old days, in Excel 2003, you could only sort by three levels, that's all gone. There is no real limit now to how many levels you can apply to a sort. Also, just like filtering, you can sort by colour. So when you're sorting, you can either use the numeric order, or the alphabetic order. Obviously, there's no colour order, so red doesn't go above yellow, or that sort of thing, so when you're sorting it tends to put the colour at the top. So again, there's no real sequence built into colours, so what it does it just puts the colours, or the icon if you're using that, at the top of your list.

Just a heads up, when it does that, if you have sorted by some other sequence, that other sequence will still be there underneath the colours. So it still keeps that sequence. So all it does is, it shifts everything up to the top with the colour for example, and so it doesn't affect any sequence that you've already built in. Also, there's also a right click sort that's a quick way to sort. If you just want to do a single column, you can just do a right click, and there's a sort option there. A couple of keyboard shortcuts, if you wanted to do the sort in ascending order on a particular column, you just select a cell in that column and press in sequence, Alt, A, S, A. The letters A, S, A in sequence, you don't hold them down. If you want to do descending, it's Alt, A, S, A, and if you want to open up the sort dialogue which gives you pretty much all of the options available for sorting, it's Alt, A, S, S.

Next thing we'll look at is, text to columns. Now, text to columns is a little bit outdated these days because of Power Query, because Power Query can pretty much give you a lot more control. Text to columns allows you to take for example, if you've got a list... a single column that has first name space last name, then text to columns can actually break that up just using the space character as the separator. So text to columns can, if it's just a quick job, then you can use text to columns. If it's something that you do regularly, I would set up a Power Query, because basically you could automate that process.

Whereas, text to columns, you've got to go through all of the steps to do it. With Power Query, if you do it once, basically you just refresh it, and it will redo that, whatever you're doing with the breaking up the columns.

Flash fill, I might do that in a separate podcast, flash fill. It's a new feature that came in, in Excel 2013, and it's sort of like pattern matching.

The next little icon there is, remove duplicates. Now, this ones really powerful, so if you've got a list and you think there's some duplicates in there, you can use that to basically look at all the columns, and it will remove... it keeps the first entry, and removes any subsequent entries in the list. You can define how many columns to look at. In most cases you're probably going to use all of the columns, and that's the default, but you can actually just specify a couple of columns, thought that could be a little bit dangerous. So, remove duplicates is a really quick and easy feature.

Okay, then data validation. I'll probably do a separate podcast on data validation as well. Data validation is, Excels ability to control what the user puts into a cell, that's data validation.

Consolidate, now that's one I must admit that I use hardly at all, and I'm not a fan of it. And these days I would actually prefer to use Power Query to do all my consolidations. Consolidate has been around for a while, but these days I prefer to use Power Query to consolidate. So for example, there's different ways to consolidate. You could just append, which basically means you put one data set under another, Power Query does that really well. You could use merge, where you're sort of combining data out of two data sets to create another data set. I don't think consolidate does that, whereas, Power Query can. So these days I prefer to use Power Query for consolidation.

There's also an icon for relationships. Now, relationships need to use formatted tables, so if you haven't got any formatted tables in your file, then the relationships icon will be greyed out. Now, relationships allow you to, as it says, create relationships between data. I'll probably do a separate podcast on that as well, because I think that's worthwhile looking at.

A couple of other icons on there, are the what if analysis. There's something called the scenario manager, goal seek, and data table. Data table is not what you might think, it's not related to formatted tables. Data tables allow you to do different scenarios. So basically, you can look at different scenarios in a budget, or a financial model, using one variable or two variables, and see the results. Just a warning, data tables do slow your model down, because it is doing multiple calculations instead of just the single calculation.

Goal seek, is just a very simple way to get Excel to do all the number crunching for you, and if you need to for example, sort out what average margin gives you a profit of $1 million, then you can use goal seek to do that. It basically just

takes a cell... an input cell and just tries all different values in it until it ends up as close to your goal as possible, and so it can be a quick way to find a solution instead of you doing trial and error, especially if you've got a fairly complex model.

Scenario manager, I mentioned is sort of related to something called Solver. Now, Solver is an add-in to Excel, and it's been around for many years, and it's developed, its got it's own website now, and its got it's own... You can actually buy a more advanced version of it. It's sort of like goal seek on steroids. Goal seek can only change one cell, whereas, Solver can change many cells, and you can put rules in place. So you can say it's got to be greater than zero, which stops it trying to negative number, those sort of things. So you can get quite advanced with Solver.

Now, scenario manager can be used by itself, so you don't need Solver to actually use it, but it tends to work fairly closely with Solver. So what you can do is, you can actually create a name for a scenario, and then define certain input cells, and capture the values in those input cells. And so you can create a number of different scenarios with a number of different entries in those input cells, and then you can sort of replay them. So you can go into the scenario manager and show a certain scenario, and you can even create a report, which will then list out each of the scenarios with their relevant input cells, and also you can define cells. Check out scenario manager, which remember is in the what if analysis dropdown.

Forecast sheet, that's another icon there. That's a brand new feature in Excel 2016. That's where if you give Excel, say a table, you can... a table of, I don't know, sales, daily sales, something like that, Excel can forecast the sales based on the existing data. And there's a couple of different ways it forecasts, so you can check that out.

Grouping, which is on the... we're getting towards the right hand side of the data ribbon now. Grouping is the best way to hide rows and columns. You can just select a range, right click, hide, that is a little bit dangerous as people may not be aware that you have hidden rows or columns. When you use grouping to do it, it actually puts an icon on the grid, above the grid for columns, and to the left of the grid for rows. So there's a visual clue that the rows have been hidden. Also, it's really easy to hide or unhide, so another advantage with using grouping. So check that out. There are some keyboard shortcuts associated with grouping. If you want to apply grouping, just select the rows, hold the Shift and the Alt key down, and press the right arrow. And funnily enough, to remove grouping, it's Shift, Alt and the left arrow. And that also applies for columns as well.

Under the grouping dropdown, is an outline option. And so, if you've got a table in existence already that has a lot of subtotals or sums in it, you can try applying the outline to it, and it will have a look at the structure, and it can apply the grouping for you automatically. So that's something to check out, it's called outline. Auto outline, actually. There it is.

There are some icons that you can put on your quick access toolbar as well. That's the little toolbar that sits above the ribbon, or below the ribbon. You can right click any icon, and add to that quick access toolbar. I tend to have that below the ribbon, that's the way I work.

And subtotal, right on the end there, we've got the subtotal. That allows you to take a list... now it must be a sorted list, so whatever you want to subtotal by, you need to sort your list by that column, and then you can click the subtotal icon. So what it will do, it will insert the subtotal at each change in the column that you have specified, so that's why it must be sorted in that column. So if all the New South Wales are together, it will go down to the bottom of New South Wales, put in a subtotal. Then it will go down to Victoria, all of Victoria is under that, it will put a subtotal. And it will even put a grand total at the bottom. So subtotal is pretty quick. It does guess what you want. It's got some options you can tick. You don't have to use the sum, it defaults to that, but you can use the subtotal for things like averaging, counting as well, that those options are there.

Okay, so that is the data ribbon, and I might do a few followup podcasts on some of these items which I'll go into a little bit more detail on them. Have a play. As I said, there's lots of good stuff that can save you a lot of time, like the remove duplicates, and definitely Power Query. I keep pushing Power Query. Power Query is a topic, I think, most accountants should be at least aware of, and I think many accountants should be able to use. Check out the articles online. So just do a search for Power Query on the INTHEBLACK website, and don't forget that all of my articles do have a companion video, and some of them have companion files. So if you want to sort of play along, you can as well.

As I said, I'll do a few more podcasts on some of the other items in a little bit more detail.

Thanks for listening.

Outro:
Thank you for listening to the CPA Australia Podcast. To download the transcript, and to access the show notes for this episode, visit www.cpaaustralia.com.au/podcast. Never miss an episode by subscribing to our podcast on iTunes, Spotify or Stitcher.