A new way of Filtering Pivot Tables by time is now available (2013) – called Timeline. It allows filtering of one or more pivot tables by years, months and quarters – without the need to have these fields in the raw data. You just need one or more date type of fields.
Filtering Pivot Tables using Timeline
Pivot Table Tools – Analyze tab – Insert Timeline. This window appears. You can move it around and adjust it as required.
Clicking on the title of the Timeline shows a new menu called Timeline Options. In this menu there is a very useful option called Report Connections. Using this option, you can connect the SAME TIMELINE to multiple pivot tables (even across sheets). This way, the timeline filter affects all pivot tables.
Along with Slicers, timeline offers a new and easy way of creating interactive dashboards.
When the Excel file is stored on OneDrive or SharePoint sites, the report is opened on a browser by default. The timeline and slicer interactivity will work even on the browser version of the report. It works with all types of browsers which support HTML 5.
Next time you create a pivot table – why not add a timeline as well?
Just one button to press. Data – Remove Duplicate.
Data should be a table (or like a table – column headings with data below). If it is a table choose Table Tools – Remove Duplicates.
By default, duplication is checked for ENTIRE ROW.
Remove checkboxes to check for duplicates for specific fields only.
Be careful. Duplicate rows are DELETED.
This is unlike Advanced Filter – Unique Rows only – where duplicates are HIDDEN.
For large data – Power Query – Remove Duplicates is infinitely more powerful and faster. We will cover it in a separate article.
Lot has been written about it. With Excel 2013, Microsoft did the smartest thing. They added a chart type called Recommended Chart. Now Excel is smart enough to look at what data you have selected and choose the most appropriate chart types for you.
It shows the preview of those charts using YOUR data.
Final decision, of course, is yours. So you choose the one which depicts the information in the most relevant way. Also notice a feature called Recommended Pivot Tables.
The most important thing is to STOP using the habitually familiar chart and explore what recommendations are given by Excel. It is not about making the chart which someone is asking for.
It is about finding out every piece of useful information from available data and to use it to your advantage. By the way, that THOUGHT PROCESS is called Business Intelligence!
It is also a part of Quick Analysis feature (which we will cover in the next article).
Press Ctrl key and drag the original sheet to the desired location. That’s it.
It is a powerful but often ignored feature of Power Pivot.
It allows you to describe the meaning of the data column – independent of its field name.
Estimated reading time 8 min
Continue reading Understanding Data Categories in Power Pivot