"Deadline-Beating Blueprints Trusted by Intermediate and Advanced Excel Users"

Master Of PivotTables And PowerQuery

"Imagine...you become 101% comfortable with pivotTables And powerQuery!"

To Data Analysts, Consultants, And Managers Who Want To Get On Top Of PivotTables and powerQuery To Display Meaningful Data For Making Decisions...Without Having To Export Data From Excel To Other Packages, Without Writing Macros Or Advanced Formulas.

From: John Franco, Excel author

At my desk, Wednesday, 8:05 AM

Dear Excel friend,

Back in 2003, everyone was talking about PivotTables and I didn’t want to miss such a trending technique.

But this is embarrassing…

…from 2003 to 2006 I used PivotTables with limited success and impact because I used PivotTables without fully understanding them. Most of my PT reports where based on some templates I got from others. And by trial and error, I managed to display the meaningful data to help my boss make decisions.

I knew that I didn’t know how PivotTables really work. I feared complex data and complex scenarios and mostly…I feared others asking me for PivotTable help and realizing I was a PivotTables fraud.

But don’t get me wrong…

…I tried hard to understand PivotTables but failed miserably with PivotTables because I assumed that they were part of Excel. I only became 101% comfortable with PivotTables when I discovered its hidden abstract side.

I said then, “this is why I never got it right”. And the reason is simple. PivotTables are not Excel and Excel users are not trained to think different than Excel.

And since 2015 or so, I heard it everywhere…

Power Query, Power Query, Power Query!

Now I totally understand the power of combining both!

If you want to be 101% comfortable with PivotTables and Power Query , then this new program is for you!

Announcing my new Master of PivotTables (VIDEO program) – A 7-week PivotTables Mastery Blueprint.

This is the insider PivotTable knowledge John wishes was around when he was starting out with data analysis and presentation earlier in his career. Learning even one or two of these fundamental PivotTable concepts and techniques would have short-cut his own success by oh five maybe five years. Probably more.

What Are Others Saying About John Franco And The 'Master Of PivotTables' Program?

"I'm not frighted of pivot tables anymore!!!!"

"My main role involves working with an MS Access Database, and exporting details from that via queries/reports to spreadsheets for distribution to various sections of our company.

However, I have recently taken on producing a dashboard for our team, which is shared with the government as well as the business! At the moment the dashboard is made up of lots of worksheets which produce tables on a “Dashboard” worksheet. There is a lot of work manipulating the data to produce an easily readable, meaningful and understandable dashboard for varying levels of management.

The Pivot Tables Seminar has given me lots of ideas of how the dashboard can be better produced, and hopefully be more dynamic rather than a monthly task! I’m not a data analyst but find manipulating the data on a dashboard to be fun (as far as work is!).

I will continue to follow your seminars, and have learnt a lot - I'm not frighted of pivot tables anymore!!!!

I find your training extremely useful and helpful, thank you"

Lesley Forster, UK

"Categories and Data has been my big step forward."

I worked through the first half of one of the videos last night... What has really helped me is the point you make about the data items in the raw data being either Categories or Data. As soon as I thought about it, that made sense of a lot of my past experiences with PTs.

Obviously, my learning style is such that I need to “see” a pattern or organization in the data structure. “Categories and Data” slotted straight into that need. The box, fruit, and vegetables didn’t help me at all, but then I knew that idea of sorting and grouping from previous work on PTs in a past existence. But horses for courses, Categories and Data has been my big step forward.

And having the spreadsheet to play with, to try things out, has been great. It is much richer in Categories than anything I might have created for myself, and so makes it easier for me to see patterns emerging as I tick successive boxes on the right-hand side.

Many thanks for a good program.

Regards,"

Chris Reynolds
IT Support Officer, USA

"...You have opened my eyes."

"Thank you, John. You have opened my eyes."

James Gibbons

“…a different insight I am thankful for”

“That was a different insight I am thankful for. It does underline the difference between excel and pivot tables. The hist video is indeed an anchor for every user”

Jan D.

What Are You Going To Get In the 'Master Of PivotTables' program?

In this comprehensive Excel PivotTables program, you will learn:

  • How to do a PivotTable step by step, the guru way
  • The silent Excel behavior outsiders never notice, which every PivotTable guru should know to reduce anxiety during the analysis and presentation of long and complex tables. If you want to PLAY with big tables, then this is for you
  • The power of understanding and using Pivot Tables. You will see how you can be in control of your data, your work and so your life
  • The secret 7 PivotTables perspectives that will give you the right mindset to take advantage of this data analysis and presentation technology. Develop the mindset, concepts, and techniques that will make you truly understand Pivot Tables and win the “big tables” game. Go beyond the oversimplified how-to PT training tailored to the masses and get deeper into secret Pivot Tables and data analysis insights that many Excel gurus never unveil because they have a big ego to satisfy
  • How to gain experience with PivotTables in minimum time by learning the fundamentals, not just the how-to of dragging fields. If you master the PivotTables fundamentals, the rest will follow. Don’t be part of the horde of people doing painful trial and error, hoping their random “fields dragging and dropping” will produce the customized and impressive reports they want
  • Learn what kind of tables are suitable for Pivoting. Discover in what format the source data should be put to analyze it. If you want to take advantage of PivotTables, you must work with the right kind of tables
  • The two types of fields present in every source data. This simple concept will simplify the understanding of PivotTables
  • What the best locations for each field type on the PivotTable canvas are.  Never again get confused as to which fields to drag to what section
  • How to present the data that is relevant and show trends. Show multiple views easily. See data by date, subject, and unit at the same time
  • How to turn big tables into simple reports quickly and easily. Understand the fine points and tricks such as how to show less/more detail without trial and error, etc.
  • Simplify the creation of fully customized analysis and reports following proven PivotTable report blueprints. Discover how to exactly drag and drop the fields to show the metrics that you want such as top 10 products/students/projects/employees, total sales/cost per product and by quarter/month/week, etc.
  • How to keep the PivotTable formatting on updating (an entire module is focused on this). Learn the single command and the two techniques to keep the PivotTable layout and format between refreshes
  • Techniques to avoid Excel slowing down and locking up because of a large amount of data (an entire module is focused on this)
  • How to create Pivot Charts step by step
  • How to navigate an existing PivotTable report without messing it up
  • How to remove fields to simplify a PT report
  • How to drag fields to a PivotTable canvas without messing it up (creating wide unreadable sheets)
  • How to manage more than one PivotTable report in the same sheet or workbook
  • How to edit and format an existing PivotTable without breaking it
  • How to show multiple views of the same data source without increasing the file size
  • Learn the concepts through varied samples across many industries and purposes: finance & accounting, marketing & sales, engineering, HR, operations, education, government, etc.
  • And much much more!…

Here's how to get this PivotTables foundational material our community needed!

Eventually, you can figure out the abstract part of PivotTables on your own. Or you can take advantage of the 10+ years John’s already invested in hard work dealing with big tables and PivotTables in a corporate environment, researching and synthesizing his findings.

There is absolutely no sane reason why you should want to waste years of your own career attempting to cover this same ground.

It’s up to you.

If you can increase your confidence to put Excel PivotTable skills in your resume and get a new and better job or get a promotion, how much extra money would you earn each month, $300, $500 or more?

The MPT program won’t cost you $500.

The MPT program won’t cost you $300.

The MPT program won’t even cost you $200.

Today, you can start learning how to simplify the analysis and presentation of big tables for any project for just one payment of:

USD 157.00

USD 127.00

 

With a small investment, the PivotTable data analysis and presentation skills will pay you back again and again.

If you don’t learn how to simplify the analysis and presentation of big Excel tables, you will never be a full-fledged Excel user.

You risk nothing

If within 30 days of purchase, you follow the Master Of PivotTables (MPT) video lessons, practice at your own pace, and cannot understand and use PivotTables to quickly find and present the crucial patterns that are in your data, or if you simply didn’t like the material, just ask a refund and you will get your money back, no questions asked.

I have been selling information products online since 2011 to thousands of professionals from around the world.

Just contact us (use the subject ‘refund’). I will take care of it.

Click that button and take your first step to understanding and using PivotTables that will supercharge your data analysis skills, your earning potential, and your life.

To your data analysis success!

What’s different from other Excel PivotTable courses out there?

Most of the Excel PT courses are all about the dragging and dropping of fields or how-to tricks that only works for one situation.

The MPT program will show you the abstract part and the fundamentals that empower you to face any big table analysis challenge, whether you work in finance, marketing, accounting, HR, IT, retailing, health, education, engineering or any other industry.

In fact, an entire module is dedicated to mastering the fundamentals.

The MPT program will also show you the practical HOW-TO, step by step guidance also. And the practical side is always accompanied by the root concepts.

Other PT courses assume you know a lot of things. I don’t take anything for granted that you should know. I explain it the way it is done to a kid. You will learn complex concepts following simple analogies anyone can understand. The MPT program will take you gradually from basic concepts such as the 2 type of fields in the data source to advanced concepts such as how to create an exact type of report.

Who is the 'Master Of PivotTables' for?

Basic to intermediate Excel users will get the most out of this program because they will start the PivotTable journey with a strong foundation. The concepts are explained using simple analogies anyone can understand and the program progresses gradually from basic to more advanced concepts.

You can ask for a full refund if you find the course is not for you.

But if you are a seasoned PivotTable expert, you might use this MPT course as a refresher or for covering some voids you might have. Also, you can ask for a full refund if you find the course is not for you.

You are covered by my 30-day money back guarantee.

What do you get when you enroll in 'Master Of PivotTables' program?

After you enroll, you get instant access to:

50+ category rich datasets

Curated data ready for pivottables reporting.

40+ video lessons

Learn by watching step-by-step lessons

Forum

Post your projects on the forums and get feedback from me and other members on your same journey. Also, hone your skills by helping others in their Excel guru journey

User and password

Email with a username and a password to access the private members' area

PivotTable building blueprints

Make meaningful progress in reasonable time by following some proven blueprints that will get your PivotTable reports done

Email support

One year of email support

Life-time access

Learn at your own pace at any time. No need to follow rigid schedules

Free updates

Get free access to updated and new lessons, bonuses, for one year

Access anytime, anywhere

Access the members' area on any device (Android, iOS, Windows, Mac).

What else do you get when you enroll in the 'Master Of PivotTabes' program?

Bonus # 1 - Library of 50+ Category-rich Excel Datasets

Get immediate access to more than 50 curated Excel category-rich workbooks in xlsx format and organized into categories: finance & accounting, marketing & sales, engineering, HR, operations, education, government, etc.

Play with the pivotable-ready tables and apply the principles to further understand PTs.

Bonus # 2 - The PivotTable Mind Map

MasterOfPivotTables

Find inside a goldmine of ideas and notes covering the entire range of PivotTable topics. You can keep it as a reference, enrich the map with your own ideas and findings.

Bonus # 3 - How To Create And Customize PivotTables Using VBA

Find inside a gallery of VBA code to speed up and automate the creation and formatting of Excel PivotTables.

  • Learn how to create a Pivot Table from scratch using VBA
  • Discover how to add new row, column or page fields to existing PivotTables using VBA
  • Learn how to format a PivotTable using VBA
  • Learn how to loop across the PivotTable fields
  • Discover how to select any element of PivotTables using VBA
  • and much more

Bonus # 4 - How To Get The Source Data Ready For PivotTables

Learn how to consistently get your data in the best quality and format for PivotTables even if it’s poor quality or spread over different sheets and files.

This is a proven step by step PivotTable data preparation blueprint!

The best thing about a system is that it’s REPEATABLE. You can use this data preparation system every time and consistently get your data ready for PivotTables.

  • Discover the secret data psychology to look at data in a simplified way. With this simplified way to look at data (even at the most complex datasets), you will never again suffer for not having the different tables together because you will be able to easily uncover the underlying structure of your data even if you work with big tables or data is spread over different sheets and files
  • Know how a perfect dataset looks like. Find inside a clear picture of a table featuring the main elements of a perfect dataset. Visualizing the special layout of your data is key to getting it in the right structure for PivotTables. You will also develop the skill to ask for the right dataset. You can also send the pdf so others produce the data in the desired format
  • Discover the 4 possible scenarios every dataset fits in. This is, in fact, a comprehensive data success roadmap that will show you every possible scenario and how to solve them. You can use this data success blueprint to tackle complex data scenarios others are scary even to think about. And learn the concepts using rich illustrations and simple explanations
  • Learn how to merge tables from different sources using several methods such as lookup formulas, Power Query, etc.
  • Learn how to perform data cleaning using formulas, filtering, Power Query, and other proven tricks and techniques. Be among the 1% elite professionals and data analysts who are up to date with the latest technology such as Power Query. Put Power Query skills on your resume and get a better job or be considered for a promotion
  • Get access to a proven source data success checklist
  • Get access to datasets in xlsx format
  • and much more…

I’ve got to tell you, it is a lot more fun and fulfill ing when you know exactly what you’re doing in your data preparation process and with my PivotTable data success blueprint, you will know exactly what data appending, merging, unpivoting and cleaning means even if you work with big datasets with hundreds of tables and thousands of rows each.

Read what others say about my PivotTable Data Success Blueprint…

"WOW, the introduction- you are so right. The objects explanation is a masterpiece. "

“I read carefully the PivotTable Data Success Blueprint – WOW, the introduction- you are so right. The objects explanation is a masterpiece.

You are right in your Objects explanation. To think and understand about the goal of the report / the query that one received and to build the solution (the table that is the source to the PT) accordingly. One need to understand what is his main object and to build and organize the data around it.

As to the ways to approach the problem – obviously, if it is a one-time occasion, the manual appending and using the vlookup and then a pivot table is much faster. However, if the pupils’ attribution to an organic class and/or field of interest is dynamic, then using the Power Query is the chosen one. I still do not understand why I failed to import the 5 classes files to the PQ using “From Folder”, I had to load them one after the other the whole 5, doing the same 5 times.

In general, since I was a “Lotus 1-2-3-“ beginner, I was taught to think in tables, and the importance of data normalization. Therefore, whenever I see data, I start thinking of how to organize it in order to have the best PT out of it. So when I got the “5 teachers files” I knew that I need to gather it to one DB, and wanted to play with the new Power Query tool. Since I’m not that skilled with PQ, I faced some know-how barriers and your PT data success blueprint helped me with them.

I must say that in Israel there are many Excel Experts and some companies that teach Excel (for beginners, advanced, VBA etc) but I didn’t find any one with an approach like yours or Oz.”

Lior D.
Accountant, Finance manager Israel

You are literally a couple of hours away from becoming the PivotTable guru of the office!

FAQ

How much time should I devote to learning?

Each tutorial is like 30 min average. Each module/week has 5 lessons on average. So you would need something like 3 hours per week.

No pressure teacher. No rigid schedules. Study at your own pace.

Click here to get started

What payment methods do you accept?

All major credit cards including Visa, MasterCard, and American Express are accepted. We also accept PayPal, Amazon Pay and Wire transfer.

Click here to get started

When does the course start and finish?

The access to the course starts now and ends whenever you like. It is a self-paced online course. You decide when you start and when you finish.

The course site remembers your progress, so you can pick up right where you left off on any device.  That means you can study a few lessons at work, then continue right where you left off on your tablet or home computer.

You can access the course site from your work or home computer, tablet, smart phone or any other device with an internet connection.  

Click here to get started

How long do I have access to the course?

You have lifetime access to the course.

Click here to get started

What Excel version do I need?

You need Excel 2010+

Click here to get started

Can I access the lessons on my mobile device?

Yes, you can log in with your smartphone or tablet (Android or iOS) and read or download the material. If you want to manipulate the workbooks, you might need an Excel app installed.

Of course, you can also log in with your computer or laptop (Windows or Mac).

Click here to get started

Are there exercises for each lesson?

Some lessons have some suggested activities. They are not assignments but ideas and inspiration to explore the given concepts.

Click here to get started

Is this PivotTables program for me?

This course is for you if you work with large and structured data and want to get insights from it and present them.

Also, you should take this course if you have never tried Pivot Tables before, if you gave up learning PTs and want to understand Pivot Tables once for all, if you want to increase your understanding of the PTs fundamentals so you perform more advanced analysis and gain confidence to present your findings, if you are a consultant looking to get more solid at your skills so you can take new and better clients, etc.

Click here to get started

Master Of PivotTables – Success

The Truth About How To succeed with pivotTables

To Data Analysts, Consultants, And Managers Who Want To Get On Top Of PivotTables To Display Meaningful Data For Making Decisions.

From: John Franco, Excel author

At my desk, Wednesday, 8:05 AM

Dear Excel friend,

Back in 2003, everyone was talking about PivotTables and I didn’t want to miss such a trending technique.

But this is embarrassing…

…from 2003 to 2006 I used PivotTables with limited success and impact because I used PivotTables without fully understanding them. Most of my PT reports where based on some templates I got from others. And by trial and error, I managed to display the meaningful data to help my boss make decisions.

I knew that I didn’t know how PivotTables really work. I couldn’t get the results I wanted such as:

  • How to organize pivot data to get stats without so many subtotals 
  • How to view dozens of scenario summaries and totals via multiple filtering 
  • How to summarize the data by product and activity on a monthly basis
  • How to compare country data across time
  • …and so forth

I feared complex data and complex scenarios and mostly…I feared others asking me for PivotTable help and realizing I was a PivotTables fraud.

But don’t get me wrong…

…I tried hard to understand PivotTables but failed miserably with PivotTables because I assumed that they were part of Excel. I only started getting the results I wanted when I discovered its hidden abstract side.

I said then, “this is why I never got it right”. And the reason is simple. PivotTables are not Excel and Excel users are not trained to think different than Excel.

To keep serious Excel students and data analysts away from frustration and unnecessary trial and error, I packaged they keys to succeed with PivotTables in a new PivotTables program!

Announcing my new Master of PivotTables (VIDEO program) – A 7-week PivotTables Mastery Blueprint.

This is the insider PivotTable knowledge John wishes was around when he was starting out with data analysis and presentation earlier in his career. Learning even one or two of these fundamental PivotTable concepts and techniques would have short-cut his own success by oh five maybe five years. Probably more.

What Are Others Saying About John Franco And The 'Master Of PivotTables' Program?

"I'm not frighted of pivot tables anymore!!!!"

"My main role involves working with an MS Access Database, and exporting details from that via queries/reports to spreadsheets for distribution to various sections of our company.

However, I have recently taken on producing a dashboard for our team, which is shared with the government as well as the business! At the moment the dashboard is made up of lots of worksheets which produce tables on a “Dashboard” worksheet. There is a lot of work manipulating the data to produce an easily readable, meaningful and understandable dashboard for varying levels of management.

The Pivot Tables Seminar has given me lots of ideas of how the dashboard can be better produced, and hopefully be more dynamic rather than a monthly task! I’m not a data analyst but find manipulating the data on a dashboard to be fun (as far as work is!).

I will continue to follow your seminars, and have learnt a lot - I'm not frighted of pivot tables anymore!!!!

I find your training extremely useful and helpful, thank you"

Lesley Forster, UK

"Categories and Data has been my big step forward."

I worked through the first half of one of the videos last night... What has really helped me is the point you make about the data items in the raw data being either Categories or Data. As soon as I thought about it, that made sense of a lot of my past experiences with PTs.

Obviously, my learning style is such that I need to “see” a pattern or organization in the data structure. “Categories and Data” slotted straight into that need. The box, fruit, and vegetables didn’t help me at all, but then I knew that idea of sorting and grouping from previous work on PTs in a past existence. But horses for courses, Categories and Data has been my big step forward.

And having the spreadsheet to play with, to try things out, has been great. It is much richer in Categories than anything I might have created for myself, and so makes it easier for me to see patterns emerging as I tick successive boxes on the right-hand side.

Many thanks for a good program.

Regards,"

Chris Reynolds
IT Support Officer, USA

"...You have opened my eyes."

"Thank you, John. You have opened my eyes."

James Gibbons

“…a different insight I am thankful for”

“That was a different insight I am thankful for. It does underline the difference between excel and pivot tables. The hist video is indeed an anchor for every user”

Jan D.

What Are You Going To Get In the 'Master Of PivotTables' program?

In this comprehensive Excel PivotTables program, you will learn:

  • How to do a PivotTable step by step, the guru way
  • The silent Excel behavior outsiders never notice, which every PivotTable guru should know to reduce anxiety during the analysis and presentation of long and complex tables. If you want to PLAY with big tables, then this is for you
  • The power of understanding and using Pivot Tables. You will see how you can be in control of your data, your work and so your life
  • The secret 7 PivotTables perspectives that will give you the right mindset to take advantage of this data analysis and presentation technology. Develop the mindset, concepts, and techniques that will make you truly understand Pivot Tables and win the “big tables” game. Go beyond the oversimplified how-to PT training tailored to the masses and get deeper into secret Pivot Tables and data analysis insights that many Excel gurus never unveil because they have a big ego to satisfy
  • How to gain experience with PivotTables in minimum time by learning the fundamentals, not just the how-to of dragging fields. If you master the PivotTables fundamentals, the rest will follow. Don’t be part of the horde of people doing painful trial and error, hoping their random “fields dragging and dropping” will produce the customized and impressive reports they want
  • Learn what kind of tables are suitable for Pivoting. Discover in what format the source data should be put to analyze it. If you want to take advantage of PivotTables, you must work with the right kind of tables
  • The two types of fields present in every source data. This simple concept will simplify the understanding of PivotTables
  • What the best locations for each field type on the PivotTable canvas are.  Never again get confused as to which fields to drag to what section
  • How to present the data that is relevant and show trends. Show multiple views easily. See data by date, subject, and unit at the same time
  • How to turn big tables into simple reports quickly and easily. Understand the fine points and tricks such as how to show less/more detail without trial and error, etc.
  • Simplify the creation of fully customized analysis and reports following proven PivotTable report blueprints. Discover how to exactly drag and drop the fields to show the metrics that you want such as top 10 products/students/projects/employees, total sales/cost per product and by quarter/month/week, etc.
  • How to keep the PivotTable formatting on updating (an entire module is focused on this). Learn the single command and the two techniques to keep the PivotTable layout and format between refreshes
  • Techniques to avoid Excel slowing down and locking up because of a large amount of data (an entire module is focused on this)
  • How to create Pivot Charts step by step
  • How to navigate an existing PivotTable report without messing it up
  • How to remove fields to simplify a PT report
  • How to drag fields to a PivotTable canvas without messing it up (creating wide unreadable sheets)
  • How to manage more than one PivotTable report in the same sheet or workbook
  • How to edit and format an existing PivotTable without breaking it
  • How to show multiple views of the same data source without increasing the file size
  • Learn the concepts through varied samples across many industries and purposes: finance & accounting, marketing & sales, engineering, HR, operations, education, government, etc.
  • And much much more!…

Here's how to get this PivotTables foundational material our community needed!

Eventually, you can figure out the abstract part of PivotTables on your own. Or you can take advantage of the 10+ years John’s already invested in hard work dealing with big tables and PivotTables in a corporate environment, researching and synthesizing his findings.

There is absolutely no sane reason why you should want to waste years of your own career attempting to cover this same ground.

It’s up to you.

If you can increase your confidence to put Excel PivotTable skills in your resume and get a new and better job or get a promotion, how much extra money would you earn each month, $300, $500 or more?

The MPT program won’t cost you $500.

The MPT program won’t cost you $300.

The MPT program won’t even cost you $200.

Today, you can start learning how to simplify the analysis and presentation of big tables for any project for just one payment of:

USD 157.00

USD 127.00

 

With a small investment, the PivotTable data analysis and presentation skills will pay you back again and again.

If you don’t learn how to simplify the analysis and presentation of big Excel tables, you will never be a full-fledged Excel user.

You risk nothing

If within 30 days of purchase, you follow the Master Of PivotTables (MPT) video lessons, practice at your own pace, and cannot understand and use PivotTables to quickly find and present the crucial patterns that are in your data, or if you simply didn’t like the material, just ask a refund and you will get your money back, no questions asked.

I have been selling information products online since 2011 to thousands of professionals from around the world.

Just contact us (use the subject ‘refund’). I will take care of it.

Click that button and take your first step to understanding and using PivotTables that will supercharge your data analysis skills, your earning potential, and your life.

To your data analysis success!

What’s different from other Excel PivotTable courses out there?

Most of the Excel PT courses are all about the dragging and dropping of fields or how-to tricks that only works for one situation.

The MPT program will show you the abstract part and the fundamentals that empower you to face any big table analysis challenge, whether you work in finance, marketing, accounting, HR, IT, retailing, health, education, engineering or any other industry.

In fact, an entire module is dedicated to mastering the fundamentals.

The MPT program will also show you the practical HOW-TO, step by step guidance also. And the practical side is always accompanied by the root concepts.

Other PT courses assume you know a lot of things. I don’t take anything for granted that you should know. I explain it the way it is done to a kid. You will learn complex concepts following simple analogies anyone can understand. The MPT program will take you gradually from basic concepts such as the 2 type of fields in the data source to advanced concepts such as how to create an exact type of report.

Who is the 'Master Of PivotTables' for?

Basic to intermediate Excel users will get the most out of this program because they will start the PivotTable journey with a strong foundation. The concepts are explained using simple analogies anyone can understand and the program progresses gradually from basic to more advanced concepts.

You can ask for a full refund if you find the course is not for you.

But if you are a seasoned PivotTable expert, you might use this MPT course as a refresher or for covering some voids you might have. Also, you can ask for a full refund if you find the course is not for you.

You are covered by my 30-day money back guarantee.

What do you get when you enroll in 'Master Of PivotTables' program?

After you enroll, you get instant access to:

50+ category rich datasets

Curated data ready for pivottables reporting.

40+ video lessons

Learn by watching step-by-step lessons

Forum

Post your projects on the forums and get feedback from me and other members on your same journey. Also, hone your skills by helping others in their Excel guru journey

User and password

Email with a username and a password to access the private members' area

PivotTable building blueprints

Make meaningful progress in reasonable time by following some proven blueprints that will get your PivotTable reports done

Email support

One year of email support

Life-time access

Learn at your own pace at any time. No need to follow rigid schedules

Free updates

Get free access to updated and new lessons, bonuses, for one year

Access anytime, anywhere

Access the members' area on any device (Android, iOS, Windows, Mac).

What else do you get when you enroll in the 'Master Of PivotTabes' program?

Bonus # 1 - Library of 50+ Category-rich Excel Datasets

Get immediate access to more than 50 curated Excel category-rich workbooks in xlsx format and organized into categories: finance & accounting, marketing & sales, engineering, HR, operations, education, government, etc.

Play with the pivotable-ready tables and apply the principles to further understand PTs.

Bonus # 2 - The PivotTable Mind Map

MasterOfPivotTables

Find inside a goldmine of ideas and notes covering the entire range of PivotTable topics. You can keep it as a reference, enrich the map with your own ideas and findings.

Bonus # 3 - How To Create And Customize PivotTables Using VBA

Find inside a gallery of VBA code to speed up and automate the creation and formatting of Excel PivotTables.

  • Learn how to create a Pivot Table from scratch using VBA
  • Discover how to add new row, column or page fields to existing PivotTables using VBA
  • Learn how to format a PivotTable using VBA
  • Learn how to loop across the PivotTable fields
  • Discover how to select any element of PivotTables using VBA
  • and much more

Bonus # 4 - How To Get The Source Data Ready For PivotTables

Learn how to consistently get your data in the best quality and format for PivotTables even if it’s poor quality or spread over different sheets and files.

This is a proven step by step PivotTable data preparation blueprint!

The best thing about a system is that it’s REPEATABLE. You can use this data preparation system every time and consistently get your data ready for PivotTables.

  • Discover the secret data psychology to look at data in a simplified way. With this simplified way to look at data (even at the most complex datasets), you will never again suffer for not having the different tables together because you will be able to easily uncover the underlying structure of your data even if you work with big tables or data is spread over different sheets and files
  • Know how a perfect dataset looks like. Find inside a clear picture of a table featuring the main elements of a perfect dataset. Visualizing the special layout of your data is key to getting it in the right structure for PivotTables. You will also develop the skill to ask for the right dataset. You can also send the pdf so others produce the data in the desired format
  • Discover the 4 possible scenarios every dataset fits in. This is, in fact, a comprehensive data success roadmap that will show you every possible scenario and how to solve them. You can use this data success blueprint to tackle complex data scenarios others are scary even to think about. And learn the concepts using rich illustrations and simple explanations
  • Learn how to merge tables from different sources using several methods such as lookup formulas, Power Query, etc.
  • Learn how to perform data cleaning using formulas, filtering, Power Query, and other proven tricks and techniques. Be among the 1% elite professionals and data analysts who are up to date with the latest technology such as Power Query. Put Power Query skills on your resume and get a better job or be considered for a promotion
  • Get access to a proven source data success checklist
  • Get access to datasets in xlsx format
  • and much more…

I’ve got to tell you, it is a lot more fun and fulfill ing when you know exactly what you’re doing in your data preparation process and with my PivotTable data success blueprint, you will know exactly what data appending, merging, unpivoting and cleaning means even if you work with big datasets with hundreds of tables and thousands of rows each.

Read what others say about my PivotTable Data Success Blueprint…

"WOW, the introduction- you are so right. The objects explanation is a masterpiece. "

“I read carefully the PivotTable Data Success Blueprint – WOW, the introduction- you are so right. The objects explanation is a masterpiece.

You are right in your Objects explanation. To think and understand about the goal of the report / the query that one received and to build the solution (the table that is the source to the PT) accordingly. One need to understand what is his main object and to build and organize the data around it.

As to the ways to approach the problem – obviously, if it is a one-time occasion, the manual appending and using the vlookup and then a pivot table is much faster. However, if the pupils’ attribution to an organic class and/or field of interest is dynamic, then using the Power Query is the chosen one. I still do not understand why I failed to import the 5 classes files to the PQ using “From Folder”, I had to load them one after the other the whole 5, doing the same 5 times.

In general, since I was a “Lotus 1-2-3-“ beginner, I was taught to think in tables, and the importance of data normalization. Therefore, whenever I see data, I start thinking of how to organize it in order to have the best PT out of it. So when I got the “5 teachers files” I knew that I need to gather it to one DB, and wanted to play with the new Power Query tool. Since I’m not that skilled with PQ, I faced some know-how barriers and your PT data success blueprint helped me with them.

I must say that in Israel there are many Excel Experts and some companies that teach Excel (for beginners, advanced, VBA etc) but I didn’t find any one with an approach like yours or Oz.”

Lior D.
Accountant, Finance manager Israel

You are literally a couple of hours away from becoming the PivotTable guru of the office!

FAQ

How much time should I devote to learning?

Each tutorial is like 30 min average. Each module/week has 5 lessons on average. So you would need something like 3 hours per week.

No pressure teacher. No rigid schedules. Study at your own pace.

Click here to get started

What payment methods do you accept?

All major credit cards including Visa, MasterCard, and American Express are accepted. We also accept PayPal, Amazon Pay and Wire transfer.

Click here to get started

When does the course start and finish?

The access to the course starts now and ends whenever you like. It is a self-paced online course. You decide when you start and when you finish.

The course site remembers your progress, so you can pick up right where you left off on any device.  That means you can study a few lessons at work, then continue right where you left off on your tablet or home computer.

You can access the course site from your work or home computer, tablet, smart phone or any other device with an internet connection.  

Click here to get started

How long do I have access to the course?

You have lifetime access to the course.

Click here to get started

What Excel version do I need?

You need Excel 2010+

Click here to get started

Can I access the lessons on my mobile device?

Yes, you can log in with your smartphone or tablet (Android or iOS) and read or download the material. If you want to manipulate the workbooks, you might need an Excel app installed.

Of course, you can also log in with your computer or laptop (Windows or Mac).

Click here to get started

Are there exercises for each lesson?

Some lessons have some suggested activities. They are not assignments but ideas and inspiration to explore the given concepts.

Click here to get started

Is this PivotTables program for me?

This course is for you if you work with large and structured data and want to get insights from it and present them.

Also, you should take this course if you have never tried Pivot Tables before, if you gave up learning PTs and want to understand Pivot Tables once for all, if you want to increase your understanding of the PTs fundamentals so you perform more advanced analysis and gain confidence to present your findings, if you are a consultant looking to get more solid at your skills so you can take new and better clients, etc.

Click here to get started

Master Of PivotTables

"Imagine...you become 101% comfortable with pivotTables!"

To Data Analysts, Consultants, And Managers Who Want To Get On Top Of PivotTables To Display Meaningful Data For Making Decisions...Without Having To Export Data From Excel To Other Packages, Without Writing Macros Or Advanced Formulas.

From: John Franco, Excel author

At my desk, Wednesday, 8:05 AM

Dear Excel friend,

Back in 2003, everyone was talking about PivotTables and I didn’t want to miss such a trending technique.

But this is embarrassing…

…from 2003 to 2006 I used PivotTables with limited success and impact because I used PivotTables without fully understanding them. Most of my PT reports where based on some templates I got from others. And by trial and error, I managed to display the meaningful data to help my boss make decisions.

I knew that I didn’t know how PivotTables really work. I feared complex data and complex scenarios and mostly…I feared others asking me for PivotTable help and realizing I was a PivotTables fraud.

But don’t get me wrong…

…I tried hard to understand PivotTables but failed miserably with PivotTables because I assumed that they were part of Excel. I only became 101% comfortable with PivotTables when I discovered its hidden abstract side.

I said then, “this is why I never got it right”. And the reason is simple. PivotTables are not Excel and Excel users are not trained to think different than Excel.

If you want to be 101% comfortable with PivotTables, then this new program is for you!

Announcing my new Master of PivotTables (VIDEO program) – A 7-week PivotTables Mastery Blueprint.

This is the insider PivotTable knowledge John wishes was around when he was starting out with data analysis and presentation earlier in his career. Learning even one or two of these fundamental PivotTable concepts and techniques would have short-cut his own success by oh five maybe five years. Probably more.

What Are Others Saying About John Franco And The 'Master Of PivotTables' Program?

"I'm not frighted of pivot tables anymore!!!!"

"My main role involves working with an MS Access Database, and exporting details from that via queries/reports to spreadsheets for distribution to various sections of our company.

However, I have recently taken on producing a dashboard for our team, which is shared with the government as well as the business! At the moment the dashboard is made up of lots of worksheets which produce tables on a “Dashboard” worksheet. There is a lot of work manipulating the data to produce an easily readable, meaningful and understandable dashboard for varying levels of management.

The Pivot Tables Seminar has given me lots of ideas of how the dashboard can be better produced, and hopefully be more dynamic rather than a monthly task! I’m not a data analyst but find manipulating the data on a dashboard to be fun (as far as work is!).

I will continue to follow your seminars, and have learnt a lot - I'm not frighted of pivot tables anymore!!!!

I find your training extremely useful and helpful, thank you"

Lesley Forster, UK

"Categories and Data has been my big step forward."

I worked through the first half of one of the videos last night... What has really helped me is the point you make about the data items in the raw data being either Categories or Data. As soon as I thought about it, that made sense of a lot of my past experiences with PTs.

Obviously, my learning style is such that I need to “see” a pattern or organization in the data structure. “Categories and Data” slotted straight into that need. The box, fruit, and vegetables didn’t help me at all, but then I knew that idea of sorting and grouping from previous work on PTs in a past existence. But horses for courses, Categories and Data has been my big step forward.

And having the spreadsheet to play with, to try things out, has been great. It is much richer in Categories than anything I might have created for myself, and so makes it easier for me to see patterns emerging as I tick successive boxes on the right-hand side.

Many thanks for a good program.

Regards,"

Chris Reynolds
IT Support Officer, USA

"...You have opened my eyes."

"Thank you, John. You have opened my eyes."

James Gibbons

“…a different insight I am thankful for”

“That was a different insight I am thankful for. It does underline the difference between excel and pivot tables. The hist video is indeed an anchor for every user”

Jan D.

What Are You Going To Get In the 'Master Of PivotTables' program?

In this comprehensive Excel PivotTables program, you will learn:

  • How to do a PivotTable step by step, the guru way
  • The silent Excel behavior outsiders never notice, which every PivotTable guru should know to reduce anxiety during the analysis and presentation of long and complex tables. If you want to PLAY with big tables, then this is for you
  • The power of understanding and using Pivot Tables. You will see how you can be in control of your data, your work and so your life
  • The secret 7 PivotTables perspectives that will give you the right mindset to take advantage of this data analysis and presentation technology. Develop the mindset, concepts, and techniques that will make you truly understand Pivot Tables and win the “big tables” game. Go beyond the oversimplified how-to PT training tailored to the masses and get deeper into secret Pivot Tables and data analysis insights that many Excel gurus never unveil because they have a big ego to satisfy
  • How to gain experience with PivotTables in minimum time by learning the fundamentals, not just the how-to of dragging fields. If you master the PivotTables fundamentals, the rest will follow. Don’t be part of the horde of people doing painful trial and error, hoping their random “fields dragging and dropping” will produce the customized and impressive reports they want
  • Learn what kind of tables are suitable for Pivoting. Discover in what format the source data should be put to analyze it. If you want to take advantage of PivotTables, you must work with the right kind of tables
  • The two types of fields present in every source data. This simple concept will simplify the understanding of PivotTables
  • What the best locations for each field type on the PivotTable canvas are.  Never again get confused as to which fields to drag to what section
  • How to present the data that is relevant and show trends. Show multiple views easily. See data by date, subject, and unit at the same time
  • How to turn big tables into simple reports quickly and easily. Understand the fine points and tricks such as how to show less/more detail without trial and error, etc.
  • Simplify the creation of fully customized analysis and reports following proven PivotTable report blueprints. Discover how to exactly drag and drop the fields to show the metrics that you want such as top 10 products/students/projects/employees, total sales/cost per product and by quarter/month/week, etc.
  • How to keep the PivotTable formatting on updating (an entire module is focused on this). Learn the single command and the two techniques to keep the PivotTable layout and format between refreshes
  • Techniques to avoid Excel slowing down and locking up because of a large amount of data (an entire module is focused on this)
  • How to create Pivot Charts step by step
  • How to navigate an existing PivotTable report without messing it up
  • How to remove fields to simplify a PT report
  • How to drag fields to a PivotTable canvas without messing it up (creating wide unreadable sheets)
  • How to manage more than one PivotTable report in the same sheet or workbook
  • How to edit and format an existing PivotTable without breaking it
  • How to show multiple views of the same data source without increasing the file size
  • Learn the concepts through varied samples across many industries and purposes: finance & accounting, marketing & sales, engineering, HR, operations, education, government, etc.
  • And much much more!…

Here's how to get this PivotTables foundational material our community needed!

Eventually, you can figure out the abstract part of PivotTables on your own. Or you can take advantage of the 10+ years John’s already invested in hard work dealing with big tables and PivotTables in a corporate environment, researching and synthesizing his findings.

There is absolutely no sane reason why you should want to waste years of your own career attempting to cover this same ground.

It’s up to you.

If you can increase your confidence to put Excel PivotTable skills in your resume and get a new and better job or get a promotion, how much extra money would you earn each month, $300, $500 or more?

The MPT program won’t cost you $500.

The MPT program won’t cost you $300.

The MPT program won’t even cost you $200.

Today, you can start learning how to simplify the analysis and presentation of big tables for any project for just one payment of:

USD 157.00

USD 127.00

 

With a small investment, the PivotTable data analysis and presentation skills will pay you back again and again.

If you don’t learn how to simplify the analysis and presentation of big Excel tables, you will never be a full-fledged Excel user.

You risk nothing

If within 30 days of purchase, you follow the Master Of PivotTables (MPT) video lessons, practice at your own pace, and cannot understand and use PivotTables to quickly find and present the crucial patterns that are in your data, or if you simply didn’t like the material, just ask a refund and you will get your money back, no questions asked.

I have been selling information products online since 2011 to thousands of professionals from around the world.

Just contact us (use the subject ‘refund’). I will take care of it.

Click that button and take your first step to understanding and using PivotTables that will supercharge your data analysis skills, your earning potential, and your life.

To your data analysis success!

What’s different from other Excel PivotTable courses out there?

Most of the Excel PT courses are all about the dragging and dropping of fields or how-to tricks that only works for one situation.

The MPT program will show you the abstract part and the fundamentals that empower you to face any big table analysis challenge, whether you work in finance, marketing, accounting, HR, IT, retailing, health, education, engineering or any other industry.

In fact, an entire module is dedicated to mastering the fundamentals.

The MPT program will also show you the practical HOW-TO, step by step guidance also. And the practical side is always accompanied by the root concepts.

Other PT courses assume you know a lot of things. I don’t take anything for granted that you should know. I explain it the way it is done to a kid. You will learn complex concepts following simple analogies anyone can understand. The MPT program will take you gradually from basic concepts such as the 2 type of fields in the data source to advanced concepts such as how to create an exact type of report.

Who is the 'Master Of PivotTables' for?

Basic to intermediate Excel users will get the most out of this program because they will start the PivotTable journey with a strong foundation. The concepts are explained using simple analogies anyone can understand and the program progresses gradually from basic to more advanced concepts.

You can ask for a full refund if you find the course is not for you.

But if you are a seasoned PivotTable expert, you might use this MPT course as a refresher or for covering some voids you might have. Also, you can ask for a full refund if you find the course is not for you.

You are covered by my 30-day money back guarantee.

What do you get when you enroll in 'Master Of PivotTables' program?

After you enroll, you get instant access to:

50+ category rich datasets

Curated data ready for pivottables reporting.

40+ video lessons

Learn by watching step-by-step lessons

Forum

Post your projects on the forums and get feedback from me and other members on your same journey. Also, hone your skills by helping others in their Excel guru journey

User and password

Email with a username and a password to access the private members' area

PivotTable building blueprints

Make meaningful progress in reasonable time by following some proven blueprints that will get your PivotTable reports done

Email support

One year of email support

Life-time access

Learn at your own pace at any time. No need to follow rigid schedules

Free updates

Get free access to updated and new lessons, bonuses, for one year

Access anytime, anywhere

Access the members' area on any device (Android, iOS, Windows, Mac).

What else do you get when you enroll in the 'Master Of PivotTabes' program?

Bonus # 1 - Library of 50+ Category-rich Excel Datasets

Get immediate access to more than 50 curated Excel category-rich workbooks in xlsx format and organized into categories: finance & accounting, marketing & sales, engineering, HR, operations, education, government, etc.

Play with the pivotable-ready tables and apply the principles to further understand PTs.

Bonus # 2 - The PivotTable Mind Map

MasterOfPivotTables

Find inside a goldmine of ideas and notes covering the entire range of PivotTable topics. You can keep it as a reference, enrich the map with your own ideas and findings.

Bonus # 3 - How To Create And Customize PivotTables Using VBA

Find inside a gallery of VBA code to speed up and automate the creation and formatting of Excel PivotTables.

  • Learn how to create a Pivot Table from scratch using VBA
  • Discover how to add new row, column or page fields to existing PivotTables using VBA
  • Learn how to format a PivotTable using VBA
  • Learn how to loop across the PivotTable fields
  • Discover how to select any element of PivotTables using VBA
  • and much more

Bonus # 4 - How To Get The Source Data Ready For PivotTables

Learn how to consistently get your data in the best quality and format for PivotTables even if it’s poor quality or spread over different sheets and files.

This is a proven step by step PivotTable data preparation blueprint!

The best thing about a system is that it’s REPEATABLE. You can use this data preparation system every time and consistently get your data ready for PivotTables.

  • Discover the secret data psychology to look at data in a simplified way. With this simplified way to look at data (even at the most complex datasets), you will never again suffer for not having the different tables together because you will be able to easily uncover the underlying structure of your data even if you work with big tables or data is spread over different sheets and files
  • Know how a perfect dataset looks like. Find inside a clear picture of a table featuring the main elements of a perfect dataset. Visualizing the special layout of your data is key to getting it in the right structure for PivotTables. You will also develop the skill to ask for the right dataset. You can also send the pdf so others produce the data in the desired format
  • Discover the 4 possible scenarios every dataset fits in. This is, in fact, a comprehensive data success roadmap that will show you every possible scenario and how to solve them. You can use this data success blueprint to tackle complex data scenarios others are scary even to think about. And learn the concepts using rich illustrations and simple explanations
  • Learn how to merge tables from different sources using several methods such as lookup formulas, Power Query, etc.
  • Learn how to perform data cleaning using formulas, filtering, Power Query, and other proven tricks and techniques. Be among the 1% elite professionals and data analysts who are up to date with the latest technology such as Power Query. Put Power Query skills on your resume and get a better job or be considered for a promotion
  • Get access to a proven source data success checklist
  • Get access to datasets in xlsx format
  • and much more…

I’ve got to tell you, it is a lot more fun and fulfill ing when you know exactly what you’re doing in your data preparation process and with my PivotTable data success blueprint, you will know exactly what data appending, merging, unpivoting and cleaning means even if you work with big datasets with hundreds of tables and thousands of rows each.

Read what others say about my PivotTable Data Success Blueprint…

"WOW, the introduction- you are so right. The objects explanation is a masterpiece. "

“I read carefully the PivotTable Data Success Blueprint – WOW, the introduction- you are so right. The objects explanation is a masterpiece.

You are right in your Objects explanation. To think and understand about the goal of the report / the query that one received and to build the solution (the table that is the source to the PT) accordingly. One need to understand what is his main object and to build and organize the data around it.

As to the ways to approach the problem – obviously, if it is a one-time occasion, the manual appending and using the vlookup and then a pivot table is much faster. However, if the pupils’ attribution to an organic class and/or field of interest is dynamic, then using the Power Query is the chosen one. I still do not understand why I failed to import the 5 classes files to the PQ using “From Folder”, I had to load them one after the other the whole 5, doing the same 5 times.

In general, since I was a “Lotus 1-2-3-“ beginner, I was taught to think in tables, and the importance of data normalization. Therefore, whenever I see data, I start thinking of how to organize it in order to have the best PT out of it. So when I got the “5 teachers files” I knew that I need to gather it to one DB, and wanted to play with the new Power Query tool. Since I’m not that skilled with PQ, I faced some know-how barriers and your PT data success blueprint helped me with them.

I must say that in Israel there are many Excel Experts and some companies that teach Excel (for beginners, advanced, VBA etc) but I didn’t find any one with an approach like yours or Oz.”

Lior D.
Accountant, Finance manager Israel

You are literally a couple of hours away from becoming the PivotTable guru of the office!

FAQ

How much time should I devote to learning?

Each tutorial is like 30 min average. Each module/week has 5 lessons on average. So you would need something like 3 hours per week.

No pressure teacher. No rigid schedules. Study at your own pace.

Click here to get started

What payment methods do you accept?

All major credit cards including Visa, MasterCard, and American Express are accepted. We also accept PayPal, Amazon Pay and Wire transfer.

Click here to get started

When does the course start and finish?

The access to the course starts now and ends whenever you like. It is a self-paced online course. You decide when you start and when you finish.

The course site remembers your progress, so you can pick up right where you left off on any device.  That means you can study a few lessons at work, then continue right where you left off on your tablet or home computer.

You can access the course site from your work or home computer, tablet, smart phone or any other device with an internet connection.  

Click here to get started

How long do I have access to the course?

You have lifetime access to the course.

Click here to get started

What Excel version do I need?

You need Excel 2010+

Click here to get started

Can I access the lessons on my mobile device?

Yes, you can log in with your smartphone or tablet (Android or iOS) and read or download the material. If you want to manipulate the workbooks, you might need an Excel app installed.

Of course, you can also log in with your computer or laptop (Windows or Mac).

Click here to get started

Are there exercises for each lesson?

Some lessons have some suggested activities. They are not assignments but ideas and inspiration to explore the given concepts.

Click here to get started

Is this PivotTables program for me?

This course is for you if you work with large and structured data and want to get insights from it and present them.

Also, you should take this course if you have never tried Pivot Tables before, if you gave up learning PTs and want to understand Pivot Tables once for all, if you want to increase your understanding of the PTs fundamentals so you perform more advanced analysis and gain confidence to present your findings, if you are a consultant looking to get more solid at your skills so you can take new and better clients, etc.

Click here to get started

How to create a Pivot Table using VBA

You can generate a Pivot Table report with a mouse click. You can do it using Excel VBA and macros!

Let’s suppose you want to generate a Pivot Table report based on a given Excel table created; see below…

Excel table with macro-button to generate a Pivot Table using VBA

Figure 1: Source data for Pivot Table report

This would be the resulting PT report (a simple one):

Summarize with Pivot Table

Figure 2: simple Pivot Table report

If you do it by hand, this would be the process:

  1. Creating the PT report by going to: Table Tools>Design>Summarize with Pivot Table (assuming the Excel table is already created)
  2. Setting row fields (“Products”)
  3. Setting data fields (“Units Sold”)

Overall, the same steps should be executed using VBA, see the Excel macro code below…

Public Sub CreatePivotTable()
'developed by John Franco
'excelgurusacademy.com
'last update: Mar-03-2012

Dim MyPTReport As PivotTable 'a pivot table report object
Dim MyPTCache As PivotCache 'the pivot table data

On Error Resume Next

'adds a new sheet
Sheets.Add

'creates a pivot table based on Table1. You can change your table name as you like

' Create PivotTable cache and report.
Set MyPTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Table1")

Set MyPTReport = ActiveSheet.PivotTables.Add(PivotCache:=MyPTCache, TableDestination:=Range("A1"), _
TableName:="PivotTable1")

'add row fields
    With MyPTReport.PivotFields("Product")
        .Orientation = xlRowField
        .Position = 1
    End With
'add data fields
    MyPTReport.AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Units Sold"), "Sum of Units Sold", xlSum

End Sub

That’s all! You can create a Pivot Table report each time without effort.

Download the Excel Pivot Table macro practice file at the bottom of this page.

Now let me explain the Excel VBA macro code in more detail…

Adding a new Pivot Table using VBA

To create a new Pivot Table in a workbook you need to be familiar with the hierarchy of Pivot Tables objects in the Excel object model, see the picture below…

Pivot tables collection, pivot table object

Figure 3: Pivot Table object model

Basically you need to specify the unambiguous path that points to your PT, e.g.

Workbooks(“YOURBOOK”).Worksheets(“YOURSHEET”).PivotTables(“YOURPIVOTTABLE”).”PT method or property here”.

You can make your Excel VBA code shorter when you are referring to the active workbook, replace the entire VBA objects hierarchy: “Workbooks(“YOURBOOK”).Worksheets(“YOURSHEET”)” by this one: “ActiveSheet()…”

Well, to create the Pivot Table invoke the “Add” method of the “PivotTables” collection.

This is the full syntax:

expression.Add(PivotCache, TableDestination, TableName, ReadData, DefaultVersion)

“expression” represents a PivotTables object.

Where the three most important arguments are:

  • PivotCache – this provides the data for the report
  • TableDestination – the cell where the PT report will be inserted, e.g. “A1”, “G2”, etc.
  • TableName –  the name of the Pivot Table, e.g. “Region sales”, “Labor hours summary”, etc.

Here’s the full code I used above:

Set MyPTReport = ActiveSheet.PivotTables.Add(PivotCache:=MyPTCache, TableDestination:=Range("A1"), _
TableName:="PivotTable1")

You first need to specify the source data of your future Pivot Table (cache). To create a “PivotCache”, use the “Create” method of the “PivotCaches” collection.

This is the syntax:

expression.Create(SourceType, SourceData, Version) or you can also use expression.Add(SourceType, SourceData, Version)

“expression” represents a “PivotCaches” object.

Where the two most important arguments are:

  • SourceType – can be either: xlConsolidation, xlDatabase, or xlExternal
  • SourceData – this is the data source of your Pivot Table, e.g. Range(“A2:F22”), Sheets(“Sheet3”).Range(“F30:G40”), “Table1”, etc.

This is the full code I used above:  

Set MyPTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Table1")

Now is time to add the respective fields…

Adding row fields to a Pivot Table using VBA

Just for refreshing the location of Pivot Table fields, here’s the PT report layout:

Drag and drop the fields to the right fields area

Figure 4: Pivot Table fields’ layout

To add row fields use the “PivotFields” collection of the “PivotTable” object.

Specifically, use the “Orientation” property of the “PivotField” object.

This is the syntax:

expression.Orientation

expression represents a “PivotField” object.

Specify the orientation (field type) using the respective “Orientation” property value:

  • xlRowField
  • xlPageField
  • xlColumnField
  • xlDataField

This is the full VBA code I used above…

With MyPTReport.PivotFields("Product")
 .Orientation = xlRowField
 .Position = 1
 End With

At this time you have defined a blank PT report, see below…

Before adding row fields, data fields, column fields or page fields

Figure 5: Blank Pivot Table report, before adding fields

It’s time to add stuff to it…

Adding data fields to a Pivot Table using VBA

You need to use the “AddDataField” method of the “PivotTable” object.

The syntax is:

expression.AddDataField(Field, Caption, Function)

“expression” represents a “PivotTable” object.

Where the main parameters are:

  • Field – what field you refer to, it should be one of the fields contained in the source data (cache). For this example the fields are: “Product”, “Salesman”, “Units Sold” or “Price per unit”
  • Caption – how your field will be presented in the Pivot Table report, e.g. “Sum of Sales”, “Average of price”, etc.
  • Function – to sum use xlSum, to average use xlAverage, etc.

This is the full code I used above…

MyPTReport.AddDataField ActiveSheet.PivotTables( _
 "PivotTable1").PivotFields("Units Sold"), "Sum of Units Sold", xlSum

Additional VBA Pivot Table techniques

Adding column fields using VBA

Use also the “PivotFields” collection, see the full VBA code below…

'define column field
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
    .Orientation = xlColumnField
    .Position = 1
End With

Adding page fields using VBA

Use also the “PivotFields” collection, see the full VBA code below…

'add page fields
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
        .Orientation = xlPageField
        .Position = 1
    End With

Converting the current region to a table

If you want to create the Excel Table on which your Pivot Table report will be based on (“Table1” for this example), you need to use the “Add” method of the “ListObjects” collection. See the full VBA code below…

'creates a table based on current selection
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Range("$A$1").CurrentRegion.Address), , xlYes).Name = "Table1"

Excel VBA Downloads

Download the Excel PivotTable macro practice workbook below:

Pivot Table Excel

This short story shows how I gained the respect and trust of 3 departments in the big company I worked for.

Before I knew how Pivot Tables in Excel worked, I found a big opportunity to become valued at my office…

…I noticed that one single Excel table contained all the data that the Planning, Cost, and Engineering department needed.

This was a huge realization at the time…

…in a way, the data and its insights were dormant!

The Planning department needed the length of pipes by diameter and by zone, the Cost department needed pipes length by diameter for suppliers, the Engineering department needed the total length of pipes to plan the work of drafters.

And nobody was providing these insights in a timely manner!

I knew Pivot Tables were good for mining data but I didn’t understand how they worked yet.

And the table was monstrous…

I have always known the benefits of trying new things so I created a Pivot Table report out of the table, and started playing with it.

I asked to each department head, what do you want to know about this data?
The table contained tens of fields but I just focused on the fields they care about: Pipe length, Pipe zone, Pipe Diameter, etc.

For example, Mirian, from the Planning department wanted to know the distribution of pipes by zone, so I dropped those fields into the Pivot Table Excel canvas. And patterns started emerging. I did a lot of trial and error until the pattern she wanted was established.

Do it now!

Pick your company data and see how you can help different people, maybe your boss and the boss of your boss. Ask them what do they want to get out of this data? And they will tell you because people is craving for more understanding.

Drag and drop the most relevant category fields (Department, Pipe diameter, Name, Year, etc.) to the Row area of your PT report. Drag and drop the most important data fields (Length, Sales, Amount, etc.) to the Value area of your PT report. If you are not satisfied with the layout, move the Pivot Table Excel fields around. This is the way I did it and it worked because any layout you create is right (maybe not needed) and you can play safely until you find something that is needed.

The worst part was when they started asking me for new reports. My trial-and-error approach was a huge limitation because it was embarrassing playing with data when they were looking over my shoulder.

Hopefully I stumbled upon a simple grocery analogy that revealed the mechanics of Pivot Tables in Excel and you can grasp it in minutes from now.

If you’re looking for an “insider” shortcut guide to finally get comfortable with Pivot Tables in Excel, creating insightful Pivot Table Excel reports (consistently) that will supercharge your data mining and presentation skills, then you’ve got to sign up for my free Excel Pivot Table Tutorial.

How to do a Pivot Table in Excel

The reason why many Excel users never learn how to do a Pivot Table in Excel is because they don’t understand why they should learn Pivot Tables neither what is a Pivot Table.

If you are still afraid of Pivot Tables or you want to establish a solid foundation to your data analysis journey, in this free Excel Pivot Table tutorial, you will learn the fundamentals first:

  1. Why you should learn how to do a Pivot Table in Excel
  2. What is a Pivot Table
  3. Learn how to do a Pivot Table in Excel

I was one of those who was not aware of the psychology and mechanics of Excel Pivot Tables.

Why you shoud learn how to do a Pivot Table in Excel

But I was determined to definitely get it because I realized Pivot Tables were a form of fast Excel dashboarding and fast data analysis and understanding.

Create Excel dashboards fast

You can create summarized tables out of big tables with a couple of clicks. You can add several views of the same data set (e.g. by department, by quarter, by region, sum, average, count, etc). You can also add charts and dashboard controls such as slicers and timelines  without using VBA, without writing any single vlookup or index and match formula.

I created the plain dashboard you see below in a matter of 5 minutes!

Dashboard using a Pivot Table

Analyze and understand data

For example you can turn a big 10K rows Excel table into a tiny Excel table packed with meaningful information because a Pivot Table finds patterns and show them to you. For example, in the table below, the thousands of “Delivery Truck” entries become a single one with a subtotal.

Simple Pivot Table example

 

When you learn how to do a Pivot Table in Excel, you become able to include in your analysis only the data that matters most to you. For example, if you are a manager, you can focus on departments or products or on both categories at the same time. You can focus your analysis even further by looking at a date range only. If you are a salesman, you can focus your analysis on the product you sell and narrow the date range to your sales period.

If you want to look at your data from another vantage point, you can do it easily because you can drag and drop any data field you want and change your Pivot Table report instantly.

Well, if you don’t know how to do a Pivot Table in Excel yet, then you need to learn what is a Pivot Table first. If you want to get Pivot Tables explained to you in a meaningful manner, you might find this explanation useful:

What is a Pivot Table

You are about to discover a simple analogy that will make Pivot Tables easier to learn and use.

If you don’t know what is a Pivot Table, if you are afraid of learning and using Pivot Tables, if you have only a basic idea of Pivot Tables in Excel and don’t know what to expect when you drop the fields to the PivotTable report, or if you just want to understand more about Pivot Tables, then keep reading…

…because this grocery analogy I stumbled upon will show you how to do a Pivot Table in Excel because it will make PTs simpler for you, and it will boost your reporting and dashboard skills, starting today.

It’s a shame…

I used to create Pivot Tables reports through trial and error but I was afraid my coworkers or boss would ask to me: how did you that? because I didn’t know exactly how my Pivot Table reports were generated, how the fields affected the layout of the report, and so forth.

I never had confidence in using Pivot Tables because it was a sort of hope-reporting. Every column, row, page, or value field I dropped into the fields’ area, produced unexpected results.

Everything changed when I discovered the Pivot Table relationships . Pivot Tables Paradigm!

A Pivot Table report is like a box. See below:

How to do a Pivot Table in Excel - Pivot table report explained

Let’s imagine the box contains oranges, apples, lemons and bananas. This is your source data.

How to do a Pivot Table in Excel - Pivot Table explained using a simple example

With this bunch of fruits as your source data, you can ask questions like: how many oranges, apples, lemons and bananas are in the box? You can achieve this by counting each type of fruit.

To better find the answer, you organize the box by fruit type as you see below:

Pivot Tables explained - Counting

Here’s the answer and a nice subtotal:

 

Pivot Tables explained - Counting

You can also organize the fruits according to its type: citric and other.

Pivot Tables explained - Counting and grouping

If each fruit have a price tag, then you will be able to answer questions like: how much the oranges, apples and bananas are?, how much the box is?, how much the citrics are?

To answer the question: how many green and mature oranges contain the box?, you would need to compartmentalize the box by fruit state: mature and green oranges.

Very simple, right?

To answer the question: how many big or small green/mature oranges are in the box?, you would need to compartmentalize the box by fruit size.

Would you compartmentalize the entire box again? Or just the existing compartments?

You would separate green oranges into small and big and mature oranges into small and big.

In other words, you compartmentalize the already compartmentalized box.

This simple principle applies to Excel Pivot Tables…

Learn how to do a Pivot Table in Excel

You simply group the data source on your Pivot Table report by any of the fields you choose.

The first item you add to your Pivot Table fields area (box) is what’s in your box (data source), the second field is how you compartmentalize the existing box, the third field is how you compartmentalize the existing already compartmentalized box, and so on.

For example, if you have a table with these fields: Department, Brand, Model, Salesman, Date, Units sold, Amount, and you add the “Department” field to the fields area, then you will answer questions like how much sales by department.

To answer the question how well each brand performed by department?, then you would add “Brand” to the fields’ area so you compartmentalize “Department”. If you add Brand, then you compartmentalize department by brand, if you add salesman, you split the brand compartment by brand, and so on.

You now should be able to drag and drop the Pivot Table report fields with confidence.

If you are more comfortable with using Pivot Tables in Excel, and want to get deeper into using Pivot Tables, you can definitely learn how to do a Pivot Table in Excel in my Free Excel Pivot Table tutorial

Or you can explore these good Pivot Table tutorials:

http://www.contextures.com/CreatePivotTable.html

http://www.excelfunctions.net/Excel-Pivot-Table-Tutorial.html