The Definitive Guide to Developer Tab in Excel - Activate and Use

Developer Tab

SUM function

 The SUM function adds values. You can add individual values, cell references or ranges or a mix of all three.

For example:

• =SUM(A2:A10) Adds the values in cells A2:10.

• =SUM(A2:A10, C2:C10) Adds the values in cells A2:10, as well as cells C2:C10.

SUM(number1,[number2],...)

Argument name	Description
number1    Required	The first number you want to add. The number can be like 4, a cell reference like B6, or a cell range like B2:B8.
number2-255    Optional	This is the second number you want to add. You can specify up to 255 numbers in this way.

This section will discuss some best practices for working with the SUM function. Much of this can be applied to working with other functions as well.

The =1+2 or =A+B Method – While you can enter =1+2+3 or =A1+B1+C2 and get fully accurate results, these methods are error prone for several reasons:

1. Typos – Imagine trying to enter more and/or much larger values like this:

   • =14598.93+65437.90+78496.23

   Then try to validate that your entries are correct. It’s much easier to put these values in individual cells and use a SUM formula. In addition, you can format the values when they’re in cells, making them much more readable then when they’re in a formula.

   

2. #VALUE! errors from referencing text instead of numbers

   If you use a formula like:

   • =A1+B1+C1 or =A1+A2+A3

   

   Your formula can break if there are any non-numeric (text) values in the referenced cells, which will return a #VALUE! error. SUM will ignore text values and give you the sum of just the numeric values.

   

3. #REF! error from deleting rows or columns

   

   If you delete a row or column, the formula will not update to exclude the deleted row and it will return a #REF! error, where a SUM function will automatically update.

   

4. Formulas won't update references when inserting rows or columns

   

   If you insert a row or column, the formula will not update to include the added row, where a SUM function will automatically update (as long as you’re not outside of the range referenced in the formula). This is especially important if you expect your formula to update and it doesn’t, as it will leave you with incomplete results that you might not catch.

   

5. SUM with individual Cell References vs. Ranges

   Using a formula like:

   • =SUM(A1,A2,A3,B1,B2,B3)

   Is equally error prone when inserting or deleting rows within the referenced range for the same reasons. It’s much better to use individual ranges, like:

   • =SUM(A1:A3,B1:B3)

   Which will update when adding or deleting rows.

VLOOKUP

 Use VLOOKUP when you need to find things in a table or range by row.

1. Select a cell.

2. Type =VLOOKUP( and then select the value to lookup.

3. Type a comma (,) and select the range or table to look for the value.

4. Type a comma (,) and the number of the column where the lookup value is located.

5. Type ,FALSE) to find an exact match.

6. Press Enter.

The formula for the video example is as follows:

=VLOOKUP(A7, A2:B5, 2, FALSE).

In its simplest form, the VLOOKUP function says:

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, optionally specify TRUE for approximate match or FALSE for an exact match).

XLOOKUP function

 Use the XLOOKUP function to find things in a table or range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.

Syntax

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. 

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

Argument	Description
lookup_value Required*	The value to search for *If omitted, XLOOKUP returns blank cells it finds in lookup_array.
lookup_array Required	The array or range to search
return_array Required	The array or range to return
[if_not_found] Optional	Where a valid match is not found, return the [if_not_found] text you supply. If a valid match is not found, and [if_not_found] is missing, #N/A is returned.
[match_mode] Optional	Specify the match type: 0 - Exact match. If none found, return #N/A. This is the default. -1 - Exact match. If none found, return the next smaller item. 1 - Exact match. If none found, return the next larger item. 2 - A wildcard match where *, ?, and ~ have special meaning.
[search_mode] Optional	Specify the search mode to use: 1 - Perform a search starting at the first item. This is the default. -1 - Perform a reverse search starting at the last item. 2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. -2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

Examples

Example 1    uses XLOOKUP to look up a country name in a range, and then return its telephone country code. It includes the lookup_value (cell F2), lookup_array (range B2:B11), and return_array (range D2:D11) arguments. It doesn't include the match_mode argument, as XLOOKUP produces an exact match by default.

Note: XLOOKUP uses a lookup array and a return array, whereas VLOOKUP uses a single table array followed by a column index number. The equivalent VLOOKUP formula in this case would be: =VLOOKUP(F2,B2:D11,3,FALSE)

———————————————————————————

Example 2    looks up employee information based on an employee ID number. Unlike VLOOKUP, XLOOKUP can return an array with multiple items, so a single formula can return both employee name and department from cells C5:D14.

———————————————————————————

Example 3    adds an if_not_found argument to the preceding example.

———————————————————————————

Example 4    looks in column C for the personal income entered in cell E2, and finds a matching tax rate in column B. It sets the if_not_found argument to return 0 (zero) if nothing is found. The match_mode argument is set to 1, which means the function will look for an exact match, and if it can't find one, it returns the next larger item. Finally, the search_mode argument is set to 1, which means the function will search from the first item to the last.

Note: XARRAY's lookup_array column is to the right of the return_array column, whereas VLOOKUP can only look from left-to-right.

———————————————————————————

Example 5    uses a nested XLOOKUP function to perform both a vertical and horizontal match. It first looks for Gross Profit in column B, then looks for Qtr1 in the top row of the table (range C5:F5), and finally returns the value at the intersection of the two. This is similar to using the INDEX and MATCH functions together.

Tip: You can also use XLOOKUP to replace the HLOOKUP function.

Note: The formula in cells D3:F3 is: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)).

———————————————————————————

Example 6    uses the SUM function, and two nested XLOOKUP functions, to sum all the values between two ranges. In this case, we want to sum the values for grapes, bananas, and include pears, which are between the two.

The formula in cell E3 is: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

How does it work? XLOOKUP returns a range, so when it calculates, the formula ends up looking like this: =SUM($E$7:$E$9). You can see how this works on your own by selecting a cell with an XLOOKUP formula similar to this one, then select Formulas > Formula Auditing > Evaluate Formula, and then select Evaluate to step through the calculation.

Use AutoSum to sum numbers

 If you need to sum a column or row of numbers, let Excel do the math for you. Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you’re done.

When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.

Here’s an example. To add the January numbers in this Entertainment budget, select cell B7, the cell immediately below the column of numbers. Then click AutoSum. A formula appears in cell B7, and Excel highlights the cells you’re totaling.

Press Enter to display the result (95.94) in cell B7. You can also see the formula in the formula bar at the top of the Excel window.

Notes: 

• To sum a column of numbers, select the cell immediately below the last number in the column. To sum a row of numbers, select the cell immediately to the right.

• AutoSum is in two locations: Home > AutoSum, and Formulas > AutoSum.

• Once you create a formula, you can copy it to other cells instead of typing it over and over. For example, if you copy the formula in cell B7 to cell C7, the formula in C7 automatically adjusts to the new location and calculates the numbers in C3:C6.

• You can also use AutoSum on more than one cell at a time. For example, you could highlight both cell B7 and C7, click AutoSum, and total both columns at the same time.