Why is Excel chart not showing all data?

Ever come across a situation when you have some blanks in data for which you need to create a chart for? Or have any hidden rows or columns in the data? We know that it is very logical how excel create a chart in this situation but sometimes we don’t want to present it in that way. So just to get rid of this we can use one of the below smartest options.

So let’s start guy’s, Just have a look at the data and the chart we are going to use to understand this.  (You can also download the exercise file by following the link at the bottom of this post)

 

Why is Excel chart not showing all data?

 

Why is Excel chart not showing all data?

 

Click anywhere inside the chart to select it. Now you can see the Chart Tools group in the ribbon.

 

Why is Excel chart not showing all data?

 

Under Design Tab -> Select Data

 

Why is Excel chart not showing all data?

 

Inside the Select Data Source, click on “Hidden and Empty Cells”

 

Why is Excel chart not showing all data?

 

Now in “Hidden and Empty Cell Settings”, you can see here that “Gaps” are selected by default.  Here we can either choose “Zero” or “Connect Data points with Line”. Just look at the below screen shots for both options.

Filling Gaps using “Zero” option.

 

Why is Excel chart not showing all data?

 

Filling Gaps using “Connect data points with line” option.

 

Why is Excel chart not showing all data?

 

It entirely depends on how you want to present the chart, accordingly you can use “Zero” or “Connect Data points with Line”.

If you create a chart from worksheet data, and then filter the data, some of the chart date might disappear. Sometimes, that’s what you want — filter by Region, and see only the East’s sales in the chart. But, if you always want to see all the data in the chart, try this easy fix.

Note: There’s a similar problem with Excel’s in-cell Sparkline charts, and you can fix that too!

Watch the Show Hidden Data in Chart Video

This video shows how to change an Excel chart’s settings, so all the data will appear in the chart, even if some of the data rows or data columns are hidden.

There are written steps below the video.

Show Hidden Data in Excel Chart

Here’s how to fix a chart, so it isn’t affected if some data is hidden.

  • Select the chart, and on the Excel Ribbon, click the Design tab
  • Click Select Data
  • Click the Hidden and Empty Cells button
  • Add a check mark to ‘Show data in hidden rows and columns’
  • Click OK, twice, to close the dialog boxes

After you change that chart setting, you can hide rows or columns in the worksheet data, and the chart data will stay visible

Why is Excel chart not showing all data?

Test the Hidden Data Setting

To see the difference this option setting makes, test the feature in this embedded Excel file.

If you filter the Region column, one chart continues to show all the data, but the other chart has hidden data

Note: This embedded workbook might not work correctly in some browsers

___________________

Sparklines Problem for  Hidden Data

Just like the normal charts in Excel, the in-cell Sparkline charts have a default setting that prevents hidden data from showing. Sparklines are available in Excel 2010 and later.

In this screen shot, column N is hidden on the worksheet. That column has the “Month 2” data for expenses.

  • In column C, the Expense sparklines only have 2 data points.
  • None of the Revenue data is hidden
  • The Revenue sparklines, in column D, have 3 data points

Why is Excel chart not showing all data?

Fix Sparklines to Show Hidden Data

To show the hidden data in your sparklines, follow these steps:

  • Select the sparkline cell, or one group of sparkline cells
    • NOTE: You can’t change multiple sparkline groups at the same time
  • On the Excel Ribbon, click the Sparkline tab
  • Click the Edit Data command
  • Next, click the Hidden & Empty Cells command
  • Add a check mark to the Show Data in Hidden Rows and Columns setting
  • Click OK, to apply that setting.

Get Excel Workbooks and More Info

To get the sample workbooks for charts and sparklines, go to the Hidden Chart Data page on my Contextures website.

That page has more details showing hidden data, and there are macros there too, if you have to change the hidden data settings for lots of charts or sparklines.

Both of the sample files are zipped, and in xlsm format. The files contain the macros from the Hidden Chart Data page, so be sure to enable macros, if you want to test the code.

In the Hidden Chart Data workbook, there are worksheet buttons set up, so it’s easy for you to test the macros.

How do I make Excel graph show more data?

On the Chart Design tab, click Select Data. The Select Data Source dialog box appears on the worksheet that has the source data of the chart. Leaving the dialog box open, click in the worksheet, and then select all data you want to use for the chart, including the new data series.

How do you adjust the chart so it shows data?

Right-click your chart, and then choose Select Data. In the Legend Entries (Series) box, click the series you want to change. Click Edit, make your changes, and click OK. Changes you make may break links to the source data on the worksheet.

How do you show all data in Excel?

Depending on your operating system, you can use the keyboard shortcuts Ctrl + A or Command + A to select the entire document. Then you can press Ctrl + Shift + 9 to unhide all rows on your spreadsheet.