Analyzing Early U.S. Population Data

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