If you want to format a data table in an Excel chart, you have come to the right place. Here, we will walk you through 4 easy methods to do the task effortlessly.
How to Format Data Table in Excel Chart: 4 Easy Methods
In the following data table, you can see the Month, Sales, and Profit columns. After that, using this data table, we will insert a Column chart.
- First of all, to insert a Column chart, we will select the entire dataset by selecting cells B4:D10.
- After that, we will go to the Insert tab.
- Moreover, from the Insert Column or Bar Chart group >> select 2D Clustered Column chart.
As a result, you can see the Column chart.
- Furthermore, we will click on the Chart Title to edit the title.
Therefore, you can see a Column chart with Chart Title.
After that, using the following Column chart, we will describe 4 easy and effective methods to format data table in Excel chart.
Here, we used Excel 365. You can use any available Excel version.
1. Using Number Format Feature to Format Data Table in Excel Chart
In this method, we will use the Number Format feature to format the data table in an Excel chart.
Here, in the following dataset, you can see that the Sales and Profit columns have General Number Format. After that, we will insert a Column chart and we will add a Data Table to the Column Chart. Furthermore, we will change the format of the source Dataset and it will change the format of the chart Data Table. Therefore, this method is helpful when we want to change the format of the chart Data Table by changing the format of the source Dataset.
Let’s go through the following steps to do the task.
Step-1: Inserting Column Chart and Adding Data Table
In this step, we will insert a Column chart, and then, we will add a Data Table to the Column chart.
- First of all, we will insert a Column chart by following the steps described earlier.
Therefore, you can see a Column chart.
Now, we will add a Data Table to the chart.
- Therefore, to add a Data Table, we will click on the Chart Elements feature.
- Then, we will mark the Data Table.
As a result, you can see a Column chart with a Data Table.
Here, you can easily notice that the Sales and Profit values are in General number format.
Step-2: Applying Currency Number Format to Chart Data Table
In this step, we will apply a Currency Number Format to the chart Data Table.
- In the beginning, we will select the values of the Sales and Profit columns by selecting cells C5:D10.
- Afterward, go to the Home tab >> click on Number Format.
- After that, select Currency.
Therefore, you can see the Sales and Profit column’s values are in Currency number format.
Along with that, you can see that the chart Data Table also has a Currency number format.
Therefore, you can format data table in an Excel chart.
Read More: How to Add Data Table in an Excel Chart
2. Use of Format Tab to Format Data Table in Excel Chart
In this method, we will use the Format tab to format data table in Excel chart.
Steps:
- First of all, we insert a Column chart by following the steps described earlier.
- After that, we add the chart Data Table by following step-1 of Method-1.
Hence, you can see a Column chart with the chart DataTable.
Next, we will format the chart Data Table so that it has no Horizontal outline.
- To do so, we will select the chart Data Table >> go to the Format tab.
- Furthermore, from the Current Selection group >> select Format Selection.
At this point, a Format Data Table dialog box will appear at the right end of the Excel sheet.
Here, in the Table Options, Data Table Options is presented.
In the Data Table Options, you will see all types of Table Borders are marked.
- After that, since we do not want a Horizontal border in the chart Data Table, we will unmark the Horizontal box.
As a result, you can see the chart Data Table has no Horizontal border.
Hence, we can format data table in Excel charts.
3. Using Chart Design Tab in Excel
In this method, we will use the Chart Design tab to format data table in Excel chart.
This method is helpful to add a chart Data Table by using the Chart Design tab, and to bring out the Format Data Table dialog box simultaneously. Then, we will customize the chart Data Table with a color.
Steps:
- First of all, we insert a Column chart by following the steps described earlier.
Hence, you can see a Column chart.
- After that, to add chart Data Table using the Chart Design tab, we will select the chart >> go to the Chart Design tab.
- Furthermore, from the Add Chart Element group >> select Data Table.
Then, select More Data Table Options.
Therefore, you can see a chart Data Table.
Along with that, you can see a Format Data Table dialog box at the right end of the Excel sheet.
- Next, to add color to the Data Table, we will click on Text Options.
- Then, click on Solid Fill >> click on the icon of the Color box to bring out several color options.
- At this point, we select color.
Here, we choose Blue color, you can choose any color according to your preference.
As a result, you can see the chart Data Table in Blue color.
Hence, you can format the chart Data Table.
4. Using Chart Elements Feature to Format Data Table in Excel Chart
In this method, we will use the Chart Elements feature to format data tables in the Excel chart.
This method is helpful to add a chart Data Table, and to bring out several options simultaneously to format a chart Data Table.
Steps:
- First of all, we insert a Column chart by following the steps described earlier.
Hence, you can see a Column chart.
After that, we want to add a chart Data Table with legends keys.
- Therefore, we will click on Chart Elements >> click on the rightward arrow of the Data Table box.
This will bring out 3 options.
- Then, we will select With Legend Keys.
As a result, you can see the chart Data Table with Legend keys.
Now, we want to add a chart Data Table without legend keys.
- Therefore, we will click on Chart Elements >> click on the rightward arrow of the Data Table box.
This will bring out 3 options.
- Then, we will select No Legend Keys.
Therefore, you can see the chart Data Table with no Legend Keys.
Hence, you can format data table in Excel chat.
How to Move a Data Table in Excel Chart
In the following article, we will describe how you can move data table in Excel chart.
- First of all, we insert a Column chart by following the steps described earlier.
- After that, we add the chart Data Table by following step-1 of Method-1.
Hence, you can see a Column chart with the chart DataTable.
Here, in the chart Data Table, you can see that the Sales is above the Profit.
Next, we will show you to change the order of the data table, and we will move Profit on the above of Sales.
- After that, we will click on the chart >> go to the Chart Design tab.
- Then, from the Data group >> we will select the Select Data option.
At this point, a Select Data Source dialog box will appear.
- Then, we will click on Profit >> select on the upward arrow marked with a red color box.
As a result, you can see Profit is now above Sales.
- Then, click OK.
Hence, you can see the Profit is now above Sales in the chart Data Table.
Therefore, you can move a chart Data Table.
Practice Section
You can download the above Excel file to practice the explained method.
Download Practice Workbook
You can download the Excel file and practice while you are reading this article.
Conclusion
Here, we tried to show you 4 easy methods to format data table in Excel chart. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.
Related Articles
- 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 to an Existing Chart 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