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

Lecture description

GETPIVOTDATA Formula in your Microsoft Excel Pivot Table - Intro

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 July 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] Data is a formula that uses the pivot table to create customized reports that give the user more flexibility. It uses the pivot table as its engine to spit out numbers based on the user's needs. There are certain advantages of using a pivot data formula. You can produce a report to your liking. So you're not limited to the pivot type of formats. When the pivot data source changes then all you got to do is refresh the pivot table and your report will update as well. You can also former Europe for an upon refresh and your pivot table. You will never lose its formatting and finally you can add extra columns for business metrics that are unable within a pivot table. There are lots of people that don't use to get pivot data formula. It's because they don't know the power that it can have. The reason is that most people actually go outside the pivot table and try to do a quick some formula. For example 2013 plus 2014 like this and when I try to scroll down then they get the same number. Then I look at this formula and assign Well it's a pivot data. I don't like it. I don't understand that. So I'm not going to use it. We just fair enough but I'll show you ways where you can use the pivot data to enhance reports. It's pest control said to get out of there. Now to activate the pivot data you could click in your pivot table good options and options from the dropdown arrow. Choose generate a key pivot data that's ticked it means it's on if you uncheck and you click anywhere inside your pivot data then you get a cell reference. If you want to use pivot data make sure they selected. So let's get a number from within our pivot table and press enter. Now let's go to our function in here. Just click anyway there and we can move this around. Here we have it up here. Okay. Now if you wanna get the explanation of get pivot data is click on that and you get the Excel help you get the details but the function and what it does now the data fields these are the values that you want to return. For example some of sales count or average name here. It gets the sales which is the sum of sales here. So it's the sum of sales that we are showing. And the second argument is the actual pivot table. So in here you can click anyway the pivot table but we usually click on the top left hand corner. Now the third argument this is the field name. So we're looking at a salesperson we have here a salesperson. And we also have recorders. So the full name is first of all the salesperson. And then within salesperson we have the item which is in right. So we've selected sell these 12. So it's in right. As a salesperson over to the second field which is the financial year which is up here. And the item within the financial year is 2014. Because we checked in there. Finally the third field is a sales quarters. So we had the sales quarters in a row labels that item 3 we have the actual sell that we've chosen relates to Q4. So it puts it in that order. And you can put up to one hundred and twenty six different combinations there. Those press enter and we'll get our value out there. And the power that comes with a pivot data formula is with the item numbers we can actually reference them to a cell. So instead of say 2014 we can change it to 2013 and see what happens the value changes to 670. This changed Q4 to Q3. It changes to 6 24. And finally instead of in right. Let's put it to Michael. So it gets John Michael Lewis's 2013 sales. So based on this you can see how you can create a report in here where you can reference your items with your own custom format your own metrics and every time the pivot table gets updated or you go to these refresh and then your data gets updated here and I'll show you how to do this in the next chapters.