This is how I ACTUALLY analyze data using Excel

Summary

In this video, Moan, a data and analytics analyst, shares his Excel data analysis workflow, focusing on solving business problems rather than creating aesthetic dashboards. He demonstrates how to analyze a fictitious gym equipment sales dataset, identifying top suppliers and brands, examining year-on-year growth, market shares, and calculating year-to-date and moving annual total profit figures. Moan emphasizes practical data analysis techniques, using pivot tables, conditional formatting, and data manipulation to extract actionable insights.

Takeaways

• 😀 The video is focused on practical Excel data analysis for solving business problems rather than creating aesthetically pleasing but non-functional dashboards.
• 💼 Moan, a data and analytics analyst in the financial services industry, uses Excel as his primary tool for quick, ad hoc analysis of data sets.
• 📊 Excel is particularly useful for answering business-critical questions involving large data sets, such as up to a million rows.
• 🧩 The video provides a hands-on example of analyzing a fictitious gym equipment sales data set to identify top suppliers, brands, and categories.
• 📈 The analysis includes year-on-year growth, market share calculations, and profit figures such as year-to-date and moving annual total.
• 📝 A quick tip is shared for organizing data sets where columns are misaligned: click on the top left, double-click, and then double-click again to align columns neatly.
• 📑 Converting data into an Excel table is recommended for easier manipulation and updating when using pivot tables and charts.
• 🔢 Pivot tables are used to quickly count the number of suppliers, brands, and categories within the data set.
• 📊 Conditional formatting is applied to highlight increases and decreases in year-on-year growth with color scales for quick visual insights.
• 📈 The video demonstrates how to create charts to visualize market share changes over time using slicers for interactive filtering.
• 📋 The script includes a step-by-step guide on calculating year-to-date and moving annual total profits using Excel functions like SUMIFS.

Q & A

What is the primary focus of the video script?

The primary focus of the video script is to demonstrate a practical Excel data analysis workflow for solving business problems, rather than creating visually appealing but non-functional dashboards.

Who is the speaker in the video script?

The speaker in the video script is Moan, who works as a data and analytics analyst in the financial services industry.

What is Moan's preferred tool for quick ad hoc analysis?

Moan's preferred tool for quick ad hoc analysis is Microsoft Excel, which he uses for any analysis up to a million rows when he needs quick answers to business critical problems.

What is the purpose of using tables in Excel as per the script?

Using tables in Excel simplifies the process of working with pivot tables and pivot charts, as everything automatically updates when changes are made to the table.

How does Moan suggest adjusting columns in Excel for better readability?

Moan suggests clicking on the top left cell and then double-clicking to automatically adjust all the columns for better readability, which also works for rows.

What is the significance of the fictitious gym equipment profit data set used in the script?

The fictitious gym equipment profit data set is used to demonstrate a hands-on practical example of how to identify top suppliers and brands, analyze year-on-year growth, market shares, and calculate year-to-date and moving annual total profit figures.

What is the shortcut Moan uses to create a pivot table in Excel?

Moan uses the shortcut 'Alt + VT' to quickly create a pivot table in Excel.

How does Moan approach analyzing year-on-year growth in the script?

Moan analyzes year-on-year growth by creating a pivot table, setting the year in the columns, and using the 'Show values as' feature to calculate the percent difference from the previous year.

What is the purpose of using conditional formatting in the pivot table as described in the script?

Conditional formatting is used to quickly visualize data by applying color scales to the pivot table, where darker colors represent larger increases or decreases in values.

How does Moan calculate market share in the pivot table?

Moan calculates market share by setting the year on the rows, brand on the columns, and using the 'Show values as' feature to display the percent of row total in the pivot table.

What is the significance of duplicating worksheets in Excel as mentioned in the script?

Duplicating worksheets in Excel is significant for creating multiple analyses that utilize the same pivot table, which updates dynamically across all copies when the original data changes.