Microsoft Excel: Data Analysis with Excel Pivot Tables

In this lecture I'll introduce you to the IMDb Movie Database, which we'll be working with through the first several sections of the course.

In this lecture, I'll outline some key assumptions and expectations to keep in mind before diving in.

Learn how PivotTables are used, and why they are one of the most powerful and versatile tools for analyzing and exploring raw data in Excel.

A Pivot Table is only as strong as the data behind it. Let's take a few minutes to talk about some key DO's and DON'Ts of preparing raw data for analysis using Excel Pivot Tables.

In this lecture we'll explore a few different ways to easily insert PivotTables in Excel.

When you analyze data with a PivotTable, think of yourself as a pilot and the Field List as your cockpit. In this lecture, we'll learn how to use the field list to slice, dice, and filter our data in Excel with ease.

In this lecture, we'll review some of the tools available in the Excel PivotTable "Analyze" and "Design" tabs, including slicers and timelines, calculated fields, PivotCharts and table styles.

Learn how to clear, select, move and copy Excel PivotTables using options from the "Analyze" tab.

Learn how easy it is to refresh and update PivotTables as your source data changes, and understand the difference between changes made within or outside of your Excel source data range.

Learn how to use Excel tables or column-only PivotTable source references to deal with data that consistently grows over time.

In this lecture I'll explain the purpose of Excel's PivotTable cache, describe how it can be used to reduce file sizes by storing data in memory, and demonstrate how to revive source data from cache using the "show details" option.

For those looking to dig a bit deeper, this lecture demonstrates how PivotTable values are actually calculated and displayed based on your table layout.

In this lecture, we'll discuss a few different ways to customize number formats in Excel PivotTables (dates, currency, percentages, etc.)

Learn how to adjust your PivotTable settings to apply a default value to blank cells.

There are several options when it comes to Excel PivotTable report layouts, and a number of ways to customize the look and feel. In this lecture, we'll review these options and discuss the pros and cons of each.

PivotTables can be an incredibly useful tool for creating brand new tables from existing source data in Excel, whether you want to aggregate your data at a different level of granularity, eliminate certain fields, or add new calculated metrics. This lecture demonstrates how this can be done using specific table layouts and design tools.

In this lecture, we'll learn how to change and customize labels and headers in Excel pivots.

In this lecture, we'll demonstrate how to use Excel's conditional formatting tools (like color scales and icon sets) to visualize patterns in your data and draw attention to notable trends within your PivotTable.

In this lecture I'll show you how to hide text to prevent it from overlapping with data bars, using a custom Excel formatting rule.

In this lecture I'll show you how to change the scope of your conditional formatting rules to customize how they react to changes in your Excel PivotTable layout.

In this lecture, we'll review basic Excel PivotTable sorting options, including manual, alphabetical and value-based sorting.

In this lecture, we'll explore cases where sorting may give unexpected results due to Excel's deafult "custom lists".

Label filters allow you to include or exclude items using text-based criteria (i.e. begins with, ends with, contains, does not contain, etc). This lecture demonstrates several ways to use these label filters in your Excel PivotTable views.

Wildcards allow you to create more complex and flexible label filters within Excel PivotTables. In this lecture we'll practice using two variations of these wilcards: the asterisk (*) and question mark (?). 

Value filters allow you to include or exclude items using numerical or value-based criteria (i.e. greater than, less than, equal to, etc). This lecture demonstrates several ways to use these value filters in your Excel PivotTable views.

Learn how to adjust Excel PivotTable settings to allow you to apply multiple filters (label + value) to the same field. 

Grouping options allow you to combine or aggregate data however you choose. In this lecture we'll walk through some of Excel's most common automatic and manual grouping techniques.

In this demonstration we'll show how daily data can be automatically grouped in Excel to summarize data by month, quarter, year, etc.

In this lecture, we'll practice using Excel slicers and timelines to add visual filtering tools to a PivotTable. 

If you need to generate multiple copies of a PivotTable view with different filter settings (i.e. a view of product sales for each region or country), the "Report Filter Pages" option is a lifesaver. In this lecture, we'll see how this tool can be used to instantly break out multiple views into their own Excel worksheets.

In this lecture, we'll explore different ways to summarize values within Excel PivotTables, including Sum, Count, Average, Max, Min and more.

In this demo, we'll talk about the difference between "Sum Of" and "Count of", and why Excel PivotTables sometimes default to counting values instead of summing them.

One of the most powerful PivotTable features is the ability to display values in multiple ways. This lecture introduces some of Excel's most common and powerful options, including % of Column, % of Parent, Difference From, Running Total, and more.

In this lecture, we'll practice using "% of Column" and "% of Row" value calculations in our Excel PivotTable.

In this lecture, we'll practice using "% of Parent" value calculations in our Excel PivotTable.

In this lecture, we'll practice using "Difference From" and "% Difference From" value calculations in our Excel PivotTable.

In this lecture, we'll practice using "Running Total" and "% Running Total" value calculations in our Excel PivotTable.

In this lecture, we'll practice using "Rank" value calculations in our Excel PivotTable.

In this demo, we'll transform PivotTable values into index numbers and explain how the approach can be used as a powerful  analytical tool in Excel.

Calculated fields are one of the most powerful PivotTable tools. In this lecture we'll explore some common ways to create new values and calculations in Excel based on existing PivotTable fields.

In this demo, we'll see why you should always create calculated "rate" metrics in your Excel PivotTable, as opposed to your raw data range.

In this lecture we'll demonstrate how adding a simple "Counting Column" in your raw Excel data can enable powerful PivotTable calculations and analysis tools.

In this lecture we'll explore a second example of using a "Counting Column" in the raw data to enable more complicated calculated fields in Excel.

Calculated items are essentially the text equivalent of calculated fields. In this lecture we'll explore why (and why NOT) to use calculated items in your Excel PivotTables.

In this lecture, we'll demonstrate how to customize the solve order for fields that are part of multiple calculations within an Excel PivotTable.

In this lecture, we'll introduce some of the pros and cons of using Excel Pivot Charts for data analysis.

In this lecture, we'll practice creating an Excel Pivot Chart in the form of a Clustered Column chart.

In this lecture, we'll practice creating an Excel Pivot Chart in the form of a Pie or Donut chart.

In this lecture, we'll practice creating an Excel Pivot Chart in the form of a Clustered Bar chart.

In this lecture, I'll explain how to prevent Excel charts from resizing and distorting when the underlying rows or columns are changed.

Changing Excel Pivot Chart types is incredibly easy. In this lecture, we'll practice modifying our charts using the Pivot Chart Design tools.

In this lecture, we'll practice creating an Excel Pivot Chart in the form of a Stacked Area chart.

In this lecture we'll practice customizes the look and feel of Excel Pivot Charts using layout and style options.

In this lecture I'll demonstrate the pros and cons of moving an Excel Pivot Chart to its own separate worksheet.

In this lecture, we'll practice inserting slicers and timelines and applying them to multiple Pivot Tables and Pivot Charts in Excel.

In this demo, we'll practice building a dynamic Excel dashboard using Pivot Charts, slicers and timelines.

In this lecture, I'll introduce the Case Study section of the course and lay out some expectations before we dive in.

In this Excel PivotTable case study we'll explore U.S. Census Bureau data from the 2012 presidential election, including population and registered voter counts broken down by state and age group.

In this Excel PivotTable case study we'll review salary information for government employees in San Francisco, including employee names and titles as well as base and overtime pay from 2011-2013.

In this Excel PivotTable case study we'll explore recorded shark attack records from 1900-2016. Dimensions include the date and location of the attack, victim demographics and activity, shark species, and whether or not the attack was provoked.

In this Excel PivotTable case study we'll explore a 3-month sample of US stock market data, including Open, High, Low and Close prices as well as trading volume for 500 individual stocks.

In this Excel PivotTable case study we'll take a look at Major League Baseball team statistics from 1995-2015, including games played, wins, losses and post-season results, along with hitting and pitching statistics (R, HR, RBI, ERA, etc).

In this Excel PivotTable case study we'll explore customer-submitted burrito ratings from a number of Mexican restaurants in San Diego. Data includes the burrito and restaurant name as well as 0-5 ratings based on tortilla quality, temperature, meat volume, uniformity, synergy, and more.

In this Excel PivotTable case study we'll analyze daily weather conditions for Jan-Dec 2016, including max, min, and mean temperature, wind speed, total precipitation and weather conditions.

In this Excel PivotTable case study we'll analyze actual post-level Facebook data from Spartan Race. Data includes the date and time of the post, post type and copy, and number of engagements (likes, comments, shares, etc).

In this Excel PivotTable case study we'll explore ratings, prices, and common attributes of 7,000+ mobile apps available in the Apple App Store.

In this Excel PivotTable case study we'll analyze information from 130,000+ wine tastings, including average point ratings, prices, and written reviews.