**Question description:**

*This user has given permission to use the problem statement for this blog.*

how to make a bar chart that displays the number of variants that exist in a column?

Solved by S. L. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
18/08/2018 - 06:53

hello there

User
18/08/2018 - 06:53

hi

Excelchat Expert
18/08/2018 - 06:53

Do you have a file to work on?

User
18/08/2018 - 06:54

yes i have

Excelchat Expert
18/08/2018 - 06:54

Please share kindly

User
18/08/2018 - 06:55

received?

[Uploaded an Excel file]

Excelchat Expert
18/08/2018 - 06:56

Yes, now can you please guide me on what is needed from this?

User
18/08/2018 - 06:57

I need to make a bar chart that shows how many variants are existing in the N column

User
18/08/2018 - 06:57

like, no of Local workshops and no of Auth workshops

User
18/08/2018 - 06:58

can we make it?

Excelchat Expert
18/08/2018 - 06:59

which column represents no of Local workshops and no of Auth workshops?

User
18/08/2018 - 06:59

the N column

User
18/08/2018 - 07:00

but it does not represent the no. of workshops

Excelchat Expert
18/08/2018 - 07:00

So number of times Local workshops is on column N is the number for that?

User
18/08/2018 - 07:00

yes yes yes

User
18/08/2018 - 07:00

exactly

User
18/08/2018 - 07:02

u thr?

Excelchat Expert
18/08/2018 - 07:02

Here check sheet 1

[Uploaded an Excel file]

User
18/08/2018 - 07:03

can you tell me how to do it?

Excelchat Expert
18/08/2018 - 07:04

Sure wait, I am making it a bit more polished

User
18/08/2018 - 07:04

thanks., but please be quick

User
18/08/2018 - 07:04

the time is running out

Excelchat Expert
18/08/2018 - 07:04

Here

[Uploaded an Excel file]

Excelchat Expert
18/08/2018 - 07:05

So lets start

Excelchat Expert
18/08/2018 - 07:05

First we need to count how many times Local and Authorised workshop are there in Column N

User
18/08/2018 - 07:05

oh., can't we skit it anyway?

User
18/08/2018 - 07:06

*skip

Excelchat Expert
18/08/2018 - 07:06

No, as we need a value for how many times those shops are there so we need a count

User
18/08/2018 - 07:07

oh., then how to calculate the count?

Excelchat Expert
18/08/2018 - 07:08

So you can see I have copied the names of local and auth shops as they are in column N of Form Responses 1 sheet

User
18/08/2018 - 07:08

yes., then

Excelchat Expert
18/08/2018 - 07:09

Under Local Workshop(B3 cell) I have put the formula to count from column N of sheet Form Responses 1on cell B4 of Sheet1

Excelchat Expert
18/08/2018 - 07:10

The formula is ....=countif(range of col N, the name to count)

Excelchat Expert
18/08/2018 - 07:10

in excels language which is.....=COUNTIF('Form Responses 1'!$N$3:$N$21,"="&B3)

User
18/08/2018 - 07:10

got it.,

Excelchat Expert
18/08/2018 - 07:11

in the fiormula....'Form Responses 1'!$N$3:$N$21....is the range from where we are counting

User
18/08/2018 - 07:11

then how to make the chart?

Excelchat Expert
18/08/2018 - 07:11

and "="&B3....is that we are counting the name which is equal to B3 of sheet1...which is Local....

Excelchat Expert
18/08/2018 - 07:11

Ok now we have got Local and Auth shop numbers

Excelchat Expert
18/08/2018 - 07:12

now select the cells B3:C4 by mouse cursor

Excelchat Expert
18/08/2018 - 07:12

Click on "insert" tab

Excelchat Expert
18/08/2018 - 07:13

Then click on "Insert Column Chart"

Excelchat Expert
18/08/2018 - 07:13

That should do

Excelchat Expert
18/08/2018 - 07:13

ok?

User
18/08/2018 - 07:15

can u see my writing?

Excelchat Expert
18/08/2018 - 07:15

where?

User
18/08/2018 - 07:15

in the excel space right to the chat box

Excelchat Expert
18/08/2018 - 07:15

no

Excelchat Expert
18/08/2018 - 07:16

yeah saw now

Excelchat Expert
18/08/2018 - 07:16

ok so first lets say you are focusing on range G1 through G10

User
18/08/2018 - 07:16

ok

Excelchat Expert
18/08/2018 - 07:17

and we need to know how many times A which is on cell I1 is there on G1 to G10

User
18/08/2018 - 07:17

ok.,

Excelchat Expert
18/08/2018 - 07:17

so you have wrote the function on A1

Excelchat Expert
18/08/2018 - 07:18

now lets select together rest

User
18/08/2018 - 07:18

ok

Excelchat Expert
18/08/2018 - 07:18

First I have cleared all except the function in A1\

Excelchat Expert
18/08/2018 - 07:18

check

User
18/08/2018 - 07:19

ok

User
18/08/2018 - 07:19

checked

Excelchat Expert
18/08/2018 - 07:19

Now, place your cursor inside the function by double clicking over A1

Excelchat Expert
18/08/2018 - 07:19

Done?

User
18/08/2018 - 07:19

done

Excelchat Expert
18/08/2018 - 07:20

now while you are on A1 and the cursor is blinking inside the function countif()....place your mouse over G1 and drag upto G10

Excelchat Expert
18/08/2018 - 07:20

Done?

User
18/08/2018 - 07:20

yeah

User
18/08/2018 - 07:20

done

Excelchat Expert
18/08/2018 - 07:21

Then put a comma next

User
18/08/2018 - 07:21

,then

Excelchat Expert
18/08/2018 - 07:21

No after G10...put a comma

User
18/08/2018 - 07:21

yeah yeah.,

User
18/08/2018 - 07:21

i have already put

User
18/08/2018 - 07:21

go on

Excelchat Expert
18/08/2018 - 07:22

After the comma...write.."="&I1

Excelchat Expert
18/08/2018 - 07:22

Then press enter

Excelchat Expert
18/08/2018 - 07:23

Now see..

User
18/08/2018 - 07:23

yeah done :-)

Excelchat Expert
18/08/2018 - 07:23

When we are changing any of The A within G1 to G10...the count is changing

User
18/08/2018 - 07:24

the I1 is for to source the keyword., ryt?

Excelchat Expert
18/08/2018 - 07:24

Yes

User
18/08/2018 - 07:24

understood :-)

Excelchat Expert
18/08/2018 - 07:24

Check now we are checking for B

User
18/08/2018 - 07:24

thanks a lot bro :-)

Excelchat Expert
18/08/2018 - 07:24

most welcome!

Excelchat Expert
18/08/2018 - 07:25

If you are leaving please give me ratings after ending manually from your end!

User
18/08/2018 - 07:25

so we must follow this step before making the chart., ryt?

User
18/08/2018 - 07:25

sure sure., but i'm not over yet

Excelchat Expert
18/08/2018 - 07:26

yes...have the number of times Local and Auth shops are there and then selecting those cells as I just did in Sheet 1

User
18/08/2018 - 07:26

ok ok

Excelchat Expert
18/08/2018 - 07:26

Look carefully in our case the range and Source key was in different sheets

User
18/08/2018 - 07:27

that's i can understand

User
18/08/2018 - 07:27

i can do it myself now

Excelchat Expert
18/08/2018 - 07:27

Great!

User
18/08/2018 - 07:27

but, in the file i sent i need convert every columns into rows, and rows into columns

User
18/08/2018 - 07:27

is it possible?

User
18/08/2018 - 07:28

oh., yes; then how to do it for different sheet?

Excelchat Expert
18/08/2018 - 07:29

For a different sheet just select the range from the sheet we need to have the count from

User
18/08/2018 - 07:30

oh., that's all?

User
18/08/2018 - 07:30

it's great :-)

Excelchat Expert
18/08/2018 - 07:31

Yes, you can do that the same way you did for the same sheet....just going to the sheet we need to have the count from

Excelchat Expert
18/08/2018 - 07:31

You can try that

User
18/08/2018 - 07:31

ok ok., fyn

Excelchat Expert
18/08/2018 - 07:31

As I have put the data now on Sheet 2 of the shared view

User
18/08/2018 - 07:31

then what about the conversion?

Excelchat Expert
18/08/2018 - 07:32

About that as that is a different type of question please post about that specifying on that part again, as we are not allowed to go for different types of question in the same session, sorry!

User
18/08/2018 - 07:33

oh., it's okay

Excelchat Expert
18/08/2018 - 07:33

Thanks for understanding!

User
18/08/2018 - 07:34

so let's go on with this

User
18/08/2018 - 07:34

let me do that this time

Excelchat Expert
18/08/2018 - 07:34

sure go on

User
18/08/2018 - 07:39

ok?

Excelchat Expert
18/08/2018 - 07:39

Yes perfect

Excelchat Expert
18/08/2018 - 07:40

You can try placing the chart now

Excelchat Expert
18/08/2018 - 07:40

You have 4 categories now

Excelchat Expert
18/08/2018 - 07:40

So there will be 4 bars

User
18/08/2018 - 07:41

u haven't taught me yet how to do that

Excelchat Expert
18/08/2018 - 07:42

Select from B4 to E5 of HSeet 2

Excelchat Expert
18/08/2018 - 07:42

*Sheet 2

Excelchat Expert
18/08/2018 - 07:42

Done?

User
18/08/2018 - 07:42

yes

Excelchat Expert
18/08/2018 - 07:42

B4 to E5

User
18/08/2018 - 07:43

yesh

User
18/08/2018 - 07:43

done

Excelchat Expert
18/08/2018 - 07:43

Check my selection...(Blue marks)

Excelchat Expert
18/08/2018 - 07:43

Now press insert

User
18/08/2018 - 07:43

mmm., then?

Excelchat Expert
18/08/2018 - 07:44

then chart

User
18/08/2018 - 07:44

then

Excelchat Expert
18/08/2018 - 07:44

Thats it

Excelchat Expert
18/08/2018 - 07:44

you have the chart, right?

User
18/08/2018 - 07:44

yes., i have

Excelchat Expert
18/08/2018 - 07:45

So thats it, right?

User
18/08/2018 - 07:45

don't we have to mention which one is the lable and which one it data?

Excelchat Expert
18/08/2018 - 07:45

You can ofcourse, those are the optional of chart

Excelchat Expert
18/08/2018 - 07:46

just right click on the chart

Excelchat Expert
18/08/2018 - 07:46

The on Chart axis& titles you will find all

User
18/08/2018 - 07:46

oh., so it just takes the upper row as lable., ryt?

Excelchat Expert
18/08/2018 - 07:46

Yes

Excelchat Expert
18/08/2018 - 07:46

A, B, C and D

User
18/08/2018 - 07:47

ok., got it :-)

User
18/08/2018 - 07:48

u can assist me only on this particular topic., ryt?

Excelchat Expert
18/08/2018 - 07:49

Yes, besides the time is almost run out

Excelchat Expert
18/08/2018 - 07:49

For further help you need to post again, please!

User
18/08/2018 - 07:50

ok., but the probability of getting you again is too low., ryt?

Excelchat Expert
18/08/2018 - 07:50

May be so, we all have to bid to get a client, so it varies

User
18/08/2018 - 07:51

mmm., ok,.

User
18/08/2018 - 07:51

anyways thank you so much for ur help

Excelchat Expert
18/08/2018 - 07:51

But all the experts are efficient , so dont worry!

User
18/08/2018 - 07:51

i was asking so many people about this issue

Excelchat Expert
18/08/2018 - 07:51

Most welcome and thanks for being so patient

User
18/08/2018 - 07:52

but nobody could help

User
18/08/2018 - 07:52

u r the one

User
18/08/2018 - 07:52

only one

Excelchat Expert
18/08/2018 - 07:52

I will take that as a compliment then! Haha!

User
18/08/2018 - 07:52

gave me the solution

User
18/08/2018 - 07:52

ha ha

User
18/08/2018 - 07:52

ok

User
18/08/2018 - 07:52

bye

User
18/08/2018 - 07:53

it's gng to end

Excelchat Expert
18/08/2018 - 07:53

Bye and have a nice time ahead

Excelchat Expert
18/08/2018 - 07:53

hope to see you soon

User
18/08/2018 - 07:53

:-)

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*