How to create a pivot table from non-numeric values/data

In this article, you will learn how to create a pivot table from 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:

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

This pivot table is created from a table that has no numeric values.

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.

Creating a pivot table from non-numeric data

The pivot table, after making some changes.

  • 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):

Employee-list.xlsx


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

2 Comments
  1. Reply
    Biobiooi July 22, 2015 at 5:55 PM

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

  2. Reply
    Kel September 21, 2016 at 9:59 AM

    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.

    Leave a reply