< Go Back

Excel OFFSET Function

Excel OFFSET Function

Excel OFFSET function can help you to quickly duplicate a reference from a particular cell to any specified part. These parts could be in a row, column, a particular point or place in your spreadsheet.

You can also use the OFFSET function to calculate average values or sums on your spreadsheet. The list of things you could do with the OFFSET function is long.

Syntax

=OFFSET (reference, rows, cols, [height], [width])

Explanation

The OFFSET function could be a bit deceptive, so you need to follow the steps carefully to get the result you want.

Arguments

reference: this is the beginning point that is needed in a cell as a reference.

rows: is directly below the reference.

cols: are the columns needed to be offset in relation to the reference. Other optional arguments are height and width.

In this article, we are going to use the OFFSET function in two ways; to get a total sum and the average sum in an excel spreadsheet.

Example

We are going to be creating a data comprising the earnings of a generic organization over the year in its different locations. And this will be done for a specific quarter.

You can apply the same steps to any situation to get the sum.

Step 1

Enter all data correctly, which includes the earnings for London, Bangkok, New York, and Thailand.

Figure 1. Excel showing the data entered correctly.

Step 2

Specify the quarter for which the earnings would be generated.

Figure 2. Excel showing how the quarter is specified

Step 3

Then enter the formula to get the total sum for that particular quarter.

Figure 3. Excel OFFSET function returns all the reference per cell.

Excel OFFSET Function Notes

  •        The OFFSET function can be problematic by making changes to the worksheet; these changes often result from other alterations in it.
  •        The REF! error is often returned when the offset value floats to the edge of your Excel spreadsheet.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar