Exercise 2: Numbers and Formulas
Cell Names
An Excel spreadsheet is similar to a tic-tac-toe grid, only
much bigger. The squares in the worksheet are called cells. Every cell has a
name. Select a cell with the mouse and you will see a letter highlighted at the
top and a number highlighted on the left. The name of the cell is the
combination of that letter and number. The name of the cell also appears in the
Name Box, above column A.
Moving Around the Worksheet
Pressing Tab moves the cursor to the next cell on the right,
Enter moves it to the next cell down, Shift + Tab moves it to the next cell on
the left. You can also shift the cursor with the arrow keys, Home, End, Page
Up, and Page Down. Use the Ctrl key with the arrow keys to move the cursor the
maximum distance. You can also move the cursor into a new cell by typing the
cell address in the Name Box. Double-clicking on a cell wall moves you to the
edge of a row or column of data.
Entering Characters, Dates, and Numeric Data
Select a cell. Enter text or numbers by typing in the cell
or in the formula bar that is centered directly above the worksheet. Enter
dates as xx/xx or xx-xx. Include the year, xx/xx/xxxx or xx-xx-xxxx, if the
year differs from the current one.
Editing Cell Contents
·
Select the cell
and then click in the formula bar to edit its contents, or select the cell and
type in a new entry, or select the cell; then press F2 to edit directly in the
cell.
·
Click and drag
to move the contents of a cell.
·
Use icons or
shortcut keys: Ctrl + Z and Ctrl + Y to undo or to redo tasks.
Copying
There are
several ways to copy and paste. Use the mouse to highlight the cells that you
want to copy. Do not highlight the
formula bar area! That will delete the
cell contents!
·
Press Ctrl + C.
Highlight the area to copy into. Press Ctrl + V.
·
Click
on the Copy icon. Highlight the area you want to copy into. Click on
the Paste icon.
·
Click the Copy
icon. Highlight the area you want to copy into and press Enter, or highlight
the upper left corner of the area you want to copy into and press Enter.
·
Move the cursor
around until you get a small black cross in the lower right hand corner of the
highlighted area. Click and drag (down or across) to highlight the area
you want the formula copied into. Release the mouse.
Formatting from the Home Tab
·
The Font group
contains icons for the standard text options (Font, Size, Bold, Italics, and
Underline) you’re probably familiar with from Word. It also contains an icon to
add borders to cells. The “A” icons grow/shrink the font size.
·
The Alignment
group contains text alignment options (Left, Center, Right, Indent, and
Decrease Indent). It also has an icon to merge/unmerge cells. Other icons let
you position text vertically in the cell, wrap text, or rotate text.
·
The Numbers
group contains icons for formatting numbers as accounting style, percents, or
comma style. Two icons increase or decrease the number of decimal places. A
pull-down menu gives you other formatting options.
·
The Styles group
provides options for applying special formatting.
Working with Columns and Rows
·
The Home tab,
Cells group has two icons, insert and delete, used to manage formatting for
columns and rows. Options include deleting, inserting, changing column width or
cell height, and hiding/un-hiding columns.
·
You can find
these same options by right-clicking on a column or row heading.
·
You can also use
the mouse to change row heights and column widths. Just click and drag between
the row or column indicators. To fit a column width to the width of its
contents, double-click between column or row indicators.
The Format Dialog Box
·
You can access
this dialog box in several ways. First select a cell or group of cells with the
mouse. Note: To format large areas, you can select column headings to format
columns, row numbers to format rows, or the blank cell above the numbers column
to format all cells.
·
Then right-click
or select the Format icon from the Home tab, Cells group, and choose the Format
Cells option. Pressing Ctrl + 1 will
also work.
·
The numbers tab
is used to change the numbering style.
·
The alignment
tab lets you choose how letters are displayed within a cell.
·
Other tabs give
you more options.
Copying Formatting
·
Select a cell
with the desired format.
·
Click the
Paintbrush icon.
·
Drag the mouse
across cells, row numbers (to select a row), or column letters (to select a
column) to apply the format. You can
also use the arrow keys to quickly apply the format to any cell next to your
starter cell.
The AutoFill Handle
·
Position the
cursor in the lower right hand corner of a range.
·
A small +
appears. Drag in any direction.
·
Use AutoFill to enter
a series of dates, months or days.
·
Use AutoFill to
extend a series of numbers.
·
Use AutoFill to
copy formulas.
·
Use AutoFill to
copy text.
AutoSum and Ranges
A range is any group of
cells that form a rectangle. You describe a range using the names of the cells
in the upper left and lower right corners of the rectangle. The cell names are
separated by a colon. B3:F5 is a range
containing 15 cells.
The
AUTO-SUM icon creates a function that returns the sum of a selected column,
row, or range. For example, the cell containing the formula = SUM(C3:G9) displays the sum of all of the numbers in a
rectangle with corners at C3 and G9. The icon is found in the Editing group on
the Home tab.
To use AUTO-SUM,
·
Position the
cursor in a cell where you want a total and click on the icon. Use the mouse to
select the range of cells whose contents will be included in the total.
·
Highlight a
column of data (include a blank cell at the bottom). Click on the icon.
·
Highlight a row
of data (include a blank cell at the right). Click on the icon.
·
Highlight a
range, including a column of blank cells to the right and a row of blank cells
below the range. Click on the icon to quickly total all the rows and columns.
·
Position the
cursor in a cell where you want a total. Click on the icon. Hold down Ctrl
while selecting cells from different areas.
Validation
The
validation feature is found on the Data tab, Data Tools group. There are three
tabs in the dialog box. The Settings tab is used to restrict the entry of data
to specific types of data. The Input Message tab tells users what type of data
to enter. The Error Alert tab is used to help users when they have made a
mistake. Validation rules are applied to individual cells, or to ranges of
cells.
Order of Operations (PEMDAS)
If
a formula contains multiple calculations, Excel calculates the parts of the
formula in this order: calculations in parentheses, then percentages, then
exponentiation, then multiplication and division, then addition and
subtraction, always moving from left to right. Use parentheses to force Excel
to do the calculations in the order you want.
For
example = 3+4*2 will display 11, since Excel would do the multiplication first,
while = (3+4)*2 will display 14.
Working with Multiple Worksheets
Click on a worksheet tab to
move into a different worksheet. By right-clicking on a worksheet tab, you can
·
Insert and
delete worksheets,
·
Move and copy
worksheets,
·
Rename a
worksheet,
·
Select multiple
worksheets to work on simultaneously,
·
Add colors to
the tabs.
Moving around in the worksheet
Every cell (box) has a
name, the letter of the column it is in followed by the number of its row. The
worksheet opens in cell A1. This is called the home cell. Notice that the
letter A above it and 1 to the left of it are highlighted to indicate which
cell you are currently working with.
There is a thick box border around the cell.
1. Use the mouse to click on another cell to select it.
Notice how the letter above it and number to the left are highlighted, and the
thick box border moved.
2. Find the cell name in the box in the upper left hand
side of the display.
3. Type G6 in the cell name box. Press Enter.
4. Press Enter, Tab, and Shift Tab.
5. Press the arrow keys.
6. Press and hold down an arrow key. The cursor scrolls
in that direction.
7. Press Page Down to move down one screen. Press Page
Up.
8. Press the End key with the down arrow key to move to
the bottom of the sheet.
9. Press the End key with the right arrow key to move to
the far right column.
10. Press Ctrl + Home to return to Cell A1.
Formatting Methods
1. Enter letters in cells A1 and B1, numbers in cells A2
and B2.
2. Widen the columns by clicking between letters in the heading
row and dragging.
3. Change the height of the rows by clicking in the
number column and dragging.
4. Notice that words are left aligned, numbers are right
aligned.
5. Click on Cell A2 and press the centering icon.
6. Click on the letter B and click on the centering
icon.
7. Click on the number 2 and click on the left alignment
icon.
8. Use the mouse to select cells A1 to B2. Press Delete.
9. Click on the tab for Sheet 2.
Working with numbers
1.
In cells A1-A4,
enter the numbers 2,3,4,5. Click and use your mouse to highlight cells A1 to
A4. Release the mouse button. Move the cursor until a tiny + sign appears in
the lower right hand corner of cell A4. Click and drag down four rows. The
numbers 6, 7, 8 and 9 should appear. This AutoFill handle is a quick way to
extend series of numbers, dates, months, and days of the week. You can use it
to copy formulas, too.
2.
Change the 2 in
cell A1 to a Currency format. To access the Format dialog box, first select the
cell, then right-click, or press Ctrl + 1, or use the format selection
pull-down box in the Numbers group on the Home tab. Find the Numbers tab and choose Currency.
3.
Change the
format of the 3 in cell A2 by clicking on the $ icon. This icon applies the
Accounting format to numbers. The dollar sign displayed in cell A2 is to the
far left of the number 3. Look at cell A1 to compare the two formats.
4.
You can use the
paintbrush icon to quickly copy formatting into other cells, rows, columns, or
worksheets. Click on cell A1. Click on the Format Painter icon. Click the down
arrow. The dollar sign in cell A2 will move next to the 3.
5.
Click on cell
A9. Type 9 numbers at random and press enter. A number will appear in scientific notation
x.xx E+8.
6.
Click again in
cell A9. Click on the Comma icon.
7.
Railroad tracks (#####)
may appear. This means the column is too narrow. To fix this problem, widen the
column. One way is to move the cursor between the letters A and B and
double-click. Another way is to click and drag in the column heading
8.
With cell A9
selected, Click on the increase decimals and decrease decimals icons to see
what happens.
Formulas
1.
In cell C1, enter
the math formula 4 * 7. You may be surprised that the number 28 didn’t appear
in cell C1. That’s because Excel formulas have to start with an arithmetic
symbol, usually an = sign.
2.
In cell C2,
enter the math formula = 4 * 7. This time you’ll see the result of the formula,
28, appear in the cell. Click on C2 and look at the formula bar to see the
actual formula. Excel shows formulas in
two ways. The actual formula appears in the formula bar. The result of the calculation
appears in the cell. This is like a flashcard.
3.
In cell C3,
enter the formula = 9 / 3.
4.
In cell C4 enter
the formula = 5 + 6.
5.
To see all of
the formulas in the worksheet, press Ctrl + ~ . This is a toggle switch. One
view, what I call mirror view, shows all of the formulas in the worksheet. The
other view, flashcard view, shows the results of the calculation in the
worksheet. Flashcard view is the default. Mirror view is used for checking
formulas. It does not show formatting, and numbers are left aligned. Press Ctrl
+ ~ to return to flashcard view.
Use cell references in place of the numbers
1.
Delete the
formulas in column C. Select the cells and use the delete key.
2.
In cell C1,
enter the formula = 5 + 6.
3.
In cell C2 enter
the formula = A4 + 6. In cell C3 enter the formula = A4 + A5.
4.
The results of
the calculations in cell C1, C2 and C3 are the same, 11. You can use either
numbers or the names of cells containing numbers when you write formulas.
5.
Change the
number in cell A4 to an 8. Notice that the results displayed in cell C2 and C3
changed to 14. This is what makes Excel both POWERFUL and DANGEROUS. If you use
formulas containing cell references, changing a number in one cell could cause
other cells to change what they display.
Copying formulas into new cells
1. Switch to mirror view by pressing Ctrl + ~.
2.
Copy and paste
the formula from cell C3 to cell C4. Notice the row numbers in the cell
references changed from a 4 and a 5 into a 5 and a 6.
3.
You can quickly
copy a formula into a neighboring cell. Click on cell C4. Type Ctrl + C. Press
the down arrow key and then Enter. Notice that the row numbers in the formula
changed again. If you copy formulas into a different row, the numbers in any
cell references will change. If you copy formulas into different columns, the
letters in any cell references will change.
4.
You can turn
this automatic adjustment feature off. Edit the formula in C5 to include a $ in
front of one of the row numbers. The $ tells Excel “Don’t change the part of
the cell reference following this sign when copying this formula.” You can use
a dollar sign in front of the letter, or the number, or both.
5.
The AutoFill
handle can be used to quickly copy formulas. Click on cell C5. Move the cursor
until a tiny + sign appears in the lower right hand corner of the cell. Click
and drag down over cells C6, C7 and C8. One row number changed, and the other
one didn’t.
6. Copy the contents of cell C2 into cell D2. Notice the
A becomes a B.
7. Edit the formula in cell C3 to include a $ in front
of one of the As.
8. Copy that formula into cell D3. Notice what happens
to the letters.
Absolute Cell References
1.
Edit the formula
in cell C3 to include dollar signs in front of the letters and the numbers.
This format, with two dollar signs in the cell reference, is called an absolute
cell reference. The cell reference will stay unchanged wherever you copy the
formula. Cell references that contain one dollar signed are called mixed cell
references. The part without the dollar sign can change in mixed cell
references.
2.
Copy this
formula into 2 or 3 other cells. Notice that this formula is unchanged.
Range notation lets you work with groups of cells
A
range is a rectangular block of cells. A range is described by the cells found
in its two opposite corners, separated by a colon. C3:F8 is a range. Using the =SUM
function is a quick way to add up all the numbers in a range of cells.
1.
Delete all the
contents of the worksheet.
2.
Press Ctrl + ~
to switch to flashcard view. Enter
numbers in cells D1,D2 D3, E1,E2,E3.
3.
In cell E5 Type
=SUM(D1:E3) - a range can be a rectangle shape.
4.
Edit the formula
in cell E5 to =SUM(D1:D3) - a range can be a column.
5.
Edit the formula
in cell E5 to =SUM(D1:E1) - a range can also be a row.
1. In cell A1 type “cashiers.” In cell B1 type “Cash Sales.” In C1 type
“Actual Cash.” In D1 type “Over/(Short).” Edit cell A1 to change “cashiers” to “Cashiers.”
(Press F2, or type directly in the address bar, or type directly in the cell.)
2. In cells A2, A3, A4 enter the names of three people.
Use the mouse to select cells A2, A3, and A4. Press the AZ icon to sort the
names alphabetically.
3. Select the heading letters A to D. Double click to
the right of the D to widen the columns.
4. In cells B2, B3, and B4, enter the numbers 120, 80,
and 160. These were the cash register readings. In cells C2, C3, and C4 enter
the numbers 125, 80 and 158. This is the amount of cash turned in by the
cashiers.
5. Was the amount of cash turned in by the cashiers over
or short of the expected amount? To find out we subtract the cash register
total from the amount turned in. In cell D2 enter = C2 – B2. Since we want to
perform the same operation in each row, we can copy the formulas and have Excel
automatically change the row numbers in the cell references. Use the AutoFill
handle to copy this formula into cells D3 and D4. Examine the formulas in mirror
view.
6. Right-click on the B and insert a new column. In cell
B1 type “# of Sales.” Notice that when you inserted the new column B, the cells
that contained your old data all shifted one column to the right. Excel
automatically adjusted your existing formulas to correct for this. In cells B2,
B3, and B4 enter 25, 21, and 30.
7. Switch back into formulas view. Widen the column.
Change the format of the range C2 to D5 to currency style.
8. In cell A5, enter the word “Totals.” Select cells B2,
B3, B4, and B5. Press the AutoSum icon.
Delete the contents of cell B5. Select the range B2:E5. Press the
AutoSum icon. This is a quick way to apply the SUM function to several columns
of numbers.
9. In cell F1 enter “Avg Ck.” In cell F2 enter the
formula = C2 / B2. Copy the formula in cell F2 into cells F3 and F4.
10. Use the Center
icon to center all of the cell contents in columns B through F.
11. Right click on the 1 and insert a new row. Select
cells A1 to F1 and use the merge and center icon to create a single cell
spanning the first 6 columns. Type “Java Juice – Cashier Report” in the cell.
Add a background color to the cell and change the font color. Add a heavy
border around the cell.
12. Add “all borders” to the range A2:F5.
13. Add a background color to the range A2:F2.
14. Add a background color to the range A3:A5.
15. In cell A8 enter “Date.” In cell C8 enter today’s
date. You can separate the month and day with either a dash or a slash. You do
not need to enter the year, unless you want a date to refer to a different year
than the current one. 1-15 or 1/15 will both give you January 15th.
Dates can also be formatted (Ctrl + 1).
16. Look at the date in Mirror view. Don’t panic when you
see a large number. Excel gives a number to each day to make it easier to sort
dates and to calculate the length of time periods. Day 1 was January 1, 1900.
Day 36526 was January 1, 2000. Note: the Excel programmers counted 1900 as a
leap year. It wasn’t. Switch back to formula view.
17. Change some of the numerical data to see how the
formula results change.