Use a Pivot Table to Count Items in Excel

To make Excel count values instead of listing them by title—such as counting how many times a value appears (like "Apples" appearing 5 times)

To make Excel count values instead of listing them by title—such as counting how many times a value appears (like "Apples" appearing 5 times)—you'll want to summarize your data first, usually with a Pivot Table or using COUNTIF formulas.

Here are two main ways to do it:

✅ Option 1: Use a Pivot Table to Count Items

This is the easiest and most flexible method:

Example Data:

Item
Apples
Oranges
Apples
Bananas
Apples

Steps:

1. Select your data column (e.g., A1:A6).
2. Go to the Insert tab → click PivotTable.
3. In the dialog:
   • Choose where to place the PivotTable (new worksheet is fine).
4. In the PivotTable Fields pane:
   • Drag Item to the Rows area.
   • Drag Item again to the Values area — it will default to Count of Item.

💡 Now you'll see a table like:

Item	Count of Item
Apples	3
Bananas	1
Oranges	1

From here, you can highlight the Pivot Table and insert a bar or pie chart to visualize the counts.

✅ Option 2: Use COUNTIF to Count Manually

If you don’t want a Pivot Table:

1. List unique items (e.g., Apples, Oranges, Bananas) in a column.
2. Use this formula next to each:

excel

CopyEdit

=COUNTIF(A:A, "Apples")

Or, referencing a cell:

excel

CopyEdit

=COUNTIF(A:A, B2)

Where B2 contains "Apples".

3. Copy this formula down for each item.

You can then select the summary data and insert a chart (e.g., Column Chart).

🧠 Want It Dynamic?

Use PivotTables + Charts to auto-update counts as new data is added. Just refresh the Pivot Table:
Go to PivotTable Analyze → Refresh.