Top Basic Excel Formulas Everyone Should Know: A Cheatsheet

1. SUM Formula

Formula: =SUM(range)

How to Use: Use the SUM function to add up a series of numbers. Simply replace the “range” with the cells you want to sum. For example, if you want to add numbers from cell A1 to A5, the formula would be =SUM(A1:A5).

What It Does: This formula quickly calculates the total of numbers in a selected range of cells. It’s perfect for tasks like summing up your sales totals, expenses, or any other numerical data in your spreadsheet. It saves time and reduces errors when manually adding numbers.

2. AVERAGE Formula

Formula: =AVERAGE(range)

How to Use: To calculate the average, just replace “range” with the cells you want to average. For example, =AVERAGE(B1:B10) would give you the average of the values in cells B1 through B10.

What It Does: This formula computes the average (mean) of a set of numbers. It’s useful for tracking the average sales over a period of time, determining average monthly expenses, or finding the central tendency in your data. It’s a quick way to spot trends and averages within your business.

3. COUNT Formula

Formula: =COUNT(range)

How to Use: Use the COUNT formula to count how many cells in a given range contain numbers. For instance, =COUNT(A1:A10) will count how many cells in the range from A1 to A10 contain numeric data.

What It Does: This formula counts the number of cells that contain numbers within a selected range. It’s especially helpful when you need to count the number of transactions, products sold, or items that meet specific numeric criteria.

4. COUNTA Formula

Formula: =COUNTA(range)

How to Use: Replace “range” with the cells you want to count. For example, =COUNTA(C1:C20) will count how many cells in the range C1 to C20 are not empty.

What It Does: COUNTA counts the number of non-empty cells in a range. This can be very useful when tracking the number of filled entries in a dataset, such as product names, customer information, or completed orders.

5. IF Formula

Formula: =IF(logical_test, value_if_true, value_if_false)

How to Use: This formula checks whether a condition is met (logical test), and returns one value if it’s true and another if it’s false. For example, =IF(A1>100, "Target Met", "Target Not Met") will return “Target Met” if the value in A1 is greater than 100, otherwise, it will return “Target Not Met”.

What It Does: The IF formula is perfect for making conditional decisions in your data. It’s ideal for situations like checking if sales targets are met, or if a particular value in a range exceeds a set threshold. It allows you to automate decision-making and reporting, reducing the need for manual checks.

6. VLOOKUP Formula

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

How to Use: Use the VLOOKUP formula to search for a specific value in the first column of a table and return a value from another column in the same row. For example, =VLOOKUP("Customer ID", A1:D10, 3, FALSE) will search for “Customer ID” in column A and return the corresponding value from the third column (C) in the same row.

What It Does: This formula helps you find and retrieve data from large tables quickly. It’s perfect for looking up customer information, order details, or any other data based on a specific identifier, such as a product code or customer ID.

7. HLOOKUP Formula

Formula: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

How to Use: Similar to VLOOKUP, but HLOOKUP searches for a value in the first row and returns data from the specified row. For example, =HLOOKUP("Product", A1:F5, 4, FALSE) will look for the value “Product” in the first row and return the data from the 4th row in the same column.

What It Does: HLOOKUP is great for looking up data in tables where information is arranged horizontally rather than vertically. It’s useful for pulling data from spreadsheets with categories in rows instead of columns.

8. INDEX Formula

Formula: =INDEX(array, row_num, [column_num])

How to Use: The INDEX formula returns the value of a cell at a specified row and column within a range. For example, =INDEX(A1:C10, 3, 2) will return the value from the cell in the third row and second column within the range A1:C10.

What It Does: INDEX gives you the ability to dynamically reference specific data in a table. It’s a flexible formula that allows you to pull data from any part of a table based on the row and column you specify, and it can be used alongside MATCH for advanced lookups.

9. MATCH Formula

Formula: =MATCH(lookup_value, lookup_array, [match_type])

How to Use: The MATCH formula finds the position of a specific value within a range. For example, =MATCH("Product B", A1:A10, 0) will return the position of “Product B” within the range A1:A10.

What It Does: MATCH helps you find the location of a value in a list, which is useful for dynamic lookups when combined with INDEX. It’s especially handy when you need to determine where a value is in a dataset to use that information for further calculations or data extraction.

10. SUMIF Formula

Formula: =SUMIF(range, criteria, [sum_range])

How to Use: Use the SUMIF formula to add up values that meet specific criteria. For example, =SUMIF(B1:B10, ">1000", C1:C10) will sum the values in the range C1:C10 only if the corresponding value in B1:B10 is greater than 1000.

What It Does: This formula allows you to sum values based on a condition. It’s perfect for adding up sales totals that exceed a certain threshold, like calculating sales revenue for orders over a specific amount or expenses that fall within a certain category.

11. SUMIFS Formula

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

How to Use: The SUMIFS formula adds up the values in a specified range that meet multiple criteria. For example, =SUMIFS(C1:C10, A1:A10, "East", B1:B10, "Product A") will sum the values in C1:C10 where the corresponding values in A1:A10 are “East” and the values in B1:B10 are “Product A”.

What It Does: SUMIFS allows for more complex calculations by taking multiple conditions into account. It’s great for situations where you need to sum sales or expenses based on multiple factors, such as region, product type, or date range.

12. COUNTIF Formula

Formula: =COUNTIF(range, criteria)

How to Use: COUNTIF counts how many cells in a given range meet a specific condition. For example, =COUNTIF(A1:A10, "Sales") will count how many times the word “Sales” appears in cells A1 to A10.

What It Does: This formula helps businesses count entries that meet a specified condition, such as counting how many products were sold, how many transactions exceed a certain amount, or how often a specific event happens.

13. COUNTIFS Formula

Formula: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

How to Use: COUNTIFS counts how many cells meet multiple conditions. For example, =COUNTIFS(A1:A10, "East", B1:B10, "Product A") will count how many times “East” appears in A1:A10 and “Product A” appears in B1:B10.

What It Does: COUNTIFS is perfect when you need to count entries that satisfy more than one condition. It’s often used for tracking specific data points like sales transactions in certain regions or customer feedback with specific ratings.

14. CONCATENATE Formula

Formula: =CONCATENATE(text1, text2, ...)

How to Use: The CONCATENATE formula joins multiple text strings into one. For example, =CONCATENATE(A1, " ", B1) will join the values in A1 and B1 with a space between them.

What It Does: This formula is great for merging data from different columns, like combining first and last names into one full name, or combining street address components into one cell.

15. TEXT Formula

Formula: =TEXT(value, format_text)

How to Use: The TEXT formula converts a value to text in a specified format. For example, =TEXT(1234.56, "$#,##0.00") will format the number 1234.56 as “$1,234.56”.

What It Does: TEXT is useful for formatting numbers, dates, or currency to match specific styles for reporting or presentations. It ensures consistency and makes your data look professional, especially when dealing with financial figures.

16. LEFT Formula

Formula: =LEFT(text, [num_chars])

How to Use: The LEFT formula extracts a specified number of characters from the beginning of a text string. For example, =LEFT("Product123", 7) will return “Product”.

What It Does: LEFT is ideal for extracting the first part of a string, such as product codes, area codes, or other prefixes that appear at the start of the text.

17. RIGHT Formula

Formula: =RIGHT(text, [num_chars])

How to Use: The RIGHT formula extracts a specified number of characters from the end of a text string. For example, =RIGHT("Product123", 3) will return “123”.

What It Does: RIGHT is useful for extracting the last part of a string, such as the last digits of product codes, dates, or other suffixes that appear at the end of the text.

18. MID Formula

Formula: =MID(text, start_num, num_chars)

How to Use: The MID formula extracts characters from the middle of a text string, starting at a specified position. For example, =MID("Product123", 8, 3) will return “123”.

What It Does: MID is great for pulling specific parts of a string, like extracting a product ID from a longer product code, or pulling out the middle part of a description.

19. NOW Formula

Formula: =NOW()

How to Use: The NOW formula returns the current date and time. For example, =NOW() will return something like “3/15/2023 11:25 AM”.

What It Does: NOW is useful for adding time stamps to reports or tracking when a report or data entry was made. It can help in situations where you need to track real-time updates or document the exact date and time of an event.

20. TODAY Formula

Formula: =TODAY()

How to Use: The TODAY formula returns the current date. For example, =TODAY() will return “3/15/2023”.

What It Does: TODAY is helpful for calculating deadlines, managing ongoing projects, or working with data that needs to be based on the current date, like calculating how long a customer has been with your business or tracking current balances.

21. ROUND Formula

Formula: =ROUND(number, num_digits)
How to Use: The ROUND formula rounds a number to a specified number of digits. For example, =ROUND(123.456, 2) will return 123.46.
What It Does: ROUND is useful when you need to round financial figures, such as prices or totals, to a specific number of decimal places. For example, rounding to two decimal places for prices ensures consistency in pricing.

22. ROUNDUP Formula

Formula: =ROUNDUP(number, num_digits)
How to Use: The ROUNDUP formula always rounds a number up, no matter what the digits after the decimal point are. For example, =ROUNDUP(123.01, 1) will return 123.1.
What It Does: ROUNDUP is helpful when you want to round up figures, such as rounding up prices, quantities, or time intervals. This is often used in pricing strategies or when dealing with minimum quantities.

23. ROUNDDOWN Formula

Formula: =ROUNDDOWN(number, num_digits)
How to Use: The ROUNDDOWN formula always rounds a number down, regardless of the digits after the decimal. For example, =ROUNDDOWN(123.99, 1) will return 123.9.
What It Does: ROUNDDOWN is useful when you need to round down numbers, such as prices, inventory levels, or time. It’s especially helpful when you need to avoid overestimating or when exact values are necessary.

24. TRUNCATE Formula

Formula: =TRUNC(number, num_digits)
How to Use: The TRUNC formula removes the fractional part of a number without rounding it. For example, =TRUNC(123.456, 2) will return 123.45.
What It Does: TRUNCATE is useful when you need to remove decimals entirely or only a certain number of decimal places, without rounding. This is often used for certain financial or statistical calculations where rounding is not necessary.

25. LEN Formula

Formula: =LEN(text)

How to Use: The LEN formula returns the number of characters in a text string. For example, =LEN("Product123") will return 11.

What It Does: LEN is helpful for checking the length of text strings, such as counting the number of characters in product descriptions, customer IDs, or order numbers. This can be particularly useful for data validation or ensuring uniformity in entries.

26. XLOOKUP Formula

Formula: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

How to Use: XLOOKUP searches a range or array for a specific value and returns a corresponding value from another array. For example, =XLOOKUP("Product A", A1:A10, B1:B10) will return the value from B1:B10 that corresponds to “Product A” in A1:A10.

What It Does: XLOOKUP is more versatile than VLOOKUP, allowing for horizontal and vertical searches and handling both approximate and exact matches. It’s a powerful tool for looking up data across large datasets.

27. AND Formula

Formula: =AND(logical1, [logical2], ...)

How to Use: The AND formula checks whether all arguments are TRUE. For example, =AND(A1>100, B1="Yes") will return TRUE only if both conditions are met (A1 is greater than 100 and B1 is “Yes”).

What It Does: AND is useful for testing multiple conditions at once. For example, you can check if both sales figures exceed a target and if a customer has paid before approving an order.

28. OR Formula

Formula: =OR(logical1, [logical2], ...)

How to Use: The OR formula checks whether any of the arguments are TRUE. For example, =OR(A1>100, B1="Yes") will return TRUE if either A1 is greater than 100 or B1 is “Yes”.

What It Does: OR is ideal for situations where any condition must be met. For instance, checking if a customer is from a specific region or has spent over a certain amount in order to apply a discount.

29. NOT Formula

Formula: =NOT(logical)

How to Use: The NOT formula reverses the boolean value of its argument. For example, =NOT(TRUE) will return FALSE.

What It Does: NOT is useful in complex formulas where you need to exclude certain values or conditions. For example, you can use it to test if a condition is not true, such as ensuring a value is not equal to a specified amount.

30. RANK Formula

Formula: =RANK(number, ref, [order])

How to Use: The RANK formula determines the rank of a number in a list of numbers. For example, =RANK(95, A1:A10) will return the rank of the number 95 within the range A1:A10.

What It Does: RANK is ideal for ranking numerical data, such as sales performance, employee productivity, or exam scores. It helps to compare and rank items based on a specific metric.

31. SUBTOTAL Formula

Formula: =SUBTOTAL(function_num, ref1, [ref2], ...)

How to Use: The SUBTOTAL formula performs a specified aggregate function (such as sum, average, count) on a range of data, excluding hidden rows. For example, =SUBTOTAL(9, A1:A10) will calculate the sum of values in A1:A10, ignoring hidden rows.

What It Does: SUBTOTAL is especially useful when working with filtered data, allowing you to calculate subtotals in reports or financial statements while ensuring hidden rows are excluded from calculations.

32. PMT Formula

Formula: =PMT(rate, nper, pv, [fv], [type])

How to Use: The PMT formula calculates the payment for a loan based on constant payments and a constant interest rate. For example, =PMT(5%/12, 60, 10000) will calculate the monthly payment for a $10,000 loan over 5 years at 5% interest.

What It Does: PMT is a powerful tool for small business owners or individuals looking to calculate monthly loan payments, interest, and financing details. It can be used for loans, mortgages, or any type of installment-based payment.

33. IPMT Formula

Formula: =IPMT(rate, per, nper, pv, [fv], [type])

How to Use: The IPMT formula calculates the interest portion of a loan payment for a given period. For example, =IPMT(5%/12, 1, 60, 10000) will return the interest paid in the first month of a $10,000 loan over 60 months at 5% interest.

What It Does: IPMT helps you understand how much of your payment goes toward interest during any specific period of a loan, which can help with financial planning or understanding loan amortisation.

34. FV Formula

Formula: =FV(rate, nper, pmt, [pv], [type])

How to Use: The FV formula calculates the future value of an investment or loan, based on periodic payments. For example, =FV(5%/12, 60, -200, 0) will calculate the future value of $200 monthly payments over 60 months at 5% interest.

What It Does: FV is useful for small businesses to forecast savings, investment growth, or the future value of regular payments. It can also be used for retirement planning or investment projections.

35. GROWTH Formula

Formula: =GROWTH(known_y's, [known_x's], [new_x's], [const])

How to Use: The GROWTH formula predicts exponential growth based on existing data. For example, =GROWTH(A2:A10, B2:B10, B11) will predict the next value based on existing data trends.

What It Does: GROWTH is ideal for forecasting sales, revenue growth, or market trends based on past performance, helping businesses make informed predictions about future growth.

36. TREND Formula

Formula: =TREND(known_y's, [known_x's], [new_x's], [const])

How to Use: The TREND formula returns values based on a linear trend. For example, =TREND(A2:A10, B2:B10, B11) will predict future values based on a straight-line trend.

What It Does: TREND is great for linear forecasting, such as predicting sales, revenue, or performance for the next quarter based on past data. It helps businesses plan for future performance based on past results.

37. DATE Formula

Formula: =DATE(year, month, day)

How to Use: The DATE formula creates a date from individual year, month, and day values. For example, =DATE(2025, 2, 12) will return the date 12/02/2025.

What It Does: DATE is useful for tracking deadlines, calculating project timelines, or determining the age of a person or entity based on their birthdate.

38. DATEDIF Formula

Formula: =DATEDIF(start_date, end_date, unit)

How to Use: The DATEDIF formula calculates the difference between two dates in years, months, or days. For example, =DATEDIF(A1, B1, "Y") will return the number of years between two dates.

What It Does: DATEDIF is perfect for calculating employee tenure, project durations, or the length of customer relationships. It helps businesses track time-based metrics efficiently.

39. NETWORKDAYS Formula

Formula: =NETWORKDAYS(start_date, end_date, [holidays])

How to Use: The NETWORKDAYS formula calculates the number of working days between two dates, excluding weekends and holidays. For example, =NETWORKDAYS(A1, B1) will return the number of weekdays between two dates, excluding weekends.

What It Does: NETWORKDAYS is useful for tracking project durations, employee working days, or calculating the turnaround time for tasks, while excluding weekends and any custom holidays.

40. TEXTJOIN Formula

Formula: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

How to Use: The TEXTJOIN formula combines multiple text strings into one, with an optional delimiter. For example, =TEXTJOIN(", ", TRUE, A1:A5) will combine the text in A1:A5 with commas between each string.

What It Does: TEXTJOIN is ideal for consolidating information such as combining customer names, addresses, or any other grouped data into a single string, with customisable delimiters.

41. FIND Formula

Formula: =FIND(find_text, within_text, [start_num])

How to Use: The FIND formula locates the position of a specific character or substring within a text string. For example, =FIND("apple", A1) will return the starting position of “apple” within the text in A1.

What It Does: FIND is useful for businesses to pinpoint specific characters or words within larger text fields, such as finding product codes, locations, or keywords within descriptions.

42. SEARCH Formula

Formula: =SEARCH(find_text, within_text, [start_num])

How to Use: The SEARCH formula works similarly to FIND but is more flexible, allowing for case-insensitive searches. For example, =SEARCH("apple", A1) will find “apple” regardless of its case (e.g., “Apple” or “APPLE”).

What It Does: SEARCH is great for scanning through customer feedback, emails, product descriptions, or any text where case-insensitive searches are needed.

43. ISBLANK Formula

Formula: =ISBLANK(value)

How to Use: The ISBLANK formula checks if a cell is empty. For example, =ISBLANK(A1) will return TRUE if A1 is empty, and FALSE if it contains data.

What It Does: ISBLANK is useful for validating whether a certain cell has been filled with the necessary data, such as checking for missing order details or incomplete forms.

44. ISNUMBER Formula

Formula: =ISNUMBER(value)

How to Use: The ISNUMBER formula checks if a value is a number. For example, =ISNUMBER(A1) will return TRUE if A1 contains a number, and FALSE if it contains anything else.

What It Does: ISNUMBER helps validate data inputs, ensuring that values like sales amounts, prices, or inventory quantities are numeric.

45. ISERROR Formula

Formula: =ISERROR(value)

How to Use: The ISERROR formula checks if a value results in an error. For example, =ISERROR(A1/B1) will return TRUE if the formula results in an error (like division by zero).

What It Does: ISERROR is useful for managing errors in formulas, preventing calculations from breaking, and improving data integrity by checking for unexpected results.

46. IFERROR Formula

Formula: =IFERROR(value, value_if_error)

How to Use: The IFERROR formula returns a custom value if a formula results in an error. For example, =IFERROR(A1/B1, "Error") will return “Error” if A1/B1 results in an error, instead of showing the default error message.

What It Does: IFERROR is helpful for handling errors and displaying user-friendly messages or default values, which is crucial for improving user experience and maintaining smooth operations in financial or data-driven reports.

47. LEN Formula

Formula: =LEN(text)

How to Use: The LEN formula returns the number of characters in a string. For example, =LEN(A1) will return the number of characters in the text within cell A1.

What It Does: LEN is useful for checking the length of data, such as ensuring product codes, descriptions, or customer names fit within required character limits for databases or forms.

48. UNIQUE Formula

Formula: =UNIQUE(array)

How to Use: The UNIQUE formula extracts unique values from a range of data. For example, =UNIQUE(A1:A10) will return only the distinct values in the range A1:A10.

What It Does: UNIQUE is perfect for eliminating duplicates in datasets, allowing businesses to identify unique customers, products, transactions, or any other distinct data entries.

49. FILTER Formula

Formula: =FILTER(array, include, [if_empty])

How to Use: The FILTER formula filters an array based on specific criteria. For example, =FILTER(A1:B10, B1:B10="North") will display only the rows where column B contains “North”.

What It Does: FILTER is ideal for segmenting data by specific conditions, such as showing only sales data for a specific region, customer type, or product category.

50. SEQUENCE Formula

Formula: =SEQUENCE(rows, [columns], [start], [step])

How to Use: The SEQUENCE formula generates a sequence of numbers in a range. For example, =SEQUENCE(5, 1, 1, 1) will generate the numbers 1 through 5 in a single column.

What It Does: SEQUENCE is helpful for generating sequential numbers for inventory, order numbers, or any situation requiring automatic numbering, making it great for streamlined data management.

Our Expert Tips On Using Excel

We use Excel for data analysis ALL the time, which means we have amassed some serious Excel skills. So believe us when we say that these functions in Excel will make your life easier.

Tip 1: Use Find and Replace to Quickly Modify Data

• Use Ctrl + F to open the Find function, or Ctrl + H for Find and Replace.
• Replace specific words or values with just a few clicks—perfect for large data sets or when you need to make batch updates.

Tip 2: Quickly Select All Cells with Data

• Use Ctrl + A to select all cells in your worksheet or press Ctrl + Shift + Arrow keys to quickly select a range with data.

Tip 3: Leverage the AutoFill Feature for Efficient Data Entry

• Use the fill handle (the small square at the bottom-right of a selected cell) to auto-fill sequences like dates or numbers.
• Double-click the handle to fill a column with data up to the last filled row in an adjacent column.

Tip 4: Freeze Panes to Keep Key Data Visible

• Keep headers or labels visible while scrolling by using the Freeze Panes option under the View tab.
• Freeze specific rows or columns by selecting the cell below/next to the row/column you want to freeze before choosing Freeze Panes.

Tip 5: Filter Data for Easier Analysis

• Turn on filters by selecting your data and clicking Filter in the Data tab.
• Filter by specific criteria, such as values, text, or dates, to narrow down your data for analysis.

Tip 6: Use Conditional Formatting to Highlight Important Information

• Highlight cells based on certain conditions using Conditional Formatting (e.g., values greater than a threshold, dates that are coming soon, etc.).
• Customise the formatting with colour scales, data bars, or icon sets to easily spot trends or outliers.

Tip 7: Summarise and Analyse Data with Pivot Tables

• Use Pivot Tables to summarise large amounts of data and gain insights quickly.
• Drag fields into Rows, Columns, and Values to create dynamic reports that update when your data changes.

Tip 8: Use Charts to Visualise Your Data

• Select your data, including labels, and go to the Insert tab.
• Choose from various chart types like Column, Bar, Line, Pie, and Scatter to represent your data visually.
• Charts are especially helpful for understanding trends, comparisons, and distributions at a glance.

Supercharge Your Business with These Formulas!

Running a small business means juggling multiple tasks at once—tracking expenses, managing inventory, and analysing sales can feel overwhelming.

Without the right tools and formulas, these daily tasks become time-consuming and prone to errors, potentially affecting your business decisions.

Mastering formulas in Excel will help you streamline your workflows, eliminate errors, and make smarter, data-driven decisions. Start practising today, and watch how Excel transforms the way you manage your business.