Excel Basics PDF

Preview

Summary

Practice of basic excel...

Description

Excel Basics

This lesson is an introduction to Excel for people who have never used it. Instructions: Read through the set of learning objectives. This is what you should know when you finish the lesson. Complete every step in the assignment, in the order given. Do not skip steps. When you have done all of the steps, fill out the Check for Understanding (CFU) form. A completed CFU for every lesson, in-class and homework, is due to be turned in during your next class. The CFU forms are not to be submitted by email. Where there are differences between software versions, the instructions for Excel 2010 are in Black, for the Mac are in green and for Excel 2013 are in blue. Summary of Key Points: Use the ALT key to locate icons. Move the cursor into a different cell. Select groups of cells. Change the format of numbers in cells. Write simple formulas (equations) using both numbers and cell references (variables). Video – for the MAC - Excel Basics https://www.youtube.com/watch?v=kkNY1ysaOe0&spfreload=10

Moving around in the worksheet

Open an Excel workbook. The workbook opens in Sheet1 with the cursor in cell A1. 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 this cell.

1. Every cell (box) has a name, the letter of the column of the cell, followed by the number of its row. 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 textbox above cell A1, just below the ribbon. This textbox is referred to as the name box. You can move to another cell by typing its name (cell reference) here. An example of a cell reference is H5. 3. Type G6 in the cell name box (the name box, not cell A1). Press Enter to move the cursor into cell G6. 4. Press Enter, Tab, and Shift Tab to move the cursor into other cells. 5. Press the arrow keys to move the cursor into other cells. 6. Press and hold down an arrow key. The cursor scrolls in that direction. 7. You can see the next set of rows by pressing the PageUp and PageDown keys, if you have them on your keyboard. Press Page Down. See how the row numbers change. Press Page Up.

8. You can see the next set of columns by pressing ALT with the PageUp and PageDown keys, if you have them on your keyboard. Press Alt and Page Down. See how the column letters change. Press ALT and Page Up. 9. You can use the End Key with the arrow keys to move around in the worksheet. Press the End key with the down arrow key to move to the bottom of the sheet.

10. Press the End key with the right arrow key to move to the far right column.

11. Press Ctrl + Home to return to Cell A1, or type A1 in the name box and press Enter.

Methods of selecting cells

You can format cells either one at a time or as a group. Formatting can be applied either before or after entering content into cells. Clicking on a cell, typing letters or numbers, and pressing Enter will put what you just typed inside the cell. You can also enter content by typing and then pressing the Tab key or one of the arrow keys. The main difference between these three methods is which cell the cursor moves into after you press the key. 12. Enter some text (words) in cells A1 and A2, and numbers in cells B1 and B2.

13. Widen column A by clicking between the letters A and B in the heading row and dragging to the right. 14. Change the height of row 1 by clicking in the row-number column between the 1 and 2 and dragging down. 15. Notice that words are left aligned, numbers are right aligned. 16. Click on Cell A2 and press on the horizontal centering icon The keyboard shortcut is [ALT H A C] Note: The ALT commands do not work in a MAC. The contents of that cell are centered left-to-right.

Note: For all of the keyboard shortcuts shown in braces, press the keys one at a

time, in a sequence, not all at the same time. Do not include the [ and ] braces when typing the shortcuts. If the shortcut uses a plus sign (CTRL + Z) then the keys should be pressed at the same time. Do not include the parentheses when using these shortcuts. 17. You can select an entire column by clicking on the letter representing that column. Click on the letter B in the column headings row to select column B and click on the horizontal centering icon [ALT H A C]. The contents of that columnare now centered.

There are some easy keyboard shortcuts to use to select blocks of cells. 18. You can select an entire column. Click in cell F4. Press CTRL + spacebar. Click on the Fill Color icon [ALT H H]on the Home tab of the ribbon and add a color to the column.

19. You can select an entire row. Click in cell D4. Press SHIFT + spacebar. Click on the Fill Color icon [ALT H H] on the Home tab of the ribbon and add a color to the row. 20. You can select an entire worksheet clicking in any cell pressing CTRL +spacebar and then SHIFT + spacebar. Try it. Click in cell A1 to deselect the worksheet.

21. Another method to select the Entire worksheet is CTRL + A. Note: This method may not work on a MAC. Try it. Click on the Fill Color icon [ALT H H] on the Home tab of the ribbon. Select the No Fill option to remove the colors you added in the previous steps. 22. Use the mouse to select cells A1, A2, B1 and B2. Press Delete. (Don’t use the Backspace key to delete. The Backspace key clears the contents of a single cell. The Delete key removes everything) For a MAC use the fn key with the Delete key. 23. Press the keyboard shortcut CTRL + Z to undo your last step. Press the keyboard shortcut CTRL + Y to reverse the actions of the undo shortcut.

Ranges: Build a tic-tac-toe grid

A range is a rectangular block of cells. A range can be described by the cells found in its two opposite corners, separated by a colon. C3:F8 is a range. In the previous step we deleted the contents of the range A1:B2. A range can also be cells in a single row or a single column. C10:F10 or H3:H10.

If a range is an entire column you can omit the row numbers. A:A would refer to all of the cells in column A. C:E would refer to all of the cells in columns C, D, and E. If a range is an entire row you can omit the row letters. 3:3 would refer to all of the cells in row 3. 6:8 would refer to all of the cells in rows 6, 7, and 8. 24. Click and drag across the range of cells from A1 to C3 (A1:C3). Give the cells a yellow fill color [ALT H H]. 25. Increase the height of rows 1, 2, and 3 so the yellow range looks like a square. Use the mouse to highlight the row numbers 1 2 and 3 on the left and then click on the Format Cells icon. [ALT H O H]. 26. Inside the grid area, we want to add “right border’ lines to A1:A3 and B1:B3 and “bottom border” lines to rows A1:C1 and A2:C2. We can do that by selecting cells (one row or one column at a time) and clicking on the dropdown arrow next to the cell borders icon [ALT H B] and then selecting an option from the menu. 27. Select columns A and B and C. Center the cell contents both vertically [ALT H A M] and horizontally [ALT H A C] so the text will appear in the middle of the cell as we enter it. Change the font color to red [ALT H F C]. Increase the font size by five points. Enter X’s and O’s to play the game.

Working with the “numbers” group of icons

28. Switch into Sheet2 (The worksheet tabs are at the bottom. In Excel 2010 you will see three tabs. In Excel 2013 you see one tab plus a plus sign +. We want to have three worksheets so press the plus sign twice. The keyboard shortcut to insert a new worksheet is [Shift + F11]. 29.  You can press Ctrl + PageUp or Ctrl + PageDown, if you have them on your keyboard, to cycle between worksheets. 30. In cells A1-A4, enter the numbers 2,3,4,5. Click and use your mouse to highlight cells A1 to A4. Move the cursor until a tiny black + sign appears in the lower right hand corner of cell A4. This is the AutoFill handle. The AutoFill handle is used to quickly extend series of number and dates, and to copy labels and formulas too. Click and drag down four rows. The numbers 6, 7, 8 and 9 should appear. 31. Change the format of the 2 in cell A1 by clicking in cell A1 and then on the $ icon [ALT H A N]. For a MAC this icon does not display a dollar sign symbol. You’ll find the icon to the left of the % icon. This icon applies the Accounting format to numbers. The dollar sign is displayed on the left side of cell A1. 32. To change the 3 in cell A2 to a Currency format, you need to open the Format Numbers dialog box by selecting cell A2, then right-clicking and choosing format cells (you could also press [CTRL 1] or click on the dialog box launcher icon in the number section of the ribbon.) Select the Currency option and click OK. Notice in Currency format the dollar sign stays next to the number, in Accounting format it stays on the left margin. 33. You can use the Format Painter icon [ALT H F P] to quickly copy formatting into other cells, rows, columns, or worksheets. Click on cell A2. Click on the Format Painter icon [ALT H F P]. Press the down arrow. The dollar sign will appear next to the 4 in cell A3. Note: pressing the down arrow may not work on some laptops. 34. Click on cell A9. Type 1234567890 and press Enter. Numbers that are too long to display in the cell may appear in scientific notation, like this 1.23 E+9, or you may see railroad tracks (#####). If you see railroad tracks it means the column is too narrow. To fix this problem position the cursor between the column letters A and B and then either drag to the right. or double-click, to widen the column. Double clicking is faster, it automatically matches the column width to the width of its widest content.

35. Click again in cell A9. Click on the Comma icon [ALT H K] to change the format.

36. With cell A9 selected, click on the increase decimals icon [ALT H 0] and decrease decimals icon [ALT H 9]. Each click hides or shows another numeral on the right.

Formulas 37. This step demonstrates the wrong way to enter a formula. In cell C1 type the math formula 4 * 7 and press Enter. All we see is what we typed, instead of 28 because we left out the = sign. This is a very common error. 38. This is the right way. In cell C2 type the formula = 4 * 7 and press Enter. The total 28 appears in the cell. Click on cell C2. Now look at the long textbox (formula bar) above the worksheet. The formula = 4 * 7 appears here. You can edit the formula by typing in the formula bar. 39. In cell C3 enter the formula = 48/3. In cell C4 enter the formula = 10 + 6. In cell C5 enter the formula = 4^2. These should all equal 16. The ^ symbol means“raise to the power of” so the 2 in our formula means square the number 4. You can also use this symbol to find roots, 4^(1/2) would find the square root of 4.

Use cell references in place of the numbers 40. Delete the formulas in column C. Select the cells and use the delete key. For a MAC use the fn key with the Delete key. 41. Instead of numbers, the names of cells (like A4 or C2) can be used when you write formulas. These cell reference are treated like an x or y variable in algebra. The values of x and y are found in the cells you used for the cell references.

42. In cell C1, enter the formula = 5 + 6. In cell C2 enter the formula = A4 + 6. In cell C3 enter the formula = A4 + A5. These should all equal 11. 43. If you use formulas containing cell references, changing the number in the referenced cell changes the total. Change the number in cell A4 to an 8 and press Enter. Notice that the results displayed in cell C2 and C3 changed to 14.

Copying formulas into new cells 44. Press the Ctrl and ~ keys to switch into formula view and see all of the formulas in the worksheet. Press Ctrl and ~ to return to normal view. The ~ character is found just above the TAB key on the keyboard. 45. You can quickly copy a formula into a neighboring cell. Click on cell C3. Type Ctrl and C to copy. Press the down arrow key and then press Enter to paste. Your number in cell C4 is 13 instead of 14. Let’s check the formula. Switch to formula view. 46. Notice that the row numbers in the cell references changed. If you copy formulas into adjacent rows, the letters in any cell references will change to refer to the next row down. Instead of 8 + 6 (A4+A5) we are now adding 6 + 7 (A5+A6).

47. The AutoFill handle can be used to quickly copy formulas. Click on cell C4 and move the cursor over the lower right corner of the cell. The AutoFill handle, a small black plus sign appears. Click and drag the cursor down over cells C5 C6, C7 and C8. Release the mouse. You have just copied the formula. Excel automatically alters formulas that are copied. Notice how the cell references changed. You will learn more about this in the Toolkit and Formulas lessons.

Use the =SUM function to add up all the numbers in a range (a block of cells.) 48. Enter numbers (any numbers) in cells E1, E2, and E3, F1, F2, F3. 49. In cell E5 Type =SUM(E1:F3) Note: Include the parentheses. The E1:F3 refers to a rectangle, Excel calls this a range. The E1 refers to the upper left corner of the rectangle. The F3 refers to the bottom right corner.

You can edit the contents of a cell in several ways. If you click on cell A2 you will see the contents of the cell are showing in the cell and also showing in a long bar just above the worksheet. That bar is called the Formula bar. You can click in the formula bar, press F2, or double click on the A2 cell to edit its contents. You can put new content into a cell, replacing its existing content, by clicking on it and typing, just as if it was an empty cell.

50. Change the formula in cell E5 to =SUM(E1:E3) A range can be a column.

51. Change the formula in cell E5 to =SUM(E1:F1) A range can also be a row.

Project: Create a Cashier Report 52. Select the Sheet3 worksheet tab that you inserted in step 28. Note: The keyboard shortcut to insert a new worksheet is [Shift + F11]. In cell A1 type “Names.” In cell B1 type “Cash Sales.” In cell C1 type “Actual Cash.” In cell D1 type “Over/(Short).” 53. Edit cell A1 to change “Names” to “Cashiers.” (Press F2, type directly in the address bar, or type directly in the cell.) In cells A2, A3, A4 enter the names of three people. Use the mouse to select cells A2, A3, and A4. Press the A-Z icon [ALT H S] to sort the names alphabetically. 54. If the columns are too narrow to show the names and labels, click and drag across the column heading letters A to D. Double click on the border-line between cells D and E. 55. In cells B2, B3, and B4, enter the numbers 1200, 980, and 1620. These were the cash register readings. In cells C2, C3, and C4 enter the numbers 1205, 980 and 1580. This is the amount of cash turned in by the cashiers. 56. 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 (see step 47) to copy this formula into cells D3 and D4. Examine the formulas in formula view. Did the cell references change? Switch back to the normal view. 57. Click on the letter B above the second column. Right click and select Insert to create a new column B. In cell B1 type “# of Sales”. When you insert a new column the cells that contain your data all shift one column to the right. Excel automatically fixes the cell references for you.

58. In cells B2, B3, and B4 enter 25, 21, and 30. 59. Change the format of the range C2 to E5 to currency style. In currency style the dollar sign is next to the number with no space in between. See step 31 for help.

60. In cell A5, enter the word “Totals.” Select cells B2, B3, B4, and B5. Press the AutoSum icon [ALT H U] in Excel 2013 use [ALT H U S]. This is a quick way to find the total of one column. 61. Delete the contents of cell B5. Select the range B2:E5. Press the AutoSum icon [ALT H U] in Excel 2013 use [ALT H U S]. This is a quick way to apply the SUM function to several columns of numbers. 62. In cell F1 enter “Avg Ck.” In cell F2 enter the formula = C2 / B2. Copy the formula in cell F2 into cells F3, F4, and F5. 63. Use the Center icon [ALT H A C] to center the contents of the cells in columns B through F. 64. Right click on the 1 in the row numbers and insert a new row. Select cells A1 to F1 and use the merge and center icon [ALT H M] in Excel 2013 use [ALT H M C] to create a single cell spanning the first 6 columns. Type “Java Juice – Cashier Report” in this large cell and press Enter. Add a background color to the cell [ALT H H] and change the font color [ALT H F C]. Add a Thick Outline Border around the cell [ALT H B]. 65. Add “all borders” to the range A2:F6 [ALT H B]. Add a background color to the range A2:F2 [ALT H H]. Add a different background color to the range A3:A5 [ALT H H]....