The 10 Excel functions to use in 2026<\/h3>\n1. IF<\/h4>\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”] 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=”top Excel functions 1. IF” title_text=”IF” 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”] 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”] 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”] 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=”best excel functions 2. SUM” title_text=”SUM (1)” 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”] 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”] 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=”top Excel functions 3. SUBTOTAL” title_text=”SUBTOTAL (1)” 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”] 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”] 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=”top Excel functions 4. CONCAT” title_text=”CONCAT (1)” 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”] 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”] 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=”top Excel functions 5. COUNTA” title_text=”COUNTA (1)” 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”] 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”] 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=”top Excel functions 6. COUNTIF” title_text=”COUNTIF (1)” 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”] 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”] 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=”top Excel functions 7. UNIQUE” title_text=”UNIQUE (1)” 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”] 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”] 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=”top Excel functions 8. SEQUENCE” title_text=”SEQUENCE (1)” 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”] 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”] 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=”top Excel functions 9. XLOOKUP” title_text=”XLOOKUP (1)” 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”]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”]
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/connections-top-processors-main.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\n2. SUM<\/h4>\n
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”]
3. SUBTOTAL<\/h4>\n
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”]
4. CONCAT<\/h4>\n
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”]
5. COUNTA<\/h4>\n
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”]
6. COUNTIF<\/h4>\n
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”]
7. UNIQUE<\/h4>\n
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”]
8. SEQUENCE<\/h4>\n
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”]
9. XLOOKUP<\/h4>\n
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”]
10. FILTER<\/h4>\n