Hello again.
In this section, we’re looking again at formatting a worksheet in our course on Excel
2013.
In the previous section, we looked at things like inserting and deleting columns and rows.
In this one, we’re going to look at a little bit more detail on formatting a worksheet.
Now I’ve gone back to the table of expenses that we looked at earlier on.
I’ve inserted another column here.
The other column I’ve got here is one that denotes the name of the particular customer
that a particular expense was associated with.
Where there isn’t a customer name then I’ve just put it under the heading of prospects.
This is for potential customers.
And one of the entries here I’ve left blank and I’ll talk about that in a moment or
two.
So having entered this other Column what I’m now going to do is to set about formatting
it in a better way, make it look a little bit more attractive.
And we’re going to use both the work we’ve done already on formatting columns and rows
but also we’re going to introduce theme and style considerations as well.
So let’s get started.
Now one of the first things that I’m going to deal with here is what happens in a situation
like this one where I’ve added another row but the description of the expense is now
even longer than any of the ones that we’ve had before.
And potentially dealing with a description as long as the long that I’ve typed in there
is going to mean that in order to extend the width of Column D enough to accommodate that,
first of all the width of the overall expenses claim is going to get wider and wider and
secondly there’s a lot of wasted space here with the others.
So the first thing I want to show you is that when you’re dealing with text fields, you
can actually use more than one line for a description in this case and you can do that
by wrapping text.
Now if I select Column D, right click, and go into Format Cells, then one of the options
there on the Alignment tab is Wrap Text.
So let’s check Wrap Text, click on OK, and see what happens.
Now at the moment there’s no need for the text to wrap because it’s got plenty of
width.
But supposing I reduced the Column D width down again, watch what happens as I reduce
it.
Now when I get to that point you can see that in two of the entries the text has wrapped.
The other things you can probably notice, first of all, in Row 7 I’ve lost part of
the text and in all of them the text is aligned with the bottom of the cell.
So for instance here, the date in Row 8 is at the bottom of Row 8.
Now you may not particularly want it at the bottom or you may.
But I need to show you how to set that as well.
And this in fact leads us to a very important aspect of formatting in a worksheet in general.
When you’re formatting in a worksheet, what you’re formatting applies to is what you
have selected at that time.
When I set Wrap Text just now, I had Column D selected, the whole of Column D. And remember
there are potentially thousands of rows in Column D. Every row in Column D has the same
setting for text wrapping and that is text will wrap.
If I had chosen say one cell in Column D, let’s say if I’d chosen that one I’d
right clicked on that, gone into Format Cells, and for that one I had checked off Wrap Text.
So I unchecked it, click on OK, and for that one cell alone text is not wrapped.
Now clearly that’s not a very good solution in this case because that’s the worse cell
of all and the one that needs wrapping the most.
But if I select one cell or two cells or three cells and do a particular thing to them that
applies to the selected cells.
If I select the whole column then it will apply to every cell in the column.
Let me select D again, go into Format Cells again, and if you look at Wrap Text now instead
of having a tick in it or instead of being blank it’s got like a little block in it.
Now when you see that it means that some cells in the selection don’t have wrapped text
checked and some cells do.
So what this is telling you is well within your current selection you’ve got a mixture,
you’ve got some text that’s wrapped and some that isn’t.
If I want to put it back so that every cell is wrapped I make sure I’ve got a tick in
it and once again every cell in that column now has text wrapping switched on.
Now let’s apply the same principle say to row height.
With row height, everything seems fine except for row 7 where we seem to have some of the
text clipped.
I could just select Row 7 and then as we saw earlier on if I look on the Home tab under
Format, Row Height.
I could either set it manually or I could say AutoFit Row Height.
Now if I’ve only got Row 7 selected and I do AutoFit Row Height, it will only adjust
the row height on Row 7.
So click that and that’s fixed the problem of not being able to read all of that text.
Now in terms of column width, I can see that the new Column B has a problem so I’m going
to select all of my columns, A to E, go into Format again, AutoFit Column Width.
Now one of the important things to recognize here is that as soon as you set a text column
to wrapping text, then Excel 2013 will not adjust the width of the column to accommodate
the longest piece of text in it.
It knows that it can wrap the text so it has no need to make that column wider.
It just wraps the text.
So there we are.
I can now read everything on this worksheet.
Now I want to look at alignment generally on this worksheet.
There are two forms of alignment; there is Horizontal Alignment and there is Vertical
Alignment.
And I’m going to deal with Vertical Alignment first, mainly because it’s a little bit
easier I think.
I mentioned already that if you look, for instance, at Row 7, the contents of Row 7
are bottom aligned.
So within a cell where you’ve got the date here in A7 the contents are at the bottom
of Cell A7.
And if on the Home tab you look in the Alignment Group, you will see there is a button there,
Bottom Align, which is currently the selected one.
And my current selection is Row 7.
So the whole of Row 7 is bottom aligned.
If I took a particular Cell, say that one with the Currency amount in it, $29.50, that’s
bottom aligned.
If I wanted to change that particular cell to be top aligned it’s as simple as doing
that.
If I wanted to middle slign the whole of the row, select the row and click on Middle Align
and everything is now middle aligned.
So the same kinds of principle apply.
There are a number of other options for alignment.
One of them is to use the Dialog Box Launcher in the bottom right corner of the Alignment
Group.
Here if you click on that you get the Format Cells dialog with the Alignment tab selected
and from here you can set the horizontal alignment and the vertical alignment.
You see at the moment the vertical alignment is set to center.
So what I want to do now is to set vertical alignment for the whole sheet to center or
middle aligned.
So select the whole sheet in the way I showed you before.
We could use the button.
We could use the dialog box.
Let’s use the dialog box.
On vertical note that’s empty.
In the case of vertical alignment, that means that what you’ve got selected is a mix because
at the moment some of it is bottom and some of it is middle or center.
So if I select that and make it center on vertical, click on OK, and now everything
is center aligned or middle aligned vertically.
So now we’re going to look at horizontal alignment and this is really quite a complicated
business in a way because the horizontal alignment usually depends on the type of data that you
have.
Let me give you a couple of specific examples.
When you’re dealing with text like you’ve got in Column D there, it’s usually the
case that text will be left aligned, that is that it will align with the left hand edge
of the column.
And if you take something like the contents of Cell D8, left align means the words go
Emergency Preparation and Dispatch and as soon as you get towards the right hand edge
and there isn’t room to fit the next word in the next word goes on the next line, it’s
left aligned, it’s hard up against that left edge, it carries on.
There is another form of Alignment which is fully justified or sometimes just justified
where the text is aligned with both the left and the right edge and what happens is that
Excel will space out the words that are on each line to make them align with the left
edge and the right edge.
Now this justified alignment is very often what’s used in newspapers and so on to give
nice, neat columns with both edges straight.
The problem is where you’ve got narrow columns that sometimes you finish up with very big
spaces between the words.
So, left alignment very often looks better than fully justified.
But in different situations you may want either.
You may want text to be right aligned.
I mean right aligned text works fine.
It looks a bit strange but it can be used.
So if you take this particular entry here, D8, there’s a set of horizontal alignment
buttons here.
Left align is what we’ve got now.
Center align puts the text in the middle like that.
We’re looking at D8.
And right align makes it look like that.
Now there are other options for the Horizontal Alignment for text.
One of them being justified or what’s sometimes called fully justified.
This is the sort of alignment that’s used typically in newspapers where you have say
a long paragraph of text and apart from the last line for all of the other lines in the
paragraph the text is aligned with both the left and the right edges of the column.
And what then happens is that in order to achieve that the spaces on each of the lines
in the paragraph are made bigger or smaller to make everything aligned on both sides.
It leads to very neat columns of text in a newspaper but when you’re working in restricted
space in a spreadsheet, it can look rather strange.
And I generally rarely used justified myself for horizontal alignment for text.
But you may well come across situations where it’s useful.
And what I want to look at next then is at these numbers because by default numbers are
generally right aligned in Excel.
But if we go into the Dialog Box Launcher and look at alignment, what we’ll find with
that number selected is that the alignment doesn’t say Right, horizontal alignment
says General.
And what this means and General is the horizontal alignment that Excel 2013 applies most of
the time, is that Excel 2013 will align that according to a general set of rules which
means that if it’s a number it will rght align it and if it’s text it will left align
it.
And for other types of data, it will align them according to its own set of rules.
Now you can always override those rules by selecting a row or a column or a cell or a
few cells and applying a more specific rule.
So for instance on this particular cell I could say I want that to be centered.
Click on OK.
Now let me make Column E wider and watch what happens.
Now the General Alignment, remember, is applied to all the cells except the one that I’ve
got selected.
And with all of the others because it’s General and because numbers and particularly
Currencies are by default right aligned in Excel only that selected one is center aligned.
Let’s now turn our attention back to the first column of dates.
Now with the Date column, I’m going to make that quite a bit wider as well.
You can see how that looks and you can see that that appears to be right Aligned.
Of course, if I looked at the alignment in the Format Cells dialog, I’d see that it
says General.
But what if I were to change the Date Format from the one that’s applied at the moment
which is that Short Date Format to this other one, the one where it says Wednesday, March
14, 2012, so the longer format.
Click on OK; let’s see what happens.
Still I have right alignment because that is the default here for dates.
But if you look at that you may think, well, actually that looks a bit strange.
You may not but you may think it looks a bit strange.
But then you know now how to override that.
So you could very simply say, well, how does center alignment look?
Do I prefer that?
Maybe that looks a bit better.
What about left alignment?
So you can experiment to see which one you like and it doesn’t only depend on the data
type because I think if you take dates for example in the Short Date Format, right alignment
looks absolutely fine whereas when you have the Long Date Format I think it’s a bit
more questionable whether that looks good right aligned or not.
So you absolutely should know enough now about vertical and horizontal alignment and in the
next section we’re going to look at one more thing, which is merging and unmerging
cells and then we’re going to start producing some nice headings and formatting of
this spreadsheet.
So I’ll see you
in the next section.