Best Microsoft Excel Bloggers

Friday, September 2, 2011

USE THE OFFSET FUNCTION TO CREATE A HORIZONTAL CHART THAT AUTOMATICALLY UPDATES







In most cases people tend to add data to a chart vertically however there are instances where you need to add columns of data to your chart. To have the chart automatically pick up additional columns as they are added you should use the OFFSET function.
In the example below I have widget sales for January through March.

 
 
 
 
I wanted to create a chart so that as I added April through December sales into the chart, the chart would automatically include the new data without me having to do anything to the chart.

 
First, I need to create my chart as I normally would. I am using a column chart as i think it is a little easier to see.

 

 

 

 
 
 
 
 
 
 
 
 
Then I need to identify each of the 2 rows to Excel by creating defined names.
We are going to create a name and formula for the months and then for the widget sales.
Go to the Formula tab and select Define Name

  •  
  • In the Name box type Month_Labels
  • In the Refers To box: type =OFFSET(Sheet1!$A$1,,1,,COUNTA(Sheet1!$B$1:$ZZ$1))
  • Click OK
This is telling Excel to go to cell A1 and then move over 1 column and to start selecting data all the way to Column ZZ.  Now currently there is only data Columns A through D but as we add additional columns of data, Excel will be looking for it.
Now we need to define the widget sales
  • Go to the Formula tab and select Define Name
  • In the Name box type Widget_Data
  • In the Refers To box: type =OFFSET(Sheet1!$A$2,,1,1,COUNTA(Sheet1!$B$2:$ZZ$2))
  • Click OK
This is telling Excel to go to cell A2 and to move over 1 column and select data from B2 through Column ZZ.



 

 

 






Click in the chart and select the data series and replace the cell references to the formula names of Month_Labels and Widget_Data












Now as you add April and May's data, Excel will automatically include it in the chart.


Click here to download the Excel file.

3 comments:

  1. great tip - saves updating all those chart data ranges. cheers

    ReplyDelete
  2. This is exactly what I was looking for! I do have a couple of questions - if I had another row of data in row 3, what would the OFFSET formula look like? I don't understand why for Month we use Sheet1!$A$1,,1,, while for Widget we use Sheet1!$A$2,,1,1, ( another 1 and different , placement). Thank you!

    ReplyDelete
  3. Glad you found it useful.
    A1 pulls the first row which is months and A2 pulls the widget data. If you had additional data in row 3, it would be similar - just copy formula above and substitute A3 for the A2. this is for a horizontal chart - most times your chart is going to pull from vertical data.

    ReplyDelete


Ms. Excel- Resident Excel Geek