daniel-s
6/8/2017 - 9:33 AM

From https://superuser.com/questions/507726/excel-prevent-cell-text-from-overflowing-into-next-empty-cell

Option 1: Fill all empty cells with a "N/A" and then use Conditional Formatting to make the text invisible. Or Option 2: Fill all empty cells with 0 and use an Excel setting to hide zero values.

Filling all empty cells:

  • Edit → Go To... → Special ... (On Windows: Home → Editing → Find & Select → Go To Special...)
  • Select "Blanks" and hit OK.
  • All blank cells are selected now. Don't click anything.
  • Type "N/A" or 0, and then hit Ctrl+Enter. This will insert the value into all selected cells.

Conditional Formatting to Hide "N/A"

  • Format → Conditional Formatting.
  • Create new rule.
  • Style: Classic, and Use a formula to determine which cells to format.
  • Formula: =A1="N/A"
  • Format with: Custom Format: Font color white, no fill.

Hide Zeros

  • Excel → Settings → View.
  • Untick "Show zero values".