Skip to content

Instantly share code, notes, and snippets.

@dsaiztc

dsaiztc/excel_prevent_text_overflow.md

Last active September 27, 2017 10:11
Show Gist options
  • Save dsaiztc/badc2619c05d3629f74a960e4c0903fd to your computer and use it in GitHub Desktop.
Save dsaiztc/badc2619c05d3629f74a960e4c0903fd to your computer and use it in GitHub Desktop.
Download ZIP
Raw
excel_prevent_text_overflow.md

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".
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment