How to Quickly Create Time-Series PivotCharts / by Robert Walker

Time-Series charts are a staple data-analysis and visualization tool in many industries and can be a "go-to" first look for finding deviations or trends overtime.  However, many times my clients want to view data for many locations on one chart and have the ability to change the comparison on a whim.  So, there are a few options for this:

  1. Use a web-based application using D3, plotly, etc. (or setup an expensive "out of the box" solution, like SiSense or Tablaeu).
  2. Write a macro that re-builds the chart
  3. Use PivotCharts

Number 1 requires significant time investment (even if you use a pre-fabbed solution like Tableau) to get the data online, setup the dashboard, etc.  That's assuming the client data is even allowed to go online and you have the capital to purchase those applications.

Number 2 requires some time investment to write the code, is often clunky, and if the data change may require code maintenance to make it work again.

So today I'll discuss Number 3 - which  I have found to be the most easily created and easily used by clients - giving them the power to choose, customize, and print all in one place.

But... PivotCharts don't handle dates!

I hear all the Excel frustrations of thousands of staffers across the world yelling right about now.  Yes and No.  PivotCharts do handle dates just fine, but they treat them like any other category - they come without assuming any missing data.  For example, if you have these data:

You will get four data points, equally spaced and in-order.  The months and years will show up as such (i.e., you can lump them into quarters, or years), but the order and count will be as they are in the source without March being assumed.  Like this:

For very small data (<10 data points) it's easy to manually get around this by inserting the missing dates.  For example, in the series above, you can insert March with blank data and get a proper chart:

Great! Problem solved for micro-data.  What happens when you have data that is really far apart in time or 10's, 100's, or 1,000's of data points or more?

Solving the Linear Date Problem

To solve the problem of inserting a lot of blank dates to create a linear time-series in PivotCharts we can use Excel's Data Model, a Date Table, and a PivotTable.  

The Date table will provide us with every date included in our dataset (or more if you want) while the PivotTable will allow us to tabulate all of those dates, including blanks with a few tweaks to the default settings.  Then we can plot it!  The worksheet used in this example can be downloaded here.

Here's how (Disclaimer: I'm doing everything in Excel 2016):

Step 1. Load your data to a query

  1. Select all of your data
  2. Go to the "Data" Tab
  3. Under "Get & Transform", click "From Table"
    1. If you have headers, check the box "My table has headers"
    2. Click "OK"
  4. The data should now be loaded into a Query.
    1. You can also load your data from an external source using a Query - see previous blog posts:
      1. https://www.rwalker.info/blog/2016/10/11/excel-power-query-and-json-real-world-example
      2. https://www.rwalker.info/blog/2016/10/28/excel-power-query-to-retrieve-agol-attribute-tables-with-dynamic-query-parameters
      3. https://www.rwalker.info/blog/2016/11/22/excel-power-query-to-retrieve-agol-attribute-tables-with-dynamic-query-parameters-part-2
  5. Check that the column representing your date is setup as a "Date" column in the query.  If not, you can change it.  Note that if any value in this column cannot be converted to a date then it will give an error on that row.
  6. Click "Close and Load To..." (this is very important!).  
  7. On the next form, click "Only Create Connection" and "Add this data to the Data Model"
  8. Click Load

Step 2.  Build the Date Table and Data Model

Now that your data are loaded to the Data Model, we can create a relationship with a Date Table.  This allows us to plot all available dates in our data range later.

  1. On the "Data" tab, click "Manage Data Model"
    1. You should see your data here.  If not, go back and check over Step 1 again.
  2. Click the "Design" tab.
  3. Click "Date Table" then "New..."
    1. This will create a date table that covers all dates in your data set by default, but you can adjust the beginning/ending dates if you like.
    2. Side note: Date Tables are super useful for PivotTables and PivotCharts just to get different formatting options quickly also. They are also very useful for knowing the day of a week something happened when doing analysis of weekday vs weekend, for example.
  4. Click "Create Relationship".  For Table 1, choose "Calendar" and select "Date" column.  For Table 2, choose your data and select the column representing your date. Click "OK".
  5. Close the Data Model.

Step 3.  Create PivotTable and PivotChart

  1. From the "Insert" tab, click "PivotTable"
  2. Choose "Use this Workbook's Data Model" and "New Worksheet"
  3. Build your PivotTable using the "Date" column from your Date Table (e.g., "Calendar").  Fill in your values and columns per your data.
    1. In my example, I use the configuration shown to the right.
  4. If you've done everything right to this point, you'll have a PivotTable, but some dates are still missing.  But here's the real magic!  PivotTables have an option to show rows with no data!
  5. Right-Click on the PivotTable and select "PivotTable Options".
  6. Go to the "Display" tab.
  7. Check "Show items with no data on rows".  Boom!  All dates show up across all of your data and between your data!
  8. Now create the PivotChart. 
  9. On the "Insert" tab, choose "PivotChart".
  10. Select the type of chart you want (I chose Line).
  11. Do some cleanup:
    1. Right click the chart, choose "Select Data".
    2. Click "Hidden and Empty Cells".
    3. Choose "Connect data points with line"
    4. Turn off Field Buttons.  With the PivotChart selected:
      1. On the "PivotChart Tools", then "Analyze".
      2. Click "Field Buttons" to turn them on/off.
    5. Adjust the axis, fonts, colors, etc. to your liking.
  12. Now, you can apply a slicer to the fields or a time-slider to the date and provide real power to your clients for some in-depth analysis!

Conclusion

Excel is still a very powerful, local tool that is rarely used to full capacity.  Using tools like Power Query, PivotTables, PivotCharts, and the Data Model allow for some very good, quick data analysis from a variety of sources without resorting to more complex data solutions like web applications or custom programming.  

I hope that this tutorial helps you better use Excel and shows that PivotCharts can create Time-Series charts with actually very little effort.  With practice, this workflow will take mere-minutes.

Questions, clarification, comments?  Leave them below!