(7) As indicated by the red arrow next to the Generate GetPivotData icon you can click the icon to toggle the GetPivotData behavior on and off. (5) Use your mouse to drag the Generate GetPivotData icon to your command bar. (4) In the Commands pane, select Generate GetPivotData. (2) In the Customize dialog box, select the Commands tab. (1) From the worksheet menu, click Tools > Customize. In Excel versions 2003 or before, as the next picture sequence shows: How you do that depends on your version of Excel. One way is to manually type the formula without using your mouse to select the pivot table cell.Īnother way is to toggle (on or off) the GETPIVOTDATA function default action. There are two ways to avoid Excel forcing the GETPIVOTDATA function upon your formulas. The second formula shows the cell address (F5 in the example) that was “pointed and clicked” to help build the formula involving a pivot table cell.
In the first formula scenario, clicking into a pivot table cell while composing a formula in an outside cell invokes the GETPIVOTDATA function. In the first picture, both examples are shown. When you are doing calculations in cells outside of a pivot table that involve cells inside of (that is, belonging to) a pivot table, sometimes you want just the pivot table’s cell value, and other times you want the relative position and content of the cell with the GETPIVOTDATA function. If you aren’t ready for GETPIVOTDATA or just need it off for a few minutes for some quick formulas, here’s how to keep Excel from creating a GETPIVOTDATA formula for you when you click on a cell in a Pivot Table.Tom’s Tutorials For Excel: Toggling the GETPIVOTDATA Function On and Off One of the big advantages of GETPIVOTDATA is that even if your data’s on another tab, GETPIVOTDATA can pull exactly what you need even if the Pivot Table changes.įinally, watch how to turn off Excel’s feature that automatically creates a GETPIVOTDATA formula for you. Again, once you understand the structure of the GETPIVOTDATA formula, having the Pivot Table on another tab is no big deal. I use GETPIVOTDATA a lot when I have Pivot Tables calculating numbers for a dashboard type report on a different tab in a spreadsheet. The pivot table is used often in financial analysis to facilitate deeper analysis of given data. The function helps to extract data from specified fields in an Excel Pivot Table. Once you understand the structure of the GETPIVOTDATA formula, adding more fields to the Pivot Table is really no big deal. The GETPIVOTDATA Function is categorized under Excel Lookup and Reference functions. We’ll start where we left off in Excel Video 311 by adding even more fields to our Pivot Table.
Excel Video 312 covers a few more things to know about GETPIVOTDATA.
0 kommentar(er)
