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.
We can use OFFSET 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.
OFFSET is a reference function. 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 [] mean 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, it's not. This is an Excel Hack that every 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
Now there is value in Learning with The Excel Club and our Learn and Earn STEEM activities.
We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn.
To Find out more now and start earning while you are learning Excel and Power BI
