Data analysis is a crucial skill in today’s business world, and Excel Microsoft Excel remains one of the most widely used tools for this purpose. This comprehensive guide will walk you through a real-life data analysis workflow in Excel, providing you with the knowledge and techniques to transform raw data into actionable insights.
by Mihir Kamdar / Last Updated:
In this comprehensive guide, you’ll gain practical skills and knowledge in:
Cleaning and preparing data efficiently using Power Query
Building robust data models and relationships
Performing advanced calculations using DAX (Data Analysis Expressions)
Creating insightful visualization.
Presenting your findings effectively through professional reports
Utilizing interactivity via slicer and timelines
Download our step-by-step tutorial file now by clicking on the icon 
below and follow along to enhance your Excel skills practically and efficiently!
In today’s data-driven business environment, Microsoft Excel is a powerful tool for data analysis. Whether you’re looking to understand sales trends, identify key markets, or forecast future performance, mastering Excel’s data analytics features can significantly enhance your ability to make informed decisions. Using an excel spreadsheet, you can perform various data analysis tasks such as budgeting, expense tracking, and visualizing data insights. This guide walks through a real-life data analysis workflow using a dataset that includes sales data from fictional companies selling real products. Each step is detailed with specific instructions on how to perform tasks in Excel, ensuring you can replicate this process on your own datasets.
Before starting your analysis, clearly define the problem you’re trying to solve. This step sets the foundation for your entire analysis.
A. Understand Your Data:
– Begin by examining your dataset to understand what information is available.
– Look at columns like Date, Company, Country, Region, Product, Sales, Revenue, Discount, and Profit.
B. Define the Problem:
– Based on the dataset, a possible problem statement could be: “Which products are the most profitable across different regions?”
C. Consider the Business Context:
– Think about why this analysis is important. For instance, identifying the most profitable products could help in making decisions about resource allocation or marketing strategies.
A. List Your Objectives:
– Example Objective 1: Determine the top three most profitable products in each region.
– Example Objective 2: Identify trends in sales volume over the last year.
B. Set Measurable Goals:
– Ensure your objectives are specific and measurable, such as “Increase the sales of Product X by 10% in the North American market next quarter.
C. Prioritize Your Goals:
– Focus on the objectives that will have the most significant impact on your business decisions.
Cleaning and preparing your data is critical for ensuring accurate analysis. Power Query is an Excel feature that simplifies these tasks. Additionally, using Excel files and cloud storage for data management and collaboration can greatly enhance efficiency, speed and accessibility.
In this tutorial, we’ll use a zipped folder that contains data related to yearly sales data. customer, item type, location, order priority, and sales channel. You can download the zipped folder.
Here’s a step-by-step process for importing data cleaning and organizing the “E-commerce Revenue Data” in Excel:
1. Open a blank Excel Workbook → Go to Data → Get Data → From File → From Excel Workbook → Select 2014 Sales Data → Click Import → Select Sheet1 → Click Transform Data → Rename the Query to 2014 Sales Data → Select Close & Load To → Select Only Create Connection & Select Add to Data Model → Click OK.
2. Do the same as shown in step 1 for all the yearly sales data from 2015 – 2021 and rename the below queries.
3. Do the same as shown in step 1 for all the customer, item type, location, order priority, and sales channel files. Rename all the queries.
4. Go to Data → Get Data → Launch Power Query Editor → Select all the Yearly Sales Data → Click Append Queries as New → Select all the Queries → Click OK → Rename the Query to FactSalesTransaction.
Once your data is clean and ready, you can begin building models and conducting in-depth analysis. Excel’s statistical functions and financial analysis tools are essential for comprehensive data analysis, enabling users to manage budgets, expenses, and other financial needs effectively.
First, you need to enable Power Pivot. Go to File → Options → Add-ins → Select COM Add-ins → Select Microsoft Power Pivot for Excel → Power Pivot tab will appear on the ribbon.
Go to Power Pivot → Go to Manage → Organize all the tables → Create a relationship between all the fields as shown below by dragging the field from Dim Table to FactSalesTransaction table.
Go to Data View → Go to FactSalesTransaction → Click any cell on the Calculation Area → Go to the Formula Bar & type in the formula → Format it to currency & remove decimal.
This is the first formula to calculate Revenue.
Add a second formula called Number of Orders.
Click any cell on the Calculation Area → Go to the Formula Bar & type in the formula → Format it to whole number.
Add a third formula called Average Revenue Per Order.
Click any cell on the Calculation Area →. Go to the Formula Bar & type in the formula → Format it to currency & remove decimal.
With your models ready, it’s time to explore and interpret your data to gain actionable insights. In Excel, you can edit chart labels and highlight key insights to enhance data visualization and make your findings easier to interpret and present effectively.
To create a PivotTable in Excel, follow these steps:
Go to PivotTable → Go to PivotTable → Click on Existing Worksheet → Select Location → Press OK → Drag all the measures in Values area.
For our e-commerce sales dashboard, let’s create PivotTables summarizing:
Go to PowerPivot → Click on Manage → Go to PivotTable → Click on Existing Sheet → Click on cell E1 → Press OK.
Revenue by sales channel.
Go to PowerPivot → Click on Manage → Go to PivotTable → Click on Existing Sheet → Click on cell I1 → Press OK.
Revenue by order priority
Go to PowerPivot → Click on Manage → Go to PivotTable → Click on Existing Sheet → Click on cell A4 → Press OK.
Revenue by item type
Visualizing data with charts and graphs is a key component of creating an interactive Excel dashboard. Charts and graphs provide a visual representation of data, making it easier to understand and to analyze data for trends, patterns, and relationships.
Selecting the appropriate chart type is crucial for effectively communicating your data insights. Different chart types are suited for different purposes and data structures. Some common chart types and their uses include:
Column and Bar Charts: Compare values across categories or show trends over time.
Line Charts: Display continuous data or show changes over time.
Pie Charts: Show the composition or proportion of a whole.
Scatter Plots: Visualize relationships between two variables.
Area Charts: Demonstrate the magnitude of change over time or across categories.
When choosing a chart type, consider the following:
The type of data you’re visualizing (categorical, continuous, discrete)
The relationship between variables (comparison, distribution, composition, trend)
The message you want to convey (change over time, proportion, correlation)
For our e-commerce sales dashboard, we’ll create:
A pie chart showing revenue by sales channel. This will help us identify which sales channel is performing better.
1. Change the values to the percentage of the Grand total.
2. Go to Insert → PivotChart → Pie → Click OK.
A column chart showing revenue by order priority. This will help us identify which order priority is performing better.
Go to Insert → PivotChart → Column → Cluster Column → Click OK.
A bar chart showing revenue by item type. This will help us identify which item type is performing better.
Go to Insert → PivotChart → Bar → Cluster Bar → Click OK.
Once you’ve selected the appropriate bar chart and types, customize your charts to enhance readability, visual appeal, and impact. Here are some tips for customizing your charts:
1. Add descriptive titles and axis labels
2. Adjust chart colors and styles
3. Format data labels and markers
4. Customize chart layout and background
5. Add trendlines and annotations
To customize your pie charts in Excel:
1. Click on the plus sign of the chart and enable all the elements.
2. Enter the chart title as “Sales Channel Distribution”.
3. Go to the Format tab to customize the chart colors by clicking on the Shape Fill. Change the online to HEX code f0a500 and offline to HEX code to 191919.
To customize your column chart in Excel:
1. Click on the plus sign of the chart and enable all the elements.
2. Enter the chart title as “Revenue by Order Priority,” the Y-axis as “Revenue,” and the X-axis as “Order Priority.”
3. Go to the Format tab to customize the chart colors by clicking on the Shape Fill and changing the color to HEX code to 191919.
To customize your bar chart in Excel:
1. Click on the plus sign of the chart and enable all the elements.
2. Enter the chart title as “Revenue by Item Type,” the Y-axis as “Revenue,” and the X-axis as “Item Type.”
3. Go to the Format tab to customize the chart colors by clicking on the Shape Fill and changing the color to HEX code to F0A500.
After analyzing and interpreting your data, it’s important to communicate your findings through interactivity via slicer and timeline.
To add timeline to your Excel dashboard, follow these steps:
1. Select any cell within a PivotTable.
2. Go to the PivotTable Analyze tab.
3. Click on “Insert Timeline ” and choose Order date.
4. Format your timeline to grey color.
To add slicer to your Excel dashboard, follow these steps:
1. Select any cell within a PivotTable.
2. Go to the PivotTable Analyze tab.
3. Click on “Insert Slicer” and choose Country.
4. Format your slicers to grey color and make the column as 2.
One of the most powerful features of slicers is the ability to connect them to multiple PivotTables and charts, allowing users to filter data across the entire dashboard with a single click. To connect a slicer to multiple elements:
1. Right-click on the slicer and choose “Report Connections.”
2. In the Report Connections dialog box, select all the PivotTables.
3. Click “OK” to apply the connections.
Now, when a user interacts with the slicer, all connected PivotTables and charts will update automatically, providing a seamless, interactive experience.
Automation is a powerful way to enhance efficiency and reduce manual effort in your Excel workflows. Using VBA (Visual Basic for Applications), you can automate repetitive tasks such as data refreshes and report generation.
Automating Data Tasks with VBA to Refresh Data and Generate Reports.
Open the VBA Editor:
Press Alt + F11 to open the VBA editor in Excel > You can write your own VBA scripts > In the VBA editor, go to Insert > Module to create a new module where you can write your code.
Example: To refresh all data connections and update all PivotTables in the workbook, you can use the following VBA code:
This code loops through all the data connections and PivotTables in the workbook, refreshing them automatically.
Sub RefreshAllData()
' Refresh all data connections
Dim conn As WorkbookConnection
For Each conn In ThisWorkbook.Connections
conn.Refresh
Next conn
' Refresh all PivotTables
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub
Go to Insert > Add a Shape > Select Assign Macro > Click OK.
Excel has evolved into a data analysis powerhouse, integrating AI and ML to enhance data analysis capabilities.
Define your problem statement and set up initial parameters in Excel using comments and notes.
Power Query connects and refreshes external data sources, ensuring data freshness and integrity.
Use formulas and conditional formatting to identify and manage missing data and outliers, maintaining data quality.
Use VBA scripts to automate repetitive tasks, improving efficiency and productivity.
This comprehensive guide has walked you through the entire process of conducting data analysis in Excel, from problem definition to automation and documentation. By following these steps and utilizing Excel’s powerful features, you can transform raw data into actionable insights that drive informed decision-making in your organization.
Remember that data analysis is an iterative process. As you gain more experience and encounter new challenges, you’ll continue to refine your skills and discover new ways to leverage Excel’s capabilities. Keep experimenting, learning, and adapting your approach to get the most out of your data analysis efforts.
Key takeaways:
Start with a clear problem definition and objectives.
Invest time in data cleaning and preparation to ensure accuracy.
Utilize Excel’s advanced features like Power Query, PivotTables, and DAX for robust analysis.
Create compelling visualizations and interactive features to communicate insights effectively.
Automate repetitive tasks with VBA to increase efficiency.
By mastering these techniques and continuously improving your Excel skills, you’ll be well-equipped to tackle complex data analysis challenges and provide valuable insights to your organization.
