## Microsoft ExcelMicrosoft Excel is an office use application Moreover, the Microsoft Excel is one of the most suitable spreadsheet programs that help us to store and represent the data in tabular form, manage and manipulate data, create optically logical charts, and more. Excel provides us the worksheet to create a new document in it. We can save the Excel file with ## What is Formula?In Microsoft Excel, the formula is used to define the relationship between the given variables as well. Generally, the formula involves symbols and various mathematical operators like as addition, subtraction, multiplication, and division. Formulas are used in enormous fields like science, education, research, finance, and various developing as well as developed fields. The purpose of the formula is to just find out the required solution to the problem, and based on the requirement, the formula is modified respectively. ## Why make use of the formula for finding solutions?There are several reasons why the formula is important for the calculations: - Manual calculations are a time-consuming process and sometimes they lead to Error.
- The formula represents the complex relationship between the given variables and helps the user to understand the problem quickly and easily.
- Moreover, the formulas help us to predict the data and draw the best output for the data as well.
- Results are retrieved by making use of the formula that are accurate and precise.
## Formulas in Microsoft ExcelBy default, Microsoft Excel primarily provides various formulas to calculate the data. The reasons why formulas are used in Microsoft Excel are as follows: - Repetitive mathematical tasks are done in Microsoft Excel. Hence every time doing manual calculations takes more time respectively.
- And to get the accuracy as well as the speed of the data, the formulas are used.
- The formula helps us to easily organize the data, which helps the user to study and go through the data easily.
## How to enter formulas in Excel?Now to type a formula in Microsoft Excel, the following steps need to be followed as well:
**For example:**If in case the mathematical operation is based on the addition, the formula will be like**=SUM (A1, A2).**Here A1 and A2 are the cell names, and the**SUM function**primarily adds two given variables.
## Excel FormulasMicrosoft Excel usually contains a wide range of formulas for each mathematical operation. The formulas primarily return the required result for the data and the result even though contains an Error respectively. And the variety of Microsoft Excel formulas is listed as follows: ## AdditionThe addition is considered as one of the most important Moreover, the various formulas that are used for the addition in Microsoft Excel are as follows:
By making use of the '+' operator, the values that are present in the worksheet are added in Microsoft Excel .**For example:**If the cells A1 and B1 contain the data, which needs to be summed, we need to enter the formula in the new cell, namely C1 as =A1+B1. And by just pressing the Enter button, the result will be getting displayed in cell C1 respectively.
## SUM FunctionAnd now the
In this, if the requirement is to just add out the multiple data ranges, the formula would be modified as **For example:**If we want to sum out the data that are present in the cell range A1:A5, B1:B5 we need to enter the formula in the new cell C1 as**=SUM (A1:A5, B1:B5),**after that we need to press out the Enter button as well. The sum of the data will be displayed in the selected cell respectively.
If the requirement is to just add the given data that are based upon the conditions, the **For example:**To add the specified data range namely**A1:A5**which is greater than the number 5, the formula to be entered is**=SUMIF (A1:A5,">5")**in the new cell B1, and then we will be pressing out the**Enter button**respectively. The sum of values greater than five is displayed in**cell B1.**
Similarly, the syntax will get varied for the conditions like lesser than, not equal to, equal to, etc. ## SubtractionSubtraction is considered one of the other most important arithmetic operations that can be used to find out the difference between the given values and it is the inverse of addition. The
By making use of the **For example:**If cell A1 contains the value five and B1 contains the data 4, where B1 needs to be subtracted with A1, and we need to enter the formula in the new cell, which is named as**C1 as =A1-B1,**and pressing out the Enter button will display the result in cell C1 as 1.
The **For example:**If the worksheet contains the data of the**cell range B1:B5**which needs to be subtracted from the value present in cell A1, the formula would be,**=A1-SUM (B1:B5)**which needs to be entered in cell C1. After that, we need to press the**"Enter"**button. The value will get displayed in**cell C1**respectively.
## MultiplicationMultiplication is termed to be the other most important type of
The asterisk symbol in Microsoft Excel is used to multiply the given values, and the symbol is represented by **For example:**If the particular cells A1 and B1 contain the value that must be multiplied, then the formula will be written as**=A1*B1**and need to be entered in cell C1, after that we need to press the**"Enter"**button as well. The multiplication result will be displayed in cell C1 respectively.
As the name implies, the Product function is used to multiply two given values. - For example, Cell A1 and B1 contain the value which needs to be get multiplied to write down the formula as
**=PRODUCT (A1, B1) in cell C1,**just after that we are required to press**the "Enter"**button as well. The product of the value will be displayed in cell C1. **For example:**To multiply the specified given range, the formula will be modified as**=PRODUCT (A1:A5),**which needs to be entered in the new cell B1, just after that we need to press**the "Enter"**button as well. The result will be displayed in cell B1 which is the product of the range A1:A5.**For example,**The formula will be then modified to multiply the given specified range with a particular number as**=PRODUCT (A1:A5)*B1.**Here A1:A5 is the cell range, and B1 is the specified number and is entered as value 10 respectively in cell C1, we need to enter the formula and press the**"Enter"**button as well. The result will be displayed in the selected cell.
## DivisionThe division is one of the other important
The **For example:**If the value present in cell**A1 is called 10,**then it needs to be divided with cell**B1 called 5,**the formula would be written as**=A1/B1**in the new cell C1, after that we need to press out the**"Enter" button.**The result will be displayed in cell C1 as well.
The quotient function returns the quotient of the dividing values which is an integer. - For example: If the value that is present in
**cell A1**needs to be divided with**cell B1,**the formula would be written as**=QUOTIENT (A1, B1)**in cell C1.The quotient function displays the integer part of the division as well, after that, we need to press the**"Enter" button**as well. The quotient for the given data will be displayed in cell C1 as well.
## AverageThe average function is purposely used to find out the mean of the given numbers, and the The formula that is used to calculate the **For example:**Now to calculate out the average for three cells, namely A1, B1, and C1 enter the formula in the new cell D1 as**=AVERAGE (A1, B1, C1),**and after that, we need to press out the**"Enter"**button, the function returns the average for the specified values respectively.
## CountThe Count function counts the number of data present in the specified range. - For example: If the cell ranging from
**A1:A5**contains the values, that need to be counted, the formula will be written as**=COUNT (A1:A5)**in cell B1. Here A1:A5 is called cell range, and after that, we are required to**"Enter"**button the result will be displayed in cell B1 respectively.
The COUNT function counts the cells which contain only the numerical values. ## 1. COUNTAIf the data contains a mixture of the text as well as the alphabet, the The syntax used is none other than: **For example:**If cells A1 and B1 need to be counted, then the result will be**=COUNTA (A1, B1)**is entered in new cell C1, after that we need to press the**"Enter" button.**The result will be displayed in cell C1 respectively.
The count blank () function in Microsoft Excel is used to The syntax used is,
Here A1:A5 is called the cell range. **For example:**If cell A1:A5 needs to be counted for the blank cells, then we can make use of the formula that is none other than**=COUNTBLANK (A1:A5)**and is entered in**the new cell B1,**after that we need to press the**"Enter"**button as well. The number of blank cells will get displayed in cell B1 efficiently.
And the Count if () is an in-built function in Microsoft Excel that is used to count out the cells in the given range which are based upon certain conditions or criteria as well. The syntax for Count if the () condition is:
In which, **Range-**It primarily defines the cells in the particular range that need to be counted respectively.**Criteria-**These are based upon these criteria the cells are counted respectively.**For example:**If the cell A1:A5, needs to be counted and whose value is greater than 5, then the formula will be**= Count if (A1:A5,">5")**is entered in new cell B1, after that we need to press "Enter" button, and the Count of cells that meet the specified criteria will be displayed in cell B1 respectively.
The Countif function counts the range of the cells which are based upon the multiple criteria. The syntax for the Countif function is:
In which, **Range 1, Range 2-**The range needs to be evaluated in the given data.**Criteria 1, Criteria 2-**The respective cells are counted in the data that are based upon the given criteria.
And the Countifs function can accept around 127 range or criteria respectively. **For example:**If the cell A1:A5 which needs to be counted whose value is equal to APPLE, and cell range id ranging from B1:B5 is counted whose value is equal to "A" then the formula would be**= Count if (A1:A5, " an apple," B1:B5, "A")**and it will be entered in new cell C1 just after that we need to press**"Enter"**button, so the Count of cells that meet the specified criteria will get displayed in cell C1 respectively.
## MODULUSNow the Modulus function in Microsoft Excel is used to return out the remainder of the dividing values, and the terms which are involved in the division are The syntax to perform the Modulus function is as follows,
Here A1 and B1 is the cell name. And in the formula either the dividend is specified directly or by cell name. **For example:**To enter out the dividend directly, the formula will be modified as**=MOD (A1, 4) in new cell B1,**and after that we are required to press**the "Enter" button**as well, and the**MOD function**will then returns the remainder in cell B1 respectively.
Here And both the divisors, as well as the dividend, are entered directly in the formula as,
## PowerThe power function in Excel raises the given number to the specified power value as well, and the formula that can be used to find out the power of the given value in Excel is as follows:
Here A1, B1 is called the cell value. **For example:**If the particular cell A1 contains value 5, and cell B1 contains value 4, then in that case we need to enter the formula as**=POWER (5, 4)**in new cell C1, after that we are required to press**"Enter"**button as well so the result will be displayed in cell C1 respectively.
And the power value is entered directly or referred to in the cell name. To directly enter the power, the syntax will be:
The formula A1 contains the value raised to the power of 5. ## CEILINGThe ceiling function in Microsoft Excel is primarily used to round a number to the nearest multiple of the specified number, and the syntax of the Ceiling function is none other than the: The number usually defines the value rounded, and significance is the multiple to which we want to round as well. **For example,**CEILING (11, 5) means the**result is 15,**the nearest**multiple of 5.**
The CEILING function rounds the number, so if it is already the multiple of the significance, it returns the number without any change. CEILING (10, 5) returns 10, as 10 is already a multiple of 5. ## FLOORThe FLOOR function works opposite to the
The number primarily defines the value which is rounded, and significance is the multiple to which we want to round respectively. **For example:**FLOOR (15.6, 5) means that the**result is 15**which is the number down to the nearest multiple of the 5.
The FLOOR function usually rounds the number, so if it is already the multiple of the significance, it returns the number without any change as well. FLOOR (15, 5) returns 15, as 15 is already a multiple of 5 respectively. ## CONCATENATEConcatenation is joining or the combining of two or more strings or characters. **For example:**If A1 contains the data**HELLO**and B1 contains the data**GOOGLE,**then the syntax will be like this,**=CONCATENATE (A1," ", B1)**which is entered in cell C1. After that, we need to press the**"Enter" button**as well. The result will be**HELLO GOOGLE.**
Another, ## LENAs the name suggests the particular LEN function returns the total length of the characters in the given specified cell, and it counts the special characters as well as the spaces respectively. **For example:**If in case of cell A1 contains the characters called**GOOGLE,**to find out the length of the characters, then the syntax will be**=LEN (A1)**entered in new cell B1 and just after that we need to press**"Enter"**button and the result will get displayed in the cell B1.
The LEN function displays the result as ## REPLACEAs the name implies that the The syntax for the REPLACE function will be like this:
Here num_chars defines the number of characters that need to be replaced as well. **For example:**If cell A1 contains the data**A101,**which needs to be replaced with the data**B101,**then the formula would be written as**=REPLACE (A1, 1, 1, "B")**in new cell B1 and just after that we are required to press "Enter" button and the result will get displayed in cell B1 respectively.
Next, if cell A1 contains the name ## SUBSTITUTEAnd the The syntax for the
**For example:**If cell**A1**contains the data**"Hello Google"**that needs to be replaced with**"Hello World",**the syntax will be like,**=SUBSTITUTE (A1, "Google", "World")**in the given cell B1, and after that we required to press "Enter" button as well and the result will be**"Hello World."**
Besides all this, the And if the cell contains text strings that are repeated more than once, the position of the text wants to be mentioned in the given formula as well. **For example:**If cell A1 contains the text string**" MS Excel 2007, MS Word 2007",**the year 2007, which is present in the second position, needs to be replaced with the year 2023, and the syntax of the formula will be,
And the output will be displayed as follows, In the example of substituting both the year with the same year, then the formula will be written as:
The result will be "MS EXCEL 2023, MS Word 2023". ## LEFT, RIGHT, MID## LEFT FunctionNow the LEFT function in Microsoft Excel usually returns the text which is left of the starting position of the given specified text as well. **For example:**If the given cell A1 contains the text string**"Google is a search engine"**to display the text**"Google",**the formula will be written as**=LEFT (A1, 6)**in new cell B1, and just after that we need to press**"Enter"**button. The LEN function extracts the character on the left of the starting position. Here**A1**represents the cell name. As Google contains six characters, it is written in the formula, so the LEFT function displays out the text left of the 6th position character respectively.
## MID FunctionThe MID function in Microsoft Excel is purposely used to extract the specific text from the cell. The syntax of the MID function is as:
In the formula, The text primarily indicates the specified text in the cell where the particular text is extracted.
**For example:** If cell A1 contains the**"Hello Google"**data, to extract the word Google from it, the formula will be written as**=MID (A1, 7, 6)**in new cell B1, and then just after that we need to press the**"Enter"**button as well. The MID function primarily extracts the character which is present in the mid of the starting position. The result will be Google respectively.
## Right FunctionThe Right function in Microsoft Excel extracts the specified number of characters from the right side of the text string as well. The syntax is as follows:
The text represents the cell where the specified characters are extracted. In which the **For example:**If cell A1 contains the data**"Hello World",**to extract the text World then the syntax will be**=RIGHT (A1, 6) in cell B1,**and just after that we need to press the**"Enter"**Button and the**RIGHT Function**primarily extracts out the character that is present on the right starting position. The result will be World.
Here the RIGHT function displays the result as World respectively. ## Upper, Lower, and Proper Case## UpperAs the name indicates, the Upper function converts all the specified text into Upper Case. And the syntax for the function is,
**For example:**If cell A1 contains the data,**"Google"**to change the word to uppercase, then the formula will be,**=UPPER (A1)**is entered in a new cell, namely B1, and just after that we need to press**"Enter"**Button and the result will be**GOOGLE.**
## LowerNow the The syntax for the function is,
**For example:**If cell A1 contains the data**"GOOGLE"**to change the word to lowercase, the formula will be**=LOWER (A1)**entered in cell B1, and just after that we need to press**the "Enter"**button and the result will be**Google.**
## Proper CaseIt was well known that, the respective proper case function in Microsoft Excel is used to The syntax of the proper case,
**For example:**If a particular cell contains the data**"GOOGLE"**and to convert the data into the proper case, then the syntax will be**=PROPER (A1)**that needs to be entered in new cell B1, and just after that we will be pressing the**"Enter"**Button and the outcome will**" Google".**
## NOW ()And the NOW () function displays the The syntax for the NOW () function is,
Enter the syntax in any of the cells and just after that press the ## TODAY ()And the The syntax for the TODAY () function is,
Now we need to enter the syntax in any of the cells and just after that, we need to press the ## DAY ()The DAY () function primarily returns the **For example:**To display today's day, we need to enter the syntax as:
In any of the cells, the result will be today's date respectively. ## MONTH ()As the name implies that the MONTH () function is used to return the **For example:**To display the current Month (), we need to enter the syntax as:
In any of the cells, the result will be the ## YEAR ()As the name implies, the **For example:**To display the current Year, we need to enter the syntax as:
## TIME ()The TIME () function in Microsoft Excel is used to convert the specified hours, minutes, and seconds into time format, which are entered as Excel Serial Number. **For example:**We need to enter specified hours, minutes, and second in the formula as,**=TIME (20, 40, 20)**
The result will be displayed at 8:40 P.M respectively. ## HOUR, MINUTE, SECONDThe Hour function primarily displays the hour value from the current time starting from 0 to 23, and here **For example:**To display the current hour, we need to enter the syntax in the new cell as**=HOUR (NOW ()),**and just after that we need to press**the "Enter"**button as well, and the**Hour Function**will return the current hour of the day respectively.
## Minute ()The Minute () function is used to return the minute from a time value where the number ranges from
## Second ()The Second () function in Microsoft Excel is used to return the second from a time value where the number ranges from **For example:**To display the second, we need to enter the syntax in the new cell as**=SECOND (NOW ()),**and just after that we need to press the**"Enter"**Button. The Second Function returns the second from a current time efficiently.
## Dated if ()The Dated if the () function returns out the difference between two dates in terms of the years, **For example:**To calculate a person's age, the two required dates are entered in cells A2 and B2. A1 contains the person's date of birth, namely 1-1-2014, and B1 contains today' date, such as 5-6-2016
And if we want to calculate the age of a person, we need to enter the syntax as ## VLOOKUP ()As the name implies, the VLOOUP () function is called the "Vertical Lookup" and looks for the specified data that are present in the table's leftmost column. It returns the value present in the specified row as well as the column. The syntax for the VLOOKUP function is,
**lookup_value -**The lookup_value indicates the value which needs to be searched in the leftmost column of the table.**table_array-**This table_array or function is used to find out the specified value in that range that needs to be retrieved as well.**Col_index_num -**The col_index_num represents the column number where the particular value is present.**Range_lookup -**This is an optional value, either**"True" or "False".**If the value is**"True" or "blank",**it looks for the appropriate value in the table. If the value**"False" is entered,**it looks for an exact match in the table.
## HLOOKUPAs the name implies, The syntax for the HLOOKUP function is as follows:
**lookup_value -**The lookup_value primarily indicates the value that must be needed to be searched in the first row of the table.**table_array-**This table_array or function is used to find out the specified value in that range that needs to be retrieved as well.**row_index_num -**The row_index_num represents the row number where the particular value is present.**Range_lookup -**This is an optional value, either**"True" or "False".**If the value is**"True" or "blank",**it looks for the appropriate value in the table. If the value**"False"**is entered, it looks for an exact match in the table respectively.
## IF FunctionThe IF is a logical function that will return the particular value if the given condition is The syntax for the IF function is:
**Logical_text-**The condition that needs to be checked**Value_if_true-**If the given condition is true, this value is returned**Value_if_false-**If the given condition is false, this value is returned.
## IF ERRORThe IF ERROR is one of the normal functions in Microsoft Excel, where it handles Errors in the formula. If the formula contains an error, it returns an expression or a value of the expression. The syntax of the IFERROR is:
**Value-**The formula or value which needs to be evaluated for errors.**Value_if_error-**This value is returned if the formula returns an error value.
The value_if_error is optional. If this option is not included in the formula, it returns a default error message. If it is included, it displays a message about what the user has entered in the formula. ## TRIMAs the name suggests, the TRIM function removes the extra space in the cell. It removes the space from the start and end of the text, whereas it doesn't remove the space between the texts respectively. The syntax for the TRIM function is,
**Text-**Text which needs to be trimmed out.
## MAX and MINThe MAX function in Microsoft Excel is used to find the maximum number in the given range. The syntax for the MAX function,
Either the number or ranges can be entered in the formula. The MIN function usually finds out the minimum number in the given range as well. The syntax for the MIN function,
## FINDThe FIND function finds out the position of the specified text string which is present inside another text string, and it will return the position of the text string if the formula is correct, or it returns the #VALUE Error. The syntax for the FIND formula,
**Find_text-**The text that needs to be searched.**Within_text-**The text to search within.**Start_num-**It is an optional one that indicates the starting position of the number.
## SumproductThe Sumproduct function multiplies out the elements in the ranges or arrays, will add the products, and returns the result in a single element The syntax for the Sumproduct is:
## UniqueThe unique function is used to return the unique values that are present in the list. Values include text, alphabet, time, date, etc. The syntax for the unique function is:
## SortThe Sort function in Microsoft Excel is used to return the selected data in ascending or descending order. The syntax for the Sort function is:
## ISODD and ISEVENThe ISODD function returns the result as The syntax for the ISODD function:
And the The syntax for the ISEVEN function:
## TEXT BEFOREThe TEXTBEFORE function in Microsoft Excel is used to return the text which occurs before the given data. And the syntax for the TEXTBEFORE function is:
## EXACT and MATCHThe match function returns the position of the lookup value where it is present in a row, column, or table respectively. The syntax for the MATCH function is:
As the name suggests that the EXACT function usually returns the result as TRUE if the compared string is exactly matched, and it will return the result as FALSE if the compared string is not matched. The exact function is case-sensitive as well. The syntax for the EXACT function is:
## REPT functionThe REPT function repeats the given character a specified number of times. The syntax for the REPT function is:
## TransposeThe transpose function primarily changes the orientation of the given array or the range in the given table. If the given range is horizontal, then the range is converted to vertical. And if in case the given range is vertical, then it will be get converted to a horizontal range as well.
## IS NUMBERThe ISNUMBER in Microsoft Excel returns the result as The syntax for the
## DropThe drop function usually returns thesubset of the array in the specified rows as well as the columns. The number of rows and the number of columns that need to be removed in the given data is mentioned in the given formula.The syntax of the Drop function is:
## ABS functionThe ABS function is termed the And the syntax for the ABS function is:
## SummaryThe formula usually plays an important role in the calculation, and in this tutorial, some of the formulas are listed, that are used in Microsoft Excel for calculating the data. By default, it contains enormous formulas based on the data. |