Appearance of empty cells when transferred via formula to the master Excel workbook

Question

Appearance of empty cells when transferred via formula to the master Excel workbook

Laura Wolgemuth 20

I'm working with 5 departments using different Excel documents for each team due to security. Each depts spreadsheet ties back to my master workbook via formulas.

Cells that are complete on the dept documents look fine on both the original and the master.
The incomplete cells on depts documents are blank on their copies which is fine.
On my master document incomplete cells show as odd values like a 0 for text fields or 1/0/1900 for dates.

Is there a way to have incomplete cells remain blank until completed? My current formula to a cell is pretty basic: =(reference the cell on the departments spreadsheet). We use Microsoft 365.

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Hendrix-C 20,015 Microsoft External Staff Moderator

Hi @Laura Wolgemuth,

Based on your sharing, this is an expected behavior in Excel: A direct link formula will return 0 when the source cell is blank. If the master cell is formatted as a date, that same zero can display as 1/0/1900 and as 0 if the master cell is formatted as text.

The most suggested approach is to use an IF formula that returns "" when the result should display blank. However, since you have worked with 5 different departments, I believe there would be huge amount of direct link cells and updating all formulas is not a viable solution.

Therefore, if you want to make the incomplete cells remain blank mainly for display/visual purpose, you can go to Excel > File > Settings > Advanced > scroll down to display option > uncheck the box of "Show a zero in cells that have zero value". Turning off this option will hide that displayed zero, so the cell appears blank instead without any 0 or 1/0/1900 value.

User's image

I hope this approach would be a suitable one for your situation. If you have any questions or need any further clarification, please leave a comment here and I will be happy to continue to help you.

Thank you for your patience and understanding. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Laura Wolgemuth 20 Reputation points

2026-06-01T20:27:31.91+00:00

Thank you. My document is saved in OneDrive and I had to pull it onto my desktop to have access to the options above but it looks like it works on the OneDrive version as well. I had to go through the process for each sheet on the master document but it held up. thank you so much!
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-01T20:41:21.5333333+00:00

You're welcome @Laura Wolgemuth,

I'm very glad to know that my sharing would help resolve your concern.

For any concerns or questions in the future, please don’t hesitate to reach out. Not only will I be happy to assist you, but all moderators in the Q&A forum will also do their best to provide support and guidance.

Wishing you a wonderful day ahead.