Excel Mini Episode: Format as Table

Intro:
Hello, and welcome to the CPA Australia podcast.

Neale Blackwood CPA:
Following on from some recent topics where we've been looking at data, I think it's worthwhile covering something that is an important feature to use in Excel, because it can actually help you improve your use of data. The feature is called format as table, and it's got a nice big button on the home ribbon. It's even got its own keyboard shortcut. You can use control. Hold the control key and press the letter T, so control T. And what that does is it tells Excel to treat your table like a little database, basically. And so with that, you get colour. So there's some colours that you can apply, but unfortunately people tend to think it's a formatting feature and it's not. It's actually a data feature. Now I did cover this in the August article from 2018. So it's worthwhile going and checking on in the black website, because there is a companion video on there and you can see how to use some of the features that the formatted table allows you to do.

Neale Blackwood CPA:
I thought I'd cover a couple of them here. One of the really useful advantages is that as you add data to the table, the table automatically expands to include that data. Now that works for when you add a row at the bottom, or if you add a column to the right. Now that's really useful if you've used the table as the source for a pivot table report, because in the old days one of the problems with pivot table reports was that as you've added new data, then the pivot table wouldn't expand to include that data when you refreshed it. But when the table is the source for the pivot table, then it does automatically expand when you hit the refresh button, so that's one of the big advantages. Also, if you create formulas in the formatted table, those formulas are automatically copied down when you create the formula. That also applies if you edit the formula, it's automatically copied down.They're two really big advantages with using format as table.

Neale Blackwood CPA:
Also, note some of the standard shortcuts work even better in a formatted table. For example, if you want to insert a row, you can just hold the control key and press the plus sign. Now if you've got the keypad on your full-sized keyboard, that's really easy. Otherwise, you need to go control shift and the equal sign which has the plus sign above it, but that will insert the row inside the table. It won't insert anything outside of the table. So control plus is usually inserting a whole row or a whole column, depending on what you're doing. But when you're inside the formatted table itself and you go control plus, it'll just insert a row within the table itself.

Neale Blackwood CPA:
Another useful shortcut is control and the space bar. What that does is that will select the whole column. And it's a really quick way to select the column. Just the column of data within the table itself. Also, control a just selects the data within the table. It doesn't include the headings, if that's what you want to copy.

Neale Blackwood CPA:
There is a little warning in terms of using the fill handle. Now the fill handle is little plus sign on the bottom right hand corner of a cell. Don't drag across if you are using what are called structured references. Now structured references come built into the formatted table. If you refer to a cell within a formatted table on the same row, then Excel will insert what's called a structured reference. It's basically using square brackets to refer to a column within the table. The trouble is if you copy across using these formatted table structured references, then you can get some unusual results, which don't necessarily make sense. It's okay to copy down using the fill handle, but not across. So just use copy paste. Don't use the fill handle to copy formulas across if you are using these structured references.

Neale Blackwood CPA:
Now the structured references actually help you learn to think a slightly different way in Excel. Now, when you are working with data in Excel, we tend to think of things in cells, because that's where we store the data. But that doesn't help us when we're trying to convert or try to learn something like Power BI, where Power BI thinks of things in columns and cells don't really exist in Power BI. And if you are trying to get your head around relational databases and also how reports should be structured, or data should be structured, you need to be thinking in columns. And that's something that the formatted tables can start to help you understand, because you can refer to the whole column within the formatted table using these structured references that I mentioned.

Neale Blackwood CPA:
Also, you need to give them descriptive names. Excel will give the name something like table one, table two. You can create the name itself. It's in the design tab. So when you're within the table, you can create a name in the design tab, far left hand side. I use a prefix. That's because I also use range names. So range names, table names, sort of a bit similar. So I use a prefix of TBL on my table names. Also, when you are using the table names in a formula, if you type TBL, all of the tables will be listed if you've used a prefix. As I said, which is what I do.

Neale Blackwood CPA:
Something that not a lot of people are aware of slices, which were added in Excel 2010. They typically work with pivot tables, but you can use a slicer on a formatted table from Excel 2013 onwards. Now a slicer is a filter interface and it's a lot easier to use than Excel's normal filtering. So have a look at that. When you are in the design tab, there is a ability to insert a slicer. So again, that's for filtering a formatted table.

Neale Blackwood CPA:
Formatted tables also work really well with range names. One of the common things you want to do for let's say a data validation, which is like a dropdown in a cell, is to have a list that automatically expands. You can do it with a range name and a reasonably complex formula, but you can also refer to the structured reference that I mentioned. And again, if you want to see some examples of that, you can check out the video that I did for the August 2018 article. And so you can use the range names with the structured references and that then gives you that flexibility of basically a dynamic range name. Also, the formula is a lot shorter if you use range names.

Neale Blackwood CPA:
So you can convert the structured reference that basically includes the table name and the column name, if you're referring to the column. So your formulas can get quite long when you are using these structured references. But if you convert the structured reference into a shorter range name, that can shorten your formulas. And I think I give an example of that in that April article.

Neale Blackwood CPA:
As I mentioned, using formatted tables is like the gateway drug into using some of these other Power products, like Power BI, Power Query and also Power Pivot. Relationships are also part of that. Relationships give you the ability in Excel to create a little relational databases. You need Excel 2013 and later to do this, but for relationships to work you have to use a formatted table.

Neale Blackwood CPA:
So relationships will only work with formatted tables. So there's another reason to use formatted tables. So within Excel, you can create a mini relational database to do reporting based on multiple tables. In the old days, you could only report on one table in a pivot table, but now with relationships and what's called the data model, you can actually report on multiple tables as long as there're relationships between the tables. And again, it gets you thinking about columns. To get the most out of the Power products, you've got to be thinking in columns rather than cells.

Neale Blackwood CPA:
One of the important concepts that you'll have to learn if you're going to take on Power BI is the idea of a filter context. And that's the idea of whatever filter is in place, is going to affect the data that's reported in a Power BI or a Power Pivot report. And the filter context is based on columns. So this is that idea of columns that you need to get your head around. And so when there's a filter in place on a column that can affect what's actually visible in the ports. So format as table gets you thinking in columns rather than cells. And again, that's going to help you to better understand how Power BI and Power Pivot work.

Neale Blackwood CPA:
I hope I've been able to convince you to have a look at format as table. It's not about the formatting. It's about the data features that come with format as table. Again, the shortcuts work really well with format as table. One thing that I use, I typically have a sheet that I dedicate to my tables in a file, and I might have all my tables next to each other. But what I will do is, I will put a blank column between the tables. So it's a good idea, because it allows you to select the tables really quickly, really easily just using either control a or control asterisks, depending on if you're selecting everything within the table.

Neale Blackwood CPA:
Control asterisks, by the way, hold the control hit the asterisks. I use the asterisks on the number pad. So just if you're using it in the middle of the keyboard, it's control shift and the number eight to do the asterisks. When you use that, that includes the headings. If you press control a, that will include the data only. Press it again and it will include the headings. So you can just use control a, just press it twice and that will give you the headings as well. If you press it a third time, by the way, it actually selects the whole sheet. So control a leads a triple life there.

Neale Blackwood CPA:
Okay. So as I said, format as table, exceptional feature in Excel. It came in at Excel 2007 and I highly recommend you start using it if you don't already. It also will work very well, very seamlessly with something that's coming called dynamic arrays.

Neale Blackwood CPA:
So dynamic arrays are arrays that do not require the old-fashioned control shift enter, but they still work like arrays and they auto expand as well. So formatted tables work really well with these dynamic arrays, which are something that are on the horizon. If you've got the latest subscription version and you've also said that you want all of the most up to date features, then you might have already seen some of these.

Neale Blackwood CPA:
One of the dynamic array functions is called unique. So it will extract a unique list out of a list basically and it will do it with a formula. In the old days that used to take a fairly complex formula to do that, an array formula. But now it's a very simple one. So if you start typing in unique equals unique and it comes up, then you have the latest version with the latest dynamic arrays. Lucky you, I don't have it yet.

Neale Blackwood CPA:
Hopefully this has been useful. So have a play with formatted tables. I said it helps you get your head around some of the concepts you'll need to understand to get the most out of Power BI and Power pivot. Thanks for listening.