This repository features an interactive Excel dashboard for analyzing production data, demonstrating a complete workflow from data cleaning to creating dynamic visualizations.
The dashboard is built on a production dataset that contains information about the production of certain products. The key columns in the dataset are:
- Production ID: A unique identifier for each production task.
- Production Date: The date the production task was executed.
- Region: The region for which the product was produced.
- Manager: The manager under whom the production task was executed.
- Product Type: The category or type of product manufactured.
- Units Produced: The number of units produced for the product.
- Total Cost: The total cost associated with the production.
- Gender: The gender of the manager.
- Age: The age of the manager.
Before visualization, the raw dataset underwent extensive cleaning and transformation steps to ensure data accuracy and integrity.
- Handling Null Values: The Gender column contained null values, which were replaced with "unknown" to maintain data consistency.
- Resolving Inconsistent Ages: A data quality issue was identified where managers had multiple age values. This was resolved by sorting the data by Production Date and using the
VLOOKUPfunction to assign a single, True Age to each manager. The new age column was then pasted as values to remove formula dependencies and prevent errors. - Creating Age Groups: A new column, Age Group, was added to categorize managers into three distinct groups (A1, A2, A3) based on their age using a nested
IFfunction. - Calculating Cost Per Unit: A new column was created to calculate the Production Cost Per Unit by dividing
Total CostbyUnits Produced. The values were then rounded to the nearest integer. - Verifying Data: The uniqueness of each Production ID was verified to ensure accurate task counts.
The final dashboard is composed of four charts and four interactive slicers. All charts were built using pivot tables to summarize the cleaned data.
-
Charts:
- 3D Column Chart: Represents the total production cost by product type.
- 3D Bar Chart: Displays the total number of production tasks for each manager.
- 3D Line Chart: Visualizes the total units produced by year and month.
- 3D Pie Chart: Shows the average production cost per unit by product type.
-
Slicers: Interactive slicers for Gender, Age Group, Region, and Quarters were added to the dashboard.
-
Report Connections: The slicers were configured to connect to all relevant pivot tables, ensuring that a selection on any slicer updates all four charts simultaneously.
The dashboard's appearance was customized for a clean and professional look.
- Gridlines were removed to enhance clarity.
- A prominent, merged title was added at the top of the sheet.
- All charts and slicers were arranged for a balanced and easy-to-read layout.