Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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

In Excel, 2010 or any other version probably, if I enter in a cell, a long single-line text that is longer than the width of the cell, Excel sometimes render the text across the next adjacent cells; some other times, it gets cut off at the boundary with the adjacent cell to the right.

I would like to know how does Excel decides what to do, so I can better control my layouts. Note that I do not want to use merge cells, as it is inapproperiate at times. Also, I already tried "Clear All" formatting on all affected cells but still doesn't seem to reveal much.

Any ideas? Thank you.

share|improve this question
    
If any comes by after 20 April 2012, this question is still not answered. I haven't found a consistent behavior to this. If you know the answer, please post and I will set the check on it. Thank you. – Jake Apr 20 '12 at 1:08
    
I'm voting to close this question as off-topic because you're looking for a definition of behavior that only the developers of the software can truly understand. – KronoS Feb 16 '15 at 15:41

13 Answers 13

up vote 6 down vote accepted

For text to overflow beyond the edge of a cell, the following conditions must be true:

  • The cell does not have "Wrap Text" turned on
  • The cell is not a merged cell
  • The cell contains a value that exceeds the width of the cell
  • The adjacent cell is empty*
  • The cell has any of the following horizontal alignments:
    • General
    • Left (Indent)
    • Center
    • Right (Indent)
    • Center across selection
      (Right overlaps the cell to the left; center overlaps on both sides.)
  • The cell contains a text value. Numerical and date values get converted to ####, or to scientific notation, instead of overlapping adjacent empty cells.
  • The worksheet does not have "Show Formulas" turned on

I believe these are all the necessary conditions. If I have missed any, please feel free to edit this answer.

*In certain circumstances, an adjacent cell can appear to be empty, but not be, in which case the text will not overflow into that cell, because it is not truly empty.

share|improve this answer
1  
A 4 yr old question with 61K views and a dozen previous answers. It doesn't happen often, but you managed to add substantive information to the thread. +1 – fixer1234 Mar 24 at 16:54
1  
Thanks :) I came across the question from a Google search and was surprised at: A) the number of answers, B) how many answer a slightly different question than what was asked, including the late answer with the top score, C) that none of the answers were complete, in the question's context "complete explanation of how excel determines whether to overflow the cell". – Dan Henderson Mar 24 at 18:44
1  
For your comprehensive answer, you deserve a green tick, 3 years later! – Jake May 8 at 11:45

This answer is three years late:

Right click the cell, format cells, alignment tab, horizontal, and select Fill or Justify.

I use office 2013.

share|improve this answer
    
Thanks! Not late at all, I still need this after 3 years. Works for 2010 as well. – Jake May 8 at 11:41
    
But incorrect.. – EML Jun 4 at 12:44

I have experienced this same issue, and it only applies when the value in the cell is all numeric characters (including decimal point), if the value in the cell has any alpha characters in it, it does properly expand into adjacent cells, but when the value is numeric only it does not. This is true even if you change the format of the cells to Text after the number was entered into the cell.

To fix this I had to pre-format the cells to Text in Excel and then paste the data into the text cells using paste special - values.

Another solution is to put a single quote in front of a number and Excel will treat it as text, allowing it to display in the adjacent cell.

share|improve this answer
    
I still have this problem after more than 1 year... and still no complete answer. – Jake Oct 30 '13 at 4:22

Many versions ago, this depended solely on whether the adjacent cell was empty or not. If anyone thinks this is no longer true, make a comment below.

share|improve this answer
    
The "Clear All" in "excel 2010 > home > editing group > clear dropdown > clear all", also clears content. i.e. all cleared cells should be empty, but still it does not overflow, sometimes. – Jake Mar 14 '12 at 5:37
1  
Your cell needs to be aligned on the left too if you want overflow – JMax Mar 14 '12 at 8:02

I AM SO HAPPY TO HAVE FOUND A REMEDY!! For some time now, I've learned to live with it. Text from one column wouldn't go into following column...it would cut off. I tried "clear all" among other many options. Finally...Format - Cells - and unchecked Merge Cells. Seems so easy now.

share|improve this answer
    
The question says, “Note that I do not want to use merge cells,” so your answer does not appear to be pertinent to this question. Also, we don’t need to hear about your emotions, and we prefer to keep the SHOUTING to an absolute minimum. – Scott Jan 12 '14 at 21:15

It will flow into the next cell unless you have some kind of content in the cell next to it (i.e. Column A, Column B).

If you delete the content in the B column, the content in the A column will no longer overflow. If you go back to the B Column, and do a 'clear contents' then A will flow past the cell size again.

share|improve this answer

In addition to the instances noted in other responses, it should be noted that Excel will always truncate text overflow if the "Show formulas" option (Ctrl + `) is active on the worksheet. If you regularly use the Ctrl + 1 keyboard shortcut to access the "Format Cells" dialog, it's very easy to accidentally activate "Show formulas", and you might not notice it has happened. This is especially true if you don't actually have any formulas on the active sheet.

share|improve this answer
    
Exactly my case!!! I often click the shortcut for mistake! – spiderman77 Feb 18 at 10:06

Click the cell in question, and turn off "wrap text". In Excel 2013, this is on the Home tab about halfway across the screen.

share|improve this answer

I do not recommend using clear all (unless you want to lose your work!). Excel (2013 at least - not sure about earlier versions - although I'm pretty sure 2000 didn't act this way) recognises an empty string (i.e. "") as a value and so even if you have an "empty" adjacent cell, your data will not overflow into that cell.

What is required in the cell is a null - I imagine this has been adopted in Excel in order for it to be more aligned with databases, where empty strings and nulls are very different things.

Selecting the cell and hitting delete will actually place a null in there. However if you have a large spreadsheet, or for some reason your spreadsheet's cells often get populated with empty strings, then a quick solution is to write a macro that checks for empty strings ("") and replaces them with nulls.

share|improve this answer

Before entering your text, change the Format of the Cells to Text. That seems to allow the text to cross into the next cell (as long as the next cell is blank).

It doesnt work if you try to change the Format after entering text.

Dave

share|improve this answer

This is frustrating for me and so this is what I do. the above answers are all good and the text does only intrude on the next cell if there is no data in the cell so what I do is, Select the column you want to work with and on the Home tab, select Find and Select drop down, choose Replace. In the "Find What", leave it blank, and in the "Replace with" put a period or space and use the Replace All selection at the bottom. This will put something in the empty fields and stop the intrusion of the data from the field before it.

share|improve this answer

Ive had the same problem and some of the solutions above wont work if the cell in question contains a formula.

One method Ive just managed to get to work is select cell along with adjacent cells that you want data to spread across, right click, format cells, alignment, horizontal alignment, centre across selection. In this way you can avoid cell merge.

Hopes this helps.

share|improve this answer

The data will overflow only if the column to the right is blank. So, filter and select the (Blanks). Select the first cell at the top of the column and enter a space. Copy and paste the first cell to the end of the column. Clear the filter, and you will see that the overflow data has now disappeared.

Voila..

share|improve this answer

You must log in to answer this question.

protected by KronoS Feb 16 '15 at 15:39

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged .