This is the master Excel hub. It covers classic and modern formulas (VLOOKUP, XLOOKUP, GROUPBY, PIVOTBY, MAP/SCAN/REDUCE, LAMBDA), pivot tables, Power Query, Power Pivot, dashboards, AI-assisted Excel workflows (ChatGPT, Claude, Microsoft Copilot, Gemini, Gemma 4), and the build-this-thing project tutorials (budget, inventory, calendar, attendance tracker, financial model). The AI sections are also indexed on the AI hub.
Who This Guide Is For
If you are an absolute beginner who has only used SUM, COUNT, and IF, start with the Lookup Functions section — VLOOKUP is the gateway to everything else. If you are an intermediate user comfortable with VLOOKUP and basic pivot tables, jump to Dynamic Array Functions and Advanced Formulas — GROUPBY, PIVOTBY, and LAMBDA separate power users from everyone else. If you want to use AI inside Excel, head to the AI Tools and AI Workflows sections — they cover ChatGPT, Claude, Microsoft Copilot, Gemini, and free local models like Gemma 4. If you are a trainer or team lead, the Productivity section covers the operational skills (Tables, structured references, shortcuts) that make every other skill faster.
How to Navigate This Reference
The hub is organised into nine sections that follow a natural learning path. Lookup Functions covers VLOOKUP, XLOOKUP, INDEX MATCH, and XMATCH. Dynamic Array Functions covers the modern Excel features (GROUPBY, PIVOTBY, MAP, SCAN, REDUCE, CHOOSECOLS, TAKE/DROP). Advanced and Reusable Formulas covers LET, LAMBDA, advanced patterns, and Python in Excel. Pivot Tables and Data Analysis covers the classic analysis stack plus dashboards and charts. Debugging and Error Fixing covers the errors that waste time in real workbooks. AI Tool Overviews and Comparisons ranks ChatGPT, Claude, Copilot, Gemini, Gemini Omni for spreadsheet video workflows, and Gemma 4 for Excel. AI for Excel by Tool goes deep on each AI assistant's Excel-specific workflows, including Gemini Omni when spreadsheet insights need to become short videos. AI for Excel by Industry covers accountants, HR, sales ops, and AI-assisted forecasting/M-code. Build-This-Thing Projects and Productivity covers the project tutorials and foundational habits.
What to Read First
If you only have an hour, read these three: VLOOKUP vs XLOOKUP, Excel Tables Best Practices, and LET and LAMBDA. If you have a weekend, add GROUPBY and Pivot Tables. If you want AI in your Excel workflow, read 7 Best AI Tools for Excel and 60 AI Prompts for Excel.
Key takeaways
- XLOOKUP replaces VLOOKUP in most cases but INDEX MATCH still wins on multi-criteria and non-standard layouts.
- Dynamic array functions (GROUPBY, PIVOTBY, MAP, SCAN, REDUCE) let you rebuild pivot-style reports as formulas that auto-update.
- LAMBDA turns repeated formula logic into named, reusable functions — the closest Excel gets to programming.
- ChatGPT, Claude, and Gemini handle general Excel formula writing well; Microsoft Copilot is the only tool with first-party access to live workbook context.
- For VBA macros and formula debugging, Claude currently produces the most reliable output across tested workloads.
- Always review AI-generated formulas against a sample of real data before trusting them at scale — the review process is part of the workflow.
- Excel Tables with structured references are more maintainable than A1:Z100 ranges in every practical workbook.
Lookup Functions
- VLOOKUP vs XLOOKUP: Differences and When to Use Each
The definitive comparison with version requirements.
- How to Use INDEX MATCH in Excel with Multiple Criteria
Multi-criteria lookups that VLOOKUP cannot handle.
- XMATCH in Excel: Smarter Lookups, Reverse Searches, Binary Search
XMATCH's advantages over MATCH.
Dynamic Array Functions
- GROUPBY Function in Excel: Formula-Based Summaries Without a Pivot Table
Replacing pivot tables with GROUPBY formulas.
- PIVOTBY Function in Excel: Build Pivot-Style Reports With a Formula
Cross-tab reports as a single formula.
- GROUPBY vs PivotTable in Excel
When each approach wins.
- MAP, SCAN, and REDUCE in Excel
Functional array logic in modern Excel.
- CHOOSECOLS, CHOOSEROWS, TAKE, and DROP in Excel
Data slicing with modern array functions.
Advanced and Reusable Formulas
- LET and LAMBDA in Excel
LET for readability, LAMBDA for reusability.
- 15 Excel Formulas That Save Hours of Manual Work
The essential advanced formula library.
- PY Function in Excel
Python formulas inside Excel cells.
- Python in Excel for Beginners
Python in Excel starting point.
Pivot Tables and Data Analysis
- How to Create a Pivot Table in Excel Step by Step
Pivot tables from scratch to advanced.
- How to Use Power Query in Excel to Automate Data Cleaning
Data transformation without formulas.
- How to Use Power Pivot in Excel to Analyse Millions of Rows
Large-scale data analysis in Excel.
- How to Build an Interactive Dashboard in Excel (No VBA)
Dashboards with pure formulas and slicers.
- How to Make Professional Charts in Excel
Chart selection and formatting.
- Map Charts in Excel
Geographic charts with the Geography data type.
- How to Build a Financial Model in Excel From Scratch
3-statement and DCF models for beginners.
Debugging and Error Fixing
- How to Fix #SPILL! Errors in Excel
Spill error causes and fixes.
- How to Audit Formulas in Excel
Formula debugging and tracing.
- How to Clean Messy Data in Excel
Cleaning imported data at scale.
AI Tool Overviews and Comparisons
- 7 Best AI Tools for Excel in 2026 (Tested and Ranked)
Head-to-head ranking of ChatGPT, Claude, Gemini, Copilot, and Gemma 4.
- ChatGPT vs Claude vs Copilot vs Gemini for Excel in 2026
Direct comparison on formulas, macros, data analysis, and error fixing.
- 60 AI Prompts for Excel That Actually Work
Copy-paste prompt library grouped by task type.
- How to Write AI Prompts for Excel: Copilot, Claude & ChatGPT
The skill behind the prompt library — prompt anatomy and a different approach for each tool.
- 60 AI Prompts for Google Sheets That Actually Work
Google Sheets-native prompts for Gemini, QUERY, ARRAYFORMULA, Apps Script, and shared dashboards.
- How to Review AI-Generated Excel Formulas Before You Trust Them
Verification workflow that prevents silent errors.
AI for Excel by Tool
- How to Use ChatGPT to Write Excel Formulas (With Real Examples)
ChatGPT prompt patterns for formula generation.
- How to Use Claude AI to Write Excel Formulas Instantly
Claude's Excel formula generation workflow.
- How to Install and Use Claude for Excel (2026 Guide)
Install the official add-in and use Claude inside the workbook with cited cells.
- How to Use Claude AI to Write Excel Macros and VBA Code
Generating production-ready VBA from plain English.
- How to Fix Excel Formula Errors with Claude AI
Debugging #REF, #VALUE, #NAME, #SPILL with Claude.
- Claude Agent Mode in Excel
When Claude's agent mode outperforms direct prompting.
- How to Set Up and Use Microsoft Copilot in Excel (2026)
First-time setup, licensing, and what to try.
- Agent Mode in Excel: What It Does and Who Should Use It
Honest scope assessment for Copilot Agent Mode.
- Analyst vs Agent Mode vs Copilot Chat
Choosing the right Copilot mode for the task.
- COPILOT Function in Excel: Syntax, Use Cases, Limits
The new native COPILOT() function explained.
- How to Automate Excel Tasks with Microsoft Copilot
Task automation patterns inside Copilot.
- How to Use Microsoft Copilot for Data Analysis in Excel
Building reports and insights with Copilot.
- Copilot in Excel With Python
Combining Copilot with Python for forecasting.
- Format Data for Copilot in Excel
Data shapes Copilot handles well vs badly.
- Create Charts With Copilot in Excel
Copilot chart generation and its blind spots.
- Create Lookups With Copilot in Excel
Copilot's lookup formula strengths and weaknesses.
- Generate Formula Columns With Copilot
Bulk formula generation patterns.
- Generate Single-Cell Formulas With Copilot
Single-cell Copilot prompts that work reliably.
- How to Use Google Gemini to Write Excel Formulas for Free
Free Gemini workflow for spreadsheet formulas.
- Gemini Omni for Excel
Turn charts, dashboards, and spreadsheet insights into AI videos without misrepresenting the data.
- Gemma 4 for Data Analysis in Excel
Open-weight Gemma 4 tested on real analysis tasks.
- Gemma 4 vs GPT-4o vs Llama 4 for Excel Formulas
Free model comparison for Excel use cases.
AI for Excel by Industry and Workflow
- Excel + AI for Accountants
Reconciliations, variance reviews, and close prep.
- Excel + AI for HR Teams
Hiring trackers, attrition analysis, and reporting.
- Excel + AI for Sales Ops
Pipeline cleanup, forecasts, and territory reporting.
- Build a Forecasting Model in Excel With AI Assistance
End-to-end AI-assisted forecasting.
- Use AI to Write and Fix Power Query M Code
AI-assisted M-code generation.
- Text Analysis in Excel With AI
Sentiment and theme extraction from unstructured text.
Build-This-Thing Projects and Productivity
- Excel Tables Best Practices
Why every data range should be a Table.
- How to Highlight Rows Based on Cell Value in Excel
Formula-based rules, data bars, colour scales, icon sets.
- How to Add a Dropdown List in Excel Using Data Validation
Dropdowns, number rules, dependent lists, and custom validation.
- How to Build a Monthly Budget Spreadsheet in Excel
Three-sheet budget pattern with SUMIFS and Tables.
- How to Create an Amortization Schedule in Excel
PMT/IPMT/PPMT schedule with extra payment scenarios.
- How to Create a Calendar in Excel That Updates Automatically
Driver-cell calendar with DATE, WEEKDAY, SEQUENCE.
- How to Build an Inventory Tracker in Excel
Three-sheet Items/Movements/Dashboard pattern.
- How to Create an Attendance Tracker in Excel
Roster/grid/summary build with COUNTIF and data validation.
- Protect an Excel Workbook Without Breaking Collaboration
Worksheet protection and co-authoring checks.
- Complete List of Excel Keyboard Shortcuts (Windows and Mac)
200+ shortcuts, grouped by category, with Mac equivalents.
- Excel vs Google Sheets: Which Is Better for You in 2026?
Platform comparison for decision makers.
Frequently asked questions
Should I still use VLOOKUP or switch to XLOOKUP?
Switch to XLOOKUP if your Excel version has it (Microsoft 365 and Excel 2021 onward). It looks left as well as right, returns whole arrays, has a built-in not-found argument, and does not break when you insert columns. Keep VLOOKUP only for compatibility with older files shared across Excel 2019 and earlier, where XLOOKUP simply does not exist.
Can AI write Excel formulas for me?
Yes. ChatGPT, Claude, and Gemini all write working formulas from a plain-English description, and Microsoft Copilot is the only assistant with first-party access to your live workbook context. The reliable workflow is to describe the columns and the result you want, then test the formula against a sample of real rows before trusting it at scale — AI still invents column references and misreads edge cases.
What is the difference between a pivot table and the GROUPBY function?
A pivot table is a manual, click-to-build summary you refresh by hand; GROUPBY is a dynamic array formula that recalculates automatically when the source data changes. Use a pivot table for quick exploration and drag-and-drop analysis, and use GROUPBY (or PIVOTBY) when you want a report that stays live inside a dashboard without a refresh step.
Do I need to know VBA to be good at Excel in 2026?
No. Dynamic array functions, LAMBDA, Power Query, and Python in Excel now handle most of what people once wrote macros for, without the maintenance burden of VBA. VBA is still worth knowing for legacy automation and bespoke add-ins, but it is a specialised skill in 2026, not a prerequisite for advanced Excel work.
Is Excel still worth learning when Google Sheets and AI tools exist?
Yes. Excel is still the standard in finance, accounting, and operations, and it now outpaces Google Sheets on dynamic arrays, Power Query, Power Pivot, and Python integration. AI assistants make Excel faster rather than replacing it — they generate and explain formulas, but you still need to structure the workbook and judge whether the output is correct.