Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In Excel 2010: Pivot Tables in Depth, author Curt Frye provides comprehensive, hands-on tutorials on Excel PivotTables, including more advanced techniques such as using macros and the new PowerPivot add-in. The course shows how to connect and consolidate data sources to power PivotTables, sort and filter records, display data in a PivotChart, print tables and charts, and also introduces the DAX language for performing advanced summaries in PowerPivot. Exercise files are included with the course.
PivotTables make it easy to emphasize aspects of your data whether that's monthly performance or sales to a customer. The problem with PivotTables is that it can be hard to remember how to reconstruct a position during a presentation. Having detailed notes helps, but you still have to take the time to manipulate your worksheet. In this movie I will show you how to create a simple Excel presentation kit that uses macros to recall positions. I have decided to run my macros by clicking shapes that I have positioned in row one but you can just as easily add macro buttons to the Quick Access Toolbar.
I've already recorded and assigned macros for the Year_Month and Quarter_Company shapes. So now what I want to do is record a macro for the company by month and year shape and then assign it. To record that macro I'll go to the View tab and then at the right end, click the Macros button's down arrow and then click Record Macro. Then in the Record Macro dialog box I can type in the macro name and in this case, I will make it exactly the same as what appears in the shape. So Comp_Mnth_Year.
Now I can click OK and start recording. My first step will be to clear everything from the body of the PivotTable. So I will click Options and the Options contextual tab appears because a cell within the PivotTable is currently selected. then click Clear and Clear All. Now with the PivotTable back to zero I can create the position I want and in this case that will be Company then Month below that in the Row Labels area, then Year in the Column Labels area and Revenue in the Values area.
That's the position I want. So I can stop recording and to do that, click the View tab, click the Macros button's down arrow, then click Stop Recording. Now I can attach the macro to the shape. So I will right-click the shape I want, click Assign Macro ,and then click the macro name that I want to apply and that's the top one, Company by Month and Year, and when I click OK the shape is assigned to the macro. Now when I am giving my presentation and I want to recall a particular PivotTable position I can just click the shape that it applies to.
So for example, if I want to display the Year by Month position I can just click that shape and it appears. Quarter Company, there it is, and Company by Month and Year and also there is nothing about this position that's magical. In other words, I can manipulate the PivotTable and then click any of the macros and because I have Excel clear the PivotTable first, then it will re-create the position exactly. So let's say that I create a filter on the Month column that displays only months that contains the letters JU.
So I will type in JU and I see June and July. Those are the only two months and I'll click OK. Now if I were to click the Year_Month shape to run that macro, Excel clears the PivotTable and then recreates the position that I wanted. Recording your PivotTable positions as macros makes it much easier for you to display the data you want. Rather than worry about how to recreate a position you can focus on your message.
There are currently no FAQs about Excel 2010: Pivot Tables in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.