Introduction
Data analysis is one of the most in-demand skills in today’s data-driven world. Whether you work in business, marketing, finance, or research, learning how to clean, analyse, and visualise data can help you make better decisions and uncover meaningful insights.
In this guide, we’ll explore the four essential stages of data analysis:
- Transforming and cleaning data
- Creating descriptive statistics
- Conducting data analysis
- Building a final report or dashboard
Let’s go step by step through each stage using a real-world example — a fast-food company’s sales dataset analysed in Excel.
1. Transforming and Cleaning Data
Before performing any analysis, data must be prepared and cleaned. Raw datasets often contain errors, inconsistencies, missing values, or formatting issues. Cleaning ensures that your analysis is accurate and reliable.
a. Converting to a Table
The first step is to convert your dataset into a table format using Excel’s shortcut Ctrl + T. Tables make it easier to sort, filter, and manage data dynamically.
b. Removing Extra Spaces
In columns such as Manager, extra spaces may distort analysis. The Excel formula =TRIM() removes unwanted spaces, ensuring consistency across entries.
c. Fixing Invalid Values
If you see decimal values in the Quantity column (e.g., 2.5 burgers), use the =ROUNDUP() formula to round them to whole numbers. This ensures the data reflects realistic quantities.
d. Adding Country Information
To enrich your dataset, you can use Excel’s Data Types > Geography feature. Once activated, you can easily extract related information, such as each city’s corresponding country, into a new column.
e. Removing Duplicates
Duplicate entries can skew your results. Using the Remove Duplicates option under the Data tab ensures that each record is unique.
After these steps, your dataset will be clean, structured, and ready for analysis.
2. Creating Descriptive Statistics
Descriptive statistics help summarise large datasets, giving you a quick overview of the key figures such as the mean, median, mode, minimum, maximum, and standard deviation.
a. Activating the Analysis ToolPak
If you don’t already have it, enable the Analysis ToolPak in Excel:
- Go to File > Options > Add-ins
- Select Analysis ToolPak and click Go
- Check the box and press OK
This will activate the Data Analysis tool under the Data ribbon.
b. Generating Summary Statistics
Use the Descriptive Statistics option in the Analysis ToolPak. Select your input range (e.g., Price or Quantity), tick Summary Statistics, and choose an output range for the results.
You’ll now have an instant breakdown of averages, totals, and dispersion measures for each selected variable.
c. Identifying Outliers with a Box and Whisker Chart
Outliers can distort averages. To visualise them, use Excel’s Box and Whisker chart:
- The box represents the first and third quartiles
- The middle line shows the median
- The “X” denotes the mean
- Dots outside the box represent outliers
You can further enhance this chart by adding manager names on the x-axis to identify whose data might contain anomalies — perhaps Joel’s entries need reviewing!
3. Performing Data Analysis
Now that the data is clean and summarised, it’s time to analyse it to answer specific business questions such as:
- What is our best-selling product?
- What is our total revenue?
- What is the revenue breakdown by payment method?
a. Calculating Revenue
Start by adding a new column called Revenue and use the formula:
=Price * Quantity
This simple calculation helps determine the income generated by each transaction.
b. Using Pivot Tables for Analysis
Pivot tables are among the most powerful tools in Excel for summarising and analysing data.
- Best-Selling Product:
- Insert a pivot table.
- Drag Product to Rows and Quantity to Values.
- Sort the results from largest to smallest.
- You’ll instantly see that Beverages sold the most units.
- Insert a pivot table.
- Total Revenue:
- Replace Quantity with Revenue under Values.
- The pivot table will show the total revenue — for example, £812,000.
- Replace Quantity with Revenue under Values.
- Revenue by Payment Method:
- Add Payment Method to Rows and Revenue to Values.
- Right-click the values and choose Show Values As > % of Grand Total to see the percentage breakdown.
- Add Payment Method to Rows and Revenue to Values.
The analysis might reveal that credit cards dominate as the most used payment method.
4. Creating a Report or Dashboard
The final step in data analysis is to present your findings clearly and visually. A well-designed dashboard allows users to interact with the data and gain quick insights.
a. Dynamic Filtering
You can add data validation drop-downs in Excel to allow filtering by variables such as Manager or City. Selecting a name like Joel automatically updates the dashboard.
b. Lookup Formulas
Use functions like XLOOKUP or SUMIFS to dynamically pull in totals and subtotals, such as each manager’s product revenue or total earnings.
c. Visual Indicators
Add data bars through conditional formatting to visually represent performance, for example showing which product categories bring the highest revenue.
These techniques make the report interactive, easy to understand, and visually appealing — perfect for management presentations or stakeholder updates.
Conclusion
Data analysis is much more than crunching numbers — it’s about turning raw information into meaningful insights that guide decisions. By following these four stages — data cleaning, descriptive statistics, analytical exploration, and report creation — you can transform a simple spreadsheet into a powerful decision-making tool.
With tools like Excel, SQL, and visualisation platforms, anyone can start their journey in data analysis. As the digital economy continues to grow, mastering these skills will help you stand out in virtually any industry.
