Excel Tips & Tricks

Practical advice to help you work faster and smarter in Excel. From keyboard shortcuts to advanced formula techniques.

Formulas

Navigation

Productivity

Data Management

Security

Visualization

Performance

Data Analysis

Use XLOOKUP Instead of VLOOKUP

Formulas

XLOOKUP is the modern replacement for VLOOKUP. It can search in any direction, return multiple columns, and handles errors natively without needing IFERROR.

=XLOOKUP(A1, Products, Prices, "Not found")

XLOOKUP searches left or right, unlike VLOOKUP
The 4th argument handles #N/A errors automatically
Can return entire rows or multiple columns at once

Freeze Panes to Keep Headers Visible

Navigation

When scrolling through large datasets, freeze the top row or first column so headers always stay visible. This prevents losing track of what each column represents.

Click on the cell below and to the right of where you want to freeze
Go to View > Freeze Panes
Choose 'Freeze Top Row' for simple header freezing
Use 'Freeze Panes' for custom freeze positions

Master Keyboard Shortcuts

Productivity

Learning even 5-10 keyboard shortcuts can save hours every week. Start with the most impactful ones and add more over time.

Ctrl+C / Ctrl+V: Copy and paste
Ctrl+Z: Undo last action
Ctrl+Shift+L: Toggle filters on/off
Ctrl+;: Insert today's date
Ctrl+D: Fill down from cell above
F2: Edit active cell
Alt+=: Auto-SUM selected range
Ctrl+`: Show all formulas in sheet

Use Tables for Dynamic Data

Data Management

Convert your data ranges to Excel Tables (Ctrl+T). Tables automatically expand formulas, adjust chart references, and create structured references that are easier to read.

Select your data range and press Ctrl+T
Formulas automatically extend to new rows
Use structured references like Table1[Sales] instead of B:B
Sorting and filtering are built in
Tables work perfectly with PivotTables

Protect Your Formulas from Accidental Edits

Security

Lock cells containing formulas while keeping input cells editable. This prevents accidental formula overwrites in shared workbooks.

Select the cells users should edit
Right-click > Format Cells > Protection > Uncheck 'Locked'
Go to Review > Protect Sheet
Set a password (optional) and choose allowed actions
Formula cells remain locked, input cells stay editable

Use Conditional Formatting for Visual Analysis

Visualization

Conditional formatting turns raw numbers into visual insights. Use data bars, color scales, or icon sets to spot trends, outliers, and patterns instantly.

Select the data range
Home > Conditional Formatting
Use 'Data Bars' for quick bar charts inside cells
Use 'Color Scales' for heat maps (green-yellow-red)
Use 'Icon Sets' for status indicators
Create custom rules for specific business logic

Use Named Ranges for Clearer Formulas

Formulas

Instead of cryptic cell references like $B$2:$B$500, name your ranges. A formula like =SUM(MonthlySales) is instantly understandable.

=SUMIF(Region, "Europe", MonthlySales)

Select a range and type a name in the Name Box (left of formula bar)
Or go to Formulas > Define Name
Use descriptive names like 'MonthlySales' or 'TaxRate'
Named ranges automatically update if you insert rows

Use Flash Fill for Quick Data Cleanup

Productivity

Flash Fill recognizes patterns and fills in data automatically. It can split names, reformat phone numbers, extract parts of text, and more, without formulas.

Type the desired result in the cell next to your data
Start typing the next result in the row below
Excel will suggest the pattern in grey
Press Enter to accept, or Ctrl+E to trigger Flash Fill
Works for splitting names, reformatting dates, extracting text

Validate Input Data with Drop-Down Lists

Data Management

Prevent data entry errors by limiting inputs to predefined choices. Drop-down lists ensure consistency and reduce typos.

Select the cells where you want the dropdown
Go to Data > Data Validation
Under 'Allow', choose 'List'
Enter values separated by commas, or select a range
Check 'In-cell dropdown' for the arrow button
Add an input message and error alert for guidance

Use IFERROR to Handle Formula Errors Gracefully

Formulas

Wrap formulas in IFERROR to display a friendly message instead of ugly error codes like #N/A or #DIV/0!. This makes reports look professional.

=IFERROR(VLOOKUP(A1, Data, 2, 0), "Not found")

Wrap any formula: =IFERROR(your_formula, fallback_value)
Use "" for blank, 0 for zero, or a message
Especially useful with VLOOKUP and division
Use IFNA if you only want to catch #N/A errors

Speed Up Slow Workbooks

Performance

Large workbooks can become painfully slow. A few targeted changes can dramatically improve calculation speed.

Replace volatile functions (INDIRECT, OFFSET) with INDEX
Avoid entire column references (A:A) when possible
Use helper columns instead of array formulas
Set calculation to Manual (Formulas > Calculation Options) for large files
Press F9 to recalculate when needed
Remove unused conditional formatting rules

Use PivotTables for Quick Analysis

Data Analysis

PivotTables can summarize thousands of rows into meaningful reports in seconds. They are the single most powerful feature in Excel for data analysis.

Select your data and go to Insert > PivotTable
Drag fields to Rows, Columns, Values, and Filters areas
Right-click values to change calculation (sum, count, average)
Group dates by month, quarter, or year automatically
Add slicers for interactive filtering
Refresh the PivotTable after adding new data

Use Absolute References ($) in Formulas

Formulas

When copying formulas, use $ to lock row or column references. This prevents references from shifting and causing errors.

=A1*$B$1 (B1 stays fixed when copied)

$A$1: Both column and row are locked
A$1: Only the row is locked
$A1: Only the column is locked
Press F4 while editing to cycle through options
Essential for lookup tables and fixed rates

Use TEXT Function for Custom Number Formatting

Formulas

The TEXT function lets you format numbers, dates, and times as text with any pattern you want. Perfect for reports and labels.

=TEXT(TODAY(), "dddd, mmmm d, yyyy")

=TEXT(1234.5, "#,##0.00") gives 1,234.50
=TEXT(0.85, "0%") gives 85%
=TEXT(TODAY(), "dd/mm/yyyy") formats dates
=TEXT(A1, "000") pads with leading zeros
Useful when combining text and numbers with &

Remove Duplicates Safely

Data Management

Excel has a built-in tool to find and remove duplicate rows. Always work on a copy of your data first to prevent accidental data loss.

Copy your data to a backup sheet first
Select your data range or table
Go to Data > Remove Duplicates
Choose which columns to check for duplicates
Review the count of removed vs. remaining rows
Alternatively, use UNIQUE() function in Excel 365 for non-destructive results

Use SUMIFS for Multi-Criteria Totals

Formulas

SUMIFS lets you sum values based on multiple conditions. It is one of the most versatile functions for business reporting.

=SUMIFS(Amount, Region, "Europe", Year, 2024, Status, "Closed")

First argument is always the sum range
Then pairs of criteria_range + criteria
Use wildcards: "*text*" for partial matches
Use comparison operators: ">100", "<>0"
Works with dates: ">="&DATE(2024,1,1)

Explore More Tools