1 / 44

Analyzing Early U.S. Population Data

Analyzing Early U.S. Population Data. p opulation data. OUR EXPLORATION ASSIGNMENT Analyzing Early U.S. Population Data. OBJECTIVES: In this project, you will: fill a range with a series of numbers edit data filter , copy, and paste data

rblessing
Télécharger la présentation

Analyzing Early U.S. Population Data

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Analyzing Early U.S.Population Data population data Unit 2 Project 8

  2. OUR EXPLORATION ASSIGNMENTAnalyzing Early U.S.Population Data • OBJECTIVES: • In this project, you will: • fill a range with a series of numbers • edit data • filter, copy, and paste data • enter formulas and use the SUM, MIN, MAX, and AVERAGE functions • use the LOOKUP, TODAY, and NOW functions • add and remove temporary subtotals Unit 2 Project 8

  3. Starting Out! • Open Julie’s workbook and save it with a new name. • Open the population data workbook. • Save the workbook as population data8. • Click the 1790-1830 Data sheet tab, if necessary, to make it the active worksheet. Unit 2 Project 8

  4. TRAIL MARKER 1Filling a Range with aSeries of Numbers • KEYWORDS • AutoFill • fill handle Unit 2 Project 8

  5. TRAIL MARKER 1Filling a Range with aSeries of Numbers • Insert a new column and fill a range using Auto Fill. • Right-click the column A heading and click Insert. Right-click the row 19 heading and click Delete. • Enter Item # in cell A5. Activate cell A5 and bold and center the contents. • Enter 1 in cell A6. Activate cell A6, move the mouse pointer to the fill handle and tap and hold down the CTRL key. (continued) Unit 2 Project 8

  6. TRAIL MARKER 1Filling a Range with aSeries of Numbers • Insert a new column and fill a range using Auto Fill. • Drag down to cell A34. Release the mouse button first and then the CTRLkey. • Select the range D5:E5. Drag the selected range’s fill handle to cell H5. • Excel recognizes the pattern of incrementing by 10. • Press the CTRL + HOME keys and save the workbook. Unit 2 Project 8

  7. TRAIL MARKER 1Filling a Range with aSeries of Numbers • CHECKPOINT • Your column A and row 5 should look like this. Unit 2 Project 8

  8. TRAIL MARKER 2Editing Data • Excel gives you four ways to edit cell contents. After you click a cell, you can: • Simply enter new text or numbers in the cell. • Click the formula bar with the I-beam and edit the cell contents in the formula bar. • Double-click the cell and edit the cell contents directly in the cell. • Tap the F2 key to open the cell and position the insertion point in the cell. Unit 2 Project 8

  9. TRAIL MARKER 2Editing Data • Edit the 1780-1830 Data worksheet. • Activate cell H16 and enter 215739 to replace the existing cell contents. • Activate cell H13; then move the mouse pointer to the formula bar. • Click the end of the number 157455 in the Formula Bar to position the insertion point. • Edit the last three numbers to be 445 and tap the ENTER key. (continued) Unit 2 Project 8

  10. TRAIL MARKER 2Editing Data • Edit the 1780-1830 Data worksheet. • Double-click B11 to position the insertion point in the cell. • Edit the State name to be Florida and tap the ENTER key. • Activate cell E31 and tap the F2 key. Edit the number to be 105602 and tap the ENTER key. • Tap the CTRL + HOME keys. Unit 2 Project 8

  11. TRAIL MARKER 2Editing Data • Format worksheet title and population data. • Select cells B1:B2 and tap the CTRL + B keys to apply the Bold font style. Click the Font Size button arrow in the Font group and click 12. • Use the SHIFT + click method to select the range D6:H34. Click the Comma Style button in the Number group to add a thousands separator. Click the Decrease Decimal button in the Number group twice to remove the two decimal places. • Resize columns D:H to fit using the mouse pointer. • Tap the CTRL + HOME keys. Unit 2 Project 8

  12. TRAIL MARKER 2Editing Data • CHECKPOINT • Your worksheet should look like this. Unit 2 Project 8

  13. TRAIL MARKER 3Filtering, Copying, and Pasting Data • KEYWORDS • AutoFilter • filter Unit 2 Project 8

  14. TRAIL MARKER 3Filtering, Copying, and Pasting Data • Filter a data range. • Click any cell inside the data range. Click the Sort & Filter button in the Editing group on the Home tab and click Filter. • The AutoFilter drop-down arrows appear to the right of each column name. (continued) Unit 2 Project 8

  15. TRAIL MARKER 3Filtering, Copying, and Pasting Data • Filter a data range. • Click the Census Division column name filtering arrow. • Click the (Select All) checkbox to remove the check mark and deselect all of the options. • Click the New England checkbox to insert a check mark. • Click OK to filter the data. Unit 2 Project 8

  16. TRAIL MARKER 3Filtering, Copying, and Pasting Data • CHECKPOINT • Your filtered data should look like this. Unit 2 Project 8

  17. TRAIL MARKER 3Filtering, Copying, and Pasting Data • Copy the filtered data into a new worksheet. • Right-click the 1840-1800 Data sheet tab and click Insert on the shortcut menu. • Double-click the Worksheet icon in the Insert dialog box to insert a blank worksheet. • Change the sheet tab name to 1790-1830 New England. • Activate the 1790-1830 Data worksheet. (continued) Unit 2 Project 8

  18. TRAIL MARKER 3Filtering, Copying, and Pasting Data • Copy the filtered data into a new worksheet. • Select the range B1:H32and click the Copy button in the Clipboard group on the Home tab. • Excel copies all of the rows except hidden rows. • Activate the 1790-1830 New England worksheet. Click cell A1 and click the Paste button in the Clipboard group. • Edit cell A1 to be New England Population, resize columns A:B to fit, and activate cell A1 again. (continued) Unit 2 Project 8

  19. TRAIL MARKER 3Filtering, Copying, and Pasting Data • CHECKPOINT • Your pasted data should look like this. Unit 2 Project 8

  20. TRAIL MARKER 3Filtering, Copying, and Pasting Data • Copy the filtered data and remove the filter. • Activate the 1790-1830 Data worksheet and deselect the range. • Click the Sort & Filter button in the Editing group on the Home tab and click Filter to turn off AutoFilter. • Activate cell A1 and save the workbook. Unit 2 Project 8

  21. TRAIL MARKER 4Entering Formulas and Using the SUM, MIN, MAX, and AVERAGEFunctions • KEYWORDS • argument • calculation operator • formula • Formula AutoComplete • function • function argument Unit 2 Project 8

  22. Examples of Basic Excel Formulas Unit 2 Project 8

  23. COMMONLY USED FUNCTIONS Unit 2 Project 8

  24. TRAIL MARKER 4Entering Formulas and Using the SUM, MIN, MAX, and AVERAGEFunctions • Build a formula manually. • Click the 1790-1830 New England sheet tab and click cell C12. • Key =C6+C7+ C8+C9+C10+ C11 and tap the ENTER key. Click cell C12 again. Unit 2 Project 8

  25. TRAIL MARKER 4Entering Formulas and Using the SUM, MIN, MAX, and AVERAGEFunctions • Enter a formula by keying the SUM function and its arguments. • Activate cell D12. Key =S and observe the Formula AutoComplete list below the active cell. • Continue by keying UM(D6:D11; then tap the ENTER key to add the closing parenthesis and enter the formula. • If you see ####### symbols in cell D12, this means that the column is not wide enough to show the formula’s calculated result. Unit 2 Project 8

  26. TRAIL MARKER 4Entering Formulas and Using the SUM, MIN, MAX, and AVERAGEFunctions • Use the Sum (AutoSum) button to enter a formula. • Select the range E6:G12. • This range includes the data to be totaled and a blank cell at the bottom of each column in which to place the formula containing the SUM function. • Click the Sum (AutoSum) button in the Editing group on the Home tab. Unit 2 Project 8

  27. TRAIL MARKER 4Entering Formulas and Using the SUM, MIN, MAX, and AVERAGEFunctions • CHECKPOINT • Your worksheet with totals should look like this. Unit 2 Project 8

  28. TRAIL MARKER 4Entering Formulas and Using the SUM, MIN, MAX, and AVERAGEFunctions • Use the MIN, MAX, and AVERAGE functions. • Enter Julie’s questions in cells A14:A16 of the worksheet. Activate cell E14. • Key =M and double-click MAX in the Formula AutoComplete list that appears below the cell. (continued) Unit 2 Project 8

  29. TRAIL MARKER 4Entering Formulas and Using the SUM, MIN, MAX, and AVERAGEFunctions • Use the MIN, MAX, and AVERAGE functions. • Select the range C6:C11 to add the MAX function’s argument. Tap the ENTER key. • Key =M, double-click MIN in the Formula AutoComplete list, select the range D6:D11 and tap the ENTER key. • Activate cell E16. Key =AV, double-click AVERAGE, select the range E6:E11, and tap the ENTER key. (continued) Unit 2 Project 8

  30. TRAIL MARKER 4Entering Formulas and Using the SUM, MIN, MAX, and AVERAGEFunctions • CHECKPOINT • Your questions and answers should look like this. Unit 2 Project 8

  31. TRAIL MARKER 5Using the LOOKUP, TODAY, and NOW Functions • KEYWORD • Function Library • LOOKUP • NOW • system clock • TODAY Unit 2 Project 8

  32. TRAIL MARKER 5Using the LOOKUP, TODAY, and NOW Functions • Sort data and use the LOOKUP function. • Enter Julie’s questions in cells A17:A18. Click any cell in column G inside the data range boundaries. • Click the Sort Smallest to Largest button in the Sort & Filter group on the Data tab. Activate cell E17. • Key =LOOKUP(MAX(G6:G11),G6:G11,A6:A11) and tap the ENTER key. Activate cell E18. • Enter =LOOKUP(MIN(G6:G11),G6:G11,A6:A11) and tap the ENTER key. • Tap the CTRL + HOME keys and save. (continued) Unit 2 Project 8

  33. TRAIL MARKER 5Using the LOOKUP, TODAY, and NOW Functions • CHECKPOINT • Your questions and answers should now look like this. Unit 2 Project 8

  34. TRAIL MARKER 5Using the LOOKUP, TODAY, and NOW Functions • Use the TODAY and NOW functions. • Click the 1790-1830 Data sheet tab. Activate cell B3. Click the Date & Time button in the Function Library group on the Formulas tab. • Click TODAY in the Date & Time gallery. Click OK to calculate the current system date. • Center the date across the range B3:H3. Click the 1790-1830 New England sheet tab. (continued) Unit 2 Project 8

  35. TRAIL MARKER 5Using the LOOKUP, TODAY, and NOW Functions • Use the TODAY and NOW functions. • Using the same steps, insert the NOW function in a formula in cell A3. • Click the Number Format button arrow in the Number group on the Home tab and click Long Date. • Center the date across the range A3:G3. • Activate cell A1 and save the workbook. Unit 2 Project 8

  36. TRAIL MARKER 6Adding and Removing Temporary Subtotals • KEYWORDS • subtotal Unit 2 Project 8

  37. TRAIL MARKER 6Adding and Removing Temporary Subtotals • Sort the data and insert subtotals. • Activate any cell in the Census Division column within the data range boundaries on the 1790-1830 Data worksheet. • Sort the data range by Census Division in ascending alphabetical order. Click the Subtotal button in the Outline group on the Data tab to open the Subtotal dialog box. • Click the At each change inarrow and click Census Division. • Click the Use functionarrow and click Sum. (continued) Unit 2 Project 8

  38. TRAIL MARKER 6Adding and Removing Temporary Subtotals • Sort the data by region and insert subtotals. • Scroll the Add subtotal to list and click the 1790, 1800, 1810, 1820,and 1830 checkboxes. • Remove any check marks from the other Add subtotal to list checkboxes. • Note the Replace current subtotals and Summary below data checkboxes already contain check marks. (continued) Unit 2 Project 8

  39. TRAIL MARKER 6Adding and Removing Temporary Subtotals • Sort the data by region and insert subtotals. • Click the OK button. Resize the columns to fit the contents, if necessary. • The subtotals are inserted and the outlining pane appears on the left side of the worksheet. • Click cells E10and F15to view examples of the SUBTOTAL formulas. Tap the CTRL + HOME keys. (continued) Unit 2 Project 8

  40. TRAIL MARKER 6Adding and Removing Temporary Subtotals • CHECKPOINT • Your worksheet should look like this. Unit 2 Project 8

  41. TRAIL MARKER 6Adding and Removing Temporary Subtotals • Collapse and expand the list using the outlining buttons. • Click the level 2 button at the top of the outlining pane to view only the subtotals and grand total. • Click the level 1 button at the top of the outlining pane to view just the grand total. • Click the level 3 button at the top of the outlining pane to view all the data. Unit 2 Project 8

  42. TRAIL MARKER 6Adding and Removing Temporary Subtotals • Remove the temporary subtotals. • Activate a cell inside the data range boundaries. • Click the Subtotal button in the Outline group on the Datatab to open the Subtotals dialog box. • Click the Remove All button. • Sort the Item # column in ascending numerical order to return the worksheet to its original order. • Save and close the workbook. Unit 2 Project 8

  43. Project Skills Review (continued) Unit 2 Project 8

  44. Project Skills Review Unit 2 Project 8

More Related