Saturday, April 13, 2013

Using Tables – Formulas and Pivot Table Summaries

Use the Subtotal() formula to get totals for columns

•Now, just above the table you can add a Subtotal formula for the value in the example to the right
•Type or Select Subtotal(function_num,ref1…)
•function_num will be 9 for Sum
•Press “,” or keyboard or select Ref1 box in the formula editor
•Now select the column of the Table that you want to Subtotal (there should be a small down arrow at the top of this field)

Adding a formula in a Table:
•If you have a table with a bunch of Data and want to add a field for Quarter so that we can summarize by quarter the total $.
•Right click the table > Insert > Table Columns to the Left

•Using the Quarter formula you can select the date in the same row and hit Enter on your keyboard
•The formulas will auto fill to the bottom of the table

Adding a Pivot Table:
•Select any 1 cell inside the Table
•Insert Ribbon > Pivot Table button
•You will see that the Range of the Pivot Table is the Name of your Table.  By using your Table, if you add any new Fields or Rows to your Table you can hit •Refresh on the Pivot table without having to change the Range if you used a fixed range.
•You now have a Blank Pivot Table
•Field List (right Picture)


Customizing your Pivot
Choose Fields to add to report – List of fields available to be added to the Pivot table
Report Filter – Filters  to be applied to the total table
Column Labels – Fields that you will see in the columns (generally I use for Quarters or categories
Row Labels – Fields that you will see in the columns (generally I use for Locations or People)
Values – fields that will have some math applied to them (Sum of, Count of, Average of, etc)


Value Field Settings
You can summarize Values in a number of different ways, Sum, Count, Average, Max, Min, etc.
If the Field you placed into Values section of the Pivot table is a number then you can use any option here.  You can place a text type field into Values as well, but will need to use Count
The Show Value As tab has more advanced options.  Some very difficult to understand.  We will look at % of Column Total and % of Row Total as two of the more basic options.


Calculated Fields
Calculated Fields are fields that go into the Values section of the Pivot Table
Calculated Item are fields that can be added to the Filters, Row Labels, Column Labels
Add Calculated Field (Right Bottom).  With Pivot table selected, PivotTable Tools Ribbon > Options Ribbon > Fields, Items & Sets Button


Make your PivotTable Pretty
In the PivotTable Tools Ribbon > Design Ribbon you will see a Layout Section and a Styles Section.  New Color Style (right)
Maybe you like a Tabular Report Layout button (below)


What if you wanted to turn a Pivot Table into a Table but there are a lot of Blanks in the Sub-Region and Quarter columns

If you have Excel 2010 you can do this through PivotTable Tools Ribbon > Design Ribbon > Report Layout Button > Repeat All Item Label Button
To do it otherwise you can copy the PivotTable and paste the value into a new sheet or empty cells
Highlight the Sub-Region and Quarter columns and rows with (don’t just highlight Column A:B)
Press Ctrl+G on your keyboard
Press Alt+S or click the Special Button
Press K or click on the Blanks bullet
Press Enter or click Ok button

You will see that only the blank cells are now selected
Press = sign and the UP arrow on your keyboard
Press Ctrl+Enter on your Keyboard.  This copies the formula to all the blank cells in the range.
No copy/paste special values and you filled in all the blanks successfully

No comments:

Post a Comment