{"id":4646,"date":"2023-02-21T16:27:17","date_gmt":"2023-02-21T16:27:17","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=4646"},"modified":"2024-03-19T14:59:07","modified_gmt":"2024-03-19T14:59:07","slug":"how-to-display-text-in-pivot-table-values-area","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-display-text-in-pivot-table-values-area\/","title":{"rendered":"How to Display Text in Pivot Table Values Area?"},"content":{"rendered":"

Display Text in Pivot Table Values Area to elevate the clarity and effectiveness of your data analysis. By incorporating textual data alongside numerical insights, you create a more comprehensive and accessible report that speaks to a wider audience. Whether it’s summarizing data categories, highlighting key findings, or indicating data quality, using text transforms your pivot tables into more informative, actionable tools. Master the skill of displaying text in the pivot table values area to unlock new dimensions in your Excel reports and dashboards.<\/p>\n

This Content Covers:<\/strong><\/p>\n

    \n
  1. How to Display Text in Pivot Table Values Area using Data Model?<\/strong><\/a><\/li>\n
  2. How to Display Text in Pivot Table Values Area using Conditional Formatting?<\/strong><\/a><\/li>\n<\/ol>\n

    1. How to Display Text in Pivot Table Values Area using Data Model?<\/strong><\/h2>\n

    Suppose we have this data table and we want to show the students\u2019 names in the values area. Normally if we put the students category in the values area, it will give us the count of students. Now we will see how we can display the students\u2019 names as text, instead of count inside Pivot table values area using the data model.<\/p>\n

    \"Display<\/p>\n

    Step 1: <\/strong>Select the data-table and go to Insert>>Pivot Table.<\/strong><\/p>\n

    \"Display<\/p>\n

    Step 2: <\/strong>This Pivot Table dialogue box will appear in your screen. Choose New Worksheet<\/strong> and check the box (Add this data to the Data Model)<\/strong>. Click Ok.<\/p>\n

    \"Display<\/p>\n

    Step 3: <\/strong>A new worksheet will open in your workbook. From PivotTable Fields<\/strong>, right on the table name and choose Add Measure.<\/strong><\/p>\n

    \"Display<\/p>\n

    Step 4: <\/strong>The Measure <\/strong>dialogue box will pop up. Type a name inside Measure Name<\/strong> box and insert the following formula inside Formula<\/strong> box. From the Category<\/strong> list, keep it to General. <\/strong>Click on Check Dax Formula<\/strong> to see if there are any errors in the formula or not. If the formula is fine then press OK.<\/strong><\/p>\n

    =CONCATENATEX(Values(Table1[Students]), Table1[Students], “, “)<\/strong><\/p>\n

    \"Display<\/p>\n

    Step 5: <\/strong>Inside the PivotTable Fields there is now a new option added (Student Names)<\/strong> that we just created from Measure<\/strong> box. Drag Student Names<\/strong> inside Values<\/strong> field and drag Class <\/strong>and Result<\/strong> inside Rows<\/strong> field.<\/p>\n

    \"Display<\/p>\n

    Step 6: <\/strong>The pivot table is created and it looks like this, where the texts are inside Values <\/strong>area. To give it a better look we will remove the grand total from this table.<\/p>\n

    \"Display<\/p>\n

    Step 7: <\/strong>Click on the table and go to Design>>Grand Totals<\/strong> and select Off for Rows and Columns<\/strong> from the drop-down list.<\/p>\n

    \"Display<\/p>\n

    Step 8: <\/strong>The table will look like this after removing the grand total.<\/p>\n

    \"Display<\/p>\n

    2. How to Display Text in Pivot Table Values Area using Conditional Formatting?<\/strong><\/h2>\n

    Now we will see how we can display text in pivot table values area by using conditional formatting.<\/p>\n

    \"Display<\/p>\n

    Step 1: <\/strong>First we have to create a pivot table by using the data above. Select the table and go to Insert>>Pivot Table.<\/strong> From the Pivot Table dialogue box, choose New Worksheet<\/strong> and keep the Add this data to the Data Model<\/strong> box unchecked. Click OK.<\/p>\n

    \"Display<\/p>\n

    Step 2: <\/strong>Drag Salesman<\/strong> inside Rows<\/strong> field and Product Code<\/strong> inside Values<\/strong> field.<\/p>\n

    \"Display<\/p>\n

    Step 3: <\/strong>Click on Sum of<\/strong> Product Code<\/strong> and go to Value Field Settings.<\/strong><\/p>\n

    \"Display<\/p>\n

    Step 4: <\/strong>Select Max<\/strong> from Summarize values field by category<\/strong> and click OK.<\/p>\n

    \"Display<\/p>\n

    Step 5: <\/strong>This is our pivot table now where the product code has three values (101,103,105).<\/p>\n

    \"Display<\/p>\n

    Step 6: <\/strong>Now we will change these values into text. For that select the range (B2:B10) <\/strong>and go to Home>>Conditional Formatting>>New Rules.<\/strong><\/p>\n

    \"Display<\/p>\n

    Step 7: <\/strong>The New Formatting Rule<\/strong> dialogue box will appear on your screen. Select the third option from the first box (All cells showing \u201cMax of Product Code\u201d values for \u201cSalesman\u201d) <\/strong>and input the range inside Apply Rule To<\/strong> box. Then select Use a formula to determine which cells to format<\/strong> from the Select a Rule Type<\/strong> box. Inside the formula box, type this formula, =B2=101. After all these are done, click on Format<\/strong> option which is at the bottom right of this dialogue box.<\/p>\n

    \"Display<\/p>\n

    Step 8: <\/strong>From the Format Cells<\/strong> dialogue box, go to Number tab>>Custom. <\/strong>Now type this, [=101]\u201dMango\u201d<\/strong> inside Type box.<\/p>\n

    \"Display<\/p>\n

    Step 9: <\/strong>Click OK>>OK<\/strong>. All the Product codes 101 which was for the product Mango<\/strong> are now changed into text.<\/p>\n

    \"Display<\/p>\n

    Step 10: <\/strong>To change the other two product codes into product names, simply follow Step 6-9<\/strong> again.<\/p>\n

    To change product 103<\/strong> into Lichi, <\/strong>use =B2=103<\/strong> inside New Formatting Rule<\/strong> dialogue box and type [=103]\u201dLichi\u201d <\/strong>inside the Type<\/strong> box in Format Cells<\/strong> prompt.<\/p>\n

    To change product 105<\/strong> into Apple, <\/strong>use =B2=105<\/strong> inside New Formatting Rule<\/strong> dialogue box and type [=105]\u201d Apple\u201d <\/strong>inside the Type<\/strong> box in Format Cells<\/strong> prompt. When you are done, the pivot table will look like this.<\/p>\n

    \"Display<\/p>\n

    Step 11: <\/strong>To remove the Grand Total, select the table and go to Design>>Grand Totals>>Off for Rows and Columns.<\/strong><\/p>\n

    \"Display<\/p>\n

    Step 12: <\/strong>Final result.<\/p>\n

    \"Display<\/p>\n

    Application of Display Text in Pivot Table Values Area<\/strong><\/h2>\n
    \n
    \n
    \n
    \n
    \n