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.)

  • 1,685
  • 3
  • 16
  • 22
  • 8
    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: http://superuser.com/a/878605/497156 – Sulaiman Sep 22 '15 at 12:26
  • "I know I can avoid this by enabling "word wrap" and adjusting row height." Thanks! That's my selected solution. – Tripartio Jul 03 '19 at 17:20

9 Answers9


Yes, you can change this behavior, 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 behavior for default and fill

In addition to this, numbers will become left aligned and if the adjacent cell is set to Fill, too, text will still overflow into that cell (thanks posfan12 and HongboZhu for pointing this out).

So it really seems like you will be stuck with the workarounds in Benedikt's post.

Recommendation: You could 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, ticks, or any other filler characters for these cells.

Marcus Mangelsdorf
  • 1,207
  • 2
  • 12
  • 19
  • 3
    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 '16 at 16:02
  • 1
    I'm even more confused. I can't see any suggestion to use `'` in Benedikt's answer at all, including in old edits! I've upvoted both, just in case... – user56reinstatemonica8 May 09 '16 at 12:49
  • 1
    @user568458: My note was actually a variation of Benedikt's answer. You could fill the cells with `'` **instead** of filling them with `N/A` as Benedikt suggested. I just found Benedikt's method of *filling all blank cells* with an arbitrary value very compelling. – Marcus Mangelsdorf May 09 '16 at 18:15
  • 1
    'Fill' will cause numbers to become left aligned as well. – posfan12 Aug 12 '16 at 08:33
  • 1
    How...amazingly stupid. (Excel, not this answer.) – T.J. Crowder Mar 08 '18 at 15:51
  • 2
    another problem to this solution is: if the adjacent cell to the right is also set to "fill" and happens to be empty, the text will still flow into it ... – HongboZhu Feb 12 '20 at 11:37

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.

Benedikt Köppel
  • 1,025
  • 11
  • 14
  • Thanks for this. I just went ahead and added an empty space in all the empty cells, that was enough – Cos Jun 10 '22 at 13:20

Try entering the formula ="" (that's two double quotes) in the adjacent cell where you don't want to see the overflow. This evaluates to "null" which displays nothing and won't affect math.

  • 231
  • 2
  • 2
  • 1
    Combined with the post from Benedikt I believe this is one of the easiest solutions as you don't have to hide anything. – Matt Feb 26 '18 at 16:18
  • 6
    If affects the statistical function `COUNTA`, which *counts the number of cells in a range that are not empty*. A cell displaying a zero length string as a result of a formula is not empty and will be counted. – ProfoundlyOblivious Sep 21 '19 at 02:05

This may not be an option for everyone, but if you import the document into Google Sheets, this functionality is supported by default. On the top menu bar, three types of text wrapping are supported. Overflow, wrap, and clip. You are looking for clip.

Depending on the requirements, this may be a viable option for some people.

  • 502
  • 1
  • 6
  • 13
  • 39
    So your answer is actually saying drop Excel and go with Google sheets. – Solar Mike Dec 13 '17 at 19:42
  • 2
    Like I said, not an option for everyone, but may be a good solution for some. – yellavon Dec 15 '17 at 03:22
  • @SolarMike - thank you. I was confused, but your comment sorts out this answer. I think &yellavon assumes people will balk at his answer and as such is defensive - before actually explaining the answer. Your comment is more concise compared to the actual posted answer. – barrypicker Jun 15 '18 at 19:29
  • 1
    Actually this happens to be the answer most close to an actual solution. I discoevered that in Excel you can do something similar to this. Adjust the column to the required width, then enable word-warp on the column (which will cause all row heights to increase) and then finally select all rows and adjust row heights to the desired height. Voila! You now have text in cells that do not overflow to the adjacent cells.(@yellavon: I will accept your answer, you might wanna update it to contain this EXcel behavior so people can access the solution more easily) – e-mre Oct 08 '18 at 09:53
  • 19
    This should not be the accepted answer. It does not tell you how to prevent the overflow *in Excel*. – Herohtar Mar 13 '20 at 16:57
  • 3
    This does not answer the question whatsoever. How is it the accepted answer? This so called answer does not even make any sense in the context of the question. A completely illogical and irrational answer. – Aalawlx Jun 03 '20 at 21:50
  • How come this is the accepted answer? – nelsonspbr Nov 03 '21 at 21:44
  • This is relevant information, but should definitely not be the accepted answer. – Philippe-André Lorin Jan 23 '23 at 11:12
  • For those protesting this as the accepted answer: please note that an accepted answer on Super User (or Stack Exchange in general) is not necessarily the best answer for all readers of the question. It is the answer that the person who posted the question liked the best as of the time they selected the answer. As in this case, it might not be the best answer for most other people, but that's what upvotes are for--everyone can upvote the answers that they like, regardless of what the question asker chooses to accept. – Tripartio May 14 '23 at 22:25

Expanding on the solution of using ' to block Excel's rightwards spilling of text into adjacent blank cells, sometimes you don't want to modify/corrupt your data column by inserting that value in there.

Instead of inserting the ' in your actual data values, you can create a new dedicated vertical column for this purpose, entirely filled with ' characters all the way down.

1. Barrier column

enter image description here

2. Insert "barrier" column to the left of the column(s) that you want protected.

enter image description here

3. Collapse the barrier columns by setting their column width to a small value

Set the column width to a value such as .1, or alternatively keep the barrier columns wider to act as a margin or inter-column whitespace.

enter image description here

Note that you must set a width greater than zero; setting the vertical barrier column width to zero will revert to the unwanted cell overflow behavior.

4. Voila!

Glenn Slayden
  • 1,465
  • 12
  • 20
  • You can also drag the tickmark down using the cell corner to copy it down a whole column. – PRMan Jul 22 '22 at 15:25
  • I like this solution because it's one of the only ones that doesn't technically corrupt data and doesn't have any other major visual side effects. That being said extra columns ARE a little annoying. The biggest downside is that you have to do it for EVERY column which may overflow, so it may not be practical for every use case. It may also complicate exporting files (e.g. to CSV) for the purposes of subsequent automations. – mmseng Jun 24 '23 at 22:24

In Excel, adjust the column to the required width, then enable word-warp on that column (which will cause all row heights to increase) and then finally select all rows and adjust row heights to the desired height. Voila! You now have text in cells that do not overflow to the adjacent cells.

(Note: I found this and posted it as a comment in the selected answer but also posting it as an answer so will be easier to find for others.)

  • 1,685
  • 3
  • 16
  • 22
  • 3
    The second paragraph of the question: "I know I can avoid this by enabling "word wrap" and adjusting row height. But that is not what I want." – fixer1234 Jun 24 '19 at 21:39

Use the Horizontal Text alignment "Fill" for the cell. You can find it in the same place as other Alignment options that should solve your problem

Fill Text Alignment

  • 3,123
  • 11
  • 23
  • 32
  • 45
  • 1
  • 5
    Wasn't this covered by [Marcus Mangelsdorf's answer](https://superuser.com/a/990321/358766)? – Stevoisiak Oct 31 '17 at 19:05
  • 13
    Also note that Marcus explains a severe problem with this method - that if the contents of the cell are short enough, they will be duplicated. So "A" would become "AAAAAAAA". – Blackwood Oct 31 '17 at 19:54
  • Nice, Thanks. Right click on cells need to re-format, choose "Format cells", and do the rest as your guidance. It worked. – Catbuilts May 25 '19 at 03:59

In the same line of thinking that Google Sheets may not be for everyone, this macro may not be for everyone but it may be for someone.

It runs through the selected range and replaces overflowing cells with truncated text.

Flags determine if:

  • the offending text is copied to the same relative address in a new worksheet or if it is discarded.

  • the truncated text is hard coded or linked via worksheet formula =LEFT().

  • the truncated text is hyperlinked to the full string in he new sheet.

Default is to retain data and use both links.

Option Explicit

Sub LinkTruncatedCells()
  Dim rng As Range: Set rng = Selection
  Dim preserveValues As Boolean: preserveValues = True
  Dim linkAsFormula As Boolean: linkAsFormula = True
  Dim linkAsHyperlink As Boolean: linkAsHyperlink = True
  Dim w As Single
  Dim c As Range
  Dim r As Range
  Dim t As Long
  Dim l As Long
  Dim s As String
  Dim ws As Worksheet
  Dim ns As Worksheet
  Application.ScreenUpdating = False
  Set ws = rng.Parent
  For Each c In rng.Columns
    w = c.ColumnWidth
    t = 0
    l = 0
    For Each r In c.Rows
      If Len(r) > l Then
        s = r
        If CBool(l) Then r = Left(s, l)
          If r.ColumnWidth > w And Len(s) > t Then
            t = t + 1
            r = Left(s, Len(s) - t)
            l = Len(r)
          End If
        Loop Until t = Len(s) Or r.ColumnWidth <= w
        r.ColumnWidth = w
        If r <> s And preserveValues Then
          If ns Is Nothing Then
            Set ns = ws.Parent.Worksheets.Add(after:=ws)
          End If
          ns.Range(r.Address) = s
          If linkAsFormula Then _
            r.Formula = "=LEFT(" & ns.Name & "!" & r.Address & "," & l & ")"
          If linkAsHyperlink Then _
            ws.Hyperlinks.Add Anchor:=r, Address:="", SubAddress:= _
        End If
      End If
    Next r
  Next c
  Application.ScreenUpdating = True
End Sub

Final note: I have used it for personal projects and found it reliable but please save and back up your work before trying any unfamiliar macro.

Marcus Mangelsdorf
  • 1,207
  • 2
  • 12
  • 19

A simple solution that can be to write ' (one apostrophe) in the next cell. It is not visible on it's own in Excel, but will stop the overflow from previous cell.

It's not a super solution, but it worked pretty well for my limited case.

Ilya Chernomordik
  • 1,875
  • 3
  • 15
  • 13