Although Microsoft Excel is designed primarily as a spreadsheet application for number-crunchers, I use it a lot to create various kinds of lists. I find it more convenient than creating tables in Word for certain purposes.
However, if you’re used to doing most of your work in Word, Excel can be a little frustrating to use because, as I said, it wasn’t designed for these different purposes. But over the years, I’ve discovered a few ways to do some things a little more easily, so I’m sharing a few of those tips here.
One of the ways I use Excel is to create a mailing list, so that’s the example I’m going to use to illustrate the various features. If you want to follow along, you can download the sample Excel file. I’ll start by showing the instructions with screenshots from Excel 2003, because the earlier versions all work more or less the same way. Then, at the end of the post, I’ll list where to find the same features in Excel 2007.
Fix column widths automatically.
This first screenshot shows a list of fields across the top row (Title, First Name, Last Name, etc.) and one row of data under them.
Now, it’s easy to see that the default column width isn’t big enough for some fields and is bigger than it needs to be for others. You could drag the line between the column headings (A, B, C, etc.) to adjust each one separately, but that can take a while if you have a lot of columns.
Instead, you can adjust all the column widths at the same time so they’re wide enough for the longest item in each cell for that column.
- Click the top-left cell (between A and 1) to select the entire spreadsheet.
- From the Format menu, select Column > Autofit Selection.
Voila! Each column is now the appropriate width for the data you have. For example, the Title column has become narrower and the E-mail column has become wider. If you add data later that’s longer than the current width, you can simply repeat these steps.
Stop e-mail addresses and URLs from becoming links.
The next problem is that the e-mail address was automatically converted to a hyperlink. (The same thing would happen if this was a web address.)
The reason that’s a problem is because, if you need to change it, and you click in the cell, it will launch a blank e-mail message window (or take you to the web page), which—trust me—can quickly become annoying.
To unlink a single instance, you can just right-click on the cell and select Remove Hyperlink form the menu.
However, you probably don’t want to do that each and every time this happens. You’ll notice under the e-mail address is a small rectangle. That is an indicator that there are some AutoCorrect options you can change. In this case, if you right-click on the rectangle, you’ll have the option to Stop Automatically Creating Hyperlinks. If you select that, it will remove the link from the current address and stop making them by default in the future.
Wrap text within cells.
When we autofitted the columns, above, it created a really wide one for the Notes field, as it should have. However, the problem is if you want to add a new column beside it, it’s way off to the right, and you’ll have to keep scrolling horizontally to see it.
Of course, we could just make the Notes column narrower to move the new column to the left, but then the text in the Notes cell gets covered up by the new one so you can’t read it all without clicking on the cell and looking at the function bar at the top.
Instead, I’m going to make the text wrap within the cell, so it will become taller, but we’ll be able to read everything in it.
- Click the Notes header cell (in this case, K) to select the entire column.
- From the Format menu, select Cells, then click the Alignment tab.
- Check the Wrap Text box.
Now we can see all the text, even though the column has been narrowed.
Add line breaks within a cell.
This one took me a while to figure out. Often, when I have a column like Notes, I want to add more info to it. For example, I might want to add something about my phone call with my client.
However, if I have a lot of notes, I don’t want them all to appear in one continuous sentence; I want each note on a separate line. The problem is, if I just click Enter, my cursor will go to the cell below it.
- Put your cursor in the cell where you want to put a line break.
- Press Alt + Enter on your keyboard.
That’s it! Your cursor will now move down to a new line, within the cell, and you can enter your text there.
Instructions for Excel 2007
- To autofit columns, from the Home ribbon, select Format > Autofit Column Width.
- To stop Excel from automatically creating hyperlinks for URLs and e-mail addresses, follow the same steps as for Excel 2003.
- To wrap text in a cell, from the Home ribbon, select Format > Format Cells > Alignment > Wrap Text.
- To add a line break within a cell, press Alt + Enter.