Top 100 Excel Tips and Tricks to Make You Productive in 2026

✦ Updated March 2026Includes AI & Copilot

Top 100 Excel Tips and Tricks to Make You Productive in 2026

It's 2026, and a lot of things have changed in Microsoft Excel because of AI and other things happening in the tech world. Now, this tutorial is completely dedicated to giving you the most amazing Excel tips and tricks in 2026.

And that's why I have worked for almost 10 hours to list all these tips and tricks and explain them in a really simple way.

AI & Copilot

Classic Tips

Categories

You can search for these tips and trick with the follfowing search bar.

Showing 103 of 103 tips

No tips found

Try adjusting your search or filters

✦

AI & Copilot AI-Powered

14 tips

▾

Copilot Agent Mode New Advanced

Multi-step autonomous workflows from a single prompt.

▾

Agent Mode plans a sequence of actions, executes them in the workbook, reviews results, identifies errors, and iterates until the output matches your intent. It's the biggest leap in Excel AI.

Add your screenshot or GIF here
Agent Mode executing a multi-step workflow

Agent Mode planning and executing steps in the Copilot pane

Step-by-step

Click Copilot in the Home ribbon.
Type a complex request like: "Build a loan calculator with monthly payment schedules based on amount, rate, and term — then create a summary chart."
Watch Agent Mode plan and execute multiple steps in the Copilot pane.
Review each step — you can pause, redirect, or undo at any point.
Accept the result or ask Copilot to refine further.

Model Switcher: Inside Agent Mode you can switch between OpenAI and Anthropic reasoning models using the model selector dropdown. Try different models for different tasks.

Requires a Microsoft 365 Copilot license. GA on Excel Web, Windows, and Mac since early 2026.

=COPILOT() Formula Hot Intermediate

Use AI directly as a cell formula — classify, generate, and enrich data at scale.

▾

The =COPILOT() function brings AI into the formula bar. It works like any Excel formula — with a prompt and optional cell context.

COPILOT Function Examples

// Basic syntax
=COPILOT("prompt", context)

// Classify sentiment of customer feedback
=COPILOT("Classify as Positive, Negative, or Neutral", A2)

// Enrich data — get state for each city
=COPILOT("What US state is this city in?", B2)

// Summarize text at scale
=COPILOT("Summarize in 10 words", C2:C100)

Add your screenshot or GIF here
COPILOT formula classifying customer feedback

The COPILOT function processing customer reviews in real-time

The context parameter is optional but recommended. You can pass multiple prompts and contexts for complex tasks.

Formula Auto-Complete New Beginner

Copilot suggests complete formulas as you type '=' based on worksheet context.

▾

When you type = in any cell, Copilot analyzes your worksheet context — headers, nearby cells, existing formulas, and table structure — and suggests a complete formula with a preview of the result.

Step-by-step

Click on an empty cell where you want a formula.
Type = and wait 1 second.
Copilot shows a gray suggestion with a formula and its result preview.
Press Tab to accept, or keep typing to ignore.

Add your screenshot or GIF here
Formula auto-complete suggesting SUM based on column header

Copilot suggesting =SUM(B2:B15) based on the Revenue column header

Works best when your data has clear headers and is formatted as an Excel Table (Ctrl+T). The more structured your data, the smarter the suggestions.

Python in Excel + Copilot New Advanced

Describe analysis in plain English. Copilot writes Python code using pandas, matplotlib, scikit-learn.

▾

Combine the power of Python with Copilot's natural language understanding. Describe what you want, and Copilot writes Python code inserted directly into Excel cells.

Python in Excel

import pandas as pd
from statsmodels.tsa.arima.model import ARIMA

df = xl("Table1[Sales]", headers=True)
model = ARIMA(df, order=(1,1,1))
forecast = model.fit().forecast(steps=4)

Add your screenshot or GIF here
Python heatmap rendered inside Excel

A matplotlib visualization rendered directly inside an Excel cell

Requires Microsoft 365 Business license (Version 2408+) and a Copilot license for AI-powered code generation. Python runs in a secure Microsoft Cloud container.

Think Deeper (Advanced Analysis) Advanced

Copilot creates a full analysis worksheet with Python — forecasting, clustering, ML models.

▾

Step-by-step

Open your data workbook and launch Copilot.
Type "Start with Think Deeper to do Python analysis" or select the Think Deeper button.
Copilot creates a new analysis worksheet with structured Python code.
Review the generated analysis — it may include statistical tests, forecasting models, or clustering.
Modify or extend the analysis by prompting Copilot further.

Add your screenshot or GIF here
Think Deeper creating an analysis worksheet

Copilot generating a full forecast analysis with ARIMA model

Think Deeper uses reasoning models to create more structured analysis plans. It's best for complex datasets where you want comprehensive analysis.

Natural Language → Formulas Beginner

Describe what you need in English, Copilot writes the exact formula.

▾

Stop googling formula syntax. Just tell Copilot what you need in plain English and it writes the formula for you.

Natural Language to Formula

// You say: "Calculate year-over-year % change"
// Copilot writes: =(B2-B1)/B1

// You say: "Find the second highest salary"
// Copilot writes: =LARGE(C2:C100, 2)

// You say: "Lookup price from Products sheet by SKU"
// Copilot writes: =XLOOKUP(A2, Products!A:A, Products!C:C)

Copilot also explains the formula it writes. Click "Explain formula" in the Copilot pane to understand each part — great for learning.

AI Data Cleaning Beginner

Copilot detects duplicates, mismatched dates, extra whitespace, mixed types and proposes fixes.

▾

Step-by-step

Select your data range or table.
Open Copilot and type "Clean this data" or "Find data quality issues".
Copilot highlights: duplicate rows, inconsistent date formats, extra whitespace, mixed types.
Review each suggestion and click Apply to fix.

Add your screenshot or GIF here
Copilot identifying data quality issues

Copilot detecting mixed date formats and proposing corrections

For best results, convert your data to an Excel Table first (Ctrl+T). Copilot understands table structure better than raw ranges.

Chat with Your Workbook New Beginner

Ask questions about your data in natural language. Now works with local files.

▾

Copilot Chat lets you have a conversation with your data. Ask questions like "What was our highest revenue month?" or "Which product had the most returns?" and get instant answers as text, charts, or PivotTables.

Add your screenshot or GIF here
Copilot Chat answering data questions

Asking Copilot about top-selling products and getting instant answers

New in 2026: Copilot Chat now works with local workbooks stored on your Windows or Mac computer — not just OneDrive/SharePoint cloud files.

AI-Powered Insights Intermediate

Copilot automatically surfaces trends, outliers, and anomalies in your data.

▾

Step-by-step

Select your data table.
Open Copilot and click "Identify insights".
Copilot generates charts, PivotTables, and text summaries highlighting key patterns.
Click any insight to insert it directly into your workbook.

Ask follow-up questions like "Why did revenue drop in March?" or "Show me the top 5 products by growth" for deeper analysis.

Model Switcher New Advanced

Choose between OpenAI and Anthropic reasoning models inside Agent Mode.

▾

Agent Mode now includes a model selector dropdown that lets you switch between reasoning models — OpenAI (GPT) and Anthropic (Claude) — depending on the task at hand.

Try different models for different tasks: one may handle formula generation better while another excels at data analysis reasoning. Experiment to find the best fit.

AI Conditional Formatting Intermediate

Describe formatting rules in plain English and Copilot creates formula-based rules.

▾

Natural Language to Conditional Format

// You say:
"Highlight cells in column C where spending exceeds revenue in column B"

// Copilot creates conditional formatting rule:
=C2>B2  applied with red fill

Copilot creates formula-based conditional formatting — more powerful than the basic preset rules in the menu.

Sentiment Classification Intermediate

Classify customer feedback, NPS responses, or support tickets at scale with =COPILOT().

▾

Sentiment Analysis

// In cell B2, classify customer review in A2:
=COPILOT("Classify as Positive, Negative, or Neutral", A2)

// Drag down to apply to hundreds of rows instantly

Add your screenshot or GIF here
Sentiment classification results column

COPILOT function classifying 500 customer reviews in seconds

AI Content Generation Intermediate

Generate product descriptions, email drafts, or social captions from spreadsheet data.

▾

Content Generation

// Generate product description from name + features:
=COPILOT("Write a 2-sentence product description", A2:C2)

// Generate email subject line from campaign data:
=COPILOT("Write a catchy email subject line", D2)

Combine with TEXTJOIN to pass multiple columns as context for richer, more accurate content generation.

Python Initialization Editor New Advanced

Customize Python's startup environment — pre-load libraries, define helpers per workbook.

▾

The new Python Initialization Editor lets you customize how Python starts up inside your workbook — pre-load libraries, define helper functions, and set configurations that run automatically.

Step-by-step

Go to the Formulas tab → Python section.
Click Initialization to open the editor.
Add your import statements, helper functions, or configuration.
Click Save — these will run before any PY() cell executes.

Use this to pre-load custom data connectors or define reusable analysis functions that work across your entire workbook.

Formulas & Functions

17 tips

▾

UNIQUE Function Beginner

Extract distinct values from any range. Returns a dynamic array that auto-spills.

▾

UNIQUE

=UNIQUE(A1:A50)

// Values appearing exactly once
=UNIQUE(A1:A50, FALSE, TRUE)

Add your screenshot or GIF here
UNIQUE extracting distinct names

6 unique names from a list of 10 with duplicates

Returns a dynamic array — don't put data below it, it expands as source data grows.

XLOOKUP Intermediate

Modern replacement for VLOOKUP — searches any direction, built-in error handling.

▾

XLOOKUP

=XLOOKUP("Widget", A2:A100, C2:C100)

// With default if not found
=XLOOKUP(E2, A:A, B:B, "Not Found")

// Return multiple columns
=XLOOKUP(E2, A:A, B:D)

Unlike VLOOKUP: can search left, has built-in error handling (no IFERROR needed), supports exact/approximate/wildcard matching.

XLOOKUP Multiple Criteria Advanced

Lookup with two or more conditions without helper columns.

▾

Multi-Criteria XLOOKUP

=XLOOKUP(1, (A2:A100="John")*(B2:B100="Sales"), C2:C100)

The trick is creating a boolean array: TRUE*TRUE=1, anything else=0. XLOOKUP finds the 1.

FILTER Function Intermediate

Dynamically return rows matching criteria — with AND/OR logic and fallback messages.

▾

FILTER

=FILTER(A2:D100, C2:C100="Active")

// Multiple criteria (AND)
=FILTER(A2:D100, (C:C="Active")*(D:D>1000))

// With fallback message
=FILTER(A2:D100, B:B>50, "No matches found")

Combine with SORT for sorted results: =SORT(FILTER(data, criteria), 2, -1) sorts by 2nd column descending.

SORT & SORTBY Intermediate

Sort data dynamically without changing the source. SORTBY sorts by a separate array.

▾

SORT and SORTBY

=SORT(A2:D100, 3, -1)  // by 3rd column, descending

=SORTBY(A2:A100, C2:C100, -1)  // sort names by scores

LAMBDA Advanced

Create custom reusable functions using formulas. Save in Name Manager, use anywhere.

▾

LAMBDA

// Define in Name Manager:
// Name: TAX_TOTAL
// Refers to:
=LAMBDA(price, rate, price + price * rate)

// Use anywhere in workbook:
=TAX_TOTAL(B2, 0.15)  adds 15% tax

Step-by-step

Go to Formulas → Name Manager → New.
Enter a name (e.g., TAX_TOTAL).
In "Refers to", enter your LAMBDA formula.
Click OK. Now use =TAX_TOTAL(args) in any cell.

LAMBDA functions persist in the workbook — anyone who opens the file can use them. Like custom functions without VBA.

LET Function Intermediate

Assign names to intermediate calculations — cleaner formulas, better performance.

▾

LET

=LET(
  tax_a, B2 * 0.15,
  tax_b, C2 * 0.1,
  IF(tax_a > tax_b, tax_a, tax_b)
)

LET also improves performance — named values are calculated once, even if referenced multiple times in the formula.

TOCOL & TOROW Beginner

Reshape multi-column data into a single column (TOCOL) or row (TOROW).

▾

TOCOL and TOROW

=TOCOL(A1:B6)
=TOCOL(A1:D10, 3)  // skip blanks and errors
=TOROW(A1:A20)

WRAPCOLS & WRAPROWS Intermediate

Transform a single column/row into a grid with specified columns or rows.

▾

WRAPCOLS and WRAPROWS

=WRAPCOLS(A1:A20, 4)  // into 4 columns
=WRAPROWS(A1:A20, 5)  // into rows of 5

SUM / SUMIF / SUMIFS Beginner

Basic summation to multi-criteria conditional sums — the workhorse functions.

▾

SUM Family

=SUM(B2:B100)
=SUMIF(A:A, "Sales", B:B)
=SUMIFS(C:C, A:A, "Sales", B:B, ">1000")

IF / IFS / SWITCH Beginner

Conditional logic from simple true/false to multi-condition and value matching.

▾

Conditional Functions

=IF(A2>90, "A", "B")
=IFS(A2>=90,"A", A2>=80,"B", A2>=70,"C", TRUE,"F")
=SWITCH(B2, "NY","New York", "CA","California", "Unknown")

INDEX + MATCH Intermediate

Powerful flexible lookup combination — works in any direction, all Excel versions.

▾

INDEX MATCH

=INDEX(C2:C100, MATCH("Widget", A2:A100, 0))

// 2D lookup (row + column match)
=INDEX(B2:F10,
  MATCH("Q3", A2:A10, 0),
  MATCH("Sales", B1:F1, 0))

INDEX+MATCH works in all Excel versions and handles 2D lookups natively — many pros still prefer it over XLOOKUP.

COUNTIF / COUNTIFS Beginner

Count cells matching one or multiple criteria across ranges.

▾

COUNTIF

=COUNTIF(A:A, "Sales")
=COUNTIFS(A:A, "Sales", B:B, ">50000")

TEXTJOIN Beginner

Concatenate with custom delimiters, optionally skipping blank cells.

▾

TEXTJOIN

=TEXTJOIN(", ", TRUE, A1:A10)
// Result: "Apple, Banana, Cherry" (skips blanks)

IMAGE Function New Beginner

Insert web images directly into cells via URL with sizing options.

▾

IMAGE

=IMAGE("https://example.com/logo.png")

// With sizing options
=IMAGE(url, "alt text", 2, 100, 100)

Add your screenshot or GIF here
Product images in cells via URL

Product catalog with images loaded via the IMAGE function

SUBTOTAL Intermediate

Aggregate only visible cells after filtering — supports SUM, AVERAGE, COUNT, and more.

▾

SUBTOTAL

=SUBTOTAL(9, B2:B100)   // SUM visible cells only
=SUBTOTAL(1, B2:B100)   // AVERAGE visible cells only
=SUBTOTAL(2, B2:B100)   // COUNT visible cells only

Use function numbers 101-111 (e.g., 109 for SUM) to also ignore manually hidden rows — not just filtered rows.

SEQUENCE & RANDARRAY Intermediate

Generate number sequences and random arrays dynamically — no helper columns.

▾

SEQUENCE and RANDARRAY

=SEQUENCE(10)              // 1 to 10
=SEQUENCE(5, 4, 100, 5)    // 5x4 grid from 100, step 5
=RANDARRAY(3, 2)           // random decimals
=RANDARRAY(10, 1, 1, 100, TRUE)  // random integers 1-100

◈

Dynamic Arrays

10 tips

▾

Spill Fundamentals Beginner

One formula returns multiple values that auto-fill adjacent cells — the foundation of modern Excel.

▾

Dynamic arrays spill results into adjacent cells automatically. This is the foundation for UNIQUE, FILTER, SORT, SEQUENCE, and all modern array functions.

Add your screenshot or GIF here
Dynamic array spilling results

FILTER formula in one cell, results auto-filling 5 rows below

Keep cells below/beside a spill formula empty — Excel shows a #SPILL! error if something blocks the output range.

Spill Range Operator (#) Intermediate

Reference an entire spill range dynamically with the # symbol.

▾

Spill Operator

=SUM(A1#)       // sums the whole spill
=COUNTA(A1#)    // counts spill items
=SORT(A1#)      // re-sorts the spill

The # reference adapts dynamically as the source array grows or shrinks — no need to update references.

VSTACK & HSTACK Intermediate

Stack arrays vertically or horizontally — combine data from multiple ranges or sheets.

▾

VSTACK and HSTACK

=VSTACK(A1:A5, C1:C3)         // stack vertically
=HSTACK(A1:A5, B1:B5)         // stack side by side
=VSTACK(Sheet1!A:D, Sheet2!A:D)  // combine sheets

REDUCE Advanced

Apply a LAMBDA function cumulatively across an array — running totals, custom aggregations.

▾

REDUCE

=REDUCE(0, A1:A10, LAMBDA(acc, val, acc + val))  // running total
=REDUCE(1, A1:A5, LAMBDA(acc, val, acc * val))   // running product

MAP Function Advanced

Apply a transformation LAMBDA to every element in an array — replaces helper columns.

▾

MAP

=MAP(A1:A10, LAMBDA(x, x * 2))       // double every value
=MAP(A1:A10, LAMBDA(x, UPPER(x)))    // uppercase all text

MAP replaces helper columns entirely — transform data inline without extra columns cluttering your sheet.

SCAN Function Advanced

Like REDUCE but returns all intermediate results — running balances, cumulative products.

▾

SCAN

=SCAN(1000, B2:B20, LAMBDA(bal, txn, bal + txn))  // running balance

MAKEARRAY Advanced

Generate arrays programmatically by applying a LAMBDA to each row/column index.

▾

MAKEARRAY

=MAKEARRAY(10, 10, LAMBDA(r, c, r * c))  // multiplication table

BYCOL & BYROW Advanced

Apply aggregation functions independently across each column or row of an array.

▾

BYCOL and BYROW

=BYCOL(A1:D10, LAMBDA(col, SUM(col)))   // sum per column
=BYROW(A1:D10, LAMBDA(row, MAX(row)))   // max per row

CHOOSECOLS & CHOOSEROWS Intermediate

Pick specific columns or rows from a range by index number — reorder and subset.

▾

CHOOSECOLS and CHOOSEROWS

=CHOOSECOLS(A1:F100, 1, 3, 6)  // pick columns 1, 3, 6
=CHOOSEROWS(A1:D50, 1, 5, 10)  // pick rows 1, 5, 10

DROP & TAKE Intermediate

DROP removes N rows/cols from edges. TAKE keeps only the first/last N rows/cols.

▾

DROP and TAKE

=DROP(A1:D100, 1)     // drop first row (headers)
=TAKE(A1:D100, 5)     // keep first 5 rows
=DROP(A1:D100, , 1)   // drop first column
=TAKE(A1:D100, -3)    // keep last 3 rows

Keyboard Shortcuts

14 tips

▾

Ctrl + N Beginner

Create a new workbook instantly.

▾

Ctrl + N (Windows) or Cmd + N (Mac) creates a blank workbook.

For a new worksheet within the current workbook, use Shift + F11 instead.

Shift + Spacebar Beginner

Select the entire row of the active cell.

▾

Shift + Space selects the full row. Useful for quickly deleting, formatting, or copying entire rows.

Ctrl + Spacebar Beginner

Select the entire column of the active cell.

▾

Ctrl + Space selects the full column of the active cell.

Ctrl + Shift + Arrow Beginner

Select from the current cell to the last data cell in that direction.

▾

Ctrl + Shift + Down Arrow selects from the current cell to the last non-empty cell downward. Works in all four arrow directions.

Ctrl + K Beginner

Insert or edit a hyperlink in the selected cell.

▾

Ctrl + K opens the hyperlink dialog. Link to URLs, files, email addresses, or other locations in the workbook.

Ctrl + 1 Beginner

Open the Format Cells dialog — fonts, borders, number formats, alignment.

▾

Ctrl + 1 opens Format Cells — the fastest way to access all formatting options including fonts, borders, numbers, and alignment.

Alt + = (AutoSum) Beginner

Instantly sum the adjacent range of cells.

▾

Select a cell below or beside your numbers, press Alt + = (or Opt + = on Mac), and Excel inserts a SUM formula for the adjacent range.

Works on multiple columns at once — select several cells at the bottom of different columns and press Alt+= to sum them all simultaneously.

Ctrl + E (Flash Fill) Beginner

Auto-detect patterns from your examples and fill the rest of the column.

▾

Type one or two examples of the pattern you want, then press Ctrl + E and Excel fills the rest of the column automatically.

Add your screenshot or GIF here
Flash Fill extracting first names from full names

Typing one example then pressing Ctrl+E to auto-fill the column

Provide 1-2 examples for best results. Flash Fill works for: extracting text, reformatting dates, combining fields, changing case, and more.

Ctrl + ; Beginner

Insert today's date as a static value into the active cell.

▾

Ctrl + ; stamps today's date. For the current time, use Ctrl + Shift + :.

This inserts a static value that won't update tomorrow. For a live date that always shows today, use =TODAY() instead.

Ctrl + Shift + L Beginner

Toggle auto-filter dropdown arrows on column headers.

▾

Ctrl + Shift + L adds or removes filter dropdown arrows on your column headers. Much faster than navigating to Data then Filter.

Shift + F11 Beginner

Insert a new worksheet tab in the current workbook.

▾

Shift + F11 instantly adds a new worksheet tab to your current workbook.

F4 (Repeat / Toggle Reference) Beginner

Repeat your last action or toggle cell reference types in formulas.

▾

F4 repeats whatever you just did — applied bold? Press F4 on another cell to bold it too. Inserted a row? F4 inserts another.

In the formula bar, F4 toggles cell references between relative (A1), absolute ($A$1), mixed ($A1), mixed (A$1) — essential when copying formulas.

Ctrl + ` (Show Formulas) Intermediate

Toggle between showing cell values and showing all formulas in the sheet.

▾

Press Ctrl + ` to reveal all formulas in the sheet. Press again to switch back to values. Great for auditing and debugging spreadsheets.

Add your screenshot or GIF here
Formula view showing all formulas in cells

Sheet toggled to formula view showing SUM VLOOKUP etc in every cell

Alt + H + O + I Intermediate

Auto-fit the selected column width to match the widest content.

▾

Press Alt, then H, then O, then I (in sequence, not simultaneously) to auto-fit the selected column width to its content.

Formatting & Selection

11 tips

▾

Conditional Formatting Intermediate

Automatically color-code cells based on their values or custom formula rules.

▾

Step-by-step

Select your data range.
Go to Home then Conditional Formatting.
Choose a preset rule or click New Rule for formula-based rules.
Set the format (fill color, font, borders) and click OK.

Add your screenshot or GIF here
Conditional formatting highlighting cells

Cells above 10000 highlighted green below highlighted red

Use formula-based rules for maximum power. Example: =AND(B2>C2, B2>10000) highlights rows where column B exceeds both column C and 10000.

Format Painter Beginner

Let's say you have a cell where you have a set of formatting, or you have multiple cells where you have a set of formatting and have applied multiple things like cell color, font color, and font size.

▾

Step-by-step

Select a cell with the formatting you want.
Click Format Painter (paintbrush icon) on the Home tab.
Click or drag across the cells to apply the formatting.

Double-click the Format Painter button to keep it active — paint multiple ranges, then press Esc when done.

Custom Number Formats Intermediate

Create custom display formats for numbers, dates, currencies, and percentages.

▾

Number Format Codes

#,##0.00      results in 1,234.56
$#,##0        results in $1,235
0.0%          results in 85.5%
[Green]0;[Red]-0  green positive red negative
dd-mmm-yyyy   results in 24-Mar-2026

Text Wrapping Beginner

Display long text within cell boundaries without overflowing into adjacent cells.

▾

Select cells then Home then Wrap Text. Long text wraps to multiple lines within the cell instead of overflowing. The row height auto-adjusts to show all content.

New Checkboxes New Beginner

Modern checkboxes from the Insert tab — much easier than legacy Developer-tab controls.

▾

Step-by-step

Select the cell where you want a checkbox.
Go to Insert then Checkbox in the Controls group.
The checkbox returns TRUE/FALSE — use in formulas like =COUNTIF(B:B, TRUE).

Add your screenshot or GIF here
New Excel checkboxes in a task list

Modern checkboxes tracking task completion with a progress formula

Transpose Rows to Columns Beginner

Flip data orientation — convert rows to columns or vice versa.

▾

Step-by-step

Select and Copy (Ctrl+C) the range you want to flip.
Click the destination cell.
Right-click then Paste Special then Transpose.

For a dynamic link that updates when source data changes, use =TRANSPOSE(A1:D10) as a formula instead.

Text to Columns Beginner

Split text in a single cell into multiple columns using delimiters.

▾

Step-by-step

Select the column with text to split.
Go to Data then Text to Columns.
Choose Delimited and click Next.
Select your delimiter (comma, space, tab, etc.).
Click Finish.

Add your screenshot or GIF here
Text to Columns splitting full names

Splitting John Smith into John and Smith using space delimiter

Sort by Color Intermediate

Organize data by cell background color or font color.

▾

Step-by-step

Go to Data then Sort.
Under Sort On select Cell Color.
Choose the color you want to sort first.
Click Add Level for multiple colors.

Right-Click Filter Beginner

Instantly filter data by the selected cell's value — one click, no setup.

▾

Right-click any cell then Filter then Filter by Selected Cell Value. Instantly shows only rows matching that value. Much faster than setting up filters manually.

Fit Image in Cell New Beginner

Lock images inside cells so they move and resize with the cell.

▾

Step-by-step

Insert an image into your worksheet.
Select the image and go to Picture Format tab.
Click Place in Cell.
The image snaps into the cell and resizes with it automatically.

Remove Duplicates Beginner

Clean duplicate rows from your data in one click.

▾

Step-by-step

Select your data range or table.
Go to Data then Remove Duplicates.
Choose which columns to check for duplicates.
Click OK — Excel removes them and tells you how many were deleted.

This permanently deletes duplicate rows. Make a backup first, or use =UNIQUE() for a non-destructive alternative.

Productivity

10 tips

▾

Pivot Tables Intermediate

The most powerful analysis tool in Excel — reorganize and aggregate data instantly.

▾

Step-by-step

Select your data range (must have headers).
Go to Insert then PivotTable.
Choose where to place it (new or existing sheet).
Drag fields to Rows, Columns, Values, and Filters.

Add your screenshot or GIF here
PivotTable summarizing sales by region

Dragging Region to Rows and Revenue to Values for instant analysis

Right-click any value in a PivotTable and select Show Details to drill down into the underlying source rows.

Flash Fill Beginner

Auto-fill column patterns with Ctrl+E — extraction, reformatting, combining, case changes.

▾

Type one or two examples of the transformation you want, then press Ctrl + E and Excel fills the rest of the column by detecting your pattern.

Add your screenshot or GIF here
Flash Fill reformatting phone numbers

Typing formatted number then Flash Fill formatting the rest

Freeze Panes Beginner

Lock top rows and/or left columns to keep headers visible while scrolling.

▾

Step-by-step

Click the cell below and to the right of what you want to freeze.
Go to View then Freeze Panes.
Choose Freeze Panes, Freeze Top Row, or Freeze First Column.

To freeze both the top row AND first column simultaneously: click cell B2, then Freeze Panes.

Named Ranges Intermediate

Assign meaningful names to cell ranges for self-documenting, readable formulas.

▾

Step-by-step

Select the range you want to name.
Go to Formulas then Define Name.
Enter a descriptive name (e.g., SalesData).
Now use it in formulas: =SUM(SalesData)

Named ranges make formulas self-documenting: =SUMIF(Regions, "West", Revenue) is much clearer than =SUMIF(A2:A100, "West", D2:D100).

Group and Outline Data Intermediate

Create expandable/collapsible sections for organized navigation of large datasets.

▾

Step-by-step

Select the rows or columns you want to group.
Go to Data then Group.
Click the +/- buttons in the margin to expand or collapse sections.

Sheet View Intermediate

Create separate views of the same worksheet without changing the original layout.

▾

Step-by-step

Go to View then Sheet View then New.
Make any changes (filters, sorting, hiding columns).
Click Keep to save the view.
Switch between views from the dropdown.

Searchable Drop-Down Lists New Beginner

Data validation drop-downs now support search-as-you-type filtering.

▾

When you create a data validation drop-down list, it now automatically supports searching. Start typing in the cell and matching values are filtered in the dropdown — no extra setup needed.

Add your screenshot or GIF here
Searchable drop-down showing filtered results

Typing low in a cell and seeing all values containing low

Data Entry Form Intermediate

Built-in no-code form for structured data entry into Excel tables.

▾

Step-by-step

Format your data as a Table (Ctrl+T).
Press Alt + D + O (press keys in sequence).
A form appears with all your column headers as fields.
Enter data and click New to add rows.

Add your screenshot or GIF here
Excel data entry form

Built-in form showing Name Email Department fields

Search Bar / Tell Me Beginner

Find any Excel command instantly — search for actions, features, and recent files.

▾

Press Alt + Q to focus the search box at the top of Excel. Type what you want to do and Excel shows matching commands, recent files, and help articles.

Data Validation Intermediate

Restrict cell inputs to specific values, number ranges, dates, or custom formula rules.

▾

Step-by-step

Select the cells you want to validate.
Go to Data then Data Validation.
Choose the type: List, Whole Number, Decimal, Date, Custom Formula, etc.
Set criteria and optional input/error messages.

◐

Charts & Visualization

8 tips

▾

Sparkline Mini-Charts Beginner

Tiny inline charts (line, column, win/loss) rendered inside individual cells.

▾

Step-by-step

Select the cell where you want the sparkline.
Go to Insert then Sparklines.
Choose Line, Column, or Win/Loss.
Select the data range and click OK.

Add your screenshot or GIF here
Sparkline mini-charts in cells

Small line charts in column E showing monthly trends for each product

Save Chart Templates Intermediate

Save custom chart styles as templates for consistent, reusable visualizations.

▾

Step-by-step

Create and format a chart exactly how you want it.
Right-click the chart then Save as Template.
Name it and save.
For new charts: Insert then Templates then select yours.

Waterfall Charts Intermediate

Show step-by-step how values increase and decrease — ideal for financial variance.

▾

Waterfall charts visualize how an initial value increases and decreases through a series of changes — perfect for revenue bridges, budget variance analysis, and financial reporting.

Add your screenshot or GIF here
Waterfall chart showing quarterly changes

Green bars for increases red for decreases gray for totals

Combo Charts Intermediate

Combine two chart types (e.g., column + line) with dual Y-axes on one visual.

▾

Step-by-step

Create a chart from your data.
Right-click a data series then Change Chart Type.
Select Combo at the bottom.
Choose chart type per series and check Secondary Axis as needed.

Map Charts Intermediate

Visualize geographic data on filled maps — Excel recognizes countries, states, regions.

▾

Select data with location names (countries, states, cities) and a value column then Insert then Maps then Filled Map. Excel auto-recognizes the geography and creates a filled map.

Add your screenshot or GIF here
Map chart showing sales by US state

Filled map with darker shades for higher revenue states

Treemap & Sunburst Intermediate

Visualize hierarchical data as nested rectangles (treemap) or concentric rings (sunburst).

▾

Treemap shows hierarchy as nested rectangles — great for budget breakdowns. Sunburst shows it as concentric rings — ideal for org charts and multi-level categories.

Format as Table Beginner

Instant styling, auto-filters, total rows, and structured formula references.

▾

Select your data and press Ctrl + T or go to Home then Format as Table. Tables add auto-filters, total rows, banded rows, and enable structured references like =SUM(Table1[Revenue]).

Funnel Charts Intermediate

Visualize progressive reduction across stages — sales pipeline, hiring funnel.

▾

Insert then Funnel chart. Shows how values decrease across stages — ideal for sales pipelines, hiring funnels, recruitment processes, and conversion tracking.

⟳

Power Query & Import

6 tips

▾

Combine Files from Folder Intermediate

Merge all workbooks in a directory into one — refresh anytime for updates.

▾

Step-by-step

Go to Data then Get Data then From Folder.
Navigate to the folder containing your files.
Click Combine and Transform.
Power Query merges all files into one table.
Click Refresh anytime to pull in new or updated files.

One-time setup — after that, just Refresh to pull in any new or updated files from the folder.

Import XML Directly Intermediate

Pull structured XML data directly into Excel tables via the Developer tab.

▾

Step-by-step

Go to the Developer tab and click Import in the XML group.
Select your XML file.
Choose the destination cell.
Click OK — data loads as a structured table.

Power Query Editor Intermediate

Clean, reshape, merge, unpivot, and transform data through a visual interface.

▾

Power Query provides a visual step-by-step interface for data transformation. Clean, reshape, merge, and unpivot data — no formulas needed. Every step is recorded and repeatable.

Add your screenshot or GIF here
Power Query Editor

Visual transformation steps in Power Query

Every transformation is recorded as a repeatable step. Change the source data and just click Refresh — all transformations reapply automatically.

Import from Web Intermediate

Scrape tables from any website directly into your Excel workbook.

▾

Step-by-step

Go to Data then From Web.
Paste the website URL.
Excel detects tables on the page.
Select the table you want then Load.

Append & Merge Queries Advanced

Stack data vertically (Append/UNION) or join tables on keys (Merge/JOIN).

▾

Append = stack data vertically (like UNION in SQL). Merge = join tables on a key column (like JOIN in SQL). Both are available in the Power Query editor.

Use Merge for lookups across different data sources — it is more flexible than VLOOKUP when working with external data.

Unpivot Columns Intermediate

Transform wide-format data into analysis-ready tall format with one click.

▾

Select columns in Power Query then Transform then Unpivot Columns. Converts wide data (months as column headers) into tall format (Month and Value columns) — essential for PivotTables and charts.

Add your screenshot or GIF here
Unpivot transformation

Monthly columns becoming rows with Month and Value fields

Protection & Sharing

6 tips

▾

Protect a Worksheet Beginner

Password-protect a sheet and control exactly which actions users can perform.

▾

Step-by-step

Go to Review then Protect Sheet.
Set a password (optional but recommended).
Check which actions users CAN perform (sort, filter, select cells, etc.).
Click OK.

Excel sheet passwords are not encryption — they prevent casual edits but can be bypassed by determined users. For real security, use file encryption.

Protect Workbook Structure Beginner

Prevent users from adding, deleting, hiding, or renaming worksheets.

▾

Step-by-step

Go to Review then Protect Workbook.
Set a password.
Users cannot add, delete, hide, rename, or move sheets.

Cell-Level Locking Intermediate

Lock critical formula cells while leaving data-entry cells editable.

▾

Step-by-step

Select cells to unlock: Right-click then Format Cells then Protection then uncheck Locked.
Then Review then Protect Sheet.
Only locked cells (the default) will be protected; unlocked cells remain editable.

By default, all cells are locked. The workflow is: unlock the cells you WANT people to edit, then protect the sheet. The unlocked cells stay editable.

Encrypt with Password Beginner

Full file-level encryption — the strongest protection Excel offers.

▾

Step-by-step

Go to File then Info then Protect Workbook then Encrypt with Password.
Enter a strong password.
Confirm and save the file.

If you forget the encryption password, there is no recovery method. Store the password securely in a password manager.

Excel to Word Integration Intermediate

Embed live-updating Excel data, charts, and tables into Word documents.

▾

Step-by-step

Copy your Excel range (Ctrl+C).
In Word: Paste Special then Paste Link.
Choose Microsoft Excel Worksheet Object.
Data in Word updates automatically when the Excel file changes.

Use Paste Special then Paste as Picture for a static snapshot that will not change — useful for finalized reports.

Co-Authoring Beginner

Real-time simultaneous editing with visible cursors and live changes.

▾

Save your workbook to OneDrive or SharePoint, then share with others. Everyone edits simultaneously — you can see other users cursor positions, cell selections, and changes in real-time.

Use Sheet View so each collaborator can sort and filter independently without affecting what others see.

◆

Power User & VBA

7 tips

▾

Script Lab Advanced

Write Office JavaScript API code and create custom Excel functions with the Script Lab add-in.

▾

Step-by-step

Go to Insert then Get Add-ins then Script Lab to install.
Add Script Lab tab to ribbon: File then Options then Customize Ribbon.
Click Code to open the editor pane.
Write JavaScript, register custom functions, and run.

Custom Function Example

/** @customfunction */
function getsquare(num) {
  return Math.pow(num, 2);
}

VBA Macros Advanced

Record or code macros to automate repetitive tasks — data entry to report generation.

▾

Step-by-step

Go to Developer then Record Macro.
Perform the actions you want to automate.
Click Stop Recording.
Run anytime with Alt + F8 or assign to a button.

For complex automation, press Alt + F11 to open the VBA editor and write code directly.

Power Pivot & DAX Advanced

Build relational data models across multiple tables with DAX calculated measures.

▾

Step-by-step

Enable Power Pivot: File then Options then Add-ins then COM Add-ins then Power Pivot.
Add tables to the data model.
Create relationships between tables (like a database).
Write DAX measures for advanced calculations that PivotTables alone cannot do.

Solver Add-In Advanced

Optimization tool — find the best value (max/min/target) by changing variables with constraints.

▾

Step-by-step

Enable: File then Options then Add-ins then Solver.
Go to Data then Solver.
Set objective cell (the result to optimize), variable cells (inputs to change), and constraints.
Click Solve.

Solver handles linear programming, what-if optimization, and constraint satisfaction — used widely in finance, operations, and supply chain.

Goal Seek Intermediate

Find the input value needed to achieve a specific desired output.

▾

Step-by-step

Go to Data then What-If Analysis then Goal Seek.
Set Cell: the formula cell with the result you want to change.
To Value: the target result you want.
By Changing Cell: the input to adjust.

Example: What price do I need to charge to reach $100K revenue? — Goal Seek adjusts the price cell until the revenue formula hits $100K.

Scenario Manager Intermediate

Create, save, and compare multiple what-if assumption sets side by side.

▾

Step-by-step

Go to Data then What-If Analysis then Scenario Manager.
Click Add to create scenarios (e.g., Best Case, Worst Case, Most Likely).
Set changing cells and values for each scenario.
Click Summary to compare all scenarios in a side-by-side table.

Custom Data Types Intermediate

Excel recognizes Stocks, Geography, and other rich data types — pulling live structured data.

▾

Type a company name or location then select the cells then Data then Data Type then Stocks or Geography. Excel converts them to rich data types and lets you extract live fields like stock price, market cap, population, area, and more.

Add your screenshot or GIF here
Custom data types showing stock prices

Company names converted to Stock data type with live prices and market caps