Advanced Features In Microsoft Excel 2016
Microsoft Corporation has added a new feature in their latest MS Excel version and also they have made some advancements to their existing features that is included in Microsoft Excel 2016 version. By introducing these new features and improvisations, the software giant intends to help users in streamlining their data analysis. This article gives you information on how to surface time-based insights within your data and navigating through pivot-charts using drill-down buttons.
Time Grouping Feature in MS Excel 2016
This is a new feature that has been included in the MS Excel 2016 version. This feature allows users to build a pivot-table with the date and time columns. This feature automatically detects time-related fields in the pivot-table and groups them on user behalf. Additionally, the grouping feature also creates a date and time column and populates the pivot-table with information automatically. This feature saves a lot of users time and gives the user the privilege to start their data analyzing job instantly. The drill-down buttons enhance navigation speed across pivot-charts.
Excel users make use of pivot-table grouping feature in many business scenarios. When this feature is used in a data model pivot-table, it adds relevant date and time columns like [Date [Year], Date [Month], and Date [Quarter]] to the table that is grouped in the model. The advantage of using this feature in the data model pivot-table is that it can be used with other data models such as Power View and Power BI. The date and time column are automatically added in accordance with the date and time format present in the data column. For instance, if the date information is available in days, then the columns that are grouped will be for months, quarters and years. Once the date and time columns are included in the model, it will help the user in analyzing the data available in various time granularities and it also provides some additional insights. It is also possible to change group columns using the regular interface. For this, you have to right-click on the date column and select group option.
Users who are not comfortable working with the automatic grouping feature can just use Undo option to revert back the changes. When the user selects “Undo” option for the first time, then the columns that were automatically added will be removed from the Rows drop zone and when the user uses “Undo” function for the second time, it will remove newly added columns from the model. If these tables are used by another pivot-table, they cannot be discarded from the model. The other way of doing this is by right-clicking and selecting the “Ungroup” option or they can just disable the automatic time grouping option.
Drill-down buttons in PivotChart
In the new version of Microsoft Excel, the software giant has made some improvisations by including pivot-chart drill-down buttons. Earlier versions of MS Excel also supported double click on pivot charts that allowed the user to drill-down to the next level of the selected element, but it did not allow the user to expand all the objects in the pivot-table to the next level. But in the new version this problem has been addressed with the inclusion of drill-down buttons. When the user selects (+) plus or (-) minus symbol, they will be able to drill down to the next level of all elements in the charts with a single click. This functionality can be added to any hierarchy or set columns in the Rows drop zone on the pivot-chart. The user must make a note that if the pivot-table is created in the earlier version of MS Excel 2016, then they will not be able to see the drill-down button unless and until they enable them on the Analyze button. The user can also remove these drill-down buttons from the chart developed in MS Excel 2016.
Using Time Group Fields In Power BI
The date columns can be reused in power BI. The user can notice that the month column added to the data model in the new version using the automatic grouping feature is in the format of the calendar (Oct, Nov, Dec...). Listed above are a few enhancements that the US-based software giant Microsoft has incorporated in MS Excel 2016.