4.67 out of 5
4.67
6 reviews on Udemy

Excel PivotTables for the Faint of Heart

How to Spin Your Data into Gold without Needing a Seatbelt
Instructor:
Allen Wyatt
40 students enrolled
English
Create, design, edit, and generally work with PivotTables.
Create, design, edit, and format PivotCharts.
Analyze large amounts of data faster and easier than ever before.

For many people, the mere mention of “PivotTables” sends shivers up their spine. To some, the PivotTable is one of those tools in Excel that is best left some mythical (or mystical) expert, perhaps locked away in some ivory tower. For such folks, the PivotTable remains the most esoteric of Excel’s analysis tools.

PivotTables don’t need to be so scary, and they certainly don’t need to be relegated to the realm of the esoteric. This tool has been available in Excel for years, yet relatively few people have learned how to use PivotTables to analyze their data in different ways. This is a pity, as PivotTables can allow you to see your data in new and exciting ways.

My PivotTables for the Faint of Heart course shows you how you can start using the PivotTable tool right away to spin your data into gold, without needing a seat belt (or a straight jacket).

Getting Ready to Learn

1
Examining the Course Outline

Every course needs a starting point, and this lecture is that point for Excel PivotTables for the Faint of Heart. Here you get an idea of the features built into the course and the content available so that you can "hit the ground running."

2
Downloading Course Resources

This lecture describes the sample data used throughout this course and helps students to download the data so that they can use it on their own systems.

3
Looking at the Sample Data

Once the sample data is downloaded (in the previous lecture), it is a good idea to look at the data and understand how it is put together. It is important for students to be comfortable and knowledgeable about the Excel worksheet so that they can understand how that data is used later in the course.

4
A Word about Excel Versions

PivotTables have been available in Excel for almost 25 years, but that doesn't mean that they are used the same in all versions of the program. In this lecture the student will better understand how different Excel versions are approached in this course and what version is used within the course.

PivotTable Basics

1
What Is a PivotTable?

For those new to PivotTables, it is helpful to understand exactly what they are. This lecture focuses on answering that question and showing the four major parts applicable to each and every PivotTable.

2
Getting Your Data Ready

Not all data is well suited for use in a PivotTable. This lecture focuses on how you can determine if your data "makes the cut" and can be better analyzed using Excel's PivotTable tools.

Creating a PivotTable

1
Section 3 Overview

This lecture provides an introduction and high-level overview of what is covered in this section of the course.

2
Your First PivotTable Steps

This lecture is fundamental to everything you do with PivotTables. Here you discover exactly how easy it is to create a PivotTable with your data; it just takes a few steps and a couple of minutes!

3
Adding Fields to Your PivotTable

Once a PivotTable has been created, the first task is to determine where the fields representing your data should be placed. Excel makes it easy to add and move fields, as discussed in this lecture.

4
Filtering Data

Filtering the data presented in a PivotTable allows you to better focus in on what you want to analyze. In this lecture you discover the basics of data filtering.

5
Developing Groups

One of the features of PivotTables is that you can, if desired, group data together. This allows you to present and understand your data in more meaningful ways.

Variations on Creating PivotTables

1
Section 4 Overview

This lecture provides an introduction and high-level overview of what is covered in this section of the course.

2
Using Quick Views to Create a PivotTable

One of the new features added in Excel 2013 is the ability to quickly generate PivotTables with a tool called Quick Views. This lecture focuses on this tool, demonstrating how easy it is to create a PivotTable that comes very close to a finished table, all in a few quick clicks.

3
Using the Classic PivotTable Layout

In much older versions of Excel (those before Excel 2007), the program used a different layout for building PivotTables. This lecture focuses on how you can use that older layout when you are designing your tables. (Some people still prefer the old ways of doing things!)

4
Using Classic PivotTable Layout as the Default

If you decide you like the classic layout better than the newer layout currently used by Excel, you can discover in this lecture how to change the default to match your preference.

Editing a PivotTable

1
Section 5 Overview

This lecture provides an introduction and high-level overview of what is covered in this section of the course.

2
Using Ribbon Tools to Edit a PivotTable

The primary method of editing PivotTables is through the use of the tools on the Analyze or Options tabs of the ribbon. This lecture shows you how to use the resources that Excel makes available for this purpose.

3
Directly Changing a PivotTable Field

Every field you place in a PivotTable has settings (or, if you prefer, properties) associated with that field. This lecture focuses on how you can modify those field settings to change how the field is treated and displayed in the PivotTable.

4
Changing Row and Column Fields

PivotTables are made of up rows and columns whose contents are determined by the fields in your source data. In this lecture you examine how the settings for your rows and columns can affect what you see in your PivotTable.

5
Specifying How Data is Summarized

PivotTables are used to aggregate or summarize large amounts of data. You can control how that data is aggregated by using the Value Field Settings dialog box. That is the focus of this lecture, where you discover the many ways you can modify these settings.

6
Using the Mouse to Edit a PivotTable

Using the mouse is a critical part of working with a computer these days. You can use the mouse to editing and manipulate what is in your PivotTable. In this lecture you learn the fine art of working effectively with the mouse toward this end.

7
Copying a PivotTable

When you want to "try out" some edits in your PivotTable, you might want to make a copy of that PivotTable first. This lecture looks at the three different ways you can create that copy.

8
Deleting a PivotTable

Done with the PivotTable you have been working with? You'll probably want to delete it so that your workbook loads faster and takes less memory. This lecture shows you how easy it is to get rid of that unwanted PivotTable.

Using Slicers and Timelines

1
Section 6 Overview

This lecture provides an introduction and high-level overview of what is covered in this section of the course.

2
Adding and Using Slicers

One of the PivotTable-related features added in Excel 2010 is the ability to filter your data using what is called a slicer. In this lecture you are introduced to how easy it is to use this tool to see just the data you want.

3
Adding and Using Timelines

A timeline is, essentially, a slicer designed to work with fields that contain dates. Timelines were first introduced in Excel 2013 and provided an easy way to filter your data based upon time ranges, as you discover in this lecture.

4
Clearing a Slicer or a Timeline

As you work with slicers and timelines, you are effectively (and easily) filtering the data that appears in your PivotTable. In this lecture you discover how to clear the effects of both slicers and timelines so that all your data can be displayed, as you originally intended.

5
Deleting a Slicer or a Timeline

While slicers and timelines are super convenient to use, at some point you may want to remove them completely from your PivotTable. In this lecture you discover just how easy it is to permanently get rid of them.

Formatting PivotTables

1
Section 7 Overview

This lecture provides an introduction and high-level overview of what is covered in this section of the course.

2
Specifying a PivotTable Layout

Once you have the data in your PivotTable set up, you can turn towards making the PivotTable look just the way you want. The first thing you should do is to choose a layout, as described in this lecture.

3
Working with PivotTable Formats

You can format your PivotTable using many of the same techniques you use to format cells in a regular Excel worksheet. This lecture focuses on the formatting tools that Excel puts at your fingertips.

4
Maintaining Formatting when Refreshing PivotTables

When you update your PivotTable by refreshing it, you'll find it is not unusual for your formatting to change. This lecture gives you information on how to protect your formatting so that it isn't modified during a refresh.

5
Default Formatting for PivotTables

WIth just a bit of forethought, you can modify the formatting that Excel applies to your PivotTables when you first create them. Here's how to do it!

Adding Calculated Data

1
Section 8 Overview

This lecture provides an introduction and high-level overview of what is covered in this section of the course.

2
Creating a Calculated Field

Calculated fields are a way for you to add formulas to your PivotTable, thereby calculating data that isn't explicitly in the PivotTable's underlying data. This lecture demonstrates how easy it is to add calculated fields to your PivotTables.

3
Creating a Calculated Item

Another way to add formulas to your PivotTables is through the use of calculated items. This lecture focus on calculated items, how to use them in your PivotTables, and how they differ from calculated fields.

4
Weighted Averages in a PivotTable

This lecture provides a hand-on, real-world example of how you can use calculated data in your PivotTables. Using a special set of data, students build a PivotTable and use the calculated data to display a weighted average.

Refreshing Data

1
Section 9 Overview

This lecture provides an introduction and high-level overview of what is covered in this section of the course.

2
Refreshing a PivotTable

The data aggregated in a PivotTable is usually based on data contained in a worksheet. When the data in the worksheet changes, the PivotTable is not automatically updated. You'll need, instead, to refresh the PivotTable, as described in this lecture.

3
Refreshing Multiple PivotTables

It is not unusual for people to create multiple PivotTables in a workbook. These can be based upon the same data or upon different data sources. Fortunately, Excel provides a way that you can update all of the PivotTables in a workbook at the same time, as discussed in this lecture.

4
Starting with Refreshed PivotTables

Excel allows you to automatically update PivotTables when you first open a workbook. This helps you always start with the most current and "freshest" data. This lecture shows you how you can make the configuration change necessary to set this feature.

5
Verifying Data Ranges

If you add data to the data source on which your PivotTable is based, you need to let Excel explicitly know this. If you don't, your PivotTable will be based on incomplete data. This lecture looks at how to change the data range on which your PivotTable is based.

Diving Into the Information in a PivotTable

1
Section 10 Overview

This lecture provides an introduction and high-level overview of what is covered in this section of the course.

2
Expanding PivotTable Rows to Sheets

When you are analyzing data with a PivotTable, it is often helpful to "drill down" to see what data is used to arrive at some value shown in the table. This lecture shows you how you can do the drilling and come up with all the detail data you need.

3
Error in a Linked PivotTable Value

If you reference information in a PivotTable from another workbook, you may be surprised if you get an error message about the link at a future time. This lecture examines the ways in which you can create the link in order to avoid potential errors.

4
Rows in a PivotTable

With your PivotTable created, you may have a need to know the number of rows that it contains. This lecture discusses several different ways you can determine the row count using functions and formulas in a regular worksheet.

Tweaking How You Use PivotTables

1
Section 11 Overview

This lecture provides an introduction and high-level overview of what is covered in this section of the course.

2
Working without Columns

Just because PivotTables allow you to add rows, columns, and values doesn't mean that you have to add all three. This lecture shows you how easy it is to analyze your data using a PivotTable that relies only on rows and values.

3
Filtering PivotTable Data for a Running Period

Filtering (as described earlier in this course) is quite powerful, but there are other ways you can limit the data presented in your PivotTables. This lecture looks at one such technique which involves the use of a "helper column" in the data on which the PivotTable is based.

4
Reducing Workbook File Size

Adding PivotTables to a workbook can easily increase the size of that workbook. Add multiple PivotTables, and the increased size is a surety. This lecture looks at ways you can decrease the size of these workbooks in order to make them more manageable.

Working with PivotCharts

1
Section 12 Overview

This lecture provides an introduction and high-level overview of what is covered in this section of the course.

2
Starting to Create a PivotChart

What are the first steps in creating a PivotChart? When you work through this lecture, you'll find that the steps are very similar to those you follow when creating a PivotTable.

3
Building Your PivotChart

The steps in building a PivotChart are similar to those in building a PivotTable, but there are differences, particularly in some terminology. This lecture brings you up to speed on how you can actually put your PivotChart together.

4
Editing a PivotChart

Once your start creating a PivotChart, you'll undoubtedly have a need to edit the way the data is presented in the chart. This lecture provides information on the various ribbon tabs and their tools that allow you to make all the edits you need.

5
Formatting a PivotChart

When formatting a PivotChart, you'll use many of the same tools that you use to format regular charts. This lecture examines the various tools that Excel makes available to do your formatting.

Wrapping Up

1
Creating Your Own Testing Data

When you want to practice your newly acquired PivotTable skills, you'll need some data to work with. If you have that data available from your job, that's great. If not, then you'll love this lecture; it focuses on how you can create your own testing data to use in your practice.

2
Where to Go from Here

The course is over, but the learning has hopefully just begun. This lecture discusses ideas on where students can go for more information about PivotTables and Excel in general.

You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4.7
4.7 out of 5
6 Ratings

Detailed Rating

Stars 5
3
Stars 4
3
Stars 3
0
Stars 2
0
Stars 1
0
393373694098eb7c33e8c76d1118a50e
30-Day Money-Back Guarantee

Includes

4 hours on-demand video
Full lifetime access
Access on mobile and TV
Certificate of Completion