EXCELACE ALL RIGHTS RESERVED.
WEBSITE BY FARM FACTORy
As far as I was concerned, if a sheet had a series of columns with headings and information in the rows below (as per the pictured example), that was a table.
But what I think and what Excel thinks are two different things. As far as Excel is concerned, my example above is just a bunch of data in cells in rows and columns.
To Excel, this isn’t a table at all. What Excel deems to be a table comes with WAY more functionality and automation capability.
How can you tell what is and what isn’t a table? Here’s a quick way to check. If you’re positioned within a table you’ll see an extra field in your Ribbon that will say ‘Table Design’. If you don’t see that field, you don’t have a table.
With regular cells formatted like my example above, we’re in control of everything and we’ll need to set and apply filters throughout the data range manually.
This includes things like name ranges, filter ranges, and colour coding – everything is completely standalone and we have to perform actions manually.
This allows you to keep a closer eye on things (which the ‘Control Freak’ in me really loved!), but it can eat up a lot more time.
The alternative (which provides much quicker ways to update things) is to use an actual Table.
In this article, I’m going to walk you through how to create a Table in Excel and I’m also going to discuss the benefits of using Tables as opposed to manual data management.
To get started with creating a Table you’ll need to highlight all of the data that you want to be included.
BONUS TIP: To highlight all of your data quickly, click into any cell then press CTRL + A on your keyboard and this will select all the surrounding data to the cell you’re in.
Once you’ve highlighted your data head to the ‘Home’ Ribbon and select the ‘Format as Table’ button.
BONUS TIP: To transform your data into a table quickly, after highlighting it just press CTRL + T on your keyboard.
There are loads of table formatting options available to pick from including a variety of colours which is ACE. Anyone who knows me knows I love colour-coding my workbooks, so this is a big YES for me.
Once you’ve chosen your format, the ‘Create Table’ window will open. This window wants to know where your data is. As you’ve already highlighted your data before creating your table, your data range should already show in the window.
If however, you’re creating a table BEFORE selecting your data range, you’ll need to input where in the sheet you want Excel to pull the information from.
Do this by adding the data range into the box underneath the question ‘Where is the data for your table?’
In this same window, you’ll also see a tick box that says ‘My table has headers’. If this is ticked, Excel will presume that the very first row of the selected data range will be the one with the headers in (which should be right) and will pull this information from that location.
Once you’re happy that your data range is inputted correctly, click ‘OK’ – this will convert your data into a functional table.
Hey presto – you’ve created a Table!
Now that we have a Table, let’s take a look at some of the advantages of displaying our data in this way.
As mentioned before, when you click into your table you’ll see an extra field appears in the Ribbon called ‘Table Design’. (This option will vanish if you click into cells that are outside of the table itself.)
The first difference in working with a table is that our filters are automatically applied throughout (meaning we don’t have to go in and add these manually).
If you go to the ‘Formulas’ ribbon and then select ‘Name Manager’, you’ll see that the table has already been assigned a named range. It will automatically extend this named range as you extend the table (either with new rows or columns).
This will extend filers, colours, and formatting across for you into the new additions.
Another ACE thing about tables is that it eliminates the need to use Freeze Panes to keep our headers showing.
As you scroll down your table you’ll notice that the Column names change from A, B, C, etc. to the name in the top row of the table.
This makes it much easier to keep track of which column contains what information, especially when you have a long table to search through.
The alternative would mean having to select the specific row we wanted to freeze and manually instructing Excel to do this for us.
If you need to add additional data to your table, it will automatically extend and update formatting for you.
For example, if your chosen template has colour-coded rows in a pattern (i.e. Row 1 is a light shade and Row 2 is a dark shade), when you add a new row (whether at the end or in the middle) the table will automatically update the formatting to ensure all rows continue to follow the correct pattern.
It will also copy any data validation or picklist that you have into new rows added to the table – everything gets copied!
You might notice that when you type something in a cell directly adjacent to the table, Excel automatically incorporates this and turns it into a new column of the table for you.
This can be a little disconcerting if you’re not expecting this to happen.
Getting around this is easy. Just make sure you leave at least one blank column in between the table and the cell where you’ll be writing your notes.
One thing that might be confusing if you’re using tables for the first time is the way formulas are written.
In usual circumstances, if you want to do a simple subtraction formula you would write this as:
=B1-A1
This tells the formula to subtract the value of cell A1 from the value of cell B1. Simple, right?
If you are writing this same formula into a table, you’ll see that instead of saying A or B, the cell reference will appear as the column header.
In the example above, you will see the formula appears as =[@[CreditValue]]-[DebitValue]]. This is the same as having selected =F2-E2 and will have the same outcome.
The difference here is that instead of referencing the specific cells, the table already knows which row you’re inputting the formula into, so doesn’t need to include this information. Instead, it just needs to reference which column you’re pulling the data from in that row.
The cool thing about this is that once you hit enter on that formula in one cell, your table will automatically apply the same formula to every row in that entire column (and to any new rows you add) – which is a big time saver.
Any edits you make to this formula will also be copied across automatically to the other rows.
If you add a new column to your table, it will automatically assign a heading. You can change this at any time, but this is handy as some functions in Excel (such as PivotTables) require column headings to operate properly. You can see this in the image above, as the column has been automatically named: Column1.
Tables are an ACE tool for organising your data.
The big upshot of adopting tables in Excel is that everything that you’re used to controlling manually is done for you automatically.
EXCELACE ALL RIGHTS RESERVED.
WEBSITE BY FARM FACTORy
