Microsoft Excel 77-727 – 2.2. Format Cells and Ranges

1. 2.2.1 Merge Cells

Merging cells is a feature commonly used on text labels to identify a group of cells together. So for example, with the Helpless Airlines marketing budget, I’ve put the title in Cell A one. But what I would like is for it to be evenly distributed across columns A to F in order to make it more centralized and professional looking. I can do this by merging the appropriate cells.

When you your marriage cells, Excel removes the edges separating the selected cells and treats the group as a single cell. I just select the cells I want to marriage and then click on the Marriage and center button here. This button marriages the cells and centers the contents at the same time. Notice that the only cell reference available now is a one. The rest are unavailable because in effect, they’ve all been married into a one.

You can see that there are a number of options here marriage and center, marriage Across Marriage Shells and Unmarried Shells. The Marriage Across option will only merge the selected cells without centering the text. The unmarried option does exactly that. It reverses the margin of the cells. If you married shells which have data in more than just the left mouse cell, this message warned you that the data in the cells other than the left mouse cell will be lost. You can also merge cells vertically, like so.

Merging cells can also be accessed by going to the Format Cells dialog box, which is available on the right mouse button. Just select the cells and then click the right mouse button to select Format Cells. The Alignment tab has this option to turn a marriage on or off.

2. 2.2.2 Modify Cell Alignment and Indentation

Alignment refers to the position or placement of data within the cell. You can align the cell contents horizontally as well as vertically, although you will most commonly use one of the horizontal alignment controls. By default, Excel assigns general alignment to new values entered into a worksheet. This means that Excel aligns numbers and dates to the right, while it aligns text labels to to the left, as you can see in these examples.

Also by default, cells are aligned on the bottom, which you’ll only be noticed if you increase the row height. You can also change the vertical alignment to the top or middle of the cell by using these alignment options on the Home tab. The alignment options on the ribbon are top align, middle align and bottom align. Then here we have align left, center line and align right. Data can also be indented within the cells by clicking on these two buttons which provide left and right indentation like so.

Every time you click one of these buttons, it increases or decreases the level of indentation by one. We saw in the last lecture how you can access the Format cell dialog box from the right mouse button, and how we can use the merge checkbox in the Alignment tab. There are other options in this dialog which are very useful to know, particularly the alignment options. For example, this orientation box allows you to drag to exactly the angle that you would like to see the data to be positioned in. Like so. We also have access to all of the alignment options available on the ribbon.

3. 2.2.3 Format Cells by using Format Painter

Once you’ve formatted a cell or range of cells, you may want to duplicate its formatting to the remaining parts of your worksheet. Excel provides a tool called the Format Painter that enables you to copy the cell formatting quickly from one area on the worksheet to another. So now, rather than copying the contents of a cell or range of cells, all you’re doing is copying the way that selected cells have been formed matters. You can then apply that same formatting elsewhere in your worksheets. When you get used to using this tool, it’s a really useful way to speed up the formatting of your work. So if I select this cell and right click on it, you can see the paintbrush icon here, which is the Format Painter.

When I click on it, notice that the cursor now has a paintbrush shape beside it, indicating that the Format Painter is active. You can apply the copied format to a single cell or range of cells. Now, when I select this range of cells, I can just click on it and the formatting is applied. The cursor is now back to normal, and the former painter has been turned off. Alternatively, if I double click on the Format Painter when I’ve selected the formatting I want to copy, the former painter remains active until I press the escape key. The same functionality can be accessed on the ribbon from the Clipboard group on the Home tab.

4. 2.2.4 Wrap Text Within Cells

Can enter up to 32,767 characters of text into a cell. As long as the cells to the right are empty, the extra text that will not fit into the cell will overflow into the cells on the right. However, there is a way to make the text wrap within the cell. In other words, as the text meets the edge of the cell, it will automatically wrap onto the next line and expand the width of the cell if needs be. This will keep the text within the left and right boundaries of the cell.

Excel increases the width of the role automatically to accommodate the full length of the text. If you turn off the Wrap text feature for a cell, the text will revert back to displaying on one line. To turn text wrapping on, just select the range of cells that you want to apply wrapping to. Right click on the mez and go to the Format Cell command. Then to the Alignment tab and turn on the Wrap text checkbox. You’ll see the effect immediately.