{"id":129476,"date":"2025-09-03T21:25:22","date_gmt":"2025-09-03T21:25:22","guid":{"rendered":"https:\/\/goodwriterz.com\/site\/?p=129476"},"modified":"2025-09-03T21:25:22","modified_gmt":"2025-09-03T21:25:22","slug":"how-to-use-the-getpivotdata-function-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/goodwriterz.com\/site\/how-to-use-the-getpivotdata-function-in-microsoft-excel\/","title":{"rendered":"How to Use the GETPIVOTDATA Function in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_68_1 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >\u062c\u062f\u0648\u0644 \u0627\u0644\u0645\u062d\u062a\u0648\u064a\u0627\u062a<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 eztoc-toggle-hide-by-default' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-getpivotdata-function-in-microsoft-excel\/#The_GETPIVOTDATA_Syntax\" title=\"The GETPIVOTDATA Syntax\">The GETPIVOTDATA Syntax<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-getpivotdata-function-in-microsoft-excel\/#Using_GETPIVOTDATA_to_Retrieve_Grand_Totals\" title=\"Using GETPIVOTDATA to Retrieve Grand Totals\">Using GETPIVOTDATA to Retrieve Grand Totals<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-getpivotdata-function-in-microsoft-excel\/#Retrieve_the_Grand_Total_of_All_Data_in_a_PivotTable\" title=\"Retrieve the Grand Total of All Data in a PivotTable\">Retrieve the Grand Total of All Data in a PivotTable<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-getpivotdata-function-in-microsoft-excel\/#Retrieve_the_Total_of_a_PivotTable_Column_or_Row\" title=\"Retrieve the Total of a PivotTable Column or Row\">Retrieve the Total of a PivotTable Column or Row<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-getpivotdata-function-in-microsoft-excel\/#Using_GETPIVOTDATA_to_Retrieve_a_Specific_Data_Point\" title=\"Using GETPIVOTDATA to Retrieve a Specific Data Point\">Using GETPIVOTDATA to Retrieve a Specific Data Point<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-getpivotdata-function-in-microsoft-excel\/#Using_GETPIVOTDATA_to_Retrieve_a_Figure_Based_on_a_Date\" title=\"Using GETPIVOTDATA to Retrieve a Figure Based on a Date\">Using GETPIVOTDATA to Retrieve a Figure Based on a Date<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-getpivotdata-function-in-microsoft-excel\/#Retrieve_a_Grand_Total_From_a_Specified_Year\" title=\"Retrieve a Grand Total From a Specified Year\">Retrieve a Grand Total From a Specified Year<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-getpivotdata-function-in-microsoft-excel\/#Retrieve_a_Grand_Total_From_a_Specified_Month_of_a_Specified_Year\" title=\"Retrieve a Grand Total From a Specified Month of a Specified Year\">Retrieve a Grand Total From a Specified Month of a Specified Year<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-getpivotdata-function-in-microsoft-excel\/#Retrieve_a_Specific_Data_Point_Based_on_a_Date\" title=\"Retrieve a Specific Data Point Based on a Date\">Retrieve a Specific Data Point Based on a Date<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-getpivotdata-function-in-microsoft-excel\/#Points_to_Note_When_Using_GETPIVOTDATA\" title=\"Points to Note When Using GETPIVOTDATA\">Points to Note When Using GETPIVOTDATA<\/a><\/li><\/ul><\/nav><\/div>\n<div>\n<p>Most people know that you can reference one or more cells, tables and their column headers, or named ranges in Excel formulas. However, fewer know that you can reference specific data points in PivotTables using the GETPIVOTDATA function. Here&#8217;s how.<\/p>\n<p>    <!-- No AdsNinja v10 Client! --><!-- No AdsNinja v10 Client! --><\/p>\n<h2 id=\"the-getpivotdata-syntax\">\n                        The GETPIVOTDATA Syntax<br \/>\n               <\/h2>\n<p>Whenever you use the GETPIVOTDATA function, you must input atyou least two arguments. Whether you input more than two depends on what you want to retrieve. Here&#8217;s the syntax:<\/p>\n<pre>=GETPIVOTDATA(<em>a<\/em>,<em>b<\/em>,<em>c\u00b9<\/em>,<em>c<\/em>\u00b2...)<\/pre>\n<p>where<\/p>\n<ul>\n<li>\n                                        <em>a <\/em>(required) is the name of the PivotTable field containing the data you want to retrieve,\n                        <\/li>\n<li>\n                                        <em>b<\/em> (required) is a reference to a single cell, multiple cells, or a named range in the PivotTable, and\n                        <\/li>\n<li>\n                                        <em>c\u00b9<\/em> and <em>c<\/em>\u00b2 (optional) are the first of up to 126 pairs of field names (<em>x<\/em><em>\u00b9<\/em>) and item names (<em>x<\/em><em>\u00b2<\/em>) defining the data you want to retrieve.\n                        <\/li>\n<\/ul>\n<section class=\"emaki-custom-block emaki-custom-note\" data-nosnippet=\"\">\n<div class=\"emaki-custom note\" id=\"custom_block_6\">\n<div class=\"custom_block-content note\">\n<p>Argument <em>a<\/em> must always be wrapped in double quotation marks. The same can be said for field names (<em>x\u00b9<\/em>) and item names (<em>x<\/em>\u00b2), unless they&#8217;re dates, numbers, or cell references.<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<\/section>\n<h2 id=\"using-getpivotdata-to-retrieve-grand-totals\">\n                        Using GETPIVOTDATA to Retrieve Grand Totals<br \/>\n               <\/h2>\n<p>Because the GETPIVOTDATA function extracts data based on field names and item names, it&#8217;s better at dealing with structural changes to a PivotTable than direct cell references. This is why it&#8217;s a great way to retrieve grand totals for, say, a report.<\/p>\n<p>In the examples used in this section, the PivotTable&#8217;s rows are countries, the columns are products, and the values are sales.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :78.8%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/a-pivottable-in-excel-with-countries-as-rows-products-as-columns-and-sales-as-values.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"A PivotTable in Excel, with countries as rows, products as columns, and sales as values.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> To pull grand totals from a PivotTable, you first need to make sure they&#8217;re displayed. To do this, select any cell in the PivotTable, and in the Design tab on the ribbon, expand the &#8220;Grand Totals&#8221; drop-down menu. Then, click &#8220;On For Rows And Columns.&#8221;<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :45.57463672391%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/the-contextual-pivottable-design-tab-on-the-excel-ribbon-is-opened-and-on-for-rows-and-columns-is-selected-in-the-grand-totals-drop-down-menu.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"The contextual PivotTable Design tab on the Excel ribbon is opened, and On For Rows And Columns is selected in the Grand Totals drop-down menu.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934711_106_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934711_106_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> Now, the PivotTable shows grand totals for each row and column, as well as a grand total of everything combined in the bottom-right cell.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :28.044280442804%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/grand-totals-are-displayed-for-each-row-and-column-in-an-excel-pivottable.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"Grand totals are displayed for each row and column in an Excel PivotTable\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934711_650_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934711_650_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<h3 id=\"retrieve-the-grand-total-of-all-data-in-a-pivottable\">\n            Retrieve the Grand Total of All Data in a PivotTable<br \/>\n    <\/h3>\n<p>The most basic example of a GETPIVOTDATA formula in Microsoft Excel is when the function is used to retrieve a grand total of all data in a PivotTable. In fact, the only thing you need to type is the equal (=) sign!<\/p>\n<p>To extract a grand total from a PivotTable into another cell in your workbook, select the cell where you want the retrieved grand total to be displayed, and type the equal sign:<\/p>\n<pre>=<\/pre>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :13.466666666667%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/grand-total-is-typed-into-cell-a1-in-an-excel-spreadsheet-and-an-equal-sign-is-typed-into-cell-b1-with-the-cell-still-in-edit-mode.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"Grand total is typed into cell A1 in an Excel spreadsheet, and an equal sign is typed into cell B1, with the cell still in edit mode.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934712_624_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934712_624_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> Then, click the cell in the PivotTable that displays the grand total, and notice that the formula is inserted automatically:<\/p>\n<pre>=GETPIVOTDATA(\"Sales\",Totals!$A$3)<\/pre>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :13.466666666667%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/a-getpivotdata-formula-typed-into-cell-b2-in-excel-with-the-sales-as-the-first-argument-and-cell-a3-from-the-totals-sheet-as-the-second.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"A GETPIVOTDATA formula typed into cell B2 in Excel, with the Sales as the first argument, and cell A3 from the Totals sheet as the second.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934712_460_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934712_460_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> In this example, <em>&#8220;Sales&#8221;<\/em> is the field containing the data to be retrieved, and <em>Totals!$A$3<\/em> is an absolute reference to the top-left cell in the PivotTable in the Totals worksheet.<\/p>\n<p>When you press Enter, the grand total is successfully extracted.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :18.533333333333%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/a-grand-total-from-a-pivottable-in-excel-is-retrieved-using-the-getpivotdata-function.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"A grand total from a PivotTable in Excel is retrieved using the GETPIVOTDATA function.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934712_788_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934712_788_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<section class=\"emaki-custom-block emaki-custom-note\" data-nosnippet=\"\">\n<div class=\"emaki-custom note\" id=\"custom_block_27\">\n<div class=\"custom_block-content note\">\n<p>The GETPIVOTDATA function only retrieves values, not formatting, so you need to apply cell, text, and number formatting manually.<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<\/section>\n<h3 id=\"retrieve-the-total-of-a-pivottable-column-or-row\">\n            Retrieve the Total of a PivotTable Column or Row<br \/>\n    <\/h3>\n<p>You can follow the same principle to retrieve a column or row&#8217;s grand total from a PivotTable in Excel\u2014simply type the equal sign, click the cell containing the grand total at the bottom of a given column or the end of a given row, and press Enter.<\/p>\n<p>Here, the sales total for the product named <em>amarilla <\/em>(in column B of the PivotTable in the sheet named <em>Sales<\/em>) is retrieved:<\/p>\n<pre>=GETPIVOTDATA(\"Sales\",Totals!$A$3,\"Product\",\"Amarilla\")<br\/><\/pre>\n<p>where <em>&#8220;Sales&#8221;<\/em> is the value you want to retrieve, <em>Totals!$A$3<\/em> references a cell in the relevant PivotTable, and the <em>&#8220;Product&#8221;,&#8221;Amarilla&#8221;<\/em> pairing tells Excel to find the field named <em>Product <\/em>and then locate the item named <em>amarilla<\/em> within that field.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :18.533333333333%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/excel-s-getpivotdata-function-is-used-to-extract-the-total-sales-value-for-the-product-named-amarilla.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"Excel&#039;s GETPIVOTDATA function is used to extract the total sales value for the product named amarilla.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934712_128_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934712_128_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> However, at the moment, the formula isn&#8217;t dynamic. In other words, if you wanted to retrieve the grand total for another product, you would need to edit the existing GETPIVOTDATA formula or generate a new one.<\/p>\n<p>Instead, rather than hard-coding the product name in the final argument, you could reference a cell containing the product name:<\/p>\n<pre>=GETPIVOTDATA(\"Sales\",Totals!$A$3,\"Product\",<strong>A1<\/strong>)<\/pre>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :18.533333333333%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/excel-s-getpivotdata-function-is-used-to-extract-the-total-sales-value-for-the-product-typed-in-cell-a1.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"Excel&#039;s GETPIVOTDATA function is used to extract the total sales value for the product typed in cell A1.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934712_696_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934712_696_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<section class=\"emaki-custom-block emaki-custom-note\" data-nosnippet=\"\">\n<div class=\"emaki-custom note\" id=\"custom_block_39\">\n<div class=\"custom_block-content note\">\n<p>Cell references in formulas don&#8217;t need to be enclosed in double quotation marks.<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<\/section>\n<p>Now, when you type a different product name in cell A1, the total updates accordingly.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :18.533333333333%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/microsoft-excel-s-getpivotdata-function-is-used-to-extract-the-total-sales-value-for-the-product-typed-in-cell-a1.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"Microsoft Excel&#039;s GETPIVOTDATA function is used to extract the total sales value for the product typed in cell A1.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934713_696_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934713_696_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<section class=\"emaki-custom-block emaki-custom-tip\" data-nosnippet=\"\">\n<div class=\"emaki-custom tip\" id=\"custom_block_42\">\n<div class=\"custom_block-content tip\">\n<p>Go one step further and create a drop-down list of products in cell A1 using Excel&#8217;s data validation tool. This saves you from having to type the products manually.<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<\/section>\n<p>Follow the same process to return the grand total at the end of a row in the PivotTable. The following formula returns the sales total in Canada:<\/p>\n<pre>=GETPIVOTDATA(\"Sales\",Totals!$A$3,\"Country\",\"Canada\")<\/pre>\n<p>where <em>&#8220;Sales&#8221;<\/em> is the value to retrieve, <em>Totals!$A$3<\/em> tells Excel where the PivotTable is located, and the<em> &#8220;Country&#8221;,&#8221;Canada&#8221;<\/em> pairing tells Excel to find the field named <em>country <\/em>and then locate the item named <em>Canada<\/em> within that field.<\/p>\n<h3 id=\"\">\n            <img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"Excel&#039;s GETPIVOTDATA function is used to extract the total sales value for Canada.\" data-image-id=\"803291\" height=\"139\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934713_993_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?resize=750%2C139&#038;ssl=1\" width=\"750\" title=\"\"><br \/>\n    <\/h3>\n<p>Then, to make the formula dynamic, replace &#8220;Canada&#8221; with a reference to a cell containing the country&#8217;s name:<\/p>\n<pre>=GETPIVOTDATA(\"Sales\",Totals!$A$3,\"Country\",<strong>A1<\/strong>)<\/pre>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :18.533333333333%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/excel-s-getpivotdata-function-is-used-to-extract-the-total-sales-value-for-canada-which-is-typed-into-cell-a1.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"Excel&#039;s GETPIVOTDATA function is used to extract the total sales value for Canada, which is typed into cell A1.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934713_206_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934713_206_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<h2 id=\"using-getpivotdata-to-retrieve-a-specific-data-point\">\n                        Using GETPIVOTDATA to Retrieve a Specific Data Point<br \/>\n               <\/h2>\n<p>As well as retrieving grand totals, Excel&#8217;s GETPIVOTDATA function can also retrieve a specific data point at the intersection of a specified column and row.<\/p>\n<p>Suppose you want to see the total sales for the product named <em>montana<\/em> in Mexico.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :13.466666666667%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/an-excel-spreadsheet-with-a-product-in-cell-a2-a-country-in-cell-b2-cell-c2-where-a-total-will-be-generated-highlighted.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"An Excel spreadsheet, with a product in cell A2, a country in cell B2, cell C2 where a total will be generated highlighted.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934713_886_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934713_886_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> As always with the GETPIVOTDATA function, the best approach is to let Excel generate the formula for you, and then you can make tweaks where necessary. So, after typing the equal sign into the cell where you want the resultant value to be located, click the relevant cell in the PivotTable. In this case, it&#8217;s cell D8, the intersection of the Montana column and the Mexico row.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :30.592503022975%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/cell-d8-in-an-excel-pivottable-is-selected-to-add-it-to-a-formula.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"Cell D8 in an Excel PivotTable is selected to add it to a formula.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934713_224_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934713_224_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> As a result, Excel generates this formula:<\/p>\n<pre>=GETPIVOTDATA(\"Sales\",Totals!$A$3,\"Country\",\"Mexico\",\"Product\",\"Montana\")<br\/><\/pre>\n<p>and when you press Enter, the montana sales total for Mexico is correctly returned.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :16.889428918591%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/the-getpivotdata-function-in-excel-is-used-to-return-the-montana-sales-total-for-mexico.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"The GETPIVOTDATA function in Excel is used to return the montana sales total for Mexico.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934714_374_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934714_374_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> Now, you can substitute the hard-coded values in the formula for cell references. Specifically, rather than referencing &#8220;Mexico&#8221; for the country, you can reference cell B2, and rather than referencing &#8220;Montana&#8221; for the product, you can reference cell A2:<\/p>\n<pre>=GETPIVOTDATA(\"Sales\",Totals!$A$3,\"Country\",<strong>B2<\/strong>,\"Product\",<strong>A2<\/strong>) <\/pre>\n<p>\u200b\u200b\u200b\u200b\u200b\u200b<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :18.533333333333%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/the-mexico-montana-sales-total-is-extracted-from-a-pivottable-using-a-dynamic-getpivotdata-formula-in-excel.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"The Mexico montana sales total is extracted from a PivotTable using a dynamic GETPIVOTDATA formula in Excel.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934714_451_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934714_451_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> As a result, when you change the product and country in cells A2 and B2, respectively, GETPIVOTDATA returns the corresponding value.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :18.533333333333%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/the-france-vtt-sales-total-is-extracted-from-a-pivottable-using-a-dynamic-getpivotdata-formula-in-excel.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"The France VTT sales total is extracted from a PivotTable using a dynamic GETPIVOTDATA formula in Excel.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934714_345_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934714_345_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<h2 id=\"using-getpivotdata-to-retrieve-a-figure-based-on-a-date\">\n                        Using GETPIVOTDATA to Retrieve a Figure Based on a Date<br \/>\n               <\/h2>\n<p>One way to see date-specific information in a PivotTable is to insert a timeline slicer. However, where timeline slicers let you <em>filter <\/em>a PivotTable based on a certain time period, the GETPIVOTDATA function lets you <em>extract <\/em>information from a PivotTable and takes up less space.<\/p>\n<p>The key to using GETPIVOTDATA with dates is to structure the PivotTable correctly. In the examples used in this section, the PivotTable&#8217;s rows are dates, the columns are products, and the values are sales. Also, grand totals for rows and columns are displayed.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :80.529801324503%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/a-pivottable-in-microsoft-excel-with-dates-as-rows-products-as-columns-and-sales-as-values.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"A PivotTable in Microsoft Excel, with dates as rows, products as columns, and sales as values.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934714_227_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934714_227_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> To make the data clearer, I right-clicked one of the dates in column A, clicked &#8220;Group,&#8221; selected &#8220;Months&#8221; and &#8220;Years,&#8221; and clicked &#8220;OK.&#8221;<\/p>\n<p>As a result, the dates are split into 2013 and 2014, with each month given its own row.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :64.854111405836%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/a-pivottable-in-microsoft-excel-with-dates-as-rows-products-as-columns-and-sales-as-values-1.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"A PivotTable in a Microsoft Excel sheet, with dates as rows, products as columns, and sales as values.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934715_536_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934715_536_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> You can see this change in the PivotTables Fields pane, where Years (Date) and Months (Date) are displayed as separate Rows fields.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :69.2%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/the-pivottable-fields-pane-in-excel-with-years-and-months-displayed-as-separate-row-fields.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"The PivotTable Fields pane in Excel, with Years and Months displayed as separate row fields.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934715_828_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934715_828_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<h3 id=\"retrieve-a-grand-total-from-a-specified-year\">\n            Retrieve a Grand Total From a Specified Year<br \/>\n    <\/h3>\n<p>Now that the PivotTable is properly structured by year and month, you can use the GETPIVOTDATA function to extract a grand total for a given year.<\/p>\n<p>Imagine you want to retrieve the total for 2013. To do this, type the equal symbol, and click the cell in the PivotTable that contains this value. Straightaway, Excel generates the formula for you:<\/p>\n<pre>=GETPIVOTDATA(\"Sales\",Sales!$A$3,\"Years (Date)\",2013)<\/pre>\n<p>where <em>&#8220;Sales&#8221;<\/em> is the field containing the data to be retrieved, and <em>Sales!$A$3<\/em> is an absolute reference to the top-left cell in the PivotTable in the Sales worksheet, <em>&#8220;Years (Date)&#8221;<\/em> is the field name, and <em>2013<\/em> is the item name within that field.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :11.910377358491%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/a-getpivotdata-formula-typed-into-cell-b2-in-excel-with-the-sales-total-for-2013-as-the-value-to-be-extracted.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"A GETPIVOTDATA formula typed into cell B2 in Excel, with the sales total for 2013 as the value to be extracted.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934715_824_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934715_824_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> Now, make the formula dynamic. In other words, replace the hard-coded year with a cell reference. In this case, the year is in cell A2:<\/p>\n<pre>=GETPIVOTDATA(\"Sales\",Sales!$A$3,\"Years (Date)\",<strong>A2<\/strong>)<\/pre>\n<p>When you press Enter, the total for 2013 is displayed in cell B2.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :18.533333333333%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/the-year-for-the-getpivotdata-function-in-excel-is-generated-by-a-reference-to-cell-a2.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"The year for the GETPIVOTDATA function in Excel is generated by a reference to cell A2.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934715_499_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934715_499_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> Because the formula is dynamic, if you change the year to 2014, the figure updates accordingly.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :18.533333333333%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/the-year-for-the-getpivotdata-function-in-microsoft-excel-is-generated-by-a-reference-to-cell-a2.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"The year for the GETPIVOTDATA function in Microsoft Excel is generated by a reference to cell A2.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934715_443_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934715_443_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<h3 id=\"retrieve-a-grand-total-from-a-specified-month-of-a-specified-year\">\n            Retrieve a Grand Total From a Specified Month of a Specified Year<br \/>\n    <\/h3>\n<p>As well as retrieving a yearly grand total from a PivotTable in Excel, you can extract a total for a specific month of a specified year.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :13.466666666667%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/an-excel-spreadsheet-with-a-month-in-cell-a2-a-year-in-cell-b2-and-c2-empty-where-the-total-sales-will-be-calculated-selected.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"An Excel spreadsheet, with a month in cell A2, a year in cell B2, and C2 (empty), where the total sales will be calculated, selected.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934716_448_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934716_448_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> As always, start by typing the equal symbol, and select the relevant cell from the PivotTable. So, for example, to return the grand total for January 2014, you&#8217;ll need to select cell H11.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :38.359788359788%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/cell-h11-in-an-excel-pivottable-is-selected-to-add-it-to-a-formula.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"Cell H11 in an Excel PivotTable is selected to add it to a formula.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934716_783_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934716_783_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> Here&#8217;s the formula that Excel generates automatically:<\/p>\n<pre>=GETPIVOTDATA(\"Sales\",Sales!$A$3,\"Months (Date)\",1,\"Years (Date)\",2014)<\/pre>\n<p>Notice how the month is stated as a number (1 for January). As a result, when you make this formula dynamic, you need to make sure that whatever you input results in a number between 1 (January) and 12 (December).<\/p>\n<p>This is where the MONTH function comes into play. Here&#8217;s the dynamic formula:<\/p>\n<pre>=GETPIVOTDATA(\"Sales\",Sales!$A$3,\"Months (Date)\",<strong>MONTH(A2&amp;1)<\/strong>,\"Years (Date)\",<strong>B2<\/strong>)<\/pre>\n<p>where<\/p>\n<ul>\n<li>\n                                        <em>&#8220;Months (Date)&#8221;<\/em> is the first row field name,\n                        <\/li>\n<li>\n                                        The item name in this first field is represented by <em>MONTH(A2&amp;1)<\/em>, which takes the three-letter month in cell A2 and turns it into a numerical value between 1 and 12,\n                        <\/li>\n<li>\n                                        <em>&#8220;Years (Date)&#8221;<\/em> is the second row field name, and\n                        <\/li>\n<li>\n                                        <em>B2<\/em> tells Excel that the item name in this second field is determined by the value in cell B2, which, in this case, is 2014.\n                        <\/li>\n<\/ul>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :15.885714285714%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/a-getpivotdata-formula-in-excel-that-turns-a-textual-month-into-a-numerical-value-and-references-cell-b2-to-ascertain-the-year.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"A GETPIVOTDATA formula in Excel that turns a textual month into a numerical value and references cell B2 to ascertain the year.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934716_365_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934716_365_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<h3 id=\"retrieve-a-specific-data-point-based-on-a-date\">\n            Retrieve a Specific Data Point Based on a Date<br \/>\n    <\/h3>\n<p>Let&#8217;s combine all the tips explained so far in this guide to return a specific sales total for a given product in a given month of a given year.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :13.466666666667%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/a-microsoft-excel-spreadsheet-with-a-product-in-cell-a2-a-month-in-cell-b2-a-year-in-cell-c2-and-d2-empty-where-the-total-sales-will-be-calculated-selected.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"A Microsoft Excel spreadsheet, with a product in cell A2, a month in cell B2, a year in cell C2, and D2 (empty), where the total sales will be calculated, selected.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934716_949_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934716_949_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> First, type the equal symbol, and click an individual data point within the PivotTable to generate the formula you&#8217;re going to work with.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :40.666666666667%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/a-cell-in-a-pivottable-is-selected-to-create-a-reference-to-it-in-a-formula.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"A cell in a PivotTable is selected to create a reference to it in a formula.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934717_820_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934717_820_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p>Here&#8217;s the resultant formula:<\/p>\n<pre>=GETPIVOTDATA(\"Sales\",Sales!$A$3,\"Product\",\"Velo\",\"Months (Date)\",3,\"Years (Date)\",2014)<\/pre>\n<p>Now, adapt the formula to make it dynamic. Change &#8220;Velo&#8221; to <strong>A2<\/strong> (the cell containing the product name), &#8220;2014&#8221; to <strong>C2<\/strong> (the cell containing the year), and 3 to <strong>MONTH(B2&amp;1)<\/strong>, which converts the month in cell B2 to a number:<\/p>\n<pre>=GETPIVOTDATA(\"Sales\",Sales!$A$3,\"Product\",<strong>A2<\/strong>,\"Months (Date)\",<strong>MONTH(B2&amp;1)<\/strong>,\"Years (Date)\",<strong>C2<\/strong>)<\/pre>\n<p>Then, press Enter to see the result.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :14.329896907216%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/a-getpivotdata-formula-in-excel-that-returns-a-total-based-on-a-selected-product-month-and-year.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"A GETPIVOTDATA formula in Excel that returns a total based on a selected product, month, and year.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934717_142_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934717_142_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<p> Finally, test that the formula works as expected by changing the details in cells A2 to C2 and cross-referencing the result with the PivotTable.<\/p>\n<h2 id=\"points-to-note-when-using-getpivotdata\">\n                        Points to Note When Using GETPIVOTDATA<br \/>\n               <\/h2>\n<p>Before you go ahead and use this handy function in your Excel workbooks, take note of these final pointers:<\/p>\n<ul>\n<li>\n                                        Typing <strong>=<\/strong> and clicking a cell in a PivotTable only triggers a GETPIVOTDATA formula if the tool is enabled. To check this, select the PivotTable, open the &#8220;PivotTable Analyze&#8221; tab, and in the Options drop-down menu beneath the PivotTable&#8217;s name, see whether the Generate GetPivotData option has a check mark next to it. If it doesn&#8217;t, click it once.\n                        <\/li>\n<\/ul>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\" :37.080103359173%\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2025\/09\/generate-getpivotdata-in-excel-s-pivottable-analyze-tab-is-checked.png\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"Generate GetPivotData in Excel&#039;s PivotTable Analyze tab is checked.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934717_187_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934717_187_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?w=1200&#038;ssl=1\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<ul>\n<li>\n                                        If the second argument of a GETPIVOTDATA formula references a cell that is not a PivotTable, you&#8217;ll see a #REF! error. The same goes for any arguments that do not match a field name in the PivotTable.\n                        <\/li>\n<li>\n                                        Remember to use double quotes in the GETPIVOTDATA arguments, unless they contain dates, numbers, or cell references.\n                        <\/li>\n<li>\n                                        If you use a whole date as a GETPIVOTDATA argument, rather than just a month or year, you&#8217;ll need to use the DATE function to turn it from text to a recognized date format.\n                        <\/li>\n<\/ul>\n<hr\/>\n<p>GETPIVOTDATA is just one of the many lookup functions in Microsoft Excel, but its uniqueness in how it works with PivotTables sets it apart from the rest. If you&#8217;re working with data in a regular range or formatted as an Excel table, you might consider using XLOOKUP, INDEX with XMATCH, or FILTER instead.<\/p>\n<div class=\"display-card tag type-generic small \" data-show-streamrentbuy-links=\"false\" data-include-community-rating=\"false\" id=\"a7af-4a61-85e373a2d094\">\n<div class=\"display-card-main-content-wrapper\">\n<div class=\"display-card-main-content\">\n<div class=\"display-card-content image-column small\">\n<p>                        <!--  Image --><\/p>\n<div class=\"w-img \" data-stnl-group-once=\"IdctS7a4W1\">\n<div class=\"body-img square \">\n<div class=\"responsive-img  img-article-square\" data-img-url=\"https:\/\/static0.howtogeekimages.com\/wordpress\/wp-content\/uploads\/2023\/06\/41lldfnvdxl-_sl500_.jpg\" data-modal-id=\"single-image-modal\" data-modal-container-id=\"single-image-modal-container\" data-img-caption=\"&quot;&quot;\">\n<picture><source media=\"(max-width: 480px)\" data- \/><source media=\"(max-width: 767px)\" data- \/><source media=\"(max-width: 1023px)\" data- \/><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.jpg\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.jpg?w=1200&#038;ssl=1\" alt=\"\" title=\"\">\n                        <\/picture>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<div class=\"display-card-content info-column\">\n<p>                    <!--  Display card main info widget --><\/p>\n<div class=\"w-display-card-info main-info\">\n<div class=\"w-display-card-info\">\n<dl>\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<!-- div --><\/p>\n<p><dt>\n\t\t\t\t\t\t<strong>OS<\/strong> \t\t\t\t\t<\/dt>\n<dd>\n\t\t\t\t\t\t\t\t<span><\/p>\n<p>\t\t\t\t\t\t\t\t\t\t\tWindows, MacOS, iPhone, iPad, Android<\/p>\n<p>\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t<\/dd>\n<\/p>\n<p>\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<!-- div --><\/p>\n<p><dt>\n\t\t\t\t\t\t<strong>Brand<\/strong> \t\t\t\t\t<\/dt>\n<dd>\n\t\t\t\t\t\t\t\t<span><\/p>\n<p>\t\t\t\t\t\t\t\t\t\t\tMicrosoft<\/p>\n<p>\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t<\/dd>\n<\/p>\n<\/dl><\/div>\n<\/p><\/div>\n<p>                    <!--  Display card main info widget end -->\n                <\/div>\n<\/p><\/div>\n<p>            <!-- Default Description or Custom one added by creator ( override )--><\/p>\n<p class=\"display-card-description default\">Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.<\/p>\n<p>            <!-- Extra content section, display hidden by default --><br \/>\n            <input type=\"checkbox\" id=\"extra-content-a7af-4a61-85e373a2d094\" name=\"extra-content-a7af-4a61-85e373a2d094\" class=\"expand-extra-info-checkbox css-menu-checkbox\"\/> <!-- Checkbox: control the expandable behavior  of the extra info --><\/p>\n<p>            <!-- PROS and CONS section --><\/p>\n<p>            <!-- Affiliate links section --><\/p>\n<p>            <!-- Display card footer --><\/p><\/div>\n<\/p><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Most people know that you can reference one or more cells, tables and their column headers, or named ranges in Excel formulas. However, fewer know that you can reference specific data points in PivotTables using the GETPIVOTDATA function. Here&#8217;s how. The GETPIVOTDATA Syntax Whenever you use the GETPIVOTDATA function, you must input atyou least two &#8230; <a title=\"How to Use the GETPIVOTDATA Function in Microsoft Excel\" class=\"read-more\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-getpivotdata-function-in-microsoft-excel\/\" aria-label=\"Read more about How to Use the GETPIVOTDATA Function in Microsoft Excel\">\u0625\u0642\u0631\u0623 \u0627\u0644\u0645\u0632\u064a\u062f<\/a><\/p>\n","protected":false},"author":1,"featured_media":129477,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[24],"tags":[39,313,288],"class_list":["post-129476","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-microsoft","tag-microsoft","tag-microsoft-365","tag-microsoft-excel"],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/09\/1756934718_How-to-Use-the-GETPIVOTDATA-Function-in-Microsoft-Excel.png?fit=1600%2C900&ssl=1","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/posts\/129476","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/comments?post=129476"}],"version-history":[{"count":0,"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/posts\/129476\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/media\/129477"}],"wp:attachment":[{"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/media?parent=129476"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/categories?post=129476"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/tags?post=129476"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}