In this article, I will discuss the details of the data table in the Excel chart. Basically, in Excel, we show a data table in a chart so that we can analyze data conveniently. Besides, data tables can be very useful if the reader wants to see the exact source of data along with a graphical display. Usually, data tables are displayed beneath an Excel chart.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
4 Methods for Data Table in Excel Chart
1. Add Data Table From Chart Design Tab in Excel
We can add a data table in an excel chart using the ‘Chart Design’ tab from the Excel Ribbon. These methods are simple and quick. So, let’s see how to add a data table using the Chart Layout group of the Chart Design tab.
1.1. Show Data Table Using ‘Quick Layout’ Option
There are two options available in the Chart Layout group to add data tables in Excel. First, we will discuss the Quick Layout option.
- First, click on the chart and go to Chart Design > Quick Layout. Next, choose a default chart layout that includes a data table.
- As a result, you will get the chart containing the data table.
1.2. Use the ‘Add Chart Element’ Option to Show Data Tables
Alternatively, you can add a data table using the Add Chart Element Option. The steps involved are described below.
- Initially, click on anywhere in the chart area.
- Next, go to Chart Design > Add Chart Element > Data Table > With Legend Keys. You can choose the No Legend Keys option instead if you want.
- Consequently, you will see that a data table is added below the chart.
2. Show/Hide Data Table by Clicking the Plus (+) Sign of Excel Chart
You can show or hide data tables in charts just by clicking on the chart. In this method, we will use the plus (+) sign in the chart area to display the data tables.
- Click anywhere in the chart and you will see the plus (+) sign at the top right corner of the chart. Now, click on the plus sign and check the Data Table option from the Chart Elements. Eventually, you will see a data table displayed on the chart.
- Similarly, by unchecking the Data Table option, you can hide the source data from the chart.
- Create a Data Table in Excel (7 Ways)
- How to Make a Data Table in Excel (Easiest 5 Methods)
- Example of Excel Data Table (6 Criteria)
- How to Create a Two Variable Data Table in Excel
3. Add Extra Data Series to Data Table but Not in Chart
Sometimes, you may need to add an extra row in the data table row. In that case, the extra data series will be displayed in the chart too. However, if you want to show the extra series in the data table but not in the chart, follow the below steps.
- Firstly, select the source data and then go to Insert and choose a recommended charts option. I have chosen the 2-D Column.
- As a consequence, here you will get the below chart.
- Secondly, show data tables from the Chart Elements. Remember, choose the No Legend Keys option from the Data Table.
- Thirdly, right-click on the column which you do not want to show on the chart and choose the Format Data Series option.
- Next, the Format Data Series window will show up. Now, click go to Series Options: Fill & Line. Then, expand the Fill tab and click on the No fill option.
- Subsequently, the following will be the result. But, the extra legend key is still there. So, we have to remove it now.
- After that, go to Chart Design > Add Chart Element > Legend > None.
- Finally, we got an extra data series on the data table but the column for that data series is not shown in the chart.
4. Format Data Table in Excel Chart
Unfortunately, there is not much formatting available for data tables in excel. Still, you can format some features such as Fill, Border, Shadow, Glow, Soft Edges, 3-D Format, Data Table Borders, etc.
To format data tables in Excel, follow the below steps.
- First, click on a chart and go to Chart Design > Add Chart Element > Data Table > More Data Table Options.
- Next, the Format Data Table window will show up. Now, format the data table as you require.
In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.