By popular demand, we’ve decided to put together a set of topics that coaches & competitors in the Texas High School UIL Computer Applications contest should know in order to be competitive at state and regional levels.  This list of 10 topics will focus more on advanced formulas and applications of the programs than it will on basics (e.g. basic math formulas in Excel, letter formats and other Word templates, etc).  We covered the first item a while back in our blog post on IF statements…and conditional IF statements, SUMIF, COUNTIF, etc.  The second blog post introduced the second item: Using Times and Dates in Excel.   Our third post was on Select Queries and Query Basics in Access.  The fourth item will keep us in Excel but turn our attention to pivot tables.

Pivot tables in Excel are similar to Crosstab queries in Access in many ways.  They are definitely one of the most powerful features of Microsoft Excel.  They allow you to cut, filter, manipulate and display summarized information based off of a stipulated data set.

We created a set of three videos on pivot tables that will start you off with the basics and then work you up to more and more advanced features of pivot tables.

The first video (previewable below) covers creating pivot table, using/finding/displaying/understanding the field list and pivot table-specific ribbons in Excel, toggling from the current pivot table setup to the classic (Excel 2003 and before) pivot table layout/functionality, parts and structure of pivot tables (column headers, row headers, values, and report filters), creating new fields for you pivot table by grouping; specifically (specifically with dates), displaying multiple values (or multiple fields in either the rows or columns section) in a pivot table, manipulating pivot table layouts to adjust how data is displayed, using and understanding differences between various provided pivot table layouts (tabular, compact, outline), changing heading or field names, dragging and dropping or overwriting to adjust the order in which column or row values appear, etc:

The intermediate video digs a big more deeply on pivot tables. Among other things, it covers 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, and utilizing filtering to show top 10 (or any other count) or bottom 10 (or any other count) of items. You can preview it here:

The advanced video goes even deeper to touch on truly advanced pivot table applications and topics. Among other things it cautions regarding use any percentage field from your raw data in your pivot table (and explains the problems therein), introduces you to and covers calculated fields (when you may use them, why you may use them, how to set them up and think about them), shows you how to isolate the underlying data that drives any individual value in your pivot table, shows you how to use traditional Excel formulas with data that is housed in a pivot table (with cell references and with GETPIVOTDATA formulae). You can preview it below:

All three of these videos are also available in a discounted bundle through our comprehensive three-video series on pivot tables in Excel.