To see less ads Register or Login ----- Daily Fantasy Sports games 18+

Excel graphs

A forum to discuss or share knowledge of spreadsheets.
Post Reply
User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Excel graphs

Post by barry »

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.....

User avatar
WilBert
Dumbledore
Posts: 7767
Joined: 28 Dec 2009, 21:07
Location: 2nd in SP4's Ashes comp.

Re: Excel graphs

Post by WilBert »

Simple way is to format the individual points on the graph
Image

User avatar
Knulpuk
Dumbledore
Posts: 8986
Joined: 05 Dec 2005, 13:22
Location: MTA (Mid Table Anonymity)
FS Record: Ultra ultra consistent (see above)

Re: Excel graphs

Post by Knulpuk »

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.

User avatar
WilBert
Dumbledore
Posts: 7767
Joined: 28 Dec 2009, 21:07
Location: 2nd in SP4's Ashes comp.

Re: Excel graphs

Post by WilBert »

Depends which version of Excel you're running as to how complex the solution you want too Barry.

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: Excel graphs

Post by barry »

WilBert wrote:Depends which version of Excel you're running as to how complex the solution you want too Barry.
old version at work (2003?)

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

User avatar
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

Re: Excel graphs

Post by Surprised »

Something like this?
You do not have the required permissions to view the files attached to this post.

User avatar
WilBert
Dumbledore
Posts: 7767
Joined: 28 Dec 2009, 21:07
Location: 2nd in SP4's Ashes comp.

Re: Excel graphs

Post by WilBert »

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 :oops:

(edit...I see you've done that now. Is that in Excel 2003?)

User avatar
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

Re: Excel graphs

Post by Surprised »

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

User avatar
WilBert
Dumbledore
Posts: 7767
Joined: 28 Dec 2009, 21:07
Location: 2nd in SP4's Ashes comp.

Re: Excel graphs

Post by WilBert »

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. :D

User avatar
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

Re: Excel graphs

Post by Surprised »

WilBert 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. :D
That's it
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.

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: Excel graphs

Post by barry »

Surprised wrote:Something like this?

aye exactly like that!
Surprised wrote:
WilBert 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. :D
That's it
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.
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....

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!!!

User avatar
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

Re: Excel graphs

Post by Surprised »

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

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: Excel graphs

Post by barry »

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!

User avatar
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

Re: Excel graphs

Post by Surprised »

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 :oops:

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: Excel graphs

Post by barry »

Worked a treat!

Found out there was a 4th class so have adapted it to include this - thanks again.

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: Excel graphs

Post by barry »

right, next question - :oops:

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? :lol:

User avatar
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

Re: Excel graphs

Post by Surprised »

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

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: Excel graphs

Post by barry »

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
I fkn love you.

User avatar
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

Re: Excel graphs

Post by Surprised »

It's nice to feel loved.

View Latest: 1 Day View Your posts
Post Reply

Return to “Spreadsheets”