Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

When you have text in an Excel cell that is too long to be shown in the visible area of that cell, and the next cell on the right is empty, Excel lets the text be displayed in that next cell (and the next, and the next, as needed). I want to change this; I want to avoid this text overflow.

I know I can avoid this by enabling "word wrap" and adjusting row height. But that is not what I want.

I want to change the DEFAULT behavior of Excel so it shows the value of each cell only in the visible area of that cell. No overflow, no word wrap.

Is this possible? (I am using Excel 2010, by the way.)

share|improve this question
2  
if it'll turn out not to be possible, you can use a space character to the empty cells... – Aprillion Nov 19 '12 at 16:45
    
quite interesting - I don't know of any other way than using WordWrap,Font-AutoSize or characters in the other cells. – Jook Nov 19 '12 at 17:28
1  
You can truncate extra characters by selecting all cells, going to Data / Text to Columns and specifying a break after as many characters as can fit in a cell without wrapping/overflowing. Remember to skip the additional column created that holds the extra chars! – Karan Nov 20 '12 at 17:20
    
I guess this would answer your question: superuser.com/a/878605/497156 – Sulaiman Sep 22 '15 at 12:26

Yes, you can change this behaviour, but you will probably not want the side effects this causes.

The key to limiting the cell contents to the cell's boundaries regardless of whether the adjacent cell contains data is the text alignment Fill. Select the cells you don't want to overflow and right click them > Format cells... > Alignment tab > Horizontal alignment > Fill

The problem with this method is that this will actually fill cells by repeating their content when it is short enough to fit in the cell multiple times. See below screenshot for what this means. (Note that B7 is filled with 'short text'.)

Screenshot showing the different text alignment behaviour for default and fill

It seems like you will be stuck with the workarounds in Benedikt's post

Note that you could also fill the adjacent cells with tick characters (') using Benedikt's first, very clever method. This way you don't have to hide anything, prevent cell overflow and if you copy the cells as text (let's say to notepad) you still get empty text and not spaces or ticks.

share|improve this answer
    
So I took your suggestion of following Benedikt's method to fill the empties with ' characters. Now I don't know whether I should upvote your answer, or his, or what! :-p – Dan Henderson Mar 24 at 16:02

Here's how I do it.

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

Filling all empty cells: (tested on a Mac)

  • 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".

_______________
  That's Ctrl+Enter, not Ctrl+Shift+Enter.

share|improve this answer
1  
Why the down vote? – Benedikt Köppel Jul 24 '15 at 13:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.