It is a common task that we want to input and maintain all data on one worksheet, and display summary on different worksheets. A typical layout will be similar to the screenshot shown above. One “Input” sheet, and then one sheet for displaying summary for each month. In this way, a total of 13 worksheets will be displayed on your workbook. Indeed, this was a question to me…

Can it be done?

Of course it can be done. BUT we could do it doesn’t mean we should do it.

My suggestion is to hold only ONE sheet to display the monthly summary on which users have the flexibility to select any month (or any period) by using Pivot Table Timeline.

Here’s the layout of the proposed solution:

You may download a sample file to follow along.

Note: The file is saved as .xlsm (macro-enable) format. Please enable content when prompted if you wish to see the auto-refresh action.

There are many ways to achieve the similar result. To me, Pivot Table is the best way. 😉

First Thing First

Before we create the pivot table, we have to think about how we can capture new data in the summary. Maybe whole column referencing is on your mind (which I highly discourage). Perhaps you know how to set dynamic name range with the use of OFFSET. But you know what, whenever we need to deal with dynamic range to anticipate new data from time to time, Excel Table is the best option. No doubt!

Insert Excel Table

1. Go to Insert tab
2. Select Table
3. Confirm the range of data; and confirm if your table has headers
4. OK

As a good practice, name your Table

5. When the Table is selected, go to Table Design tab
6. Rename it to “Data”

Insert Pivot Table

1. Go to Insert tab
2. Click PivotTable
3. Input the Table name, i.e. Data
4. Select where you want to insert the pivot table -> “New Worksheet” in our case
5. OK

6. Drag the data fields to the corresponding areas of the pivot table (per your need)
7. Rename the worksheet (per your need)

Insert Timeline

PivotTable Timeline is a handy feature added to Excel 2016 (or later for Windows). It requires a column of “date” in the data source for the pivot table. It then automatically groups dates into calendar month, quarter and year for user’s selection. Check it out!

Select any cell in the pivot table

1. Go to PivotTable Analyze tab
2. Insert Timeline
3. Select the data field “Date“
4. OK

Note: a few blank rows have been inserted on top for placing the Timeline

Here we go!

Tips:

1. Resize the timeline to fit your pivot table layout
2. Turn off the “Autofit column widths on update” in PivotTable Options

As you see, we don’t need 12 separate sheets for each month. We can have one summary sheet where we can select which month(s) of data we want to look into. We can even change the intervals to quarter or year on the timeline. Isn’t it a better option?

Limitation

Wait… The figures do not update automatically. A manual refresh is required after new data added or old data updated in the data source. This is not ideal. I know. You know.

Macro comes to recuse

No worry. This limitation can be overcome with a simple macro that refreshes all pivot tables when the worksheet holding the pivot table is selected. In this way, user will always see updated data without refreshing the pivot table manually.

Not familiarize with Marco? Me either 😅

Indeed, you don’t need to be a VBA expert to achieve that. In the following video, I showed all the steps discussed above, and how to create the Macro required, with the help of Macro recorder. Have a look!

I hope you like it.

Please tell me what you think by leaving comments below.