Microsoft Excel is one of the most widely used data analysis tools on the planet. It
is used by professionals across finance, retail, healthcare, and logistics every single
day. What makes it special is that it is accessible to anyone — available as a free web
version or through a Microsoft 365 subscription — and beginner-friendly enough to pick
up without any coding background.
In this article we will walk through three core Excel features using a real
product dataset to see how they work.
1. Formulas and Functions
Excel has hundreds of built-in functions. Here are the essential ones you need to know
as a beginner.
Mathematical and Statistical Functions
These are the most common functions :
| Function | What it does | Example using our dataset |
|---|---|---|
| SUM | Adds up a range of numbers | =SUM(B2:B113) — total current price of all products |
| AVERAGE | Calculates the mean | =AVERAGE(D2:D113) — average discount offered |
| COUNT | Counts how many entries exist | =COUNT(A2:A113) — number of products listed |
| MAX | Returns the highest value | =MAX(C2:C113) — the highest old price in the dataset |
Logical Functions
The IF function lets you apply logic to your data — essentially telling Excel
"if this condition is true, do this; otherwise, do that."
Using our dataset, we can categorize each product's discount as HIGH, LOW, or AVERAGE:
=IF(D2<20%, "LOW", IF(D2>40%, "HIGH", "AVERAGE"))
Application of the Formulas.
This checks the discount column and labels each product automatically. This kind of categorization saves hours of manual work.
Conditional Calculations
COUNTIF and SUMIF are extensions of logical thinking — they count or sum only
the rows that meet a condition.
To find how many products have an EXCELLENT rating:
=COUNTIF(H2:H113, "EXCELLENT")
Conditional calculations
Connecting Datasets with VLOOKUP
VLOOKUP lets you search for a value in one column and return a related value
from another column — like looking up a product name and returning its price.
=VLOOKUP(A2, $A$1:$I$500, 3, FALSE)
Breaking it down:
-
A2— the product name you are searching for -
$A$1:$I$500— the full data range to search within -
3— return the value from the 3rd column (Old Price) -
FALSE— find an exact match only
Use of VLOOKUP.
2. Pivot Tables
Once you have clean data, Pivot Tables let you summarize and group it in seconds —
no formulas required. They are one of the most powerful tools in Excel for anyone
doing data analysis.
How to Create a Pivot Table
- Click any cell inside your dataset (e.g. cell A1)
- Go to Insert → PivotTable
- A dialog box appears — Excel automatically selects your full data range
- Choose New Worksheet so your Pivot Table gets its own clean tab
- Click OK
You will now see a blank Pivot Table on the left and a PivotTable Fields panel
on the right.
The Four Drop Zones
| Area | What it does |
|---|---|
| Filters | Filters the entire table by a field |
| Columns | Spreads values across columns |
| Rows | Groups data down the rows |
| Values | The number you want to calculate (sum, count, average) |
Example: Average Price by Discount Category
Drag Discount Category to Rows and Current Price to Values. Then change
the Values setting to Average. Your Pivot Table instantly shows which discount
tier has the highest average price — no formulas needed.
Example pivot table from dataset.
Adding Slicers
Slicers are clickable filter buttons that make your Pivot Table interactive —
perfect for presentations.
- Click anywhere inside your Pivot Table
- Go to PivotTable Analyze → Insert Slicer
- Select the fields you want to filter by (e.g. Rating Category)
- Click OK
Clicking a slicer button instantly filters the entire table. In our dataset,
clicking EXCELLENT on the Rating slicer immediately shows only top-rated products.
Slicer inserted and filtered under Rating category.
3. Data Visualization
Numbers in a table are hard to interpret quickly. Charts turn your data into a visual
story your audience can understand at a glance.
Choosing the Right Chart
| What you want to show | Best chart type |
|---|---|
| Comparing categories side by side | Column chart |
| Ranking items (especially with long names) | Horizontal bar chart |
| Showing proportions of a whole | Pie or Donut chart |
| Showing a trend over time | Line chart |
How to Add a Pivot Chart
- Click anywhere inside your Pivot Table
- Go to PivotTable Analyze → PivotChart
- Choose your chart type — a Clustered Column for category comparisons
- Click OK
The chart is now linked directly to your Pivot Table. When you click a slicer,
both the table and the chart update at the same time.
What Our Data Reveals
Using a column chart on our dataset, we can immediately see:
- EXCELLENT-rated products have an average rating of 4.68 out of 5
- AVERAGE-rated products sit at 3.84
- POOR-rated products drop to 2.90
Without the chart, these differences are easy to miss in a table. Visualized as bars,
the gap is obvious in under a second.
Three Quick Formatting Tips
Give your chart a meaningful title. Instead of "Chart 1", write something that should state the insight,
not just the topic.
Remove the field buttons. Right-click the chart and select Hide All Field Buttons
— they clutter the chart when presenting.
Add data labels. Right-click a bar and choose Add Data Labels so the exact
values are visible without the audience having to read the axis.
Conclusion
These three features — formulas, Pivot Tables, and charts — form the foundation of
real-world data analysis in Excel. After learning them all together I have been able to use raw data, analyze and create presentation-ready dashboard without writing a single line of code.
Top comments (0)