In this post, you'll learn what causes data to overflow into the next columns and how to keep text from spilling over in Excel.
When entering a long text in a cell, you may notice that sometimes it spills out into adjacent columns and at times gets hidden behind the neighboring cells. How does Excel determine what to do and how do you stop words from overflowing into next cells?
Knowing the circumstances under which text continues beyond the edge of the current column can help you better control the layouts of your Excel sheets. So, let's make up two lists.
For the cell content to overflow beyond the cell boundaries, all of the following conditions must be true:
For text to be truncated at the cell's edge, any one of the following conditions must be true:
Now that you know what things affect text overflowing, you can reasonably assume that fulfilling any requirement from the second list will prevent Excel cells from spilling out. And you are right! However, there is a small complication - all conditions but one would make some visual changes to the sheet. Which is the safe option that won't impact the spreadsheet's consistency in any way? Obviously, the last one. And the next section provides the detailed steps on how to implement it.
To keep Excel cells from spilling out into the next column, perform these steps:
Now, the data in the selected cells is truncated at the cell boundary.
Note. This method works correctly only if all of the selected cells spill out - in this case, the text will crop correctly. If some cell contains shorter text, Fill will populate an entire cell with the content, repeating it as many times as necessary. For example, a cell containing the word "Hey!" may look like "Hey!Hey!Hey!".
Those of you who know how the Wrap Text feature works in Excel may feel skeptical about this method. Indeed, turning wrapping on makes rows very tall:
However, there is a small clever trick to avoid this effect! Before wrapping text, this is what you need to do:
That's it! Using these two features together forces long text to be cut off at the cell edge.
Tip. This also works in the reverse order. If you have already enabled Wrap Text and now want to get rid of tall rows, set the row height back to default (15 points) as explained above. The result will be normal rows that do not overflow into adjacent empty cells.
And here's another amazingly simple way to keep long text within the cell boundaries - just type something in the neighboring cell. Don't want any extra data in your sheet? Type a space :) There are too many overflowing cells? Enter a space in a range or entire column to remove spill-over from multiple cells in one go!
Here are the steps to hide long text that overflows beyond the cell edge:
Done! The adjacent column still looks empty but no text flows into it from the left.
In some situations, an additional hurdle may arise - some cells in the adjacent column may be filled with data while others are not. As the result, the contents of some cells will continue into the next column and beyond while other data will be limited to the visible part of their current cells. This tends to confuse things and makes quite an ugly view.
To resolve this case, enter a space only in blank cells of the column into which data is spilling (column C in our example). To quickly select blank cells, make use of the Go to Special feature:
Voilà, no data is spilling into the next columns even if an adjacent cell is empty (in fact, it does contain a space character, but who will know it? ;)
Tip. The first two methods (Fill horizontal alignment and row height + wrap text) will also work in this scenario.
These are four quick and easy ways to remove cell's spill-over in Excel. I thank you for reading and hope to see you on our blog next week!
Table of contents