Learning Excel formulas is a must when you work with data. Excel is used in all industries for its versatility and popularity. Excel is a go-to tool to store, manage, and retrieve data. Its versatility extends to generating reports and providing business insights.
Excel is used in the financial sector for financial modelling and analysis, in the banking sector, and in business for data analysis and forecasting. You can learn how to use this software with comprehensive Excel courses, such as those at Academy of Learning Career College.
One important feature of Excel is the Excel formulas feature. Learning a few frequently used formulas while working with large data sets is extremely valuable.
Excel Formulas – List and Guide for Beginners
- What Are Excel Formulas?
- Difference Between Excel Functions and Formulas
- Excel Formulas List
- Frequently Asked Questions
- Closing Thoughts
What Are Excel Formulas?
An Excel formula is an expression that takes operands and operators. Operators show what action to perform. Operands show what item to apply the action to. For instance, a+b is a formula to calculate the sum of two numbers. Here, a and b are the operands, and + is the operator.
In other words, an Excel formula is an expression you use to make mathematical calculations in Excel. You can use a formula to add, subtract, divide, multiply, etc. For example, =1+1 is a formula to calculate the sum of two numbers. Note, you will always have = at the beginning of your formula in Excel.
A formula is one of a few ways you could add numbers in Excel. You could also use Excel’s built-in functions to quickly find the sum of two numbers, cells, or a range of cells. Functions are predefined formulas in Excel. They make calculations in Excel faster and easier, especially if you are working with large data sets. The Excel function to add two numbers together is =SUM(
- Example of Excel Formula:
- In a blank cell, type the = sign.
- Enter a number, cell address, or manually click a cell address to add it (In this case, we will enter 1).
- Type your operator (In this case we will use +).
- Enter another number, cell address, or manually click a cell address to add it (Again, we will use the number 1)
It should look like this: =1+1. - Hit enter, and your calculation will appear.
- Example of Excel Function:
- In a blank cell, type =SUM(
- To find the sum of cells A1 and A2, you can simply type =SUM(A1,A2)
- Hit enter and your calculation will appear.
Besides basic calculations, there are a wide variety of formulas you can use in Excel to aid your work. You can find a separate tab, Formulas, that contains all the Excel formulas list.
In the Formulas tab, in the Function Library group, you can also find Financial, Logical, Text, Date & Time, Lookup & Reference, and other functions.
- Financial Functions – This has a list of financial functions used to calculate interest rates, return depreciation amounts, and perform other functions related to finance.
- Logical Functions – This lists logical functions, such as TRUE, FALSE, IF, and IFNA.
- Text Functions – This covers all the functions needed to work with text.
- Date & Time Functions – This lists functions to work with date, time, and year.
- Lookup & Reference Functions – Lookup and reference functions help you find the value of a row or column.
- Math & Trig Functions – All mathematical and trigonometric functions are listed here.
- More Functions:
- Statistical
- Engineering
- Cube
- Information
- Compatibility
- Web
The below screenshot shows the functions listed in each category. When you hover your mouse over any function, it displays a dialogue box explaining what the function does. You can click on the option “Tell me more” to learn more about the function. This opens a separate window where the function is explained in detail.
Insert Function – You can pick any function from the list and get help working with the functions.
Insert function is another method of inserting a function in your selected cell. First, select a cell where you wish your Excel formula to appear. Then, click on Insert Function, select a category, and then select the appropriate function.
In the above example, the category Text is chosen. You can see that the functions used in the text are listed. Choose any function you would like to use. In this example, the CONCAT function is chosen, which combines two texts.
You can see the explanation of the function, which links a list or range of text or strings. When you click on that function, the Function Arguments dialogue opens. You are then prompted to enter the text. You must provide a text or a series of texts you wish to link in the text bar.
Click OK. You can find the result of the concatenated text at the bottom of the dialogue box, and the same concatenated text should also appear in the spreadsheet.
AutoSum – AutoSum automatically does a quick calculation of the numbers that you save in the spreadsheet.
Recently Used – This gives a list of functions that you recently used.
Difference Between Excel Functions and Formulas
An Excel function is a predefined piece of code that executes when you use it in a spreadsheet. It takes the specified number of arguments as defined by the source code. Since it is a predefined expression, you can select any cell in the worksheet where you wish the result to be displayed and enter ‘=’ followed by the function name.
As you enter the function name, you should see the list of all the Excel functions and what the function does. For instance, to calculate the sum of ‘n’ numbers, the predefined function in Excel is =SUM(number1, number2,…). Similarly, the function is =AVERAGE(number1, number2,…) to calculate the average.
An Excel formula is an expression that must contain the right operators and the operands as arguments. The user must take the utmost care to represent all the operations involved in the formula to arrive at the correct result.
For instance, to calculate the sum of ‘n’ numbers, the Excel formula is =(number1+number 2..) To find the average of these numbers, the formula is =(number1+number 2…)/n). Where ‘n’ is the total number in the list or table.
Excel Formulas List
Let’s look at the most commonly used formulas you need to know when working in Excel. Mastering them can save you a lot of time and give you the confidence to be an expert.
1. IF()
The IF() function in Excel performs the logical operation. Select Formula → Function Library → Logical, and you find logical functions, like AND, OR, NOT, IF, IFNA, and other such functions. The IF() function checks for a condition and returns a positive value or text that you provide if the condition is satisfied. If it does not meet the condition, it returns the alternative negative value or text you provide along with the positive value or text.
Syntax =
IF(logical_test,[value_if_true],[value_if_false])
In the above example, the IF() function is entered in the formula bar, specifying the logical test that has to be performed on the values in the spreadsheet. Here, the condition is to check whether the person’s age is above 18 or not.
If the value in the table satisfies the condition, the value Yes is displayed, or else No is displayed.
Another method of using the function is to select the cell where you need to enter the formula and then select Formulas → Logical → IF. Now, enter the function arguments: the logical test, the value to display if the condition is true, and the value to return if the condition is false.
2. LEN()
The function LEN() returns the number of characters in a text string. It counts even the spaces and the special characters in the string.
=LEN(TEXT)
In the example above, the function is entered in two ways. One in the formula bar as =LEN(A2), and the other by selecting the options Formulas → Text → Len. The function takes the text string or the cell reference as an argument.
3. PROPER()
This function converts a text string to the proper case: the first letter in each word is converted to an uppercase letter and all other letters to lowercase. The function takes one argument that can accept the actual text enclosed in quotation marks, a formula that returns text, or a reference to the cell that contains the text.
Syntax
=PROPER(TEXT)
In the example above, the names are in small letters. The Excel formula, =PROPER() is chosen from the options: Formulas → Text → PROPER.
4. REPLACE( )
The Excel function, =REPLACE(), replaces part of a string with a different text string.
Syntax
=REPLACE(OLD_TEXT, START_NUM, NUM_CHARS,NEW_TEXT)
This function takes four arguments. The first argument is the old text. In the second argument, start_num, you must mention the text position where you need the new text to be entered. The third argument, NUM_CHARS, is the length of the new text, and the fourth argument is the new text that has to replace the old text.
In the example above, a new text, ‘er’, is included at the end of the old text. Both values in cells A2 and A3, Garden and Market, have 6 letters each. The function inserts the new and old text, displaying them as Gardener and Marketer.
5. SEARCH()
The SEARCH() function returns the number of characters at which a specific character or string is first found, reads it left to right, and is not case-sensitive.
Syntax
=SEARCH(FIND_TEXT,WITHIN_TEXT,[START_NUM])
This Excel function takes two mandatory arguments and one optional argument. The argument Find_Text finds the text that you have mentioned. It accepts wildcard characters, such as ~, ? and *. The argument Within_Text is the text in which you wish to search the Find_Text. Start_num is the character number in Within_text, counting from left, at which you want to start searching.
This function returns a default value of ‘1’ when the character to be searched is not mentioned. That’s why ‘1’ is returned as a result in cell C8 since the text to be searched is not mentioned in the table. The SEARCH() function is case-insensitive.
6. Date Functions
The Date function in Excel is used to find the date and days between the dates, months, and years. It is grouped under the category, Date and Time functions.
Syntax
DAY(SERIAL_NUMBER) – returns the day of the month, a number from 1 to 31.
DAYS(END_DATE, START_DATE) – returns the number of days between the two dates.
MONTH(SERIAL_NUMBER) – returns the month, a number from 1 (January) to 12 (December).
NOW() – returns the current date and time, formatted as a date and time.
YEAR(SERIAL_NUMBER) – returns the year of a date, an integer in the range 1900-9999.
TODAY() – returns the current date formatted as a date.
WEEKDAY(serial_number, return_type) – returns a number from 1 to 7 identifying the day of the week of a date.
The DATE() function takes the cell reference which contains the date as an argument and returns the result as a number. Excel considers the year 1900 as 1 and increments the number as the year proceeds.
In the example above, the functions =TODAY() and =NOW() do not take any arguments. The function TODAY() returns the current date as a result, and the function =NOW() returns the current date and time as a result.
7. VLOOKUP( )
VLOOKUP() function is categorized in Lookup and Reference and is used to search and find a corresponding value from the table.
Syntax
=VLOOKUP(LOOKUP_VALUE, TABLE_ARRAY,COL_INDEX_NUM,[RANGE_LOOKUP])
In the example above, to find the profession of a person, use the VLOOKUP function that searches vertically and returns the result. You can enter the formula directly in the formula bar, =VLOOKUP( A6, A3:C6,3 FALSE).
Cell A6 contains the lookupo_value, Sarah. It has to be searched in the table ranging in the cells A3:C6. The column index is in the 3rd position from the lookup_value. The range_lookup can either take TRUE or FALSE. A ‘FALSE’ returns the exact match, and a ‘TRUE’ returns an appropriate match.
Related: LOOKUPS in Excel Explained – A Beginner’s Guide
8. PRODUCT()
The PRODUCT() function is listed under the Mathematical and Trigonometric function library. It returns the product of the numbers that are mentioned within the function. It can also take the cell reference where the numbers are available.
Syntax
=PRODUCT(NUMBER1,NUMBER2)
9. ROUND( )
ROUND() function rounds a number to the specified number of digits. This is categorized under the Mathematical and Trigonometric function library.
Syntax
=ROUND(NUMBER, NUM_DIGITS)
In the example above, the values in cells A1 and A2 are multiplied, and the result is available in cell B4. Using the ROUND() function, it is rounded to the nearest 2 digits.
10. AutoSum Functions
The AutoSum functions allow you to perform quick calculations, such as sum, average, count, minimum, and maximum. All these functions operate upon a series of numbers and return the appropriate result.
Syntax
=SUM(NUMBER1, NUMBER2…)
In the example above, there are three different numbers for which the sum, average, count of numbers, min, and max is calculated, and the results are returned
11. CORREL()
CORREL() is a Statistical function grouped under More Functions. There are many Statistical functions available that can be used for different purposes. This function calculates the relationship between two different values.
The CORREL() function takes two data sets as arguments and returns the result as positive or negative. Statistical functions require operating data sets, and Excel formulas cannot calculate using a single value.
Syntax
=CORREL(ARRAY1, ARRAY2)
In the example above, there are two data sets, X and Y. To find the correlation between these two data sets, use the CORREL function. It can be accessed from Functions → More Functions → Statistical → Correl.
When you select that function, it prompts you to enter the values. Select the values in column A and input them in Array1 in the Function Arguments dialogue box. Similarly, select the values in column B and give them as input in Array2. Click OK. You get the value displayed in the spreadsheet.
Frequently Asked Questions
1. What are the benefits of using Excel?
Excel is the best tool to do the following:
- Store data
- Perform calculations
- Create a table to organize and analyze data
- Visualize data using charts
- Predict the outcomes
2. What are the most valuable tools in Excel?
Excel offers built-in tools, such as:
- Functions
- Tables and Charts
- Pivot Table and Slicers
- Sort and Filter
- Data Validation
- Macros
3. How do I improve my Excel skills?
Follow these steps to improve your Excel skills:
- Learn basic formulas. This can help you to understand and work with complex ones.
- Use conditional formatting and other basic elements to organize your data.
- Learn how to sort and filter data.
- Learn keyboard shortcuts to work fast in Excel.
Closing Thoughts
Excel formulas are used in various calculations. All fields, such as business, research, and education, need a technique to store and work with numbers. The built-in Excel functions are capable of catering to every industry’s data storage and manipulation needs.
The above-listed functions are basic and easy to understand. When you start learning, you will be amazed at the kind of control you develop over data.
Are you motivated to learn more? Check out AOLCC’s courses in Excel and Microsoft Office Applications to learn everything from what a pivot table is in Excel to practical tips on how to make the most of Microsoft Office Applications. Also, you can learn what lookup functions are, and how to use them using our Lookups in Excel – Beginner’s Guide. You will also earn Micro-credentials on the completion of the course.