New Line in Excel Cell: Doing It Right

Microsoft Excel is the most popular piece of software when it comes to dealing with spreadsheet and, even though it is quite intuitive and easy to use most of the times, there are situations when performing a very simple task can become downright frustrating. A good example of such operation is adding a new line in a cell – it sounds very simple at first, just to become a real nightmare moments later if you don’t how to actually do it, or how to do it right. There’s no reason to freak out, though, as you are about to learn two different ways of adding a new line in an excel cell – it is super easy and super useful.

For other useful tips and tricks, consider checking out this excel for beginners online course.

New Lines – Do You Need Them?

While in most cases spreadsheet cells don’t get very crowded with information, there may be times when you will eventually need to add more content to a cell and want it a bit organized as well. At this point, one of two scenarios will probably take place:

• You will start typing in the text, just to notice that it is not all visible once you move to another cell;
• You will start typing in the information, hit Enter when you will want to move to a second row and notice you’ve actually moved to another cell.

Both scenarios are equally common, and both are equally annoying, so let’s see what you can do about it.

Simple Formatting

A very common mistake people make when trying to get a new line in a cell is that they enable the Wrap Text feature and then manually adjust the length of the cell in order to break the text at the desired point.

At a first glance, the results might look okay, but this is actually just an illusion, since the formatting is  entirely cell-length-dependent, meaning that every time the length of the cell changes, so will the break point.

This eventually turns out to be problematic even if you use it for one single cell, so you shouldn’t even dare to imagine what this would do if you were to use it in a spreadsheet with thousands of entries.

In order to have control over the line breaks, you will need to set the breaking points yourself, and the method is easier than you think: while entering the content, simply hit Alt + Enter and your cursor will automatically move to a new line.

At this point, the only noticeable difference between this method and the manual-formatting method is that, in this method, the line break also appears in the formula field. As unimportant as this detail might seem, it is exactly the detail that makes the difference. Look what happens if you resize the cell length:

As you can see, this time the text is still broken into two pieces, even though there would be plenty of space for the whole text to fit on one line. This is because the formatting is clearly defined in the formula bar and it is not in correlation with the length of the cell.

You can learn more Excel essentials from this online course.

Formulas & Functions

If you’re the technical-savvy type and want to make things a bit more interesting, you can also use an Excel function to get a new line in a cell. While there will be no noticeable differences between this method and the previous one from a visual point of view, the use of formulas and functions will prove to be a better approach in terms of performance if you will be exporting the spreadsheet and loading it into a database, as it will be parsed more efficiently. This Microsoft Access introductory course will teach you more about databases and importing Excel data, so you will better understand why it is important to make your Excel spreadsheet parsable.

Let’s take the previous text example and see how it looks in the form of a formula:

As mentioned before, there is no visual difference in the cell itself, but if you will take a look in the formula field you will notice that the text looks nothing like before. Here is an explanation of all the elements of that formula:

• The equal sign: this symbol tells Excel that the cell contains a formula.
• The quotation marks: these elements have the role of delimiting the pieces of content from other elements of a formula. Without these, Excel would try to interpret the words as functions, thus returning an error.
• The CHAR(10) element: this is the part that creates the line break. The CHAR() function is used to insert special characters or symbols, while the number “10” in it is the code for the Line Feed symbol in ASCII code.
• The ampersand symbols: these elements are used to combine the elements of the formula together.

The advantage of using formulas and functions is that you have a significant higher level of control over the content you are working with. The screenshot below shows a formula that contains two pieces of content extracted from two different cells, displayed on two different lines of a single cell.

You should now have a pretty clear idea as to why using formulas instead of simple text formatting can be more efficient, especially if you are working with a lot of values that are prone to changes. This 2014 Excel training online course will show you how to easily create and handle formulas, thus unleashing the full powers of Excel.

Bottom Line

This is another proof, in case you still needed one, that Excel is much more than some simple spreadsheet-handling software – all you need is the right set of skills. You can obtain those skills by taking this comprehensive Excel online course that covers all the basics as well as advanced stuff. You might also want to check out this blog post to learn some interesting Excel tips & tricks and you’ll be using Excel like pro in no time!