 |
 Vidalia Slice Wizard New As Seen TV 10 Blades $9.99 Time Remaining: 7d 19h 36m Buy It Now for only: $9.99 |
 Vadalia Slice Wizard As Seen on TV $9.99 Time Remaining: 2d 11h 39m Buy It Now for only: $9.99 |
 roll a wizard grates slices and much more vintage $14.99 Time Remaining: 20d 9h 54m Buy It Now for only: $14.99 |
Slice Wizard
3 Timeless Principles to Work Intuitively with Pivot Tables
A Pivot Table allows you to quickly slice and dice information from any large table you can imagine.
For example: you can crunch a table in several ways…
- Category Sales Total sales for each product category.
- Product Sales Total sales for each product, organized by category.
- Q1 & Q2 compares the sales for the first and second quarter.
- Multiple Subtotals calculates additional summaries: the average, largest, and smallest sales for each category.
- Average Sales provides the average sales amount for each product.
- Top 3 Products Identifying the three best-selling products within each category.
- All Quarters Sales for each product and category by quarter.
- And more ways…
Many users focus on the wizard, big mistake. If you are here, you want to learn PT fundamentals not the mechanics.
Now, to create any Pivot Table report intuitively, let's make an outline of main principles
Principle #1: Make a clear distinction of table fields (refer to the above table)
- Category field (Category, Product and Quarter). Imagine categories as the units of data you want information about, for example: the table above has three category fields: Category, Product and Quarter, you may know Sales per Category, Sales per Product and per Quarter, etc. A category field usually contains texts that are repeated across lines. For example, Quarter field contains: Q 1, Q 2, Q 1, Q 1, Q 4, etc
- Numeric field (Sales). Imagine a numeric field as the source of information for categories, for example: the table above have one numeric field called Sales, you may know the total, average, max, etc Sales of any category: Category, Product or Quarter. There are other types of numeric fields like: Profit, Losses, Salaries, etc. This type of field is necessarily a number
Principle #2 Drop the fields to the correct area
You already know the building block concepts to build a Pivot Table report: category and numeric fields. Now you will learn how to configure a PT report.
A PT report is built in front of you as you drop fields appropriately.
This is the most important principle.
Drop to where?
To the Drop fields panel
Let's begin…
Create the Table and summarize it with a Pivot Table
Do it this way…
Excel 2007 users must do the following:
- Place inside the table range and then
- Go to Insert>Tables>Table>Ok or press CTRL + T. The table is created
- Go to Design>Tools>Summarize with Pivot Table
- Choose the destination of your PT in the Create Pivot table wizard: new or existing worksheet and press Ok
Excel 2003 users must do the following:
- Place inside the table range and then
- Go to Data>Pivot Table and PivotChart Report
- Choose Next in the wizard step 1 of 3
- Choose Next or change the range for the data source in the wizard step 2 of 3
- Choose the destination of your PT: new or existing worksheet and press Finish in the wizard step 3 of 3
You now have two main components:
The drop data panel
And the Pivot Table Field List, see below:
Important notice
- If you don't see the PivotTable Field List, make sure that you click the PivotTable. If you still don't see the PivotTable Field List, on the Options tab, in the Show/Hide group, click Field List
- If you don't see the fields in the Field List that you want to use, refresh the PivotTable report to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation.
Drop each field appropriately to form the desired report.
You must proceed this way:
- Drop Category Fields to Column or Row labels areas and
- Drop Numeric Fields to Values area
Each time you drop a field you have the report configuring in front of you.
Finally you have it…
Now let's know each area better…
Row labels
What could be here? Category fields.
You can put here any field included Numeric field but it does not make sense to have as many items as values you have. It is like inverting the table, see illustration below…
- $43.50 Laughing Lumberjack Lager
- $62.14 Konbu
- $63.63 Konbu
- $70.84 Inlagd Sill
- $71.30 Chocolade
- $88.44 Chef Anton's Gumbo Mix
- $90.87 Ravioli Angelo
Principle #3 The order of row labels counts
Order counts so you must place the fields in the order the will be summarized in the report. For example:
Product and Quarter will produce different report layout than Quarter and Product
About the Author
John Franco helps you improve your professional Excel skills.
Get this free Pivot Table Tutorial
'CutUp' Not so bad to bleed, is it?
Swamp witches and master wizards
carried me away from social harmony.
I thank them today for the murky waters
they swam through and found me.
I also am quite glad
that they left me here alone:
without empowerment of freewil,
the druidess turns to stone.
Statues have no feelings.
Feelings bring me life.
Life is just a filament,
thin-sliced. Get a knife.
To dwell in both darkness and light and recognize them as one can be a gift. Embrace those deep emotions and turn them into art…which you do so well through prose and poetry. The tide will change again and again, and each time you will learn something new about yourself
Vidalia Slice Wizard Review