Show Empty Cells As Gaps Not Working

Sometimes, when data cells are empty, they look like they are missing something rather than blank. This is called a visual confusion between a gap and a blank cell. Show Empty Cells As Gaps Not Working will remove the visual confusion by automatically converting all gaps to blanks and vice versa.

Gaps are rectangular areas of your spreadsheet that contain cells that are not listed in between them. They appear dark even when a cell is not empty.

Blank cells are rectangular areas in which the data that is listed in any of the cells, including the gap, will show. They are usually very light in your spreadsheet and look just like an ordinary data cell.

When you do some things like copying or duplicating a column that contains empty cells, you won’t see any visual confusion and will not see gaps or blank cells in your workbook.

What happens when you select Show Empty Cells As Gaps and set it to “Off” instead?

You will see really small blank spaces or gaps on your workbook, instead of the actual blank cells that are inside. This is done because of what is called data cell sizing or the ability of Excel to change its size automatically for each data value that is listed within. The larger a cell, the more value it holds or the bigger the number within it.

When both the empty cell or gap and the actual data cell are small, Excel automatically converts a gap to a blank cell and vice versa. Excel allows you to enable or disable this global setting.

Visual Confusion

This causes visual confusion for users who are not familiar with this option and may be expecting to see a blank cell instead. By setting Show Empty Cells As Gaps Not Working, you can choose to manually replace gaps with blanks and vice versa without needing to go back to the “Options” menu.

To set Show Empty Cells As Gaps Not Working, go to the Review tab. Under the Editing group, look for “Show Empty Cells As Gaps Not Working” and double-click it. Using a drop-down menu, you can choose to display empty cells as blank cells or gaps instead.

Turning off this setting will remove the gaps in your workbook automatically. However, it will not replace blanks with gaps even if you have another setting on your workbook that converts gaps to blanks.

Show empty cells as gaps greyed out

When this setting is greyed out, it means that you do not have enough cell formatting to make this option available. You can click on “Show empty cells as blank cells” instead. If your workbook contains multiple colored cell formats, you can use this setting.

As soon as you change the formatting of a cell, you will see “Show empty cells as blank cells” instead and will be able to display gaps or blanks just by clicking it so that it becomes active again.

You can set up the formatting of your cells and ensure that you have a minimum of two or more cell colors before you set “Show empty cells as blank cells”.

For instance, if you want to see two or more shades of grey in your Excel workbook, then select one color for the gutter area of the table. There is the option of selecting a different color for the text cells, which are usually gray or navy gray. You can also set up your cell colors depending on the number of rows and columns in your workbook so that they will be similar.

Learn More:

How to remove gaps in the excel line chart

It is easy to “remove” gaps in your line chart. You just have to specify the cell ranges where you want the gaps removed. As soon as this is done, the gap will be removed from all charts that use the same set of formulas and data.

As an Excel user, you have probably noticed when using simple charts that Excel does not automatically fill a chart with a solid color or cell shading for every value in your data range. In other words, if your data range has a zero value for the last month, there will be a gap on the chart between one month and zero months.

This is done in Excel charting because charts are meant to display data values, not blank cells or gaps. Filling in gaps on your charts can be done by these steps:

Start by editing the chart that contains empty cells or gaps that you want to remove so that it starts showing them as blanks instead of leaving them blank. If a chart has this problem, there will be empty cells or gaps in the data range that are outside of the chart area. If a chart has a gap that is inside the chart area but still outside of it, you can edit it by selecting only that part of your data range and replacing the formula with another one. Add the missing values to your data range and then select “Reset Selection” so that you no longer see any gaps in your data range.
Click on the “Chart Tools” tab and select “Format Data Series”. If your data series contains more than one data set, select the one that you want to remove gaps from.
Click on the “Show Details” button. The “Data Table” box will appear on your screen and will have a title that says “Details for Series 1” or something similar in it. This is the data table that contains all the missing values for your data series if you want to remove all gaps from every single series, select “Series 1” in this box.
All of your charts will update automatically and you will have removed all gaps from each one of them now.

Final Word

Too often we come across an Excel workbook that has empty cells displayed as gaps. This can sometimes be frustrating for the user when they’re trying to understand the data and how it aligns in columns, and rows. For example, to make the data a little bit more exciting, we can move one of the rows or columns to the right and everything starts to look like a jumble of letters. This can lead to misunderstanding and lost work time trying to figure out why the data isn’t aligning.

You can easily fix the problem by choosing a custom cell alignment. This setting allows you to change the display of an empty cell from displaying as a gap (the default selection) to displaying as a space between cells.