Category Archives: Excel

Filtering Pivot Tables by Timeline

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.

timeline

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?

***

Remove Duplicate Rows Instantly

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.

Remove Duplicate

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.

***

Which type of chart to use when? No worries. Excel will help you!

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.

recommended chart

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.

chart

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).