Graphed LogoGraphed
Graphed LogoGraphed
FeaturesUse CasesIntegrationsPricing

How to Change Number Format in Excel Chart Data Table

Cody Schneider••7 min read

Building a chart in Excel is the easy part, but getting every detail right - like the number formats in the data table below it - can often feel like a frustrating game of hide-and-seek. You’ve formatted your worksheet cells perfectly as currency or percentages, yet the chart's data table stubbornly shows a long string of unformatted numbers. This guide will walk you through exactly how to fix that and take control of your chart’s data presentation.

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

Why Don't Chart Data Tables Copy My Cell Formatting?

The number one source of confusion is that chart elements in Excel often act as separate objects from the worksheet data they reference. You can think of a chart as a snapshot or a linked image of your data. When you create the chart, it pulls in the values, but it has its own set of formatting rules. By default, it’s supposed to reflect the formatting of your source cells, but this link can sometimes be overlooked or reset.

When you add a data table to a chart, new formatting settings come into play. Excel can sometimes revert to a 'General' number format for the chart table, even if your source cells are meticulously set up. The key is knowing that to format the chart's data table, you almost always need to adjust the source cells on your worksheet because the table is designed to inherit its formatting from there.

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Changing Number Formats in an Excel Chart Data Table: The Step-by-Step Method

The most reliable way to format the numbers in your chart's data table is to format the original cells that the chart is pulling its data from. Here’s how to do it step by step.

1. Create Your Chart with a Data Table

First, make sure your chart is set up correctly. Let's use a simple example of monthly revenue data. Your raw data in the worksheet might look like this:

  • January: 25450.75
  • February: 19875.5
  • March: 31200

To create the chart and add the table:

  1. Select your data range, including headers (e.g., A1:B4).
  2. Go to the Insert tab on the Ribbon and choose a chart type, like a Clustered Column chart.
  3. With the new chart selected, click the green plus sign (“+”) next to it, which is the "Chart Elements" icon.
  4. Check the box for Data Table.

You'll now see a table underneath your chart with the raw, unformatted numbers. Our goal is to change these to look like this: $25,451, $19,876, and $31,200.

2. Go Back to Your Source Data

Here's the most important step. Do not try to right-click the numbers inside the chart's data table - that won't give you the number formatting options you need. Instead, navigate back to the original cells in your worksheet that contain the numbers (in our example, a range like B2:B4).

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

3. Apply Your Desired Number Format

Now, you'll use Excel's standard formatting tools on these source cells. The chart's data table will automatically update to reflect these changes.

  1. Highlight the numerical cells in your worksheet (e.g., B2:B4).
  2. Right-click the selected cells and choose Format Cells... from the context menu. You can also use the keyboard shortcut: Ctrl + 1 (or Cmd + 1 on Mac).
  3. The 'Format Cells' dialog box will appear. On the Number tab, select your desired category from the list.

Here are a few common examples of formats you might apply:

Formatting as Currency

To show a dollar sign and get rid of decimals:

  • Choose the Currency category.
  • Set Decimal places: to 0.
  • Choose your preferred Symbol (e.g., $).
  • Click OK.

The numbers in both your worksheet and the chart's data table will immediately update to display as $25,451, $19,876, and $31,200.

Formatting as a Percentage

If your data was something like 0.75 and you wanted to show it as 75%:

  • Select the Percentage category in the 'Format Cells' box.
  • Adjust the decimal places as needed.
  • Click OK.

Using Custom Formats

For more specific needs, you can use Custom formats. For instance, to display large numbers in thousands (K) or millions (M) to save space:

  • Select the Custom category.
  • In the Type: input box, enter a format code. For thousands with a 'K,' you can use: $#,##0,"K".
  • Our value of 25450.75 would now display as $25K in the data table, making it much cleaner.

The core principle is an "a-ha!" moment for many Excel users: Your chart data table's number format is directly controlled by the format of its source worksheet cells.

Troubleshooting: When Your Formatting Doesn't Update

What if you followed the steps above and nothing happened? Here are a few common issues and their solutions.

Issue 1: The "Link to source" Box is Unchecked

Sometimes, individual elements within a chart can have their formatting linked to the source data disabled. To check and fix this:

  1. In your chart, click on one of the data labels or values in your data table. You may need to click twice to select a specific series or element.
  2. Press Ctrl + 1 to open the 'Format' pane on the right-hand side of your screen.
  3. Scroll down to the Number section at the bottom and expand it if it's not already open.
  4. Look for a checkbox labeled Link to source. Make sure this box is checked.

If this box was unchecked, Excel allowed the chart element to have its own independent number format. Checking it re-establishes the connection to your worksheet cells, and the numbers should immediately adopt the source formatting.

Issue 2: Formatting a Data Table in a PivotChart

If your chart is based on a PivotTable (a 'PivotChart'), the process is slightly different. Formatting the source data directly won't work. Instead, you have to format the value field within the PivotTable itself.

  1. Find your PivotTable that the chart is based on.
  2. In the PivotTable Fields pane (usually on the right), find the field you are charting in the Values box.
  3. Click the dropdown arrow next to the field name and select Value Field Settings...
  4. In the new dialog box, click the Number Format button in the bottom-left corner.
  5. This will open the familiar 'Format Cells' dialog. Apply your desired formatting here (e.g., Currency with 0 decimal places) and click OK twice.

Your PivotChart, along with its data table, will instantly update with the new number formatting.

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Issue 3: Accidentally Using the "Format Data Table" Pane

If you right-click the outer edge of your chart's data table and select "Format Data Table," you'll open a formatting pane. It’s easy to get lost in here looking for number formatting options. However, this pane only controls the appearance of the table - things like its border color, fill options, and shadows. All number formatting must happen at the source data level, as described above.

Final Thoughts

Getting your chart data table numbers to display correctly in Excel all comes down to focusing on the right thing: the original source cells in your worksheet. Once you format those cells, the "Link to source" feature ensures your chart - data table and all - presents the data in a clear, professional, and readable way.

Of course, manually building and styling Excel reports week after week can quickly become a repetitive chore that pulls you away from actual analysis. For us, automating this process was essential, which is why we built Graphed. We connect directly to your data sources, like Google Analytics, Shopify, or Salesforce, and automatically generate real-time, professional dashboards. You just describe what you want to see in plain English and instantly get a live report, so you can focus on insights instead of fighting with formatting dialogs.

Related Articles