Chapter 3 - Introducing Excel

Chapter 3 - Introducing Excel 1

Beginning Excel 1

Exercise 1 - Beginning Excel 5

Exercise 2 - Numbers and Formulas. 6

Exercise 3 – Cashiers’ Report 8

Exercise 4 - Practicing With Excel Formulas. 11

Exercise 5 - Campus Life. 12

Exercise 6 – Copying Formulas. 12

Exercise 7 – Creating a Times Table. 14

Beginning Excel

Identifying the Parts of the Excel Worksheet

• The Workbook Environment is similar to a tic-tac-toe grid, only much bigger.
• Close the Task Pain if it opens when you open Excel.
• Turn on the Standard and Formatting toolbars from the View menu, Toolbars submenu.
• Use the Tools menu, Customize submenu, Options tab to Show toolbars on two rows. Select all options except large icons, and then click Close. You’ll see this:

Note the “Currency” icon is mislabeled. It is actually “Accounting“ style.

Moving Around the Worksheet

• Cells have names, consisting of a letter and a number. 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 selected cell also appears in the Name Box, above the letter A.
• Tab moves you to the right, Enter moves you down, Shift + Tab moves you to the left.
• Other ways to move around: the Arrow Keys, Home, End, Page Up, Page Down
• Use the Control key with the arrow keys to extend their effect.
• Typing a cell name in the Name Box moves the cursor into that cell.
• Control + Page Up or Control + Page Down shifts between worksheets.
• 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.
• Or, enter data in the formula bar that is centered directly above the worksheet.
• Enter dates as xx/xx or xx-xx. Enter the year if it differs from the current one.
• Ways to Edit Cell Contents:
1. Select the cell and then click in the formula bar to edit its contents.
2. Or, select the cell and type in a new entry.
3. Or, select the cell; then press F2 to edit directly in the cell.
• Click and Drag to Move Data.
• To Undo / Redo use icons or use shortcut keys: Control + Z and Control + Y.

Copying Formulas

There are several ways to copy and paste. Use the mouse to highlight the cells that you want to copy from. Do not highlight the formula bar area! That will delete the cell contents!

· Press Ctrl + C. Highlight the area you want to copy the formula into. Press Ctrl + V.

· Click on the Copy icon. Highlight the area you want to copy the formula into. Click on the Paste icon.

· Click on the Copy icon. Highlight the area you want to copy the formula into. Press the Enter key.

· From the Edit menu, select Copy.  Highlight the area you want to copy the formula into. From the Edit menu select Paste.

· 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. 

Making Formatting Changes

• Formatting from the Toolbar
1. The five Standard Text options (Font, Size, Bold, Italics Underline)
2. Aligning Text and Labels – Icons (Left, Center, Right, Indent, Decrease Indent)
3. Merge/Unmerge Cells Icon (Toggle Switch)
4. Adding Cell Borders and Shading – Icons
5. Use icons for Percents, Accounting, or Comma Styles to format numbers
6. Use the decimal icons to change the number of decimal places displayed
• The Format Dialog Box is used to format Cells or Ranges
1. Select a cell or range of cells. A range is a group of cells forming a rectangle.
2. To access the dialog box: Right click, Press Ctrl + 1, or use the Format Menu, Cells Submenu.
3. Change the numbering style from the Numbers tab.
4. Change how letters are displayed within a cell using the Alignment tab.
5. Change Font Style, Size, and Color from the Font tab.
6. Add backgrounds with the Borders and Patterns tabs.
• Format Rows and Columns
1. From the Format menu, you can change the height and width, or hide and unhide, Rows and Columns.
2. You can also use the mouse for some tasks.
3. To change row heights and column widths, click and drag between the row or column indicators.
4. To fit a column width to the width of its contents, double click between column or row indicators.
• Shortcuts

1. The AutoFill Handle quickly copies text and formulas:

a. Position the cursor in the lower right hand corner of a range.

b. A small + appears. Drag in any direction.

c. Use AutoFill to enter a series of dates, months or days.

d. Use AutoFill to extend a series of numbers.

e. Use AutoFill to copy formulas.

2. To format large areas: Select letters to format columns, numbers to format rows, or the blank cell above the numbers column to format all.

3. To copy formatting, select a cell with the desired format. Click the Paintbrush icon Use the mouse to select the cells, row numbers, or column letters where you want the desired format to be applied.

AutoSum

• The AutoSum icon (Σ) gives 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.
• To use AutoSum, first position the cursor in a cell where you want a total. Then click on the icon. Use the mouse to alter the shape of the range of numbers you want included in your total.
• Or, highlight a column of data (include a blank cell at the bottom). Click on the icon.
• Or, highlight a row of data (include a blank cell at the right). Click on the icon.
• Or, 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.
• Or, position the cursor in a cell where you want a total. Click on the icon. Hold down Control while selecting cells from different areas.

Validation

• The validation feature is found on the Data menu. You can use all the tabs, any combination of tabs, or a single tab.
• 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.

Working with Multiple Worksheets

• Click on a worksheet tab to move into a different worksheet.
• By right - clicking on a worksheet tab, you can:
1. Insert and Delete Worksheets,
2. Move and Copy Worksheets,
3. Rename a Worksheet,
4. Select Multiple Worksheets to work on simultaneously,
5. Add colors to the tabs.

Exercise 1 - Beginning Excel

Open Excel

From the Tools menu, Customize submenu, Options tab, select all options except “large icons.”

From the View Menu, close the task pane, if it is open. (Uncheck the box next to it)

From the View Menu, select Toolbars.

Only the Standard and the Formatting toolbars should have checks next to them. Remove any checkmarks next to the other toolbars.

Notice that the menus show icons and shortcut keys next to some of the menu choices. These are alternative ways to perform the same task.

Moving around in the worksheet

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 Tab, Enter, and Shift Tab.
5. Press the arrow keys. Press and hold one down to scroll.
6. Press Page Down to move down one screen. Press Page Up.
7. Press the End key with the down arrow key to move to the bottom of the sheet.
8. Press the End key with the right arrow key to move to the far right column.
9. 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 letters row and dragging.
3. Change the height of the rows by clicking in the number area 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.

Build a small table

1. In cell A1 type Fun Things to do. Press Tab, Enter, or an arrow key.
2. Click on the letter A. Drag to widen this column.
3. Click on cell A1 again. Press F2. Change A1 to say Fun Things To Do.
4. Press Ctrl + Z to reverse the correction.
5. Click in the formula bar (The area above the center of the worksheet.)
6. Change A1 to say Fun Things To Do.
7. In cells A2-A4, list 3 of your friends.
8. Select cells A2-A4 by highlighting them with your mouse.
9. Click on the A-Z icon to sort the names. If the names don’t sort correctly, try again, but this time include cell A1.
10. List an activity in column B next to each name.
11. Use icons to apply color formatting to font and background in cell A1.
12. Click on the number 2 in the row indicator column. Click on the fill color icon.
13. Select cell A2. Click on the paintbrush icon. Use the mouse to select the numbers 3 and 4.
14. Use the borders icon to apply a Heavy Border to cell A1.
15. Select cells A1 and B1. Click the Merge and Center icon. Click the icon twice more.
16. Press Ctrl + A to select all cells.
17. Press Ctrl + B to apply Bold format. Press Escape.

Close Excel

Exercise 2 - Numbers and Formulas

Working with numbers

1. In cells A1-A4, enter the numbers 2,3,4,5. Left 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, press Ctrl + 1, or use the Format menu. 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. It is mislabeled as Currency. 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 7-9 numbers at random and press enter.
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 formula shows 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. This is the default for formulas. If you copy them into a different row, the numbers in any cell references will change. If you copy them 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. See how one row number changed, and the other 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 A’s
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 its two opposite corners, separated by a colon. =SUM is a quick way to add up all the numbers in a range of cells.

1. Press Ctrl + ~ to switch to flashcard view. Enter numbers in cells D1,D2 D3, E1,E2,E3
2. In cell E5 Type =SUM(D1:E3) - a range can be a rectangle shape.
3. Edit the formula in cell E5 to =SUM(D1:D3) - a range can be a column.
4. Edit the formula in cell E5 to =SUM(D1:E1) - a range can also be a row.

Exercise 3 – Cashiers’ Report

Tasks:

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. 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.

4. Was the amount of cash turned in by the cashiers over or short of the expected amount? If you want Excel to do any calculations, start with an = sign. In cell D2 enter = 125 – 120. In cell D3 enter = 80 – 80. In cell D4 enter = 158-160. Look at the results of the formulas in cells D2, D3 and D4. Do the results make sense?

5. What if the cash numbers changed in columns B and C? We would need to use different numbers in the formulas in column D. We don’t want to write a new formula every time a number changes in a cell, so we need a shortcut. Instead of using numbers in our formulas, we can use the name of the cell that contains the number. This is called a cell reference. Edit cells D2, D3, and D4 to contain cell references. = C2 – B2, = C3 – B3, = C4 – B4. Did the results displayed in cells D2, D3 and D4 stay the same?

6. Notice that the formulas are not displayed in the worksheet, only the results of the formulas are showing. The actual formula for the selected cell appears in the formula bar above the worksheet. This is the default view, which I call the Flashcard view. If you want to see the formulas in the cell, press Ctrl + ~. This is what I call mirror view, since both the formula bar and the cell display the same information. Mirror view is great when you need to find mistakes in formulas. Press Ctrl + ~ again to return to the flashcard view.

7. In cell A5, enter the word ‘Totals.” In cell B5 enter = 120 + 80 + 160. In cell C5 enter = C2 + C3 + C4. In cell D5 enter =SUM(D2:D4) These are all methods for adding groups of numbers. The last method is the most powerful one. It’s designed for working with several cells arranged in a rectangle shape. The shape is called a range. To use a range in a formula, list the first cell in the upper left hand corner of the range, a colon, and then list the cell in the opposite corner.

8. Delete the contents of cell B5. Highlight cells B2, B3, B4, and B5. Press the AutoSum icon. Press Enter. This is a quick way to create the SUM formula to add a column of numbers.

9. Insert a new column between A and B. In cell B1 type “# of Sales.” In cells B2, B3, and B4 enter 25, 21, and 30. Copy the formula from cell D5 into cell B5. Switch to mirror view (Ctrl + ~). What changed in the formula that you copied from cell D5? When you copy a formula from one column to another, the letters in the cell references change by default. 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.

10. In cell F1 enter “CC Sales.” In cell G1 enter “Total Sales.” In cell H1 enter “Avg Check.” In cells F2, F3 and F4 enter 80, 40, and 120. In cell G2 enter the formula = C2 + F2. Did you get 200? In cell H2 enter the formula = G2 / B2. Did you get 8?

11. Copy the formulas in cells G2 and H2 down into the next 2 rows (Into cells G3, G4, H3 and H4). Did the results in column H show decimal places? You can increase or decrease the number of decimal places using icons on the tool bar. Change cells H2, H3 and H4 to show 2 decimal places.

12. Switch to mirror view (Ctr + ~). What changed in the formulas that you copied from cells G2 and H2? When you copy a formula from one row to another, the numbers in the cell references change by default.

13. Change the format of the data in the "# of sales" column to Number Style. (Ctrl + 1) Change the format of the Cash column to Currency style. (Use the Format menu) Use the paintbrush icon to copy the currency style into columns DEFG. Use the currency icon to change the Average Check column to Accounting style. Use the Center icon to center all of the cell contents in columns B through H.

14. Insert a new row 1. Use the merge and center icon to create a heading cell across the top of the first 8 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.

15. Insert a new row below row 4. Highlight the area from A4 to H4. Copy down into the new row. Use mirror view to examine the new formulas. What changed and why?

16. Use the undo icon (or Ctrl + Z) to reverse the copying. Edit the value of the formulas to include dollar signs before the numbers in the cell references. Copy the formulas down again. What parts of the formulas changed this time? What effect did the dollar signs have?

17. Delete the row (5) that you just added.

18. 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 15^th. Dates can also be formatted (Ctrl + 1).

19. In cell A9 enter “Accounting Entry:” Use the Merge and Center icon on cells A9, B9 and C9. In cell A10 enter “Cash (DR).” In cell A11 enter A/R-CC (DR).” In cell A12 enter “Sales Rev (CR).” In cell B11 enter = SUM(A3:A5). In cell B12 enter = SUM(F3:F5). In cell C13 enter = SUM(G3:G5).

20. Use the Accounting Format icon (this is a $; it is mislabeled as Currency) and the Increase Decimal icon to format cell C13 as Accounting style, with 2 decimal places. Use the format paintbrush to copy the format from cell C13 into cells B11 and B12.

21. Change some of the numerical data to see how the formula results change.

Exercise 4 - Practicing With Excel Formulas

When you enter a formula into a cell, you see the formula in the “formula bar” above the worksheet. If the formula also appears in the cell, you are in “mirror” view. If the formula does not appear in the cell, you are in “flashcard” view. Switch between the two views by simultaneously pressing Ctrl + ~ on the keyboard. Do this exercise in flashcard view.

Construct the Excel Worksheet as shown

	A	B	C	D
1	2	3	4	2
2	5	6	2	12
3	3	2	5