OmniDocChange Chart Data Range and Show Blank Cells in Excel
Fix a chart that reads the wrong cells or hides gaps in your data. Learn how to change the source range and control how empty cells appear.
Your Excel chart may look fine at first glance, then show the wrong months, skip a column you expected, or leave holes where you wanted a clean line. Two problems cause most of these headaches. One is that the chart reads a different range of cells than you think. The other is that blank cells appear in a way you did not intend. Both are fixed in the chart's data source, not by resizing or restyling the chart.
This guide is for anyone who builds charts from worksheet data and needs the chart to pick up the right cells. You will learn how to change the data range, decide how empty cells should look, and track down the common causes of missing or misleading points. You do not need to be an Excel expert to follow it.
The sections below start with a quick answer, then help you match your symptom to its cause. Each fix is written as a general method that works in any recent version of Excel.
How do you change the chart data range in Excel? The short answer
Select the chart, then edit each series so its values and category labels point to the correct cells, and set how empty cells are plotted in the chart's hidden and empty cell settings. Doing both fixes most range and gap problems.
| Your situation | What to do | Why |
|---|---|---|
| The chart is missing a row or column you added later | Extend each series range, or convert the source data to a table | The chart only reads the cells it was given, so new rows need to be in the range |
| The chart plots a header row as data | Remove the header from the values and keep it only as the series name | A text header in the values range can turn the series into zeros or shift the points |
| Empty cells show as zero and drop the line to the baseline | Change the empty cell setting to gaps or connected lines | Excel plots an empty cell as zero unless you tell it otherwise |
| Some data points are missing even though the cells have numbers | Check for numbers stored as text and for hidden rows or columns | Text values and hidden cells are skipped or treated as zero |
| You need one chart to follow a growing dataset | Build the chart from a formatted table and point it at the table | The chart expands automatically as rows are added to the table |
Figure out which problem you have
Before you change anything, match what you see to a likely cause. Many charts have more than one issue at once, so check each row.
| Symptom | Likely cause | How to confirm |
|---|---|---|
| A recent row or column is missing from the chart | The series range stops before the new data | Click the chart and look at the colored outline around the source cells, or open the series settings and read the values range |
| A series is drawn as a flat line at zero | Some cells contain text, or formulas return "" | Click a cell that looks blank and check whether the formula bar shows a formula or a space |
| Lines drop to zero where data is missing | Empty cells are set to plot as zero | Open the hidden and empty cell settings for the chart and check the selected option |
| A month or category is absent entirely | The row is hidden, or the category is outside the range | Look at the row numbers in the worksheet for gaps, then check whether hidden data is set to show |
| Numbers look right but the bars are tiny or missing | Values are stored as text | Select the cells and check whether the number is left-aligned or shows a small green marker in the corner |
Fix it step by step
Work through these steps in order. The first few fix the data itself, which is usually the root cause.
- Check that the source cells are numbers. Select the values in the sheet and look at the cell format and alignment. Numbers that are left-aligned, or that show a small warning marker, are often text. Convert them to numbers before you touch the chart.
- Confirm that each series points to the right cells. Click the chart, open the dialog that lists every series, and read the values and category ranges. If a range stops too early, drag the outline to include the new rows, or type the corrected range directly.
- Show data from hidden rows and columns if needed. Charts skip hidden cells by default. Open the hidden and empty cell settings and turn on the option to show data in hidden rows and columns if those rows should appear in the chart.
- Choose how empty cells should look. In the same settings, pick gaps to leave the space blank, zero to plot a drop to the baseline, or connected lines to bridge the missing point. Gaps usually suit monthly reports, and connected lines suit trends where a missing value should not break the visual.
- Replace formulas that return "" with a true gap. A formula that returns an empty string looks blank but is text to the chart, so it often plots as zero. In a line chart, you can return the error value NA() instead, which Excel treats as a missing point and skips. Use this only where a visible error in the cell is acceptable, or hide the cell's error with formatting.
- Turn the data into a table and point the chart at it. Select the data and convert it to a table. Then build the chart from the table. When you add rows to the table, the chart updates its range automatically, so you do not need to edit every series again.
Prevent it next time
A few habits keep charts reading the right cells as your data changes.
- Keep one header row and no blank rows inside the data block. A blank row in the middle of a dataset can split the range and leave out everything below it.
- Enter numbers as numbers. Paste from other sources with care, and watch for alignment that suggests text. Fixing this once prevents many charts from failing silently.
- Use a table for any data that will grow. Tables expand the chart range without extra work from you.
- Avoid formulas that return "" in charted columns. Decide whether a missing value should be a true blank, a gap marker, or a zero, and build the formula to match.
- Check the empty cell setting whenever you build a new chart type. The default behavior can differ between line, column, and scatter charts, so confirm it each time.
❓ FAQ
1. Why does my Excel chart ignore blank cells?
Excel only plots what is in the series range, and it treats empty cells according to the hidden and empty cell setting. If that setting is set to gaps, a blank cell is left empty, but if the cell contains a formula that returns "" or a space, the chart may read it as text and plot a zero. Check the cell's contents and the chart setting together.
2. Why does my chart show zero where a cell is blank?
The chart is probably set to plot empty cells as zero, or the cell contains a formula returning an empty string. Change the empty cell setting to gaps, and replace "" formulas with NA() in line charts if you need the point skipped.
3. Can I change the data range without rebuilding the chart?
Yes. Select the chart and edit the range for each series, or drag the colored outline around the source cells. Your formatting usually stays in place, so you only need to adjust the data source.
4. Why are some rows missing even though the cells are filled in?
Those rows are often hidden. By default, charts do not plot hidden rows or columns, so unhide them or turn on the option to show data in hidden rows and columns.
📝 Summary
A chart's data range and its treatment of blank cells are two separate settings, and both live in the source data and the chart's data options. Confirm the series ranges, check for numbers stored as text, and choose how empty cells should appear. Building the chart from a table will keep the range current as your data grows.
