Project Botticelli

Selecting, Filtering, and Sorting Data in Excel Purchase this course

31 October 2013 · 2489 views

Essential Enterprise BI Skills

Selecting, Sorting, and Filtering Data with Excel BI

There are many ways to select data in Excel: basic and advanced filtering, range selections, pivot table filtering, date and context filtering, sorting, ranking, grouping, slicing (including timelines), creating user-defined sets, and advanced data navigation. If you want to do things faster and better in Excel, make sure you become proficient with all the different ways of selecting data by watching this in-depth, 48-minute video tutorial by Carmel Gunn, Data Specialist from Prodata, and follow her many hands-on demos.

Excel has basic filtering, letting you select single or multiple items, and ranges and advanced filtering, through the Pivot Field List. You can create label, value, and date filters. It is also possible to make those filters take advantage of the underlying multi-dimensional model to create more complex reports. This can help you with time intelligence, like Year-to-Date (YTD), or to filter at different levels of a hierarchy.

Value filters can be used in many ways, for example for selecting top 10 or bottom 10 products, or any other thresholds. Date filtering has been enhanced in Excel 2013, and there are now many pre-defined date filters, like “this year,” “last year,” or YTD. Context filtering lets you keep only the items you are interested in working with, or to hide items that you wish to suppress, perhaps because they are distorting the results, such as totals, as shown in the demo.

Sorting involves arranging your data in a way that is logical to the business user, and this can often mean that data needs to be sorted following business logic, rather than alphabetically or numerically. You may even need to sort it manually, for example for financial data, and lay it out like in a book of accounts. Ranking enables us to weight data in order to present it in the way we require, for example to display measures, weighted relative to totals. Note that when right-clicking to bring up the ranking options, you must right click on the value in the pivot table, and not on the label.

Slicers are a visual aid to filtering data, more accurately showing filters which have been applied. They have been specially designed for saving worksheets to SharePoint, Office 365, SkyDrive and they work well with mobile devices. Timeline, introduced in Excel 2013, enables slicer-type functionality for dates. Make sure to update report connections for the timeline, in the same way as it is done for a slicer, to connect the timeline to multiple pivot tables on the same report.

Sets are groups of data, where the group can be based on a business rule or a requirement, eg. “must stock products,” or top-selling products. Advanced navigation features are available in Excel via the Analyse ribbon: drilling up and down a hierarchy, grouping, and collapsing data, and data exploration, using the new Quick Explore feature.

Log in or purchase access below to the premium version of this content.

Purchase This Course or Full Access Subscription

Single Course

$200/once

Access this course for its lifetime*.
Purchase
Subscription Best Value

$480/year

Access all content on this site for 1 year.
Purchase
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.

Online Courses