{"id":31960,"date":"2021-02-26T15:53:47","date_gmt":"2021-02-26T14:53:47","guid":{"rendered":"https:\/\/blog.sheetgo.com\/?p=31960"},"modified":"2026-10-30T14:17:47","modified_gmt":"2026-10-30T13:17:47","slug":"les-10-meilleures-fonctions-excel","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/fr\/blog\/excel-functions\/top-10-excel-functions\/","title":{"rendered":"Liste des formules Excel : 78 fonctions essentielles avec leur syntaxe"},"content":{"rendered":"\n[et_pb_section fb_built=”1″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_row _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

This Excel formulas list groups 78 functions by category, then walks through the top 10 Excel functions that you need to learn. With these 10 functions you can unlock the power of Excel. Functions are the driving force behind Excel. They allow you to turn info into insights. Functions in Excel allow you to interpret and alter your data according to the conditions you set.<\/p>\n

However, if you\u2019re an Excel pro, this list isn\u2019t for you. Our list goes over the basics \u2014 stuff everyone needs to know \u2014 and a few new things recently added by Microsoft.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Excel formulas list<\/h2>\n

The tables below list 78 Excel functions by category, with the syntax of each one. Arguments in square brackets are optional. Ten of them, from IF to FILTER, get a full walkthrough with screenshots further down.<\/p>\n

Math and counting functions<\/h3>\n

Add up, average, and count the values in a range.<\/p>\n\n\n
Function<\/th>Syntax<\/th>What it does<\/th><\/tr><\/thead>\n
SUM<\/td>=SUM(number1, [number2], …)<\/td>Adds up a range of values.<\/td><\/tr>\n
AVERAGE<\/td>=AVERAGE(number1, [number2], …)<\/td>Calculates the average of a range of values.<\/td><\/tr>\n
MAX<\/td>=MAX(number1, [number2], …)<\/td>Returns the highest value from a range.<\/td><\/tr>\n
MIN<\/td>=MIN(number1, [number2], …)<\/td>Returns the lowest value from a range.<\/td><\/tr>\n
COUNT<\/td>=COUNT(value1, [value2], …)<\/td>Counts the number of cells containing numbers in a range.<\/td><\/tr>\n
COUNTA<\/td>=COUNTA(value1, [value2], …)<\/td>Counts the cells in a range that are not empty.<\/td><\/tr>\n
COUNTIF<\/td>=COUNTIF(range, criteria)<\/td>Counts the cells in a range that meet one condition.<\/td><\/tr>\n
SUBTOTAL<\/td>=SUBTOTAL(function_num, ref1, [ref2], …)<\/td>Returns a sum, average, count, or other result for a range, picked by a function number.<\/td><\/tr>\n<\/tbody>\n<\/table>\n

Text functions<\/h3>\n

These formulas let you join, extract, and modify text data in your spreadsheets.<\/p>\n\n\n
Function<\/th>Syntax<\/th>What it does<\/th><\/tr><\/thead>\n
CONCATENATE<\/a><\/td>=CONCATENATE(text1, [text2], …)<\/td>Combines two or more text strings into one.<\/td><\/tr>\n
CONCAT<\/td>=CONCAT(text1, [text2], …)<\/td>Does the same as CONCATENATE and also accepts whole ranges.<\/td><\/tr>\n
LEFT<\/td>=LEFT(text, [num_chars])<\/td>Extracts a specified number of characters from the left side of a text string.<\/td><\/tr>\n
RIGHT<\/td>=RIGHT(text, [num_chars])<\/td>Extracts a specified number of characters from the right side of a text string.<\/td><\/tr>\n
MID<\/td>=MID(text, start_num, num_chars)<\/td>Extracts a specified number of characters from a text string, starting at a specific position.<\/td><\/tr>\n
TRIM<\/td>=TRIM(text)<\/td>Removes leading, trailing, and extra spaces within a text string.<\/td><\/tr>\n
UPPER<\/td>=UPPER(text)<\/td>Converts a text string to uppercase.<\/td><\/tr>\n
LOWER<\/td>=LOWER(text)<\/td>Converts a text string to lowercase.<\/td><\/tr>\n
PROPER<\/td>=PROPER(text)<\/td>Converts a text string to proper case, capitalizing the first letter of each word.<\/td><\/tr>\n
SUBSTITUTE<\/td>=SUBSTITUTE(text, old_text, new_text, [instance_num])<\/td>Replaces every occurrence of a specified text string with another, or only the occurrence you pick with instance_num.<\/td><\/tr>\n
REPLACE<\/td>=REPLACE(old_text, start_num, num_chars, new_text)<\/td>Replaces a specified number of characters in a text string with another text string, starting at a specific position.<\/td><\/tr>\n<\/tbody>\n<\/table>\n

Date and time functions<\/h3>\n

These date and time formulas help you work with time-sensitive data.<\/p>\n\n\n
Function<\/th>Syntax<\/th>What it does<\/th><\/tr><\/thead>\n
TODAY<\/td>=TODAY()<\/td>Returns the current date.<\/td><\/tr>\n
NOW<\/td>=NOW()<\/td>Returns the current date and time.<\/td><\/tr>\n
DATE<\/td>=DATE(year, month, day)<\/td>Returns a date based on the specified year, month, and day.<\/td><\/tr>\n
TIME<\/td>=TIME(hour, minute, second)<\/td>Returns a time based on the specified hour, minute, and second.<\/td><\/tr>\n
DATEDIF<\/td>=DATEDIF(start_date, end_date, unit)<\/td>Calculates the difference between two dates in days, months, or years.<\/td><\/tr>\n
EDATE<\/td>=EDATE(start_date, months)<\/td>Returns a date that is a specified number of months before or after a given date.<\/td><\/tr>\n
EOMONTH<\/td>=EOMONTH(start_date, months)<\/td>Returns the last day of the month that is a specified number of months before or after a given date.<\/td><\/tr>\n
DAY<\/td>=DAY(serial_number)<\/td>Returns the day of the month for a given date.<\/td><\/tr>\n
MONTH<\/td>=MONTH(serial_number)<\/td>Returns the month for a given date.<\/td><\/tr>\n
YEAR<\/td>=YEAR(serial_number)<\/td>Returns the year for a given date.<\/td><\/tr>\n
WEEKNUM<\/td>=WEEKNUM(serial_number, [return_type])<\/td>Returns the week number for a given date.<\/td><\/tr>\n
WEEKDAY<\/td>=WEEKDAY(serial_number, [return_type])<\/td>Returns the day of the week for a given date, as a number.<\/td><\/tr>\n<\/tbody>\n<\/table>\n

Logical functions<\/h3>\n

Logical formulas make decisions based on the conditions you set.<\/p>\n\n\n
Function<\/th>Syntax<\/th>What it does<\/th><\/tr><\/thead>\n
IF<\/td>=IF(logical_test, value_if_true, [value_if_false])<\/td>Evaluates a logical condition and returns one value if the condition is true and another value if the condition is false.<\/td><\/tr>\n
AND<\/td>=AND(logical1, [logical2], …)<\/td>Returns TRUE if all specified conditions are true. Otherwise, returns FALSE.<\/td><\/tr>\n
OR<\/td>=OR(logical1, [logical2], …)<\/td>Returns TRUE if at least one specified condition is true. Otherwise, returns FALSE.<\/td><\/tr>\n
NOT<\/td>=NOT(logical)<\/td>Reverses the result of a logical expression, returning TRUE if the expression is FALSE and vice versa.<\/td><\/tr>\n
IFERROR<\/a><\/td>=IFERROR(value, value_if_error)<\/td>Returns a specified value if a formula results in an error. Otherwise, returns the formula’s result.<\/td><\/tr>\n
IFNA<\/td>=IFNA(value, value_if_na)<\/td>Returns a specified value if a formula results in an #N\/A error. Otherwise, returns the formula’s result.<\/td><\/tr>\n
SWITCH<\/td>=SWITCH(expression, value1, result1, [default_or_value2, result2], …)<\/td>Evaluates an expression against a list of values and returns the result for the first matching value, or a default value if no match is found.<\/td><\/tr>\n
IFS<\/td>=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)<\/td>Evaluates multiple conditions and returns the value for the first TRUE condition.<\/td><\/tr>\n<\/tbody>\n<\/table>\n

Lookup and reference functions<\/h3>\n

Lookup and reference formulas find and retrieve information from your spreadsheets. For a more flexible lookup, use MATCH as the row or column argument in INDEX, the INDEX MATCH combination<\/a>.<\/p>\n\n\n
Function<\/th>Syntax<\/th>What it does<\/th><\/tr><\/thead>\n
VLOOKUP<\/td>=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])<\/td>Searches for a value in the first column of a range and returns a value in the same row from another column.<\/td><\/tr>\n
HLOOKUP<\/td>=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])<\/td>Searches for a value in the first row of a range and returns a value in the same column from another row.<\/td><\/tr>\n
XLOOKUP<\/td>=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])<\/td>Finds a value in one range and returns the matching item from another range. Available in Microsoft 365 and Excel 2021 or later.<\/td><\/tr>\n
INDEX<\/td>=INDEX(array, row_num, [column_num])<\/td>Returns a value from a range based on row and column numbers.<\/td><\/tr>\n
MATCH<\/td>=MATCH(lookup_value, lookup_array, [match_type])<\/td>Searches for a value in a range and returns the relative position of the value.<\/td><\/tr>\n
CHOOSE<\/td>=CHOOSE(index_num, value1, [value2], …)<\/td>Selects a value from a list of values based on an index number.<\/td><\/tr>\n
INDIRECT<\/td>=INDIRECT(ref_text, [a1])<\/td>Returns the value of a cell specified by a text string that represents a cell reference.<\/td><\/tr>\n
OFFSET<\/td>=OFFSET(reference, rows, cols, [height], [width])<\/td>Returns a reference to a cell or range that is a specified number of rows and columns away from a starting cell.<\/td><\/tr>\n<\/tbody>\n<\/table>\n

Financial functions<\/h3>\n

These financial formulas can help you calculate present value, future value, loan payments, and more.<\/p>\n\n\n
Function<\/th>Syntax<\/th>What it does<\/th><\/tr><\/thead>\n
PV<\/td>=PV(rate, nper, pmt, [fv], [type])<\/td>Returns the present value of an investment based on periodic, constant payments and a constant interest rate.<\/td><\/tr>\n
FV<\/td>=FV(rate, nper, pmt, [pv], [type])<\/td>Returns the future value of an investment based on periodic, constant payments and a constant interest rate.<\/td><\/tr>\n
PMT<\/a><\/td>=PMT(rate, nper, pv, [fv], [type])<\/td>Calculates the periodic payment for a loan based on constant payments and a constant interest rate.<\/td><\/tr>\n
IPMT<\/td>=IPMT(rate, per, nper, pv, [fv], [type])<\/td>Calculates the interest payment for a specific period of a loan or investment based on constant payments and a constant interest rate.<\/td><\/tr>\n
PPMT<\/td>=PPMT(rate, per, nper, pv, [fv], [type])<\/td>Calculates the principal payment for a specific period of a loan or investment based on constant payments and a constant interest rate.<\/td><\/tr>\n
RATE<\/td>=RATE(nper, pmt, pv, [fv], [type], [guess])<\/td>Returns the interest rate per period of an investment based on constant payments and a constant present value.<\/td><\/tr>\n
NPER<\/td>=NPER(rate, pmt, pv, [fv], [type])<\/td>Returns the number of periods for an investment based on constant payments, a constant interest rate, and a constant present value.<\/td><\/tr>\n
NPV<\/td>=NPV(rate, value1, [value2], …)<\/td>Returns the net present value of an investment based on a series of periodic cash flows and a discount rate.<\/td><\/tr>\n
IRR<\/td>=IRR(values, [guess])<\/td>Returns the internal rate of return for an investment based on a series of periodic cash flows.<\/td><\/tr>\n
SLN<\/td>=SLN(cost, salvage, life)<\/td>Returns the straight-line depreciation of an asset for one period.<\/td><\/tr>\n
SYD<\/td>=SYD(cost, salvage, life, per)<\/td>Returns the sum-of-years’ digits depreciation of an asset for a specified period.<\/td><\/tr>\n
DDB<\/td>=DDB(cost, salvage, life, period, [factor])<\/td>Returns the double-declining balance depreciation of an asset for a specified period.<\/td><\/tr>\n<\/tbody>\n<\/table>\n

Statistical functions<\/h3>\n

Statistical formulas help you analyze and understand your data.<\/p>\n\n\n
Function<\/th>Syntax<\/th>What it does<\/th><\/tr><\/thead>\n
STDEV<\/td>=STDEV(number1, [number2], …)<\/td>Calculates the standard deviation based on a sample (the older version of STDEV.S).<\/td><\/tr>\n
STDEV.P<\/td>=STDEV.P(number1, [number2], …)<\/td>Calculates the standard deviation based on an entire population.<\/td><\/tr>\n
STDEV.S<\/td>=STDEV.S(number1, [number2], …)<\/td>Calculates the standard deviation based on a sample.<\/td><\/tr>\n
VAR<\/td>=VAR(number1, [number2], …)<\/td>Calculates the variance based on a sample (the older version of VAR.S).<\/td><\/tr>\n
VAR.P<\/td>=VAR.P(number1, [number2], …)<\/td>Calculates the variance based on an entire population.<\/td><\/tr>\n
VAR.S<\/td>=VAR.S(number1, [number2], …)<\/td>Calculates the variance based on a sample.<\/td><\/tr>\n
CORREL<\/td>=CORREL(array1, array2)<\/td>Calculates the correlation coefficient between two data sets.<\/td><\/tr>\n
NORM.DIST<\/td>=NORM.DIST(x, mean, standard_dev, cumulative)<\/td>Returns the normal distribution of a value for a specified mean and standard deviation.<\/td><\/tr>\n
NORM.INV<\/td>=NORM.INV(probability, mean, standard_dev)<\/td>Returns the inverse of the normal distribution for a specified probability, mean, and standard deviation.<\/td><\/tr>\n
FORECAST<\/td>=FORECAST(x, known_y’s, known_x’s)<\/td>Calculates a future value based on existing values and a linear trend. Excel 2016 and later also offer it as FORECAST.LINEAR.<\/td><\/tr>\n
TREND<\/td>=TREND(known_y’s, [known_x’s], [new_x’s], [const])<\/td>Returns values along a linear trend fitted to existing values.<\/td><\/tr>\n<\/tbody>\n<\/table>\n

Array and dynamic array functions<\/h3>\n

Array formulas perform calculations on entire ranges of cells rather than individual values. In Microsoft 365 and Excel 2021 or later, their results spill into the neighboring cells on their own. In older versions, you confirm an array formula with Ctrl + Shift + Enter, and FILTER, SORT, UNIQUE, and SEQUENCE are not available.<\/p>\n\n\n
Function<\/th>Syntax<\/th>What it does<\/th><\/tr><\/thead>\n
SUMPRODUCT<\/a><\/td>=SUMPRODUCT(array1, [array2], [array3], …)<\/td>Multiplies corresponding components in the given arrays and returns the sum of those products.<\/td><\/tr>\n
TRANSPOSE<\/td>=TRANSPOSE(array)<\/td>Transposes the rows and columns of a range or array.<\/td><\/tr>\n
FREQUENCY<\/td>=FREQUENCY(data_array, bins_array)<\/td>Returns a frequency distribution as an array: how many values fall within each range you specify.<\/td><\/tr>\n
MMULT<\/td>=MMULT(array1, array2)<\/td>Returns the matrix product of two arrays.<\/td><\/tr>\n
FILTER<\/td>=FILTER(array, include, [if_empty])<\/td>Filters the contents of a range or array based on the criteria you set.<\/td><\/tr>\n
SORT<\/td>=SORT(array, [sort_index], [sort_order], [by_col])<\/td>Sorts the contents of a range or array.<\/td><\/tr>\n
UNIQUE<\/td>=UNIQUE(array, [by_col], [exactly_once])<\/td>Returns the unique values from a range or array.<\/td><\/tr>\n
SEQUENCE<\/td>=SEQUENCE(rows, [columns], [start], [step])<\/td>Returns an array with a specified number of rows and columns, filled with a sequence of numbers.<\/td><\/tr>\n<\/tbody>\n<\/table>[\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Formulas vs functions in Excel<\/h2>\n

A formula is anything you enter in a cell that starts with an equals sign, such as =A2*B2. Formulas can use the plus sign (+) to add, the minus sign (-) to subtract, the asterisk (*) to multiply, and the forward slash (\/) to divide.<\/p>\n

A function is a named calculation built into Excel, such as SUM or VLOOKUP, that takes its arguments in parentheses. Every function you use sits inside a formula, and one formula can combine several functions. For example, =ROUND(AVERAGE(B2:B10), 2) averages a range and rounds the result to two decimal places.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

The 10 Excel functions you will use most<\/h2>\n

Follow along and try these out yourself. The example data set is available here<\/a>! We\u2019ll be assuming the role of a car dealership owner.<\/p>\n

1. IF<\/h3>\n

First of all, let’s start with a simple yet very useful function. The IF function<\/a> allows you to compare two values and receive an output based on the answer.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Syntax<\/h5>[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=IF(logical argument,value if true,value if false)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

In our example, I want to know if the price we\u2019ve listed for each car is greater than the cost we paid for it.<\/p>\n

So our function is =IF(E2>F2,\u201dOK\u201d,\u201dRaise Price\u201d)<\/b>.<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/IF.png” alt=”excel formulas list \u2014 IF formula in a Profit Check column returns OK or Raise Price by comparing the list price of each car with its cost” title_text=”IF formula in a Profit Check column returns OK or Raise Price by comparing the list price of each car with its cost” align=”center” _builder_version=”4.23.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Basically, the =IF function allows you to set values for true and false. Here we\u2019ve set it to read \u201cOK\u201d if our price is higher than our cost and \u201cRaise Price\u201d if we\u2019ll lose money at the current price.<\/p>\n<\/p>Let\u2019s label our new column Profit Check. Include the quote-marks if you want to use a string of letters. Because if you don\u2019t, Excel will think you\u2019re entering a function and it won\u2019t recognize it, creating a #NAME? error for unrecognized text.<\/p>\n

Read more about the IF function<\/a> on our blog.<\/p>[\/et_pb_text][et_pb_text admin_label=”Connections T – One tool – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” global_module=”50458″ global_colors_info=”{}” theme_builder_area=”post_content”]

\"\"<\/p>\n

\"39\"<\/p>\n

One tool to merge, split, and filter all your spreadsheet data<\/span><\/p>\n

Find out how<\/a><\/p>\n[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

2. SUM<\/h3>\n

Next up is the SUM function<\/a>. It does exactly what you\u2019d expect it to do. It sums the items in a specified range.<\/p>\n

It\u2019s usually the first function you learn when you open up Excel. It\u2019s a great stepping stone for understanding how functions work.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Syntax<\/h5>[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=SUM(number1,number2,…)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Remember that mathematics symbols can be inserted to change the function performed from adding to your desired operation.<\/p>\n

In our example, I want to know what our total profit will be if we sell all of our current inventory. This means that my function will be the range of our list price minus the range of our cost, written as =SUM(E2:E25-F2:F25)<\/b>.<\/p>\n

As a result, the SUM function automatically totals the two ranges and subtracts the total cost from the total list price.<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/SUM-1.png” alt=”excel formulas list \u2014 SUM subtracts the total cost from the total list price to show the profit on the car inventory” title_text=”SUM subtracts the total cost from the total list price to show the profit on the car inventory” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

While SUM is simple \u2014 it\u2019s just a basic math equation \u2014 It is a great time saver and shouldn\u2019t be overlooked. It\u2019s a quick way to understand your dataset.\n<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

3. SUBTOTAL<\/h3>\n

Similar to SUM, SUBTOTAL<\/a> performs a function on a group of numbers.<\/p>\n

Its power, however, comes from its modifiers. Depending on the modifier you include, SUBTOTAL can display the average, count, or rage of your data set, among other things.<\/p>\n

The list of modifiers is available on Microsoft’s website<\/a>.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Syntax<\/h5>[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=SUBTOTAL(function modifier,number1,number2,…)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

We want to know the average cost and list price for our inventory.<\/p>\n

So our functions are =SUBTOTAL(101,E2:E25)<\/b> and =SUBTOTAL(101,F2:F25)<\/b>.<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/SUBTOTAL-1.png” alt=”excel formulas list \u2014 SUBTOTAL with function number 101 returns the average cost and the average list price of the inventory” title_text=”SUBTOTAL with function number 101 returns the average cost and the average list price of the inventory” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

When the SUBTOTAL function is used at the bottom of a table, a dropdown menu appears that lets you cycle through each of the operations it performs, making analysis a snap.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

4. CONCAT<\/h3>\n

The CONCAT function<\/a> joins, or concatenates, the inputs. It\u2019s that simple.<\/p>\n

You might have a list of first names and a list of last names, the CONCAT function will create a list of full names for you.[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Syntax<\/h5>[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=CONCAT(input1,input2,…)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

If you want a space between elements, include a space between quote marks in your input list. It will look like this: (input1,\u201d \u201c,Input2).<\/p>\n

We want to make a column with the title of each car to post on our website. Because color, make, and model are all separate cells, we\u2019re going to use the CONCAT function to make one cell with all of that information.<\/p>\n

To do that we\u2019ll write =CONCAT(C2,” “,A2,” “,B2)<\/b>. Make sure to include spaces in quotes between each element.<\/p>\n

Let\u2019s call our new column Sticker Name. Now we have a list of titles that we can hand over to our webmaster.<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/CONCAT-1.png” alt=”excel formulas list \u2014 CONCAT joins color, make, and model into a Sticker Name column” title_text=”CONCAT joins color, make, and model into a Sticker Name column” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

5. COUNTA<\/h3>\n

Similar to some previous Excel functions listed, the COUNTA function<\/a> is another basic, but useful function. It simply counts. COUNTA will total the number of nonempty cells in a range.<\/p>\n

It doesn\u2019t matter if the cell contains a number, a string of letters, or an error, COUNTA will count it. On the other hand, if you only want a count of cells with number values, you can use =COUNT.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Syntax<\/h5>[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=COUNTA(first cell in range:last cell in range)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

If we want to have a running total of our inventory, we can use the COUNTA function for an entire Column.<\/p>\n

To do that, we\u2019ll write =COUNTA(A:A)-1<\/b> off to the side of our table. We include the minus one because the COUNTA function is counting our column label which we want to exclude.<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/COUNTA-1.png” alt=”excel formulas list \u2014 COUNTA counts the filled rows in column A as a running inventory total” title_text=”COUNTA counts the filled rows in column A as a running inventory total” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Because COUNTA only counts cells with something in them, we can count the whole column and only get back the number of rows with information in them.<\/p>\n

Now, when we update our inventory list, our inventory counter will update automatically. Put a label in front of our formula so you remember what the number represents.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

6. COUNTIF<\/h3>\n

COUNTIF is related to COUNTA.<\/p>\n

COUNTIF<\/a> is a conditional counter, which means it only counts cells that fulfill an argument you give it.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Syntax<\/h5>[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=COUNTIF(first cell in range:last cell in range,logical argument)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

In this example, what if we wanted to know how many of the various brands of cars we have in stock. To do that we can use the COUNTIF function. Column A as our range. For the argument, if we put the model name in quotes, COUNTIF will only count the cells whose content matches the brand we\u2019re interested in.<\/p>\n

For example, our Honda counter is written =COUNTIF(A:A,\u201dHonda\u201d)<\/b>. We can create one for each of the models we carry, and make sure to label them.<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/COUNTIF-1.png” alt=”excel formulas list \u2014 COUNTIF counts how many cars of each brand are in stock” title_text=”COUNTIF counts how many cars of each brand are in stock” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Now we have a granular view of our inventory to go along with our general count.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

7. UNIQUE<\/h3>\n

What if we don\u2019t know all of the types of models we have in stock?<\/p>\n

The UNIQUE function<\/a> is useful in that situation. It creates a list of all of the unique values in a data set. It can also be used to make a list of values that only appear once.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Syntax<\/h5>[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=UNIQUE(Array,By Column T\/F, Exactly Once T\/F)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

By default it assumes that our data is organized in rows, and we want a list of the unique values in an array, but you can change that by setting the respective modifiers to true.<\/p>\n

For our car dealership example, the UNIQUE function can replace the manually typed make names. In the first cell we type =UNIQUE(A2:A25)<\/b>. You don\u2019t have to include the modifiers, they\u2019ll automatically be set to false.<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/UNIQUE-1.png” alt=”excel formulas list \u2014 UNIQUE lists each car brand in the inventory once” title_text=”UNIQUE lists each car brand in the inventory once” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Now we\u2019ve confirmed which brands we have in our inventory. Each brand is displayed once in our function\u2019s list.<\/p>\n

At this point we can update our COUNTIF functions by changing the brand name with the cell containing the brand in our new list. Now, anytime we reorder our list, or change a brand name, our inventory counter will update automatically.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

8. SEQUENCE<\/h3>\n

On its surface, the SEQUENCE function<\/a> doesn\u2019t seem like it would be very useful. It only creates a list of numbers.<\/p>\n

But when combined with other functions, it becomes a powerful tool.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Syntax<\/h5>[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=SEQUENCE(number of row, number of columns,starting number, step size)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

The SEQUENCE function assumes, if left blank, that columns, start number, and steps should be set to one.<\/p>\n

Let’s assume we need to give each car in our list a unique identifying number. SEQUENCE can help us out. How can we make our sequence dynamic so that it automatically updates when we add or remove a vehicle from our stock? What if we reference our dynamic inventory counter from earlier to populate our sequence.<\/p>\n

That would look like this: =SEQUENCE(L3)<\/b>. Let\u2019s insert a new column on the left and title it ID Number.\n<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/SEQUENCE-1.png” alt=”excel formulas list \u2014 SEQUENCE fills an ID Number column from the inventory count” title_text=”SEQUENCE fills an ID Number column from the inventory count” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Now each of our vehicles has a unique ID Number, that sets us up nicely for our next function.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

9. XLOOKUP<\/h3>\n

If you\u2019re familiar with Excel you might know the function VLOOKUP. Well, XLOOKUP<\/a> is a new and improved version of VLOOKUP. If you want to read about VLOOKUP, check out our previous blog post here<\/a>.<\/p>\n

XLOOKUP works by matching a source to a cell in an array and returning information from a parallel array.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Syntax<\/h5>[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=XLOOKUP(Lookup value,lookup array, return array,error message)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Back at the car dealership, let’s use XLOOKUP to find the price of a vehicle based on its ID Number. To do that we\u2019ll write the ID Number we want to find in the cell above our XLOOKUP function.<\/p>\n

That way we can reference the cell in our formula and easily change the ID Number we\u2019re looking for. Our formula will be =XLOOKUP(L11,A:A,F:F)<\/b>.<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/XLOOKUP-1.png” alt=”excel formulas list \u2014 XLOOKUP returns the list price for the ID Number typed in a cell” title_text=”XLOOKUP returns the list price for the ID Number typed in a cell” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

If you go in and change the ID Number, the List Price will update automatically. This is a great tool for large datasets. Especially if everything has an ID or serial number.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

10. FILTER<\/h3>\n

Now lastly, my favorite function, FILTER<\/a>.<\/p>\n

The FILTER function is like combining XLOOKUP and IF functions. It displays results based on conditions you provide.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Syntax<\/h5>[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=FILTER(Array,Include If,error message)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Searching our inventory for certain colors becomes easy with the FILTER function. We\u2019ll write the color we\u2019re interested in first so we can reference it in our function.<\/p>\n

In the cell below that we\u2019ll write our function, =FILTER(I2:I25,D2:D25=L14)<\/b>. We\u2019re just pulling up the Sticker name in this example, but you can have it display as large of an array as you\u2019d like.<\/p>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/FILTER-2.png” alt=”excel formulas list \u2014 FILTER lists the sticker names of the cars in the color typed in a cell” title_text=”FILTER lists the sticker names of the cars in the color typed in a cell” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Now you can type any color and pull up available models in that color. A great tool for salespeople who wanted to tell a client what is available in the color they want.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]

Final thoughts<\/h2>\n

With just these 10 functions we\u2019ve created a handy little spreadsheet, saving our small business time. This isn\u2019t a comprehensive list. Many more powerful Excel formulas exist.<\/p>\n

To read up on some of them, check out the rest of our blog<\/a>.<\/p>\n

When the same Excel files need combining or updating every week, Sheetgo<\/a>, an AI-powered data automation platform, can connect them and refresh the results on a schedule.<\/p>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]\n","protected":false},"excerpt":{"rendered":"

This Excel formulas list groups 78 functions by category, then walks through the top 10 Excel functions that you need to learn. With these 10 functions you can unlock the power of Excel. Functions are the driving force behind Excel. They allow you to turn info into insights. Functions in Excel allow you to interpret […]<\/p>\n","protected":false},"author":40,"featured_media":31961,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[65],"tags":[39,48,28],"class_list":["post-31960","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-functions","tag-connections-t","tag-excel","tag-spreadsheets"],"acf":[],"permalink_manager":null,"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/31960","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=31960"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/31960\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/31961"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=31960"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=31960"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=31960"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}