Rolling 12 Months Chart

I know I haven't posted in a while... I've been crazy busy with work, moving, and life in general.. But things are settling down and I hope to get back on track.

Recently I was asked to create a chart that could function as a "rolling 12 months" chart.  If using Power Pivot in Excel 2010 or 2013, this would have been quick and easy using a date/time dimension table to slice the data.  However, this needed to be supported in Excel 2007.  Soooo, I had to get creative.

Here is what I came up with -

Specific formulas needed:
=EDATE(start date, months)
=VLOOKUP(Lookup value, table array, col_index_num, [range_lookup])

To begin, we have our data set:

This is simply a random data set that I generated for this example with no true significance in the real world... Note: The dates in column "A" were entered as "6/1/2012" and then formatted within the cell to only show month/year.  I always use the first day of the month when working with month-year formats.  Not sure if this is best practice, but it is my own standard since there is always a day "1" in every month.

Next, I created a table that will show the 12 months' data:

Above the table I simply added "Last Month" and "Last Year" fields for data entry.  These will be used by formulas within the table.  The numbers in the yellow cells are manually entered/changed - although I suppose you could use a data validation drop-down if you wanted to limit what could be entered.

Anyway, Here's how I created the table:
In the bottom left cell of the table ("Jun-14") I added the formula: =DATE(I1, G1, 1) where G1 is the value entered in the yellow cells for "Last Month" and I1 is the value entered in the other yellow cell for "Last Year" - Last month and Last year meaning the last month/year you wish to be shown on the chart.

In the cell above the "Jun-14" cell, I use the formula: =EDATE(F14,-1) where F14 is the date that we just created in the bottom left cell of the table, and "-1" tells excel to subtract 1 month.

I simply continue or "Auto-Fill" the formula to the top of the table.  Now, when you change the date parts in the yellow cells, the dates in the table will automatically adjust.

Next, we use a vlookup formula to go and gather the data that corresponds with the date in the first column of the table. By using the "dynamic" dates we've created, our data will also be dynamically selected from the original data set. - Since I've addressed vlookup in past posts, I'll skip this part.  I will post a snapshot of the table showing the formulas at the end though.

The last column in the table is simply a preference.  I create the numerator/denominator for the data label on the chart.  I do this simply by concatenating the values using this formula: =H3&"/"&I3 (this results in the value at the top right of the table: 1091/1100).  I find it very helpful to include the n/d when displaying percentages in a chart.  This shows the reader if 50% is 1/2 or 500/1000.

Final Snapshot of table showing formulas:

Last, I create a simple line chart using the "Month" and "Success%" columns in our table.  Add data labels to each of the data points on the chart.  To create dynamic chart data point labels simply click on an individual label, and in the formula bar type "=" and the cell reference of the value you are looking to display - In this case, the first label on the left would be =$J$3.

Since we created the table using relative references instead of specific numbers and values, when the Last Month and Last Year are changed at the top, the data in the table as well as the data on the chart are instantly updated to show the "rolling 12 months".

I know this was a bit rushed...  That's why I'm including an example file this time!  You can get it here.

Dates and Pivot Tables

I was asked the other day how to show just the month within a pivot table row using a field that has dates formatted like "1/1/2014" throughout the year.  As I discussed this further, I found out that it would be useful to have the month show and then all of the dates within that month below it.

Today we're going to address methods to accomplish these tasks:

#1 - In a cell or cells, how do I show only the month in a year?  Let's Use "1/1/2014":

• =Text([cell reference with date value],"mmm") = Jan
• "m" = 1
• "mm" = 01
• "mmm" = Jan
• "mmmm" = January

• =Month([cell reference with date value])
• This will give the number of the month = 1]

• Right-click on column with the dates > Format cells > Number Tab > Custom > Type = "mmm" (use one of the above)

#2 - In a pivot table, how do I group the dates by month in the row category?

First, let's check to see that our data table is ready for a pivot table... What are we missing?  Well, in order for a pivot table to function properly and include all columns selected, each column must have a column header.  In this data set, we're missing the header in column A.  Let's add one and then insert our pivot table.

To insert a pivot table: Highlight your data table (A1:D9) > Insert > Pivot Table > New Worksheet > OK

Now, we're ready to include our dates in the Rows.  Add the "Date of Purchase" field to the Rows area:

The Resulting table looks like this:

Now, lets group by the date:

1. Click on a date in the pivot table
2. Right-click the same date you just selected
3. Click "Group..."
4. For this example, choose both "Months" and "Years" (**to de-select an item just click it again!) 
5. Click OK

**Tip: If you are getting an error when trying to click "Group...", check to make sure that none of your cells in your date column are blank.  Excel will not allow you to group with blank cells.

Now, Add the Price field to the values field in your pivot table.  Your pivot table will now look like this:

The above example shows us how to group the data by year and month.  

Now, how do we add the individual dates underneath the month?  For this, we will need to make some additions to our table.  Add the following formulas into the columns next to the table:

Auto-fill or copy/paste the formulas down to the end of the table.  

*I'm also going to change the bottom date value to 2/2/2014 for purposes of this example.

Use the following steps to create the desired table:

• Highlight the data table
• Insert Pivot Table on a New Worksheet
• Add (in this order) Year, Month, Date of Purchase to the rows section of the pivot table
• Add Price to the values section of the pivot table

You now have a pivot table that groups the data By Year, By Month AND has individual line items under the month.

Last, since we're looking at dollars, let's format the "Sum of Price" column as currency:

• Double-click on "Sum of Price" column header in the pivot table
• Click the "Number Format" button at the bottom of the Value Field Settings pop-up window
• Choose "Currency" and your preferred format for negative numbers
• Click "OK"

You now have this:

There are lots of great ways to make data more meaningful through the use of pivot tables.  In the future I will discuss using Power Pivot, data models, and slicers to be more efficient and effective in your work.