Dashboard assignment Using PivotTables & Pivot Charts
Objective:
This assignment aims to provide hands-on experience with Pivot Tables in
Excel, focusing on analyzing employee data. You will learn how to create,
format, and manipulate Pivot Tables to extract meaningful insights from a
dataset. This exercise will also include creating interactive reports and
visualizations to enhance data presentation.
Dataset Headers:
EmpID
Name
ID No.
Dep (Department)
Gender
DOB (Date of Birth)
Age
PayType
Position
Branch
Place of Birth
Hire date
Years no. (Years of Service)
Salary
Tasks:
[Link] and Set Up Pivot Tables (10 points)
Insert a Pivot Tables to analyze the employee data. ( Minimum
6 Pivot Tables ) such as by department, Branch , Position , Pay
Type , Gender.
2. Aggregation and Formatting (5 points)
Summarize total salary and count of employees
Format numbers as currency for salary.
3. Grouping Data (5 points)
Group employees by age ranges and years of service.
4. Filtering and Sorting (10 points)
Apply filters to show data for a specific department or branch.
Sort data to show the highest paid employees.
5. Pivot Table Styles and Layouts (10 points)
Apply a built-in style to your Pivot Table.
Change the layout to Tabular form for better readability.
7. Advanced Analysis (10 points)
Show values as a percentage of the grand total for salary.
show average salary by department.
8. Create Chart (10 points)
Insert a chart based on your Pivot Table data.
Choose an appropriate chart type and add chart elements
(title, labels).
Create Dashboard: Storytelling with Data (40 points)
Task Breakdown:
1. Define the Objective (10 points)
o Clearly state the purpose of the dashboard. What story are you
trying to tell with the data?
Example Objective: "To analyze and visualize employee
distribution and salary allocation across departments and
branches."
2. Select Key Metrics (10 points)
o Identify and include key metrics that are relevant to your
objective.
Example Metrics: Total Salary by Department, Number of
Employees by Branch, Average Salary by Position.
3. Create Interactive Elements (10 points)
o Incorporate interactive elements such as slicers and timelines to
allow users to filter and explore the data dynamically.
Example: Use slicers for Department, Branch, and Gender to
filter the data displayed in the charts.
4. Design and Layout (10 points)
o Arrange the Pivot Tables, charts, and slicers in a logical and
visually appealing manner.
Example: Place key metrics at the top, interactive elements on
the side, and detailed charts below.
Grading Checklist:
Insert and Set Up Pivot Table: /10
Aggregation and Formatting: /5
Grouping Data: /5
Filtering and Sorting: /10
Pivot Table Styles and Layouts: /10
Advanced Analysis: /10
Create Chart: /10
Create Dashboard: /40
Total Points: 120 – 15
Grading Rubric:
90-100 points: Excellent - Thorough understanding and accurate
completion.
80-89 points: Good - Minor errors or omissions.
70-79 points: Satisfactory - Several errors or omissions.
60-69 points: Needs Improvement - Lack of proficiency in key areas.
Below 60 points: Unsatisfactory - Insufficient understanding.
Notes:
Ensure all tasks are completed accurately.
Use the feedback provided to improve and clarify any doubts.
This assignment will help you develop a strong foundation in using Pivot
Tables , PivotChart for data analysis and creating interactive dashboard,
skills that are highly valuable in various professional fields.