Charts That Don't Ghost Your New Data

Keep your charts and your data on speaking terms.

You added November's numbers to the spreadsheet, updated the report, and sent it off to your manager. A few minutes later, the reply arrives: "Why does the chart stop at October?"

Your new data got ghosted. It was right there in the worksheet, but the chart never acknowledged it.

This happens because a chart has a relationship with a specific range of cells. In the practice workbook, for example, the chart was created from cells A16:D26, so that's exactly what it plots: A16:D26, and nothing more. Add a row below, and the chart doesn't know it exists. The same relationship can also make data disappear from a chart when rows are hidden, which surprises a lot of people.

The good news: a few simple habits keep your charts and your data on speaking terms.

1. Turn Your Data into an Excel Table Before You Chart It

An Excel Table knows how big it is. When you add a row directly below it, the table expands to include it, and any chart built from the table expands too.

1. Click any cell in your data.
2. Press Ctrl T. Confirm the range and make sure My table has headers is checked, then click OK.
3. With a cell in the table still selected, go to the Insert tab and choose a chart, such as a column or line chart.
4. To test it, type a new month and value in the first empty row directly below the table. The table grows, and the new data appears in the chart right away.

Already have a chart? Convert the data to a table as described above, then add a test row. If the chart doesn't pick it up, right-click the chart, choose Select Data, and re-select the table's data in the Chart data range box.

Outcome: Your monthly and weekly charts update themselves as you add data, so reports are always complete and you never have to rebuild a chart just to add a row.

2. Check the Relationship with Select Data

When a chart looks wrong, the first thing to check is exactly which cells it's connected to.

1. Right-click the chart and choose Select Data.
2. The Chart data range box at the top shows the cells the chart uses. Excel also outlines those cells on the worksheet.
3. Under Legend Entries (Series), select a series and click Edit to see or change its name and values.
4. Under Horizontal (Category) Axis Labels, click Edit to see or change the labels, such as your months.
5. Click OK when you're done.

Outcome: You know exactly what your chart is showing, so you can fix a mismatched series or a missing month in seconds instead of guessing.

3. Don't Let Hidden Rows Vanish from Your Chart

Here's the surprise that catches many people: by default, Excel charts don't show data in hidden rows or columns. Hide a few rows to tidy up your worksheet, and those values quietly disappear from the chart too.

1. Click the chart. On the Chart Design tab, click Select Data.
2. In the lower-left corner, click Hidden and Empty Cells.
3. Check Show data in hidden rows and columns.
4. While you're here, look at Show empty cells as. Choose Gaps to leave a break, Zero to plot blanks as zero, or Connect data points with line to bridge the gap in a line chart.
5. Good to know: the difference is easiest to see in a line chart. In a column chart, Gaps and Zero look the same: the bar is simply missing.
6. Click OK, then OK again.

Outcome: Your chart shows the complete picture, even when you've hidden rows to make the worksheet easier to read.

4. Turn the Surprise into a Feature with a Slicer

Sometimes hiding data from the chart is exactly what you want. Because filtered rows are hidden rows, filtering a table also filters any chart built from it. Add a slicer, and you have a simple interactive chart with a click-to-filter button panel.

1. Make sure Show data in hidden rows and columns is unchecked for this chart (see tip 3).
2. Click a cell in the table. On the Table Design tab, click Insert Slicer.
3. Check a field to filter by, such as Region or Product, and click OK.
4. Click a button on the slicer. The table filters, and the chart updates to match.
5. Click the Clear Filter icon at the top-right of the slicer to show everything again.

Outcome: Managers can explore the data themselves with a click, and you avoid building a separate chart for every region or product.

Keep Your Charts Connected

A chart is only as good as its relationship with its data. Build it from a table, check the connection when something looks off, and decide on purpose whether hidden data should appear. Do that, and your charts will never ghost your new numbers again.

Want reports that update themselves every month? Reach out to us and let us know how we can help you.