Excel Tips & Tricks - Professional Guide

⌨️ Essential Shortcuts

Navigation

Quick Navigation

Jump to the edge of data regions instantly:

Ctrl + Arrow Keys

Go to beginning/end of worksheet:

Ctrl + Home

Ctrl + End

Selection

Select Entire Column/Row

Select entire column or row quickly:

Ctrl + Space (Select Column)

Shift + Space (Select Row)

Time Savers

Insert Current Date/Time

Quickly insert today's date or current time:

Ctrl + ; (Insert Date)

Ctrl + Shift + ; (Insert Time)

Formatting

Fill Down & Fill Right

Copy format or formula from cell above/left:

Ctrl + D (Fill Down)

Ctrl + R (Fill Right)

🔢 Essential Formulas

Basic Math

SUM

Add up numbers in a range:

=SUM(number1, [number2], ...)

Example: =SUM(A1:A10) - Sum all values from A1 to A10

Practical: =SUM(B2:B50) - Calculate total sales for the month

Counting

COUNTA

Count non-empty cells in a range:

=COUNTA(value1, [value2], ...)

Example: =COUNTA(A1:A100) - Count how many cells have data

Practical: =COUNTA(C2:C100) - Count number of responses in survey

Logic

IF Statement

Make decisions based on conditions:

=IF(condition, value_if_true, value_if_false)

Example: =IF(A1>100, "High", "Low")

Practical: =IF(B2>=60, "Pass", "Fail") - Check if student passed (60+ score)

Nested: =IF(A1>90, "A", IF(A1>80, "B", IF(A1>70, "C", "F"))) - Grade calculator

Conditional Math

SUMIF & COUNTIF

Sum or count based on specific criteria:

=SUMIF(range, criteria, [sum_range])

=COUNTIF(range, criteria)

Example: =SUMIF(A2:A100, ">100", B2:B100) - Sum values in B where A > 100

Practical: =SUMIF(C2:C50, "Completed", D2:D50) - Total revenue from completed orders

Count Example: =COUNTIF(B2:B100, "Yes") - Count how many "Yes" responses

Lookup

VLOOKUP

Find and retrieve data from a table:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example: =VLOOKUP(A2, B2:D100, 3, FALSE) - Find value in column A, return from 3rd column

Practical: =VLOOKUP(E2, Products!A:C, 3, FALSE) - Look up product ID, get price

Pro tip: Always use FALSE (or 0) for exact match

🚀 Advanced Formulas

Math

PRODUCT

Multiply numbers in a range:

=PRODUCT(number1, [number2], ...)

Example: =PRODUCT(A1:A5) - Multiply all values from A1 to A5

Practical: =PRODUCT(B2, C2, D2) - Calculate total price (quantity × unit price × tax rate)

Text

CONCATENATE (CONCAT)

Combine text from multiple cells:

=CONCATENATE(text1, [text2], ...)

=CONCAT(text1, [text2], ...) (Modern version)

Example: =CONCATENATE(A2, " ", B2) - Combine first and last name

Practical: =CONCATENATE(A2, " - ", B2, " (", C2, ")") - Create product label "Name - Code (Category)"

Alternative: Use =A2&" "&B2 for simpler concatenation

Lookup

INDEX

Return value from specific position in array:

=INDEX(array, row_num, [column_num])

Example: =INDEX(A1:C10, 5, 2) - Get value from 5th row, 2nd column

Practical: =INDEX(Products!B:B, 10) - Get 10th product name from column B

Advanced Lookup

INDEX MATCH

More powerful than VLOOKUP - can look left, right, up, down!

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example: =INDEX(C2:C100, MATCH(E2, A2:A100, 0)) - Find E2 in column A, return from column C

Practical: =INDEX(Prices!B:B, MATCH(A2, Prices!A:A, 0)) - Look up product ID, get price (works left!)

Advantage: Can look in any direction, not just right like VLOOKUP

Modern Lookup

XLOOKUP (Excel 365)

Modern replacement for VLOOKUP - more powerful and flexible:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Example: =XLOOKUP(A2, B2:B100, D2:D100, "Not Found") - Look in B, return from D

Practical: =XLOOKUP(E2, Products!A:A, Products!C:C, "Item not in stock") - Get stock status by product ID

Benefits: Can search any direction, built-in error handling, faster than VLOOKUP

Error Handling

IFERROR

Handle errors gracefully in your formulas:

=IFERROR(formula, value_if_error)

Example: =IFERROR(A2/B2, 0) - Divide, return 0 if error (e.g., divide by zero)

Practical: =IFERROR(VLOOKUP(A2, Products!A:C, 3, FALSE), "Not found") - Clean VLOOKUP without #N/A errors

Text Replace

SUBSTITUTE

Replace specific text within a string:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Example: =SUBSTITUTE(A2, "old", "new") - Replace "old" with "new"

Practical: =SUBSTITUTE(B2, "-", "") - Remove hyphens from phone number (555-1234 → 5551234)

Replace 2nd occurrence: =SUBSTITUTE(A2, " ", "-", 2) - Replace only 2nd space with hyphen

⚡ Data Processing

Dynamic Filter

FILTER

Display only rows that meet your criteria (Excel 365):

=FILTER(array, include, [if_empty])

Example: =FILTER(A2:C100, B2:B100>1000) - Show all rows where column B > 1000

Practical: =FILTER(Orders!A:D, Orders!C:C="Pending", "No pending orders") - Show only pending orders

Multiple criteria: =FILTER(A2:D100, (B2:B100="USA") * (C2:C100>500)) - USA AND sales > 500

Dynamic Sort

SORT

Automatically sort data in ascending or descending order (Excel 365):

=SORT(array, [sort_index], [sort_order], [by_col])

Example: =SORT(A2:C100, 2, -1) - Sort by 2nd column, descending (-1)

Practical: =SORT(Sales!A:D, 4, -1) - Show sales data, highest revenue first

Note: Use 1 for ascending, -1 for descending order

Remove Duplicates

UNIQUE

Extract only unique values from a list (Excel 365):

=UNIQUE(array, [by_col], [occurs_once])

Example: =UNIQUE(A2:A100) - Get list of unique values from column A

Practical: =UNIQUE(Customers!B:B) - Create dropdown of unique customer names

Combine with SORT: =SORT(UNIQUE(A2:A100)) - Unique values, alphabetically sorted

Multiple Criteria

SUMIFS, COUNTIFS & AVERAGEIFS

Apply multiple conditions at once:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]...)

Example: =SUMIFS(Sales, Region, "East", Quarter, "Q1") - Sum sales for East region in Q1

Similarly: Use COUNTIFS for counting, AVERAGEIFS for averaging with multiple criteria

Text Processing

Text Manipulation Functions

Extract and manipulate text data:

=LEFT(text, num_chars) First N characters

=RIGHT(text, num_chars) Last N characters

=MID(text, start_num, num_chars) Middle characters

=TRIM(text) Remove extra spaces

=TEXTJOIN(delimiter, ignore_empty, text1, [text2]...) Join text with delimiter

Example: =LEFT(A2, FIND(" ", A2)-1) - Extract first name

Date Functions

Date & Time Calculations

Advanced date calculations for business and analytics:

=DATEDIF(start_date, end_date, "D") Days between dates

=WORKDAY(start_date, days, [holidays]) Add business days

=NETWORKDAYS(start_date, end_date, [holidays]) Count business days

=EOMONTH(start_date, months) End of month

Use "Y", "M", "D", "YM", "MD", "YD" in DATEDIF for years, months, days, etc.

Data Cleaning

Clean & Transform Data

Remove unwanted characters and clean messy data:

=CLEAN(text) Remove non-printable characters

=SUBSTITUTE(text, old_text, new_text) Replace text

=UPPER(text) / =LOWER(text) / =PROPER(text) Change case

Example: =TRIM(CLEAN(A2)) - Remove extra spaces and non-printable chars

🗂️ Data Management

Analysis

Pivot Tables

Create powerful data summaries in seconds:

Select your data range
Insert → PivotTable
Drag fields to Rows, Columns, Values

Alt + N + V (Quick PivotTable)

Organization

Auto-Filter

Add dropdown filters to headers instantly:

Ctrl + Shift + L

Click the dropdown arrows to filter or sort your data.

Data Quality

Data Validation

Create dropdown lists or restrict input:

Select cells
Data → Data Validation
Choose criteria (List, Number, Date, etc.)

Example: Create a dropdown with "Yes, No, Maybe"

View

Freeze Panes

Keep headers visible while scrolling:

Click the cell below and right of what you want frozen
View → Freeze Panes → Freeze Panes

Quick freeze top row: View → Freeze Top Row

⚡ Quick Tips & Tricks

Reference

ROW & COLUMN

Get row or column number of a cell:

=ROW([reference]) Returns row number

=COLUMN([reference]) Returns column number

Example: =ROW() - Returns current row number (e.g., 5 if in row 5)

Practical: =ROW()-1 - Auto-numbering (1, 2, 3...) starting from row 2

Date

TODAY Function

Insert current date that auto-updates:

=TODAY()

Example: =TODAY() - Shows today's date (updates daily)

Practical: =TODAY()+30 - Date 30 days from today

Calculate age: =DATEDIF(B2, TODAY(), "Y") - Age in years from birth date in B2

Static date: Use Ctrl + ; to insert today's date (doesn't update)

Characters

CHAR & ASC

Convert between characters and their codes:

=CHAR(number) Number to character

=CODE(text) Character to number (also called ASC)

Example: =CHAR(65) - Returns "A" (65 is ASCII code for A)

Practical: =CHAR(10) - Line break in cell (use with ALT+ENTER or in formula)

Create alphabet: =CHAR(ROW()+64) - A, B, C... (drag down from row 1)

Pattern Matching

Wildcards (* and ?)

Use wildcards in formulas for flexible matching:

* (asterisk) - Matches any number of characters
? (question mark) - Matches exactly one character

Example: =COUNTIF(A:A, "John*") - Count cells starting with "John" (John, Johnny, Johnson)

Practical: =SUMIF(B:B, "*pending*", C:C) - Sum where B contains "pending" anywhere

Question mark: =COUNTIF(A:A, "????") - Count cells with exactly 4 characters

Combined: =COUNTIF(A:A, "A*n") - Starts with "A", ends with "n" (Alan, Aaron, Amazon, etc.)

💡 Pro Tips

AI Helper

Flash Fill

Excel detects patterns and fills data automatically:

Type a few examples of the pattern you want
Press Ctrl + E

Ctrl + E

Use cases: Extract first names, format phone numbers, split addresses