Did you know that a significant percentage of people have not mastered the advanced concepts of Excel?
Microsoft Excel is probably the most common term you have come across in your student or professional career. If you are an aspirant looking for a career in analytics or data science, a student or a professional, you must be aware that familiarity with the concepts of Microsoft Excel is among the core competencies to thrive in the profession.
With the technical advancement, students are expected to be equipped with the knowledge of Microsoft Excel. There are several useful features in Excel, however, Pivot Tables are amongst the most powerful features provided in Excel.The knowledge of Pivot tables will change your approach towards using Excel.
Pivot Tables are tools that enable you to summarize large datasets and acquire relevant information from them quickly. It is easy to use and you can analyze multiple rows in a short time that could otherwise take many hours with the use of complex formulas.
To find a course on Excel that covers all the essential topics is not easy to find, as multiple platforms have shifted focus on providing courses that are trending in the job market.If you are looking to level-up your skills with important concepts of Excel, then Udemy offers a course on Excel Pivot Table Basics which has been popular among learners.
This course is beneficial for learners to get familiar with the basic concepts of Pivot Tables before moving on to an advanced course for the same. There are certain basic requirements for this course such as how to navigate the Excel ribbon, the knowledge of creating basic formulas using Excel, and how to copy and paste the information in Excel.
What is a Pivot Table?
Pivot tables are one of the most powerful features in Excel. A Pivot table allows extracting information from a large data set with very little effort. It can rightly be considered as an interactive approach to viewing a report. There is no requirement for complex formulas to view data from different perspectives.
The data can be grouped into categories or broken down into monthly and yearly reports as well. There is a provision of filters that allows to include or exclude categories in the dataset that are relevant to a particular report. You can build charts to visualize the data reports for analysis.
What to Expect from the Course?
Section 1: Preparing Your Data
Introduction to Preparing Your Data
The first section of the course navigates you with important concepts. The process of structuring the information is presented in this section. Restructuring of data has been covered which is among the most important components in Excel. If data is incorrectly structured, then it diminishes the possibility of leveraging pivot tables.
The tutor highlights an important fact that the data is not often handed over in the appropriate format to be used directly for pivot tables, hence structuring plays a crucial role. The section further demonstrates how to set up the workbook for structuring the information. It is followed by the use of certain formulas to answer questions which are then summarized to gain insights into the data. The same information is reorganized to be able to leverage pivot tables to get you started.
Formulas vs Pivot Tables
The second module of the first section describes how Pivot tables are more powerful in comparison to using formulas for data analysis. The instructor defines how formulas for analyzing data is slow and inefficient. Pivot tables are simpler to use but a lot of people are not aware of how to use them. There is a follow-along demonstration of an example in which the data is analyzed by using formulas as well as Pivot tables to highlight the differences.
The example is about sales data that needs to be summarized to extract meaningful information out of the dataset. The use of filters is demonstrated as well for better understanding. The time taken to complete the task using Pivot tables took 15 seconds which was much more efficient than using formulas for the same task. There are also additional resources that are shared in this section.
How to Fix Your Data?
The section deals with trickier aspects of the data. The previous example had the right format for the data, however, the tutor looks into the factor when the information is not available in the right format for analysis. The cleaning of data is explained which is explained with the help of an example. It is also highlighted that sometimes there is a need to manually analyze the data before cleaning.
The data that is in the right format is copied and pasted into the workbook. The whole process of manual processing takes about three minutes in total. There are additional tips that it is better served to use manual processing for structuring the data rather than the formulas for compiling the data.
Related: Udemy Review
Section 2: Creating Pivot Tables
Introduction to Creating Pivot Tables
The tutor provides a walkthrough of the section and explains what will be the outcome at the end of this section. You will be able to create pivot tables on your data along with the basic skills that are needed for creating pivot tables. You will be able to have a clear understanding of working with your data and how to explore them.
Creating a Pivot Table with our Sample Data
In this section, the tutor covers the topic of creating a pivot table. The section begins with data that is needed to be downloaded for the example. The sample data is provided in the appropriate format so there is no need for changes in terms of cleaning and structuring the data.
The example consists of a sales database of a car company with region wise data that is available. There is a follow-along demonstration to create the pivot table out of the sample data. There is an explanation on how you can select your range manually which is followed by the concept of placing the pivot table report. The filters are used for the columns, rows, and values. It is explained that there is no benefit of a pivot table without placing something in the value space. The value area is where the information is placed for aggregation.
The creation of a new column for each unique value in the dataset has been shown in the example as well. Another place where the information can be placed in the filters area which is a bit outside of the pivot table. This area is used to filter information upon selecting the required part. The option to select multiple items is also described in detail. Finally, the proper way to create a pivot table with relevant features is shown at the end of the example. The section also has additional resources for your understanding.
Using Multiple Fields to Analyze Your Data
The instructor focuses upon the analysis of individual fields of the data. It is emphasized that the data needs to be broken down for an in-depth analysis. The outlook of the pivot table is determined by the number of fields in any area within a pivot table. The order of the fields within the area defines the overall structure of the pivot table. The example is demonstrated with the help of region-based sales data for a clearer understanding. The section further highlights the process of grouping the region within a pivot table.
Formatting Values
The section discusses some key points concerning values in the data. The tutor explains how certain data has pricing numbers but it is difficult to understand the actual value. For example, a certain price that could be 24 million or 2 million or 2.4 million is hard to tell without proper formatting. Hence, to make the numbers more readable, it is of utmost importance to format the values.
It is described that the Excel formatting options are very familiar, but to format the cells in the pivot table differs from the actual approach. If a pivot table is not told explicitly about what the changes are then the results will differ in the subtotal values as it might not reflect the necessary changes. It is common when working with decimal-based values. The instructor provides an example that demonstrates the options that are needed to be kept in mind while formatting values in a pivot table.
Losing your Field List
This section deep dives into an important concept of losing the field list. The tutor explains that it can be confusing if it happens when you have just started using Pivot tables. It is explained how the pivot tools and ribbon area works.
When you close out the pivot table, the tools and the ribbons sections along with the fields disappear, however, there are situations when you click back on the pivot table, the tools, ribbons area comes back while the fields area doesn’t come back. It is explained about the additional option of tackling such a problem and what is the procedure that needs to be followed to have the fields back as well.
Summarizing Your Data
The field settings present a screen with the option of summarizing values with the type of calculation that you require. However, it doesn’t always work in the case of the sum of the purchase price. The example is demonstrated clearly on how to efficiently aggregate the information. Additionally, there are options of standard deviation and variance calculations as well which is beneficial for statisticians.
Section 3: Lock in Your Knowledge
Section Introduction
This part of the course is an introduction to the course contents in section three of the module. The tutor provides a few challenges in the form of exercises and quizzes to be completed before moving on to the next module. Certain questions are related to the sample data. Also, there is a provision of a sample workbook with challenges built in them. The tutor encourages you to go through the challenges to get more familiar with tricky questions related to the pivot table and data.
Pivot Table Basics Challenge
The first pivot table challenge deals with identifying the total value of Toyota Land Cruiser sales from the data provided. The second set of the challenge involves the identification of the number of Subaru foresters that were sold whereas the final challenge involves the identification of the average Subaru sales price for Dusty and Dusty.
Challenge Answers
This section particularly discusses the answers to the challenge in detail. The tutor also completes the challenges and compares the solutions with the learners. The solutions provided by the tutor for this section are as follows.
For vehicle sales data, you just need to create a Pivot Table off of the information provided.
So go to insert a pivot table.
Select OK.
A new pivot table in a new worksheet code is available.
For the first question and the challenge was to identify the total value of Toyota Land Cruiser sales though when we talk about sales we know our vehicle sales data were referring to purchase price so drag purchase price into my values area and just while we’re here we’ll update my value field settings and update the number format and up to 2 accounting with zero decimal places just because the focus is on the total vehicle sales in dollars.
To identify the value of Toyota Land Cruiser sales, Toyota is the make and Land Cruiser is the model so you can see that in the Toyota section of the data.
Under Toyota, there is the total value of the Toyota Land Cruiser sales which is 12 million two hundred fifty-eight thousand five hundred and eighty-one dollars.
The next question is how many Subaru Forester were sold?
The tutor provides the solution by explaining that the information is already organized with the make and the model which is Subaru Forester. All you have to do is identify the count of Subaru Foresters.
So instead of updating the value, the instructor provides an additional tip to use for this challenge.
There was an explanation for being able to drag multiple field values into any area within the pivot table. That is what you are expected to use.
Although, it is not the most intuitive thing with Pivot Tables because the field is already in the value section but there is a possibility to drag it in more than once which is fine to do so.
Once the solution is implemented, you can see that it has defaulted to some of the purchase prices in the data. As there is already a purchase price in the pivot table, you are expected to change the name of those fields with the help of the field settings.
You can overwrite and change it to total sales. Now, there is a field with total sales and this field is required to represent the vehicles sold so you can do it in the field settings and change it to count and create a custom name and call it vehicle sold. This provides the answer to the second challenge which is 329 Subaru Forester were sold.
For the final challenge question about What is the average Subaru sales
price for Dusty? To answer this question, the tutor begins from the scratch and empties the pivot table. It is explained how to empty the pivot table using the clear button. To solve the final question, the purchase price is taken into consideration and the average option is selected from the field setting.
The average purchase price of all of the vehicle sales that exist in the database is displayed. You can update to account for zero decimal places. The make of the vehicle is important for this challenge. The make is added into the columns which show Dusty’s average Subaru sale. The tutor provides another method to solve this task with the use of filters. It is explained that pivot tables are flexible and you can answer different questions with a different approach and yet you can achieve the same answer.
Practice Workbook
In the practice workbook, the instructor provides a hands-on experience with more questions. New worksheets are provided with a new set of data and new problems to master your skills. The exercise contains important questions to enhance your practical knowledge and it is suggested to go through this section as well. There are additional resources shared for more information.
Section 4. Bonus Lecture: Thank You and Coupon
For those looking to move to advanced concepts, the tutor explains the provision for an advanced course offered by him. It is a comprehensive and in-depth course on Pivot tables. With an experience of over a decade, the tutor highlights very important concepts in the advanced course as well. There is a coupon provided by the tutor that allows you to take up the course with that amount. It is a 10 USD coupon but it may vary according to the country you are accessing from.
Benefits of this course
Learning Path
The advantage of this course on Excel Pivot table is the content that has been provided for a beginner course. The course doesn’t overwhelm the newcomers as certain introductory courses focus on many concepts at once which confuses them. It follows a very simple approach and begins from very scratch with the installation process.
Some additional resources and challenges and quizzes have been provided to enhance your hands-on experience. The core concepts are explained well. A major benefit is that all the concepts are also demonstrated with an example that learners can practice and improve on. The theoretical explanations are clear and simple to relate to. The examples are easy to follow for a beginner.
Certification
The course provides a certificate of completion and access to online content, certification, and direct access to the instructor for Q&A, and direct reply from the instructor for the paid option. It is important to note that access to online content is available for the free version and the remaining options from the paid version are not being offered.
Learning Community
The learning community on Udemy comprises students and professionals and instructors who are constantly sharing additional resources and important discussions on relevant topics. There are important tips that are shared as well that can benefit learners to implement along the course and helpful for their careers. The instructors are actively communicating with the students and answering their doubts that they post on the forum.
Hands-on Approach
The course provides a hands-on learning experience. The course provides sufficient exposure to theoretical concepts while making the learners practice them in the additional challenges that have been provided for having more practical knowledge for the same.
A hands-on approach lets you grasp the concept much faster. Additionally, there are tips and alternative methods that are discussed during the practical exercises. Additionally, every installation has been demonstrated.
The examples that are being covered are also shown practically, thus enhancing the overall learning process. The tutor has used a single example throughout the course. This helps to understand the practical part more clearly as too many examples can lead to confusion over its implementation. The course is a perfect balance of theory and practical knowledge and provides crucial information along with additional resources.
About the Instructor
Matt Jackson is a digital analytics and SEO professional. He has been using Excel for over 15 years. He has been involved with some of Australia’s largest companies for developing business intelligence solutions. For business analytics to derive meaningful business solutions, he has constantly used Excel as one of the primary tools.
From the experience of working in large organizations, he has been motivated by the reactions he has witnessed when he could show something new for the first time. Currently, Matt Jackson is the director at Stay Digital, a company dedicated to helping local businesses thrive online.
Overall Rating of the Course
- Instructor Expertise: 5
- Additional Resources: 5
- Course Content Quality: 5
- Career Value: 5
- Delivery of the Content: 5
- Visuals and Readability: 5
- Examples: 5
- Speed of Delivering the Content: 5
- Basic Concepts: 5
- Overall Rating: 5
