Excel graphs
- barry
- FISO Knight
- Posts: 18871
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Excel graphs
afternoon troops,
wanting to produce a simple graph at work using 2 columns, dates and results
30.08.10
31.72
24.05.11
46.71
02.11.11
42.39
01.05.12
35.79
29.05.12
44.69
20.07.12
27.62
24.08.12
43.27
09.10.12
37.86
12.11.12
71.9
but I want all the columns in the graph above 44 to be one colour, and the columns less than 35 to be another column, and the rest to be a third colour - is there an easy way to do this? plus it would be handy if the colours (relating to "class") could be shown on the legend.....
wanting to produce a simple graph at work using 2 columns, dates and results
30.08.10
31.72
24.05.11
46.71
02.11.11
42.39
01.05.12
35.79
29.05.12
44.69
20.07.12
27.62
24.08.12
43.27
09.10.12
37.86
12.11.12
71.9
but I want all the columns in the graph above 44 to be one colour, and the columns less than 35 to be another column, and the rest to be a third colour - is there an easy way to do this? plus it would be handy if the colours (relating to "class") could be shown on the legend.....
- WilBert
- Dumbledore
- Posts: 7767
- Joined: 28 Dec 2009, 21:07
- Location: 2nd in SP4's Ashes comp.
Re: Excel graphs
Simple way is to format the individual points on the graph
- Knulpuk
- Dumbledore
- Posts: 8986
- Joined: 05 Dec 2005, 13:22
- Location: MTA (Mid Table Anonymity)
- FS Record: Ultra ultra consistent (see above)
- FPL:
Re: Excel graphs
That what I would do - but I would make the basic graph then on each column I would just right click and use the shape fill drop down to change the colour that way on all the columns I wanted one colour.
I would then take that to powerpoint and then add the other legend manually probably.
I would then take that to powerpoint and then add the other legend manually probably.
- WilBert
- Dumbledore
- Posts: 7767
- Joined: 28 Dec 2009, 21:07
- Location: 2nd in SP4's Ashes comp.
Re: Excel graphs
Depends which version of Excel you're running as to how complex the solution you want too Barry.
- barry
- FISO Knight
- Posts: 18871
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Re: Excel graphs
old version at work (2003?)WilBert wrote:Depends which version of Excel you're running as to how complex the solution you want too Barry.
yes I have done the above suggestion (format individual data points) for the graph I produced but couldn't get it to tie up with a legend, the 3 colours would represent High, good and moderate.
bloody annoying as I know you can conditional format the raw data, but thought there would be a similar "method" for the graph....
I only ask as there may be a good few of these types of graphs to produce soon
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Excel graphs
Something like this?
You do not have the required permissions to view the files attached to this post.
- WilBert
- Dumbledore
- Posts: 7767
- Joined: 28 Dec 2009, 21:07
- Location: 2nd in SP4's Ashes comp.
Re: Excel graphs
Quick cheat for the legend - make a meaningless chart in the background with the legend headings you want.
Suprised, in later excel versions you can overlay the three columns per date that you've done there but I don't think it can be done in 2003? If you can I'd really like to know as one of my clients insist on 2003 and earlier
(edit...I see you've done that now. Is that in Excel 2003?)
Suprised, in later excel versions you can overlay the three columns per date that you've done there but I don't think it can be done in 2003? If you can I'd really like to know as one of my clients insist on 2003 and earlier
(edit...I see you've done that now. Is that in Excel 2003?)
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Excel graphs
It is Excel 2010 but can be done in all versions I think as it does not depend on any special extras.
Basically create 3 data series. 1 for High, 1 for good and 1 for moderate and use Stacked Column as chart type
I can post screenshot of the table if you want
Basically create 3 data series. 1 for High, 1 for good and 1 for moderate and use Stacked Column as chart type
I can post screenshot of the table if you want
- WilBert
- Dumbledore
- Posts: 7767
- Joined: 28 Dec 2009, 21:07
- Location: 2nd in SP4's Ashes comp.
Re: Excel graphs
Brilliant, thanks. No need for the table, solution makes sense now - combine the three options with some IF statements and you have a workable automated solution. Top work that man.
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Excel graphs
That's itWilBert wrote:Brilliant, thanks. No need for the table, solution makes sense now - combine the three options with some IF statements and you have a workable automated solution. Top work that man.
IF and IF(AND) for the over 35 part and it's done.
The key is to have #N/A in the cells where a number isn't needed as graphs don't plot #N/A.
I stumble across solutions by trial and error as I often present finance data to non finance people and I forget how I did things but this was one I remembered from a google search
If it looks OK barry then let me know and I can tell you how it's done.
- barry
- FISO Knight
- Posts: 18871
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Re: Excel graphs
Surprised wrote:Something like this?
aye exactly like that!
stacked columns eh? the nearest I got to it was to created 3 columns (m,g,h) and have blanks in each cell not required, the problem was it created space for 3 mini-columns in each date slot in the graph and was untidy....Surprised wrote:That's itWilBert wrote:Brilliant, thanks. No need for the table, solution makes sense now - combine the three options with some IF statements and you have a workable automated solution. Top work that man.
IF and IF(AND) for the over 35 part and it's done.
The key is to have #N/A in the cells where a number isn't needed as graphs don't plot #N/A.
I stumble across solutions by trial and error as I often present finance data to non finance people and I forget how I did things but this was one I remembered from a google search
If it looks OK barry then let me know and I can tell you how it's done.
if you could either tell me how it's done or even better, send me a copy (barryfisodas@yahoo.co.uk) and I'll work it out from there or come back to you with specific questions, that would be great, cheers mate!!!
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Excel graphs
My first attempt looked untidy as I used columns rather than stacked columns. No idea why it works differently but it does.
I think Excel plots blank cells.
I'll email you with a step by step guide with some screenshot if I can
I think Excel plots blank cells.
I'll email you with a step by step guide with some screenshot if I can
- barry
- FISO Knight
- Posts: 18871
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Re: Excel graphs
Surprised wrote:My first attempt looked untidy as I used columns rather than stacked columns. No idea why it works differently but it does.
I think Excel plots blank cells.
I'll email you with a step by step guide with some screenshot if I can
only if you get time mate, just the file and I should be able to suss it. many thanks Surprised!
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Excel graphs
barry wrote:Surprised wrote:My first attempt looked untidy as I used columns rather than stacked columns. No idea why it works differently but it does.
I think Excel plots blank cells.
I'll email you with a step by step guide with some screenshot if I can
only if you get time mate, just the file and I should be able to suss it. many thanks Surprised!
I'll send the file.
For some reason that never occurred to me
- barry
- FISO Knight
- Posts: 18871
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Re: Excel graphs
Worked a treat!
Found out there was a 4th class so have adapted it to include this - thanks again.
Found out there was a 4th class so have adapted it to include this - thanks again.
- barry
- FISO Knight
- Posts: 18871
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Re: Excel graphs
right, next question -
having wored a treat and produced a graph, I want to keep the table so I can cut and paste data in and the graph automatically is produced, but how do I save each graph? I tried copying the worksheet over in the thought that sheet 1 would produce graph 1 and if I copied sheet 1 to sheet 2 I could then produce graph 2 but it still references to sheet 1.
so I either need a way of remedying that or a way of pasting data in and saving the graph seperate (in a paste special - values) kinda way.
does that even make sense?
having wored a treat and produced a graph, I want to keep the table so I can cut and paste data in and the graph automatically is produced, but how do I save each graph? I tried copying the worksheet over in the thought that sheet 1 would produce graph 1 and if I copied sheet 1 to sheet 2 I could then produce graph 2 but it still references to sheet 1.
so I either need a way of remedying that or a way of pasting data in and saving the graph seperate (in a paste special - values) kinda way.
does that even make sense?
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Excel graphs
Copy the sheet.
Right click on the tab you want to copy (right click where it says "sheet 1") or whatever you called it.
Then select "move or copy"
Pick where you want it to be and make sure "create a copy" is checked.
That gives you a new sheet with an independent graph
Right click on the tab you want to copy (right click where it says "sheet 1") or whatever you called it.
Then select "move or copy"
Pick where you want it to be and make sure "create a copy" is checked.
That gives you a new sheet with an independent graph
- barry
- FISO Knight
- Posts: 18871
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Re: Excel graphs
I fkn love you.Surprised wrote:Copy the sheet.
Right click on the tab you want to copy (right click where it says "sheet 1") or whatever you called it.
Then select "move or copy"
Pick where you want it to be and make sure "create a copy" is checked.
That gives you a new sheet with an independent graph
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Excel graphs
It's nice to feel loved.
View Latest: 1 Day View Your posts