OFFSET Function to make Excels SUM function Dynamic

Everyone can use the SUM function in Excel, it is the most basic and used functions available. In fact it is the only formula in Excel that has its own keyboard shortcut (Alt + =).  However many users find difficulty when adding additional rows to the data which they are summing.  The formula has taken cell references and this is causing problems.

The OFFSET function can be used to overcome this problem and make the SUM function more dynamic, eliminating the problem of adding additional rows of data to the data set that is to be totted.

The OFFSET function can be used to refer to the location of specific data in an Excel worksheet. It returns the value of a cell that is a specified number of rows and columns away from the active cell

The Syntax is OFFSET(Reference, Rows, Columns, [height],[width])

The OFFSET function will first look for a Reference, this is the starting point, the cell that you want the answer to end up in.  The function then looks for rows, and this is the number of rows away that you want to move to get the data you require.  If you are moving up rows you must use a minus value.  The function then looks for Columns, this is the number of columns away that you need to move to get the data you require. Height and width as they are placed inside [] means that they are optional

What we will look at in the video below is replacing cell references in the SUM function with the OFFSET function.  Now this might sound a little complicated but to be honest its not.  This is an Excel Hack that even beginner Excel users should know!

Have a look at the video and it will explain all.  And if you find it useful I hope that you will like and share


Looking for some FREE Excel Training:  Take our Basic Excel for Basic Bookkeeping free here with The Excel Club

In return for this Excel Tip –OFFSET Function to make the SUM function Dynamic – I ask that you share this post with your friends and colleagues

One thought on “OFFSET Function to make Excels SUM function Dynamic

Comments are closed.

%d bloggers like this: