Skip to content

Excel Formulas and Functions: A Comprehensive Guide

Search Content:

If you are proficient in using Formulas and Functions in Microsoft Excel, you will save millions of your time.

For example, formulas for addition, subtraction, multiplication, function SUM, and AVERAGE will complete the arithmetic calculations. You can even perform logic testing using Excel functions such as the IF function.

In fact! Microsoft Excel has lots of formulas and functions that can be used. Not just 10 or 20 formulas, but there are hundreds more.

Luckily, you don’t need to memorize all these formulas and functions. Just use the ones that are most popular and relevant to your work.

Of all the existing Excel formulas, I have summarized them into 11 main chapters. I focus this summary on its usefulness for the work fields of Office Administration, Educators (Teachers), Management / Accountants, and so on…

Note: Most of the content is available in Indonesian. Please visit the Excel Formula Guide!

“THE MAIN KEY TO UNDERSTANDING THE USE OF FORMULA AND FUNCTIONS IN EXCEL IS TO UNDERSTAND THE RULES FOR WRITING THEM”

Rolan Mardani

Chapter 1: Formulas & Functions (Basic)

If you want to learn to use Excel formulas, you must understand how to enter formulas/functions via the formula bar or the insert function.

You are also required to know basic formulas such as addition, subtraction, multiplication, division, percentages, and so on.

These basic formulas are very common and are most often used in any profession, whether in administration, teachers, accountants, etc. For in-depth understanding, I have divided Chapter 1 into 11 Sub-Chapters. Please learn them one by one.

  1. Formulas & Functions: Basic examples of how to use Excel formulas & functions in the Formula Bar using the equal sign (=) and using the Insert Function feature.
  2. Addition: 9 examples of manual and automatic (basic) addition formulas.
  3. Subtract: Subtraction Formula in Excel + 8 Complete Examples.
  4. Multiply: Multiplication Formula in Excel: manual, between cells, PRODUCT Function, absolute reference ($), and with paste special.
  5. Divide: Division formula in Excel: 4 basic examples + using paste special and QUOTIENT & MOD functions.
  6. Square Root: Formula for calculating roots and squares (powers) of any number using the caret symbol (^) and the SQRT function.
  7. Percentage: Excel percentage formula: Basic examples, addition, subtraction, multiplication, and division of percent to calculate value growth (%).
  8. Named Range: How to create a named range so that formulas are easier to understand + named range tricks for formulas in tables.
  9. Dynamic Named Range: Dynamic named ranges (connected) can expand automatically when you add new cells in the named range.
  10. Paste Options: 15+ examples of paste special Excel: Useful if you copy cells that use formulas, conditional formatting, data validation, cell formatting, etc.
  11. Not Equal To: Example using the formula Not equal to <>.
  12. Cm to Inches: How to convert centimeters to inches

Chapter 2: Cell Reference

All Excel Formulas & Functions, both basic and advanced, will always be associated with something called “Cell Reference”. For example, the formula =A1+A$10 or =A1+Sheet2!B1. Arguments A1, A$10, and Sheet2B1 are also called Cell Reference.

When you use the wrong cell Reference, there is a high possibility that errors will occur in formulas such as #N/A, #REF!, etc.

You may also encounter difficulties when copying and pasting cells that contain formulas, either in the same sheet or a different sheet.

To avoid and overcome possible errors that will occur, it’s a good idea to understand the following Cell Reference sub-chapters:

  1. Cell References: The importance of cell references in Excel when using formulas/functions. Learn all about relative, column/row absolute, and absolute (Dollar Symbol $) references.
  2. 3D-Reference: How to use formulas/functions for different sheets, as well as 5+ in-depth examples (bonus case studies recap data between sheets).
  3. Structured References: Structured references in tables to make formulas/functions easier to understand.
  4. Union & Intersect: Learn Excel formulas/function operators. Such as commas (,) or semicolons (;), and spaces ( ).
  5. External References: How to use formulas/functions in different Excel files (Workbooks).
  6. Hyperlinks: How to create hyperlinks between sheets, different Excel files, to other files (such as images, PDF, Word, etc.), and tricks for creating hyperlink designs using buttons.
  7. Percent Change: Example using absolute value / absolute reference/symbol $ for beginners.
  8. Copy Exact Formula: How to copy a formula/function automatically according to the reference.

Chapter 3: COUNT & SUM

Who isn’t familiar with the COUNT and SUM family functions such as the COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, SUM, SUMIF, and SUMIFS functions?

Basically, to calculate the sum of numbers, you can use the SUM function and its variants depending on your goals. Meanwhile, if you want to count a lot of data, then use the COUNT function and its variants.

However, each variant of the COUNT and SUM Functions can perform calculation operations for specific purposes. For more details, please learn each of the following subchapters:

  1. COUNT & SUM: Types and how to use the COUNT and SUM functions in Excel (COUNT, COUNTIF, COUNTIFS, SUM, SUMIF, SUMIFS)
  2. COUNT: How to use the COUNT function to count the number of cells that contain numeric data (numbers, dates, times, percents, fractions).
  3. COUNTA: How to use the COUNTA function to count the number of cells that contain only (not empty).
  4. COUNTBLANK: How to use the COUNTBLANK function to quickly count the number of empty cells in a data range.
  5. COUNTIF: Complete example of how to use the COUNTIF formula (Criteria based on numbers, text, calculating errors, etc.)
  6. COUNTIFS: How to use the COUNTIFS function to count multiple data with 2 or more criteria.
  7. SUM: Complete SUM function (such as summing a specific range, all columns, cells that are far apart, large numbers, auto-summing, summing every nth row, etc.)
  8. SUMIF: How to use the SUMIF function to add conditional data (Complete)
  9. SUMIFS: How to use the SUMIFS function to add up data with many conditions/criteria.
  10. SUBTOTAL: Example of how to calculate a complete Excel SUBTOTAL (writing rules + differences in function num 1-11 and 101-111 for AutoFilter and manual hidden cells).
  11. Running Total: Adds up the running total (The running total will change each time a new value is added)
  12. SUMPRODUCT: A more complex way of adding (understanding each example)
  13. Count Characters: How to count the number of characters in a cell or range and how to count the number of times a particular character appears in a cell or range. [Bonus] Combination of SUM, LEN, SUBSTITUTE, and LOWER functions.
  14. Count Cells with Text: An alternative to the COUNTIF function. Trick: Count the number of cells containing letters using a combination of the SUM, IF, ISTEXT, SUMPRODUCT, ISLOGICAL, and COUNTIFS functions.

Chapter 4: Logical

If you aim to carry out logical (conditional) testing, then I’m sure some of the formulas in this Logical Chapter can meet your needs. For example, determining Pass or Fail.

For example, if cell A1 contains >=80, then Pass. If <80, then Fail.

To fulfill a goal like this, you can use one of the Logical Formulas such as IF, AND, OR, NOT, SWITCH, etc.

Of course, I also have several in-depth examples related to these functions such as the multilevel IF formula or the combined IF + NOT + ISBLANK function, etc.

So… get your guidelines…

  1. Logical Tests: Types of logical functions (IF, AND, OR, NOT) and how to use them (Basic level).
  2. Comparison Operators: Comparison operators for Logical functions (such as equal to =, greater than >, less than <, not equal to <>, etc.).
  3. IF Function: IF Formula Writing Rules, how to use it for logic (conditional) tests. For example, if A1 > 70, then B2 = “Pass”. (Single IF, Multilevel, Combination of 2 or more IF formulas with “And” + “Or” Criteria, etc.).
  4. IF with 3 Conditions (Or More): How to use the IF formula with 3 conditions (criteria) + best alternative uses.
  5. IF + AND Combination: How to combine IF formula + AND formula (Single and Multilevel). Gain deep understanding.
  6. OR: Example of using the OR (Complete) function. Combine IF + OR, AND + OR, OR + WEEKDAY, and XOR.
  7. IFS: Can only be used on Excel version 2016 or later. The use of this function is the same as the Stacked IF Function.
  8. Contains Specific Text: Aims to check whether the cell contains specific text.
  9. SWITCH: This can only be used on Excel version 2016 or later. The use of this function is almost similar to the IFS function.
  10. IF Cell is Blank: Combined IF + NOT + ISBLANK function to check whether a cell is empty or not. Then use multiplication formulas etc.

Chapter 5: Date & Time (Basic)

when using Excel, which field of work doesn’t deal with Dates and Times? I’m sure all fields of work need this DATE & TIME formula.

For example, for a Human Resource when they want to calculate an employee’s years of service. Of course, you can use this function.

Important! I need to emphasize, before learning to use the DATE & TIME function, first understand the Format Number sub-chapter. Because in my experience, there are still some mistakes in using the date and time format in Excel.

To maximize the use of date and time formulas, first understand the formulas that can support your work as follows:

  1. TODAY: Creates today’s date with the TODAY formula and the keyboard shortcut CTRL + ;
  2. DAY, MONTH, YEAR: Get day (1 – 31), month (1 – 12), and year (1900 – 9999) values from a date.
  3. DATE: The formula creates a date, and adds day, month, and year with the DATE function.
  4. DATEVALUE: Converts a date to a serial number (a positive integer number).
  5. NOW: Creates the current date and time with the NOW formula.
  6. SECOND, MINUTE, HOUR: Retrieves seconds (0 – 59), minutes (0 – 59), and hours (0 – 23) values from time data.
  7. TIME: Formula to create and add time based on hours, minutes, and seconds.
  8. TIMEVALUE: Converts time to a serial number (decimal number).

Chapter 6: Date & Time Advanced

After understanding the basics of using date and time formulas (Chapter 5), it’s time you can start combining these 2 types of formulas.

Specifically in this chapter, I will discuss the most frequently used combination of date and time formulas as follows:

  1. DATEIF: How to use the DATEDIF function for dates in Excel complete.
  2. Today’s Date: How to use the DATE and NOW functions to get today’s date and use Static Date.
  3. Calculate Age: How to calculate complete age in Excel (such as 25 years 5 months 21 days)
  4. Time Difference: How to calculate the distance between times A and B etc. (complete)
  5. Weekdays: How to use WEEKDAY, NETWORKDAYS, and WORKDAY functions to calculate days or weekdays in 2 date ranges
  6. Days until (H): How to calculate the remaining days before the H day. For example, before a birthday or before an event, etc.
  7. Time Sheet: Creates an automatic time calculator in your Excel file.
  8. Last Day of the Month: How to use the EOMONTH function to get the last date of the current month.
  9. Holidays: How to find out the holiday dates of any year (2020, 2021, 2022, 2023, etc.) in Excel (complete).
  10. Quarter: How to easily find out which quarter a particular date is in. For example, September 2 2020 is in Quarter 3 of 2020.
  11. Day of the Year: How to find out the remaining days in this or any other year. This sub-chapter combines the DATE & YEAR functions.

Chapter 7: Text

If the COUNT & SUM chapter is useful for data between each cell, then the TEXT chapter is useful for data within cells.

For example, you want to use a formula to combine the contents of 2 or more cells, calculate how many characters are in a cell, separate the contents of 1 cell into several cells, delete spaces, and so on.

At first, you might be a little confused. But I’m sure you will understand better with the following examples:

  1. TEXT Functions: How to combine words in 2 or more cells, take certain words in a cell, and replace certain words without changing the original word.
  2. Separate Strings: How to separate words in cells without changing their original values.
  3. Count Words: How to count the number of characters, remove excessive spaces, etc. (complete)
  4. Text to Columns: How to separate content in 1 cell into separate columns. This usually occurs in downloaded CSV files and is often considered messy.
  5. FIND Function: How to use the FIND Function to find certain characters in your Excel file.
  6. SEARCH: How to use the SEARCH function in Excel to find the position of a character in a particular cell.
  7. Change Case: Automatic way to change words to capital letters.
  8. Remove Spaces: How to delete spaces or excessive spaces in cells. There is also a way to delete special characters in cells.
  9. Compare Text: How to compare 2 cells with the same content. This example uses case-sensitive and non-case-sensitive comparison methods.
  10. SUBSTITUTE vs. REPLACE: How to use the SUBSTITUTE and REPLACE (complete) functions to replace certain words/characters in cells.
  11. TEXT: How to use the TEXT function to combine numbers and letters into 1 cell.
  12. CONCATENATE: How to combine 2 or more cells using the CONCATENATE function and the Ampersand symbol (&).
  13. Substring: Combines the MID, LEFT, RIGHT, FIND, LEN, SUBSTITUTE, REPT, TRIM, and MAX functions to extract specific characters in cells. For example, taking numbers in different sentences.

Chapter 8: Lookup & Reference

The Lookup & Reference function is useful for searching and retrieving values (with certain criteria) from data sources.

At first glance, it is almost similar to the Find & Select feature. But don’t be mistaken. There are quite significant differences. Take a look at the following examples:

  1. Lookup & Reference: Basics of Lookup and Reference functions in Excel (VLOOKUP, HLOOKUP, MATCH, INDEX, and CHOSE)
  2. VLOOKUP: Complete guide on how to use the VLOOKUP function (writing rules, type TRUE, FALSE, etc.).
  3. Tax Rates: Example case of calculating Income Tax (PPH) using the VLOOKUP function.
  4. INDEX: How to use the INDEX function to search for data from 1 Data Range (Source) or more. There are 7 different Examples of use + Tips
  5. MATCH: How to use Excel’s MATCH function to find the position of a particular item in a data range, whether it is in the column or row of the data range. Search criteria can be determined by yourself.
  6. INDIRECT: How to use the INDIRECT function. You are sure to find inspiration from this function.
  7. INDEX + MATCH: Combines Excel’s INDEX and MATCH functions as an alternative to the VLOOKUP function. Follow this guide if you want more complex search functions than VLOOKUP.
  8. Two-way Lookup: Search for specific data based on 2 or more criteria. Like creating a search form with 2 or more criteria.
  9. OFFSET: How to use the OFFSET function to search for data based on location (cell/sheet, etc.).
  10. Case-sensitive Lookup: How to use the INDEX, MATCH, and EXACT functions to search for data (case-sensitive).
  11. Left Lookup: How to search for data to the left of the source. This example cannot be done using the VLOOKUP function.
  12. Locate Maximum Value: How to find the maximum value in a lot of data. You can also find out which cell the maximum value is in.
  13. Two-column Lookup: How to search for data based on 2 different criteria.
  14. Closest Match: Combination of INDEX, MATCH, MIN, and ABS functions to find data that is closest to your criteria.
  15. Compare Two Columns: Compares 2 columns. Can be used to find duplicate and unique data.
  16. XLOOKUP: If you are an Office 365 user, It is better to use the XLOOKUP function instead of VLOOKUP.

Chapter 9: Financial

Especially for those of you who work as accountants or in the management field, of course, you will often use financial formulas such as PMT, RATE, NPER, etc.

Basically, this formula is intended to carry out financial calculation operations such as calculating the future value of investments, compound interest, and so on.

Hopefully, the following examples + case studies can provide an in-depth understanding for you…

  1. Financial: The most popular Financial functions (PMT, RATE, NPER, PV and FV).
  2. PV (Present Value): Calculates loan payments or investment returns and fixed interest rates. (Another understanding: PV = Current value of money).
  3. RATE: Calculates the interest rate percentage on an investment or loan.
  4. NPER: Calculates the number of investment repayment periods based on periodic and continuous payments at a fixed interest rate.
  5. FV (Future Value): Calculates the future value of an investment based on periodic and continuous payments at a fixed interest rate.
  6. PMT, PPMT, and IPMT: Calculate payments for loans based on fixed payments and a fixed interest rate.
  7. Loans with Different Durations: Example of comparing 2 loans with different durations.
  8. Investment or Annuity: Example of calculating the future value of an investment (FV function) and the present value of an annuity (PV function).
  9. Compound Interest: How to calculate compound interest in Excel.
  10. Compound Annual Growth Rate: How to use the RRI function to calculate the compound annual growth rate of an investment over several years.
  11. Loan Amortization Schedule: How to create a loan amortization schedule. This example uses a combination of several Excel functions.
  12. NPV: An example of using the NPV function to calculate the present value of a series of future cash flows and subtract the initial investment.
  13. IRR: Example of using the IRR function to calculate a project’s internal rate of return (Discount Rate).
  14. Depreciation: Excel function to calculate depreciation. Complete examples (SLN, SYD, DB, DDB, and VDB functions).

Chapter 10: Statistical

I can conclude that the Statistical Function is a collection of “One Million People” Excel formulas. Almost all fields of work use this function. Even final-year students who are writing their theses often use these statistical formulas.

For example, calculating the Average, Median, Mode, Min, Max, or Standard Deviation values to see an overview of the data. Or for a teacher who wants to determine student ranking using the RANK formula.

You can learn about these goals and several other similar goals from each of the following sub-chapters:

  1. Statistics: An overview for you of the Statistics functions (AVERAGE, AVERAGEIF, MEDIAN, MODE, STANDARD DEVIATION, MIN, MAX, LARGE and SMALL).
  2. AVERAGE: How to use the AVERAGE function to calculate the average (Complete Guide).
  3. AVERAGEIF & AVERAGEIFS: How to use the AVERAGEIF function to calculate the average with 1 criterion and AVERAGEIFS for more than 1 criterion.
  4. MAX & MIN: How to use the MAX and MIN + 7 formula Complete examples.
  5. MAXIFS and MINIFS: How to use the MAXIFS and MINIFS functions to calculate the highest and lowest values with 1 or more criteria (Excel 2016 or later only).
  6. Standard Deviation: How to use the STDEV.P function to find the standard deviation of a population and STDEV.S for a sample.
  7. MODE: How to use the MODE function to search for frequently occurring values (mode).
  8. FREQUENCY: How to use the FREQUENCY function to find out how often a value occurs in a set of data.
  9. RANK: How to use the RANK function to determine the ranking of a list of values.
  10. PERCENTILES & QUARTILES: How to use the PERCENTILES and QUARTILES functions.
  11. Forecast: How to predict future values with the FORCAST.LINEAR and FORECAST.ETS functions and the Forecast Sheets feature.
  12. Negative Numbers to Zero: Trick to change negative numbers to zero without changing positive numbers.
  13. Random Numbers: How to use the RAND, RANDBETWEEN, and RANDARRAY functions to generate random numbers.
  14. Box and Whisker Plot: How to create a Box and Whisker Plot in Excel.
  15. Weighted Average: How to calculate a weighted average or use weights in Excel.

Chapter 11: Round

In the Decimal Place sub-chapter, you can learn how to round numbers in Excel without formulas.

Meanwhile, in this Round Chapter, you will learn various ways of rounding numbers using formulas that you can’t do using the Decimal Place feature.

  1. ROUND: How to round numbers in Excel using the ROUND, ROUNDUP, and ROUNDDOWN functions.
  2. Chop off Decimals: Removes decimals using the INT and TRUNC functions
  3. Nearest Multiple: How to round numbers based on multiples using the MROUND, CEILING, and FLOOR functions
  4. Even and Odd: How to round to even and odd numbers (EVEN and ODD functions) and find out whether a number is even or odd (ISEVEN and ISODD functions)
  5. MOD: MOD function to find out the remaining number after dividing a certain number. For example, 49:10 = 4 remainder 9. Then the value that will appear is 9.

Chapter 12: Formula Errors

When learning to use Excel Formulas, there is a possibility that Excel will provide error results. For example, #VALUE!, #NAME?, #DIV/0!, etc.

If you encounter an error message from an Excel formula/function, then to resolve the error, please follow the guidelines in the following sub-chapter:

  1. Formula Errors: How to resolve formula/function errors in Excel. Such as #####, #NAME?, #VALUE!, #DIV/0!, and #REF!.
  2. IFERROR: Create another alternative text/word/value if Excel displays an Error.
  3. ISERROR: How to use the ISERROR function to find out whether the formula will error if used on data.
  4. AGGREGATE: How to use the complete AGGREGATE function on cells containing error messages (alternatives SUM, AVERAGE, COUNT, etc. if the data contains an error message)
  5. Circular References: Circular references to prevent errors.
  6. Formula Auditing: How to quickly check formula errors.
  7. Floating Point Errors: One of the calculation errors in Excel, but very rare.

Chapter 13: Array Formulas

All formulas and functions begin with an equal sign (=). For example, for the following MAX function: =MAX(A1:A5). The formula will look for the highest value from the range A1:A5. Looks simple as usual, right?

So, have you ever seen the MAX formula like this: {=MAX(F1:F5-E1:E5)}. This formula will find the highest value of the difference between the ranges F1:F5 and E1:E5.

See, the MAX formula is enclosed in curly braces { … } and there are 2 data ranges in 1 MAX formula argument, namely F1:F5-E1:E5. These are the characteristics of a formula array.

However, I need to emphasize, that for Array Formulas, do not type the curly braces manually. Because Excel does it automatically. To understand better, study the following sub-chapters:

  1. ARRAY Formulas: Get to know ARRAY formulas and functions, their uses, writing rules + examples.
  2. Constant ARRAY: Get to know the constants in the ARRAY formula (1 dimension vertical, horizontal, and 2 dimensions).
  3. Named Constants: How to give a name (identity) to an ARRAY constant so that it can be reused easily.
  4. COUNT-Errors: Uses an Array to count the number of formula errors.
  5. COUNT-Unique Values: Counts unique values using Array, UNIQUE function, etc.
  6. COUNT-with Or Criteria: Counts with or without criteria. Complete example.
  7. SUM-Every Nth Row: How to sum every nth row in Excel with Arrays
  8. SUM-Largest Numbers: How to add the largest numbers in a certain range.
  9. SUM-Range with Errors: How to add up data in a range that contains an Excel error message
  10. SUM-with Or Criteria: How to sum with criteria (complex example)
  11. Most Frequently Occurring Word: How to find the most frequently occurring words in Excel.
  12. System of Linear Equations: How to calculate linear equations in Excel.

Microsoft Excel Guide Shortcut

Excel Basics
Formula/Function
Data Analytics
VBA & Macro
New Guideline
Lihat Versi Bahasa Indonesia