Charles

Posted on Jan 25

MS Excel for Data Analytics — A friendly, practical guide for beginners

#datascience #beginners #analytics #dataanalytics

Introduction

Microsoft Excel is often the first tool people use when they start learning data analytics — and for good reason. It is easy to use, widely available, and powerful enough to turn raw data into useful insights.

At its core, data analytics is about answering questions using data. For example:

How much did we sell?
Which product performed best?
Are sales increasing or decreasing over time?

Excel helps answer these questions by allowing you to organize data in tables, perform simple calculations, summarize large datasets, and create visual charts — all in one place.

This article is a step-by-step introduction to using Microsoft Excel for basic data analysis. No prior experience is required. We will start with simple concepts and gradually build up, showing how Excel can be used to clean data, calculate key values, and create clear summaries that support decision-making.

What Is Data Analytics?

Data analytics is simply the process of looking at data to find meaning, patterns, and answers.

In everyday life, data analytics helps answer questions like:

How much money did we make this month?
Which product is selling the most?
Are sales improving or declining over time?

The “data” can be anything — sales records, survey responses, attendance lists, or expenses. On its own, raw data is often messy and hard to understand. Data analytics helps turn that raw data into useful information.

Microsoft Excel is one of the easiest tools to use for data analytics. With Excel, you can:

Organize data into rows and columns
Perform calculations automatically
Clean and fix messy data
Summarize large datasets
Create charts to visualize trends

This is why Excel is commonly used as a starting point before learning more advanced tools like Power BI, SQL, or Python.

Getting started with Excel (Rows, Columns & Cells — super simple)

Excel stores information in a big grid. The grid has rows, columns, and cells — that’s it. Think of a spreadsheet like a paper chart: each row is a single item (one sale, one product, one quantity), each column is a type of information about that item (product, quantity, price), and each cell is the box where one piece of information sits (for example: the price of one sale).

Why structure matters: good data analysis needs neat, predictable data. That means:

One header row at the top (column names like Date, Product, Quantity)
No blank rows inside your data block
No mixed data in a column (don’t put text and numbers in the same column)

When your data follows those simple rules, Excel can easily:

Sort and filter it,
Run basic calculations,
Summarize it with PivotTables,
And make charts that actually tell a story.

Quick tip: Select your data and press Ctrl + T (or Insert → Table). Turning your range into a Table makes formulas, filters, and charts behave more reliably — and it’s the one small habit that makes Excel feel professional fast.

Excel looks like a big grid. That’s it. Behind the grid you’ll do all your data work.

Think of the grid like a paper ledger or a notebook:

Row — a horizontal line (1, 2, 3…). Each row usually represents one record — for example, one product’s sales in our February table.
Column — a vertical line (A, B, C…). Each column stores one type of information — for example: Product, Unit Price, Quantity, Total.
Cell — the single box where a row and column cross (A1, B2, C3). A cell holds one piece of information: text (e.g., Microwave), a number (e.g., 160), a date, or a formula.

Why this simple structure matters for analysis

When your data is tidy — one header row, no blank rows, one type of value per column — Excel can:

Sort and filter quickly,
Run formulas that fill down correctly,
Summarize with PivotTables,
And build charts that actually make sense.

If your data is not tidy (mixed values, empty headers, blank rows) Excel still works — but you’ll waste time untangling problems. Good structure = faster answers.

Real example

Look at the row for Air Conditioner:

Product	Unit Price	Quantity	Total
Air Conditioner	850	40	34,000

Here:

One row Air Conditioner record.
The Unit Price column always has prices.
The Total cell is the product of Unit Price × Quantity.

In Excel you would put a formula like =C2*D2 in the Total cell and then drag the fill handle down to copy that formula for every row. Excel updates cell references automatically — that’s called relative referencing and it makes repetitive calculations easy.

One tiny pro tip for beginners

Select your full range and press Ctrl + T (or Insert → Table).
Turning your range into an Excel Table:

Adds filter arrows automatically,
Makes formulas easier ([@unit Price] style),
Auto-fills formulas for new rows,
Keeps charts and pivots stable.

This small habit makes your workbook feel professional instantly.

Quick practice (30 seconds)

Click any cell with a number and type =ISNUMBER(A2) in a blank cell — it returns TRUE if A2 is a real number.
Put =C2*D2 in the first Total cell, press Enter, then drag the fill handle down. See the totals fill? You did calculation automation.
Select the range and press Ctrl + T to make it a table.

Hands-on: Basic calculations, quick cleaning, and your first PivotTable

We’ll do three small things in order:
Add simple formulas (Total, SUM, AVERAGE, COUNT, IF)
Quick data-cleaning checks
Build your first PivotTable to summarize the table

Add simple formulas/Functions (Total per row + a running summary)

A — Total per row (you already saw this idea):

Click the Total cell for the first product (e.g., cell E2 if your table columns are A:SlNo, B:Product, C:Unit Price, D:Quantity, E:Total).
Type =[@unit Price] * [@Quantity] or if you prefer cell references (example for row 2):=C2*D2
Press Enter. If your data is an Excel Table, Excel auto-fills the formula for all rows.

B — Grand Total (SUM)

Below the table, pick a blank cell and type:=SUM(SalesData[Total])
or, if not a table:=SUM(E2:E7)
Press Enter → this gives total sales for February.

C — Average & Count

Average Unit Price:=AVERAGE(SalesData[Unit Price])

Number of product rows: =COUNTA(SalesData[Product])

D — Simple IF check (flag low-selling items)

Add a new column LowSales and use:=IF([@total] < 10000, "Low", "OK")

This flags products with Total < 10,000 for review.

Tiny practice for easier understanding;

Change the Quantity for Television to 100 and watch the Grand Total update automatically. You just saw reactive calculation.

Quick data-cleaning checks

These checks help with the following;

Removing Duplicates and Irrelevant Observations: Identifying and deleting repeated records or data points that do not fit the specific problem you are trying to solve, which makes analysis more efficient.
Fixing Structural Errors: Standardizing inconsistent formats (e.g., converting "st." to "Street") and ensuring uniform naming conventions across the dataset.
Handling Missing Data: Addressing gaps in the data through methods like imputation (filling in values) or deletion to maintain the integrity of the dataset.
Managing Outliers: Detecting and handling extreme values that could skew results and lead to inaccurate conclusions.
Ensuring Data Consistency and Formatting: Scaling or normalizing values to a specific range (e.g., 0 to 1) so that algorithms can process them more effectively.
Validating Accuracy: Checking for human errors such as typos, misspelled names, or incorrect numerical values against known lists or standards.
Enhancing Data Compliance: Maintaining accurate and valid records to minimize legal risks and ensure compliance with data regulations.

1) Are numeric cells real numbers?

In a blank cell type: =ISNUMBER(C2)
Replace C2 with a Unit Price cell. TRUE = good.

2) Trim spaces

If Product names look odd, create helper:=TRIM([@Product])

Copy the trimmed values → Paste Values → replace original Product column.

3) Remove exact duplicates

Select the table → Data → Remove Duplicates → check all columns → OK.

4) Find blanks quickly

Select ALL Ctrl + A, Press Ctrl + F → Leave search blank? (Instead use filter): click any column filter → (Blanks) to show missing values.

Build your first PivotTable (summarize Total by Product)

Pivot Tables help you summarize large data quickly without complex formulas.

You can answer questions like:

Total sales per product
Sales per month
Quantity sold by category

Key Functions

Summarization: Automatically calculates sums, averages, counts, or other statistics without requiring manual formulas.
Data Grouping: Organizes information into categories, such as sales by region, employee, or time period.
Trend Identification: Helps spot patterns, outliers, and relationships within complex datasets.
Interactive Exploration: Uses a drag-and-drop interface to rearrange fields, apply filters, and drill down into specific details instantly.

The Four Main Components
When building a pivot table, you typically assign your data fields to four areas:

Rows: Fields placed here appear as labels down the left side of the table.
Columns: Fields placed here appear as headers across the top.
Values: This is where numerical data is calculated (e.g., "Sum of Sales" or "Count of Orders").
Filters: Allows you to isolate specific subsets of data, such as a single year or a specific department.

Steps (exact Hacks)

Click anywhere inside your SalesData table.
Go to Insert → PivotTable. Excel will suggest the table range — leave defaults and choose New Worksheet, then OK.
In the PivotTable Fields pane:
Drag Product into Rows.
Drag Total into Values (it defaults to Sum).
The Pivot shows Sum of Total by Product — great!

Drag LowSales into Filters to quickly filter only low sellers.

PivotField Layout

Pivot Results

Charts in Excel

In Microsoft Excel, charts are visual representations used to make complex data easier to interpret. They allow you to illustrate trends, comparisons, and relationships between different data series graphically.

Common Chart Types

Excel offers several standard chart types, each suited for different data analysis goals:

Column Charts: Best for comparing items across categories using vertical bars.
Line Charts: Ideal for showing trends over time or continuous data.
Pie Charts: Used to show proportions and how parts contribute to a whole for a single data series.
Bar Charts: Function like column charts but use horizontal bars, making them better for long category labels.
Scatter (XY) Charts: Used to show relationships between sets of values, common in scientific and engineering data.
Combo Charts: Combine two or more chart types (e.g., a column and a line chart) to highlight different types of information in one view.

Make a quick PivotChart

With the PivotTable selected, click PivotTable Analyze → PivotChart → choose Column.
Place the chart on the Pivot sheet or copy it to a Dashboard sheet.
Add a clear title: Revenue by Product — February.

Short notes (best-practice tips)

Use Tables (Ctrl + T) — they auto-fill formulas and make Pivots easier.
Avoid mixing data types in a column (numbers + text).
Keep original raw data untouched — do cleaning in a copy or new sheet.
When Pivot doesn’t update after changing data, click Refresh (PivotTable Analyze → Refresh).

Building a Simple Excel Dashboard (Charts, KPIs & Slicers)

Excel dashboards are interactive, visual summaries of key data, using charts, tables, and KPIs in a single view for quick analysis and decision-making.

In this section, we’ll turn our PivotTable into a clean, interactive dashboard that anyone can understand at a glance.

By the end, you’ll have:

A summary chart
Clear KPI numbers
An interactive slicer to filter results instantly

No advanced Excel. Just smart setup.

Create a Dashboard Sheet

Click the + icon to add a new worksheet
Rename it to Dashboard

This sheet will only contain visuals and summaries — no raw data.

Best practice:

Raw Data → one sheet
PivotTables → one sheet
Dashboard → one clean sheet

Add Key KPI Numbers (Top Summary)

From your PivotTable sheet, identify these values:

Total Sales (February)
Number of Products
Average Sales per Product

A — Total Sales

Copy the Grand Total value from your PivotTable
Paste it at the top of the Dashboard

Label it clearly:

Total Sales – February
Increase font size, make it bold.

B — Number of Products Sold
C — Average Revenue per Product

Use borders or shapes (Insert → Shapes → Rectangle) to make these KPIs look like cards.

Add a Slicer (Make It Interactive)

This is where the dashboard comes alive.

Click anywhere inside the PivotTable
Go to PivotTable Analyze → Insert Slicer

Select:

Product
(Optional) LowSales

Click OK

Now:

Drag the slicer onto the Dashboard
Resize it neatly on the side

Clicking a product instantly updates:

Chart
KPIs
PivotTable values

This is real-time analysis.

Clean Up the Dashboard (Very Important)

Make it executive-friendly:

Remove gridlines
View → Uncheck Gridlines
Align everything evenly
Use consistent fonts
Avoid clutter — less is more

Optional polish:

Light background color for KPI cards
Bold headers
No unnecessary decimals

Wrap-Up (What You Built)

Created a dedicated dashboard sheet
Displayed KPI metrics clearly
Visualized revenue by product
Added slicers for instant filtering
Designed a clean, professional layout

This is the foundation of business reporting in Excel.

KPI cards

Chart on dashboard

Product slicer

Full dashboard view

Data Analysis Step by Step;

1st Read: Git & Github Beginner's guide
If you’re also learning version control with Git, you can read my Git & GitHub beginner’s guide here: 👉 https://dev.to/charles_ndungu/git-for-data-scientists-data-engineers-my-very-first-beginner-guide-git-bash-github-3952

2nd Read: Mastering Excel

After mastering Git basics, you can learn how to analyze data using Microsoft Excel here:
👉 https://dev.to/charles_ndungu/ms-excel-for-data-analytics-a-friendly-practical-guide-for-beginners-hjn

Repo

https://github.com/Charles-Ndungu/excel-for-data-analytics

DEV Community