Ken Bluttman

Cheat Sheet / Updated 01-27-2022

It’s easy to use Excel for many of your day-to-day number-crunching tasks, like determining your business’s average sale, computing classroom grades, or forecasting college expenses. Use this handy Cheat Sheet to discover great functions and tips to help you get the most out of Excel.

Article / Updated 01-07-2022

Despite all the functions provided by Excel, you may need one that you just don't see offered. Excel lets you create your own functions by using VBA programming code; your functions show up in the Insert Function dialog box. Writing VBA code is not for everyone. But nonetheless, here is a short-and-sweet example. If you can conquer this, you may want to find out more about programming VBA. Who knows — maybe one day you'll be churning out sophisticated functions of your own! Make sure you are working in a macro-enabled workbook (one of the Excel file types). Follow along to create custom functions: Press Alt + F11. This gets you to the Visual Basic Editor, where VBA is written. You can also click the Visual Basic button on the Developer tab of the Ribbon. The Developer tab is visible only if the Developer checkbox is checked on the Customize Ribbon tab of the Excel Options dialog box. Choose Insert→Module in the editor. You have an empty code module sitting in front of you. Now it's time to create your very own function! Type this programming code, shown in the following figure: Writing your own function Public Function Add(number1 As Double, number2 As Double) Add = number1 + number2 End Function Save the function. Macros and VBA programming can be saved only in a macro-enabled workbook. After you type the first line and press Enter, the last one appears automatically. This example function adds two numbers, and the word Public lists the function in the Insert Function dialog box. You may have to find the Excel workbook on the Windows taskbar because the Visual Basic Editor runs as a separate program. Or press Alt+ F11 to toggle back to the Workbook. Return to Excel. Click the Insert Function button on the Formulas tab to display the Insert Function dialog box. Finding the function in the User Defined category Click OK. The Function Arguments dialog box opens, ready to receive the arguments. Isn't this incredible? It's as though you are creating an extension to Excel, and in essence, you are. Using the custom Add function This is a very basic example of what you can do by writing your own function. The possibilities are endless, but of course, you need to know how to program VBA. Macro-enabled workbooks have the file extension .xlsm.

Article / Updated 01-07-2022

Most Excel functions take inputs — called arguments or parameters — that specify the data the function is to use. Some functions take no arguments, some take one, and others take many; it all depends on the function. The argument list is always enclosed in parentheses following the function name. If there's more than one argument, the arguments are separated by commas. Look at a few examples: Function Comment =NOW() Takes no arguments. =AVERAGE(A6,A11,B7) Can take up to 255 arguments. Here, three cell references are included as arguments. The arguments are separated by commas. =AVERAGE(A6:A10,A13:A19,A23:A29) In this example, the arguments are range references instead of cell references. The arguments are separated by commas. =IPMT(B5, B6, B7, B8) Requires four arguments. Commas separate the arguments. Some functions have required arguments and optional arguments. You must provide the required ones. The optional ones are, well, optional. But you may want to include them if their presence helps the function return the value you need. The IPMT function is a good example. Four arguments are required, and two more are optional.

Article / Updated 01-07-2022

Excel makes entering functions with the Insert Function dialog box easy. But what do you do when you need to change a function that has already been entered in a cell? What about adding arguments or taking some away? There is an easy way to do this! Follow these steps: Click the cell with the existing function. Click the Insert Function button. The Function Argument dialog box appears. This dialog box is already set to work with your function. In fact, the arguments that have already been entered in the function are displayed in the dialog box as well! Add, edit, or delete arguments, as follows: To add an argument (if the function allows), use the RefEdit control to pick up the extra values from the worksheet. Alternatively, if you click the bottom argument reference, a new box opens below it, and you can enter a value or range in that box. To edit an argument, simply click it and change it. To delete an argument, click it and press the Backspace key. Click OK when you're finished. The function is updated with your changes.

Article / Updated 01-07-2022

A nested function is tucked inside another Excel function as one of its arguments. Nesting functions let you return results you would have a hard time getting otherwise. The following figure shows the daily closing price for the Standard & Poor's 500 for the month of September 2004. A possible analysis is to see how many times the closing price was higher than the average for the month. Therefore, you need to calculate the average before you can compare any single price. Embed the AVERAGE function inside another function to calculate the average first. When a function is nested inside another, the inner function is calculated first. Then that result is used as an argument for the outer function. The COUNTIF function counts the number of cells in a range that meet a condition. The condition in this case is that any single value in the range is greater than (>) the average of the range. The formula in cell D7 is =COUNTIF(B5:B25, ">" & AVERAGE(B5:B25)). The AVERAGE function is evaluated first; then the COUNTIF function is evaluated, using the returned value from the nested function as an argument. Nested functions are best entered directly. The Insert Function dialog box does not make it easy to enter a nested function. Try one. In this example, you use the AVERAGE function to find the average of the largest values from two sets of numbers. The nested function in this example is MAX. You enter the MAX function twice within the AVERAGE function. Follow these steps: Enter a few different numbers in one column. Enter a few different numbers in a different column. Click an empty cell where you want the result to appear. Type =AVERAGE( to start the function entry. Type MAX(. Click the first cell in the second set of numbers, press the mouse button, and drag over all the cells of the first set. The address of this range enters into the MAX function. Enter a closing parenthesis to end the first MAX function. Enter a comma (,). Once again, type MAX(. Click the first cell in the second set of numbers, press the mouse button, and drag over all the cells of the second set. The address of this range enters into the MAX function. Enter a closing parenthesis to end the second MAX function. Enter a ). This ends the AVERAGE function. Press Enter. This figure shows the result of your nested function. Cell C14 has this formula: =AVERAGE(MAX(B4:B10),MAX(D4:D10)). Getting a result from nested functions. When you use nested functions, the outer function is preceded with an equal sign (=) if it is the beginning of the formula. Any nested functions are not preceded with an equal sign. You can nest functions up to 64 levels.

Article / Updated 01-07-2022

This list includes the top ten Excel functions. The functions in this list are of the type that apply to a wide array of needs. You won't see a financial function or any advanced statistical function — just the basics — but knowing the functions here is essential to good Excel work. You can always refer here for a quick brush-up on how to use these important functions. SUM Adding numbers is one of the most basic mathematical operations, and so there is the SUM function, dedicated to doing just that. SUM takes up to 255 arguments. Each argument can be a single number or a range containing multiple numbers. That means SUM can add up a whole bunch of numbers! The syntax follows: =SUM(number 1, number 2, …) You can also use SUM with a range, as shown here: =SUM(A1:A12) You can also use SUM with more than one range, such as this: =SUM(A1:A12, B1:B12) AVERAGE Although technically a statistical function, AVERAGE is used so often that it deserves a place in the top ten functions. Everyone is interested in averages. What's the average score? What's the average salary? What's the average height? What's the average number of hours Americans watch TV? AVERAGE can take up to 255 arguments. Each argument can be a number or a range that contains numbers. The syntax follows: =AVERAGE(number 1 ,number 2 ,…) You can also use AVERAGE with a range, as shown here: =AVERAGE(A1:A12) You can also use AVERAGE with more than one range, such as this: =AVERAGE(A1:A12, B1:B12) COUNT COUNT counts the number of cells in a range that contain numbers. It does not provide any sum — just the count. For a list with ten numbers, for example, COUNT returns 10, regardless of what the numbers are. COUNT takes up to 255 arguments, which can be cell references, range references, or numbers themselves. COUNT ignores non-numeric values. If an argument to COUNT is A1:A10 but only two cells contain a number, COUNT returns 2. The syntax follows: =COUNT(cell reference 1, cell reference 2,…) You can also use COUNT with a range, as shown here: =COUNT(A1:A12) You can also use COUNT with more than one range, such as this: =COUNT(A1:A12, B1:B12) INT and ROUND The INT and ROUND functions both work by removing or reducing a number's decimal portion. They differ in exactly how they remove it. INT INT simply drops the decimal portion without rounding — that is, without regard to whether the number is closer to the next higher integer or the next lower integer. Be aware that INT always truncates to the next lower integer. For example, INT changes 12.05 to 12, but it also changes 12.95 to 12. Also, INT changes both –5.1 and –5.9 to –6, not to –5, because –6 is the next lower integer. INT takes a single number argument (as an actual number or a cell reference). The syntax follows: =INT(number or cell reference) ROUND On the other hand, the ROUND function lets you control how the decimal portion is handled. ROUND takes two arguments: the number to be manipulated and the number of decimal places to round to. This gives you more control. A number such as 5.6284 can become 5.628, 5.63, 5.6, or just 6. ROUND always rounds up or down to the nearest number of the next significant digit, so 5.628 becomes 5.63, not 5.62. ROUND turns 12.95 into either 12.9 or 13, depending on the setting of the second argument. Note that two functions — ROUNDUP and ROUNDDOWN — round in one direction only. The syntax for ROUND follows: =ROUND(number, number of decimal places to round to) The syntax for ROUNDUP and ROUNDDOWN is the same as ROUND: =ROUNDUP(number, number of decimal places to round to) =ROUNDDOWN(number, number of decimal places to round to) IF IF is a very handy function. It tests a condition and returns one of two results, depending on the outcome of the test. The test must return a true or false answer. For example, a test may be B25 > C30. If true, IF returns its second argument. If false, IF returns its third argument. IF is often used as a validation step to prevent unwanted errors. The most common use of this is to test whether a denominator is 0 before doing a division operation. By testing for 0 first, you can avoid the #DIV/0! error. One of the great things about IF is that the result can be a blank. This function is great when you want to return a result if the test comes out one way but not if the result is otherwise. The syntax follows: =IF(logical test, value if true, value if false) NOW and TODAY The NOW function returns the current date and time according to your computer's internal clock. TODAY returns just the date. If the date or time is wrong, it can't help you with that. A common use of NOW is to return the date and time for a printed report. You know, so a message such as Printed on 12/20/2015 10:15 can be put on the printed paper. A common use for TODAY is to calculate the elapsed time between a past date and today. For example, you may be tracking a project's duration. A cell on the worksheet has the start date. Another cell has a formula that subtracts that date from TODAY. The answer is the number of days that have gone by. NOW and TODAY take no arguments. The syntax for each follows: =NOW() =TODAY() HLOOKUP and VLOOKUP HLOOKUP and VLOOKUP both find a value in a table. A table is an area of rows and columns that you define. Both of these functions work by using a search value for the first argument that, when found in the table, helps return a different value. In particular, you use HLOOKUP to return a value in a row that is in the same column as the search value. You use VLOOKUP to return a value in a column that is in the same row as the search value. The syntax for these functions follows: =HLOOKUP(lookup value, table area, row, match type) =VLOOKUP(lookup value, table area, column, match type) ISNUMBER A rose is a rose and by any other name would smell as sweet, but numbers don't get off that easy. For example, 15 is a digit, but fifteen is a word. The ISNUMBER function tells you, flat-out true or false, if a value in a cell is a number (including the results of formulas). The syntax follows: =ISNUMBER(value) MIN and MAX MIN and MAX find the respective lowest or highest numeric value in a range of values. These functions take up to 255 arguments, and an argument can be a range. Therefore, you can test a large list of numbers simply by entering the list as a range. The syntax for these functions follows: =MAX(number1,number2,…) =MIN(number1,number2,…) You can also use MIN and MAX with a range, as shown here: =MAX(A1:A12) or with more than one range, such as this: =MAX(A1:A12, B1:B12) SUMIF and COUNTIF SUMIF and COUNTIF sum or count values, respectively, if a supplied criterion is met. This makes for some robust calculations. With these functions, it's easy to return answers for a question such as "How many shipments went out in October?" or "How many times did the Dow Jones Industrial Average close over 18,000 last year?" SUMIF takes three arguments: A range in which to apply the criteria The actual criteria The range from which to sum values A key point here is that the first argument may or may not be the same range from which values are summed. Therefore, you can use SUMIF to answer a question such as "How many shipments went out in October?" but also one such as "What is the sum of the numbers over 100 in this list?" The syntax of SUMIF follows: =SUMIF(range,criteria,sum_range) Note, too, that the third argument in SUMIF can be left out. When this happens, SUMIF uses the first argument as the range in which to apply the criteria and also as the range from which to sum. COUNTIF counts the number of items in a range that match criteria. This is just a count. The value of the items that match the criteria doesn't matter past the fact that it matches the criteria. But after a cell's value matches the criteria, the count of that cell is 1. COUNTIF takes just two arguments: The range from which to count the number of values The criteria to apply The syntax for COUNTIF follows: COUNTIF(range,criteria)

Article / Updated 01-07-2022

A common need in business and when working with Excel is calculating the percentage a value changes from one period to another. For example, showing how revenue changed from one quarter of the current year to the same quarter of the previous year is a standard metric reported in business. Excel is the perfect platform for working with this standard business formula. The formula for calculating change is (new value – old value) / old value. That is, first calculate the difference between the values by subtracting the old value from the new value. Then divide that result by the old value. Fire up Excel, and give it a try! Enter some numbers in a worksheet. This example uses sales figures covering two years, broken out by quarters. One year follows the other in a vertical perspective. Enter a summary row. In this example, the row is below the data. A formula is entered in the column for the first quarter. The formula in cell D11 is =(D8-D5)/D5 Make sure to put parentheses around the subtraction operation; otherwise, the result will be incorrect. Also, make sure that the cell is formatted to General and the Show Formulas button in the Formulas tab is not clicked. If it is, Excel shows you the formula and not the results of the calculation. Frustrating. But it is what it is. Using the drag handle (lower-right corner of the cell), drag across the row to enter the formula in the three columns for the second, third, and fourth quarter. After copying the formula to the adjacent cells, click each cell and then click the fx button in the formula bar and then click the green check mark to commit the change. If you do not do this, Excel will copy the value from the first cell to the last and not recalculate. The calculated value can be positive, negative, or zero. A positive number shows a gain, or increase. A negative number shows a loss, or decrease. A result of zero indicates no change. A value returned from this formula is a percentage and often appears as a fraction. If the percent change is greater than 100, the value will be greater than 1 (or greater than –1 for a loss of more than 100 percent). Change the formatting to a percentage. Excel’s formatting options includes a handy button that multiplies by 100 and puts the percentage sign (%) in the result. With the cells containing the formulas selected, click the disclosure button on the Number Format box on the Home tab of the Ribbon; then, choose Percentage from the drop-down list. The change in a value over time is best presented as a percentage. Just showing the difference as a number does not give it a conclusive meaning. Saying that sales have increased by 100 units does not make it clear whether this news is good or bad. Saying that sales increased by 10 percent is clearly good news. Review your worksheet to ensure the calculated results appear as percentages. For all the great number-crunching you do, when you hand the worksheet up to management, it should be dressed to the nines. On the worksheet it is now uber-easy to see the percentage change in the cells along Percent Change (+/–) row.

Article / Updated 01-07-2022

The Insert Function dialog box (shown in the following figure) is designed to simplify the task of using functions in your worksheet. The dialog box not only helps you locate the proper function for the task at hand, but also provides information about the arguments that the function takes. Use the Insert Function dialog box to easily enter functions in a worksheet. If you use the Insert Function dialog box, you don't have to type functions directly in worksheet cells. Instead, the dialog box guides you through a (mostly) point-and-click procedure — a good thing, because if you're anything like me, you need all the help you can get. In the Insert Function dialog box, you can browse functions by category or scroll the complete alphabetical list. A search feature — you type a phrase in the Search for a Function box, click the Go button, and see what comes up — is helpful. When you highlight a function in the Select a Function box, a brief description of what the function does appears under the list. You can also click the Help on This Function link at the bottom of the dialog box to view more detailed information about the function. You can display the Insert Function dialog box in three ways: Click the Insert Function button on the Formulas Ribbon. On the Formula Bar, click the smaller Insert Function button (which looks like fx). Click the small arrow to the right of the AutoSum feature on the Formulas Ribbon, and select More Functions. AutoSum has a list of commonly used functions that you can insert with a click. If you select More Functions, the Formula Builder dialog box opens. The AutoSum button offers quick access to basic functions and the Insert Function dialog box.

Article / Updated 03-22-2019

Excel’s IF function is like the Swiss Army knife of Excel functions. Really, it is used in many situations. Often, you can use Excel’s IF function with other functions. IF, structurally, is easy to understand. The Excel IF function takes three arguments: A test that gives a true or false answer. For example, the test "is the value in cell A5 equal to the value in cell A8" can have only one of two possible answers, yes or no. In computer talk, that's true or false. This is not a calculation, mind you, but a comparison. The data to be returned by the IF function if the test is true. The data to be returned by the IF function if the test is false. Sounds easy enough. Here are some examples: Function Comment =IF(D10>D20, D10, D20) If the value in D10 is greater than the value in D20, the value in D10 is returned because the test is true. If the value in D10 is not greater than — that is, smaller or equal to — the value in D20, the value in D20 is returned. If the values in D10 and D20 are equal, the test returns false, and the value in D20 is returned. =IF(D10>D20, "Good news!", "Bad news!") If the value in D10 is greater than the value in D20, the text “Good News!” is returned. Otherwise, “Bad News!” is returned. =IF(D10>D20, "", "Bad news!") If the value in D10 is greater than the value in D20, nothing is returned. Otherwise, “Bad News!” is returned. Note that the second argument is a pair of empty quotes. =IF(D10>D20, "Good news!", "") If the value in D10 is greater than the value in D20, “Good News!” is returned. Otherwise, nothing is returned. Note that the third argument is empty quotes. An important aspect to note about using IF: letting the second or third argument return nothing. An empty string is returned, and the best way to do this is to place two double quote marks together with nothing in the middle. The result is that the cell containing the IF function remains blank. IF, therefore, lets you set up two results to return: one for when the test is true and another for when the test is false. Each result can be a number, some text, a function or formula, or even blank. As you see in the preceding example, a common use of IF is to see how two values compare and return either one value or the other, depending on how you set up the test in the first argument. IF is often used as a validation check to prevent errors. Suppose that you have a financial worksheet that uses a variable percentage in its calculations. The user must enter this percentage each day, but it must never be greater than 10 percent. To prevent the chance of errors, you could use the IF function to display an error message in the adjacent cell if you mistakenly enter a value outside the permitted range. Assuming that the percentage is entered in cell A3, here's the required IF function: =IF(A3>.1, "ERROR: the % in A3 IS TOO LARGE", "") The following image shows how IF can be put to good use in a business application. A fictitious store shop — Ken's Guitars (kinda snappy, don’t you think?) — keeps tabs on inventory in an Excel worksheet. Column D shows the inventory levels, and column E shows the reorder levels. It works this way: When a product's inventory level is the same or less than the reorder level, it is time to order more of the product. The cells in column F contain a formula. The Excel formula in cell F8 is =IF(D8<=E8,"ORDER",""). It says that if the number of Stratoblaster 9000 guitars in stock is the same or less than the reorder level, return Order. If the number in stock is greater than the reorder level, return nothing. Nothing is returned because three are in stock and the reorder level is two. In the next row, the number of Flying Xs is equal to the reorder level; therefore, cell F9 displays Order. Using Excel’s IF function is easy. Follow these steps: Enter two values in a worksheet.These values should have some meaning to you, such as the inventory levels example shown above. Click the cell where you want the result to appear. Type =IF( to start the function. Decide what test you want to perform.You can see whether the two values are equal; whether one is larger than the other; whether subtracting one from the other is greater than, equal to, or less than 0; and so on. For example, to determine whether the first value equals the second value, click the first cell (or enter its address), enter an equal sign (=), and then click the second cell (or enter its address). Type a comma (,). Enter the result that should appear if the test is true. For example, enter “The values are equal”. Text must be enclosed in quotes. Type a comma (,). Enter the result that should appear if the test is false.For example, enter “The values are not equal”. Type a ) and press Enter. The Excel IF function can do a whole lot more. Nested IF functions give you a lot more flexibility in performing tests on your worksheet data. A bit of perseverance is necessary to get through this. Nested means that you can place an IF function inside another IF function. That is, the inner IF is placed where the true or false argument in the outer IF goes (or even use internal IFs for both of the arguments). Why would you do this? Here’s an example: The other night, we were deciding where to go for dinner. we were considering Italian and decided that if we went to an Italian place and it served manicotti, we would have manicotti. Otherwise, we decided to eat pizza. Logically, this decision looks like this: If the restaurant is Italian, then If the restaurant serves manicotti, then we will have manicotti else we will have pizza This looks a lot like programming code. The End If statements have been left off on purpose to prevent confusion because the IF function has no equivalent value. That's it! Make note that the inner IF statement has a result for both the true and false possibilities. The outer IF does not. Here is the structure as nested Excel IF statements: =IF(Restaurant=Italian, IF(Restaurant serves manicotti, "manicotti", "pizza"), "") If the restaurant were not Italian, it wouldn’t matter what the choice was (as indicated by the third argument of the outer IF being empty). You can nest up to 64 IF statements, although things are likely to get very complicated once you go beyond 4 or 5. You can apply a nested IF statement to increase the sophistication of the inventory worksheet from above. The following image has an additional column: Hot Item. A Hot Item can take three forms: If the inventory level is half or less of the reorder level and the last sale date is within the last 30 days, this is a Hot Item. The point of view is that in 30 days or less the stock sold down to half or less than the reorder level. This means that the inventory is turning over at a fast pace. If the inventory level is half or less of the reorder level and the last sale date is within the last 31–60 days, this is a Warm Item. The point of view is that in 31–60 days the stock sold down to half or less than the reorder level. This means that the inventory is turning over at a medium pace. If neither of the preceding two conditions is met, the item is not assigned any special status. There are Hot Items, and there are Warm Items. Both must meet the common criterion that the inventory is 50 percent or less of the reorder level. Only after this first condition is met does the second criterion — the number of days since the last order — come into play. Sounds like a nested IF to me! Here is the formula in cell G8: =IF(D8<=(E8@@ts0.5),IF(NOW()-C8<=30,"HOT!",IF(NOW()-C8<=60,"Warm!","")),"") Okay, take a breath. The outer IF tests whether the inventory in column D is equal to or less than half (50 percent) of the reorder level. The piece of the formula that does that is =IF(D8<=(E8@@ts0.5). This test, of course, produces a true or false answer. If it is false, the false part of the outer IF is taken (which is just an empty string found at the end of the formula: ,"")). That leaves the whole middle part to wade through. Stay with it! If the first test is true, the true part of the outer IF is taken. It just so happens that this true part is another IF function: IF(NOW()-C8<=30,"HOT!",IF(NOW()-C8<=60,"Warm!","")) The first Excel argument of the inner IF tests whether the number of days since the last order date (in column C) is less than or equal to 30. You do this by subtracting the last order date from today, as obtained from the NOW function. If the test is true, and the last order date is within the last 30 days, HOT! is returned. A hot seller indeed! If the test is false … wait, what's this? Another IF function! Yes: an IF inside an IF inside an IF. If the number of days since the last order date is greater than 30, the next nested IF tests whether the number of days is within the last 60 days: IF(NOW()-C8<=60 If this test is true, Warm! is returned. If the test is false, nothing is returned. A few key points about this triple-level IF statement: The IF that tests whether the number of elapsed days is 30 or fewer has a value to return if true (HOT!) and a value to return for false (whatever is returned by the next nested IF). The outer IF and the innermost IF return nothing when their test is false. On the surface, the test for 60 or fewer days also would catch a date that is 30 days or fewer since the last order date. This is not really what is meant to be. The test should be whether the number of elapsed days is 60 or fewer but more than 30. You do not have to actually spell it out this way, because the formula got to the point of testing for the 60-day threshold only because the 30-day threshold already failed. Gotta watch out for these things!

Article / Updated 01-31-2019

Excel’s OFFSET function lets you get the address of the cell that is offset from another cell by a certain number of rows and/or columns. For example, cell E4 is offset from cell B4 by three columns because it is three columns to the right. The Excel OFFSET takes up to five arguments. The first three are required: A cell address or a range address: Named ranges are not allowed. The number of rows to offset: This can be a positive or negative number. Use 0 for no row offset. The number of columns to offset: This can be a positive or negative number. Use 0 for no column offset. The number of rows in the returned range: The default is the number of rows in the reference range (the first argument). The number of columns to return: The default is the number of columns in the reference range. If you omit the last two arguments, OFFSET returns a reference to a single cell. If you include a value greater than 1 for either or both, the function's return references a range of the specified size with the top-left cell at the specified offset. The following image shows some examples of using Excel’s OFFSET function. Columns A through C contain a ranking of the states in the United States by size in square miles. Column E shows how OFFSET has returned different values from cells that are offset from cell A3. Some highlights follow: Cell E4 returns the value of cell A3 because both the row and column offset is set to 0: =OFFSET(A3,0,0). Cell E7 returns the value you find in cell A1 (the value also is A1). This is because the row offset is –2. From the perspective of A3, minus two rows is row number 1: =OFFSET(A3,-2,0). Cell E8 displays an error because OFFSET is attempting to reference a column that is less than the first column: =OFFSET(A3,0,-2). Cell E10 makes use of the two optional OFFSET arguments to tell the SUM function to calculate the sum of the range C4:C53: =SUM(OFFSET(A3,1,2,50,1)). Here's how to use the OFFSET function: Click a cell where you want the result to appear. Type =OFFSET( to start the function. Enter a cell address or click a cell to get its address. Type a comma (,). Enter the number of rows you want to offset where the function looks for a value. This number can be a positive number, a negative number, or 0 for no offset. Type a comma (,). Enter the number of columns you want to offset where the function looks for a value. This can be a positive number, a negative number, or 0 for no offset. Type a ) and press Enter. OFFSET is another of those functions that can be used alone but is usually used as part of a more complex formula.