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
•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