TEACHING EXCEL FOR DATA ANALYSIS: HANDS ON PRACTICE 8 SEARCHING AND REPLACING DATA

 You have been asked to make some changes to the Sales Report. Rather than manually changing every cell you decide to use the Find and Replace tool.

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/SEARCHING%20FOR%20AND%20REPLACING%20DATA.xlsx

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.

15. 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%20SEARCHING%20FOR%20AND%20REPLACING%20DATA.xlsx