Excel Features and Functions

Intro:
Hello, and welcome to the CPA Australia Podcast. Your weekly source for accounting, education, career and leadership discussion.

Neale Blackwood CPA:
Hi, my name's Neale Blackwood. I write the Excel Yourself articles for the INTHEBLACK magazine. With every new version of Excel, Microsoft adds more and more features and functions. So depending on your version, you could be sitting on some hidden gems. Now, when Excel 2007 was being developed, Microsoft did a survey of Excel 2003 users. So, they wanted to find out what they wanted. They got the responses back and they found out that 80% of what people wanted was already in Excel, but people didn't know what it was called or where it was.

Neale Blackwood CPA:
That was what drove the development of the ribbon interface, which first appeared in Excel 2007. So, today we're going to be looking at what you might have missed in Excel over the past 10 or 11 years. We'll go through version by version. Excel 2007, that's where, as I mentioned, we were first confronted with the ribbon. The ribbon was a change from the menu system that was already there and toolbars. But unfortunately when they put the ribbon in, they didn't really explain, there was what's called a quick access toolbar that's also there and you can customise that toolbar.

Neale Blackwood CPA:
So on any icon on the ribbon, you can right click it and add it to what's called the quick access toolbar. Now that exists when you instal Excel above the ribbon. But if you right click it, you'll see there's an option to show below the ribbon, which is sort of what I do. And once you add all of the icons that you use frequently, you can then hide the ribbon. So Ctrl+F1 is the quick way to do that. That's a toggle command. So if you press it again, it'll show the ribbon. Also in Excel 2007, they brought in a new file type. So it was an XML file type and there was a couple of them.

Neale Blackwood CPA:
There was the XLSX and the XLSM and the M was the macro enabled version. There was also another file type that was brought in and that's XLSB, now that stands for binary. Now I have written an article about that and the binary file type wasn't really taken up when it first came out, because it was some compatibility issues with other systems, but over the years, that's sort of gone away now as an issue. And the binary file type is actually very efficient if you do have large files. So for example, if you had a 10 megabytes Excel file, if you save it as binary, then you'll probably drop to six or seven megabytes.

Neale Blackwood CPA:
Binary file types can also handle macros as well. In Excel 2007, that's where our number of rows leapt up to over a 1,000,000 and the columns are now over 16,000. Probably one of the most important features that was added in Excel 2007 was the formatted table feature. Now formatted tables are a lot more than just about formatting. In fact, the formatting is probably the least important part of it. Formatting tables basically allow you to define a table and that table will be treated like a little database basically by Excel. And so as you add columns to it or add rows to it, the table will automatically expand.

Neale Blackwood CPA:
It's also got... Okay, they're called structured references. I tend to call them table names, but they sort of range names. And the beauty of them is that they automatically expand. When you use a formatted table as a data source for a PivotTable, it has the advantage of when you refresh the PivotTable, then any extra rows or columns that have been added to the formatted table will automatically be included. A couple of functions that were added that are quite useful is the SUMIFS function and the COUNTIFS function. Now the S on the end basically means that you can do multiple criteria.

Neale Blackwood CPA:
There was a SUMIF function, SUMIFS allows you to do multiple criteria when you are summing and the COUNTIFS you can do multiple criteria for counting. Another useful function that was added in Excel 2007 is the IFERROR function, which basically simplifies the handling of Excel's errors. So check those three functions out if you haven't already used them. Okay, let's jump ahead three years. So we're in Excel 2010, what's been added to that is something called a slicer. So slicers are in the PivotTable ribbon, and they allow you to filter PivotTables.

Neale Blackwood CPA:
Now that already was a filter built into PivotTables, but there was a couple of things that you couldn't do with the filters in PivotTables. One of them was, they're a bit clunky to actually use for the users. Slicers are a graphical interface, so they're a lot easier to use, and also if you add a filter with a slicer, you can still report on that field. One of the limitations with filtering a field in a PivotTable is you can't report on that field, but with a slicer you can. A chart, there's no new charts in Excel 2010, but there was a new chart type and that was the sparkline charts.

Neale Blackwood CPA:
Now sparklines were named by the visualisation author, Edward Tufte, and they are small charts that fit basically in a cell. And the two common ones are a column and a line chart. So sparklines are in the insert menu, so you can check them out, they're reasonably easy to use. A new function that was added in Excel 2010 was the AGGREGATE function. Now this is sort of a cousin of SUBTOTAL, and it allows you to do multiple calculations, but the advantage with AGGREGATE it has the option of ignoring errors. So no other calculation function can ignore errors, but AGGREGATE can. It's only limitation is that it only works on columns. You can't use it sort of sideways across a row.

Neale Blackwood CPA:
Now, probably the most important addition to Excel in recent years happened in 2010, when the Power Query add-in was made available. It's a free add-in. So it's free for 2010 and 2013. It's actually built in to 2016. It had a name change which I'll get to when we get there. Now, Power Query allows you to import data whilst at the same time, performing data cleansing operations. So I've written a number of articles over the last two or three years on Power Query, highly recommend that you look it up if you haven't already started using it. It's extremely powerful.

Neale Blackwood CPA:
It can do things in minutes that used to take hours to create with macros and formulas and other things like that. So Power Query really has made it a lot easier to import data from virtually any source. Power Pivot was also an add-in to 2010. Power Pivot is sort of like a PivotTable on steroids, and it allows you to basically treat Excel like a relational database, so you can report on multiple tables in the one report. So that's Power Pivot. Also in 2010, we got introduced to the idea of a 32 bit versus a 64 bit Excel. Now that's to do with memory, and basically how much memory Ram that you can use.

Neale Blackwood CPA:
A 32 bit is limited two gigabytes of Ram. The 64 bit version basically doesn't have a limit. It's limited by how much Ram you actually have. If you are using large data sets, so I'm talking millions of rows, then the 64 bit version was the way to go. Now, obviously you can't have millions of rows within Excel, but Power Query and Power Pivot, both can handle external data sources that have millions of rows. There were other issues associated with the 32 bit versus 64 bit and that related to compatibility. Hopefully that's less of an issue now, but certainly when it came out, some people couldn't upgrade to the 64 bit due to legacy system compatibility issues.

Neale Blackwood CPA:
Okay. Another three years Excel, 2013, we're up to. Now, this introduced the idea of a subscription version. So office, you can subscribe, pay per month and basically get the latest updates. That's actually what I use. I use the subscription version and the pro version, whichever is the most expensive. So you sort of get everything. That was one of the problems in some of the subscriptions versions. You couldn't get Power Pivot for example. Again, that was back in 2013. But something they did add to Excel 2013 was the data model, which basically allowed you to create relationships and relate tables together.

Neale Blackwood CPA:
So you could report on multiple tables with a PivotTable. So the relationships is in... That's an icon in the data ribbon so check that out and said, the data model is all part of that. And then we got the timeline slicer, the slicers were really good, and they said, the graphic interfaces for filtering, but they didn't really work too well with dates since. So that's what the timeline slicer allows you to do. It works really well with months and years and quarters and things like that. So timeline slicers can work on PivotTables.

Neale Blackwood CPA:
I remember back in the day when I was doing a training session on Excel 2013, and I showed one of my clients, the timeline slicer, they decided to upgrade to Excel 2013, just for that ability. Also in Excel 2013, they tweaked the normal slicer so that it was available to work on a formatted table. So that was that formatted table that I mentioned back in 2007. You can actually use a normal slicer to filter those type of formatted tables. A good function that was added into Excel 2013 was the IFNA. So the good old #N/A error in Excel, typically for the look ups and matches that you'll see it.

Neale Blackwood CPA:
It was always difficult to actually handle the #N/A error a little bit differently than other errors. But now with the, IFNA and the, IFERROR functions, you can create shorter formulas that they're still reasonably long, but they're not as long as they used to be. So the IFNA function will handle the #N/A error, and then you can use the IFERROR to handle all of the other types of errors that Excel can throw at you. Okay. One of the other areas that people sort of spotted when Excel 2013 came out was that it was a little bit squarer.

Neale Blackwood CPA:
Okay? So there were straighter lines like on the ribbon and also even on the sheet tabs at the bottom. And there was actually method in their madness so, Excel 2013 looked squarer because they were looking at adding the ability to use touch screens. So, that's why don't quite get the angles that you used to get on a lot of the interfaces they're trying to make it a better for our fingers to be pressing on screens. And so having squarer and straight lines enables that. Okay, the current version, or as I speak the current version, Excel 2016 Power Query, which now has been re badged as Get and Transform is now part of Excel 2016. It's in the data ribbon.

Neale Blackwood CPA:
Again, as I mentioned earlier, that's really powerful and if you haven't already started using Power Query, I highly recommend you do. A lot of the basic data cleansing stuff is actually built into it. Things like trimming, leading and trailing spaces is just a button click, fixing dates is basically just a button click. So, so many things that would take a lot of effort in excel are now just button clicks on the ribbon. And also allows you just to extract from a whole lot of different data sources and just clicking refresh to do it.

Neale Blackwood CPA:
Okay. A couple of new charts were added in Excel 2016. We've had to wait many years to get new charts in Excel. And so two of the useful ones are all waterfall chart. Waterfall charts, hard to explain in a podcast, but basically they allow you to explain visually how you get from one figure to another figure and sort of broken down by categories. And so really common for showing how the actuals compared to budget or last year to this year. A waterfall chart, they're also called bridge charts. I did actually do an article on them last year, I think it was.

Neale Blackwood CPA:
So look it up on, INTHEBLACK website. So waterfall chart and another chart that was added was Pareto chart. So the Pareto chart is the good old 80/20 rule, where 80% of something is driven by 20% of something else. So the common example is 20% of your clients might account for 80% of your sales. Pareto analysis is really useful to sort of hone in on really important sections of your business. And so Pareto charts enable you to sort of look at that, to see it visually and see what the actual impact is. So obviously the 80/20 is an estimate.

Neale Blackwood CPA:
It actually goes back to actually agriculture, that's where it came from, but they found out that this 80/20 rule actually applies to a whole lot of stuff. So the Pareto charts now make it a lot easier to create or to visualise that Pareto principle. A couple of new functions now Excel 2016, it's in the subscription version, also is a little bit different to just the desktop version, if you've loaded it off a DVD for example. Because if you've got the subscription version, you can get extra stuff and there's a couple of functions or three functions actually that are available in the full subscription version that I don't think you get in the updated 2016 version.

Neale Blackwood CPA:
There is a TEXTJOIN function, which I covered at the end of last year's article, which allows you to actually refer to a range and join all of the texts within the range. It's got a sort of a sister function called CONCAT. The CONCAT function, which is short for CONCATENATE. I love that word CONCATENATE, which is actually an Excel function. The CONCAT function also works on a range, but it's a very basic one. So it just joins everything together with nothing between what you're joining. The TEXTJOIN allows you to specify a spacer or a delimiter that goes between each of the entries in a range.

Neale Blackwood CPA:
And lastly, there's another function called IFS, so I-F-S and it is a simpler version of IF statement, how can I put it? It doesn't have a false. So you basically just set up a whole lot of logical tests and you just tell Excel what to do if it's true, and you just keep ,,, and you just have a logical test, what do you do if it's true? And right at the end, you can actually specify something that is like the default, but basically the IFS function just simplifies looking at multiple logical tests. And lastly, again, if you're in the, the subscription version, you are able to set some defaults for your PivotTables.

Neale Blackwood CPA:
So that's with the latest update. It's not a lot. I was actually a little bit disappointed in some of the defaults that you can set, but some of the ones might be useful. You can check that out by clicking file, then options and on the left hand side, there's data, and there's an edit default layout button. And when you click that, you can at the options that you can change. Just keep checking back on this, because this may develop over time and you may get some more options here. So that's the Excel in case you missed it version of the podcast.

Neale Blackwood CPA:
So some of the options that have been added to Excel over the last 10, 11 years, and no doubt, we've got more coming when we get to this year's version. I want to finish with a topic that is fairly popular at the moment. It's regarding a package called Power BI. The BI stands for Business Intelligence. It's Microsoft's dash boarding product. Power BI works seamlessly with Excel. And I mentioned earlier about the Power Query and Power Pivot add-ins, while both of those are actually included in Power BI. So any skills you learn in Power Query and Power Pivot can be directly applied to Power BI. It's likely that Power BI will replace Excel as the go-to dashboard creation package.

Neale Blackwood CPA:
Power BI basically allows you to use a drag and drop interface to create reports and charts. It's very much like a PivotTable, but you can create virtually any type of chart now. When set up correctly, Power BI also has a much better sharing and security features than Excel. Also, Power BI is being upgraded constantly, so each month there's a new set of upgrades. You can even suggest improvements and if the suggestion gets enough votes, there's a voter system there that you can actually get it added to the development schedule.

Neale Blackwood CPA:
Power BI has a fully featured free desktop version, as well as a paid subscription version. I highly recommend that you have a look at Power BI. Now, early last year, I wrote a series of three posts on the INTHEBLACK website, they all include a video and it's sort of an introduction to Power BI, so you can sort of check that out. Now, I mentioned a few limitations back then, most of those have been fixed already between Power BI and Excel. I think you're going to be able to handle all of your reporting needs going forward. Thanks for listening and best of luck, checking out all of these new features in Excel.

Outro:
Thanks for listening to the CPA Australia podcast. For more information on today's episode, please visit the show notes at www.CPAaustralia.com.au/podcast. Never miss an episode by subscribing to our podcast on Apple Podcasts, Spotify, or Stitcher.