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. Show
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)
Click anywhere inside the chart to select it. Now you can see the Chart Tools group in the ribbon.
Under Design Tab -> Select Data
Inside the Select Data Source, click on “Hidden and Empty Cells”
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.
Filling Gaps using “Connect data points with line” option.
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 VideoThis 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 ChartHere’s how to fix a chart, so it isn’t affected if some data is hidden.
After you change that chart setting, you can hide rows or columns in the worksheet data, and the chart data will stay visible Test the Hidden Data SettingTo 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 DataJust 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.
Fix Sparklines to Show Hidden DataTo show the hidden data in your sparklines, follow these steps:
Get Excel Workbooks and More InfoTo 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.
|