TEACHING EXCEL FOR DATA ANALYSIS: HANDS ON PRACTICE 3 WORKING WITH DATA

 1. Click on the link below to open the Microsoft Excel exercise document you can use to complete this exercise.

https://github.com/alcadelina/Excel-

https://github.com/alcadelina/Excel-/blob/main/WORKING%20WITH%20DATA.xlsx

2. First, let’s use AutoFill to complete the formulas for the two profit columns. First, select cell G2 and place your cursor over the green AutoFill handle in the bottom right corner of the cell:

3. Click and drag it down to cell G20. This will copy the formula from G2 to all the blank cells, adjusting the cell references in the formula for each row:

4. Observe the results:

5. To extend the formula in column I, rather than clicking and dragging the AutoFill handle, simply double-click it. Note that Excel identifies the data pattern and copies the formulas to the end of the data in row 20:

6. Now, use Flash Fill to separate the City and State data from column A into columns B and C. Click in cell B2 and type the City name from cell A2, “Warner”, then click in cell C2 and type the State abbreviation “NH”:

7. Now select cell B3, then click Data → Flash Fill:

8. Cells B2 through B20 now contain only the city name from column A. Now click cell C3 and click Data → Flash Fill to repeat the process for the State abbreviations:

9. Observe the results:

10. You can now discard the data in column A by right-clicking on the column header and selecting Delete from the menu:

11. The data in column A is removed and the remaining data shifts one column to the left. Note that the cell references in your formulas have changed to the new column letters automatically:

12. Finally, we need to copy the Scorecard data to its own sheet. Select cells J1 through to L4 by clicking and dragging:

13. Click Home → Copy:

14. Click the Financial Scorecard worksheet tab:

15. Ensure your cursor is in cell A1. Click the Home tab in the ribbon, click on the drop-down arrow of the Paste command, then click the Paste Values icon from the menu:

16. The data will be pasted:

If you select one of the cells in Column C and click in the formula bar, you will notice that the formulas have been replaced by static values. This is because we selected the Values option to prevent the formulas from breaking during the copy process.

17. Save your workbook as Activity 2-3 Complete. Close Microsoft 365 Excel to complete this activity.

18. Now, you can check out an example of a completed document in the link below:

https://github.com/alcadelina/Excel-

https://github.com/alcadelina/Excel-/blob/main/COMPLETED%20EXERCISE%20WORKING%20WITH%20DATA.xlsx