You can display your data analysis reports in several ways in Excel. However, if you know the right data visualization technique your data analysis results can be more notable, and 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 guess 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 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 several formats available in the graph format menu option.