You can add a chart in Excel, based on worksheet data, but if you filter the data, and rows are hidden, that data also disappears from the chart. See how to prevent that problem from happening.

您可以基于工作表数据在Excel中添加图表,但是如果您过滤数据并且行被隐藏,则该数据也会从图表中消失。 了解如何防止该问题的发生。

销售图 (Sales Chart)

In this example, there is a line chart based on worksheet data, from cells A1:C9.


过滤图表数据 (Filter the Chart Data)

The chart looks fine, but if you filter the data, some of the worksheet rows are hidden, and that data also disappears from the chart.


In some cases, you might like that feature, and not mind that the chart changes.


In the example shown below, only the West region's data is visible on the sheet and in the chart.


所有数据消失 (All the Data Disappears)

However, there are some situations when you want the chart to show the data, even if the data is hidden. In the next example, columns A:C are hidden, so that they don't distract from the chart.

但是,在某些情况下,即使隐藏了数据,也希望图表显示数据。 在下一个示例中,A:C列被隐藏,因此它们不会分散图表的注意力。

Oops! Now there's nothing in the chart. That's not the effect that you were going for.

糟糕! 现在图表中没有任何内容。 那不是您想要的效果。

显示隐藏行中的数据 (Show Data From Hidden Rows)

If you want your chart to show all the data, even if some of the source data is hidden, you can change one of the chart settings.


The Excel 2010 instructions are shown below, and you can click here for instructions to show hidden data in Excel 2003 charts.

Excel 2010的说明如下所示,您可以单击此处以获取在Excel 2003图表中显示隐藏数据的说明。

要在Excel 2010中更改设置,请执行以下操作: (To change the setting in Excel 2010:)
  • Click on the chart to select it, and on the Excel Ribbon, under Chart Tools, click the Design tab


  • Click the Select Data command


  • 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


Now, you can hide rows or columns, and the chart data will remain visible.


测试隐藏数据功能 (Test the Hidden Data Feature)

To see the difference this option setting makes, test the feature in this embedded Excel file. Filter the Region, and one chart continues to show all the data, but the other chart has hidden data.

若要查看此选项设置的不同,请测试此嵌入式Excel文件中的功能。 过滤区域,一个图表继续显示所有数据,但另一图表具有隐藏数据。


观看图表视频中的显示隐藏数据 (Watch the Show Hidden Data in Chart Video)

To see the steps for changing the chart settings in Excel 2010, to show data from hidden rows in Excel chart, watch this short video.

若要查看在Excel 2010中更改图表设置的步骤,以显示Excel图表中隐藏行中的数据,请观看此简短视频。


翻译自: https://contexturesblog.com/archives/2012/05/10/show-data-from-hidden-rows-in-excel-chart/