In today’s data-driven landscape, mastering the art of data analysis is an essential skill that can open doors.
Join us as we delve into the fundamental stages of data analysis, encompassing everything from data collection and cleansing to insights that empower you to make informed decisions.
As a data analyst your role revolves around harnessing the potential of data to extract valuable insights and facilitate decision-making within your organization.
The good news is that you don’t necessarily need to dive into complex programming languages like Python or R; many data analysis tasks can be accomplished effectively using familiar tools like Excel, making it accessible to everyone.
In this post I’ll be sharing the tools that will get you results quickly and easily. No coding, no complex formulas and in many cases, Excel will do all the work for you resulting in this:
Data Gathering and Cleaning (Using Power Query)
To add to the challenge, the data is spread across 11 separate Excel files encompassing January to November, and you need to be prepared to include December’s data when it becomes available in a couple of weeks’ time.
The data also requires cleaning before you can analyse it for the following:
- Find and Replace – United States of America with USA so that it fits more easily in your reports.
- Rearrange Manager Names from Last Name, First Name to First Name, Last Name
- Add a calculated column for Gross Profit % (Sales – COGS)
- Add a calculated column for Discount % (Discount /Gross Sales)
- Round Units Sold to whole numbers
- Ensure data is in a Tabular Layout ready for fast analysis
While you can use various tools to perform the above tasks one by one, the most efficient way to get these done is with Power Query. Don’t be put off by ‘Power’ in the name. It’s super easy to use with point and click menus.
Power Query automates the laborious tasks of gathering and cleaning data. And the best part is next week/month when you need to add new data, you can have Power Query run it through the same cleaning and transformation steps and add it to the file with one click.
A better way is with a PivotTable because then it retains a connection to the source data and can be updated when new data is added.
In the chart below we can see discounts between 12%-16% are quite high and need more investigation as these will be having a significant impact on gross profit:
Exploratory Data Analysis (EDA)
EDA involves a deeper exploration of the data to uncover patterns, relationships, and trends. Visualizations, such as scatter plots, correlation matrices, and heatmaps, are used to identify insights that may guide further analysis.
Now that we have a feel for the data we’re working with, it’s time to dig deeper to get to the bottom of the areas Gross Profit is underperforming.
Analyze Data – AI
A fast way to get insights into your data is with the built in AI tool found on the Home tab of the ribbon called Analyze Data*:
*Available in Excel for Microsoft 365 (Windows and Mac) and Excel for the web.
Analyze Data scans your data for trends, patterns and outliers and can be helpful to not only get a feel for the areas that account for the majority of your sales or profit, but it’s also great at identifying insights not immediately obvious when working with large amounts of data.
We can let Analyze Data look at the entire dataset or ask it to focus on a specific area in our data. For example, here I’ve asked for insights for Gross Profit %:
We can simply click the + Insert Chart or + Insert PivotChart buttons to add these insights to our workbook, where we can work with them like regular charts.
Interestingly, Analyze Data has identified that ‘Sales’ and ‘GP %’ appear to cluster into 2 groups with 4 outliers, which may be something to dig deeper into:
And Channel Partners is our most profitable segment by far, but more importantly, Enterprise is running at a loss:
These are just a couple of the 35 insights Analyze Data generated for this dataset!
PivotTables
PivotTables are an efficient way to rapidly summarise data grouped into the various categories and segments in your dataset.
When used with conditional formatting they can uncover insights that are easy to see.
For example, the PivotTable below uses a heatmap effect to colour code underperforming products by country in shades of red:
Tip: Double Click a value to dig into the details behind the values in the PivotTable. For example, double clicking on VTT for Mexico at 18.6% returns a table with the underlying transactions:
Summarising the data by segment in a PivotTable reveals that the problems are specific to the Enterprise and Small Business segments of the business. We’re getting closer!
PivotTables also support charts to show trends and patterns:
If you’re not confident with PivotTables yet, you can use the Recommended PivotTables via the Insert tab to get started:
This opens a task pane with some suggestions based on your data:
Or ask Analyze Data to build a specific PivotTable for you. Here I asked it to ‘show me Average ‘Gross Profit %’ by ‘Country’ and ‘Product’ in a PivotTable’:
And I can click the + Insert PivotTable button to add it to my workbook. Once inserted, you can work with it like any other PivotTable. For example, you might prefer countries listed in the rows and products across the columns.
Visualisation and Reporting
The final task is to prepare a report that summarises your findings so your boss can take corrective action or identify further analysis to undertake.
Try to keep this focused on the most important findings without overwhelming them with too many options.
I’m keeping the ‘Sales’ and ‘Gross Profit %’ chart that clusters into 2 groups with 4 outliers for further analysis myself.
