## What is a Pivot Table?

I have had a couple of people ask me what a pivot table is so I made a short video that walks you through the basics.
One key bit of information that I left out of the video is that if your underlying data changes, all you have to do is right-click the pivot table and select Refresh.
This will make the pivot table update and display the newest information.

Click on the dog with the movie popcorn to watch the video.

## GETPIVOTDATA Function

I am in the process of updating my Pivot Table course and decided that I take another look at  the GetPivotData function.
GET PIVOT DATA is an Excel function that pulls data from your pivot table. I personally don’t use it  because if you hide a field you referenced in GETPIVOTDATA, the function stops working. Sometimes, just rearranging the fields in the table can impact the function as well. However, I did want you to be aware of it.
If you try to multiply a total in a pivot table, you will immediately see that Excel automatically references the GETPIVOTDATA function.  This is a problem because if you copy the formula,  you will get the same answer for the entire column or row.
In the example below, I opened Pivot_Rearrange.xlsx and removed the Product Name field from the Pivot Table.  Next I  clicked on cell C5, as I wanted to multiply B5 by 1.10 to estimate next year’s order amount by carrier.

Instead of showing =B5 it shows =GETPIVOTDATA(“Order Amount”,\$A\$3,”Ship Via”, “FedEx”)*1.10

Notice that A3 is treated as an absolute cell reference. If you copy the formula down,  you will get the same answer for all of the Ship Via carriers.  As a work around, I know many people who copy and paste the pivot table into another workbook if they want to do any math on the pivot table.  However, I found a great tip on the MrExcel.com website on how to avoid this problem.

1. Go up the Options icon and click the drop-down arrow to the right of it.
2. Uncheck Generate GetPivotData.

Now, if you go back to cell C5 and recreate the formula, you will see  the formula bar now shows =B5*1.10. It will now copy down correctly. (Excel will not correct the GETPIVOTDATA formula so you need to delete it and then type in =B5*1.1.)

The best part is that once you uncheck it, Excel remembers so you do not have to keep unchecking the Generate GetPivotData icon every time you want to use a formula that references the pivot table.
This is an excerpt from my new Pivot Table Course that will be out the first week of June 2016.

# Source Data – Pivot Table

When you close an Excel file, containing a pivot table, Excel can save the source data in the file or clear it from memory. If you go to Options>Pivot Table Options you will see that Excel by default saves the source data with the file.

The advantage of this, is that the file will open a little quicker and you do not need to refresh the pivot table. The disadvantage is that if you copy the sheet containing the pivot table and email that sheet to someone then they have access to all the source data! Another disadvantage is that your file is larger.If you uncheck the Save source data with file, your file will be smaller, but will take a little longer to open as Excel needs to rebuild the pivot cache of data. If you do uncheck the box, it is recommended that you check the Refresh data when opening the file.   Microsoft keeps moving this option icon around.  if you can’t find the Option icon on the ribbon, try right-clicking in the pivot table itself.  Check out our Excel CPE course on Pivot Tables.

• “Thank you for a high quality CPE resource. As a CPA in a small nonprofit, I find your library quite helpful to meet my needs. I appreciate the service.”-

- Cheryl

• ” This was my first experience with CPASelfStudy.com and I was very impressed. I will definitely consider taking CPE through CPASelfStudy.com in the future.”   12/2017

- Christopher Q.

• “I am extremely impressed with the courses and the reasonable price.” -Janet A. May 2017

- Janet