How to set up a reference date table and summarise figures to get absolute and Year-On-Year (YOY) monthly figures

Challenge level: 1.5 out of 5

Estimated reading time: 9 min

Excel functions covered: YEAR(), MONTH(), VALUE(), LEFT(), VLOOKUP(), SUMIFS() and IFERROR()

Excel attachment reference sheets: raw data, template_Excel and template_date table

1. Get your date into a YYYYMM format.

In sheet “raw data”, add a column that turns dates into YYYYMM format. This allows for easy reference when creating summary tables of your data based on time periods.

day 1 step 1 (further crop).png
day 1 step 1 formula (transparent).png

2. Set up a reference date table

In order for the formulas that we will introduce later to automatically update their calculations when the referenced YYYYMM changes, we have set up a reference date table that sits on the sheet titled “template_date table”.  

We work further off this table to create reference cells on our main template sheet (titled “template_Excel”) that will form part of the formulas for Total Rolling Last 12 months, Year-to-Date, Financial Year-to-date figures etc.

day 1_mailchimp edit.png

2.1. Manually input your data’s date range in YYYYMM format in column B. Next to each individual date entry, input their corresponding start of financial year in YYYYMM format as well in column C.

day 1 date table (further crop).png

2.2. On a separate sheet (titled “template_Excel”), create a drop down list with the date range from step 1. With this drop down list, you can easily select the YYYYMM you want to focus on.

How to create a drop down list:

a. Select cell that you want to attach the drop down list to (we have selected cell G3 on sheet “template_Excel”).

b. Click on the “Data” tab and select Data validation.

day 1 date table-2.png

c. Once the window pops up, select “List” as the validation criteria.

d. You will now be able to reference the data source you want to populate the drop down list with. This is done under the “Source” field. Go to the sheet titled “template_date table” and select the cells holding your YYYYMM data.  

day 1 date table-4.png

e. Finally, hit the “OK” button and voila!

day 1 date table-5.png

2.3. Once the drop down list is set up, combine VALUE() and LEFT() functions to help obtain the correct Start of Calendar Year figure off your selected Latest YYYYMM. Our set-up of the LEFT() function allows us to extract the first 4 characters what appears in cell G3, which is effectively the year (i.e. YYYY). However, just using LEFT() function on its own would give us returned results in text form, rather than reflecting YYYY as numerical values. To fix this, we nest LEFT() within a VALUE() function to get numerical figures. To round off, we simply need to add “01” after the embedded LEFT() function so that the full text to convert into numerical figures reads as YYYY01.    

2.4. To get the accompanying Start of FInancial Year to your selected Latest YYYYMM, we make use of the VLOOKUP() function. Our set up of the VLOOKUP() function looks up the selected Latest YYYYMM against the reference date table on the “template_date table” sheet. It then returns the value of the Financial Year Start that sits next to the selected Latest YYYYMM .   


3. Summarize your figures to get monthly values

It is always good practice to set up your summary tables on a separate Excel sheet ( this is why we have the sheet titled “template_Excel”) from the original data. So let’s proceed in getting our summary tables populated!

3.1 Summary table of absolute figures

day 1 step 2 (further crop).png
day 1 step 2 formula (transparent).png

In simple terms, the formula is structured to return a summed-up value of only “Bluetooth Headphones" in “200701”. So, for example, if you edit your CRITERIA1 to cell $A11 but keep your CRITERIA2 unchanged, you will get a summarized value of “Camcorder” in “200701”.

3.2 Summary table of Year-On-Year (YOY) figures

day 1 step 2.2 (further crop).png
day 1 step 2.2 formula (transparent).png

The formula is basically a mathematical equation of dividing the difference between current and previous years’ figures by the previous year figure. We use the IFERROR function here to help keep a clean look across the table so that a blank cell is reflected where the equation returns an error value (i.e. when the denominator is zero).

You may be wondering: “why can’t I just set-up these tables using Pivot Table?”

It's true that Pivot Tables could help you summarize these figures according to the months easily.

However, Pivot Tables don’t work well when it comes to dynamically updating and calculating more complex figures like Totals for Rolling Last 12 Months, Year-To-Date and Financial-Year-To-Date. Tomorrow, we’ll learn how this set up helps to achieve these dynamic updates and calculations.