If you have to display text in the value field of the Excel PivotTable, put this technique aside

2022-05-18 0 By

As we all know, forget it, cross it out, because my lower limit is constantly being refreshed without a bottom line.Whether you know it or not, a PivotTable value field can only display values, not text, and even if the source data is text, the PivotTable will count it, either way, as values.Someone asked: Can’t I just display text in the PivotTable value field?If you can only fight with weapons that do not weigh your hand, of course it is absolutely necessary.Case: Figure 1 shows the mid-year and end-of-year assessment forms of the employees of the company. Please use a PivotTable to display them as a TWO-DIMENSIONAL table, as shown in Figure 2 below.Solution: 1. Select any cell of the data table –> Insert from the menu bar –> PivotTable 2.In the dialog box that pops up, select “Existing worksheet” and where you want to create it –> click “OK” 3.In the PivotTable Fields area on the right, drag the fields as follows: Row: Department, Name column: Time Period Value: Assessment Level The PivotTable counts the value fields and does not retain the text values.4. Select any cell in the PivotTable -> right click -> select value Field Settings from the pop-up menu to find all “calculation types”, there is no such option as “Save text values”.That’s okay. Here’s how to do it today.5. Set column F as the Secondary column of Level, set A, B, and C to 1, 2, and 3 respectively, and leave D blank.6. Select any cell in the PivotTable -> Analysis on the menu bar -> Change Data Source 7.Add the new auxiliary column E to the area -> click “OK” now there is a “level” in the field to the right.Replace “Assessment Level” in the value field with “Level” 9. Select any cell in the PivotTable -> right click -> Select “Number Format” from the menu that pops up 10.In the dialog box that pops up, select “Custom” -> Enter the following type -> click “OK” : [=1]!A;[= 2]!B;!C11. Select any cell of the pivot table – > select menu bar “design” – > “total” – > “for rows and columns to disable the” 12.Choose “Design” –> “Category Summary” –> “Do not Show Category Summary” 13.Choose “Design” > “Report Layout” > “Display in Table” 14.15. Select any cell in the PivotTable -> right-click -> choose PivotTable Options from the menu that pops up. 16.In the dialog box that pops up, select the layout and Format TAB –> check “For empty cells, display” –> Enter “D” –> click “OK” 17.Center the H and I columns.Many students will think that Excel single case explanation is fragmented, beginners may not be able to fully understand and master.Many students hope to have a complete set of graphic teaching, starting from the most basic concepts, step by step from simple to complex, from the entry to master, systematically explain the various knowledge points of Excel.The following column, starting from the most basic operations and concepts, uses vivid and interesting cases to guide you to master Excel operation skills, shortcut keys, function formulas, pivottables, charts, printing skills and so on…You can become an Excel master by learning everything.