Hello friends, when you use pivot tables, will you encounter situations such as tabs missing and command buttons not working?
Today, my cousin will share with you the pivot table problems that have plagued your workplace friends for many years, to help you improve your pivot table application technology and enhance your workplace competitiveness.
The pivot table field name is invalid
When creating a pivot table, a prompt pops up that the pivot table field name is invalid, what is the reason?
In fact, this is caused by empty cells in the data header row or merged cells.
Just unmerge the cells to fill in the contents of each header cell.
What if the tabs are missing
The pivot table analysis and design tabs are gone, and I'm in a hurry.
Just click on the pivot table with the mouse, stop the active cell in the pivot table, and the pivot table analysis and design tabs will be displayed.
The list of fields is missing
The field list is an important tool for the pivot table to do data analysis dimensions, what should you do if the pivot table field list is missing?
Just click on the pivot table, click on the pivot table analysis tab, and the point field list command will be displayed.
Pivot tables cannot be filtered
What should I do if I want to filter data with an amount greater than 20,000 yuan in a pivot table, and the filter command is grayed out when I click on the data filter?
Just click on any cell on the right side of the pivot table to create a filter.
If you need to sort the data, you can directly click the ascending or descending command in Sort and filter, or you can sort by right-clicking in the data cells, ascending or descending.
Auxiliary column formulas cannot be automatically recalculated
I want to add an auxiliary column on the right side of the pivot table and multiply it by 25% to calculate the gross profit, but when writing the formula, the mouse pointer selects cell e4 and a long list of formulas appears: =getpivotdata("Amount",$d$3,"products","Anti-blue light protection screen"*25%, the formula pull-down calculation result is the same number, check the worksheet and have turned on the automatic recalculation, what is the reason?
In fact, this is ** to enable the function of generating getpivotdata, just click on the pivot table analysis, pull down on the right side of the option to cancel the generation of getpivotdata, and rewrite the formula.
Of course, if you don't want to turn off this option, you can also write the formula in the cell: =E4*25%, and directly reference the cell address formula to get the correct calculation result.
Selected areas cannot be grouped
After creating a pivot table, I want to group date fields to group, but I find that the ribbon group fields command is grayed out and cannot be used.
Through the right-click menu combination, a prompt will pop up that the selected area cannot be grouped, what is the reason?
This is because the dates in the data source are textual dates, and the dates in the data source need to be processed into standard dates by columns, and then the date fields can be combined by refreshing the pivot table twice.
Regarding the skills of processing text-based dates into standard dates, my cousin has shared the processing skills before, you can take a look at the previous articles.
The same pivot table field name already exists
Isn't this kind of pivot table ugly, the cursor enters the formula bar, delete the "sum item:", press enter to appear "the same pivot table field name already exists" prompt.
This is due to the fact that the data source already has the "amount" field, the pivot table does not allow multiple identical fields at the same time, you can use a trick to deceive Excel, just check the "sum item:" press space, and then press enter.
If you need to delete multiple "sum items:" to sum multiple fields, you can press CTRL+H to open the Find and Replace dialog box, enter the sum item :: in the search content, enter a space in Replace as, and replace all points can be processed in batches.
The data source reference is invalid
When inserting a pivot table, a prompt pops up that the data source reference is invalid, what is the reason?
In fact, this is caused by special symbols in the workbook name, for example, there is a pair of brackets in the name of this workbook, just rename it and remove the square brackets.
Well, that's all for today's sharing, if you encounter excel problems in your study and work, you can send me a private message, I wish you happy learning!