Introduction to MS Excel for Data Analytics - A Beginner's Guide

So I recently started learning data analytics, and everyone kept telling me that i needed to learn excel first. I'll be honest, at first I thought Excel was just for making boring spreadsheets.

Excel is actually super powerful for analyzing data, and the best partis that you don't need to know programming to use it. In this guide, I'm going to walk you through everything I learned about using Excel for basic data analysis.

Before we dive into Excel, let me explain what I mean by "data analytics" in simple terms.

Data analytics is basically looking at a bunch of information (data) and trying to find patterns, answers, or insights. For example:

Which product sells the most?
What's the average score of students in a class?
How do sales change month by month?
Excel helps us answer these questions without doing everything manually!

Part 1: Getting Started with My Data

Opening the Dataset

First thing I did was open Excel and load my HR dataset file.

I saw columns with data about employees: Employee ID, Age, Department, Gender, Job Role, Monthly Income, Years at Company, and more. Honestly, it was a bit overwhelming at first!

Basic Navigation

I learned that:

• Rows go horizontally (each row = one employee)
• Columns go vertically (each column = one type of information)
• Cells are the individual boxes where rows and columns meet

Freezing Headers

One thing that annoyed me was scrolling down and forgetting which column was which! So I froze the top row:

1. Clicked on any row
2. View tab → Freeze Panes → Freeze Top Row

Now when I scroll, the headers stay visible!

Part 2: Sorting and Filtering Data

Sorting by Salary

I wanted to see who had the highest salary:

1. Clicked anywhere in my data
2. Data tab → Sort
3. Sort by: MonthlyIncome, Order: Largest to Smallest

Using Filters

To see only specific data, I turned on filters:

1. Data tab → Filter
2. Clicked the dropdown arrow in Department column
3. Selected only "Sales"

Part 3: Basic Formulas

Calculating Average Salary

I clicked on an empty cell and typed:

=AVERAGE(E2:E877)

This calculated the average salary instantly - around Ksh. 74066.533

Other Useful Formulas

Counting employees:

=COUNT(A2:A877)

Conditional Counting

To count how many employees left the company:

=COUNTIF(J2:J1001,"Yes")

To sum salaries for just the Sales department:

=SUMIF(D2:D1001,"Sales",I2:I1001)