While many of the updates for analytics that Microsoft has released recently have been related to Power BI, there are a few features in Excel 2016 that are worth calling attention to for data analysts.
The most critical may be automated time grouping. This allows an analyst to take any date field, and select it to be grouped by years, quarters, and months automatically when formatted in a pivot chart or table. This is a great formatting leap forward, and makes it significantly easier to pull data from Hubspot and quickly roll-up a pipeline or forecast, and return it to key business users with the ability to drill down from quarter to month to day. Here’s a quick screen shot of the formatting –
Setup was as simple as right clicking on the close date field and then selecting to “group”, and highlighting the level at which the grouping should occur –
A second interesting feature for analysts working with Hubspot is the ability to drill down from within pivot charts. This shows up as a +/- icon at the bottom of pivot charts and allows an analyst to enable the sales manager to navigate the data via the chart and explore the underlying data. If you put the two features together you can provide high level analysis in the form of a pivot chart, and allow sales management to drill down into dates or other data categories. Here’s a simple example of both a pipeline chart, and a pipeline chart expanded on an automated date group –
…..and the same data expanded to month view
Just a couple of quick highlights of features in Excel 2016 that make the Hubspot data analyst’s life a bit easier.