Excel Formulas and Functions Quick Tips

Introduction

• Calculate data with ease in Excel

1. Formula and Function Basics

• Build a formula in a cell with =
• Create a named range
• Reference cells and worksheets
• Join text with an ampersand

2. Identifying Values

• Calculate a total with SUM
• Sum values that meet specific criteria with SUMIF
• Find the mean value with AVERAGE
• Test for valid data with IF
• Create complex tests with AND and OR
• Find record values with MAX, MIN, SMALL, and LARGE
• Perform date calculations with TODAY()

3. Count Functions

• Count cells that contain numbers with COUNT
• Count nonblank cells with COUNTA
• Return a number of occurrences with COUNTIF
• Check-in with Jess

4. Text Functions

• Format numbers and dates with TEXT
• Extract characters with LEFT AND RIGHT
• Extract substrings with MID
• Count characters with LEN
• Identify where a character occurs with FIND
• Join strings with delimiters with TEXTJOIN
• Change text with SUBSTITUTE and REPLACE

5. Index, Match, and Lookups

• Retrieve a specific value with XLOOKUP
• Use INDEX and MATCH to retrieve a value

6. Avoiding Errors and Invalid Formulas

• Remove leading spaces with TRIM
• Include IFERROR to trap errors in formulas

Conclusion

• Learn more complex features of Excel