Thinking In Windows

Pivot Tables

What can you do with a Pivot Table?

Steps to Creating a Pivot Table

You create a pivot table using the PivotTable Wizard.  The PivotTable Wizard is an interactive set of dialog boxes that guide you through the steps of choosing the source data and layout you want to use for the pivot table.

NOTE: You can creat a pivot table on any worksheet you choose.  You can also have more than one pivot table on a worksheet.  If you will be changing the pivot table often, you should place it on its own worksheet.

The following instructions utilize Excel 2003 but you'll still need a data file. So, use the "Google" search option mentioned in the following sentences.  Because each version of Microsoft Excel has different locations for the Pivot Table Wizard, do a "Google" search on your Excel version, and append "Pivot Table Example."  Then, work from the tutorial provided.  And if you get stuck, then you can always send me an email!

  1. Start the PivotTable Wizard
    • From the Data menu, choose the PivotTable command.  This displays the PivotTable Wizard.
  2. Specify the Type of Source Data
    • As mentioned above (under "What data can you use?"), your choices are many and varied.
  3. Specify the Location of the Source Data
  4. Create a Pivot Table Layout
    • Spreadsheet fields and items control how data is organzied.
      • A field is a category of data, such as "Year". 
      • An item would be a sub-category in a field, e. g., a "Month" could be a sub-category of "Year".
    • Spreadsheet fields and items also control how data is summarized.
      • If a "data" field that you choose from the source list or table contains
        • numeric data
          • then Microsoft Excel uses the Sum function to calculate the values in the data area of the pivot table
        • text items
          • then Microsoft Excel uses the Count function to provide a count of the source items
  5. Select the Worksheet Destination and Display Options
  6. Customize the Fields, Data, and Layout
    • Once you create a basic pivot table, you can customize it in the following ways:
      • Insert, delete, or rearrange fields and items in the pivot table
      • Change how data is calculated
      • Display or delete subtotals and block totals
      • Change the names of fields and items
      • Change the format
      • Hide and show detail data
      • Group and sort items

Microsoft Excel - Other Resources

Now, there are lots and lots of other things that you can do with macros as there are lots and lots of other things that you can do with Microsoft Excel.  So, some pointers with regard to resources are in order.

It used to be that you received paper documentation manuals with your Microsoft products (and, indeed, paper manuals can still be ordered).  Now, however, the preferred avenue for documentation is to build the documentation into each application with access via the Help menu.  The help file for Microsoft Excel (as for any of the Microsoft Office applications) is a rich resource of primary importance!

With the advent of the World Wide Web, even Microsoft is making use of its advantages.  Thus, one of the choices on the Help pop-up menu is "Microsoft on the Web" another rich resource of World Wide Web information for the Office products.

Specifically for the Microsoft Office products, Woody Leonhard one of the authors of the famous "The Mother of All Windows . . ." books (which open up the internals of the Windows O/S [3.1, 95, 98 {I kept hoping for NT, and he changed the title for the Windows XP O/S to Windows XP for Dummies}] for the novice in a humorous [and therefore powerful] way), and, also, the author of the ". . . Annoyances" books on the MS Office Products done for O'Reilly and Associates, has a couple of periodic newsletters - "Woody's Office Watch" and "Woody's Office for Mere Mortals - which are "goldmines" for information on the Office products and can be subscribed to through his "Woody's Office Watch" link - http://office-watch.com/

He's also got "watch" newsletters on "Windows," "Access," and "Project" as well as those for the "Office" items noted above.

Another rich resource all in all!

Workshop Summary

Well, now that we've completed this Introduction to Object-Oriented Design and Programming by taking a brief look at one of the Microsoft Office products, what are some of the things that we have learned which are important to carry with us in our "kit-bag" of tools as we continue into the next project in our lives?

  • The categorizations (or progression of hierarchies) - "Elements, Collections, Objects, Properties, Methods, and Procedures" - are really a subset of an overall "systems philosophy" called "Systems Dynamics" which is a subset of a broader philosophy called "General Systems Theory".  Really, a way of thinking with which one must become acquainted to continue vibrant work in the field of programming!

  • The technological concepts important to the programming endeavor have been shrouded in an aura of "mystification".  Once one is able to break through the shroud and think for themselves, then the concepts are fairly straight-forward and simple.  You become comfortable breaking down what appear to be complex systems into their relatively simple foundational structures.

  • Graphical User Interfaces are built with object-oriented languages which hold the "categorizations" listed above together.  These languages are called "object- oriented" because they work with "objects".  And, recalling the "picture is worth a thousand words" from the third workshop - at its simplest an object is data (or variables) surrounded by methods and / or properties.

  • Some of the key principles of object-oriented languages are reuseability, reliability, robustness, and extensibility.

  • There's lots of helpful resources in the applications themselves and on the World Wide Web - make use of them!

If you have a question, then click here

Return to main menu