MrExcel.com - Your One Stop for Excel Tips & Solutions

Hide Subtotals From Chart in Excel 2013

July 13, 2023 - by

Hide Subtotals From Chart in Excel 2013

Problem: My data has subtotals that cause spikes in the chart.

The 12 months in this chart are interrupted by tall columns for Q1, Q2, Q3, and Q4.
Figure 1191. Charting data with subtotals does not look good.

Normally, you would create another range with the monthly data and create a chart from that range. But, Excel 2013 introduces the Funnel icon to the right of the chart. Click the Funnel and you can uncheck certain data points.

Use the Funnel icon to the right of the chart and uncheck Q1, Q2, Q3, and Q4.
Figure 1192. Remove each Quarter from the chart.

The result: a monthly chart:

Those points are hidden from the chart, leaving you 12 months.
Figure 1193. No more spikes.

Create Pivot Charts from Detail Data

Say that you have hundreds of rows of detail data. Select the entire data set and use Insert, Recommended Chart. Excel will recommend that you let it create pivot charts to summarize the data. You will see tiny Pivot icons on each chart tile.

The Recommended Charts panel is offering pivot charts that summarize the data. Tiny Pivot icons appear in the thumbnail for each of these charts.
Figure 1194. Excel offers pivot charts.

This article is an excerpt from Power Excel With MrExcel

Title photo by Ana Cruz on Unsplash