Having completed the fifth section, it's now time to put your skills to the test. Your initial task is to complete all the exercises in the workbook provided earlier.

If you can't find it or if it's been corrupted during your training, don't worry. You can download it again here:

Task

Please verify that you have already completed these steps. If not, go ahead and finish your workbook:

Select cells C5 to C25. Click the Sort button, check My data has headers, and choose Product Name, Cell Values and A to Z to sort.
Select the range. Choose Expand the selection, and click Sort. Click Undo.
Select the range. Click the Sort button, check My data has headers, and choose Product Name, Cell Values and A to Z to sort.
Select the range. Select Reorder Level, choose Conditional Formatting Icon, select the red flag, and click OK.
Select the range. Select Category, Cell Colour and choose no colour. Click OK.
Select the range. Change to Unit Price, set Sort On to Cell Values, and choose Smallest to Largest.
Select the range. Select Last Order Date, keep Cell Values and choose Oldest to Newest.
Select the range. Open the Sort dialog, select Unit Price and Smallest to Largest, then click Add Level and select Reorder Level with Conditional Formatting Icon and On Top. Click OK.
Select the range. Choose Reorder Level, Cell Values and Smallest to Largest. Click OK.
To delete levels, highlight a level in the Sort dialog and click Delete.
For custom sorting, select Category, then Custom List and input your categories. Click OK.
Select the range. Click the Filter button. Click the arrow on the Category header, choose Toys. Click OK.
Click Category autofilter, choose Books. Click Supplier autofilter, choose Orion Distributors.
Click Re-order Level autofilter, choose the red flag Click a dropdown arrow on a column header. Select Clear Filter From [Column Name].
Click Clear in the Sort & Filter group under the Data tab.
Click the Filter button to remove all Autofilter menus.
Select cells J6 to J25. Click on Data Validation. Choose Date, Between, 01/01/2023 and 31/12/2023. Click OK.
Click on Find. Type Household, choose the cell's fill color, and click Find All. Highlight all and change the fill color. Click Find All again. Click on Clear Find Format and Find All again.
Switch to the Replace tab and enter Books in Find What and Paper goods in Replace with. Click Replace All.
Click on Go To, enter D14. Click on Special and select Data Validation.
Select the data range and click on the Table button. Click My table has headers and click OK.
Type in Stock Value in J5 and press enter.
Delete data in the Revenue column. Select I6 and enter = then click on G6, enter * then click on H6. Press Enter.
Click on the Table Design tab. Apply a predefined style or customize a new style via the Table Styles options.
Select table, click on Insert Slicer. Choose Supplier then click OK. Adjust the size and position of the slicer.
Add a Total Row. Click on G27 and choose Average and press Enter. Click on H27 and choose MAX. Press Enter. Select I27 and choose SUM. Press Enter.
Select Convert to Range in the Design tab to deactivate table features.

Task

Please verify that you have already completed these steps. If not, go ahead and finish your workbook:

Select cells C5 to C25. Click the Sort button, check My data has headers, and choose Product Name, Cell Values and A to Z to sort.
Select the range. Choose Expand the selection, and click Sort. Click Undo.
Select the range. Click the Sort button, check My data has headers, and choose Product Name, Cell Values and A to Z to sort.
Select the range. Select Reorder Level, choose Conditional Formatting Icon, select the red flag, and click OK.
Select the range. Select Category, Cell Colour and choose no colour. Click OK.
Select the range. Change to Unit Price, set Sort On to Cell Values, and choose Smallest to Largest.
Select the range. Select Last Order Date, keep Cell Values and choose Oldest to Newest.
Select the range. Open the Sort dialog, select Unit Price and Smallest to Largest, then click Add Level and select Reorder Level with Conditional Formatting Icon and On Top. Click OK.
Select the range. Choose Reorder Level, Cell Values and Smallest to Largest. Click OK.
To delete levels, highlight a level in the Sort dialog and click Delete.
For custom sorting, select Category, then Custom List and input your categories. Click OK.
Select the range. Click the Filter button. Click the arrow on the Category header, choose Toys. Click OK.
Click Category autofilter, choose Books. Click Supplier autofilter, choose Orion Distributors.
Click Re-order Level autofilter, choose the red flag Click a dropdown arrow on a column header. Select Clear Filter From [Column Name].
Click Clear in the Sort & Filter group under the Data tab.
Click the Filter button to remove all Autofilter menus.
Select cells J6 to J25. Click on Data Validation. Choose Date, Between, 01/01/2023 and 31/12/2023. Click OK.
Click on Find. Type Household, choose the cell's fill color, and click Find All. Highlight all and change the fill color. Click Find All again. Click on Clear Find Format and Find All again.
Switch to the Replace tab and enter Books in Find What and Paper goods in Replace with. Click Replace All.
Click on Go To, enter D14. Click on Special and select Data Validation.
Select the data range and click on the Table button. Click My table has headers and click OK.
Type in Stock Value in J5 and press enter.
Delete data in the Revenue column. Select I6 and enter = then click on G6, enter * then click on H6. Press Enter.
Click on the Table Design tab. Apply a predefined style or customize a new style via the Table Styles options.
Select table, click on Insert Slicer. Choose Supplier then click OK. Adjust the size and position of the slicer.
Add a Total Row. Click on G27 and choose Average and press Enter. Click on H27 and choose MAX. Press Enter. Select I27 and choose SUM. Press Enter.
Select Convert to Range in the Design tab to deactivate table features.

Mark tasks as Completed

Everything was clear?

Section 5. Chapter 8

Choose your plan

Challenge

Quiz

It's time to assess your theoretical knowledge. Watch the video below and try to answer all the questions within the allotted time.

Note
If you find you've missed some topics and answered incorrectly, consider revisiting the relevant section to review the material.

Completing the Workbook

Having completed the fifth section, it's now time to put your skills to the test. Your initial task is to complete all the exercises in the workbook provided earlier.

If you can't find it or if it's been corrupted during your training, don't worry. You can download it again here:

Task

Please verify that you have already completed these steps. If not, go ahead and finish your workbook:

Select cells C5 to C25. Click the Sort button, check My data has headers, and choose Product Name, Cell Values and A to Z to sort.
Select the range. Choose Expand the selection, and click Sort. Click Undo.
Select the range. Click the Sort button, check My data has headers, and choose Product Name, Cell Values and A to Z to sort.
Select the range. Select Reorder Level, choose Conditional Formatting Icon, select the red flag, and click OK.
Select the range. Select Category, Cell Colour and choose no colour. Click OK.
Select the range. Change to Unit Price, set Sort On to Cell Values, and choose Smallest to Largest.
Select the range. Select Last Order Date, keep Cell Values and choose Oldest to Newest.
Select the range. Open the Sort dialog, select Unit Price and Smallest to Largest, then click Add Level and select Reorder Level with Conditional Formatting Icon and On Top. Click OK.
Select the range. Choose Reorder Level, Cell Values and Smallest to Largest. Click OK.
To delete levels, highlight a level in the Sort dialog and click Delete.
For custom sorting, select Category, then Custom List and input your categories. Click OK.
Select the range. Click the Filter button. Click the arrow on the Category header, choose Toys. Click OK.
Click Category autofilter, choose Books. Click Supplier autofilter, choose Orion Distributors.
Click Re-order Level autofilter, choose the red flag Click a dropdown arrow on a column header. Select Clear Filter From [Column Name].
Click Clear in the Sort & Filter group under the Data tab.
Click the Filter button to remove all Autofilter menus.
Select cells J6 to J25. Click on Data Validation. Choose Date, Between, 01/01/2023 and 31/12/2023. Click OK.
Click on Find. Type Household, choose the cell's fill color, and click Find All. Highlight all and change the fill color. Click Find All again. Click on Clear Find Format and Find All again.
Switch to the Replace tab and enter Books in Find What and Paper goods in Replace with. Click Replace All.
Click on Go To, enter D14. Click on Special and select Data Validation.
Select the data range and click on the Table button. Click My table has headers and click OK.
Type in Stock Value in J5 and press enter.
Delete data in the Revenue column. Select I6 and enter = then click on G6, enter * then click on H6. Press Enter.
Click on the Table Design tab. Apply a predefined style or customize a new style via the Table Styles options.
Select table, click on Insert Slicer. Choose Supplier then click OK. Adjust the size and position of the slicer.
Add a Total Row. Click on G27 and choose Average and press Enter. Click on H27 and choose MAX. Press Enter. Select I27 and choose SUM. Press Enter.
Select Convert to Range in the Design tab to deactivate table features.

Task

Please verify that you have already completed these steps. If not, go ahead and finish your workbook:

Select cells C5 to C25. Click the Sort button, check My data has headers, and choose Product Name, Cell Values and A to Z to sort.
Select the range. Choose Expand the selection, and click Sort. Click Undo.
Select the range. Click the Sort button, check My data has headers, and choose Product Name, Cell Values and A to Z to sort.
Select the range. Select Reorder Level, choose Conditional Formatting Icon, select the red flag, and click OK.
Select the range. Select Category, Cell Colour and choose no colour. Click OK.
Select the range. Change to Unit Price, set Sort On to Cell Values, and choose Smallest to Largest.
Select the range. Select Last Order Date, keep Cell Values and choose Oldest to Newest.
Select the range. Open the Sort dialog, select Unit Price and Smallest to Largest, then click Add Level and select Reorder Level with Conditional Formatting Icon and On Top. Click OK.
Select the range. Choose Reorder Level, Cell Values and Smallest to Largest. Click OK.
To delete levels, highlight a level in the Sort dialog and click Delete.
For custom sorting, select Category, then Custom List and input your categories. Click OK.
Select the range. Click the Filter button. Click the arrow on the Category header, choose Toys. Click OK.
Click Category autofilter, choose Books. Click Supplier autofilter, choose Orion Distributors.
Click Re-order Level autofilter, choose the red flag Click a dropdown arrow on a column header. Select Clear Filter From [Column Name].
Click Clear in the Sort & Filter group under the Data tab.
Click the Filter button to remove all Autofilter menus.
Select cells J6 to J25. Click on Data Validation. Choose Date, Between, 01/01/2023 and 31/12/2023. Click OK.
Click on Find. Type Household, choose the cell's fill color, and click Find All. Highlight all and change the fill color. Click Find All again. Click on Clear Find Format and Find All again.
Switch to the Replace tab and enter Books in Find What and Paper goods in Replace with. Click Replace All.
Click on Go To, enter D14. Click on Special and select Data Validation.
Select the data range and click on the Table button. Click My table has headers and click OK.
Type in Stock Value in J5 and press enter.
Delete data in the Revenue column. Select I6 and enter = then click on G6, enter * then click on H6. Press Enter.
Click on the Table Design tab. Apply a predefined style or customize a new style via the Table Styles options.
Select table, click on Insert Slicer. Choose Supplier then click OK. Adjust the size and position of the slicer.
Add a Total Row. Click on G27 and choose Average and press Enter. Click on H27 and choose MAX. Press Enter. Select I27 and choose SUM. Press Enter.
Select Convert to Range in the Design tab to deactivate table features.

Mark tasks as Completed

Everything was clear?

Section 5. Chapter 8

Choose your plan

Challenge

Quiz

It's time to assess your theoretical knowledge. Watch the video below and try to answer all the questions within the allotted time.

Note
If you find you've missed some topics and answered incorrectly, consider revisiting the relevant section to review the material.

Completing the Workbook

Having completed the fifth section, it's now time to put your skills to the test. Your initial task is to complete all the exercises in the workbook provided earlier.

If you can't find it or if it's been corrupted during your training, don't worry. You can download it again here:

Task

Please verify that you have already completed these steps. If not, go ahead and finish your workbook:

Select cells C5 to C25. Click the Sort button, check My data has headers, and choose Product Name, Cell Values and A to Z to sort.
Select the range. Choose Expand the selection, and click Sort. Click Undo.
Select the range. Click the Sort button, check My data has headers, and choose Product Name, Cell Values and A to Z to sort.
Select the range. Select Reorder Level, choose Conditional Formatting Icon, select the red flag, and click OK.
Select the range. Select Category, Cell Colour and choose no colour. Click OK.
Select the range. Change to Unit Price, set Sort On to Cell Values, and choose Smallest to Largest.
Select the range. Select Last Order Date, keep Cell Values and choose Oldest to Newest.
Select the range. Open the Sort dialog, select Unit Price and Smallest to Largest, then click Add Level and select Reorder Level with Conditional Formatting Icon and On Top. Click OK.
Select the range. Choose Reorder Level, Cell Values and Smallest to Largest. Click OK.
To delete levels, highlight a level in the Sort dialog and click Delete.
For custom sorting, select Category, then Custom List and input your categories. Click OK.
Select the range. Click the Filter button. Click the arrow on the Category header, choose Toys. Click OK.
Click Category autofilter, choose Books. Click Supplier autofilter, choose Orion Distributors.
Click Re-order Level autofilter, choose the red flag Click a dropdown arrow on a column header. Select Clear Filter From [Column Name].
Click Clear in the Sort & Filter group under the Data tab.
Click the Filter button to remove all Autofilter menus.
Select cells J6 to J25. Click on Data Validation. Choose Date, Between, 01/01/2023 and 31/12/2023. Click OK.
Click on Find. Type Household, choose the cell's fill color, and click Find All. Highlight all and change the fill color. Click Find All again. Click on Clear Find Format and Find All again.
Switch to the Replace tab and enter Books in Find What and Paper goods in Replace with. Click Replace All.
Click on Go To, enter D14. Click on Special and select Data Validation.
Select the data range and click on the Table button. Click My table has headers and click OK.
Type in Stock Value in J5 and press enter.
Delete data in the Revenue column. Select I6 and enter = then click on G6, enter * then click on H6. Press Enter.
Click on the Table Design tab. Apply a predefined style or customize a new style via the Table Styles options.
Select table, click on Insert Slicer. Choose Supplier then click OK. Adjust the size and position of the slicer.
Add a Total Row. Click on G27 and choose Average and press Enter. Click on H27 and choose MAX. Press Enter. Select I27 and choose SUM. Press Enter.
Select Convert to Range in the Design tab to deactivate table features.

Task

Please verify that you have already completed these steps. If not, go ahead and finish your workbook:

Select cells C5 to C25. Click the Sort button, check My data has headers, and choose Product Name, Cell Values and A to Z to sort.
Select the range. Choose Expand the selection, and click Sort. Click Undo.
Select the range. Click the Sort button, check My data has headers, and choose Product Name, Cell Values and A to Z to sort.
Select the range. Select Reorder Level, choose Conditional Formatting Icon, select the red flag, and click OK.
Select the range. Select Category, Cell Colour and choose no colour. Click OK.
Select the range. Change to Unit Price, set Sort On to Cell Values, and choose Smallest to Largest.
Select the range. Select Last Order Date, keep Cell Values and choose Oldest to Newest.
Select the range. Open the Sort dialog, select Unit Price and Smallest to Largest, then click Add Level and select Reorder Level with Conditional Formatting Icon and On Top. Click OK.
Select the range. Choose Reorder Level, Cell Values and Smallest to Largest. Click OK.
To delete levels, highlight a level in the Sort dialog and click Delete.
For custom sorting, select Category, then Custom List and input your categories. Click OK.
Select the range. Click the Filter button. Click the arrow on the Category header, choose Toys. Click OK.
Click Category autofilter, choose Books. Click Supplier autofilter, choose Orion Distributors.
Click Re-order Level autofilter, choose the red flag Click a dropdown arrow on a column header. Select Clear Filter From [Column Name].
Click Clear in the Sort & Filter group under the Data tab.
Click the Filter button to remove all Autofilter menus.
Select cells J6 to J25. Click on Data Validation. Choose Date, Between, 01/01/2023 and 31/12/2023. Click OK.
Click on Find. Type Household, choose the cell's fill color, and click Find All. Highlight all and change the fill color. Click Find All again. Click on Clear Find Format and Find All again.
Switch to the Replace tab and enter Books in Find What and Paper goods in Replace with. Click Replace All.
Click on Go To, enter D14. Click on Special and select Data Validation.
Select the data range and click on the Table button. Click My table has headers and click OK.
Type in Stock Value in J5 and press enter.
Delete data in the Revenue column. Select I6 and enter = then click on G6, enter * then click on H6. Press Enter.
Click on the Table Design tab. Apply a predefined style or customize a new style via the Table Styles options.
Select table, click on Insert Slicer. Choose Supplier then click OK. Adjust the size and position of the slicer.
Add a Total Row. Click on G27 and choose Average and press Enter. Click on H27 and choose MAX. Press Enter. Select I27 and choose SUM. Press Enter.
Select Convert to Range in the Design tab to deactivate table features.

Everything was clear?

Quiz

It's time to assess your theoretical knowledge. Watch the video below and try to answer all the questions within the allotted time.

Note
If you find you've missed some topics and answered incorrectly, consider revisiting the relevant section to review the material.

Completing the Workbook

Having completed the fifth section, it's now time to put your skills to the test. Your initial task is to complete all the exercises in the workbook provided earlier.

If you can't find it or if it's been corrupted during your training, don't worry. You can download it again here:

Task

Please verify that you have already completed these steps. If not, go ahead and finish your workbook:

Select cells C5 to C25. Click the Sort button, check My data has headers, and choose Product Name, Cell Values and A to Z to sort.
Select the range. Choose Expand the selection, and click Sort. Click Undo.
Select the range. Click the Sort button, check My data has headers, and choose Product Name, Cell Values and A to Z to sort.
Select the range. Select Reorder Level, choose Conditional Formatting Icon, select the red flag, and click OK.
Select the range. Select Category, Cell Colour and choose no colour. Click OK.
Select the range. Change to Unit Price, set Sort On to Cell Values, and choose Smallest to Largest.
Select the range. Select Last Order Date, keep Cell Values and choose Oldest to Newest.
Select the range. Open the Sort dialog, select Unit Price and Smallest to Largest, then click Add Level and select Reorder Level with Conditional Formatting Icon and On Top. Click OK.
Select the range. Choose Reorder Level, Cell Values and Smallest to Largest. Click OK.
To delete levels, highlight a level in the Sort dialog and click Delete.
For custom sorting, select Category, then Custom List and input your categories. Click OK.
Select the range. Click the Filter button. Click the arrow on the Category header, choose Toys. Click OK.
Click Category autofilter, choose Books. Click Supplier autofilter, choose Orion Distributors.
Click Re-order Level autofilter, choose the red flag Click a dropdown arrow on a column header. Select Clear Filter From [Column Name].
Click Clear in the Sort & Filter group under the Data tab.
Click the Filter button to remove all Autofilter menus.
Select cells J6 to J25. Click on Data Validation. Choose Date, Between, 01/01/2023 and 31/12/2023. Click OK.
Click on Find. Type Household, choose the cell's fill color, and click Find All. Highlight all and change the fill color. Click Find All again. Click on Clear Find Format and Find All again.
Switch to the Replace tab and enter Books in Find What and Paper goods in Replace with. Click Replace All.
Click on Go To, enter D14. Click on Special and select Data Validation.
Select the data range and click on the Table button. Click My table has headers and click OK.
Type in Stock Value in J5 and press enter.
Delete data in the Revenue column. Select I6 and enter = then click on G6, enter * then click on H6. Press Enter.
Click on the Table Design tab. Apply a predefined style or customize a new style via the Table Styles options.
Select table, click on Insert Slicer. Choose Supplier then click OK. Adjust the size and position of the slicer.
Add a Total Row. Click on G27 and choose Average and press Enter. Click on H27 and choose MAX. Press Enter. Select I27 and choose SUM. Press Enter.
Select Convert to Range in the Design tab to deactivate table features.

Section 5. Chapter 8

Switch to desktop for real-world practiceContinue from where you are using one of the options below