Tag Archives for " format cell "

7 Creating a custom timeline in Excel

Recently, a fellow reader, Jessica from Miami, asked if I would help her figure out a way to create an event timeline in a format her boss is partial to:

Example of a timeline created in Microsoft Excel She had tried to find templates online, but nothing really seemed geared to a legal context.

I tried creating a solution in Word, but it was less than satisfactory.  So, given that Jessica was pretty comfortable with Excel, I developed a template for her there.

Changing the orientation of text within cells (vertical, horizontal, or diagonal, as in the example above) is actually pretty easy — here, I’ll show you:

(To view in full-screen mode, click the button in the lower right-hand corner.)

There’s other formatting done here too — the cells are wrapped (the Wrap Text checkbox above), I shifted the vertical alignment to Bottom, and in some cases, to get the middle cell to look more “centered,” I added a hard return before the text (with ALT-ENTER).  There’s a fair bit of eyeballing that has to be done to get it to look right, and it’s all a judgment call according to your personal preference.

What uses could you find for this trick?  Let me know in the comments below.

(P.S.: Jessica seemed to be pretty happy with her new template last I heard!)

59 How to put multiple lines into cells in Microsoft Excel

If you use Microsoft Excel to organize data (say, a list of documents being produced), you may have run across The Cell That’s Too Small For Its Data.  You know, you’ve got a bunch of stuff typed into a cell (not because you’re rambling, but because you need all that information, dang it), and it just breaks out of the borders of the cell and keeps on going:

Text in an Excel cell not wrapped

And if that’s not annoying enough, if you have to type something into the cell to the right, then you’ve just cut off the last part of that other cell:

Text in an Excel cell that's not wrapped and is cut off

What you want to be able to do is have the information in the first cell wrap so it appears on multiple lines within that cell.  Right?

Here are a couple of different tricks to try:

Wrap text. If it really doesn’t matter where the line wraps (as long as everything stays within the same cell), then the thing to do is format the cell so that the text wraps automatically.  Although the different versions of Excel (from 2002 through 2013) have various buttons and commands to do this, the one foolproof method that works in all versions is this:

  • Right-click your mouse inside the cell.
  • You’ll see a menu that looks like this:

 

Excel right-click menu with "Format Cell" selected

  • Once you have the Format Cells dialog box open, go to the Alignment tab and check the “Wrap Text” box:

Excel Format Cells Dialog | Alignment Tab

  • The text in that cell will now wrap automatically.

Text in an Excel cell with line wrap turned on

In the Ribbon-based versions of Word, this is a one-click operation. Just go to the Home tab and click on Wrap Text:

excel-format-cells-align-wrap-ribbon

Good news: You can apply this formatting to more than one cell at a time.  You can select multiple cells by holding down the CNTRL key while clicking on them, select entire columns or rows by clicking on their headers (the “A, B, C” on top of columns or the “1, 2, 3” to the left of rows), or even select the entire spreadsheet by clicking on the upper-left-hand corner (where the A and 1 meet).  Once you’ve selected all your cells, then just follow the steps above.

Inserting a line break within a cell. But what if you want to control exactly where the line breaks (say, you want an address to appear like this):

A mailing address typed into Excel

Even easier!  After you type each line, just press ALT-ENTER on your keyboard to insert a hard return.

Controlling vertical alignment. By default, data in cells is aligned at the bottom.  This can create some readability problems if some of your cells have multiple lines:

Text in an Excel cell with line wrap turned on

If you have a particular preference as to whether all the data across the row lines up at the top, the bottom, or in the center, select all the cells you want to re-align, then right-click to get the menu (as we saw above), choose Format Cells, and go back to the Alignment tab.

Format Cells | Alignment Tab | Vertical Alignment

See that drop-down menu that says “Vertical” (above)?  That allows you to change the vertical alignment within the cells you have selected.  If you want everything to line up across the top, then choose “Top.”  If you want everything centered, choose “Center.”  You get the idea, right?

What cell alignment issues do you run into in Microsoft Excel? Let me know in the comments below.

Update: Copying cells that have hard returns

Down in the comments, there’s a bit of a controversy about whether you can successfully copy a cell into which you’ve embedded hard returns to another cell. Here’s a quick guide to both “how to” and “how NOT to”: