Ultimate Pivot Tables Course
About this course
Having worked with Excel for well over a decade in global companies, I’ve seen the rapid rise of data volumes over the last few years. Although many people only deal with ‘Small Data’ (Less than 5000 rows of 15-20 columns), larger data sets are on fast on the rise and you need to get comfortable dealing with them.
Here are five good reasons you want to consider learning Pivot Tables in a world of increasing data levels:
1. Speed of calculation
Once you are over 5000 rows X 15-20 columns then formulas can slow down, Pivot Tables run off the ‘Pivot Cache’, a makeshift memory that will keep the calculations quick
2. Get a perspective on existing data…really quickly
This is what I often refer to as quick and dirty analysis, we’ve just been landed with some data and want to understand it, so I’ll throw it into a Pivot Table and have a quick play around, usually I’ll uncover some headline number insights and also dodgy data that ‘ll go away and fix.
3. Summarise your information…really quickly
This is focused on summarising, so you should have a grasp of your data, you construct a Pivot Table of what you expect, then you spend a few minutes playing around with slicing and dicing to see if you can get more insights. If not, time to move on
4. Where Pivots shine more than formulas
Grouping data summaries, especially when reporting quarterly data, it’s a lot more work to do this with formulas, you can do it, I used to do it, but I would suggest letting Pivot Tables do the heavy lifting here.
5. Interface with PowerPivot
This is more advanced, when your dataset is very large (greater than >100,000 rows…occasionally 50,000 rows * 15-20 columns), then Pivot tables will slowww down, so you need PowerPivot, which is like taking out the Pivot Tables ford engine and replacing it with a V8!
So I’ve given you five good reasons to learn Pivot Tables and improve the way you deal with summarising your data. Now, I won’t leave you hanging, I’ve spent a few months developing this course on Pivot Tables with top-level financial analysts. The course is both quick to complete (two hours) and not very expensive. Here's what is in the course
Module 1: Introduction & Data Preparation
We will have a no-nonsense start and immediately build a Pivot Table, no theory or overly long explanations, straight into it! Then you go into the key topic of getting your data correct, I’ll briefly dive into Power Query to show how you can fix messy data and create what I call a ‘long boring list’
Module 2: Layout And Formats
Now that you have a basic Pivot, you are going to start to refine it into something increasingly useful and understanding Layouts is the first step to getting different perspectives on your data.
Module 3: Core Pivot Table Analysis
This is the meat of the course, this is where you really turn a Pivot Table in to an analysis engine by digging in to running totals, grouping data, sorting, filtering, multiple subtotals, insights with percentages of column and the perspectives with show value as.
Module 4: Filters And Slicers
Here’s where you will learn best practice on slicing and dicing your data with multiple filter techniques. Including more visual and specific filters like ‘Slicer’ and ‘Timeline’.
Module 5: Advanced Analysis
At this point in the course you will have a great understanding of Pivot Tables, it’s time to step up even more by implementing some of the more advanced Pivot Table analysis techniques such as Calculated fields, where you incorporate your own formulas in to your outputs. You will also learn about the power of connecting multiple data tables to one Pivot Table.
Module 6: Visuals And Reports
Here, you are going to be working conditional formatting in to your Pivot Table, you will look at PivotCharts which turn your data source directly in to a chart and finally you will take everything you have learned and build a Sales dashboard using key components such as Map charts.
Skill level: Beginner to Intermediate
Video: 2+ total hours
Get a certificate of completion that can be displayed on LinkedIn
What You'll Learn (click for details)
MODULE 1: INTRODUCTION & DATA PREPARATION
3. Altering the Data Source
4. Unpivot data to a long boring list
5. Moving row and column
6. Expand and Collapse field
MODULE 2: LAYOUT AND FORMATS
7. Format Error and Empty cells
8. Change Layout of Pivot Table
9. Place Pivot Table
10. Pivot Table Styles
11. Format Content in Pivot
MODULE 3: CORE PIVOT TABLE ANALYSIS
12. Summarise Value by
13. Multiple Subtotal
14. Percentage of column total
15. Difference From value as percentage
16. Double click for detail
17. Running Total
18. Grouping Year Month Quarter
MODULE 4: FILTERS AND SLICERS
20. Filtering – Part 1
21. Filtering – Part 2
22. Slicer – Insert Style Property
23. One Slicer connected to Multiple Pivots
24. Timeline Slicer
MODULE 5: ADVANCED ANALYSIS
25. Calculated Field
26. Link Multiple Table into One Pivot – Part 1
27. Link Multiple Table into One Pivot – Part 2
28. Calculated Field with IF statement
MODULE 6: VISUALS AND REPORTS
29. Conditional Formatting in Pivot – Part 1
30. Conditional Formatting in Pivot – Part 2
31. PivotCharts Part 1
32. PivotCharts Part 2 – Linking and Slicers
33. PivotCharts Part 3 – Formatting
34. Dashboard 1 – What we will build
35. Dashboard 2 – Our Dataset
36. Dashboard 3 – Pie Chart
37. Dashboard 4 – Line Chart
38. Dashboard 5 – Bar Chart
39. Dashboard 6 – Map Chart
40. Dashboard 7 – Putting it all together
We have had the pleasure of helping over 2000 professionals to better their skills and become indispensable to their companies. Here are some encouraging words from just a few:
Investment Banking PMO, London
“This course is excellent. The Skills I have developed have given me the confidence to tackle advanced problems in an Investment Banking environment. Unlike other courses, the tutorials are practical, to the point and are relevant to professionals. I would highly recommend this course.”
Senior Project Manager, New Zealand
“Sohail’s courses have provided me with the guidance and traction to hugely grow my earnings – in fact trebled my income over the past few weeks upon completion. This was due to the fact there’s a formula that for me clarified core missing pieces. For me this has been a real game changer and a catalyst.”
Senior Financial Analyst, Canada
“I applied many of the skills I learnt through your excel course to automate many aspects of my work and it has been a huge help. My next step is to improve my VBA skills!”
Project Manager, US
“The VBA course has been a game changer as the ability to automate was something I longed for in order to be more efficient and more effective as a professional. I also really liked the PowerPoint lesson and the Marketing Your Skills lesson. This course is the complete package, I would recommend this course 100%
Some Of The Companies Our Students Work At
Frequently Asked Questions
HOW DO YOU DELIVER YOUR COURSES?
Once you have signed up then we will send you an email with a link to access our training dashboard, you can access all of our courses from this dashboard.All courses consist of video lessons that can be viewed on your device of choice and also to truly enhance your learning you will access lots of exercise files relevant to the lessons.
THERE ARE MANY OPTIONS THESE DAYS TO LEARN, WHY GO WITH YOUR COURSES?
Great question, well frankly we make the best courses because all of our trainers have spent 10+ years using these skills in the real world! True knowledge is passed on from people who have been in the trenches, putting in long hours, solving real problems creatively. Our mission is to empower you with the very best of what works and more importantly, leave out what is unnecessary and thus a waste of your precious time.
HOW LONG WILL IT TAKE TO LEARN THESE SKILLS?
It depends on your level of commitment. Our students have seen incredible results from as little as 3o mins a day. But remember the magic is in applying the skills as much as learning them. Our courses are designed with this in mind. On the very practical end we have students who squeeze in a few mins here and there on their commute and some do dedicated 2-3 hour weekend sessions. One of our most successful students Jayne managed to get up at 4am to do her learning! This paid off massively as it landed her a six figure analyst job in a bank!
CAN MY EMPLOYER PAY FOR MY TRAINING?
Yes, this is quite common since we focus on helping actual corporate employees. Note that for teams we offer bulk discounts. However, this needs to be done with a corporate credit/debit card. We don’t accept transfer/remittances. Get in touch if you have any specific questions.
DO YOU HAVE A REFUND POLICY?
Yes, we do, we will dust it off if you need it because it rarely happens! You have 30 days to let us know that the training isn’t right for you and we will happily refund.
HOW MUCH DOES IT COST TO TAKE YOUR COURSES?
You can either purchase access to one course or all of our courses, which are listed above. What we have done in a post COVID-19 world is to keep our prices low to accommodate as many ambitious people from all over the world who are looking to upskill through our high level courses without the burden of high expense.
WHY DO YOU SELL IN MULTIPLE CURRENCIES?
We recognise that people prefer to pay in their own currencies and especially when our main product is a recurring membership to all our courses, you can rest assured knowing that fluctuations in currency won't affect your financial commitment.