When people hear Data Analytics, they often think of complex programming languages like Python or SQL. However, the functions found in Excel are generally the same ones found in Power BI, SQL, and Python—the primary difference is just the syntax used for execution.
This guide will introduce you to MS Excel as a powerful analytics tool, covering everything from basic data cleaning to interactive dashboards using Pivot Tables.
1. Organising Your Data: Sorting and Filtering
Before analysing data, you must ensure it is organised.
Data Sorting: Sorting involves arranging data in a specific order.
Text: Arranges data from A to Z or Z to A.
Numbers: Arranges data from smallest to largest or largest to smallest.
Dates: Arranges data from oldest to newest or newest to oldest.
⚠️ Important Tip: When sorting, always expand the selection when prompted. If you don't, Excel might reorder only the selected column, which will disorient the rest of your data and cause records to be mismatched.
Data Filtering allows you to temporarily display only the rows that meet specific criteria while hiding the rest. You can toggle filters on or off using the shortcut Control + Shift + L.
Text Filters: Use these to find cells that "contain" specific words or "begin with" a certain letter.
Number Filters: Use these to filter for "Top 10" items or values "Greater Than" a specific number.
Sort & Filter dropdown menu in the Home tab
2. Cleaning Data with Text Functions
Raw data is often messy. Excel provides specific functions to clean and standardise text.
TRIM: Removes extra leading or trailing spaces that are often invisible but cause errors.
PROPER: Capitalises the first letter of each word (great for fixing names).
UPPER / LOWER: Converts text entirely to uppercase or lowercase.
CONCAT: Combines two or more text strings into one cell. In older versions of Excel, you can use the & (ampersand) symbol to achieve this.
Messy names and a 'Cleaned' column using =PROPER(TRIM(cell))
3. Automating Decisions with Logical Functions
Logical functions help you categorise data automatically based on rules.
The IF Function The IF function performs a test: it returns one value if the test is true, and a different value if it is false.
Example: Imagine you want to categorise salaries. If the salary in cell E2 exceeds 80,000, it is "High"; otherwise, it is "Low".
Formula: =IF(E2 > 80000, "High", "Low").
Nested IFs. If you have more than two categories (e.g., Old, Middle-aged, Young), you can use a Nested IF, which places a second IF function inside the first one.
AND / OR Logic You can combine IF with AND (where both conditions must be met) or OR (where at least one condition must be met).
AND Example: Assign a bonus only if experience > 30 years AND projects > 10.
Formula: =IF(AND(O2 > 30, P2 > 10), "Assign Bonus", "Do not Assign Bonus").
4. Connecting Data with Lookup Functions
Data is often split across different tables. Lookup functions allow you to retrieve data from one table and pull it into another.
VLOOKUP (Vertical Lookup) VLOOKUP searches for a value in the first column of a range and returns a result from a column you specify.
The Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
lookup_value: The ID you are searching for.
table_array: The range containing your data.
col_index_num: The column number containing the answer (e.g., column 5 for Salary).
range_lookup: Use FALSE for an exact match, which is recommended for IDs.
A VLOOKUP formula connecting an Employee ID to their Salary
5. Mastering Date Functions
Excel stores dates as serial numbers, allowing for powerful calculations.
TODAY: Returns the current date.
NETWORKDAYS: Calculates the number of working days between two dates, automatically excluding weekends.
DATEDIF: A "hidden" function that calculates the difference between two dates in years ("y"), months ("m"), or days.
6. The Power of Pivot Tables
Pivot tables are the ultimate tool for summarising data. They allow you to aggregate thousands of rows into a clear summary table without writing complex formulas.
How to create one:
Click a single cell inside your data range (avoid selecting the whole sheet).
Go to Insert > Pivot Table.
Drag and Drop fields:
Rows: For categories (e.g., Department).
Values: For numbers to calculate (e.g., Sum of Salary, Count of Employees).
Interactive Slicers: To make your report interactive, insert a Slicer. This is a visual button menu that filters your Pivot Table instantly when clicked.
A Pivot Table with a Slicer for 'Department' next to it.
Summary
Excel is more than just a spreadsheet; it is a robust data analytics environment. By mastering text cleaning, logical functions, lookups, and Pivot Tables, you can transform raw data into meaningful insights.
Top comments (0)