Scribd
Upload
20 views2 pages

Excel Formulas Functions Cheat Sheet

This cheat sheet provides essential Excel formulas and functions, including SUM, AVERAGE, IF, VLOOKUP, and CONCAT, along with their usage examples. It highlights common mistakes to avoid, such as forgetting absolute references and using merged cells, and offers quick tips and shortcuts for efficient Excel use. The document serves as a handy reference for users looking to enhance their Excel skills.

Uploaded by

deepakr3410a
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
20 views2 pages

Excel Formulas Functions Cheat Sheet

This cheat sheet provides essential Excel formulas and functions, including SUM, AVERAGE, IF, VLOOKUP, and CONCAT, along with their usage examples. It highlights common mistakes to avoid, such as forgetting absolute references and using merged cells, and offers quick tips and shortcuts for efficient Excel use. The document serves as a handy reference for users looking to enhance their Excel skills.

Uploaded by

deepakr3410a
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Excel Formulas & Functions Cheat Sheet

1. Key Definitions & Formulas


=SUM(A1:A5): Adds all numbers in cells A1 through A5.

=AVERAGE(A1:A5): Calculates the average of selected cells.

=COUNT(A1:A10): Counts numeric values only.

=COUNTA(A1:A10): Counts all non-empty cells.

=IF(A1>50,"Pass","Fail"): Returns 'Pass' if A1 > 50, otherwise 'Fail'.

=MAX(A1:A5) / =MIN(A1:A5): Finds the highest or lowest value.

=VLOOKUP(A2, B2:D10, 3, FALSE): Looks for A2 in the first column and returns value from column 3.

=HLOOKUP(A2, B1:Z3, 2, FALSE): Looks for A2 in the top row and returns value from row 2.

=INDEX(B2:D10, 3, 2): Returns value at 3rd row and 2nd column in range.

=MATCH("Apple",A1:A10,0): Finds the position of 'Apple' in range.

=INDEX + MATCH: =INDEX(B2:B10,MATCH("Apple",A2:A10,0)) - powerful lookup combo.

=XLOOKUP(A2, B2:B10, C2:C10): Modern and more flexible lookup function.

=CONCAT(A1," ",B1): Joins text values together.

=TEXT(A1,"$#,##0.00"): Formats a number as currency text.

=TODAY() / =NOW(): Inserts current date or date and time.

2. Step-by-Step Examples
Example 1: Calculate Average Score

=AVERAGE(B2:B6)

-> Adds all scores in B2:B6 and divides by the number of entries.

Example 2: Conditional Result with IF

=IF(C2>=70,"Pass","Fail")

-> Displays 'Pass' if score is 70 or above; otherwise 'Fail'.

Example 3: Lookup a Price

=VLOOKUP("Apple",A2:C10,3,FALSE)

-> Finds 'Apple' in the first column and returns price from column 3.

Example 4: Combine Names


=CONCAT(A2," ",B2)

-> Combines first and last names into one cell.

3. Common Mistakes to Avoid


- Forgetting to use absolute references ($A$1) when copying formulas.

- Not setting FALSE in VLOOKUP when an exact match is required.

- Mixing text and numbers (Excel won't calculate properly).

- Using merged cells - they break formulas and references.

- Leaving blank cells in lookup ranges can cause errors.

4. Quick Tips & Shortcuts


- Ctrl + ` : Show/hide all formulas.

- F4 : Toggle absolute/relative references ($A$1, A$1, $A1, A1).

- Ctrl + Shift + L : Add or remove filters.

- Ctrl + Arrow keys : Jump to the edge of data region.

- Ctrl + Space / Shift + Space : Select entire column/row.

- Ctrl + Shift + + : Insert new cell/row/column.

- Alt + = : AutoSum selected range quickly.

- Keep formula ranges dynamic using Tables or named ranges.

You might also like