Join Dennis Taylor for an in-depth discussion in this video Adjusting horizontal and vertical cell alignment, part of Excel 2013: Advanced Formatting Techniques.
- In this worksheet called Cell Alignment, as you look at row seven, I think almost all of us would wanna make a change there. The text is not sitting on top of the numbers. We might want to center these entries or possibly align them on the right-hand side. Buttons that have been in Excel for a long, long time available on the Alignment tab on the Home Group are right here. Right Align, possibly that's an option here, or Center. I think most people choose one of those two, certainly not left aligned. They are left aligned automatically, and if you unclick the Center box, notice that's their native state.
Text entries are always lined up on the left side, as we see them in Column G as well. But let's say a lot of us would either want these to be centered or right aligned. And also Cell O here as well, right aligned probably. The entries here most often probably we're gonna see these on the left side of the cell, but they might look better centered. Or maybe even right aligned, sometimes you'll see that, although it is a little unusual. You've got complete control over that. With numbers, you wanna be careful with these. Should we center these numbers along with the text? Depending upon the format, you can do this.
Let's highlight all these and center these. Is that a good idea? I think not, particularly if these numbers are computational in nature. If we have decimal places as well, you can imagine how the decimals are not going to be lined up with one another. If you do format these, for example, what if we format all these numbers using the Comma button here on the Home tab within the Number group? Now try centering these, or try making them left aligned. We can't do that. You might think as you look at these, aren't they centered? They're not really, they're aligned on the right.
The reason they appear to be not fully aligned is because if they were negative we'd have parentheses. These will automatically cause the decimal points to always line up, even if you have a mix of negatives and positives. For example, if I click right here and make this entry be 200, we'll have a negative below this. But the decimals still line up. Certain other kinds of formats will allow you to center, but as I sometimes say in live sessions, they taught us back in the second grade, line up numbers on the right side before you add them or subtract them or multiply them.
So most of the Excel formats will not let you center the data once the format's been applied. But if these are so-called general format, as they looked right here, you can center them. I'd say don't do that. With percents, these can be centered, too. I think it's a good idea not to center them. Leave them in their native state, which is right aligned as well. Recognize that row 14 is a little different here, too, in a different way. It's taller because we've got text wrap right here. So, should these numbers be in the bottom of the cell? Although equally available here, these are much less likely to be used.
The three buttons that we see here above the right, center, and left buttons are called, respectively, Bottom Align and then Middle Align and then Top Align. Let's choose Top Align and watch those cells in row 14. They go to the top of the cell. Maybe we want middle align, like that, or bottom align. At least we've got a choice, and sometimes those will come into play. Ultimately we've got a lot of choices and I've depicted in the upper part of this worksheet, although we'd never actually see the data like this, the various combinations that we can have here.
In these three examples here in Column B, recognize that they are left aligned. The top one here is aligned on the top as well as left. Center left, and again, keep an eye on these icons as we make these changes. These three are all in the middle, and that's left, right, middle, but this one's on the top, center, and bottom and so on. You see all the rest of these. This is merely a depiction of the various ways that we can line up information in cells. Again, be wary of doing this with numbers, particularly computational numbers.
Centering ID numbers, nothing wrong with that, of course, but the other options, not a good idea. Different ways to align the data within cells.
- Adjusting fonts, cell borders, and fills
- Aligning text
- Indenting data
- Setting special formats for dates, times, phone numbers, zip codes, and more
- Creating formats based on formulas with conditional formatting
- Applying styles and themes
- Adjusting row heights and column widths
- Adding background images and pictures
- Outlining data
- Printing double-spaced data