Excel Queries
for Beginners
Learn to use formulas, filters, VLOOKUP, and Power Query
to find and analyze data in Microsoft Excel
Formulas Filters VLOOKUP Power Query Pivot Tables
01
What is a Query in Excel?
Understanding how to ask questions of your data
What is a Query?
🔍 Query = Question 📊 3 Ways to Query 💡 Why Use Queries?
A query is simply asking Excel to 1. Formulas (IF, VLOOKUP) Save time, reduce errors, instantly
find, filter, or summarize your data. 2. Filters & Sort find what you need, and create
Like asking: 'Show me all sales 3. Power Query (advanced) reports automatically from large
above $500.' datasets.
02
Basic Filters & Sorting
The easiest way to query data in Excel
AutoFilter — Step by Step
1️⃣ Select Your Data 2️⃣ Turn on Filter
Click anywhere inside your data table. Make sure your Go to Data tab → click Filter button. Small dropdown
first row has column headers like: Name, Age, Salary. arrows appear on each column header.
3️⃣ Filter Your Data 4️⃣ Clear the Filter
Click the arrow on any column → uncheck items or use Click Data tab → click Clear to remove the filter and show
'Number Filters' → 'Greater Than' to filter by condition. all data again.
03
Essential Query Formulas
Use formulas to find and calculate data
SUM, COUNT & AVERAGE
Formula Syntax Example Result
SUM =SUM(range) =SUM(B2:B10) Total of all values
COUNT =COUNT(range) =COUNT(A2:A20) Count of numbers
COUNTA =COUNTA(range) =COUNTA(A2:A20) Count non-empty cells
AVERAGE =AVERAGE(range) =AVERAGE(C2:C10) Mean of values
MAX =MAX(range) =MAX(D2:D50) Largest value
MIN =MIN(range) =MIN(D2:D50) Smallest value
SUMIF =SUMIF(range,crit,sum) =SUMIF(A2:A10,'Y',B2:B10) Conditional sum
COUNTIF =COUNTIF(range,crit) =COUNTIF(A2:A20,'Yes') Count matching cells
IF & Logical Formulas
Formula Syntax Example Result
IF =IF(test, yes, no) =IF(B2>100,'High','Low') 'High' or 'Low'
AND =AND(cond1, cond2) =AND(A2>5, B2<10) TRUE or FALSE
OR =OR(cond1, cond2) =OR(A2='Y',A2='Yes') TRUE or FALSE
IFERROR =IFERROR(formula, val) =IFERROR(A2/B2, 0) 0 (instead of error)
IFS =IFS(t1,v1,t2,v2...) =IFS(A2>90,'A',A2>80,'B') Grade letter
SUMIFS =SUMIFS(sum,r1,c1...) =SUMIFS(C2:C10,A2:A10,'Jan') Multi-cond sum
04
VLOOKUP & XLOOKUP
Look up values from another table
VLOOKUP — The Most Used Lookup Formula
=VLOOKUP( lookup_value , table_array , col_index_num , [range_lookup] )
lookup_value table_array col_index_num range_lookup
Use FALSE for exact
What you're searching for. The table where the data Which column to return.
match.
E.g. the product ID or lives. 1=first column,
TRUE = approximate
name. E.g. A1:D100 2=second…
match.
Example: =VLOOKUP(A2, Products!A:D, 3, FALSE) → Finds A2's value in Products sheet, returns
column 3
XLOOKUP — The Modern Alternative
Formula Syntax Example Result
=XLOOKUP(val, look_arr,
XLOOKUP =XLOOKUP(A2,B:B,C:C) Returns matching value
ret_arr)
→ if missing =XLOOKUP(val,look,ret,'N/A') =XLOOKUP(A2,B:B,C:C,'Not found') 'Not found' if missing
→ exact =XLOOKUP(val,look,ret,,0) =XLOOKUP(A2,B2:B10,C2:C10,,0) Exact match
=HLOOKUP(val,table,row,FALSE
HLOOKUP =HLOOKUP('Jan',A1:D3,2,FALSE) Horizontal lookup
)
INDEX =INDEX(range, row, col) =INDEX(A1:C10, 3, 2) Value at row 3, col 2
MATCH =MATCH(val, range, 0) =MATCH('Bob',A2:A20,0) Row number of 'Bob'
=INDEX(ret,MATCH(val,look,0)
INDEX+MATCH =INDEX(C2:C20,MATCH(A2,B2:B20,0)) Flexible lookup
)
05
Power Query Basics
Import, clean, and transform data automatically
Getting Started with Power Query
📂 Open Power Query 🧹 Remove Columns
Go to Data tab → Get Data → From Table/Range (or From Right-click a column header → Remove Column.
File). This opens the Power Query Editor. Removes unwanted columns from your dataset.
🔤 Rename Columns 🔽 Filter Rows
Double-click a column header to rename it. Or right-click Click the dropdown arrow on any column → uncheck
→ Rename. values or use Text/Number Filters to remove unwanted
rows.
Power Query — Transform Tab
🔡 Change Data Type ✂️ Split Column
Click a column → Transform → Data Type. Change to If one column has 'First Last', use Split Column → By
Text, Number, Date, etc. Essential for correct calculations. Delimiter (space) to separate into two columns.
➕ Add Custom Column 📅 Extract from Date
Add Column tab → Custom Column. Write a formula like Select a date column → Add Column → Date → Year /
[Price] * [Qty] to create a new calculated column. Month / Day to extract parts of dates.
06
PivotTables as Queries
Summarize thousands of rows in seconds
Creating a PivotTable
1️⃣ Select Your Data 2️⃣ Insert PivotTable
Click anywhere in your data table. Make sure it has clear Go to Insert tab → PivotTable → OK. Excel asks where to
column headers and no blank rows. place it — choose New Worksheet.
3️⃣ Drag Fields 4️⃣ Choose Summary
In the PivotTable Fields panel: drag items to Rows, In Values area, click the field → Value Field Settings →
Columns, Values, or Filters areas. choose Sum, Count, Average, Max, etc.
📋 Quick Reference Cheat Sheet
🔢 Math Formulas ✅ Logic Formulas 🔍 Lookup Formulas ⚡ Quick Actions
=SUM(A1:A10) =IF(A1>10,"Yes","No") =VLOOKUP(val,table,col,F) Ctrl+Shift+L = AutoFilter
=AVERAGE(A1:A10) =COUNTIF(A:A,"Y") =XLOOKUP(val,look,return) Ctrl+T = Create Table
=COUNT(A1:A10) =SUMIF(A:A,"Jan",B:B) =INDEX(range,row,col) Alt+= = AutoSum
=MAX(A1:A10) =IFERROR(formula, 0) =MATCH(val,range,0) Ctrl+Z = Undo
=MIN(A1:A10) =IFS(A1>90,"A",A1>80,"B") =HLOOKUP(val,table,row,F) Data → Refresh All
You're Ready to Query Excel! 🎉
Start with AutoFilter → then try IF formulas → then VLOOKUP → then Power Query
✅ ✅ ✅
Google any formula — there's
Practice with small data first Use Ctrl+Z to undo mistakes
always help!
[Link]/excel ● [Link] ● [Link]