WARNING: The PivotTable Is An Amazing And Powerful Tool, But Can Only Be Used To Its Full Potential If The Source Data Is In The Right Structure. If Data Isn’t Normalized And Consolidated (And Cleaned), It Makes It Incredibly Difficult To Analyze It As A Single Dataset
“Who Else Wants To Know The Secrets Of Getting The Source Data In The Right Format For Pivot Tables?”
From: John Franco, Excel author
Thursday, 9 am
Dear Data Analyst,
If you want to stop pulling your hair trying to take full advantage of the PivotTables power because your data is never in the right format, then this is the most important message you will ever read…
…because I will share the secret psychology of perfect data and proven data preparation techniques to get your data in the best format for PivotTables.
In fact, I am going to reveal more data preparation techniques than you will ever need.
Data preparation is something I do extremely well. But beware that data preparation is not for everyone. Some want a magic shortcut that will turn the messy data into structured data. That’s like hoping your clothes will come folded out of the washing machine.
This is not about magic shortcuts. It’s about a comprehensive data approach that will help you get the data in the best format consistently, no matter the size of your data or the number of tables.
Data preparation is not for the faint of heart.
Some years ago, I was in the same situation you are now. Data was never in the right format…
…and I started dealing with more and more data :(
Startling data facts
If you struggle trying to summarize your data using PivotTables, then at least some of these things are true for you…
- You cannot get your reports done because the required fields seem to be missing
- Lack of time for data preparation
- Lack of time for learning data preparation techniques
- Getting the data in the best format requires it to be “cleaned”
- The big tables are intimidating
It’s well known that everyone is working with larger data sets from multiple sources (and players). Data collecting and storing is at its peak. It can be a challenge to put everything together.
And the sad truth is that data travels a lot, and in that handling process, ends up corrupted and needs to be cleaned. It explodes across several sheets or workbooks, and you know the different datasets are related but you don’t know how to put everything together into a single table.
And this is also a startling fact…
…people never produce data for PivotTables. Excel, neither other programs have an “export for PivotTable” command.
To make things worst, you and I know that there’s no even a picture of how the perfect dataset looks like. And because only a few know the right data structure, almost nobody cares about exporting data in the special layout that is needed for PivotTables.
If the person or software who produced the data is not present, you cannot blame anybody but get your hands dirty and put the messy data in the best format using some proven techniques such as VLOOKUP or even copying and pasting, but it can be time-consuming and prone to error.
An unfair data game
You are valued by the quality of your outputs but…
…you have no control over the quality of the inputs :(
It’s an unfair data game.
If you don’t know the right data structure and the proven techniques and tools used to consolidate and clean the data, you cannot win the data analysis game.
Data preparation skills are even a job requirement these days.
And everyone is talking about the new Excel tools such as Power Query (PQ) but only 1% of Excel users know how to take advantage of this new technology. Many fail to try to transform their knowledge to this instead of Vlookup.
It’s time to face the elephant in the room
Unstructured data will keep raining from all sort of uncaring sources and players. And in this data chain, you are the one to blame. What excuse are you going to offer when your reports take too much time to produce, contain errors or never contain the information your company wants?
You are the data quality hub.
You would like to learn in what format the data should be put before you start analyzing it.
Everything would be easier if you just knew the special structure of your data because you can be literally paralyzed if you don’t know how a perfect PivotTable source data looks like.
For me, even after I discovered the right structure of my data, the size of data and number of factors were intimidating.
It’s a fact that with the size of the data, we feel we always miss something important. We are never sure if we are working with the right kind of data.
It’s time to face the elephant in the room.
Many don’t know the data structure a PivotTable loves. And even if you knew what kind of tables your PivotTables love, you don’t know all the data cleaning and transformation techniques available.
But data is not the problem…
Why your current data approach fails
Tables are the mainstream data paradigm and everyone use it, but paradoxically, thinking in tables never reveals the structure of your data.
If you think in tables, your attention is split into tables, rows, columns, records, headers, fields, blanks, data types, etc., but none of that matter when it comes to visualizing the special layout of your data.
The fact is that you are blind to the underlying structure of data because you are still thinking in tables.
Most of the data problems come from a fragmentary view of your data.
You are facing a challenge that you know is easy to solve but you can’t reach the solution because you are using the wrong data mindset.
A simplified approach to data quality
If you want to be successful with PivotTables, you must stop thinking in terms of tables.
Forget tables and ask yourself, “what is the object you are dealing with?”
A school. A store. A farm.
If you know how a school looks like you know how your data should look like. That simple.
When you know the structure, you find the ways to put your data in that layout. Fundamentals come first, then the techniques.
The secret is thinking in objects, not tables.
When you think this way, suddenly everything becomes much easier. You see the many tables as one single unit and you stop suffering from data that is spread over different sheets and books.
Thinking in terms of objects is the easiest way to becoming a data guru because you see the underlying patterns that are not visible to most professionals, who are trapped in tables-thinking. You can impress your boss and everybody in the office with such hidden knowledge.
Thinking simple is an unfair advantage in today’s complex world.
I have compiled this secret philosophy and everything that a table should be and have in order to get it to work for you not against you. It will simplify your work and enable you to get more done within allocated time.
But this 134-page comprehensive data success blueprint is not for everyone.
This is not for those looking for a shortcut. I am sorry.
Only a few individuals are willing to invest the time to put their data in the best format so they are proud of their work.
If you qualify, then keep reading.
It’s all in a practical 130+ page blueprint using simple language you can understand and with step by step explanations so you implement what you learn.
Announcing my new Excel program…
PivotTable Data Success Blueprint
The Secrets Of High-Quality PivotTable Data And Reliable Reporting And Dashboarding
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.
Find inside secrets many Data Analysts and professionals never get it such as…
- 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
- 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, unpivonting 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.”
Accountant, Finance manager
Find inside a comprehensive roadmap to navigate even the most complex data scenarios…
- Getting started
- What is data?
- Why tables-thinking is not the way to successful pivoting
- Objects – A smarter approach to data
- What’s a whole object
- How this idea of objects applies to your work with tables
- How object-thinking increases your efficiency for pivoting
- New and easier data terminology
- How to identify the objects in your tables
- How a perfect PT dataset looks like
- Common tables issues and how to easily solve them with object-thinking
- PT data issue #1 – The PivotTable data is not consolidated (it’s not a whole object)
- PT data issue #2 – The PivotTable deals with incomplete data (incomplete elements)
- How to get your data in the best format for PivotTables (Measures)
- Pre-requisite – Data transformation
- Adding missing elements (appending tables)
- Removing elements
- Enriching existing elements/adding attributes (tables merging)
- Fixing existing elements attributes (data cleaning)
Here’s how it works…
- Order your “PivotTable Data Success Blueprint” Home Study Course right now.
- You’ll get immediate access to the book and bonus practice files so you can get started right away, if you like.
- If at any time within the next 60 days you decide this system is not for you, simply send me an email and you will receive a prompt refund.
How much is this?
Preparing your data for PivotTables is not brain surgery. You can figure it out on your own. Eventually and if you are willing to invest time and effort deconstructing the exact data structure PivotTables love, and doing trial and error trying to consolidate big tables, dealing with data type issues, etc. Or reading books, forums, and blogs and synthesizing that information.
Or you can take advantage of the 10+ years John’s already invested in hard work preparing data for PivotTables, 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 order today, you can be successful with preparing data for PivotTables and producing reliable reports for yourself and upper management. You are literally minutes away to unleash the data analysis power of PivotTables and quickly analyze and present data for just…
If you attend an Excel seminar, you will not cover even a fraction of the techniques covered here, and you will pay $300+ for one day of training, and spend even more on hotel and traveling expenses.
Power Query seminars cost hundreds of dollars these days, as trainers take advantage of the hungry crowd desperate to stay up to date with the latest Excel technology.
And what’s the cost of a damaged reputation?
Avoid criticism and embarrassment because working with poor-quality data makes you a pest in the office because others will be embarrassed by the decisions they can’t make using the messy data and reports you send.
There’s a saying that goes like this: “if you think education is expensive, try ignorance.”
…just think, how much will it cost you to do nothing?…
…no confidence to put this valuable skill on your resume to land a new and better job. No ability to lead your organization to a brighter future and get a promotion for doing it. No ability to understand the world around you and make better decisions.
If you are serious about having reliable and useful data to quickly analyze it and present the insights to upper management, and secure a leadership position, get a better job, put Dashboarding skills on your resume, then this program is for you.
And you risk nothing…
To access this program, just click here, fill out the order page and follow the simple directions. You are literally – only seconds away from TURBOCHARGING your Excel Data preparation skills by 1000% with this amazing program. And you can see for yourself what all the fuss is about!
One more thing – it’s important!
If you order today, you will also get…
Bonus 1 – The High-Quality PivotTable Data Checklist
If you don’t use a list of the most common data corruption issues and the exact data preparation methods, you might simply forget to do simple things that are crucial to your PivotTable success.
This practical checklist helps you achieve high-quality datasets consistently. No matter the size and complexity of the source data. It comes as an Excel workbook so you can print it or adapt it to your needs.
There’s no other checklist available in the Excel market. if you don’t get it here, you won’t find it anywhere else.
Bonus 2 – Data preparation practice workbooks
If you are the kind of person that need to see things in action to understand and get started, then here you will find joy in having the files to practice the concepts, and being able to actually play with them.
Find a library of datasets to get a sense of what a perfect dataset looks like, how the data fundamentals are actually present in every perfect dataset, find datasets to append and merge, and much more…
You risk nothing
If within 60 days of purchase, you follow the data fundamentals, apply the techniques and cannot adapt them to your situation for preparing data for your PivotTables and produce reliable reports, or if you simply find the material too advanced or if you didn’t like the material or my style, 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.
P.S Remember, No More frustrations and headaches trying to manipulate data that is poor quality or spread over different sheets and files, or returning to the office tomorrow and being asked by your manager to produce a report out of a messy dataset
P.P.S Don’t let the unstructured data keep draining your energy before the analysis starts. What would you do with your time if you wouldn’t be working with the wrong data anymore?
What Excel version do I need?
Excel 2010+ (Power Query is available for 2010 and 2013 as an addon and as a built-in feature in Excel 2016)
If I am not getting correct results, can I send my practice to someone for comments/review? Is help only available through email or is it possible to chat or talk to someone?
You have 6 months email support. You can open a support ticket. You will get an answer by email in the next 24 hours.
Can I purchase the book for myself and my team?
Yes, you can purchase for multiple users and take advantage of my group discount. Please email me with the number of students and I can provide a discounted price: firstname.lastname@example.org