How-to Create a Dynamic Excel Chart of Last 3 Months Data Without Offset

Chart of Last 3 Months

If you wanted to create a dynamic Excel chart of last 3 months of your data but didn’t know how to use or didn’t want to use the Excel Offset Function, then check out this alternate technique.  Maybe it is because you don’t want to use Excel Volatile functions or just don’t get how the Offset function works.  Please note that this technique will require you to press a button but that isn’t too difficult.

Monthly Chart Not Using Offset Sample
Monthly Chart Not Using Offset Sample

If you are interested in how this trick is different than an Offset function, then check out this link:

Create a Dynamic Chart Using the Offset Function

 

Breakdown

The basic’s of this technique is to build a table of chart values with corresponding formulas that identify the last 3 months.  Then create a Pivot table and resulting Pivot chart out of the data table with a filter on the last 3 months that were identified.

1) Add Column In Data Set

2) Create Last 3 IF Formula and Across All Data Points

3) Create Pivot Table of New Data Set

4) Create Pivot Chart

5) Refresh Pivot Data to Refresh Pivot Chart

 

Step-by-Step Tutorial

1) Add Column In Data Set

If we start with our data in a typical monthly sales format of the month name and sales amount in the adjacent column:

A B
1 Month Sales
2 Jan 560
3 Feb 641
4 Mar 777
5 Apr 113
6 May
7 Jun
8 Jul
9 Aug
10 Sep
11 Oct
12 Nov
13 Dec

Next you will want to add a column where we will put our calculation.  It doesn’t matter where, so I put mine in Column C as you see here:

A B C
1 Month Sales Last 3 Months
2 Jan 560
3 Feb 641
4 Mar 777
5 Apr 113
6 May
7 Jun
8 Jul
9 Aug
10 Sep
11 Oct
12 Nov
13 Dec

 

2) Create Last 3 IF Formula and Across All Data Points

In the newly created “Last 3 Months” column, we will create a fairly straight forward IF formula.  If your new column is in column C, then you can use this formula in Cell C2 and copy it down to C13:

=IF(AND(B2>0,ISBLANK(B3)),1,IF(C3=1,2,IF(C3=2,3,””)))

This formula checks to see if there is a values in the sales column.

Formula Breakdown

IF Sales Column cell adjacent to the current cell is greater than zero and the Sales Column cell one row down to the current cell is blank, then this is the last entry of the current year.  So we put a “1” as the result.  IF that is not the case, then we check to see if the value 1 row down from the current cell in the Last 3 Months Column equals a 1 then this cell is the 2nd most recent and we put a 2 in the cell.  Finally, if that is not the case, then we check to see if the value 1 row down from the current cell in the Last 3 Months Column equals a 2 then this cell is the 3rd most recent and we put a 3 in the cell or else we leave it as blank.

Here are the formulas by cell and what your data table will look like:

A B C
1 Month Sales Last 3 Months
2 Jan 560
3 Feb 641 3
4 Mar 777 2
5 Apr 113 1
6 May
7 Jun
8 Jul
9 Aug
10 Sep
11 Oct
12 Nov
13 Dec
Worksheet Formulas

Cell Formula
C2 =IF(AND(B2>0,ISBLANK(B3)),1,IF(C3=1,2,IF(C3=2,3,””)))
C3 =IF(AND(B3>0,ISBLANK(B4)),1,IF(C4=1,2,IF(C4=2,3,””)))
C4 =IF(AND(B4>0,ISBLANK(B5)),1,IF(C5=1,2,IF(C5=2,3,””)))
C5 =IF(AND(B5>0,ISBLANK(B6)),1,IF(C6=1,2,IF(C6=2,3,””)))
C6 =IF(AND(B6>0,ISBLANK(B7)),1,IF(C7=1,2,IF(C7=2,3,””)))
C7 =IF(AND(B7>0,ISBLANK(B8)),1,IF(C8=1,2,IF(C8=2,3,””)))
C8 =IF(AND(B8>0,ISBLANK(B9)),1,IF(C9=1,2,IF(C9=2,3,””)))
C9 =IF(AND(B9>0,ISBLANK(B10)),1,IF(C10=1,2,IF(C10=2,3,””)))
C10 =IF(AND(B10>0,ISBLANK(B11)),1,IF(C11=1,2,IF(C11=2,3,””)))
C11 =IF(AND(B11>0,ISBLANK(B12)),1,IF(C12=1,2,IF(C12=2,3,””)))
C12 =IF(AND(B12>0,ISBLANK(B13)),1,IF(C13=1,2,IF(C13=2,3,””)))
C13 =IF(AND(B13>0,ISBLANK(B14)),1,IF(C14=1,2,IF(C14=2,3,””)))

 

3) Create Pivot Table of New Data Set

Your data set is not completed for this tip/trick.  But to only show the data for the last 3 months, we need to filter out all the blanks in the “Last 3 Months” column.  You can do that in one of 2 ways.  Either add a filter to your columns in the original data set or to create an Excel pivot table.  I choose the pivot table route as it does not hide your rows so that you don’t have to unhide them next month to add your next data point.

 

Filtered Chart Data Table
Filtered Chart Data Table

 

To create a pivot chart, highlight the data range of A1:C13 and then go to the Insert Ribbon and choose the Pivot Table button

Insert Ribbon Excel Offset Chart Data
Insert Ribbon Excel Offset Chart Data

Then create your pivot table with the following settings:

Pivot Table Field List Non-Offset Tip
Pivot Table Field List Non-Offset Tip

Then change the Report Filter of “Last 3 Months” and de-select the Blanks.

Pivot Table Non-Offset Tip Report Filter
Pivot Table Non-Offset Tip Report Filter

Your resulting pivot table will look like the image you see above titled: “Pivot Table Field List Non-Offset Tip”.

 

4) Create Pivot Chart

Now that your pivot table is completed, all you need to do is create a pivot chart.  To do that, simply click anywhere in your pivot table and then go to the Insert Ribbon and choose any chart that you want to create.

Pivot Table Tools Options Ribbon Pivot Chart Button
Pivot Table Tools Options Ribbon Pivot Chart Button

Here is what your final chart will look like.  You may want to clean up the chart by Hiding All Field Buttons on the chart (right click to do that).

Pivot Chart Non-Offset Tip
Pivot Chart Non-Offset Tip

 

5) Refresh Pivot Data to Refresh Pivot Chart

The trick to make the pivot chart dynamically change is to Refresh the Pivot Table after you add new data to the original data range.  As you add data, your “Last 3 Months” column of data will adjust and also in your pivot table data as you press the Refresh All button on the Data Ribbon

Data Ribbon Refresh All Button
Data Ribbon Refresh All Button

 

Video Demonstration

So if you need to create a non-volatile ‘mostly dynamic’ chart, you can use a simple IF formula along with a Pivot Table or Worksheet Filter to help you chart the most recent data points and save you time adjusting your Excel Charts.

 

Steve =  True