How to create pie chart from 1 column with duplicates

[Tyrel Smith]

Could someone please help me.

I'm trying to create a pie chart from only one column which has duplicates, I want it to display the name of the item and how many duplicates there are of it. Se below

Apple

Mac

Windows

Apple

Mac

Apple

Mac

Please help.

 

[6StringJazzer]

You can't do it with just that one column. Do you know in advance what the possible values are? If so then you would create another column (let's say B) with a list of possible values, and another column to the right of that which uses

=COUNTIF(A:A,B1)

Then you use column B and C to create your pie chart.

Book2
	A	B	C
1	Apple	Apple	3
2	Mac	Mac	3
3	Windows	Windows	1
4	Apple
5	Mac
6	Apple
7	Mac
Sheet3

Cell Formulas
Range	Formula
C1:C3	C1	=COUNTIF(A:A,B1)

 

[Misca]

I don't think you can create a chart using text values alone. That being said you can however create a pivot table from your text column. Pivot table puts the text values to the row labels but when you drag the same column to the values field as well the pivot table counts the number of values. You'll end up with two columns which you can visualize in a chart.

The same thing can be done without pivot tables as well but you're going to have to create the two columns yourself. The pivot table is by far the easiest way to get all the unique values. In Excel 365 there's the UNIQUE function as well but with older versions you're going to have to enter the unique values manually. The COUNTIF formula gives you the numeric values.

The pivot table solution is super easy but if the values change you're going to have to refresh the pivot table to get the new values. With the formula version the numbers update automatically if you've used a dynamic data range ( = select a single cell from your data and press Ctrl + T to turn it into a table ).

 

[Tyrel Smith]

You can't do it with just that one column. Do you know in advance what the possible values are? If so then you would create another column (let's say B) with a list of possible values, and another column to the right of that which uses

=COUNTIF(A:A,B1)

Then you use column B and C to create your pie chart.

Book2
	A	B	C
1	Apple	Apple	3
2	Mac	Mac	3
3	Windows	Windows	1
4	Apple
5	Mac
6	Apple
7	Mac
Sheet3

Cell Formulas
Range	Formula
C1:C3	C1	=COUNTIF(A:A,B1)

View attachment 26503

the problem is I don't know what the next values will be

 

[6StringJazzer]

you can however create a pivot table from your text column

Actually that is better than my idea. Then you do not need to know in advance what the possible values are. Here is what it would look like.

 

[Tyrel Smith]

Actually that is better than my idea. Then you do not need to know in advance what the possible values are. Here is what it would look like.
View attachment 26504

how did you create this

 

[Tyrel Smith]

Actually that is better than my idea. Then you do not need to know in advance what the possible values are. Here is what it would look like.
View attachment 26504

Will column "Row Labels" automatically update if I get Lenovo?

 

[Misca]

The row labels will not update automatically. To update them press the Refresh All button found from the Data tab of your Excel ribbon. This works if the data source you used to create the pivot table was a real Excel table ( = the Ctrl + T "trick" ). The table grows automatically when you add new rows to your data set. If you created your pivot table without a table the data range is fixed and you'll need to reset the range every time manually.

 

[6StringJazzer]

Here is a video demonstrating how to create the chart. I am using Office 365 so the menu navigation may be slightly different for you, but it should be similar and all the same options exist.

 

[tusharm]

By using the more recent (relatively speaking) dynamic array functions and spill references, you can automate the entire process. Suppose your data are in a table (as Excel understands it) in col A starting with A1. Suppose the header (A1) is 'Name' (w/o the quotes).

Then in some empty range, say C1:D1 enter the literals Unique and Count
In C2 enter the formula =UNIQUE(Table3[Name])
In D2 enter the formula =COUNTIF(Table3[Name],C2#)
Create 2 named ranges:

XRng	=Sheet5!$C$2#
YRng	=OFFSET(Sheet5!XRng,0,1)

Finally, create a pie chart that uses this names:
=SERIES(Sheet5!$D$1,Sheet5!XRng,Sheet5!YRng,1)

Now, if you add new values to the table in column A, Excel will update the chart.