Why you should Convert your Microsoft Excel Lists to Tables

Do you regularly work with Lists in Microsoft Excel? If so, you should consider converting them to Tables. At first glance they may seem to be much the same thing but when you dig a little deeper, you’ll find many advantages that Tables have over Lists.

This article covers the key concepts and features of Tables and explains why you should think about using them. Let’s get started.

What is an Excel Table?

A Microsoft Excel table is more than just a way to format data; it’s a dynamic structure that turns a simple range into a fully functional tool. Tables automatically format your data, add headers, and allow for easier manipulation through tools like sorting, filtering, and conditional formatting. Unlike a basic data range, an Excel table automatically expands as you add new rows or columns, keeping your data organized without the need for manual adjustments. Microsoft Excel tables also enable you to use structured references in formulas, making them more intuitive and flexible as you work with large data sets.

When you convert an existing list to a Table, or create a Table from scratch, you’ll see a Table Tools tab appear on the Ribbon to the right of your existing tabs. All the tools you need to work with your shiny new Table are located here.

How to Convert a List to a Table

Creating a Table is simple. Click anywhere within an existing List, then from the Insert Tab, click the Table button to the far left of the ribbon. You can select the list first if you want, but it’s not necessary, since Microsoft Excel will automatically detect the list range.

In the Create Table dialog box confirm the correct range has been selected, and if necessary, tick the box marked “My Table has headers”. You don’t want the headers to be sorted with the data! Click OK and your list will be converted to a Table.

After conversion, Microsoft Excel automatically applies a default table style. You can further customise it using the Table Design tab. Here, you can change the table style, enable banded rows, or add a Total Row for automatic calculations. By using a Table, you can enhance your data management in Excel, making it easier to analyse and edit your data.

TIP: Create a Table even faster by clicking into your list, then using the keyboard shortcut Ctrl + T. Also, check out our Excel Tips article to see some more shortcut goodness!

The Elements of a Table (See what I did there!)

After creating your Table, the first thing you should notice is the Table Tools ribbon. This is a contextual ribbon that automatically appears when you create the Table. At the far left of the ribbon, the Table name appears, usually something like Table2. It’s a good idea to change this to a more meaningful name, although you can leave this until later if you want.

The first row is now formatted as Headers, and Filter arrows are automatically added. You can also see some Table formatting has been applied to alternating rows. The formatting can be changed easily by selecting an option from the Table Styles gallery.

At the bottom right corner of the Table you can see a small blue handle. This can be used to quickly resize a Table if needed. Keep in mind that Tables will automatically resize if you add new rows of data.

Advantages of Using Tables

Microsoft Excel Tables provide several advantages over traditional lists.

Simple Formatting

To reformat the Table, just click anywhere in the Table, and select an option from the Table Styles Gallery. Hovering over any style displays a live preview. This saves lots of time when you want to add a bit of pizzaz to your data.

Always Visible Headers

If you work with lists, you know that the headings disappear as soon as you scroll down, and the usual solution is to Freeze Panes. This step isn’t necessary with Tables, because the column headings are automatically replaced with the Table Headers when scrolling. I often find when I’m teaching that I need to point this out because people aren’t used to seeing the usual column letters displayed as meaningful headings. We tend to fail to see the things that are right in front of us!

Automatic Filters

As mentioned, Filter Arrows are automatically added to the Header row when you create a Table. They work just like the filters you’re familiar with from using Lists. Click on a filter arrow to drop down a list of filter options. The specific choices will depend on the type of data in the column.

Tables Automatically Create Named Ranges

Tables use Named Ranges automatically when created. Named Ranges in turn provide simple navigation for large worksheets, and simplified referencing when applying formulas. For example, instead of referencing a range like (A2:G4564) when using a VLOOKUP you can simply refer to a range called something like MyTable. Because the range name behaves like an Absolute Reference you don’t need to worry about modifying the formula to ($A$2:$G$4564). Simple!

Add Totals in a Jiffy

If you want to see the Totals at the bottom of your Table, there’s no need to use AutoSum. Simply tick the Totals checkbox on the Table Tools contextual tab and a Total Row appears. By default, the last column in the Table will have a total applied (or count if it’s a text field).

Click in the Totals Row below any column, then use the drop-down list to select the function you want, such as Average. The calculations in the Total row will dynamically update when you apply a filter to your Table.

Pro tip: Although setting up totals is easy with a Table; you can’t use subtotals. This is because Tables are often used as the basis for Pivot Tables, and one of the first rules of Pivot Tables is that your source data cannot have subtotals. Or is the first rule, “we don’t talk about Pivot Tables!” Sorry, I couldn’t help myself. Anyway, if you want subtotals, don’t use a Table.

Tables Automatically Expand when Data is Added

Another useful feature is that whenever you add a new row of data to the bottom, the Table is dynamically updated, so the new row automatically becomes part of the Table. The same thing also happens if you copy and paste data to append your Table.

Create Calculated Columns with Ease

You can quickly add a new column to your Table by typing a heading (such as GST) and pressing Enter. The new column will automatically be formatted to match the rest of the Table.

Now you can enter a formula, and as soon as you press Enter the formula will be copied down the entire column.

Note: Table formulas use Structured References, so they may look a little weird until you get used to them. Once you do however, they are simple to use and save time since you only need to know the name of the table and the heading, no more looking for cell references.

Example =SUM(MyTable[Units Sold]) is much clearer and easier to read than =SUM(Sheet3!D2:D10), and it makes much more sense to anyone looking at the data.

Create Pivot Tables

If you need to do further analysis on the Table data, just go to the Table Tools tab and click “Summarize with PivotTable”. Click Ok to accept the defaults and a new sheet is created ready for you to begin building your PivotTable. Because the Table is dynamic, your PivotTable will update easily.

Create Dynamic Charts

Yep! If you create a Pivot Chart based on your Table, any new data you add to the Table will be reflected in the Chart. Just go to the Pivot Chart and hit the Refresh button and you’re done. This is a bonus if you like to use Microsoft Excel to build dashboards.

Add Slicers

If you really want to jazz up your Tables and impress your colleagues, you can quickly add one or more Slicers. A Slicer is a visual filter that makes filtering faster and, you guessed it, visual! Just go to the Table Tools tab and select Insert Slicer. Choose the field you want to filter and click OK.

From the Slicer, just click the item to be filtered and the Table updates immediately. To filter by more than one item, click the first, then hold the Ctrl key and click the next item. Clear the filter by clicking the clear button at the top right of the Slicer. This is exactly the way they work on Pivot Tables, so the learning curve is flattened!

As if all this isn’t enough, you can even export your table to SharePoint! That way, your team can directly interact with your shiny new table.

Conclusion

I hope this gives you some insights into Microsoft Excel Tables and encourages you to give them a try. Tables can save you time, deliver consistent formatting and provide you with quick insights into your data. I use Tables all the time in my own spreadsheets, and I reckon they’re an incredibly useful addition to my bag of Microsoft Excel tools. And before you go, pick up a few more tips from our article on Advanced Excel Formulas.