Excel 2007 Tutorial 2.1. Formatting Spreadsheets

In this video, we will be discussing the formatting options found in Microsoft Excel.
We will be working with a file which can be found at this web address. The file contains
a list of college courses, textbooks, and prices.
When we look at the ISBN column in Excel, it shows the number with scientific notation
in some of the cells because these cells are too narrow.
We can fix this by resizing the column. We can resize the column by moving our mouse
between the column labels, waiting for the cursor to turn into a vertical bar with horizontal
arrows, and dragging the edge of the column. We can also have the column resize itself
to an appropriate size by simply double clicking between the column labels.
You may notice that we lost the leading zero in most of our ISBNs. Only one ISBN did not
lose the leading zero, because that ISBN contained the letter X and was treated as text. In all
other cases, the ISBNs were treated as numbers and the leading zero was dropped.
If you look at the top of the Home ribbon, there is Number Format dropdown box showing
the word General. This indicates that this cell is currently formatted as a General data
type, which means that Excel will attempt to pick the best format for the data. In our
case, we want the cell to be formatted as Text, so the leading zero is not lost.
Notice that when we reenter the leading 0, Excel now shows us a warning message indicating
that “The number in this cell is formatted as text or preceded by an apostrophe.” We
can click on the drop down arrow next to the warning exclamation mark and tell Excel to
ignore this error. Another way that we can make sure that Excel
shows our leading zeros is preceding the leading 0 with an apostrophe ‘. This indicates that
the contents of the cell following the apostrophe will to be treated as text, even though the
cell still has a General format. When we enter text that exceeds the size of
the cell, the text will appear to overflow into other cells unless those adjacent cells
are filled with content. We can resize the cell to show all of the text or we can have
Excel word wrap the cell by clicking on Wrap Text in the Home ribbon.
We have two columns in our spreadsheet containing dollar amounts. When we look at the Number
Format drop down in the Home ribbon, we can see that there are two ways we can format
cells containing money. To see the difference, let’s select Currency for the first cell
of the wholesale column and Accounting for the first cell of the retail column. Notice
how the dollar sign in the Currency format is positioned right next to the number, while
in the Accounting format, the dollar sign is spaced away from the number.
If we resize the column, we find that with the Accounting format, the dollar is always
as far left in the cell as possible. Another way we can quickly access the Accounting format
is by clicking on the dollar sign in the Home ribbon. Accounting format allows the columns
and dollar signs in different cells to line up with each other.
You may notice that some of the numbers in the column have different levels of decimal
precision. We can click on Increase Decimal Places in the Home ribbon to increase the
number of places shown to the right of the decimal point. However, our numbers are still
not lining up with the Accounting formatted cells at the top. If we only want to show
the dollar sign for the first row, we can click on the comma style icon in the Home
ribbon and our columns will line up because this icon applies the Accounting format, but
without currency symbols. Let’s add a couple of columns. First, we
will add a Quantity column and fill in some numbers.
Then, we will add a Cost column that will be our Quantity multiplied by Wholesale price.
We will enter the equal sign, select the quantity cell, enter an asterisk * for multiplication,
and select the wholesale cell. We can use the fill handle to fill the entire column
with this calculation. We can then select the new cells and click
on the Comma style, so the cells are formatted in Accounting format, but without the currency
symbol. Let’s select our data and move it towards
the center of the spreadsheet. I know I am moving the cells when I drag the selection
because the mouse cursor has changed to a black four headed arrow with a white pointer
cursor. Do you notice that some of the values in the
cost column have been replaced by number signs? This means that the column is too small for
the data that it contains. If we adjust the column, the number signs will disappear and
we will see our numbers again. Our columns are all different sizes, but we
can quickly and easily adjust that by holding down the Control [Ctrl] key while pressing
[A] to select the entire spreadsheet, and then double clicking between any two columns,
adjusting all of the columns simultaneously. We can add a row of totals, using the AutoSum
button to sum our columns. Autosum creates a formula using the Sum function, predicting
what cells we want to total. Let’s add some borders to make the data
a little clearer, separating the headings and the totals from the data.
Selecting the heading row of cells, we can click on the border icon in the Home ribbon,
giving the cells a bottom border. Selecting the totals row of cells, we can
click on the dropdown arrow next to the border icon, and select a top and double bottom border.
Let’s say we want to add a large title to the top of spreadsheet. We can select multiple
cells and click on Merge & Center in the Home ribbon. This combines multiple cells into
one cell with centered text. If we wanted to Unmerge these cells later, we can click
on the dropdown and select Unmerge. Let’s add some text and apply some text
formatting. We can go into more detailed formatting options by right clicking on the cell and
selecting Format Cells. This allows us to specify exactly how we want data to appear.
We might want to add conditional formatting, such as drawing attention to any orders containing
more than 40 of a specific book. We can do this by selecting a cell, clicking
on the Conditional Formatting dropdown in the Home ribbon, and then selecting a Highlight
Cells Rule, highlighting cells that are greater than 40.
If we were to use the fill handle to propagate these changes to other cells, we would be
replacing the data in those other. We definitely do not want to lose our data, so instead of
using the fill handle, we can use the Format Painter to apply this format to our other
cells. First, we select the cell containing the format that we want to use. Then we click
on Format Painter in the Home ribbon. Then we select the cells we wish to apply the format
to, and the format is copied to those cells without altering the data in those cells.
Another interesting feature we can find under conditional formatting is the Data Bars feature,
which shows a miniature bar in the background of the cell showing the value of this cell
in comparison to the other cells. Finally, let’s say we do not like the formatting
we have created so far and we just want to start over, but we do not want to lose our
data and formulas. We can select our entire spreadsheet, again by pressing [Ctrl] + [A],
and click on the Clear dropdown in the Home ribbon, and select Clear Formats. This removes
all of the formatting, but without removing any of the formulas or data.
If we wanted to, we could apply Clear Formatting to just a single cell, instead of all of our
spreadsheet.