Disclaimer: This is a test on how the author will take study notes in the future and testing the lay-out of the website. Please do not use the below information or on this website for any subject matters or for use of examinations. Furthermore, the author is not rendered any service in accounting, taxation or similar professional services.
Pivot Tables: How To Organize, Summarize And Analyze Data
I. How To Create Pivot Tables
Select a single cell within the data, or all of the data, and then select Insert from the Ribbon, and click the Pivot Table icon in the Tables group.
Because Excel assumes an empty row or column means the end of the data range.
- Select the desired Excel sheet name.
- Select a single cell in the data range or select all of the data.
- Click the Insert tab.
- Click the PivotTable icon.
- Specify data range, if necessary. The default is the data range where your cursor is.
- Leave the default of New Worksheet.
- Click OK.
- In the PivotTable Fields List Pane, click and drag the desired field 1 to the Rows section.
- Drag the desired field 2 to the Columns section.
- Drag the desired field 3 to the Filters section.
- Drag the desired field 4 to the Values section.
II. Modified A Pivot Table
- Filtering data.
- Changing the sort order of fields and rows.
- Creating and modifying formulas.
deleted.
Go to the PivotTable Fields List Pane and remove the checkmark from the desired Item, or drag it out of the Rows section if preferred.
- Right-click on the item and select Filter, and then select Hide Selected Items.
- Keyboard short-cut is Ctrl + – (hyphen)
- Right-click and select Filter>Keep Only Selected Items
- Allow users to see what has been filtered.
- Provide users a button to filter PivotTable data.
- Indicate the current state, making it easy to understand what is shown in a filtered PivotTable report.
- Allow users to select fields that are not displayed in the Pivot Table.
- In Excel 2019 and higher, click on Insert Slicer in the PivotTable Analyze ribbon.
- In Excel 2010, click on the Options ribbon and then select Insert Slicers.
- In Excel 2013 and 2016, click the Insert Slicers icon on the Analyze ribbon.
On the far left of the ribbon, users can change the name of the slicer by changing the Slicer Caption.
or
By clicking on Slicer Settings. This Slicer Settings dialog box allows users to change the name of the slicer, and it allows users to control how to sort.
- Click on the desired Sheet.
- Click and select the label desired data in the Pivot Table.
- Click on the Data Ribbon and select the descending Sort icon.
or
- Right-click on the desired data column.
- Select Sort.
- Make preference sort selections.
- Click Sort A to Z.
- Click the Undo icon so that the data is sorted in descending order again.
- Click on the desired Sheet.
- Right-click on the desired cell.
- Select Sort.
- Select More Sort Options.
- Click Manual.
- Click OK.
Click on the desired sheet.
Double-click on the desired cell.
Scroll down and select the desired detail.
Click OK.
- It controls all the information seen in the Pivot Table.
- It also allows users to easily generate multiple pivot reports.
It is on the PivotTable Analyze contextual menu for Excel 2016 and later, and in earlier versions, it is on the Options contextual toolbar.
- Click or chose on the desired or created sheet.
- Click in the Pivot Table.
- Select the contextual Pivot Table toolbar (depending on the excel version).
- Click the drop-down arrow beside Options.
- Select Show Report Filter Pages….
- Select the desired item.
- Click OK.
Users may need to group internal divisions that are being merged together, or perhaps users want to view products by product line instead of by individual products.
- Select the desired cell.
- Hold down on the Control key and then click on the other desired cell or range.
- Right-click and select Group.
Note: To ungroup click on the Ungroup icon on the Group tab or right-click and select Ungroup.
New Versions
- After creating a Pivot Table for the desired data.
- Drag the Related Item to the Columns section and Related Item Amount to the Values section.
- Drag the Related Item Date to the Rows section.
Note: In the latest versions of Excel, the dates are automatically grouped into years. Also in the PivotTable Fields Pane area, users will see that the Rows section has changed and will display Years, and Quarters as well as Related Item Date.
Older Versions (2010 and prior)
- Right-click on the Related Item Ship Date and select Group Field, users can group the dates manually.
- Select Group Selection or Group Field.
- To select both Months and Years, hold down the Control key, and after selecting Months.
Note and Warning: Users must select Years. If the data have more than one year, Excel lumps it all together by month if users do not tell it to group by year first!
- Click OK.
III. Pivot Table Calculation
- Right-click on Sum of Related Item Amount at the designated cell in the Pivot Table.
- Select Summarize Values By.
- Click on Average.
- After the Average is created then format the cell by Right-click on Average of Related Item Amount at the designated cell.
- Select Number Format.
- Select Currency… 0 decimal places.
When users move, add data, or refresh it in the Pivot Table, the formatting they selected will still be applied versus when Ribbon formatting is used the cells applied will revert to the default formatting.
Subtotal
- Click on the contextual Design toolbar and click on the Subtotal icon.
- Select Show all Subtotals at Bottom of Group.
Total
- Click on the contextual Design toolbar and click on the Grand Totals icon.
- Select Show all Grand Totals at Bottom of Group.
- Right-click on Sum of Related Item Amount or one of any related amounts in the Pivot Table.
- Select Show Values As.
- Select % of Grand Total.
New Version
- Click on the contextual ribbon named PivotTable Analyze.
- Click on the drop-down arrow beside the Fields, Items & Sets icon.
- Click on Calculated Field. A dialog box will display.
- Type the name of the new field or any desired in the Name: section
- In the Formula: section, type or select Last Year’s desired value and click Insert Field.
- Type *any% in the Formula section.
- Click Add.
- Click OK.
2013 or prior versions
- Click on the contextual ribbon named Options. Click on the drop-down arrow beside the Fields, Items & Sets icon.
- Click on Calculated Field. A dialog box will display.
- Type the name of the new field or any desired in the Name: section
- In the Formula: section, type or select Last Year’s desired value and click Insert Field.
- Type *any% in the Formula section.
- Click Add.
- Click OK.
- Click on a cell in the desired or designated Column.
- Select the drop-down arrow under Fields, Items & Settings on the contextual ribbon.
- Select Calculated Item…
- In the Name: section type over Formula1 and replace it with any desired name.
- In the Formula: section, select the 0 to overwrite it and then click on the desired name just created lock in the Items: section.
- Click Insert Item.
- Type + .
- Click on the next desired item created (Desired name ATB Lock) and then click Insert Item.
- Continue until all desired items are selected.
- Click Ok.
- Click the drop-down arrow on the Fields, Items & Settings icon.
- Select List Formulas.
Note: Excel will display all calculated fields and items on a new sheet in the workbook.
Because sometimes it moves the data around a bit, and sometimes the formatting makes it more difficult to edit.
- Display the PivotTable Style gallery.
- Choose Clear from the from the bottom of the dialog box.
- The PivotTable will display in the default format.
- Click anywhere within the PivotTable to select it.
- Click the contextual Design Ribbon.
- Click the More button on the PivotTable Styles group to see the gallery of available styles.
- Click the style of anyone’s preference.
- Click anywhere within the PivotTable to select it.
- Click the contextual Design Ribbon.
- On the PivotTable Style Options group, do one of the following:
- To turn the header rows on or off, select or clear the Row Headers or Columns Headers the check box.
- To display odd and even rows with different formatting, select the Banded Rows check box.
- To display odd and even Columns with different formatting, select the Banded Columns check box.
- Click the Report Layout button to change the report layout to compact, outline or tabular.
- To insert or remove a blank row after each item, click the Blank Row button.
Note: If users do not see a contextual toolbar then the cursor is not in the Pivot Table.
IV. Pivot Table Charts
The data within the excel must be summarized well.
Scatter, bubble, and stock charts.
Click in your data and then select the Insert menu.
- Double-click on the Pivot Chart icon or click the drop-down arrow under the icon select PivotChart.
- Double-check the data range and location and click OK.
- Click and drag the desired data to the Value section and desired Item Name to the Axis category.
New Version
- Click in the Pivot Table itself.
- Click on the PivotTable Analyze contextual toolbar and select PivotChart or go to the Insert menu and select PivotChart from there.
Older versions (2013 or prior)
- Select a single cell in your data on the Data sheet.
- Click on the Insert Ribbon and click the drop-down arrow under Pivot Table.
- Select PivotChart.
- Specify the location of the PivotChart and click OK. A blank pivot chart is created and the PivotTable Field appears.
- Move the fields to the desired location in the chart.
V. Options
It is saved with the Pivot Table in a pivot cache.
- Click on a cell in a Pivot Table.
- On the contextual ribbon, click the Change Data Source drop-down.
- Click Change Data Source and the following dialog box will display.
Users may select the REFRESH button located on the contextual ribbon.
- Click in the data.
- Click Insert tab.
- Click Table.
- Verify the data range and click OK.
- Click on the source data.
- Select the Formulas tab and click on Define Name.
- In the New Name dialog box, name your data.
- The scope should stay at the default level of the Workbook.
- In the Refers to: section type the following:
- =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)) Click OK.
Note: Make sure there is no other data below the dynamic range. If there is, this will not work.
GET PIVOT DATA.
When a field is hidden for use, the user reference in GETPIVOTDATA, the function stops working.
When fields are rearranged in the table, the function can be altered.