Excel Advance Post-Assessment

Excel Advance Assessment

By Shahadatuliskandar Rosli

Get Your Exam Data

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.

Data Preparation

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

Data Preparation Step 1

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

Data Preparation Step 2

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

Data Preparation Step 3

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

Data Preparation Step 4

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

Data Preparation Step 5

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.

Simple Analysis

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.

Intermediate Analysis

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.

Multiple Choice

Which "Department" is not having any "Manager" leaving the company?

• R&D

• Sales

• HR

Multiple Choice

Based on your analysis, what might be the most possible reason, the "Manager" for the "Department" in your previous answer do not leave the company?

• Highest job satisfaction rating

• Highest average monthly income

• Nearest distance to home

• Highest total monthly income

Multiple Choice

Which year is having the greatest number of new employees joining the company?

• 2014

• 2019

• 2022

• 2023

Advance Analysis

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.

Multiple Choice

Which "Education Field" is going to have the highest average of "Percent Salary Hike"?

• Medical

• Other

• Life Sciences

• Human Resources

Multiple Choice

Which "Education Field" is going to have the lowest average of "Percent Salary Hike"?

• Human Resources

• Life Sciences

• Marketing

• Technical Degree