Excel techniques

 Week 1

Exercise 1: Working with data

In this activity, you will use AutoFill and Flash Fill to complete the financial data workbook.

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

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.

Exercise 2: Working with rows and

columns
2. The Financial Scorecard worksheet should be open. (If not, click its tab to switch to it.) You can see that Column A
overlaps Column B, which is blank. Double-click the separator between these two columns to automatically resize Column
A:

3. Now, right-click the Column B header and click Delete:

4. The scorecard should look like this:

5. Our next task is to improve the layout of the Scorecard title. Select cells A1 and B1:

6. Click Home → Merge & Center:

7. Cells A1 and B1 will now look like this:

8. Now, switch to the Financial Data worksheet:

9. The scorecard data is duplicated on this sheet, so let’s remove it. Select cells J1 to L4:

10. Click Home → Clear drop-down arrow → Clear All:

11. The data is now removed. Finally, let’s resize this sheet’s columns. Since we have many columns to resize, we will use a
slightly different method. Click the selector icon in the top left corner of the data to select the entire worksheet:

12. Click Format → AutoFit Column Width:

13. Review the results:

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

Exercise 3: Formatting text

2. First, let’s format the header row of the Financial Data sheet. Select Row 1:

3. Click the Increase Font Size button twice:

4. The value in the Size menu should now be 14:

5. Apply the Bold effect:

6. Click the drop-down arrow next to the Font Color command:

7. Click the Blue swatch in the Standard Colors palette:

8. Because of the font changes, the data no longer fits in the cells. Press Ctrl + A to select the entire worksheet. Click Home
→ Format → AutoFit Column Width:

9. Now, let’s copy the header row formatting to the second sheet. Click any cell in Row 1 to select it and click Home →
Format Painter:
10. Click the Financial Scorecard worksheet tab:

11. Click cell A1:

The formatting will be applied; notice that the Merge & Center formatting option has been removed as part of this process.
We will fix this in a later activity.
12. Save your workbook as Activity 3-1 Complete. Close Microsoft 365 Excel to complete this activity.

Exercise 4: Formatting cells

2. First, let’s apply the proper formatting to our data. The Financial Scorecard worksheet should be open. (If not, click its tab
to switch to it.) Select the three cells containing numbers in Column B:

3. Click the dollar icon in the Number group of the Home tab and click $ English (United States):

4. The formatting will be applied. However, we do not need the cents information, so click Decrease Decimal twice:

5. This will remove the decimals from the numbers, but notice that the full value is still available in the formula bar:
6. Now, switch to the Financial Data worksheet:

7. Select cells D2 through H20:

8. Click the dollar icon in the Number group of the Home tab and click $ English (United States):

9. Click the Decrease Decimal button twice to remove the cents values:

10. Review the results:

11. Finally, let’s make the header row stand out a bit more. Select cells A1 to H1:
12. Click the Borders drop-down arrow and click Thick Bottom Border:

13. The border will be added. Now, click the Fill drop-down arrow and choose the color shown here:

14. Click any cell to de-select the header row. Review your formatting changes:
15. Save your workbook as Activity 3-2 Complete. Close Microsoft 365 Excel to complete this activity.

Exercise 5: Aligning cell content.

2. First, select columns D through H:

3. Click the Format drop-down arrow in the Cells group of the Home tab:
4. Enter 15 in the Column width field of the Column Width dialog box, then click OK:

5. Now, select Row 1, then click Home → Wrap Text:

6. Click the Middle Align button in the Alignment group of the Home tab:

7. Now select cells D1 through H1 and click the Center button:

8. Now that the financial data is neatly arranged with a well-formatted header row, select cells A1 through C20, then
click the Increase Indent button:

9. To adjust the cell width, click Home → Format drop-down arrow → AutoFit Column Width:
10. Now select cell A1 and click Home → Format Painter, to copy the cell formatting. Once done, switch to the
Financial Scorecard worksheet:

11. Click on cell A1 to apply the format:

12. Finally, select cells A1 through B1, then click Home →Merge & Center:

13. The cells are now merged and centered, and the formatting from cell A1 has also been applied to cell B1:
14. Save your workbook as Activity 3-3 Complete. Close Microsoft 365 Excel to complete this activity.

Exercise 6: Searching for and replacing

data
2. First, you need to replace all of the forward slashes separating the countries and regions with a hyphen. If you have any
area of the spreadsheet highlighted, click outside the highlight to cancel it. Click Home → Find & Select →
Replace:
3. Type the forward-slash character (“/”) in the “Find what” field, type space, hyphen, space (“ – “), in the “Replace with”
field, then click Replace All:

4. An information dialog box will appear indicating that the operation is complete, and 20 replacements were made. Click
OK:

5. Now click on the Email Addresses tab:

6. Notice that the forward slashes on this sheet have not been replaced. Click on the Sales Data tab to return to that
sheet:
7. Now Click the Options button in the Find and Replace dialog box, select Workbook from the Within drop-down
menu, then click Replace All:

8. Note that the Information dialog box now reports that another 20 replacements have been made, this time on the Email
Addresses sheet:

9. The next task is to remove any values of zero from the quarterly sales numbers. Switch back to the Sales Data worksheet
and replace the contents of the Find what field with a zero (“0”). Next, select the contents of the Replace with field
and press Delete. Finally, click Replace All:
10. Once you have clicked OK to acknowledge the 40 replacements, note that the cells with a value of zero been replaced,
and any zeros in the quarterly sales values have also been removed:

11. Click the Undo drop-down arrow on the Quick Access toolbar and select the most recent replace action to
restore the correct data:
12. Now click to select the Match entire cell contents checkbox and, once again, click Replace All:

13. Now only the six cells that have a zero as the entire cell contents have been replaced:

14. Click Close to close the Find and Replace dialog box, then save your workbook as Activity 3-4 Complete. Close
Microsoft 365 Excel to complete this activity.

Exercise 7: Spell checking a worksheet

2. Ensure that you are on the first worksheet of the workbook. Click Review → Spelling:

3. The word “Asia” is spelled incorrectly. The Spelling dialog box has selected the correct spelling, so click Change to
continue:
4. Correct the following errors, for “Tangier” and “Santiago” and the remaining instances of “Asia” in the same way:

5. Click Add to Dictionary when you reach the spelling suggestions for the proper names “Sonnie” and “Talhah”:
6. When Excel has finished checking the current sheet, click OK to close the spell checker:

7. Now let’s check the Email Addresses sheet. Switch to it by clicking its tab:

8. Press F7 to check this sheet. Click Change All to correct all the instances of “Aisa”:
9. If you are prompted to start checking at the beginning of the sheet, click Yes:

10. You will see a dialog box stating the spell checking is complete. Note that you were not asked to review both the proper
names “Sonnie” and “Talhah” because they are now included in the dictionary. Click OK to continue:

11. Click Close to close the Find and Replace dialog box, then save your workbook as Activity 3-5 Complete. Close
Microsoft 365 Excel to complete this activity.

Exercise 8: Previewing a workbook before

printing
2. Click File → Print:
3. With the Print category now open in the Backstage view, you will see a preview of the current worksheet:

4. Right now, only the currently displayed worksheet will be printed. Within the Settings section, click Print Active Sheets
→ Print Entire Workbook:
5. In the bottom left-hand corner of the preview area, click the Next Page button:

6. The second worksheet in the workbook is now displayed:

7. Click the Next Page button until you reach page 4. Note that the third sheet does not fit within the defined page
boundaries and has flowed to a fourth page:
8. Within the Settings section, click Portrait Orientation → Landscape Orientation:

9. The page orientation is now landscape and the print preview is now displaying page 3 of 3:

10. Apply the same settings to pages 1 and 2, by first clicking the Previous page button, and again clicking Portrait
Orientation → Landscape Orientation:
11. Save your workbook as Activity 4-1 Complete. Close Microsoft 365 Excel to complete this activity.

Exercise 9: Refining the page layout and

applying print options
2. Insert a manual page break by first selecting cell H1 and then clicking Page Layout → Breaks → Insert Page Break:

3. Insert another manual page break by selecting cell H27 and then clicking Page Layout → Breaks → Insert Page Break:
4. Next, use your cursor to select the D6:K50 range:

5. Click Page Layout → Print Area → Set Print Area:

6. Next, with the previously selected cell range still selected, click Page Layout → Print Titles:

7. The Page Setup dialog box will now be open to the Sheet tab. Click the cell selector button on the far right of the Rows
to repeat at top text box:
8. Your worksheet will now be shown. Click and drag to select rows 1 through 5:

9. In the cell selector dialog box, click the cell selector button to return to the Page Setup dialog box, or press Enter:

10. Back at the Page Setup dialog box, click inside the Columns to repeat at left text box and type “$A:$C”:
11. Click OK to apply the new settings:

12. Click File → Print:

13. Examine the preview portion of this screen. You will see that the current worksheet has been divided into four pages.
Each page has both column and row titles:

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