DEV Community

Emmanuel Atemba
Emmanuel Atemba

Posted on

Learn Excel in a week!

#msexcel #excel #datascience #data

Everyone in the tech field or at university has at least heard of Microsoft Excel. However, the extent to which Excel is used—and how powerful it becomes—largely depends on how well you understand it. Some people use Excel simply to display data, such as sales figures or transaction records, while others go further by uncovering insights through charts, graphs, and advanced summaries.
In this article, I aim to highlight what Excel can truly help you achieve. Excel is more than a tool for displaying data; it is a powerful platform for data storytelling and decision-making when used effectively.

Table of Content

  1. Getting started with excel software
  2. Formulas in Excel
  3. Working with Tables in excel
  4. Working with Pivot Tables in excel
  5. Charts and Graphs
  6. Beyond the norm: Building excel dashboards

1. Getting Started with Excel

Before diving into formulas and dashboards, it’s important to understand the basic building blocks of Excel.

Key Parts of Excel

  • Workbook – The Excel file itself
  • Worksheet – Individual tabs (e.g., Staging, Analysis, Dashboard)
  • Cells – The intersection of rows and columns (e.g., A1, B2)
  • Ranges – A selection of multiple cells (e.g., A1:D20)
  • Formula Bar – Where formulas are written and edited
  • Ribbon – The toolbar containing commands (Home, Insert, Data, etc.)

In most real-world scenarios, data first lands in Excel in a raw, unstructured format—just like the Staging sheet. The goal is to transform this raw data into insights.

2. Formulas in Excel (With Real Sales Data Examples)

Let’s assume the Staging sheet contains raw sales data with columns similar to:

OrderID, OrderDate, RequiredDate, Region, 
Country, City,CustomerSegment   Channel, 
Salesperson, ProductCategory, SKU, UnitCost, 
UnitPrice   DiscountPct, Quantity
Enter fullscreen mode Exit fullscreen mode

We’ll convert this data into an Excel Table named SalesData.
This allows us to write clean, readable formulas using column names.

Simple but Powerful Excel Formulas

1. Total Revenue Generated

Business Question: How much revenue have we generated in total?

> =SUM(SalesData[Revenue])

2. Average Order Value

Business Question: On average, how much does a customer spend per order?

=AVERAGE(SalesData[Revenue])

3. Total Units Sold

Business Question: How many items have we sold overall?

=SUM(SalesData[Quantity])

4. Number of Orders

Business Question: How many sales transactions were completed?

=COUNTA(SalesData[OrderID])

5. Earliest and Latest Sale Date

Business Question: What is the time range of our sales data?

=MIN(SalesData[OrderDate])

=MAX(SalesData[OrderDate])

Intermediate & Advanced Excel Formulas for Sales Analysis

These formulas answer real analytical questions businesses care about.

6. Revenue by Category

Business Question: How much revenue does each product category generate?

=SUMIFS(
  SalesData[Revenue],
  SalesData[Category],
  A2
)
Enter fullscreen mode Exit fullscreen mode

Where A2 contains a category name (e.g., Electronics).

7. Revenue by Region

Business Question: Which region is performing best?

=SUMIFS(
  SalesData[Revenue],
  SalesData[Region],
  A2
)
Enter fullscreen mode Exit fullscreen mode

8. Number of Orders per Customer

Business Question: Who are our most frequent customers?

=COUNTIFS(
  SalesData[CustomerName],
  A2
)
Enter fullscreen mode Exit fullscreen mode

9. Monthly Sales Trend

Business Question: How do sales change month over month?

First, extract the month:

=TEXT([@OrderDate],"mmm-yyyy")

Then calculate monthly revenue:

=SUMIFS(
SalesData[Revenue],
SalesData[Month],
A2
)

10. Calculate Revenue (If Not Provided)

Business Question: How do we derive revenue from raw transaction data?

=[@Quantity] * [@UnitPrice]
Enter fullscreen mode Exit fullscreen mode

📌 Best practice: Always calculate revenue rather than manually entering it.

11. Identify High-Value Orders

Business Question: Which orders exceed a certain revenue threshold?

=IF([@Revenue] > 100000, "High Value", "Standard")
Enter fullscreen mode Exit fullscreen mode

12. Handle Missing or Incorrect Data

Business Question: How do we avoid formula errors in reports?

=IFERROR(
  [@Quantity] * [@UnitPrice],
  0
)
Enter fullscreen mode Exit fullscreen mode

13. Lookup Product Price or Category

Business Question: How do we enrich sales data from a master product list?

=XLOOKUP(
  [@Product],
  Products[ProductName],
  Products[UnitPrice]
)
Enter fullscreen mode Exit fullscreen mode

3. Tables + Formulas = Faster Analysis

Once your formulas are written inside a Table:

  • They automatically copy to new rows
  • They update when new sales data is added
  • They integrate seamlessly with Pivot Tables and charts

-> Calculated columns inside Excel Table

-> Automatically filled to all data rows

4. Pivot Tables: When Formulas Are Not Enough

While formulas answer specific questions, Pivot Tables help answer exploratory questions, such as:

  • Which category drives the most revenue?
  • How do sales vary by region and month?
  • Who are the top 10 customers?

Pivot Tables often replace dozens of SUMIFSformulas with a drag-and-drop interface.

Pivot Table summarizing revenue by category and region

5. Charts: Turning Answers into Stories

Once formulas and Pivot Tables provide answers, charts help communicate them:

Line chart → Monthly sales trend

Column chart → Revenue by category

Bar chart → Top customers
Enter fullscreen mode Exit fullscreen mode

6. Dashboards: From Analyst to Decision-Maker

A dashboard built on:

Excel Tables

Pivot Tables

Slicers

Charts
Enter fullscreen mode Exit fullscreen mode

Allows leadership to answer questions like:

  • Are sales improving or declining?
  • Which regions need attention?
  • What products drive profitability?

Key Takeaway

Excel formulas are not just calculations—they are questions translated into logic.

If you can clearly ask:

  • What happened?
  • Why did it happen?
  • What should we do next?

Then Excel becomes one of the most powerful data analysis tools you already own.

Top comments (0)