How To Create Tables in Excel: 5 Simple Steps
This tutorial and sample workbook demonstrate how to enhance Excel tables and improve data usability within 5 minutes. Users learn to convert AI-created CSV files into tables, name tables for easier reference, format columns, insert slicers, and create dynamic filtered graphs. The tutorial includes options for customizing visualizations and provides a sample workbook on GitHub for reference.
With built-in table functionality and formatting, you can improve your Excel tables and increase their data usability. This how-to create tables in Excel 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 usual starting point for most analyses. You can format a table manually, such as the right table pictured below; however, if you define the range as a table, you can benefit from many built-in features.
The starting point and manual table.
Step 1. Create The Table
We will review the following step-by-step instructions on how to convert an Excel range to a table.
Place your cursor in the area where you want to create a table. On the menu bar, select Insert, then Table. Excel will create the table by guessing or estimating the range.
How to convert an Excel range to a table
You will then validate the area that Excel has determined is the range of the table you wish to create. Your table should have headers, and the checkbox will not default to accepting the first row of the range as the table headers.
Excel Step By Step to convert a range to a table.
Once you hit OK, the range will be formatted as a table using the default formatting. Select Table Tools, which will now be visible on the menu bar and display more formatting options.
The Design menu bar offers many visual and operational options, some of which we will cover below.
The range will be formatted as a table with the default formatting
Step 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 it in calculations and other functionality. Rather than Table 1, we will change the reference to be more descriptive, 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
The Design menu bar has many visual options, including naming the table to something more descriptive.
Step 3. Format the Columns
The column format will be picked up as you use the tables in other Excel features, such as pivot tables and graphs. 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.
Some other tools can also use this formatting when a table is used as a data source. Always try to format the data as close to the source as possible. This will save you and your team effort later.
Step 4. Insert Slicers
Under the table tools, there is a Graphical Slicer option. Clicking on this option allows you to use various columns in the table as filters, slicing 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.
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.
Step 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.
Now that you have filters, you can easily add a chart.
Note: The graph pictured has the axis formatted with the Year value removed. The graph format menu option offers several formats.
There are many formats available in the graph format menu option.
Next Steps
There are many options available to customize your visualization. This tutorial's purpose is to show you how to format and create a dynamic filtered graph and table from your raw data in less than five minutes with five simple menu options. An example of a use case is the Dax Conditional Formatting functionality in Power BI. The sample workbook is available on GitHub.
