Learn how to use PivotTables to summarize, sort, count, and chart your data in Microsoft Excel 2016. Author Curt Frye shows you how to navigate the complexity of PivotTables while taking advantage of their power. This course shows how to build PivotTables from single or multiple data sources, add calculated fields, filter your results, and format your layout to make it more readable. Plus, learn how to create PivotCharts to visualize your data, enhance PivotTables with macros, and use the Data Model feature to build PivotTables from related tables. Instructor. Curt Frye is a freelance course developer and writer.
Excel for Mac lets you apply a general filter option, which then lets you customize the filter to meet your needs. When you choose a main filter type, you hear the Custom Filter options available for that specific filter. Since the release of Excel 2016 for Mac, customers have given great feedback about its ease of use and the benefits of the new features. We want to highlight the Excel 2016 for Mac features that customers rave about and share some tips and tricks that could save you a ton of time using the most.
He has developed more than 50 online courses on topics including Microsoft Excel, Tableau, Mathematica, and social network analysis. He has also written more than 40 books, with recent titles including Microsoft Excel 2016 Step by Step and Microsoft OneNote 2016 Step by Step, both for Microsoft Press. In addition to his writing and course development, Curt is a popular conference speaker and entertainer. His programs include his Improspectives® approach to teamwork and creativity, analyzing and presenting data in Microsoft Excel, and his interactive Magic of the Mind show. By: Dennis Taylor course. 1h 5m 11s.
44,252 viewers. Course Transcript - When you filter a pivot table Excel indicates that the field is filtered by placing a filter icon in the body of the pivot table and also in the pivot table field list.
Unfortunately, those icons are small and hard to see. And give no indication of which values are included or excluded from the filter. In Excel 2016 you can filter your pivot table by using slicers. Which graphically indicate which values are included and excluded by a filter. I will show you how to work with slicers using the slicers Excel sample file that is a workbook you can find in the chapter three folder of your exercise files collection. To work with slicers, click any cell in your pivot table. And then on the analyze contextual tab, click the insert slicer button.
That's in the filter group. The insert slicers dialog box appears. And now you can check the box next to any field for which you want to display a slicer. In this case I'll just do room type. So I'll check the room type box and click ok. Excel creates my slicer and I can drag it like any other shape, over to the side.
And you can also see that because the slicer is selected the pivot tables field task pane has been hidden for the time being. The slicer displays the field name, which is room type, and also values that are currently displayed.
Those are typically a darker color verses white or another light color based on the formatting that you apply. If I want to only display values for the Cambridge room type, I can click the Cambridge pill. That displays only Cambridge, and we have hidden Piccadilly, Oxford, and Westminster. If I want to switch to Oxford, I can click that item and it's displayed. One thing that's new, there's a new feature in Excel 2016, that allows you to select multiple items at a time without using your shift key or control key. On the title bar of the slicer, click the multi-select button, or press alt + s, and now you can select multiple items by clicking them individually. So I can do Oxford and Cambridge.
![Apply Apply](/uploads/1/2/5/4/125408500/536501201.jpg)
If I want to remove an item from a multi-select slicer filter, I can click it to deactivate it. So if I want to hide values for Cambridge, I can click the Cambridge item and it's removed from the pivot table. If I want to turn off multi-select I can just click it's button, and now clicking items individually switches to that item instead of adding it to the list of values that are displayed. If I want to clear the slicer filter I can go to the title bar and click clear filter or press alt + c, and if I want to get rid of the slicer entirely, I can right-click the title bar of the slicer and click remove room type. And of course the name to the right of your move will reflect the field that you created the slicer for. Filtering your pivot tables using slicers helps you and your audience visualize which values are included and excluded from your filter.
Slicers work best for filtering fields with 20 or fewer unique values. Especially if your pivot table takes up more than half of the screen after you apply the filter. Practice while you learn with exercise files. Watch this course anytime, anywhere.
Course Contents. Introduction Introduction. 1.
![Apply Filters In Excel For Mac Apply Filters In Excel For Mac](/uploads/1/2/5/4/125408500/851075385.png)
Creating and Pivoting PivotTables 1. Creating and Pivoting PivotTables. 2. Summarizing PivotTable Data 2. Summarizing PivotTable Data. 3.
Sorting and Filtering PivotTable Data 3. Sorting and Filtering PivotTable Data. 4. Formatting PivotTables 4. Formatting PivotTables.
5. Applying Conditional Formats to PivotTables 5.
Applying Conditional Formats to PivotTables. 6. Creating and Manipulating PivotCharts 6.
Creating and Manipulating PivotCharts. 7. Printing PivotTables 7. Printing PivotTables. 8. Manipulating PivotTables 8.
Manipulating PivotTables. 9. Creating PivotTables Using the Data Model 9. Creating PivotTables Using the Data Model.
Conclusion Conclusion.
Hi, I have locked a worksheet (with certain cells unlocked) and need other users to be able to reapply the filters I have set when they enter data in the unlocked cells. I was hoping they could click on 'Home' (ribbon)- 'Sort & Filter' - 'Reapply', but this option is greyed out even if I ensure that the protection settings allow users of the sheet to 'Sort' and 'Filter'. As soon as I remove protection, the 'Reapply' option becomes available. Is there anyway to make it available when the sheet is protected? Any help much appreciated! Cheers, Nick.