Why Every Data Analyst Should Learn Excel in 2026

Raw data in Sheet 'Raw', columns: A Name, B City, C Phone, D OrderDate

Clean city (spaces + case):
  =PROPER(TRIM(Raw!B2))

Remove non-breaking spaces copied from web pages:
  =TRIM(SUBSTITUTE(Raw!B2, CHAR(160), " "))

First and last name from "Firstname  Lastname" (double space):
  First: =TEXTBEFORE(TRIM(Raw!A2), " ")
  Last : =TEXTAFTER(TRIM(Raw!A2), " ", -1)

Keep only the last 10 digits of a phone number:
  =RIGHT(SUBSTITUTE(SUBSTITUTE(Raw!C2," ",""),"-",""),10)

Text date "15/03/2026" to a real date (dd/mm/yyyy):
  =DATE(RIGHT(Raw!D2,4), MID(Raw!D2,4,2), LEFT(Raw!D2,2))

Flag duplicates by order ID (column A of table Orders):
  =IF(COUNTIF(Orders[OrderID], [@OrderID])>1, "Duplicate", "")

Count blank cells in a required column:
  =COUNTBLANK(Orders[Amount])

Count text values hiding in a numeric column:
  =SUMPRODUCT(--ISTEXT(Orders[Amount]))

Unique list of cities after cleaning (spills down):
  =SORT(UNIQUE(Orders[City]))

Reconcile: row count before vs after cleaning
  =ROWS(Raw!A2:A5001) & " raw vs " & ROWS(Orders[OrderID]) & " clean"

Bring region from the Customers table into Orders:
  =XLOOKUP([@CustomerID], Customers[CustomerID], Customers[Region], "Missing")

All orders for the city typed in cell H1, newest first:
  =SORT(FILTER(Orders, Orders[City]=H1, "No orders"), 4, -1)
  (4 = position of the OrderDate column in the table)

Orders between two dates in H2 and H3:
  =FILTER(Orders, (Orders[OrderDate]>=H2)*(Orders[OrderDate]<=H3))

Either of two categories (OR uses +):
  =FILTER(Orders, (Orders[Category]="Laptops")+(Orders[Category]="Tablets"))

Reference a spilled range elsewhere with #:
  =COUNTA(K2#)

One-formula summary table: city, orders, revenue

=LET(
   cities,  SORT(UNIQUE(Orders[City])),
   orders,  COUNTIFS(Orders[City], cities),
   revenue, SUMIFS(Orders[Amount], Orders[City], cities),
   HSTACK(cities, orders, revenue)
)

Add a header row on top:
=VSTACK({"City","Orders","Revenue"},
   LET(c, SORT(UNIQUE(Orders[City])),
       HSTACK(c, COUNTIFS(Orders[City], c),
                 SUMIFS(Orders[Amount], Orders[City], c))))

(HSTACK, VSTACK and TAKE need Microsoft 365.)

let
    Source   = Csv.Document(File.Contents("C:\Reports\sales_export.csv"),
                 [Delimiter=",", Encoding=65001]),
    Headers  = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Trimmed  = Table.TransformColumns(Headers,
                 {{"City", each Text.Proper(Text.Trim(_)), type text}}),
    Typed    = Table.TransformColumnTypes(Trimmed,
                 {{"OrderDate", type date}, {"Amount", type number}}, "en-IN"),
    NoBlanks = Table.SelectRows(Typed, each [OrderID] <> null and [OrderID] <> ""),
    Deduped  = Table.Distinct(NoBlanks, {"OrderID"}),
    AddMonth = Table.AddColumn(Deduped, "Month",
                 each Date.StartOfMonth([OrderDate]), type date)
in
    AddMonth

PIVOT FOR MONTHLY REGION REVIEW

Rows    : Region, then City
Columns : Month (grouped by Months and Years)
Values  : Sum of Amount       -> Number format, no decimals
          Count of OrderID    -> rename "Orders"
          Sum of Amount again -> Show Values As
                                 % of Column Total
Filters : Category
Slicers : Channel, Product Line
Timeline: OrderDate

Then: PivotTable Analyze > Insert Slicer
      Slicer > Report Connections > tick every pivot
      Data > Refresh All after replacing the source file

DATA CLEANING CHECKLIST (before any analysis)

[ ] Raw export saved untouched on its own sheet or file
[ ] Row count of raw data noted: ______
[ ] Headers in one row, no merged cells
[ ] Text trimmed, case made consistent
[ ] Dates are real dates (right-aligned, can group)
[ ] Numbers are numbers (no text, no currency symbols)
[ ] Duplicates checked on the key column
[ ] Blanks in required columns counted and explained
[ ] Lookup gaps (#N/A / "Missing") investigated
[ ] Clean data formatted as a Table and named
[ ] Final totals reconciled with the source system

EXCEL CHEAT SHEET FOR ANALYSTS

Clean   : TRIM  PROPER  UPPER  SUBSTITUTE  CLEAN
          TEXTBEFORE  TEXTAFTER  TEXTSPLIT  VALUE
Lookup  : XLOOKUP(key, lookup_col, return_col, "Missing")
Filter  : FILTER(table, condition, "None")
          AND = (cond1)*(cond2)   OR = (cond1)+(cond2)
Distinct: UNIQUE(range)   COUNTA(UNIQUE(range))
Sort    : SORT(range, col, -1)   SORTBY(range, by, -1)
Top N   : TAKE(SORTBY(names, values, -1), N)
Summary : SUMIFS  COUNTIFS  AVERAGEIFS  MAXIFS
Readable: LET(name, value, ..., result)
Errors  : IFERROR(formula, "")   ISNUMBER  ISTEXT
Keys    : Ctrl+T table   Ctrl+Shift+L filter   Ctrl+Alt+F5 refresh all

ONE-PAGE DASHBOARD PLAN

Audience       : ____________ (who reads it, how often)
Main question  : ____________________________________

KPI row (max 5): Revenue | Orders | Avg order value |
                 New customers | Return rate
                 each vs previous period
Chart 1        : Monthly revenue trend (line)
Chart 2        : Revenue by region (sorted bar)
Chart 3        : Top 10 products (table or bar)
Slicers        : Channel, Category + date timeline
Data source    : Power Query -> Orders table
Refresh        : Replace file, Data > Refresh All
Check          : KPI totals match source system