How to Create Pivot Table with Values as Text (with Easy Steps)

In this article, you will learn how to create a pivot table with values as text. That means I will show how to create Pivot Table with non-numeric data or values. It’s really fun.

Let’s start…

From our previous discussion, you already know how to create pivot tables using a set of data. Creating a basic pivot table is easy, so we have created pivot tables with additional features that you may find helpful.

Generally, pivot tables are created from numeric data, but you can create pivot tables with some types of non-numeric data. Creating pivot tables with non-numeric data involves counting, as you can’t sum non-numbers.

This article is part of my series: Pivot Table Tutorials for Dummies: Learn Excel Pivot Table Step by Step!

To better understand this article, you have to master the following topics:


Download Practice Workbook

Download this workbook and practice while going through the article.


2 Easy Steps to Create Pivot Table with Values as Text

The following figure shows a table and a pivot table created from the table. The table has no numeric data, a list of some employees, their location, and gender. Though the table has no numeric values, it is possible to create a useful pivot table. Creating a pivot table this way counts the items. I will show how to create a pivot table with values as text using this dataset.

Create Pivot table pivot table values as text


Step 1: Create Pivot Table from Text Data

Let me first explain how to create a pivot table.

  • First, select the data range.
  • Then, go to the Insert
  • After that, select PivotTable.
  • Finally, choose From Table/Range.

  • A box will appear. Select New Worksheet to create a pivot table in a separate worksheet.
  • Then, press OK.

Create Pivot table pivot table values as text

  • Excel will create a new pivot table.

Create Pivot table pivot table values as text


Step 2: Drag Columns to PivotTable Fields

Now, you need to analyze the dataset using PivotTable Fields. To do so,

  • Drag the Sex column to the Columns field.
  • After that, drag the Location column to the Rows field and Values field.
  • Excel will automatically show the Count of Location when you drag the Location column to Values Field.

Drag Columns to Fields-pivot table values as text

  • Excel will return the following output.


Use Pivot Table to Show Non-Numeric Values as Percentage

You can also express values in percentage format in Pivot Table. Let’s see how you can do that.

  • Drag Location column to Values field

Drag Columns to Fields-pivot table values as text

  • After that, select the drop-down icon (see image).
  • Then, select Value Field Settings.

  • Value Field Settings box will appear.
  • Then, go to Show Values As
  • After that, choose % of Column Total.
  • Finally, click OK.

Drag Columns to Fields-pivot table values as text

  • Excel will show the count of location as a percentage as well in the output now.

Drag Columns to Fields-pivot table values as text


Conclusion

In this article, I have explained how to create a pivot table with values as text. I hope it helps everyone. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

4 Comments
  1. Is there a way we can compare 2 pivot tables with similar information to spit out the difference betwen the two ?

  2. Hi, I just want to ask, I’m making a template for the company I am currently working (Bank here in the Philippines). I’m making a template to check if clients have submitted all necessary documents to open an account. I have made a table already. so in column a are list of sample accounts and for columns b onwards are for the required documents. i’ve made a drop down for those so we will only input either “ok” or x. is there a way i can summarize for example, account of john doe, all documents marked with x? thanks.

    • Hi there!

      I am assuming that you want the following result.
      get the summary data from a dataset
      Then follow the steps below.

      First, apply the following formula in cell E2 and copy it down.
      =TEXTJOIN(",",TRUE,IF(B2:D2="X",$B$1:$D$1,""))

      Then, filter out the blank cells from column E.

      Next, hide columns B to D.

      Now you can print the summary data.

      Please let us know if this is what you needed. If not then tell us more about it so that we may help. And thank you for being with us.

      Regards
      Md. Shamim Reza (Exceldemy Team)

Leave a reply

ExcelDemy
Logo