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.

Fill in the Blanks

What is the "Total Records" for the entire table?

Fill in the Blanks

What is the "Total Monthly Income"?

Fill in the Blanks

What is the "Lowest Monthly Income"?

Fill in the Blanks

What is the "Average Monthly Income"?

Fill in the Blanks

What is the "Highest Monthly Income"?

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.

Fill in the Blanks

What is the "Total Monthly Income" for all "Current Employees"?

Fill in the Blanks

What is the "Average Monthly Income" for all "Ex-Employees"?

Fill in the Blanks

What is the "Total Records" for the entire "R&D"?

Fill in the Blanks

What is the "Total Records" of "Female" "Current Employees" from "R&D"?

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

Fill in the Blanks

How many employees have waited for at least 10 years since their last promotion?

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.

Fill in the Blanks

What "Age" is having the greatest number of employees?

Fill in the Blanks

Group the "Age" data into these categories:

<20

20-29

30-39

40-49

50-59

>60

How many employees are in the "20-29" age group?

Fill in the Blanks

How many employees are "Single" from the "20-29" age group?

Fill in the Blanks

What is the total increase of "Monthly Income"?

HINT: Create a Calculated Field to get the difference between "New Monthly Income" and "Monthly Income"

Fill in the Blanks

What is the equivalent % of your previous answer relative to "Monthly Income"?

Make sure to use 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