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.
Add Data Table in Excel Chart: 4 Quick Methods
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.
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.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
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.
- How to Select Data for a Chart in Excel
- Selecting Data in Different Columns for an Excel Chart
- How to Expand Chart Data Range in Excel
- How to Add Data Points to an Existing Graph in Excel
- How to Create Excel Chart Using Data Range Based on Cell Value
- How to Get Data Points from a Graph in Excel
- Excel VBA: Get Source Data Range from a Chart