Graph in Excel without numerical values
comi60
-
gbinforme Posted messages 14930 Registration date Status Contributeur Last intervention -
gbinforme Posted messages 14930 Registration date Status Contributeur Last intervention -
Hello,
I am a beginner with Excel and would need a little help.
I have a table with drop-down lists in columns I and K. The user must choose the material in I (metal, plastic, cardboard, wood) and its color in K (yellow, green, blue, orange).
I would like to create a "pie chart" that shows the percentage for each color based on the material. My table has an unlimited size, which means that new rows will be added over time.
Thank you in advance for your help.
I am a beginner with Excel and would need a little help.
I have a table with drop-down lists in columns I and K. The user must choose the material in I (metal, plastic, cardboard, wood) and its color in K (yellow, green, blue, orange).
I would like to create a "pie chart" that shows the percentage for each color based on the material. My table has an unlimited size, which means that new rows will be added over time.
Thank you in advance for your help.
Configuration: Windows XP Firefox 2.0.0.20
9 réponses
Hello
You must select your data with the column headers first:
- you can select your 2 columns.
- you can select the documented range but it will be locked.
- you can create a named formula:
. 1 insert menu / name / define
. 2 "names in the workbook" you type "table" for example
. 3 "refers to" you put the formula
. 4 this will ensure that your table is always the right size without having to modify anything.
You create the pivot table:
- right-click in a free area of the menu and choose "pivot table"
- choose "assistant" on the left and then "next"
- type "=table" if you created the name, otherwise it's your range then "next".
- choose the same sheet or another and then "finish".
- place the material in the column
- place the color in the top row or the other way around, it's up to you and it's easily reversible.
- place the color in the center of the table or vice versa.
You create the chart associated with the pivot table:
- click on the chart wizard: the chart is created
- you just need to customize the chart to your liking.
All of this is dynamic afterwards and you can modify pretty much everything and have the chart follow your entries.
--
Always zen
You must select your data with the column headers first:
- you can select your 2 columns.
- you can select the documented range but it will be locked.
- you can create a named formula:
. 1 insert menu / name / define
. 2 "names in the workbook" you type "table" for example
. 3 "refers to" you put the formula
=OFFSET(Sheet1!$A$1;;;COUNTA(Sheet1!$A:$A);2)
. 4 this will ensure that your table is always the right size without having to modify anything.
You create the pivot table:
- right-click in a free area of the menu and choose "pivot table"
- choose "assistant" on the left and then "next"
- type "=table" if you created the name, otherwise it's your range then "next".
- choose the same sheet or another and then "finish".
- place the material in the column
- place the color in the top row or the other way around, it's up to you and it's easily reversible.
- place the color in the center of the table or vice versa.
You create the chart associated with the pivot table:
- click on the chart wizard: the chart is created
- you just need to customize the chart to your liking.
All of this is dynamic afterwards and you can modify pretty much everything and have the chart follow your entries.
--
Always zen
Hello
You need to make a summary of your table, and you can do that with a pivot table, which will then allow you to easily create a graph.
--
Always zen
You need to make a summary of your table, and you can do that with a pivot table, which will then allow you to easily create a graph.
--
Always zen
Hi.
It's difficult to help you further without seeing your table!
You can send it to us using https://www.cjoint.com/ or http://www.cijoint.fr/ but provided that it’s not in Excel 2007 format.
And since it’s impossible to draw a graph without numerical values, the work will consist of putting in values to identify the chosen colors.
So see you soon.
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
It's difficult to help you further without seeing your table!
You can send it to us using https://www.cjoint.com/ or http://www.cijoint.fr/ but provided that it’s not in Excel 2007 format.
And since it’s impossible to draw a graph without numerical values, the work will consist of putting in values to identify the chosen colors.
So see you soon.
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
Here is the file http://www.cijoint.fr/cjlink.php?file=cj200812/cijCblW9AV.xls
I cannot provide the original file because I am doing this project for a company as part of an internship and I am bound by professional secrecy!!!
So I created this file which captures the idea that I want to develop.
I am open to using macros if necessary.
Good luck and thank you for your help.
I cannot provide the original file because I am doing this project for a company as part of an internship and I am bound by professional secrecy!!!
So I created this file which captures the idea that I want to develop.
I am open to using macros if necessary.
Good luck and thank you for your help.
Hello
Here is a small example of what is possible with your file and a pivot table associated with a pie chart:
http://www.cijoint.fr/cjlink.php?file=cj200812/cijRjzacvx.xls
--
Always zen
Here is a small example of what is possible with your file and a pivot table associated with a pie chart:
http://www.cijoint.fr/cjlink.php?file=cj200812/cijRjzacvx.xls
--
Always zen
Hello.
"I have a table with drop-down lists in columns I and K"
Actually, these are not drop-down lists, but the auto-filter arrows in Excel.
"The user must choose the material in I (metal, plastic, cardboard, wood) and its color in K (yellow, green, blue, orange). I would then like to obtain a pie chart that shows me the percentage for each color based on the material"
I don't understand the process: the user only chooses the material, and your pie chart should represent the percentage of each color for that material? So why does the user also need to choose a color? The result is a single number, and there is no graph to draw!
The ambiguity in your explanation means that the pie chart proposed by gbinforme cannot adapt to all your choices... https://www.cjoint.com/?mBsXDzoReJ
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
"I have a table with drop-down lists in columns I and K"
Actually, these are not drop-down lists, but the auto-filter arrows in Excel.
"The user must choose the material in I (metal, plastic, cardboard, wood) and its color in K (yellow, green, blue, orange). I would then like to obtain a pie chart that shows me the percentage for each color based on the material"
I don't understand the process: the user only chooses the material, and your pie chart should represent the percentage of each color for that material? So why does the user also need to choose a color? The result is a single number, and there is no graph to draw!
The ambiguity in your explanation means that the pie chart proposed by gbinforme cannot adapt to all your choices... https://www.cjoint.com/?mBsXDzoReJ
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
Yes, I indeed referred to dropdown lists as automatic filter arrows because I don't distinguish between the two. Excuse me. However, if you look at my file, it is possible for each material to choose from a selection of 4 colors. The material and color variables are independent. So I want to obtain 4 charts showing the percentage of each color based on the material.