Excel Dashboard
Hatching Insights: Building Excel-ptional Dashboards
This post covers:
-Why I decided to make the dashboard in Excel,
-The interactive features added, and
-How it can help the company leadership team.
The Why behind the project:
Being a data analyst means having your eggs in all baskets 🥚🧺—yes, you read that right, every single basket! Now the question arises, “But how many baskets do I need?” The answer? It depends, but in this post, I’m naming some of the absolutely essential ones.
Let me introduce myself first. I’m an undergraduate, hustling hard to break into the data industry. I’ve spent countless hours scouring hundreds of job postings (yes, I even have a full-fledged Notion Kanban board for it) to figure out exactly what skills recruiters are looking for. And here’s what I discovered: you need to be an all-rounder. Whether it’s business acumen, statistical and mathematical know-how, effective communication, or technical prowess in coding languages like Python, R, or SQL, every basket counts.
Excel: Despite not being as intricate as SQL or any other programming language, Excel can be used to build dashboards and perform exploratory data analysis on large (but not massive) datasets. Apart from that, it always comes in handy during data cleaning. So, I decided to add one more egg to my languages basket and dive deep into Excel.
Interactive Features of the Dashboard:
Dynamic Dropdown Menus:
Dropdowns for filtering by country and year.
Real-Time Data Updates:
Leveraged Excel functions such as INDEX/MATCH/LARGE for ranking, SUMIFS for aggregating data with multiple criteria, and HLOOKUP/XLOOKUP for flexible data retrieval. The precise use of absolute and relative cell references ensured that every formula was robust and easily scalable.
Conditional Formatting:
Used conditional formatting to highlight key metrics and trends.
Benefits of the Dashboard for the Company:
Pinpoints Top-Performing States & Regions
-Identifies where the company earns the most revenue and incurs the most expenses.
-Helps prioritize markets for investment or cost control.
2. Tracks Year-over-Year Trends
-Compares performance from 2020 to 2023 across revenue, expenses, web traffic, and social media.
-Supports better forecasting and strategic planning.
3. Highlights Travel & Operational Costs
-Reveals which states have high travel expenses, offering opportunities to cut costs or revisit travel policies.
4. Supports Financial Risk Management
-Shows states with the highest late debt percentages, allowing the team to address credit risk and improve collections.
5. Optimizes Workforce Allocation
-Displays FTE (Full-Time Equivalent) counts by state, helping HR evaluate staffing needs and balance workloads.
6. Evaluates Digital & Marketing Performance
-Monitors website traffic, visitor behavior, and social media engagement to fine-tune marketing strategies.
You can access the full Excel workbook through the link provided:
