Excel Tables
One of my favourite features that has been added in recent years, are the Excel Tables.
Excel Tables, really, are just a way of formatting your data to make it easier to work with and manage – but despite it having been around since Excel 2007, they don’t seem to have had a huge uptake, which is such a shame, as I find them incredibly useful.
Creating a Table is very easy. Simple select somewhere within your data range, and select Insert from the Ribbon, and select Table
It will then ask you to confirm the data range, and whether you have column titles or not. Click OK once you’re done
The first thing you’ll notice is that Excel now formats your data range for you. You can change it if you wish, but I rarely do so, as for me, this will usually remain hidden from the final piece.
What you probably will want to do straight away, is give you Table a name, as by default, it’ll be called ‘Table1‘ or similar.
To do this, select anywhere in your new table. You will see an extra Ribbon item named Table Tools ¦ Design. Select this, and on the left will be a small box where you can change your Table name.
Take care not to enter into the Cell Reference box (where is says E30). My data contains names & addresses, so I’ve names it AddressBook
For a fuller overview of Tables themselves, there is an article on Microsoft’s website covering this, which is worth a read through, as there are many useful features which I do not plan to cover in this post.
Instead, I’m now going to jump to the part I find the most useful.
Let us say we want to count the number of users who live in Birmingham. In the past, we would do something along the lines of 
, which involves swapping to the sheet you want to count, selecting the column / range you’re interested in etc.
If you’re doing this just once or twice, it’s not such a big deal. But if you’re working on something much bigger, perhaps with multiple data tabs and hundreds of formulas – this can become a cumbersome and time consuming process.
However, now we’ve converted our range to a Table, things become a litter easier and a little smarter…
You create the formulas in the same way, but rather than tabbing to the required sheet, we simply start typing the name of our table. As soon as you do, Excel will recognise this, and suggest the rest of what you’re typing…
Hitting [TAB] will complete the highlighted item.
Then, press they ‘[‘ key to indicate that you wish to select a given column, and again, it will list the available columns for you…
Like before, select [TAB] to select the highlighted item (or continue typing if you choose). End with a closing ‘]‘, and enter your criteria as normal.
What you end up with is something like this… 
So, you never left the sheets you’re working on, but can have high confidence that what you’ve asked for is what you’re getting – and when you’re repeating this dozens of times, you’ll notice just how much faster it is to work with. Plus, if you (or someone else) comes to it in the future, they’ll be able to get to grips with what you’re trying to achieve much quicker this way, as the formula ‘reads’ much better than the traditional method.
And that’s all I wanted to show you! There is, of course, far more to Tables than this, but I’ll let you explore these for yourself!
Leave a Reply