Part of that format includes the cell’s alignment, which means that the data can start at the left edge of the cell, end at the right edge, or be centered within the cell. If you want, you can reposition your data within a cell by changing its alignment.
- (Narrator) When you enter a value into your worksheet, Excel determines what type of value it is, and assigns that cell a format. Part of that format includes the cell's alignment, which means that the data can start at the left edge of the cell, start at the right edge, or be centered within the cell. If you want, you can reposition your data within the cell by changing it's alignment. I'll demonstrate how to perform these tasks, my sample file for this movie is the alignment workbook, and you can find it in the Chapter six folder of the exercise files collection.
This workbook summarizes monthly revenue data, and you can see that it is for battery sales. What I want to do is change the alignment of the contents of some of these sales, to make the data a little more attractive. You have three basic alignments for a cell, and those are left, right, and center. Left align text, as you can see here in column C, starting with C3 and going down through C15, starts at the left edge of the cell and continues as far to the right as necessary.
In cell F2, you can see that the contents of cell F2 have continued on past the edge of that cell, but because there's nothing in cell G2, Excel renders that text. If there were something in G2, then all the text after the comma in cell F2 would be hidden. Also note that the contents of column B, starting with B4 and going down to B15, the numbers are right aligned. If you want to change the alignment of the values in your cell, then you can do that in a couple of ways.
First, you need to select the cells that contain the values you want to align. In this case I've selected B3 through D3, and then on the Home tab of the ribbon, you can see the alignment tools here, and you have the left align, or line left, center text, and align right. I prefer to align my headings to the center, so with B3 through D3 selected, I will click the center text button, and it's been applied. Also note that the text appears at the bottom of cells B3 through D3, and you can see those boxes here.
So, we have bottom align, middle align, and top align. For many headers, I stay with bottom, but in this case I want to see what it would look like if I were to align the text to the middle of the cell. So I will click the middle align button, and I can see that the headers are separated from the text a little bit, more effectively and it's easier to read. Another change you can make is to indent the contents of a cell. You can see here that the values in the cells C4 through C15 run very close to the values in B4 through B15.
I don't want to center the values in B4 through B15, so instead I will indent, or move to the right, the values in cells C4 through C15. So I will select those cells and then, again on the Home tab of the ribbon, I will click the increase indent button. And that moves the values a little bit off the left edge. If I wanted to move them back, I could click the decrease indent button, but in this case I'll stay with what I had, and increase it.
Finally, if you have text that extends beyond the edge of a cell, you can either wrap the text within the cell, or you can add a line break. I'll click cell F2, cause that's the cell that contains the text I want to work with. If I want to wrap the text within the cell, then I select the cell, and again on the Home tab, click the Wrap Text button. What that does is it leaves the edges of the cell, as they were, the horizontal edges, but it increases the vertical so that you have all that space taken up.
If I were to double click within the cell, you'll see that pressing the delete button doesn't let me move back, if I press return then the value stays as they are. I can change the height of that row by dragging the bottom edge of the row header, and I can go up just a little bit more, and there you go. If I want to increase the horizontal size I can drag the right edge of column F, and that's a little better. I have battery revenue, plus a comma, for calendar year 2018.
I'll press Command+C a couple of times to undo my changes. There we go. And now, I'll show you what happens if you add a line break. Now I will go to the Formula bar, and click to the left of the C, and CY2018, and I'll backspace twice, or delete twice, to get rid of the comma and the space, and then the keyboard control, or keyboard shortcut that you used to enter a line break is Command+Ctrl+Return. So, Command+Ctrl+Return, and you can see that was entered, and now if I press Tab to move away from the cell, I have my line break entered.
So I have Battery Revenue calendar year of 2018. Changing a cell's alignment helps you distinguish your headers from you data, and makes your data easier to read. There are lots of options available in the Home Tab's alignment group. I didn't have time to cover them all in this movie, but you should take the time to explore them on your own.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks