Excel is one of the most commonly used tools for working with data.
You can use it to enter information, organize it, format it, clean it, perform calculations, and identify patterns.
In this guide, we'll use a simple sales dataset to learn the fundamentals.
NB : I have used a made up dataset in my analysis.
- Understanding Excel
An Excel file is called a workbook.
A workbook contains worksheets.
A worksheet is made up of:
Columns — A, B, C, D...
Rows — 1, 2, 3, 4...
Cells — where rows and columns meet
For example:
B4
means Column B, Row 4.
A group of cells is called a range.
For example:
A1:D10
- Our Example Dataset
We'll use a simple sales dataset:
Date Customer Region Salesperson Product Qty Unit Price Sales
01-Aug Alpha Traders Maputo Dinis Nation PVA 10 750 7,500
02-Aug Benny Hardware Beira Laura Weather Guard 5 1,200 6,000
03-Aug City Build Maputo Dinis Primer 8 900 7,200
04-Aug Delta Supplies Nampula Imran Nation PVA 20 750 15,000
05-Aug Eastside Stores Xai-Xai Ricardo Gloss Enamel 6 1,500 9,000
Each row represents a sale.
Each column describes something about that sale.
This is the basic structure you'll see in many real-world datasets.
- Basic Formatting
Formatting controls how your data looks.
You can change:
Font
Font size
Bold
Background
Borders
Alignment
Number formats
Date formats
For example:
7500
can be displayed as:
7,500.00
The formatting makes the number easier to read.
Alignment
You can align information:
Left
Center
Right
Top
Middle
Bottom
You can also use Wrap Text when text is too long for a cell.
- Number and Date Formatting
Different types of information should be displayed appropriately.
Numbers
7500
can become:
7,500.00
Dates
01/08/2026
can be displayed as:
01-Aug-2026
Good formatting makes a spreadsheet easier to understand without changing the underlying data.
- Sorting and Filtering Sorting
You can arrange Sales from:
Largest → Smallest
For our example:
15,000
9,000
7,500
7,200
6,000
Filtering
You can filter the dataset to show only:
Region = Maputo
Now you see only the Maputo sales.
Sorting and filtering are simple but important tools for exploring data.
- Conditional Formatting
Conditional formatting automatically changes the appearance of cells when a condition is met.
For example:
Highlight all sales above 10,000.
Excel can automatically highlight:
15,000
while leaving the other values unchanged.
You can use conditional formatting to identify:
High or low sales
Duplicates
Negative values
Values above/below a target
Trends and patterns
- Text Functions
Excel can also manipulate text.
CONCAT
CONCAT joins text together.
For example:
=CONCAT(B2," - ",D2)
could produce:
Alpha Traders - Dinis
This is useful when you want to combine information from multiple columns.
REPLACE
REPLACE changes part of a text string based on its position.
For example:
=REPLACE(B2,1,5,"New")
This replaces characters within the text.
Important: REPLACE works based on character position.
- Core Excel Formulas
Now we can start asking questions about our sales data.
SUM
Adds values together.
=SUM(H2:H6)
Question:
What are our total sales?
AVERAGE
Calculates the average.
=AVERAGE(H2:H6)
Question:
What is the average sale?
COUNT
Counts cells containing numbers.
=COUNT(H2:H6)
Question:
How many numeric sales records do we have?
COUNTA
Counts cells that aren't empty.
=COUNTA(B2:B6)
Question:
How many customer entries do we have?
MIN and MAX
Find the smallest and largest values.
=MIN(H2:H6)
=MAX(H2:H6)
Questions:
What was the smallest sale?
What was the largest sale?
- IF
IF allows Excel to make a decision.
For example:
=IF(H2>=10000,"High","Normal")
Excel checks the sales value.
If it is 10,000 or more, it returns:
High
Otherwise:
Normal
This is the beginning of using Excel for logical analysis.
- AND / OR
These allow you to test multiple conditions.
AND
=AND(C2="Maputo",H2>=10000)
This asks:
Is the sale from Maputo AND is it at least 10,000?
Both conditions must be true.
OR
=OR(C2="Maputo",C2="Beira")
This asks:
Is the sale from Maputo OR Beira?
Only one condition needs to be true.
- SUMIF and SUMIFS
These allow you to calculate totals based on conditions.
SUMIF
For example:
What are the total sales in Maputo?
=SUMIF(C2:C6,"Maputo",H2:H6)
SUMIFS
SUMIFS allows multiple conditions.
For example:
What are the total Nation PVA sales in Maputo?
=SUMIFS(H2:H6,C2:C6,"Maputo",E2:E6,"Nation PVA")
- COUNTIF and COUNTIFS
These count records based on conditions.
COUNTIF
How many sales came from Maputo?
=COUNTIF(C2:C6,"Maputo")
COUNTIFS
How many Nation PVA sales came from Maputo?
=COUNTIFS(C2:C6,"Maputo",E2:E6,"Nation PVA")
- AVERAGEIF
AVERAGEIF calculates an average based on a condition.
For example:
What is the average sale in Maputo?
=AVERAGEIF(C2:C6,"Maputo",H2:H6)
Instead of calculating the average of every sale, Excel only uses the sales that meet the condition.
Top comments (0)