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.
