 |
|
|
|
|
See Also: Print Each Page Field | Refresh Pivot Table via Excel Macros || Hide/Show Pivot Table Field Items || Excel Subtotals || Making the SUBTOTAL Function Dynamic || Bold Excel Subtotals Automatically || Sum Every Nth Cell || Count of Each Item in a List || Grouping Pivot Tables Problems || Microsoft's Analysis Services - o2olap for Excel | Microsoft SQL/Analysis Services - o2olap for Excel
I won't try and hide the fact from you that I am a big fan of Pivot Tables. I use them a lot in the development of Spreadsheets for my clients. Once the client sees the Pivot Table, they nearly always ask "could I do that?" the answer of course is yes! Unfortunately most people tend to shy away from Pivot Tables, as they see them as too complex. to be honest with you, when you first use a Pivot Table the whole thing can seem a bit daunting. Don't be put off by this as persistence will almost certainly pay off. I find the best part about Pivot Tables is their ability to be manipulated via 'Trial-and-Error' and immediately show the result. If its' not what you expect, simply use the Undo feature and/or have another go! What you must always remember is that you are not changing the structure of your original table in anyway at all, so you can do no harm!
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 once you give Pivot Tables a go. 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 information. 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:
- How many people have the same names.
- How many postcodes are the same.
- A count of a particular occupation.
- See only people that match a particular occupation.
- Find out the addresses of people that match a postcode
In fact the list can go on and on!
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. Or perhaps it is because they will not use up a lot of memory from your PC. In a lot of cases we could get the same results from a table of data by using Excels built in functions, but this would take more time and use far more memory. On top of this, if we wanted some new information we can simply drag-and-drop (pivot). We can also opt to have our information update each time we open the Workbook and/or by clicking refresh.
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 your part. Pivot Charts allow us to create professional interactive charts that were previously not possible without either VBA or using Excel's Controls.
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 a heading) with more than one Column of data and a different heading for each column. 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.
If we follow these guidelines as close as possible, using Pivot Tables will be a relatively easy task.
The screen shot above shows both a well laid out table of data and the Layout step of the Pivot Table Wizard. Note that many of the same dates are repeated in the "Date" Column. In front of this data is the Layout step for the data showing the optional Page, Row and Column fields and also the mandatory Data field.
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 four step process. In a nutshell these steps are:
Once we have done this three step process we end up with a empty Pivot Table ready to accept our data.
There are also 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 4 There is however a slight difference with Excel 97 and 2000 + 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 +, but you also have a choice of doing this after the wizard has completed.
Free Excel Pivot Table Tutorial From Microsoft | Excel 2007 Demo: Analyze product sales with a PivotTable report | Pivot Table reports 101 | 25 easy Pivot Table Reports | About Using Microsoft Query to Retrieve External data | Create a PivotTable report | Tips for Working With Pivot Tables | Excel 2007 Document: Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables These pages and all content is the sole property of Microsoft. Excel PivotTable Site Pages With Search | Excel PivotTable Gear (Demo Download)
Excel Dashboard Reports & Excel Dashboard Charts 50% Off
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft � and Microsoft Excel � are registered
trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
GIVE YOURSELF
OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS