This situation began to appear after Excel 2002. Very intoxicating.
Let me analyze why this is so. In the following example, we use a PivotTable report to summarize the daily output data by year and month. In normal times, we can use the formula (2019-2018)/2018 to calculate the growth rate. But we can't use the data in the PivotTable as usual. Therefore, when we use the mouse to select the corresponding cell, enter the corresponding mathematical symbol and press Enter, the system will automatically get the correct 1 monthly growth rate of 3.3 1%. Unfortunately, when you drag the formula down, you get the same growth rate every month.
When we further observe the reason, we will find the formula =(GETPIVOTDATA ("output ",$F$ 1," date ",1," year ",2019)-getpivotdata ("output ",$ f $650. 20 1 8))/getpivotdata ("output", $F$ 1, "date",1,"year", 20 18) But obviously, this is not the formula you entered. At that time, you simply established this formula with the mouse.
The same thing happens when you use the keyboard to move left and right to select the desired cell.
Friends who like to think should ask: what is GETPIVOTDATA, and why does this run into my Excel? I guess, it may be that Microsoft people want you to find GETPIVOTDATA, and then get to know it and learn how to use it. Finally, you will love it slowly. Unfortunately, this will not happen. Usually, we feel bored.
Of course, if you want to know how to use GETPIVOTDATA, please leave a message below. When there are many people, I will open a new article to explain its usage.
Here are some simple ways to achieve our goal without knowing more about GETPIVOTDATA.
Method 1. This is the quickest and most convenient way: enter the formula you want directly, but don't use the mouse or keyboard to move the arrow to select the cell you want. But direct input =(H3-G3)/G3? In this way, we have established a formula that can be copied.
Method 2: Turn off this option of GETPIVOTDATA completely. The operation method is: select file/option/formula/delete the reference of the PivotTable report obtained by using the GetPivotData function.
Free Excel operation skills:
There is a very common problem about PivotTable, that is, the total number of rows in PivotTable will change constantly. When we enter the following formula, we get the proportion of the current month. It can be displayed normally every month when there is data.
However, when a month's data is missing or a new month is added, this data will be wrong.
The specific reason is that when the position of the total number of rows in the PivotTable is changed from line 15 to line 14, your formula is still divided by I 15.
The solution is to use Excel skills to let Excel get the last number in the first column. ..
The formula is shown in the figure below. The principle is to use the fuzzy search principle of vlookup to return a maximum value. We can set the custom cell format according to Figure 2 below. Leave the third area blank so that we can hide all numbers with a value of 0.