OmniDocHide Zero Values in Excel Charts
Learn how to make zeros disappear from an Excel chart without deleting data, using #N/A helper formulas, number formats and hidden rows.
When an Excel chart shows a flat line at zero, a row of empty bars, or a row of "0" labels, the chart is accurate but cluttered. Often the zeros are placeholders for months with no activity, products that are not sold yet, or formulas that return 0 when the input is blank. You want the chart to skip those points without losing the numbers behind it. This is common when a report is shared with people who only need to see the trend.
This guide shows how to make an Excel chart not show 0 values, and why that is a different job from fixing blank cells or missing ranges. You will learn three display methods: a helper column that returns #N/A, a custom number format for labels and axes, and hidden rows. You will also learn how to check each result and how to get a table out of a PDF and into Excel if your source data starts there.
How do I make an Excel chart not show 0? The short answer
Use a helper column that returns #N/A where the value is zero, then point the chart at that column. Excel does not plot #N/A points, so the line breaks or the bar disappears. If you only need the zero labels gone, a custom number format is quicker.
| Your situation | What to do | Why |
|---|---|---|
| Zeros come from formulas that return 0 | Build a helper column that returns #N/A when the value is 0, and chart that column | #N/A points are skipped in line charts and leave no bar in column charts |
| You only want zero labels hidden | Apply a number format with an empty third section to the data labels or axis | The bar stays at zero height, but the "0" text is not shown |
| Zero rows are placeholders you do not need | Hide those rows and set the chart to skip hidden data | The chart drops hidden rows from the plot |
| The table came from a PDF | Export the PDF table to Excel with the PDF editor, then apply one of the methods above | You get editable cells instead of text you cannot chart |
Before you start
Check these points first. Each one prevents a fix that looks right on screen but misleads readers.
- Decide whether each zero is real. A zero that means "no sales this month" carries information. Hiding it can make a weak month look like missing data, so keep real zeros and hide only placeholders.
- Find out where the zeros come from. Click a cell and read the formula bar. A typed 0 needs a different fix from a formula that returns 0 for blank inputs.
- Note your chart type. Line and column charts respond to #N/A in different ways, and pie charts show a zero slice as nothing at all. Pie charts usually need the zero categories removed from the source range.
- Save a copy of the workbook. The helper column and format changes are easy to reverse, but a copy protects the original formulas if something goes wrong.
- Run OCR if the table is scanned. If the PDF is an image of a table rather than text, the PDF editor can OCR scanned PDFs first. Otherwise the values will not come across as cells.
Replacing zeros with #N/A, step by step
Use this method when the zeros come from formulas or when you want the chart line to skip the points. It works because Excel treats #N/A as "no value" and does not plot it.
- Identify the source range. Click the chart, then note which cells it references, such as B2:B13 for values and A2:A13 for categories.
- Add a helper column. Put it beside the source data, for example in column D, so the original values stay untouched.
- Enter the formula. In the first helper cell, type a formula such as =IF(B2=0,NA(),B2), adjusting the cell references to match your data.
- Fill the formula down. Copy it to every row in the range so each helper cell matches its source row.
- Point the chart at the helper column. Edit the series so its values come from the helper range and its categories still come from column A.
- Hide the #N/A text if it distracts. Cells showing #N/A are expected. You can use a font color close to the background or place the helper column on a separate sheet.
Avoid returning an empty text string ("") instead of NA(). A line or column chart treats text as zero, so the zeros return to the chart.
Hiding zero labels with a number format
Use this method when the bars or line points are correct, but the "0" labels or axis ticks clutter the chart. A number format controls how a number is displayed without changing its value.
- Select the data labels or axis. Click the labels you want to change, or the axis tick labels, in the chart.
- Open the number format settings. Choose the format option for the selected element, then pick a custom format.
- Enter a format with an empty zero section. A format such as #,##0;-#,##0; has three sections separated by semicolons. The third section applies to zero, and leaving it empty hides the value.
- Confirm the link to source. If the format does not change, the labels may still be linked to the source cells. Set the format on the labels directly, not on the source range.
The zero-height bars or points remain. This method hides text, not the category or the data point.
Hiding rows with no data
Use this method when the zero rows are placeholders that do not belong in the chart at all, such as months not yet reported.
- Hide the unwanted rows. Select the rows in the source table and hide them so they are not visible on the worksheet.
- Set the chart to skip hidden data. In the chart's data settings, choose the option that excludes hidden rows and columns from the chart.
- Check the category labels. Confirm that the remaining categories are still in the right order and that no unrelated rows were hidden.
Hiding rows removes data from the chart, not from the worksheet. If someone unhides the rows later, the zeros return, so mention the hidden rows in a note.
Getting the table out of a PDF first
If the chart data lives in a PDF table, you cannot edit those values directly in a chart. Use the desktop client to export the table to Excel, then apply one of the methods above.
- Download the desktop client. Get it from download.omnidoc.top, which supports Windows, macOS and Linux.
- Open the PDF in the PDF editor. Choose the file that contains the table, and run OCR first if the table is scanned.
- Export to Excel. Use the export option to save the content as an Excel file, then open that file to check the values.
OmniDoc's PDF editor exports to Excel, but it does not change chart display settings. Make the zero-hiding changes in your spreadsheet program after the export.
Check the result
Once the chart looks right, check it against the source table. A chart that looks clean can still hide a real problem.
| Check | How to see it | If it's wrong |
|---|---|---|
| Placeholder zeros are gone | Look at the line or bars where zeros used to be | If the line drops to zero, you may have returned "" instead of NA() |
| Real zeros still appear | Compare the chart with the source table, row by row | Remove the helper formula from those rows and rebuild the series |
| Axis labels show no stray zero | Check the axis tick labels near the baseline | Apply the number format to the axis, not only to the data labels |
| Data labels are clean | Check each label on the chart | Reapply the empty-zero format to the labels directly |
| Hidden rows match your intent | Unhide, check, then hide again if needed | Confirm the chart setting for hidden data and recheck the category range |
❓ FAQ
1. Does hiding zeros in a chart delete the values from my worksheet?
No. A number format changes only how the value is displayed, and a helper column leaves the source cells alone. Only deleting or hiding rows removes data from the chart's view, and the values remain in the file.
2. Why does my column chart still have empty space where the zeros were?
A column chart reserves a slot for every category, so a #N/A or hidden zero leaves the category visible on the axis with no bar. If you want the category gone too, remove that row from the source range or hide it and set the chart to skip hidden data.
3. Can I hide zeros in a pie chart?
Zero slices take up no visible area, so the chart shows nothing for them. The zero labels may still appear, so apply the empty-zero number format to the labels or remove zero categories from the source.
4. Can OmniDoc hide zeros in my Excel chart automatically?
OmniDoc's listed features do not include changing chart display settings. Use your spreadsheet program for the chart changes, and use OmniDoc's PDF editor to export a PDF table to Excel when your data starts in a PDF.
📝 Summary
To make an Excel chart not show 0 values, use a helper column that returns #N/A for placeholder zeros, or a number format that hides zero labels, and keep real zeros in the data. Check each change against the source table so that the chart shows the trend without misleading readers.
