Excel Pivot Table Tutorial and Excel Pivot Tables Creation via VBA.

All Excel Templates Super Special $189.00! New Templates Added!

Build a Stock Trading Model Using Microsoft Excel | Neural Network Trading Software

File Conversion Software | Advanced Excel Timesheet . NEW FEATURES ADDED!

Newsletter Index

What's New at OzGrid

As we enter 2005 we hope that all our readers had a wonderful Christmas and New Year. Unfortunately the dreadful devastation of the tsunami's that affected so many countries and lives marred the festive season. Our hearts go out to all those who have been affected by this terrible natural disaster.

Unfortunately for millions of people around the World the year 2004 will always be known as the worst year ever. The tragic events of Dec 26 were so bad that words can never describe them. While I'm sure most reading have donated some money to the massive task at hand, please also consider a donation via our Excel forum when/if you are given assistance. A link has been placed top center of all pages onour forum .

Our book is still selling well, it has just been printed and released in Polish, Spanish and Japanese. We should receive some more stats in about mid-February, so we will keep you informed.

Excel Tips and Tricks

To kick off the year 2005 I thought we would run a series on Pivot Tables in the newsletter. In my opinion, Pivot Tables, are one of (if not the) most powerful and useful feature of Excel.

However, before even considering a Pivot Table it is vital than one understands that Excel will expect your data to be laid out in the correct manner.

Possibly one of the most important factors when considering using a Pivot Table is to have our data set up in what could be termed as a table and/or list. As our Pivot Table will be basing all its data on this list or table it is vital that we set our tables and lists up in a uniform way.

In the context we are discussing here, a Table is no more than a List with more than one Column of data. A List is often referred to in the context of a Table as well. The 'good practice' that applies to setting up a List will aid us greatly when we need to apply a Pivot Table to our Data. When we extract data via the use of Lookup functions or Database functions we can be a little less stringent in how we set up our Table or List. This is because we can always compensate with the aid of a function and probably still get our result. However, having said this we should always set up our List or Table as well as possible. When we use Excels built-in features they will and do assume a lot about the layout and setting up of your data. They do have a degree of flexibility but more often than not you will find it easier to follow the guidelines for setting up your Table or List.

Let's look at what I believe to be the most important aspects of setting up a Table or List.

1) Headings. This is a must! They should always be in the row directly above your data. Never have a blank row between your data and the headings. Make them distinct in some way eg; Bold them.

2) Leave at least three blank rows above your headings. These can be used for formulas, critical data etc. You can hide the Rows if you wish.

3) If you do have more than one List or Table on the same Worksheet leave at least one blank column between your List and Tables. This will aid Excel in recognizing them as separate.

5) If your data includes date and/or times ensure they are all valid dates and times. More onExcel Dates and Times

6) Base your Pivot Table off aDynamic Named Range This will not only keep the size of the housing Workbook down and allow your Pivot Table to easily include newly added data, but it will also make 'grouping' of data (another issue) much easier.

7) Sort your List or Data. This in not so much for Pivot Tables, but for the person reading the data.

If we follow these guidelines as close as possible, using Pivot Tables will be a relatively easy task.

Why are they called Pivot Tables ? - Well, basically they allow us to pivot our data via drag-and-drop to produce meaningful information. This makes Pivot Tables interactive in that once the table is complete we can very easily see what effect moving (or pivoting) our data has on our information. This will become patently clear later on (another issue), once we have produced a Pivot Table. Believe me, no matter how experienced you get at Pivot Tables there will always be an element of trial-and-error involved in producing the desired results! What this means is you will find yourself pivoting your table a lot!

What would we use them for ? - We would use a Pivot Table to produce meaningful information from a table of perhaps, non-meaningful information. A Pivot Table could be used on a table to create a table that could tell us much statistical information about all the data contained within it. Imagine you have a table of data that contains names, addresses, ages, occupations, phone numbers, postcodes etc. With a Pivot Table we could very easily and quickly find out:

What is the advantage ? - Perhaps the biggest advantage to using Pivot Tables is the fact that we can generate and extract meaningful information from a large table of information within a matter of minutes, seconds once set-up. Or perhaps it is because they will not use up a lot of memory from your PC.Dynamic Named Ranges are ideal for this.

Pivot Charts - In Excel 2000 Microsoft introduced Pivot Charts. These are simply charts that are read from the table created via the Pivot Table Wizard. In fact Pivot Tables are really no longer just Pivot Tables, they are now PivotTables and PivotChart Reports. These two features go hand-in-hand with each other. By this I mean, when you create a Pivot Table you can also create a Pivot Chart at the same time with no extra effort needed on our part. Pivot Charts allow us to create professional complex charts that were previously not possible without either VBA or using Excel's Controls.

Pivot Table and Pivot Chart wizard can be found via Data>PivotTable and PivotChart Report... on the Worksheet Menu Bar. Whenever we create a Pivot Table and Chart we use what is known as the Pivot Table and Pivot Chart wizard. This guides us through the creation of our Pivot Table using a three step process.

There are many side-steps we can take along the way to manipulate our Pivot Table, but most users may find it easier to do this after step 3. There is however a slight difference with Excel 97 and 2000 onwards in that there are 4 steps in Excel 97. Step 3 in Excel 97 is where you can set-up your data, this can still be done in Excel 2000 onwards, but you have a choice of doing this after the wizard has completed.

Next month we will go through the paces of creating one. In the interim, you cango here and read (most is the same as here) as well as follow some links to free tutorials on Pivot Tables.

Excel VBA Tips and Tricks

Let's keep with the Pivot Table theme of this newsletter and allow me to show you some code that can make the creation of them, based on a list, very easy.

It will allow us to select the heading of any list and base a Pivot Table off it. As it's a single column list we place it into both the Row Field and Data Field of the Pivot Table we will get a COUNT if the list is Text and a SUM if the list is numeric.

Sub MakeTable()
Dim Pt As PivotTable
Dim strField As String

    'Pass heading to a String variable
    strField = Selection.Cells(1, 1).Text

   'Name the list range
    Range(Selection, Selection.End(xlDown)).Name = "Items"

    'Create the Pivot Table based off our named list range.
    'TableDestination:="" will force it onto a new sheet
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
        SourceData:="=Items").CreatePivotTable TableDestination:="", _
            TableName:="ItemList"

    'Set a Pivot Table variable to our new Pivot Table
    Set Pt = ActiveSheet.PivotTables("ItemList")

    'Place the Pivot Table to Start from A3 on the new sheet
    ActiveSheet.PivotTableWizard TableDestination:=Cells(3, 1)

    'Move the list heading to the Row Field
    Pt.AddFields RowFields:=strField
    'Move the list heading to the Data Field
    Pt.PivotFields(strField).Orientation = xlDataField
End Sub

When creating code for Pivot Tables the Macro Recorder is your best friend. The code above was initially created via the Macro Recorder and then modified to suit. To run the code you successfully you must be selected in the heading of any list. Download a Working Copy Here

If you have a list which has a lot of blank cells (to represent repeated data) you can download and use the open source Fill Blanks Excel Add-in .

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.
Contact Us

OzGrid Excel and VBA Newsletter

Newsletter Index

What's New at OzGrid

Excel Tips and Tricks

Excel VBA Tips and Tricks