HANDS-ON LEARNING GUIDE

Microsoft Excel
Basic to Advanced
Learn Excel the practical way — using one real-world retail sales dataset
from your first formula to PivotTables, dashboards, and What-If Analysis.

180-row Sales Dataset Real Formulas 13 Practice Exercises Keyboard Shortcuts

Companion file: Excel_Practice_Dataset.xlsx

Prepared for Som · August 2026
Table of Contents

Part 0 The Dataset You'll Use Throughout This Guide

Part 1 Excel Basics — Interface, Data Entry & Simple Formulas

Part 2 Intermediate Excel — References, Logic, Lookups & Formatting

Part 3 Sorting, Filtering & Charts

Part 4 Advanced Formulas — SUMIFS, INDEX/MATCH, Text & Date Functions

Part 5 PivotTables & PivotCharts

Part 6 Data Validation, What-If Analysis & Goal Seek

Part 7 Power Query & Dashboards (Advanced)

Part 8 13 Practice Exercises (with the Dataset)

Part 9 Keyboard Shortcut & Function Reference

How to use this guide: Open the companion file Excel_Practice_Dataset.xlsx side by side with this PDF. Every formula shown here
refers to real cells in the Sales_Data sheet of that file, so you can type them in and see the same results. The Formula_Examples tab
already contains working versions of most formulas — check your answer there once you've tried it yourself.
Part 0 — The Dataset You'll Use Throughout This Guide
Rather than teaching Excel with abstract examples, this guide uses one consistent, realistic dataset: eighteen months of retail sales orders
for a company selling Electronics, Furniture, Office Supplies, Apparel, and Sports products across five U.S. regions.

Sales_Data sheet — 180 orders, 13 columns

Col Field Description

A Order ID Unique order code, e.g. ORD-1001
B Order Date Date the order was placed (Jan 2024 – Jun 2025)
C Region North, South, East, West, or Central

D City City where the order shipped

E Category Product category
F Product Specific product name

G Units Quantity ordered

H Unit Price Price per unit ($)
I Discount Discount applied (%)

J Revenue Formula: =G*H*(1-I)

K Customer Customer name

L Sales Rep Assigned sales representative

M Channel Online, Retail Store, or Wholesale

First 10 rows (preview)

Order ID Date Region Category Product Units Price Disc. Revenue Channel
ORD-1001 4/24/2024 North Furniture Standing Desk 4 $349.00 0% $1,396.00 Retail Store

ORD-1002 2/2/2024 North Office Supplies Stapler 9 $8.99 10% $72.82 Retail Store
ORD-1003 3/5/2025 South Sports Basketball 1 $27.50 20% $22.00 Online
ORD-1004 8/8/2024 East Electronics 27in Monitor 2 $219.00 0% $438.00 Retail Store
ORD-1005 4/15/2025 Central Apparel Cotton T-Shirt 9 $14.99 0% $134.91 Online

ORD-1006 3/12/2024 North Office Supplies Desk Organizer 2 $18.50 20% $29.60 Online

ORD-1007 1/8/2025 South Furniture Standing Desk 11 $349.00 0% $3,839.00 Online

ORD-1008 9/7/2024 South Apparel Baseball Cap 11 $12.50 15% $116.88 Online

ORD-1009 2/27/2024 South Furniture Filing Cabinet 5 $159.50 0% $797.50 Online

ORD-1010 5/26/2025 West Apparel Fleece Jacket 5 $49.99 0% $249.95 Online

Full dataset (180 rows) is in the companion workbook, sheet Sales_Data .

Why one dataset for the whole guide? In the real world you build skills on top of each other against the same data — you don't relearn a
new dataset for every function. By the end of this guide you'll have summarized, filtered, visualized, and pivoted this exact dataset a dozen
different ways, which mirrors how you'll actually use Excel at work.
Part 1 — Excel Basics

1.1 The Excel Interface

When you open Excel_Practice_Dataset.xlsx , you'll see these core pieces:

Ribbon — the tabbed toolbar at the top (Home, Insert, Formulas, Data, Review, View). Each tab groups related commands.
Formula Bar — shows the exact contents (value or formula) of the selected cell.
Name Box — top-left box showing the current cell address; type a cell/range name here to jump to it instantly.
Sheet Tabs — bottom of the window. This workbook has three: Sales_Data , Formula_Examples , Practice_Exercises .
Cell Reference — every cell has an address: column letter + row number, e.g. J2 is column J, row 2.

1.2 Navigating & Selecting

1. Click sheet tab Sales_Data.

2. Press Ctrl + Home to jump to cell A1.
3. Press Ctrl + End to jump to the last used cell (M181).
4. Press Ctrl + ↓ from A1 to jump to the bottom of column A instantly.
5. Click cell A1, hold Shift , click M181 to select the entire table in one motion.

1.3 Entering & Editing Data

Data entry rules that will save you hours of cleanup later:
Keep one row = one record (this sheet: one row = one order). Never merge cells inside a data table.
Keep headers in row 1 only, with no blank rows or merged header cells.
One data type per column — column G (Units) is always a number, column B (Order Date) is always a date, etc.
Press Tab to move right after entering a value, Enter to move down.

Common beginner mistake: typing numbers as text (e.g. typing a quantity with a leading space or apostrophe). Text-formatted numbers are
left-aligned and won't work in SUM/AVERAGE. Numbers should always be right-aligned by default.

1.4 Basic Formulas

Every formula starts with = . Try these in any empty cell, e.g. on the Formula_Examples sheet:

SUM — add a range

=SUM(Sales_Data!J2:J181)

Adds every value in the Revenue column. Result: $98,494.92 total revenue across all 180 orders.

AVERAGE — mean of a range

=AVERAGE(Sales_Data!J2:J181)

Result: $547.19 average revenue per order.

COUNT / COUNTA — count numbers or non-empty cells

=COUNTA(Sales_Data!A2:A181)

COUNT counts only numeric cells; COUNTA counts any non-empty cell (text or number). This returns 180 — the total number of orders.

MAX / MIN — largest and smallest value

=MAX(Sales_Data!J2:J181) → $4,491.00 (biggest single order) =MIN(Sales_Data!J2:J181) → $5.63 (smallest single order)

Try it yourself
1. Click sheet Formula_Examples — every formula above is already built there and live-linked to the data.
2. Click on any formula cell (column B) and look at the Formula Bar to see exactly how it's written.
3. Go back to Sales_Data, change any Unit Price in column H, then return to Formula_Examples — the totals update automatically.
This is the core idea of a spreadsheet: change an input, and every formula that depends on it recalculates.

1.5 Basic Formatting

Task How

Currency format Select cells → Home tab → Number group → click the $ icon, or Ctrl + Shift + 4

Percentage format Select cells → click % icon, or Ctrl + Shift + 5

Bold headers Select row 1 → Ctrl + B

Auto-fit column width Double-click the border between two column headers

Freeze header row View tab → Freeze Panes → Freeze Top Row (already applied in this workbook)

Wrap text Home tab → Alignment group → Wrap Text

Note: This workbook already has the header row frozen and formatted (Section 1.6 shows how it was done) so scrolling through 180 rows still
keeps column names visible.
Part 2 — Intermediate Excel

2.1 Relative vs. Absolute References

This is the single most important intermediate concept. When you copy a formula, Excel shifts relative references but keeps absolute
references (marked with $ ) fixed.
Relative reference: Absolute reference:

=G2*H2 =J2/$J$181

Copied down to row 3, becomes =G3*H3 automatically — perfect for the The $J$181 stays locked even when copied down — useful when every row
Revenue column, since every row needs its own Units × Price. needs to divide by the same fixed total or rate.

Shortcut: select a reference inside a formula and press F4 to cycle through A1 → $A$1 → A$1 → $A1 .

2.2 Logical Functions: IF, AND, OR

Simple IF

=IF(Sales_Data!I2=0,"None","Discounted")

Reads: if the Discount in I2 is 0, return "None", otherwise "Discounted".

Nested IF — discount tiers

=IF(Sales_Data!I2=0,"None",IF(Sales_Data!I2<0.1,"Low","High"))

Three outcomes: None (0% discount), Low (under 10%), or High (10% or more). This is the formula used in row 13 of Formula_Examples .

IF combined with AND / OR

=IF(AND(Sales_Data!C2="West",Sales_Data!M2="Online"),"Target Match","No")

Returns "Target Match" only when both conditions are true — Region is West and Channel is Online.

Tip on nesting: once you need more than 2–3 nested IFs, switch to IFS (Excel 2019+) or a lookup table with VLOOKUP / INDEX-MATCH — it's
far easier to read and debug.

2.3 Lookup Functions

VLOOKUP — the classic lookup

=VLOOKUP("ORD-1050",Sales_Data!A2:J181,5,FALSE)

Searches column A for "ORD-1050", then returns the value 5 columns to the right (Category). The FALSE means "exact match" — almost
always what you want.

VLOOKUP's limitation: it can only look to the right of the search column, and breaks if you insert a new column in between. This is why
professionals increasingly prefer INDEX/MATCH below.

INDEX + MATCH — the flexible alternative

=INDEX(Sales_Data!E2:E181,MATCH("ORD-1050",Sales_Data!A2:A181,0))

MATCH finds the row position of "ORD-1050" inside column A; INDEX then returns the value at that row position from column E
(Category). This combination can look left, right, or even across a different sheet layout — MATCH just needs a position, INDEX just needs
a range.

XLOOKUP: if your Excel version is 2021, Microsoft 365, or newer, XLOOKUP replaces both of the above with one simpler function:
=XLOOKUP("ORD-1050",Sales_Data!A2:A181,Sales_Data!E2:E181) . It looks either direction and returns a clean error if nothing's found. This
guide keeps INDEX/MATCH as the primary technique because it works in every Excel version, including older ones.
 2.4 Conditional Formatting

Highlight high-value orders

1. Select the Revenue range: Sales_Data!J2:J181 .
2. Home tab → Conditional Formatting → Highlight Cell Rules → Greater Than…
3. Enter 300 , choose a green fill, click OK.
4. Every order over $300 in revenue is now highlighted automatically — and stays that way even as data changes.

Data bars for a quick visual scan

1. Select Sales_Data!J2:J181 again.
2. Home tab → Conditional Formatting → Data Bars → pick a gradient fill.
3. Each cell now shows an in-cell bar proportional to its value — useful for scanning 180 rows without a chart.

2.5 Text Functions

Function Example (using Sales_Data) Result

LEFT =LEFT(Sales_Data!A2,3) "ORD"

RIGHT =RIGHT(Sales_Data!A2,4) "1001"

LEN =LEN(Sales_Data!K2) Character count of customer name

UPPER / LOWER / PROPER =UPPER(Sales_Data!E2) "FURNITURE"

CONCAT / & =Sales_Data!D2&", "&Sales_Data!C2 "Detroit, North"

TRIM =TRIM(" Extra Spaces ") Removes leading/trailing/extra spaces

Part 3 — Sorting, Filtering & Charts

3.1 Sorting

Sort orders by Revenue, highest first

1. Click any cell inside the Sales_Data table.
2. Data tab → Sort.
3. Sort by: Revenue, Order: Largest to Smallest. Click OK.

Multi-level sort
1. Data tab → Sort → Add Level.
2. Level 1: Region (A to Z). Level 2: Revenue (Largest to Smallest).
3. Result: orders grouped by region, and within each region, highest revenue first.

Always sort with the whole table selected (or just one cell inside it, letting Excel auto-detect the range). Sorting a single column while
leaving others unselected scrambles the rows and breaks your data.

3.2 Filtering
This workbook already has AutoFilter arrows enabled on row 1 (Data tab → Filter to toggle on any sheet).

1. Click the dropdown arrow on the Category header.

2. Uncheck "Select All", then check only "Electronics".
3. The sheet now shows only Electronics orders — row numbers turn blue to indicate a filter is active.
4. Combine filters: also filter Channel to "Online" — now you see only Electronics orders sold online.
5. Clear filters via Data tab → Clear, or click the header dropdown → "Clear Filter From…".

Filter by number condition

1. Click the dropdown on Revenue.

2. Number Filters → Greater Than… → type 500.
3. Only orders above $500 remain visible.

3.3 Charts

Column chart: Revenue by Region

1. First build a small summary using SUMIF (Part 4 covers this) or a PivotTable (Part 5) — you need 5 rows: one per region, with total
revenue.
2. Select that 2-column summary table.
3. Insert tab → Charts group → Clustered Column.
4. Add a chart title ("Revenue by Region"), and axis labels via the Chart Design tab → Add Chart Element.

Line chart: Revenue trend over time

Best built from a PivotTable grouped by month (see Part 5.3) — line charts communicate trend over time far better than a table of
numbers.

Pie chart: Revenue share by Category

1. Build a summary of total Revenue per Category (5 rows).

2. Select it → Insert tab → Insert Pie or Doughnut Chart.

Chart choice rule of thumb: use column/bar charts to compare categories, line charts to show trend over time, and pie charts only when you
have a handful of categories that sum to a meaningful whole (like 100% of revenue). Avoid pie charts for more than 5–6 slices — they become
unreadable.
Part 4 — Advanced Formulas

4.1 SUMIFS, COUNTIFS, AVERAGEIFS — multi-condition analysis

These are the workhorses of real analytical work — they let you sum, count, or average based on multiple conditions at once, unlike
SUMIF/COUNTIF which handle only one.

SUMIFS — Revenue from West region, Online channel only

=SUMIFS(Sales_Data!J2:J181, Sales_Data!C2:C181,"West", Sales_Data!M2:M181,"Online")

Syntax: SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...) . Result: $10,620.47.

COUNTIFS — orders that are both high-value and discounted

=COUNTIFS(Sales_Data!J2:J181,">300",Sales_Data!I2:I181,">0")

Counts rows where Revenue exceeds $300 and a discount was applied.

AVERAGEIFS — average order value, Wholesale channel

=AVERAGEIFS(Sales_Data!J2:J181,Sales_Data!M2:M181,"Wholesale")

Result: $781.66 — noticeably higher than the overall average of $547.19, since wholesale orders tend to be bulk purchases.

Pattern to remember: in SUMIFS / AVERAGEIFS the range-to-total comes first. In SUMIF (singular) it comes last. This inconsistency trips up
even experienced users — always double check argument order with the formula tooltip Excel shows as you type.

4.2 SUMPRODUCT — the flexible multi-condition tool

=SUMPRODUCT((Sales_Data!C2:C181="South")*(Sales_Data!E2:E181="Furniture")*Sales_Data!J2:J181)

Multiplies arrays element-by-element then sums the result — functionally similar to SUMIFS here, but SUMPRODUCT can also handle
calculations SUMIFS can't, like weighted averages or conditions that mix AND/OR logic.

4.3 Date & Time Functions

Function Example Purpose

YEAR / MONTH / DAY =YEAR(Sales_Data!B2) Extract year (2024), month, or day number

TEXT =TEXT(Sales_Data!B2,"mmmm") Format a date as "April"

EOMONTH =EOMONTH(Sales_Data!B2,0) Last day of the order's month — useful for monthly grouping

DATEDIF =DATEDIF(Sales_Data!B2,TODAY(),"d") Days between order date and today

NETWORKDAYS =NETWORKDAYS(Sales_Data!B2,TODAY()) Business days between two dates, excluding weekends

4.4 Error Handling: IFERROR / IFNA

=IFERROR(INDEX(Sales_Data!E2:E181,MATCH("ORD-9999",Sales_Data!A2:A181,0)),"Order not found")

Wrapping a lookup in IFERROR replaces a raw #N/A with a readable message — essential in any dashboard or report a non-technical
colleague will see.

4.5 Array Thinking with SUMPRODUCT + LEN

=SUMPRODUCT(LEN(Sales_Data!K2:K181))/COUNTA(Sales_Data!K2:K181)

Average character length of customer names — demonstrates how SUMPRODUCT can apply a function across an entire array without
needing to enter it as a special array formula.

Checkpoint: before moving to Part 5, make sure you can explain — in your own words — the difference between SUMIF and SUMIFS,
and between VLOOKUP and INDEX/MATCH. If not, re-read 2.3 and 4.1; everything from here builds on these.
Part 5 — PivotTables & PivotCharts
PivotTables are the single highest-leverage skill in this guide — they let you summarize 180 rows (or 180,000) into a clean cross-tabulation
in seconds, without writing a single formula.

5.1 Building Your First PivotTable

1. Click any cell inside Sales_Data .

2. Insert tab → PivotTable → PivotTable from Table/Range.
3. Confirm the range is Sales_Data!A1:M181 and choose "New Worksheet". Click OK.
4. In the PivotTable Fields pane on the right, drag Category into the Rows box.
5. Drag Revenue into the Values box — it defaults to "Sum of Revenue".

You now have total revenue for each of the 5 categories in five rows — instantly.

Add a second dimension

1. Drag Channel into the Columns box.

2. The PivotTable now shows a grid: Category (rows) × Channel (columns), with revenue in each cell.
3. Right-click any value → "Number Format" → Currency, to format all values as $.

5.2 Useful PivotTable Layouts for This Dataset

Question Rows Columns Values

Which region sells the most? Region — Sum of Revenue

Which rep is top performer? Sales Rep — Sum of Revenue

Category performance by channel Category Channel Sum of Revenue

Average order size by region Region — Average of Revenue

Order count by category Category — Count of Order ID

5.3 Grouping Dates by Month

1. Drag Order Date into the Rows box (put it above or below Category).
2. Right-click any date value inside the Pivot → Group…
3. Choose "Months" (and "Years" if you want them separated), click OK.
4. The Pivot now shows monthly revenue trends — perfect source data for a line chart.

5.4 PivotCharts

1. Click anywhere inside your PivotTable.

2. PivotTable Analyze tab → PivotChart.
3. Choose "Line" for a trend view (if rows are grouped by month) or "Clustered Column" for category comparisons.
4. The chart updates automatically whenever you change the PivotTable's fields or filters — this link is what makes PivotCharts more
powerful than a static chart.

5.5 Slicers — Interactive Filtering

1. Click inside the PivotTable → PivotTable Analyze tab → Insert Slicer.

2. Check "Region" and "Channel", click OK.
3. Two floating buttons-panels appear. Click "West" on the Region slicer — the whole PivotTable (and any linked PivotChart) instantly
filters to West only.
4. Hold Ctrl while clicking to select multiple items in a slicer.
 Why this matters: Slicers turn a static PivotTable into a lightweight, click-to-filter dashboard that non-Excel-users on your team can operate
without touching a formula.

5.6 Calculated Fields

1. Inside the PivotTable, go to PivotTable Analyze tab → Fields, Items & Sets → Calculated Field.
2. Name it "Revenue per Unit", formula: =Revenue/Units .
3. Click Add, then OK — this new metric is now available to drag into the Values box like any other field.
Part 6 — Data Validation & What-If Analysis

6.1 Data Validation — Dropdown Lists

Suppose you want to add a "Priority" column so your team can flag orders needing follow-up.

1. In Sales_Data , add the header "Priority" in cell N1.

2. Select N2:N181.
3. Data tab → Data Validation → Allow: List.
4. Source: type High,Medium,Low directly, or reference a small list of cells elsewhere.
5. Click OK — every cell in N2:N181 now shows a dropdown arrow restricting entry to those 3 options.

Why this matters: data validation prevents typos ("hi" vs "High" vs "high") that would otherwise silently break every COUNTIF/SUMIF that
filters on that column later.

Validation with a custom rule

Allow: Whole Number, Data: greater than, Minimum: 0

Applied to the Units column, this stops anyone from accidentally entering a negative or zero quantity.

6.2 Goal Seek

Goal Seek answers: "What input value gives me the output I want?" — useful for pricing and discount questions.

Example: what discount rate brings a $500 order down to exactly $425?
1. In a blank cell, set up: Units = 1, Unit Price = $500, Discount = 0 (a placeholder), Revenue formula = =Units*Price*(1-Discount) .
2. Data tab → What-If Analysis → Goal Seek.
3. Set cell: the Revenue cell. To value: 425. By changing cell: the Discount cell.
4. Click OK — Excel iterates until it finds Discount = 15%, since 500 × (1−0.15) = 425.

6.3 Data Tables (Sensitivity Analysis)

A Data Table shows how one formula's result changes across a whole range of input values at once — for example, Revenue at every
discount level from 0% to 30%.

1. List candidate discount rates down a column: 0%, 5%, 10%, 15%, 20%, 25%, 30%.
2. In the cell above and one column right, reference your Revenue formula.
3. Select the whole block (rates + formula row) → Data tab → What-If Analysis → Data Table.
4. Leave "Row input cell" blank, set "Column input cell" to your Discount cell.
5. Excel fills in the Revenue result for every discount rate automatically.

6.4 Scenario Manager

For comparing a few named "what-if" scenarios (e.g. "Conservative," "Expected," "Aggressive" growth assumptions) side by side, use Data
tab → What-If Analysis → Scenario Manager to save and switch between complete sets of input values.
Part 7 — Power Query & Dashboards

7.1 Power Query — Cleaning & Combining Data

Power Query (Data tab → Get Data) is built into modern Excel and is the professional way to clean and reshape data before it hits your
worksheet — instead of manually fixing typos or merging files.

Load Sales_Data into Power Query

1. Click inside the table → Data tab → From Table/Range.
2. The Power Query Editor opens showing the same 180 rows in a separate transformation window.
3. Right-click the Region column → Group By → Region, Aggregation: Sum of Revenue. This creates a new summarized table without
touching your source data.
4. Home tab (inside Power Query) → Close & Load → choose "New Worksheet" to bring the result back into Excel as a live, refreshable
table.

Why Power Query beats manual cleanup: every step you take is recorded. If new orders get added to the source data tomorrow, you click
Refresh and the entire cleaned, grouped output rebuilds automatically — no repeating manual work.

Common Power Query transformations

Transformation Where

Remove duplicate rows Home → Remove Rows → Remove Duplicates

Split a column (e.g. "First Last" → two columns) Transform → Split Column → By Delimiter

Change data type Click the type icon in the column header

Merge two tables (like a lookup) Home → Merge Queries

Append two tables (stack rows) Home → Append Queries

Unpivot columns into rows Transform → Unpivot Columns

7.2 Building a One-Page Dashboard

A dashboard combines several of the techniques above onto one sheet for at-a-glance reporting.

1. Insert a new sheet named "Dashboard".

2. Add 3–4 KPI cells at the top: Total Revenue ( =SUM ), Total Orders ( =COUNTA ), Average Order Value ( =AVERAGE ), formatted in large
bold font.
3. Below, place 2–3 charts built from PivotTables: Revenue by Region (column chart), Revenue trend by month (line chart), Revenue
share by Category (pie or donut chart).
4. Add the slicers from Section 5.5 near the top so a viewer can filter every chart and KPI at once, since PivotCharts sharing the same
PivotTable respond to the same slicer.
5. Hide gridlines on the Dashboard sheet (View tab → uncheck Gridlines) for a cleaner, presentation-ready look.

7.3 Intro to Macros (VBA)

For repetitive multi-step tasks (e.g. "clear filters, refresh all PivotTables, export to PDF"), Excel can record your clicks as a macro.

1. View tab → Macros → Record Macro. Name it, click OK.

2. Perform the steps you want automated (e.g. clear all filters, refresh PivotTables).
3. View tab → Macros → Stop Recording.
4. Run it anytime via View tab → Macros → View Macros → Run, or assign it to a button (Insert tab → Shapes, then right-click → Assign
Macro).

Note: macros require saving as a macro-enabled workbook (.xlsm). This is an optional, advanced topic — most analytical work in this guide
doesn't need VBA at all.
Part 8 — 13 Practice Exercises
These are pre-loaded on the Practice_Exercises sheet of the companion workbook. Work through them in order — later exercises build
on earlier ones. Answers/worked formulas are on Formula_Examples where applicable.

Basic

1. Use SUM to total all Revenue in Sales_Data.

2. Use AVERAGE to find the average Units per order.

3. Use COUNTA to count how many orders exist in total.

Intermediate

4. Use COUNTIF to count how many orders came from the Central region.

5. Use SUMIF to total Revenue for the Apparel category only.

6. Apply conditional formatting so any Revenue over $300 is highlighted green.

7. Create a column or bar chart showing total Revenue by Region.

Advanced

8. Use SUMIFS to total Revenue for East region AND Online channel simultaneously.

9. Use INDEX/MATCH to find the Product for Order ID "ORD-1100".

10. Build a PivotTable summarizing total Revenue by Category and Channel.

11. Add a PivotChart showing the Revenue trend by month.

12. Use Data Validation to add a "Priority" column restricted to High/Medium/Low.

13. Use Goal Seek to find what discount rate on a $500 order brings Revenue down to exactly $425.

Self-check: exercise 8's answer is $8,... range — build it, then cross-check against a PivotTable filtered the same way (Category=all,
Region=East, Channel=Online) to confirm your formula and your Pivot agree. This habit — checking one technique against another — is what
separates confident Excel users from ones who trust formulas blindly.
Part 9 — Keyboard Shortcuts & Function Reference

9.1 Essential Keyboard Shortcuts

Shortcut Action

Ctrl + C / Ctrl + V Copy / Paste

Ctrl + Z Undo

Ctrl + Arrow key Jump to the edge of a data region

Ctrl + Shift + Arrow key Select to the edge of a data region

Ctrl + Home / End Jump to A1 / last used cell

Ctrl + T Convert range to a formatted Table

Ctrl + 1 Open Format Cells dialog

F4 Toggle relative/absolute reference; also "repeat last action"

Alt + = AutoSum the selected range

Ctrl + ; Insert today's date

Ctrl + Shift + L Toggle AutoFilter

Alt + F1 Insert a quick chart of the selected data

Ctrl + ` Toggle showing formulas vs. values in every cell

9.2 Function Quick Reference

Category Functions

Math & Stats SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, MEDIAN, ROUND

Logical IF, IFS, AND, OR, NOT, IFERROR, IFNA

Conditional Aggregation SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS

Lookup VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP (365/2021+)

Text LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, PROPER, CONCAT, TEXT, SUBSTITUTE

Date & Time TODAY, NOW, YEAR, MONTH, DAY, EOMONTH, DATEDIF, NETWORKDAYS

Array/Advanced SUMPRODUCT, UNIQUE, FILTER, SORT (365/2021+)

9.3 Where to Go Next

Practice the same techniques on your own data — the fastest way to cement these skills is applying them to something you actually
care about.
Explore Power Pivot and the Data Model once you're comfortable with standard PivotTables — they let you relate multiple tables
together, similar to a small database.
Learn basic SQL alongside Excel; the logic of GROUP BY and WHERE clauses maps almost directly onto SUMIFS and PivotTables, and is a
natural next step if you're building broader data-analysis skills.
For very large datasets (500K+ rows), Excel starts to strain — that's the point to explore Power BI or a lightweight Python/pandas
workflow.

End of guide · Companion file: Excel_Practice_Dataset.xlsx