Working with PowerPivot and the Timeline slicer in Excel 2013/2016

Q. How come I can’t get PowerPivot in 2013/2016?

A. Between Excel 2010 and 2013, the PowerPivot changed. While most things are upward compatible, PowerPivot is not. You’ll need to download it from Microsoft. Try starting here. Before you go through that whole process, be sure you really need to do this.

Native to the capabilities in Excel 2013 and 2016 is the data model. This component of PowerPivot (2010) allowed us to analyze multiple related tables in a Pivot Table or using one of the new visual elements like Power View. Now, you can do this with regular Excel, and from just about any data source, databases, other workbooks or the web. If you were using the more advanced functionality of PowerPivot, then you may need the add-in. To get it, you’ll need to use the correct version of Excel. Here are 2013 versions that will allow PowerPivot:

  • Office Professional Plus 2013
  • Excel 2013 stand-alone version
  • Office 365 ProPlus via Office 365

For 2016, the following versions allow and/or include PowerPivot

  • Office 365 ProPlus, E3 and E4
  • Office Professional 2016
  • Excel 2016 stand-alone version

Q.  I have dates in my source data, but I can’t seem to use the Timeline slicer. What am I doing wrong?

A. The timeline slicer only works with actual date data. Sometimes, data may appear to be valid dates, but are instead formatted text. For example, DEC 12 2016 may appear to be valid, but is likely actually ‘DEC 12 2016. The easiest test is to use the Ctrl+` (~ key) shortcut. If it turns into a serial number date, it’s a valid date. If it remains intact, then it’s not. A Timeline slicer will offer you a choice of validly formatted fields to add to a timeline. If you don’t see any, check the formats. You may need to apply Text to Columns and some conversion functions to get your dates in the right format.

For example, if you performed a Text to Columns to our example, you’d end up with DEC in column A, 12 in column B, and 2016 in column C.

Then, you’d need to apply a long if statement, or VLookUp with a Month Name/Month Number table to return 12 for DEC. Once you have 12 in column A, 12 in column B, and 2016 in column C, you could use the Date function in a formula to create this formula =DATE(C1,A1,B1). This would return 12/12/2016.