Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Format Data Table in Excel Chart (4 Easy Methods)

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.


Download Practice Workbook

You can download the Excel file and practice while you are reading this article.


4 Methods to Format Data Table in Excel Chart

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.

Data Table to Format Data Table in Excel 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.

Inserting Column Chart to Format Data Table in Excel Chart


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.

Using Number Format Feature to Format Data Table in Excel 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.

Adding Data Table to Excel Chart to Format 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.

Applying Currency Number Format to Format Data Table in Excel Chart

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 (4 Quick Methods)


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.

Use of Format Tab toFormat Data Table in Excel Chart

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.

Read More: How to Make a Data Table in Excel (Easiest 5 Methods)


Similar Readings


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.

Formatting Data Table in Excel Chart hy Using Chart Design Tab

  • 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.

Read More: How to Change Chart Data Range in Excel (5 Quick Methods)


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.

Use of Chart Elemets Feature to Format Data Table in Excel Chart

As a result, you can see the chart Data Table with Legend keys.

Formatting Data Table in Excel Chart Using Legends

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.

Read More: Example of Excel Data Table (6 Criteria)


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.

Read More: How to Create a Data Table in Excel (7 Ways)


Practice Section

You can download the above Excel file to practice the explained method.


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. Please visit our website Exceldemy to explore more.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo