In this article, you will learn how to create a pivot table from 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.
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:
- Creating a Pivot Table Manually
- Pivot Table Terminology
- Pivot Table Calculations
- 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 400 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 rather than sums them.
Creating a Pivot Table from Non-numeric data or values
Here are the steps that generate this pivot table:
- The Sex field is placed in the Columns area.
- The Location field is placed in the Rows area.
- The Location field is also placed in the Values area and is summarized by Count.
- We have turned off the Field Headers in our pivot table. If you want to turn off the field headers, choose PivotTable Tools ➪ Analyze ➪ Show group. Show Group will be deselected.
Now we want to create another pivot table using the same data that will show the values in percentage.
- The second instance of the Location field is added to the Values section. Then right-click on any value in the newly created column and choose Show Values As ➪ Percent of Column Total from the shortcut menu.
- Then change the field names in the pivot table to ct and pct.
- You can select a pivot table style that makes it easier to distinguish the columns.
Happy Excelling 🙂
Download Working File
Download the working file that I’ve used to create this blog post (from the link below):