This is the ‘Apply Excel Formula and Excel Functions’ tutorial of the Microsoft Excel 2013 MOS foundation course, offered by Simplilearn. In this tutorial, we will be learning various excel functions and excel formulas that we often come across while working with Excel.
By the end of this tutorial, we will be able to:
A Formula is defined as an equation that performs calculations and returns the result value in cells. It contains several functions, cell references, operators on functions. A formula can be simple or complex based on users needs.
A Function can be defined as a built-in operation in Excel that returns a result value in cells. A function contains only cell references and values. Functions are tied alongside parenthesis were in the arguments, if any, are listed.
Formulas and Functions are similar, and they return results in cells. The only difference between a formula and function is that while formulas can be changed based on users needs, functions cannot.
A Cell Reference is defined as a cell or a cell range in a worksheet that can be used in a formula. This helps Excel too easily, find the data or values that need to be processed by a formula. There are three types of references in Excel 2013:
Types | Functions |
Relative Reference |
By default, each formula in Excel contains a relative reference. When we use a formula with a relative cell reference and copy the same formula into another cell, the formula reference changes. Usually, the relative reference will be identified as A1. If we want a formula cell reference to change automatically, we need to use relative cell reference. |
Absolute Reference or Fixed Reference |
If a cell reference is an absolute reference and formula is copied to another cell, the cell reference will not change. Usually, the absolute reference is identified as $A$1. If we want a formula to have a constant cell reference, we need to use a dollar sign before the column name and the Row number. |
Mixed Reference |
If we want to fix either a column or a row, then we use a mixed reference. We can fix a column or a row by adding a dollar sign before it. Mixed Reference is useful when a column needs to be fixed on a road needs to change, or when a road needs to be fixed or a column, needs to change. In other words, it can be described as a combination of absolute reference and a relative reference. |
Using the correct type of cell reference in formulas ensures that they work as expected when copied to another location in the worksheet. Formulas and Functions are useful when using references. By default, cell addresses are dynamic in Excel 2013. It is possible to reference cells in other worksheets within a workbook or other workbooks.
Business Scenario:
Simplilearn has given a raise to all its employees. John’s manager has assigned him the task of calculating the revised net earnings for each employee after deducting taxes. He has asked John to add both the ‘tax amount’ and the ‘net earnings’ data to the earnings data sheet. John wants to do this using cell references in Excel 2013.
Steps Performed in the above example:
Generally, every formula in mathematics uses the BODMAS rule. But in Excel 2013, we have the PEDMAS or BEDMAS rule.
BODMAS checks for brackets, orders, power, square roots division, multiplication, addition, and subtraction in the given order.
BEDMAS or PEDMAS checks for bracket or parenthesis, exponents, division, multiplication, addition, and subtraction in given order.
The only difference between BODMAS and BEDMAS is that while using BEDMAS in Excel, there are no orders on the formula. The calculation order is as below:
Learn more about Excel Function and Excel Formulas. Click to know more!
In Excel 2013, the sum function allows us to add values in a data range or add specific cell values up to 255 cells.
Syntax: =SUM(Value1, value 2,......, value 255) or =Sum(select range of cells).
The sum function is a built-in Excel function that ignores text values empty cells, and logical values. The shortcut key to writing the sum of a function for a data range is ALT+. When the sum function is used for a data range, any error values or arguments that are in text form are displayed as an error.
Business Scenario:
Simplilearn is about to start its annual financial review. John’s manager wants to know the total cost to the company from employee salary. John has the ‘individual employees’ gross earnings’ and wants to calculate the total using the SUM function in Excel 2013.
Steps Performed in the above example:
In Excel 2013, the count function is used to count the number of cells containing numbers in a data range, or up to 255 cells. While using the count function Arguments, numbers, date or text representation of the numbers are counted.
Syntax: =COUNT(value1, value 2,......, value 255) or =COUNT(select range of cells).
Arguments that give error values or text, that cannot be translated into numbers are not counted.
In Excel 2013, the COUNTA function is used to count the number of cells containing text, number and alphanumeric values in a given range, or up to 255 cells.
Syntax: =COUNTA(Value1, value 2,......, value 255) or =COUNTA(select range of cells).
COUNTA function will count even if a cell contains space, but it gives logical and error values.
Business Scenario
New employees have joined Simplilearn. John has added their data to the employee earnings sheet. John’s manager wants him to include the total number of salaried employees in the sheet. John wants to make use of the COUNT function in Excel 2013 to do this. John also notices that some employees haven’t been assigned their employee codes and wants to get a count of this using the COUNTA function.
Steps Performed in the above example:
The average function returns the average of a selected data range or up to 255 cells. The average function ignores text values, logical values, and empty cells.
Syntax: =AVERAGE(Value1, value 2,......, value 255) or =AVERAGE(select range of cells). Generally, the average function adds the number and then divides the result by the count of numbers.
In Excel 2013, the MIN function returns the minimum (lowest) value in the data range or up to 255 cell values. The MIN function ignores empty cells, text values, and logical values.
Syntax: =MIN(Value1, value 2,......, value 255) or =MIN(select range of cells)
In Excel 2013, the MAX function returns the maximum (highest) value in the data range or up to 255 cell values. The MAX function ignores empty cells, text values, and logical values.
Syntax: =MAX(Value1, value 2,......, value 255) or =MAX(select range of cells)
Business Scenario
The HR manager requires the employee earnings data and has assigned John the task of providing the median earnings and the minimum and maximum earnings of employees. John wants to use the AVERAGE, MINIMUM, AND MAXIMUM functions in Excel 2013 to complete this task.
Steps Performed in the above example:
Conditional logic is generally used in data summary functions.
The SUMIF function is used to sum a data range by using a criterion condition. For example, if we want to sum the earnings of a department, we can use the SUMIF function. The following arguments are required in the SUMIF function.
Syntax: =SUMIF(criteria range, criteria, sum range)
The SUMIFS function is used to sum a data range by using multiple criteria. For example, if we want to add the earnings of a department, region wise, we can use the SUMIFS function. This function will sum the cell values only if the conditions are met. The following arguments are used in the SUMIFS function.
Syntax: =SUMIFS(sum range, criteria range1, criteria1, [criteria range2], [criteria2],……, [criteria range127], [criteria127])
Business Scenario
Simplilearn is conducting its annual financial review. John’s manager has asked him to provide the department-wise expenditure on employee salaries and also the region-wise break-up of employee salaries for the Sales department. John wants to calculate the department-wise expenditure using the SUMIF function in Excel 2013. For including the region-wise employee earnings of the Sales department, he wants to use the SUMIFS function.
Steps Performed in the above example:
The COUNTIF function is used to count the cells that contained data in a certain data range by using a criterion. For example, if we want to count the number of rows or records that contained the earnings of a department, we can use the COUNTIF function. The following arguments are used in COUNTIF function.
Syntax: COUNTIF(criteria range, criteria)
The COUNTIFS function is used to count the number of cells that contain the values of a data range by using multiple criteria conditions. For example, if we want to count the rows or records of earnings of a department region wise, we can use the COUNTIFS function. This function counts cells if all the conditions are met and also allows us to select up to 127 criteria.
The following arguments are used in the COUNTIFS function:
We can use wildcard characters like an asterisk, question mark, and the tilde symbol.
Syntax: =COUNTIFS(criteria range1, criteria1, [criteria range2], [c,riteria2],…….., [criteria range127], [criteria127])
Business Scenarios:
Simplilearn is conducting its annual financial review. John has the employee earnings data for the Production and the Sales (South Region) departments. John’s manager has asked him to include the number of employees in each category in the same data sheet. John wants to use the COUNTIF and COUNTIFS functions for completing this task.
Steps Performed in the above example:
The AVERAGEIF function is used to get the average of a data range by using a criterion condition. For example, if we want to return the average earnings of a department, we can use the AVERAGEIF function. The following arguments are used in the AVERAGEIF function.
Syntax: =AVERAGEIF(criteria range, criteria, average range)
The AVERAGEIFS function is used to find the average of a data range by using multiple criteria. For example, if we want to return the average of the earnings of a department region wise, we can use the AVERAGEIFS function and this function will return the average of the cell values if all conditions are met. We can select up to 127 criteria.
The following arguments are used in the AVERAGEIFS function.
Average Range: In AVERAGEIF, the average range is selected as the last argument, but in AVERAGEIFS it is selected first because, in the AVERAGEIFS function, up to 127 criteria can be added.
Syntax: =AVERAGEIFS(average range, criteria range1, criteria1, [criteria range2], [criteria2],…….., [criteria range127], [criteria127])
Business Scenario
The HR at Simplilearn is planning to revise the pay scales of its employees. The HR manager has asked John to provide the department-wise average earnings data. She has also asked for a region-wise average of employee earnings for the Sales department. John wants to explore the AVERAGEIF and AVERAGEIFS functions in Excel 2013.
Steps Performed in the above example:
In Excel 2013, there are different text functions to format a modified data based on users needs.
Some basic data functions are:
Syntax: =CONCATENATE(text1, text2, ……, text255)
While using CONCATENATE function, we can insert space or any symbol between two words or just using the symbol with double quotes. To CONCATENATE two or more strings use the ampersand(&) symbol between two words.
Excited to explore Excel formulas and Excel functions in detail. Get the course preview here!
When there is extra or unwanted space between words in our data, we can use the TRIM function to remove them. Only one argument is required for this function.
Text: Select a cell value as the text argument.
Syntax: =TRIM(text)
Business Scenario
Simplilearn is planning to organize a Sports Week for its employees and has asked the interested people to fill in their details on an Excel sheet. The text entries in the participant list are in different cases. John has been asked to edit the entries and remove the extra spaces. For this, he wants to use the LOWER, UPPER, CONCATENATE, PROPER, and TRIM functions.
Steps Performed in the above example:
When we need to divide a part of a text from the beginning of the text, we can use the LEFT function. It needs two function argument.
Syntax: =LEFT(text, number of characters)
The left function is generally used to get the first name from a full name or to derive the user id from an email id without a domain.
The RIGHT function is used to divide a part of a text from the end of the text. It needs two function arguments.
Syntax: =RIGHT(text, number of characters)
The Right function is used to get the last name from a full name.
The Mid function is used to extract a string of characters from the middle of the text string that can start at any position in Excel 2013. Following arguments are required in the MID function:
Syntax: =MID(text, start number, number of characters)
Business Scenario
Simplilearn is planning to reorganize its branches and change the employee code format being used. John’s manager has assigned him the task of extracting the branch numbers from the employee code of each employee and the last four digits of their account numbers. John also has to indicate the employees’ departments with initials, such as P for Production. John wants to use the LEFT, RIGHT, and MID functions in Excel 2013 to do this.
Steps Performed in the above example:
Let us summarize what we've learned in this lesson.
This concludes ‘Apply Functions and Formulas” tutorial.
A Simplilearn representative will get back to you in one business day.