This limitation is resolved in slicers as you are able to move them around as shown in Figure 11.įigure 11: Slicers in different positionsĪlthough since its release in Office 2010, slicers continue to be located in the INSERT menu over the releases, they have evolved in terms of graphical icons as well as functionality. Some of the frustrations with traditional report filter in Excel is that you cannot move the positions of your report filters.įor instance, if you try cut and move the filters in rows 1-3 shown in Figure 9 to be underneath the grand total row or delete row 4, you will run into a cells locked error message as shown in Figure 10. Much of report development has to do with working on the look and feel of the report. Immediately after you have clicked the OK button to save your Slicer settings, the SIFISOTEST item disappears from the slicer list as shown in Figure 8.įigure 8: Slicer items without SIFISOTEST fruit This is achieved via the Slicer Settings property as shown in Figure 7.
However, this works differently in the traditional filter as it can be seen in Figure 6 that there is no convenient way to differentiate items that do not have data.įigure 6: Updated items of in fruit filterĪnother advantage of slicers is that they allow users to exclude filter items with no data from showing up in a slicer. Thus, report users can intuitively see that SIFISOTEST has no data.įigure 5: Updated items of in fruit slicer It can also be seen that because there are no transactional data associated with this new dummy fruit, the background colour of the SIFISOTEST fruit is different to other items.
Once we have refreshed the pivot report, we can see in Figure 5 that SIFISOTEST is now part of items of our fruit slicer. Say for instance, we refactor our dataset and add a dummy type of fruit called – SIFISOTEST, as shown in row 13 of Figure 4. On the other hand, however, a slicer not only indicate the presence of filters (as highlighted in Figure 3), I can also see the selected filter items.Īnother benefit to using slicers is that you can visually indicate items that have no data. This is less frustrating because dataset from my fictitious scenario is small, but if you are referencing datasets from production systems – you are more likely to scroll down the list just to see the selected items.įigure 2: Multiple Items in Traditional Excel Pivot Report Filter In order to identify the items, you would have to click the dropdown, as shown in Figure 2. The screenshot shows that there are filters in the report but there is now way of knowing the actual items except for that there are (Multiple Items).įigure 1: Traditional Excel Pivot Report Filter For instance, Figure 1 shows my fictitious fruit sales report by total quantities of fruits purchased per customer. Some of the negative feedback relating to my excel reports had nothing to do with my report development but limitations of the tool in that users were usually frustrated when attempting to identify items that they have chosen as filters. Some of these advantages can be summarised as follows:Įasily visualise items you have filtered on As per the explanation here, slicers have several advantages to the traditional filtering approach that has long existed in Excel Pivots. Since its initial release in Office 2010, slicers have always been part of the excel reports that I produce for my clients.
#Hide slicer in excel upgrade#
For some people, this may not be a sufficient reason to upgrade to Office 2016 but for developers of business intelligence (BI) solutions, this new feature further enhances the experience of consumers of BI solutions. Whilst researching for the article Report filtering: Excel slicers vs SQL Server Reporting Services (SSRS) parameters, I discovered a new Excel Slicer feature in Microsoft Office 2016 that allows users to select/deselect multiple items without having to hold down the control ( Ctrl) keyboard key.
0 kommentar(er)
