Improve Your Productivity: Master Excel Pivot Tables

Become a Microsoft Excel Certified Specialist! The perfect first step towards a Professional Microsoft Certification!
4.3 (57 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,448 students enrolled
$19
$20
5% off
Take This Course
  • Lectures 45
  • Length 2.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 1/2016 English

Course 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!

What are the requirements?

  • Microsoft Excel
  • The want to attain the Microsoft Excel Specialist Certification
  • The need to advance career
  • The need to increase productivity

What am I going to get from this course?

  • 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

What 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

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Overview
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.

Section 2: Workbook Setup
00:58

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

02:18

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

01:12

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

01:18

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

01:50

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

Section 3: Data Analysis & Reporting
05:42

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

03:00

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

01:54

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

02:08

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

See QUICK REFERENCE GUIDE

02:37

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

02:38

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

03:41

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

02:27

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

03:29

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

See QUICK REFERENCE GUIDE

Section 4: Formatting the Workbook
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

Section 5: Worksheet Formatting
01:09

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

See QUICK REFERENCE GUIDE

03:24

This video demonstrates how to merge cells.

See QUICK REFERENCE GUIDE

02:17

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

See QUICK REFERENCE GUIDE

01:44

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

See QUICK REFERENCE GUIDE

02:21

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

See QUICK REFERENCE GUIDE

03:28

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

01:42

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

Section 6: Advanced Workbook Formatting
03:01

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

02:07

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.

Section 7: Advanced Pivot Table Techniques
04:55

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.

03:45

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.

03:44

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.

06:03

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.

04:57

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!

03:22

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.

05:56

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!

03:02

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.

03:45

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.

07:19

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.

05:55

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.

06:53

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.

Section 8: PivotChart Dashboard
03:09

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.

07:32

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.

02:21

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.

03:19

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.

04:13

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.

04:20

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.

02:50

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.

09:52

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.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Logan Spiers, 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.

Ready to start learning?
Take This Course