MS Excel for Data Analytics: A Beginner-Friendly Introduction

#data #tutorial #beginners

Microsoft Excel is a tool that is everywhere, in schools, offices and even personal projects.
Yet many people, myself included, avoided it because it looked complicated.
Microsoft tool is widely used in finance(budgets, expenses),sales reports, Inventory management and data analysis

This article breaks Excel down into simple, beginner-friendly concepts so you can start using it with confidence.

Download
If you do not have Microsoft excel downloaded,here is the link https://igetintopc.com/microsoft-office-2024-professional-plus-free-download/

The Excel Interface
Key terms used in this article:

1. Cell - A single box where a row and column meet (example: A1).
2 Column - A vertical group of cells labeled with letters (A, B, C…).
3 Row - A horizontal group of cells labeled with numbers (1, 2, 3…).
4 Worksheet Tabs- At the bottom: Sheet1, Sheet2, Sheet3, etc. Each tab is one sheet in the same Excel file.
5 Worksheet - One page inside an Excel file where you enter and analyze data.
6 Workbook - An Excel file that contains one or more worksheets.
7 Dataset - A collection of related data arranged in rows and columns.
8 Formula - An equation used in Excel to perform calculations (example: =A1+B1).
9 Function - A built-in formula in Excel that performs a specific task (example: SUM, AVERAGE).
Range - A group of selected cells (example: A1:A10).
10 Pivot Table - A tool that summarizes and analyzes large datasets by grouping and calculating data automatically.
11 Chart - A visual representation of data, such as a bar chart or line graph.
12 Data Cleaning - The process of fixing or organizing data so it is accurate and ready for analysis.

Data Cleaning and Formatting
Text Formatting:
Select the cells -> Go to the Home tab ->In the Font group:
-Click B for Bold.
-Click I for Italic.
-Click U for Underline.
-Change font type and font size.

Number Formatting
Number formats convert numerical data into readable formats like percentages,currency,date and time without changing their actual value.
Steps: Select the cells with numbers > Home tab > Number group. Then use the dropdown to choose Number, Currency, Percentage, Date and click OK

Conditional Formating
Select the dataset > click home > conditional formatting > highlight cell rules > choose a color

Removing duplicates
Select your dataset > Go to Data tab > Remove Duplicates > Select the columns that define a duplicate(e.g Email) > Click OK.

Excel will shows a message with how many duplicate rows were removed.

Sorting Data
Sorting changes the order of rows based on chosen columns.
Types of sorting:
∙ Text: A to Z or Z to A
∙ Numbers: Smallest to Largest or Largest to Smallest
∙ Dates: Oldest to Newest or Newest to oldest.

Click a cell in the column you want to sort > Go to Data > Sort A to Z or Sort Z to A. Excel will sort the entire table based on that column.

Basic Calculations & Core Functions
A formula is an instruction for Excel to calculate something.
Formulas must start with an EQUAL TO SIGN ( = )
The Arithmetic operators are as follows:
Addition
Adds numbers in a range.
=A1+B1 → 14
Subtraction =A1-B1 → 6
Multiplication =A1*B1 → 40
Division =A1/B1 → 2.5
Power(exponent) =A1^B1 → 10^4 = 10000

FUNCTIONS
SUM
Examples
Total salary for all employees (rows 2 to 7):
=SUM(f2:f7) where f is Salary

AVERAGE
Mean
e.g average salary of all employees
=AVERAGE(F2:F7)

MAX
=MAX(F2:F7)

MIN
=MIN(F2:F7)

COUNTIF
=COUNTIF(F2:F7,">80,000")

LOOKUP FUNCTIONS
VLookup
This function searches for a value vertically in the first column of a table and returns a value from another column in the same row.

HLookup
It searches for a value horizontally across the first row and returns a value from a specified row.
-Used only when data is arranged horizontally.

XLookup
It replaces VLOOKUP and HLOOKUP.
Can look left or right and handles errors automatically.

Date and Time Functions
Now
Returns the current date and time.
Today
Returns the current date.
=Today()
DateIf
Calculates the difference between two dates.

=DATEDIF(F2,TODAY(), "y")

PIVOT TABLES
Allows one to summarize, analyze, and explore large datasets like HR data without writing formulas.

Steps to create a Pivot Table

Click anywhere inside your dataset
Go to Insert → PivotTable
Excel automatically selects the dataset Table
Choose “New Worksheet”
Click OK
Drag fields into rows (departments), columns and values(salaries).

Using Filters in PivotTables
Filters affect the entire PivotTable.
Example ;
Show salary data for only Full-Time employees.
Steps;
• Drag Full-Time → Filters
• Select Yes
Now all results reflect only Full-Time staff.
You can also add multiple filters needed.

Pivot Charts
This are charts linked to PivotTables.
Steps:
• Click inside PivotTable
• Insert → PivotChart
• Choose chart type

Slicers

It provide visual filters.
Steps;
• Click PivotTable
• Insert → Slicer
• Select fields
• Click OK
Example-Add slicers for department.[You can now filter data needed according to department]

Creating Dashboard
A dashboard is a visual representation of of key data and metrics on one screen. It summarizes data in one place.
It is used to tell a story.

Dashboard Principles
• One screen only (no scrolling)
• Focus on key KPIs, not raw data
• Consistent colors and fonts
• Clear titles and labels
• Interactive but simple

Example

Summary
Excel is a foundational tool for data analytics. Mastering gives you a strong foundation before moving into advanced analytics tools like Power BI, Python, or SQL.