Improve Your Productivity: Master Excel Pivot Tables
4.9 (68 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,468 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Improve Your Productivity: Master Excel Pivot Tables to your Wishlist.

Add to Wishlist

Improve Your Productivity: Master Excel Pivot Tables

Become a Microsoft Excel Certified Specialist! The perfect first step towards a Professional Microsoft Certification!
4.9 (68 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,468 students enrolled
Created by Logan Spiers
Last updated 2/2016
English
Current price: $10 Original price: $20 Discount: 50% off
1 day left at this price!
30-Day Money-Back Guarantee
Includes:
  • 2.5 hours on-demand video
  • 39 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Satisfy Professional Development Unit (PDU) Requirements (2.5 PDUs), Project Management Institute Accreditited
  • First Step Towards Microsoft Excel Specialist Certification
  • Improve Productivity
  • Immediately Apply Skills to your Job
  • Create PivotTables
  • Format Data into Tables
  • Analyze Data in PivotTables
  • Filter Data based on desired Information
  • Extract Data using Formulas
  • Create PivotCharts based on PivotTables
  • Reformat PivotCharts
  • Rename, Rearrange, Delete Worksheet Tabs
  • Insert Columns into a worksheet
  • Maintain PivotTables with current data using Refresh
View Curriculum
Requirements
  • Microsoft Excel
  • The want to attain the Microsoft Excel Specialist Certification
  • The need to advance career
  • The need to increase productivity
Description

***February 2016 - New Advanced Content Live! Over 1.5 Hours of added content!***

Over 800 enrolled since January 2016! One of Udemy's fastest growing courses!

In today's job market Excel proficiency is required. The ability to not only fluently use Excel but implement it into the daily routine is increasingly mandatory. A majority of employers requires Excel proficiency but lack any real on-the-job training in this software.

Pro tip: Want to improve your Resume? Become a Microsoft Certified Excel Specialist! This course is the first step towards becoming certified! It will prepare you in a core aspect of the certification and set you on the path towards one of the most valuable Professional Certifications you can earn!

  • Excel 2010 Certification Exam Offered via Microsoft Learning Exam #77-882
  • See Promo Video for Link!

There are numerous ways to learn, yet many lack the real world application you desire. You need to be able to take data sets your job uses and turn them into user-friendly, easy-to-analyze reports that your boss and even his bosses can read to use to make real-time decisions. Today's industries are more data driven than ever; will you be the one to shine?

This is comprehensive PivotTable course will provide you with a step-by-step guide into developing and using PivotTables. It is designed in a way that even the most basic beginner to an experienced user can follow along and take away valuable knowledge and skills you can immediately apply to your job. These are Excel's most important tools. Quite literally, all analysis stems from PivotTables. In short, they are a summarizing tool to the basic user. But developed by an ace, they can literally shape the decisions a company makes!

By the end of this course you will be able to not only be well versed in Excel PivotTables, but empowered with the skills to develop and implement powerful, real-time reports your bosses, even executives would be impressed with!

This course covers:

  • Formatting data as a table
  • Optimizing your Workbook
  • Using logic functions to extract information
  • Applying filters and sorts
  • Creating multiple PivotTables
  • Applying PivotTable Styles
  • Drilling down details
  • Presenting sales data
  • Using Advanced Formulas
    • GetPivotData
    • IfError
    • Left & Right
  • Manipulating and configuring data for real-time decision making
  • Refreshing you PivotTables
  • Fixing Common Errors
  • Creating PivotCharts
  • Advanced PivotChart Formatting
  • Building & Using PivotChart Dashboards

Mastering PivotTables is a massive leap forward to mastering Excel, this is the solution!

Who is the target audience?
  • People working towards Microsoft Excel Specialist Certification
  • Project Management Professionals (PMP's) Looking to Satisfy PDU Requirements
  • Course can be completed by all levels of experience with Excel
  • Beginners can follow this course easily
  • Intermediate Users can Learn Advanced Techniques such as Dashboards
  • Anyone who wants to make themselves more valuable in the workplace
  • Anyone who wants to advance their professional careers
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 45 Lectures Collapse All 45 Lectures 02:37:14
+
Overview
1 Lecture 01:31

This is a quick overview and preview of what a PivotTable looks like and how it can be used to make a usefull report.

Preview 01:31
+
Workbook Setup
5 Lectures 07:36

This video will demonstrate the basic function of inserting columns. You will use these inserted columns to add new data points that will be used later in the creation of PivotTables.

See QUICK REFERENCE GUIDE

Tutorial 2 - DATA TABLE SETUP - Inserting Columns
00:58

This video will demonstrate how to not only structure all formulas, but provides you with 2 very important formulas that can be used to extract data out of a single cell. This will provide you with the ability to take a phrase or piece of data, and implement logic formulas to extract the year from a date as well as the name of a month spelled out, AUTOMATICALLY!

See QUICK REFERENCE GUIDE

Tutorial 3 - DATA TABLE SETUP - Extract Data With Formulas
02:18

The use of autofills are an incredibly usefull tool when working with massive amounts of data. The ability to take a formula you have written once, and autofill it for an entire column is impreative to your success when using Excel. This technioque can be applied to multiple uses not just formulas.

See QUICK REFERENCE GUIDE

Preview 01:12

This video will demonstrate how to form your dataset into a Table. This formatting allows for the use of several exclusive tools. It also expedites the creation of PivotTables and formula writing.

See QUICK REFERENCE GUIDE

Tutorial 5 - DATA TABLE SETUP - Format as a Table
01:18

This video will demonstrate how easily you can filter your data and sort it useing the Filter & Sorts in a Table Format.

See QUICK REFERENCE GUIDE

Tutorial 6 - DATA TABLE SETUP - Table Filters & Sorts
01:50
+
Data Analysis & Reporting
9 Lectures 27:36

This is the most important tutorial of the entire course. This course will demonstrate the diversity you can achieve in analysis of data. It will all show you how easy it will be to move from an Excel novice to expert by simply using this tool.

See QUICK REFERENCE GUIDE

Tutorial 7 - DATA ANALYSIS - Making PivotTables
05:42

This video will demonstrate how to convert the unformatted values that result in a default PivotTable and change them into all types of formats. This specifically shows the options then demonstrates the Accounting Format. It also will show you how you can change the sum operation to other like average, products, counts, min/max, etc.

See QUICK REFERENCE GUIDE

Tutorial 8 - DATA ANALYSIS - Formatting Values
03:00

One of the many important attributes of a PivotTable is the ability to add multiple layers of data. This allows you to drill down to very specific details about your data, which is an incredibly useful tool to decision making in business.

See QUICK REFERENCE GUIDE

Preview 01:54

This short video demonstrates how to expand and collapse PivotTables to show more or less data.

See QUICK REFERENCE GUIDE

Tutorial 10 - DATA ANALYSIS - Expand & Collapse PivotTables
02:08

Often when analyzing data, you will want to filter your PivotTables to show only specific data. This tutorial demonstrates how to add Report Filters to your PivotTable as well as use them and their effects.

See QUICK REFERENCE GUIDE

Tutorial 11 - DATA ANALYSIS - Report Filters
02:37

The most important feature of a PivotTable is the ability to update and add data then simply refresh it. PivotTables create a real-time way of organizing and analyzing data for this reason. This video will demonstrate the various ways you can Refresh your data.

See QUICK REFERENCE GUIDE

Tutorial 12 - DATA ANALYSIS - Refreshing PivotTables
02:38

This video demonstrates the various capabilities of advanced filtering your data once it is in a PivotTable using both the Values and the Labels as your criteria.

See QUICK REFERENCE GUIDE

Tutorial 13 - DATA ANALYSIS - Advanced Filters
03:41

Often when presneting data in any form it is encouraged to have the data set in a logic order. The data will often appear in the PivotTable in a random order. This makes graphs look disorganized and makes it much more difficult to read the data. This video will demonstrate how to sort the multiple levels of a PivotTable.

See QUICK REFERENCE GUIDE

Tutorial 14 - DATA ANALYSIS - Sorting PivotTables
02:27

This video will demonstrate how to take the resulting PivotTables and add PivotCharts to your analysis very simply.

See QUICK REFERENCE GUIDE

Tutorial 15 - DATA ANALYSIS - PivotCharts
03:29
+
Formatting the Workbook
1 Lecture 02:06

This video will demonsrate how to format the workbook. It focuses on the renaming, rearranging, and deleting worksheet tabs.

See QUICK REFERENCE GUIDE

Tutorial 16 - FORMATTING WORKBOOK - Rearrange, Rename, Delete Worksheet Tabs
02:06
+
Worksheet Formatting
7 Lectures 16:05

This video will demonstrate how to insert rows into a worksheet.

See QUICK REFERENCE GUIDE

Bonus Tutorial 17 - Inserting Rows
01:09

This video demonstrates how to merge cells.

See QUICK REFERENCE GUIDE

Bonus Tutorial 18 - Merging Cells
03:24

Excel provides many Style options you can add to your PivotTables. This video demonstrates how to implement PivotTable Styles.

See QUICK REFERENCE GUIDE

Preview 02:17

This video demonstrates how to adjust row hieghts in a few different ways.

See QUICK REFERENCE GUIDE

Bonus Tutorial 20 - Row Heights
01:44

This video demonstrates how to adjust column widths manually, automatically, and for best fit.

See QUICK REFERENCE GUIDE

Bonus Tutorial 21 - Columns Widths
02:21

In this video you will learn how to adjust fonts, increase font size, and splash color into cells for more aesthect formatting.

See QUICK REFERENCE GUIDE

Bonus Tutorial 22 - Fonts, Fills, Formats
03:28

This really nifty tool allows you to instantly copy all formatting from a range or a single cell and paste it on a new cell.

See QUICK REFERENCE GUIDE

Preview 01:42
+
Advanced Workbook Formatting
2 Lectures 05:08

In this tutorial you will learn the organizing technique of Coloring your worksheet tabs. You will learn different methods and color modes.

Tutorial 24 - Worksheet Tab Colors
03:01

In this video you will learn the how to use Named Ranges when using PivotTables. This allows for ease of making selections of data and organizing data when it is not in a Formatted Table.

Tutorial 25 - Naming Ranges
02:07
+
Advanced Pivot Table Techniques
12 Lectures 59:36

Very often when using PivotTables you will need to update and change the data source of a PivotTable. This video will demonstrate the various ways you can use to complete this very important function.

Tutorial 26 - Changing The Data Source
04:55

There are three layouts when using PivotTables. Each one has it's own benefits and uique characteristics. This video will demonstrate the three types and explain the differences.

Tutorial 27 - PivotTable Report Layouts
03:45

All PivotTables, default in the Compact Report Layout. This layout comes with automatics subtotals on top of each group. This video will demonstrate how to change the postion and even remove subtotals. You will also learn about repeating item labels and the usage of them for future tutorials.

Tutorial 28 - Subtotals & Repeating Labels
03:44

There are a vast number of uses for PivotTables, but that does come with limitations. Luckily there are various workarounds of these limitations, the PivotTable that sources another PivotTables is one of the most widespread workarounds.

Tutorial 29 - PivotTable on a PivotTable
06:03

The Get Pivot Data Formula is certainly a favorite! This is one of the most powerful and useful formulas as it allows you to grab data from PivotTables when there is several variables. Grabbing the same data using nested If Statements can become very difficult and almost impossible. This video will demonstrate the usefulness of this Expert Formula!

Tutorial 30 - GetPivotData Formula
04:57

When writing formulas there is often outputs that will end with errors; however, these errors are not always an accident or really errors at all. Often the errored output is the result of if statements or vlookup. Excel has formulas like IF Error so that when these outputs do result you can make them blank, add a phrase that identifies the error, or sets a default output like $0 in this tutorial. This is an incredibly important formula.

Preview 03:22

This is a very important tool when using Excel and PivotTables. In this tutorial you will learn how to set Data Validation requirements on data. It also demonstrates how to incorporate a drop down list into your data sets so you can select from a list rather than type and entry. This is incredibly useful when you have to enter the data that repeats over and over again. This prevents typing errors or erroneous data! This is one of my favorite techniques!

Preview 05:56

In this tutorial you will learn the various methods to move PivotTables around worksheets and within Workbooks. This is a very important skill as you will move PivotTables constantly when using Excel in any capacity.

Tutorial 33 - Moving PivotTables
03:02

PivotTables will almost always grow. Therefore, it is important to plan for this when setting up your PivotTables on a worksheet. It is almost always best practice to set them next to eachother. This tutorial will show you the potential errors you may come accross when dealing with multiple PivotTables.

Tutorial 34 - Overlapping PivotTables
03:45

One of the most frustrating issues when using Excel, is validating data. Data must be exact otherwise Excel will read it wrong, typically causing all sorts of errors. It is imperative as a data manager or when using Excel to ensure the integrity of your data. This tutorial will demonstrate a clever, easy technique for validating your data.

Tutorial 35 - Validate Dataset Errors
07:19

In this tutorial you will learn how to create PivotTables that display percentages without having to complete any additonal calculations! This is a very usefull when analyzing sales mixes and other similar types of business applications.

Tutorial 36 - Percentage PivotTables
05:55

This video will teach you how to create three different PivotCharts using Percentages. These are very impressive in business applications as they display incredibly valuable information for decision making.

Tutorial 37 - Percentage PivotCharts
06:53
+
PivotChart Dashboard
8 Lectures 37:36

Now this is the crown jewel of PivotTables. This is a very advanced skill set. By now you are an expert of all things PivotTables and more than ready to accomplish this. This is a skill set that sets you apart from all others using Excel. The Dashboard is an incredibly effective and easy means of displaying data and setting filters. This tutorial sets up this process. You will create a total of 5 different PivotTables & PivotCharts to display on the Dashboard. You will also perform several advanced PivotChart Formatting techniques! You will need to save your work, as each tutorial 38-45 will build on the previous. Use this resource for the next several tutorials.

Preview 03:09

In this video you will create the first PivotChart for the Dashboard. In this Tutorial you will perform several very advanced formatting Techniques which you will copy throughout the remainder of this Course. Once complete please save your work as you will build on this in the next tutorial.

Preview 07:32

In this tutorial you will copy the worksheet tab of the previous Tutorial and then reorganize the PivotChart to create the second PivotChart for the Dashboard. Once complete please save your work as you will build on this in the next tutorial.

Tutorial 40 - Dashboard - Create PivotChart 2
02:21

In this tutorial you will copy the worksheet tab of the previous Tutorial and then reorganize the PivotChart to create the third PivotChart for the Dashboard. Once complete please save your work as you will build on this in the next tutorial.

Tutorial 41 - Dashboard - Create PivotChart 3
03:19

In this tutorial you will copy the worksheet tab of the previous Tutorial and then reorganize the PivotChart to create the fourth PivotChart for the Dashboard. Once complete please save your work as you will build on this in the next tutorial.

Tutorial 42 - Dashboard - Create PivotChart 4
04:13

In this tutorial you will copy the worksheet tab of the previous Tutorial and then reorganize the PivotChart to create the fifth PivotChart for the Dashboard. Once complete please save your work as you will build on this in the next tutorial.

Tutorial 43 - Dashboard - Create PivotChart 5
04:20

In this tutorial you will now place your newly created PivotCharts on to the dashboard sheet. Once complete please save your work as you will build on this in the next tutorial.

Tutorial 44 - PivotChart Dashboard - Place PivotTables on Dasboard
02:50

This tutorial is the culmination of the entire training! You at this point are a PivotTable Wizard! In this video you will add slicers to your dashboard as well as learn how to use the Dashboard! This is an incredibly impressive skill and one you can use today at your job! Your productivity potential is exponentially higher than when you began! Congratulations getting this far!

I have included the complete Dashboard as seen in Tutorial 38! This is not required to complete this tutorial. Please use the workbook you have worked on throughout the tutorial 38-44. Feel free to use this to compare and check your work.

Tutorial 45 - Dashboard - Add & Use Slicers
09:52
About the Instructor
Logan Spiers
4.9 Average rating
68 Reviews
1,468 Students
1 Course
Certified Excel Specialist, Professional Development Trainer

I am an Excel Whiz Certified By Microsoft! I have spent the past 10 years working professionally as a consultant specializing in the Microsoft Suite and business process implementation.

I began college in Texas where I played Division III baseball. I developed a passion for business and knew consulting and training were a passion.

A year after finishing my undergraduate studies in Arizona, I landed a career in business consulting. I had the incredible opportunity to work with some of the world's largest companies including a Suncor a Major Oil Refinery, a SalutarisMD startup medical device firm, and even a massive youth baseball organization. I have found a real niche in the use and implementation of Excel as well as all things business.

I have also earned my Masters of Business Administration Degree (MBA) and use that knowledge to help people like you get to where they want to be professionally. I specialize in small business growth, employee training, and technology implementation.

I look forward to sharing my knowledge of technology, and my passion for business management, growth, and marketing with you.