Join Dennis Taylor for an in-depth discussion in this video Adjusting horizontal and vertical cell alignment, part of Excel 2010: Advanced Formatting Techniques.
Most Excel users, even beginning Excel users quickly, have become proficient with alignment formatting. For example, in these cells right here, some of us would leave the cells looking just the way they are, but if we'd like to right-align the data in the Alignment group on the Home tab in the ribbon, surely right alignment makes sense. Center may be your preference, either way it can be changed. In column G, these cells here look perfectly fine in their default location. Notice that sometimes you don't see any alignment buttons that appeared to have been chosen, so the default location, left alignment of nonnumeric data is automatic.
We might want to center these or even right align them. Occasionally that looks better to some people's eyes. So these are pretty easy to get to and most Excel users know them pretty readily and quickly. I might point out here that with numeric entries, there is some other thinking going on that might occur to you. I have seen data like this where people would like to center data and I want to suggest that that's a bad idea. If you are dealing with accounting type information, particularly when it has decimals, centering numbers is not a great idea. What you might find too is if you try to center these, as I'm about to do here, that maybe what happened here? A mix of reactions.
And Excel is a little bit inconsistent about this. Look what happens in these cells here, and earlier I just try this but it didn't change. I am trying to left align it. I am trying to center it. Now there is a slight wiggle there with a dollar sign moving around a little bit, but the data itself isn't. Now these are in Accounting format activated or created with the dollar sign button here. If you use the keystroke shortcut for applying currency, or if you happen to just right-click, go to Format Cells and choose Currency Format, and maybe concerned about negatives isn't an issue here, but we want two decimals.
The dollar sign. Let's click OK. We have obvious change there with the dollar sign, which you may have seen before, but try centering the numbers now. We can. Now again this is a bad idea, I think, and left aligning is too. But the fact that you can't do with some formats and not others can be a little bit confusing. With numerical information, keep it on the right side. If it's strictly numbers and you don't have dollar signs and/or commas, maybe it's still in General format. Again, think out the issues of how you want alignment to occur, but as a general rule unless the numbers happened to be ID numbers, keep them in their native right-aligned state. They look best like this.
Now, new in Excel 2007 and also in Excel 2010, we have available already without any kind of customizing alignment buttons for aligning data in the top, middle or bottom of cells. And in this particular worksheet, the upper-left corner is a graphic that more or less depicts what we mean by these. And this is pretty apparent once you try one or two of these buttons. This data of course says top or left and you see the two icons that are currently active in the Alignment group on the Home tab here.
Top, Middle, and again notice how the change here. So the three lower alignment buttons have to do with the left to right alignment of data within cells. The three upper buttons have to deal with the top down alignment. So as you might expect, as we click here, we are still within the left to right. Actually we call it middle this time. Sometimes you see the words center and middle are used interchangeably, but you get the idea pretty quickly as you compare these with those different combinations that we have here.
And many times when you're considering centering data in the top bottom cells, it's pertinent when the cell is taller than usual. If we had, we just normal data here and these rows where we have typical height, obviously this graphic doesn't make a lot of sense right now. But do think of those situations where perhaps you've got wrapping text and when you do, then you want to consider how the text appears within a cell. The alignment buttons as we've seen them being used here and prior in the data here are easily accessible in the Alignment group on the Home tab of the ribbon.
- Saving time with keyboard shortcuts
- Adjusting fonts, cell borders, and fills
- Setting themes
- Formatting numeric data with cell formats
- Using conditional formatting
- Hiding repeating column information
- Printing double-spaced data
- Elbow formatting
Skill Level Intermediate
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.