THE POWER OF EXCEL FOR DATA-DRIVEN INSIGHTS
Excel is undoubtedly one of the most versatile and important skills for any working professional. Beyond basic number crunching, Excel contains powerful features for data analysis and visualization — making it a must-have analytics toolkit. In this post, I’ll walk through an Excel project analyzing sales data, where I leveraged pivot tables, macros, and conditional formatting to uncover trends.
Automating with Macros:
While sorting and filtering raw data provides a useful lens, I wanted to swiftly visualize the highest growth accounts and unit sales leaders. Using Excel’s macro recorder, I created automation to enable this with one click:
1. A macro to sort the entire spreadsheet by 5 YR CAGR in descending order to reveal the accounts with the highest 5-year sales growth rates.
2. A macro to sort by 2021 unit sales in descending sequence to showcase last year’s top-selling accounts.
Get Sneha Singh’s stories in your inbox
Join Medium for free to get updates from this writer.
With these macros assigned to buttons, I can now instantly switch between perspectives to inform account planning and decisions!
Insights through Pivot Tables:
Beyond sorting, I leveraged pivot tables to aggregate five years of historical account data into a powerful interactive dashboard. With the ability to filter, re-arrange fields, and toggle summaries, I gained multiple visibility into trends.
Key insights uncovered included: the Online Retailer segment saw magnificent growth over 2017–2021 as well as strong response to catalog and coupon-based campaigns. I was able to showcase these trends through the pivot table plus conditional formatting.
This pivot table dashboard became a dynamic tool for sales analysis rather than static reporting. Paired with the macro-enabled sorting, it enabled playing with different cuts of the data.
You can find the full project files, underlying data, and presentation visuals in this Github repo: LINK
