Skip to main content
Skip to main content
Microsoft 365

Time grouping enhancements in Excel 2016

Excel 2016 introduces new features and improvements that help you streamline your data analysis. Today we will walk through the new options for building PivotTables with a Date/Time column and show how to surface time-based insights in your data. Additionally, we will help you take advantage of the ability to navigate your PivotCharts using drill-down buttons.

Automatic time grouping

Automatic time grouping, a new feature in Excel 2016, helps use time-related fields in a PivotTable by auto-detecting and grouping them on your behalf. The automatic grouping creates new Date/Time columns and automatically populates the PivotTable in one action. This allows users to quickly start analyzing their data across different levels of time with drill-down capabilities.

PivotTable grouping is used frequently by customers in many business scenarios. Implementing grouping for Data Model PivotTables allows for grouping to be used in conjunction with the power of the xVelocity engine and is a key feature for making Data Model PivotTables a replacement for native ones in the future. When used in a data model PivotTable, Time grouping adds relevant Date/Time columns such as Date (Year), Date (Quarter) and Date (Month) to the grouped table in the model; these columns could then be reused with other user endpoints of the data model, such as PowerView and Power BI.

Time grouping enhancements in Excel 2016 1 - words

Time grouping columns in the model.

The Date/Time columns are automatically added according to the Data column date/time granularity. For example, if the date data is in days and is for more than a year’s duration, then the added grouping columns would be for months, quarters and years.

Once the Date/Time columns are added to the model, not only can you analyze your data over the different time granularities it entitles, but you may also find additional insights. For example, to get a monthly year-over-year comparison of your data, drag the Date (Year) column over from the ROWS drop zone to the COLUMNS drop zone while keeping only the Date (Months) column in the ROWS drop zone.

Auto-grouping for a data model PivotTable also means that for the first time in Excel you are able to aggregate data larger than one million rows.

Right-click Group and Group dialog.

Changing the Group columns is always possible through the usual user interface. In the PivotTable, right-click the Date column and select Group.

The Group dialog is where you can define other columns to be added or removed from the time group. You may have noticed that “Starting at/Ending at” and “Number of days” are greyed out; this is because we do not support these settings for data model PivotTables.

Don’t like automatic grouping? Undo it just like you would any other applied change. The first undo (Ctrl+Z) removes the auto-added columns from the ROWS drop zone. The second undo removes the newly added columns from the model, so you are back at square one. In case these columns are already used in another PivotTable, then they will not be deleted from the model. Right-click Ungroup… is also supported and will act as if you have done the two undo operations, removing the group fields from the drop zones and the model. You can also turn off automatic time grouping.

When the model already contains a “Date Table,” we do not want to do time grouping, since the “Date Table” includes all required date columns already. So when building a PivotTable and dragging a Date column from a “Date Table” from the data model, automatic grouping would not apply.

PivotChart drill-down buttons

PivotCharts have always supported a double-click on a chart element to do a drill-down to the next level of the selected element. This is the same as clicking the plus sign on the PivotTable collapsed level. A tougher job is when you want to expand all objects in the PivotTable to the next level. This is where the newly added PivotChart drill-down buttons come in handy, as clicking the + (plus) or (minus) buttons allow you to drill down to the next level of all elements in the charts with a single click.

This functionality applies to any hierarchy or any set of columns in the ROWS drop zone of the PivotChart.

When you open a PivotTable created on a previous version of Excel, you will not see the new drill-down buttons until you enable them on the Analyze ribbon. It is also possible to remove these buttons from a chart created in Excel 2016.

Toggle viewing of PivotChart field buttons.

Reuse of time group fields in Power BI

The new Date columns really come in handy in Power BI. One noticeable value to users is that the Month column added to the data model through the Automatic Grouping in Excel is calendar ordered (Jan, Feb, Mar…).

Power BI - final

The new Date columns put to use in PowerBI.com.

What do you think?

We just went through the new time grouping functionality in Excel 2016. Try it out for yourself and feel free to add suggestions or vote for them if they already exist in the Excel UserVoice.

You may also like these articles

Image for: Family hanging out at home baking holiday cookies using Lenovo Ideapad Flex 3 to make holiday cards

New apps and features in Microsoft 365 for individuals and families this fall

Since we introduced Microsoft 365 to individuals and families earlier this year, we have continued to deliver new innovations across our apps and services to help you and your family save time and stay connected. See what’s new below. Locate your loved ones and build better driving habits with Microsoft Family Safety Stay connected even…

Image for: A man is using his Lenovo laptop like a tablet while sitting in a comfortable chair in a Modern office setting

Microsoft Productivity Score and personalized experiences—here’s what’s new to Microsoft 365 in October

As I reflect on an action-packed few weeks, I’m struck by how much work has evolved in these past months. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing…

Image for: Surface Pro X and Surface Arc Mouse inside on desk with Excel and search on screen.

Connect to your own data with more new data types in Excel

The evolution of Excel Excel is the ultimate decision-making tool. We’re amazed every day by the ways in which you, our customers, use Excel to make better decisions, leveraging the flexibility of the 2D grid and formulas to capture, analyze and collaborate on data. Up to this point, Excel has only had a couple base…