5 Steps to Improve Your Excel Data Tables

Introduction

You can improve your excel tables and increase their data usability with the built-in table functionality and formatting. This tutorial and sample Excel workbook, available on GitHub, will move you from raw table to interactive analysis in less than 5 minutes.

The table pictured below, left, is the normal starting point for most analyses. You can format a table manually, right table; however, if you define the range as a table, you can benefit from a number of built-in features.

1. Create The Table

Place your cursor in the area you want to make a table. On the menu bar, select Insert, then Table.  Excel will take a guess at the range to create the table.

You will then validate the area that Excel has determined is the table you wish to create. Your table should have headers, and the checkbox will default to accept the first row of the range to be the table headers.

Once you hit OK, the range will format as a table with the default formatting. Select Table Tools that will now be visible on the menu bar, which will display more formatting options.

The Design menu bar has many visual and operational options to choose from, some of which we will cover below.

2. Name the Table to Allow for Easier References

As shown in the screen capture above, the default name for the first table is “Table1”. Naming the table something meaningful allows you to reference the table in calculations and other functionality. The reference, rather being Table1 would be YearlySalary which allows for built-in documentation, making your calculations more meaningful.

To change the table name, enter a new value in the Table Name box under the Table Tools option on the toolbar, as pictured below

3. Format the Columns

As you use the tables in other Excel features, such as pivot tables and graphs, the column format will be picked up in the other tools. For example, formatting the columns as Currency and then no decimals will cause the formats to be used in Graphs, as shown in the last section below.

4. Insert Slicers

Under the table tools, there is a Slicer option. Clicking on this option allows you to use various columns in the table as filters which allows you to slice the data for different views. With a cell selected in the table, select the Insert Slicer toolbar item in the Table Tools menu bar. You can then select the slicers you want to add.  The final view is below with the Insert Slicer dialogue.

5. Insert a Chart

Now that you have filters, you can easily add a chart. Selecting the table, you can insert a graph by selecting the Insert menu option, then select a chart which chart you want to view by selecting that option on the toolbar.  The graph is then filtered based on your slicer selection.

Note: The graph pictured has the axis formatted with the Year value removed.  There are a number of formats available in the graph format menu option.

Next Steps

There are many options available to customize your visualization. The purpose here was to show you that in less than 5 minutes, with five simple menu options, how to format and create a dynamic filtered graph and table from your raw data.  The sample workbook is available on Github.

Steve