MS Excel is one of the most beginner-friendly tools for data analysis. It allows users to manipulate, process, and visualise large datasets efficiently, turning raw data into meaningful insights.
Why MS Excel for data analytics
Useful in many entry-level analysis tasks eg: formulas, tables, conditional formatting, charts, pivottables etc
It's fast and easy
Widely available in many institutions, workplaces and schools.
What we will learn in this article
Data cleaning: Removing duplicates, sorting & filtering
Simple formulas
Conditional formatting
Data Visualisation: Tables, Charts, Pivot tables
Data Cleaning
Before data analysis commences, it's important to clean and organize the dataset to ensure accuracy. The common cleaning tasks are:
Removing duplicates
Use Data > Remove duplicates This removes redundancy in a dataset
Sorting Data
Sorting data makes it easier to immediately view and comprehend your data, organize and locate the facts you need, and ultimately help you make better decisions.
Filtering Data
This fn enables you to pull information from a given range that specifies the criteria. It shows the data that's only required.
Select any column from the table. After that go to the data tab on the top of the ribbon and then in the sort and filters group select filter.
Simple Formulas
Built in fns in Excel enable users perform calculations easily. Below are some of the examples:
-
Sum
Adds a range of numbers
=sum (a2:a877)This adds all values from cell a2 to a877 -
Average
Calculates the averages of numbers
=average (a2:a877)This displays the average value of the numbers between cell a2 and cell a877 -
Count
Counts how many cells display numbers.
=count (a2:a877) -
CountA
Counts cells that aren't empty
=countA(a2:a877)
There are more formulas used in excel. Above formulas are just but a few of them
Conditional Formating
Trends and patterns in a dataset can be highlighted for easier visualization.
Step 1: Go to Home > Conditional Formatting.
Select any column from the table. After that go to the home tab on the top of the ribbon and then in the styles group select conditional formatting and then in the highlight cells rule select Greater than an option.
Then a greater than dialog box appears. First write the quarter value and then select the colour.
Step 2: Preview Result
As you can see in the excel table 'quarter' column changes the colour of the values that are greater than 6.
Charts
Any set of info can be graphically represented using a chart. Excel offers several chart types of your choice. Charts make it easier to identify trends and relationships
- Select your dataset and go to Insert > Charts.
- Choose from bar charts, line charts, or pie charts.
- Customize the chart for clarity and impact.
Pivot Tables
Pivot tables much like charts help visualise datasets in graphical representation. The difference is pivot tables use charts for representation. So what are pivot tables? They summarize, analyze, and reorganise large datasets without formulas, allowing you to drag and drop headers to create interactive reports.
Steps to create a Pivot Table:
Select your data
Go to Insert > PivotTable
Drag fields into Rows, Columns, and Values
Example:
Rows > Department
Values > Count of Expense Type
The count of expense type will automatically be done by Excel.
Dashboards
Finally with all these tools and more we can now generate dashboards. Dashboards are paramount in data analysis. So what are dashboards? Like a car's dashboard that displays the combined data of a vehicle, dashboards consolidate key business metrics, charts and tables into a single view for easy analysis. What does a dashboard consist of:
Charts
Slicers (interactive filters)
Pivot tables
Top comments (0)