5 Steps to Improve Your Data Visualization In Excel

You can display your data analysis reports in a number of ways in Excel. However, if you know the right data visualization technique your data analysis results can be more notable, your audience can quickly grasp what you want to project in the data. It also leaves a good impact on your presentation style. You can improve your data visualization productivity by using the built-in table functionality available in Excel.

1. Create The Table

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

5 Steps to Improve Your Data Visualization In Excel

You will then validate the area that Excel has determines 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.

5 Steps to Improve Your Data Visualization In Excel

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.

5 Steps to Improve Your Data Visualization In Excel

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.

5 Steps to Improve Your Data Visualization In Excel

Now we are on Patreon: https://www.patreon.com/pyoflife

Spread the love

1 thought on “5 Steps to Improve Your Data Visualization In Excel”

Leave a Comment