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.
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.
To better understand this article, you have to master the following topics:
- Creating a Pivot Table Manually
- Microsoft Excel Terminology
- Formatting the pivot table
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.
Step 1: Creating 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.
- Excel will create a new pivot table.
Step 2: Dragging 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.
- Excel will return the following output.
How to 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.
- 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.
- Excel will show the count of location as a percentage as well in the output now.
Read More: How to Calculate Median in Excel Pivot Table
Download Practice Workbook
Download this workbook and practice while going through the article.
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.