Excel Advance Post-Assessment

Remember to use Get Data in Excel!

Click HERE to open the exam data in a new web browser. Go to File > Create a Copy > Download a Copy. Use the downloaded copy as your data source for this assessment.

There is no associated marks for these steps. However, these steps are essentials for you to obtain the correct answers in the subsequent questions.

Refer to the "Business Travel" column. Identify all blank cells and enter "Non-Travel" in all blank cells.

Refer to the "Business Travel" column. Replace the "_" symbol with a space.

Change the number format of "Monthly Income" to "Currency" or "Fixed Decimal Places".

Change the number format of "Percent Salary Hike" to "Percentage". Make sure to have 2 decimal places.

Based on the information in "Monthly Income" and "Percent Salary Hike", add a new column showing "New Monthly Income". Use an appropriate formula to get the result.

In this section you will be tested on creating a simple Pivot Table analysis using COUNT, SUM, MIN, AVERAGE, and MAX functions.
For all the answers involving currency/accounting/percentage data, be sure to get an answer of 2 decimal places.

In this section you will be tested on creating multi-levels Pivot Table analysis. You need to combine the usage of 'Rows' and 'Columns' field in the Pivot Table Fields panel in order to obtain the answers.
For all the answers involving currency/accounting/percentage data, be sure to get an answer of 2 decimal places.

In this section you will be tested on creating custom grouping and calculated field in Pivot Table.
For all the answers involving currency/accounting/percentage data, be sure to get an answer of 2 decimal places.