Download Excel formulas and keys and more Study notes MS Microsoft Excel skills in PDF only on Docsity!
Excel Formulas & Functions There are plenty of Excel formulas and functions depending on what kind of operation you want to perform on the dataset.
1. SUM The SUM() function gives the total of the selected range of cell values. It performs the mathematical operation which is addition. Here’s an example of it below =SUM(C2:C4) 2. AVERAGE The AVERAGE () function focuses on calculating the average of the selected range of cell values. =AVERAGE(C2, C3, C4) 3. COUNT The function COUNT() counts the total number of cells in a range that contains a number. It does not include the cell, which is blank, and the ones that hold data in any other format apart from numeric. =COUNT(C1:C4) To count the number of blank cells present in a range of cells, COUNTBLANK() is used.
4. SUBTOTAL The SUBTOTAL() function returns the subtotal in a database. Depending on what you want, you can select either average, count, sum, max, min =SUBTOTAL(1, A2: A4) In the subtotal list “1” refers to average. Hence, the above function will give the average of A2: A4 and the answer to it is 11, which is stored in C5. Similarly,=SUBTOTAL(4, A2: A4) This selects the cell with the maximum value from A2 to A4, which is 12. Incorporating “4” in the function provides the maximum result.
- MODULUS The MOD() function works on returning the remainder when a particular number is divided by a divisor. In the first example, we have divided 10 by 3. The remainder is calculated using the function. =MOD(A2,3). We can also directly type =MOD(10,3)”as it will give the same answer. 6. POWER The function “Power()” returns the result of a number raised to a certain power.
9. LEN The function LEN() returns the total number of characters in a string. So, it will count the overall characters, including spaces and special characters. 10. REPLACE As the name suggests, the REPLACE() function works on replacing the part of a text string with a different text string. The syntax is “=REPLACE(old_text, start_num, num_chars, new_text)”. Here, start_num refers to the index position you want to start replacing the characters with. Next, num_chars indicate the number of characters you want to replace. Here, we are replacing A101 with B101 by typing =REPLACE(A15,1,1,"B") Next, we are replacing A102 with A2102 by typing: “=REPLACE(A16,1,1, "A2")” Finally, we are replacing Adam with Saam by typing: “=REPLACE(A17,1,2, "Sa")”
11 SUBSTITUTE The SUBSTITUTE() function replaces the existing text with a new text in a text string. The syntax is “=SUBSTITUTE(text, old_text, new_text, [instance_num])”. Here, [instance_num] refers to the index position of the present texts more than once. Here, we are substituting “I like” with “He likes” by typing: “=SUBSTITUTE(A20, "I like","He likes")” Now, we are replacing both the 2010s in the original text with 2016 by typing “=SUBSTITUTE(A22,2010,2016)”. 12. LEFT, RIGHT, MID The LEFT() function gives the number of characters from the start of a text string. Meanwhile, the MID() function returns the characters from the middle of a text string, given a starting position and length. Finally, the right() function returns the number of characters from the end of a text string. In the example below, we use the function left to obtain the leftmost word on the sentence in cell A5.
14. NOW() The NOW() function in Excel gives the current system date and time. The result of the NOW() function will change based on your system date and time.
15. TODAY() The TODAY() function in Excel provides the current system date. The function DAY() is used to return the day of the month. It will be a number between 1 to
- 1 is the first day of the month, 31 is the last day of the month. The MONTH() function returns the month, a number from 1 to 12, where 1 is January and 12 is December. The YEAR() function, as the name suggests, returns the year from a date value. 1 6. TIME() The TIME() function converts hours, minutes, seconds given as numbers to an Excel serial number, formatted with a time format.
The HOUR() function generates the hour from a time value as a number from 0 to 23. Here, 0 means 12 AM and 23 is 11 PM. The function MINUTE(), returns the minute from a time value as a number from 0 to 59. The SECOND() function returns the second from a time value as a number from 0 to 59. 1 7. DATEDIF The DATEDIF() function provides the difference between two dates in terms of years, months, or days. LOOKUP functions The Microsoft Excel LOOKUP function is one of the most popular excel functions. It returns a value from a range (one row or one column) or an array. We can use it as a worksheet function (WS) in Excel. Lookup functions in Excel mean referencing a cell to match values in another row or column against the cell and thereby retrieving the corresponding results from the respective rows and columns. Uses of LOOKUP functions are: You can find the exact or appropriate match by using the lookup function. Users can search for data both vertically (columns) and horizontally (rows). It is simpler to use and does not require selecting the entire table.
19. HLOOKUP Similar to VLOOKUP, we have another function called HLOOKUP() or horizontal lookup. The function HLOOKUP looks for a value in the top row of a table or array of benefits. It gives the value in the same column from a row you specify. Below are the arguments for the HLOOKUP function: lookup_value - This indicates the value to lookup. table - This is the table from which you have to retrieve data. row_index - This is the row number from which to retrieve data. range_lookup - [optional] This is a boolean to indicate an exact match or approximate match. The default value is TRUE, meaning an approximate match. Given the below table, let’s see how you can find the city of Jenson using HLOOKUP. Here, H23 has the lookup value, i.e., Jenson, G1:M5 is the table array, 4 is the row index number, 0 is for an approximate match. Once you hit enter, it will return “New York”. 2 0. IF Formula The IF() function checks a given condition and returns a particular value if it is TRUE. It will return another value if the condition is FALSE. In the below example, we want
to check if the value in cell A2 is greater than 5. If it’s greater than 5, the function will return “Yes 4 is greater”, else it will return “No”. In this case, it will return ‘No’ since 4 is not greater than 5. ‘IFERROR’ is another function that is popularly used. This function returns a value if an expression evaluates to an error, or else it will return the value of the expression. Suppose you want to divide 10 by 0. This is an invalid expression, as you can’t divide a number by zero. It will result in an error. 2 1. INDEX-MATCH The INDEX-MATCH function is used to return a value in a column to the left. With VLOOKUP, you're stuck returning an appraisal from a column to the right. Another reason to use index-match instead of VLOOKUP is that VLOOKUP needs more processing power from Excel. This is because it needs to evaluate the entire table array which you've selected. With INDEX-MATCH, Excel only has to consider the lookup column and the return column. Using the below table, let’s see how you can find the city where Jenson resides.
By using the IFERROR function, you can add a message if the formula evaluates to an error.
Logical Functions
IF Function The IF function is the most basic logical function in Excel. It tests a condition and returns one value if the condition is true, and another value if the condition is false. The syntax for the IF function is IF(logical_test, value_if_true, value_if_false). AND, OR, and NOT Functions The AND, OR, and NOT functions in Excel are used to perform more complex logical tests. The AND function returns true if all conditions are met, the OR function returns true if any of the conditions are met, and the NOT function reverses the result of a logical test. The IF function can be nested within another IF function to create a nested IF function. This allows you to test multiple conditions and return different values based on the results of these tests. Example: IF(A1>1000, “High”, IF(A1>500, “Medium”, “Low”)). IFS Function The IFS function is a premade function in Excel, which returns values based on one or more true or false conditions. It is typed =IFS and has two or more parts: =IFS( logical_test1 , value_if_true1 , [logical_test2, value_if_true2] , [logical_test3; ...) SWITCH function T he Excel SWITCH function compares one value against a list of values, and returns a result corresponding to the first match found. When no match is found, SWITCH can return an optional default value. Syntax is : =SWITCH(expression, value1, result1, [value2, result2, …], [default])
The INDEX function in Excel is very powerful at the same time a flexible tool that retrieves the value at a given location in a range. In another words, It returns the content of a cell, specified by row and column offset. INDEX Function Syntax in Excel: =INDEX(reference, [row], [column]) Parameters: array : The data range where the lookup is performed. row_num : The row number of the value to retrieve. column_num (optional): The column number of the value to retrieve. The MATCH function in Excel is used to locate the position of a specific value within a row, column, or array. Unlike VLOOKUP or HLOOKUP, the MATCH function does not return the actual data but instead provides the relative position of the value. It is case-insensitive and works seamlessly in both horizontal and vertical ranges. The INDEX MATCH formula is the combination of two functions in Excel: INDEX and MATCH: INDEX finds a cell’s value in a table using its column and row number MATCH finds the position of a cell in a row or column Together, they can find and give you a cell’s value in a table by looking up both up and down, and left and right. This is known as the Index and Match formula. Array Functions and formulas Array in Excel An Array in Excel is the structure that holds a collection of data. These data can be in the form of numbers or texts. There could be an array of 1-D or 2-D as well in Excel. We can directly enter it into the sheet by selecting the cells where you want to enter the data and then writing the array preceded by an “=” sign. After these press Ctrl+Shift+Enter. How to insert an Array Formula? Before entering the Array formula fir of all we understand some important points about the Array formula: After typing the formula you have to press keys CTRL+SHIFT+ENTER together. It will automatically change the normal formula into an array formula.
IMPORTRANGE function is one of the text and data functions. It allows you to pull data from one spreadsheet into another using a simple formula. It is particularly useful for collaborative projects, data analysis, and creating dashboards that require data from various sources. The syntax of the IMPORTRANGE function is IMPORTRANGE (“spreadsheet_url”, “range_string”) Where the arguments correspond to:spreadsheet_url: The URL of the spreadsheet the data is imported from. It should be enclosed in quotation marks. range_string: The range of cells you want to import. Also enclosed in quotation marks.
