Tag Archives: Filtering

How to Filter on Blanks quickly

A simple but very useful method. All of us use AutoFilter in Excel. The dropdown shows unique values in the column. Sometimes we just want to see the rows with blank cells. Unfortunately, the (blank) item appears at the end of the list. If the data contains many unique values, you have to waste time and scroll manually to the bottom of the long list. Often, after reaching the list we realize that we should actually have unselected all the items first. Then you go up again! Pathetic. Here is a smart way to Filter on Blanks instantly.

Continue reading How to Filter on Blanks quickly

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?

***