Formula Errors

How to Copy a Cell in Excel Without Showing Zeros

Muhammad Talha Oct 10, 2026 870 views

Question details

The user needs a formula to reference a cell on another worksheet, but it must return a completely blank cell instead of a '0' if the source cell is empty.

How to Copy Excel Cells Without Showing Zeros

Product: Excel
Device & OS: not provided
Scenario: Referencing data from a different worksheet to compile a list, particularly preparing the destination sheet to be exported as a clean CSV file.
Observed behavior: A standard cell reference formula returns a 0 when the source cell is blank, which introduces unwanted zero values into the exported CSV data.

Before you start

Verify the exact name of your source worksheet and the specific cell reference you want to pull data from, as you will need them to structure your formula correctly.

Solution 1Recommended

Use the IF Function to Return a Blank String

This is the most reliable method for ensuring destination cells remain completely empty when referencing a blank source cell, which is essential for a clean CSV export.

By default, Excel treats empty cells as a zero when referenced. Wrapping your cell reference inside an IF statement allows you to test whether the source is blank first, and then explicitly output an empty text string if it is.

Select the destination cell

Click on the cell in your new worksheet where you want the copied data to appear (e.g., T2 on the '2. Running Billing' sheet).

Enter the IF formula

Type the formula: =IF('1. Running List'!W2="","",'1. Running List'!W2). Replace the sheet name and cell reference with your actual source data locations.

Apply the formula

Press Enter. If the source cell is empty, the destination cell will now appear perfectly blank instead of displaying a zero.

Copy the formula to other cells

Click the destination cell, click and hold the small square at the bottom-right corner (fill handle), and drag it down or across to apply the formula to the rest of your list.

Use the IF Function to Return a Blank String

Formula Breakdown: The two double quotes ("") represent an empty string. The formula translates to: If the source cell is empty, output nothing. Otherwise, output the value of the source cell.

Solution 2

Hide Zero Values Using Custom Number Formatting

If you only need to hide zeros visually within the spreadsheet and do not strictly require empty strings for a CSV export, custom formatting is a quick alternative.

Professional Spreadsheet Editor

Handle Complex Formulas Effortlessly with WPS Office

WPS Spreadsheet provides 100% compatibility with Excel formulas, allowing you to use functions like IF, VLOOKUP, and more without any hassle. Prevent zero-value errors and export perfectly clean CSVs for free.

1. Install WPS Office: Download and install WPS Office for free from the official website.
2. Open your workbook: Launch WPS Spreadsheet and open your existing Excel workbook.
3. Apply your formula: Enter the =IF() formula in your destination cell exactly as you would in Excel to handle blank values.
4. Export to CSV: Go to Menu > Save As, and choose CSV format to export your clean data.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsSupports over 400 native Excel functions and formulasFree and lightweight alternative for seamless spreadsheet managementIntuitive UI that requires no learning curve for Excel users

Frequently Asked Questions

Why does Excel show a 0 instead of a blank when referencing an empty cell?

Excel automatically treats empty cells as having a numeric value of zero when they are referenced directly by another cell. This default behavior is designed to prevent calculation errors in mathematical formulas.

Will the IF formula method work when I export my sheet to a CSV file?

Yes. Unlike custom formatting which only hides the zero visually, using the IF formula to return an empty string ("") ensures that no zero character is written to the CSV file during the export process.

Can I turn off zero values for the entire worksheet instead of using formulas?

Yes. You can go to File > Options > Advanced, scroll down to the 'Display options for this worksheet' section, and uncheck 'Show a zero in cells that have zero value'. Note that this will hide all zeros, including intentional ones, but it does not remove the underlying zero value upon CSV export.

Excel & Data Problems