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

The following figure shows a table and a pivot table created from the table. The table has no numeric data, containing 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 by counting the items.

Step 1 – Creating a Pivot Table from Text Data

• Select the data range.
• Go to the Insert tab.
• Select PivotTable.
• Choose From Table/Range.

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

• Excel will create a new pivot table.

Step 2 – Dragging Columns to PivotTable Fields

• Drag the Sex column to the Columns field.
• 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 a Pivot Table to Show Non-Numeric Values as a Percentage

• Drag the Location column to the Values field.

• Select the drop-down icon next to the new result you got.
• Select Value Field Settings.

• A Value Field Settings box will appear. Go to Show Values As.
• Choose % of Column Total.
• Click OK.

• Excel will show the count of locations 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.

<< Go Back to Pivot Table Value Field Settings | Pivot Table in Excel | Learn Excel