Formula Errors

How to Keep Linked Excel Cells Blank Instead of Showing Zero

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to reference a cell in another worksheet, but needs the destination cell to remain completely blank if the source cell is empty, rather than returning a 0.

Product: Excel
Device & OS: not provided
Scenario: Linking cells across different worksheets where some of the source data cells are currently empty.
Observed behavior: Excel automatically displays a 0 in the destination cell when referencing a blank cell. Additionally, linked date cells sometimes display as a five-digit serial number instead of a formatted date.

Before you start

Verify the worksheet name and cell reference you are trying to link (e.g., 'By Name'!A2), and check whether the expected output should be text, a number, or a date.

Solution 1Recommended

Use an IF Formula to Return an Empty String

This is the most versatile and recommended method. It works perfectly for numbers, dates, and text, ensuring the destination cell remains truly blank if the source is empty.

By wrapping your cell reference in an IF function, you can instruct Excel to check if the source cell is empty first. If it is, the formula outputs an empty string; if not, it outputs the cell's actual value.

Select the destination cell

Click on the cell in your destination worksheet where you want the linked data to appear.

Enter the IF formula

Type the formula =IF('By Name'!A2="","",'By Name'!A2) into the formula bar. Replace 'By Name'!A2 with your actual source sheet name and cell reference.

Apply the formula

Press Enter. The cell will now display a blank space instead of a zero if the source cell has no data.

Fixing Date Formats: If your source cell contains a date but the destination cell shows a five-digit number after using this formula, right-click the destination cell, select 'Format Cells', and change the format to 'Date'.

Solution 2

Append an Empty String for Text Values

A faster, alternative formula specifically for text values that forces the software to read the blank cell as a text string instead of a zero.

Seamless Formula Management

Easily Manage Formulas and Cell References in WPS Spreadsheet

WPS Spreadsheet offers robust data linking capabilities and full formula support. You can easily use conditional statements to manage blank cells and maintain clean, professional-looking reports without unwanted zeros.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your linked data.
2. Select the destination cell: Click the cell where you want the data to appear without showing a zero.
3. Input the IF function: Type =IF(Sheet1!A1="","",Sheet1!A1) in the formula bar, replacing the reference with your actual source cell.
4. Adjust date formats if necessary: If you are linking dates and see a serial number, right-click the cell, select 'Format Cells', and choose the 'Date' category.

Fully compatible with Microsoft Excel formulas and .xlsx formatsAdvanced cell formatting options for dates, numbers, and custom textLightweight application with a highly intuitive user interfaceFree built-in templates for professional data management

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show a 5-digit number instead of a date when linking cells?

Spreadsheet programs store dates as sequential serial numbers for calculation purposes. If you link to a date cell and it displays a 5-digit number (like 44197), the destination cell is currently set to the 'General' format. You can fix this by changing the destination cell's format to 'Date' via the Format Cells menu.

Can I hide all zero values across an entire worksheet at once?

Yes. Instead of using formulas, you can change the display settings. Go to File > Options > Advanced, scroll down to the 'Display options for this worksheet' section, and uncheck the box that says 'Show a zero in cells that have zero value'.

Does adding &"" to a cell reference affect mathematical calculations?

Yes, appending an empty text string (&"") to a cell reference forces the result to become text. If you try to SUM or multiply that destination cell later, it may result in an error. For numerical data, it is always safer to use the IF function.

Excel & Data Problems