Hi! We show you're using Internet Explorer 6. Unfortunately, IE6 is an older browser and everything at MindBites may not work for you. We recommend upgrading (for free) to the latest version of Internet Explorer from Microsoft or Firefox from Mozilla.
Click here to read more about IE6 and why it makes sense to upgrade.

Series: Microsoft Excel: Using & Manipulating Pivot Tables

Preview

Buy the Series and be able to watch all of the lessons.

About this Series

  • Lessons: 3
  • Total Time: 1h 10m
  • Use: Watch Online
  • Access Period: 360 Days
  • Created At: 08/15/2011
  • Last Updated At: 08/15/2011

This series of three videos covers the nuts and bolts of Pivot Tables in Microsoft Excel. This series is designed for students and coaches seeking to learn access to compete in the UIL High School Computer Applications contest but has proven to be useful for anyone seeking to learn or brush up on their Excel skills.

In the first video, we focus on pivot tables basics in Excel. In this video, you'll learn:
- how to create a pivot table
- how to use/find/display the field list and pivot table-specific ribbons in Excel
- how to toggle from the current pivot table setup to the classic (Excel 2003 and before) pivot table layout/functionality
- what the parts are to a pivot table are (column headers, row headers, values, and report filters) and how to structure a pivot table
- how to create new fields for you pivot table by grouping; specifically, we'll briefly talk about manually grouping item but then look at how Excel can help you to group dates (to break a date down into multiple fields for year, month, day, etc)
- how to display multiple values (or multiple fields in either the rows or columns section) in a pivot table
- how to manipulate pivot table layouts to adjust how data is displayed
- how to use different provided pivot table layouts (tabular, compact, outline) and how these layouts differ
- how to change heading or field names
- how to use dragging and dropping or overwriting to adjust the order in which column or row values appear

In the second video lesson, we introduce intermediate features and capabilities of pivot tables in Excel. In this video, you'll learn:
- duplicating or copying existing pivot tables
- grouping manually to create new fields within a pivot table
- using subtotals - adding them, determining where they'll show, picking what math function to use for them, etc
- formatting values within pivot tables
- displaying values in alternative way (as a percentage of row totals, as a percentage of column totals, as a percentage of grand totals, etc)
- sorting and filtering fields by values (of that field or other fields) to order data in more useful ways
- utilizing filtering to show top 10 (or any other count) or bottom 10 (or any other count) of items

In the final video lesson, we introduce advanced features and capabilities of pivot tables in Excel. In this video, you'll learn:
- cautions regarding use of percentage field from the raw data in your pivot table
- calculated fields - when you may use them, why you may use them and how to set them up and think about them
- tools for isolating the underlying data that drives any individual value in your pivot table
- using traditional Excel formulas with data that is housed in a pivot table (with cell references)
- using traditional Excel formulas with data that is housed in a pivot table (with GETPIVOTDATA formulae) and the advantages of the GETPIVOTDATE formulas in Excel

To illustrate these things, we'll start with some raw data in Excel that we can build pivot tables off of. Note that a starting point worksheet is attached as is the ending point worksheets (in the attached Excel workbook) for each video in order to allow you to work off of the starting document and compare your results to the ending document.

These videos are screencasted. To watch them in the resolution in which they were recorded (which will offer a crisper screen resolution than the player here), move your mouse over the player and click the 100% (Actual Size) button.

These videos were recorded using Microsoft Excel 2010. There are very minor differences between Excel 2007 and 2010, but the content covered here is virtually the same across versions.

About this Author

Hexco Academic
Hexco Academic
31 lessons
Joined:
02/21/2011

Academic publishing company for 28+ years offering instructional videos, books, software, virtual flashcards, workshops, spelling coaching, and geographic coaching. Most products are geared for competitive academics and home schooling to include materials to study for the Scripps' National Spelling Bee, National Geographic Bee, Texas University Interscholastic League (UIL), North South Foundation, TAPPS, SAT, and more. The 2010 National Spelling Bee champion, Anamika Veeramani was the 11th champion in a row to use our products. Studio Hexco at MindBites is a collection of course material and live presentations by experts in a variety of popular topics. We welcome and appreciate your...

Lessons Included

None of the lesson in this series have been reviewed.

Below are the descriptions for each of the lessons included in the series:

Supplementary Files: