Different pivot table views macro

MyExcelOnline John Michaloudis
A free video tutorial from MyExcelOnline John Michaloudis
Chief Inspirational Officer at MyExcelOnline
4.4 instructor rating • 10 courses • 77,263 students

Lecture description

VBA Macros in your Microsoft Excel Pivot Table - Different Pivot Table views

Learn more from the full course

Pivot Tables, Power Pivot Tables, Power Query & Power BI

5 Course Excel Bundle: Microsoft Excel, Pivot Tables, Power Pivot Tables, Power Query & Power BI (Excel 2007-2019, O365)

18:07:15 of on-demand video • Updated October 2020

  • Become more PRODUCTIVE at using Microsoft Excel which will SAVE YOU HOURS per day & ELIMINATE STRESS at work!
  • We teach you how to use Microsoft Excel with CONFIDENCE that will lead to greater opportunities like a HIGHER SALARY and PROMOTIONS!
  • Increase your Microsoft Excel SKILLS and KNOWLEDGE within HOURS which will GET YOU NOTICED by Top Management & prospective Employers!
  • COURSE #1: Learn ALL the Pivot Table features: Slicers, Pivot Charts, Filtering, Grouping, Sorting, Conditional Formatting, Macros, Dashboards, Data Analysis, GETPIVOTDATA, Calculated Fields, Formatting plus MORE!
  • COURSE #2: Learn Power Pivot, DAX Formulas, Measures, Calculated Columns & Table Relationships!
  • COURSE #3: Learn Power Query (Get & Transform), import & transform your data, consolidate from multiple workbooks, folders, worksheets plus MORE!
  • COURSE #4: Learn Power BI and create interactive Dashboards that tell a story about your data!
  • COURSE #5: 333 Excel Keyboard Shortcuts explained, covering Formatting, Ribbon, Formulas, VBA, Pivot Tables, Power BI plus MORE!
  • 7 COURSE BONUSES: Excel PDF Cheat Sheets, 101 Ready Made Excel Templates, plus MORE!!!
  • ADDED BONUS: This entire courses can be DOWNLOADED for offline viewing on the Udemy mobile app (Android & iOS)!
  • DOWNLOADABLE Excel workbooks so you can keep on your desktop & practice!
  • Test your skills with various real life Quizzes & Homework Practice Activities & Assignments!
  • Personal Excel support from Professional Excel Trainers with 30+ Years of combined Excel Training!
English [Auto] When record a macro where we're going to get different pivotal views depending on the button. But when pressed. Now the trick to this is that the first step of the macro is to clear the pivot table and the second step is to create the pivot table. So let's go and record our first macro and call it region by quarter and press. Okay so the first step is go to the options tab. Clear. Clear all. The second step is to create the regions by quarter. So grab the quarters reach it and then we'll grab if sales twice into the values area. Net from the dropdown arrow which is value for settings and then we put in here on average and press. Okay. Finally select everything go to the Home tab and it's from a comma give the decimal places and then we can go to develop and stop recording. That's our first macro then the second macro is gonna be called the year to date sales by month. So press record and call you did day sales by month and press. Okay. The first step once again the options. Clear Clear hold. The second step is to create the pivot table GRAB THE YEARS IN A ROW Labels sales month as well and dropping the sales twice into the values area from the drop down narrow value fuel settings and show values as from the dropdown box here. We're going to show values as a running total in and the base field will be sales month and the custom name will change for year to date. And press okay. And then once again click in here go to the Home tab and customize it a bit like this developer tab stop recording the third macro is what we call top 10 channels. CHRIS Okay options. Clear all grab out regions and our channel partners on the left and our sales in our values area and then from the channel partners can filter out by Valley filters and top 10 and press. Okay. And then finally in the pivot table we just right click and so largest to smallest angle to develop a tab stop recording. So we've done our three macros now all that's left to do is to insert the shapes and assign the macros to the shapes. So let's do this insert shape in there. And we've got one set in there and let's choose the color of the shape like this. Now hold down your mouse okay and then press control shift and drag down with your mouse click of the mouse cake. Then while the controls shift case still being pressed click the mouse drag down and then click of the mouse case or created three similar shapes. Now let's name them region by quarter sales an average year to date sales by month and then top 10 channels. Okay. Now let's click in one here price control all so we can format the shapes and then in here we can just choose a color I like color like this and press old and we can just make it a little way bigger like that. Okay. Escape. So now let's assign the macros right quick aside macro and in when the cheese region by quarter and then the second one is see to date sales in the third one is the channel partners. Now all that's left is to press the button. Sit back and enjoy the magic of the macros. So we have the region by quarter sales and average the other day sales by a month and then the top 10 channels by Richard sorted from life's smallest.