3 different data ranges to chart on one graph

I have a graph I am trying to create that in essence has 
three different types of data in it, but they are all 
associated.

I am charting 2000-2005 information.  I would have 7 
columns of data

Year / Spending / %change from prior year / Segments / % 
change / Project counts / %change from prior year

For example this would be what 2001 would look like...
2001/1,500,000/56%/22,000,000/-34%/16,000/84%

So my numbers are all over the scale.  Can I do this?  
Any charting advice would be greatly appreciated. 


0
anonymous (74718)
9/9/2004 10:57:53 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
669 Views

Similar Articles

[PageSpeed] 45

Hi Donna -

One of the principles of effective data presentation, is to keep it 
simple. That is an awful lot of information, of vastly different value 
and "type" (i.e., values vs. percentage change). I think that I would 
avoid putting it all onto one chart, and break it up in one of two ways. 
(I know that sometimes it's hard to convince the boss, but you have to 
try.) Otherwise it will take viewers a long time and a lot of mental 
energy to figure out the complex chart.

Here's my preferred way. Make three charts, each one showing one of the 
values, paired with the corresponding percentage change. Start each by 
making a clustered column chart with both the value and the percentage. 
The percentage is tiny compared to the value, but we'll adjust that. 
First select the percentage series, and from the Chart menu, select 
Chart Type, and pick a Line chart type. Look at that, a combination 
chart, and you did it yourself. Select the new line chart, choose 
Selected Data Series from the Format menu, and on the Axis tab, choose 
Secondary. Adjust the two Y axes to improve the appearance of the chart.

For example, if you have some negative percentages, the X axis should 
cross the secondary Y axis somewhere between its endpoints. Pick min and 
max values for the secondary Y axis that look okay, then choose min and 
max values for the primary Y axis which are proportional to these. Say 
the percentage axis goes from -5% to 20%. That means the min is -1/4 of 
the max. So if the max of the primary Y axis is 40 Million, the minimum 
is -1/4 of that, or -10 Million. Now the X axis lines up with both 
zeros, and if you're clever with your tick spacing, the gridlines will 
line up with both sides. Now, the X axis and the gridlines are both 
black lines, too confusing. Double click the plot area, give it a black 
outline, and a white fill, not that ugly gray Microsoft likes. Double 
click the gridlines, and change their color to the lightest gray. What 
an improvement. Double click the X axis, and on the Patterns tab, select 
Low for the tick label position, so the labels aren't hovering within 
the plot area.

My second favorite way to plot this data would be a clustered column 
chart of the values. Now I realize the numbers are way off scale from 
each other. The smart thing would be to normalize them (fancy word for 
divide them) by a certain reference point. I would use the first year's 
values. This way, in the first year, the plotted value for each would be 
100%, and the values would show the relative change since the first 
year. To display the values, use data labels. But it's hard to get data 
labels to show something other than the category or the value, and our 
values have been normalized. But there are third party utilities to add 
the data labels you want, based on what's in some other cells. Here are 
two, both free, easy to install, and easy to use, and they work just as 
if Microsoft remembered to add this feature in the first place:

   Rob Bovey's Chart Labeler, http://appspro.com
   John Walkenbach's Chart Tools, http://j-walk.com

So use one of these to put the real values on the columns. Make a second 
chart, maybe using a line chart again, for the percentage change.

As I said before, I think it's ill-advised to try to get all that 
disparate data onto a single chart. Just because you CAN combine lots of 
series and use lots of colors and wonderful formatting effects, doesn't 
mean you SHOULD combine lots of series and use lots of colors and 
wonderful formatting effects. If my boss still insisted I put them all 
into one chart (and back when I had a boss, he just might have), I'd 
probably just do the normalized clustered column chart I described just 
above, with data labels that conveyed both the values and the percentage 
change.

Another idea is to do the two chart method, one large chart with 
clustered columns and values in the data labels, then make the line 
chart with percentages much smaller, and drag it over a corner of the 
larger chart. Look at that, a nice inset chart, just like in Time magazine.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Donna wrote:

> I have a graph I am trying to create that in essence has 
> three different types of data in it, but they are all 
> associated.
> 
> I am charting 2000-2005 information.  I would have 7 
> columns of data
> 
> Year / Spending / %change from prior year / Segments / % 
> change / Project counts / %change from prior year
> 
> For example this would be what 2001 would look like...
> 2001/1,500,000/56%/22,000,000/-34%/16,000/84%
> 
> So my numbers are all over the scale.  Can I do this?  
> Any charting advice would be greatly appreciated. 
> 
> 

0
9/10/2004 3:02:41 AM
Reply:

Similar Artilces:

Can I use a logo in place of the standard chart markers
I was hoping someone could help me figure out how to use my company's logo as my series marker on a chart I am working on. Hi Kimberly, Take a look at Jon Peltier's page to see the options available. http://peltiertech.com/Excel/ChartsHowTo/CustomSeriesFormatting.html Kimberly Bassininsky wrote: > I was hoping someone could help me figure out how to use my company's logo > as my series marker on a chart I am working on. > > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Custom format #3
To format a custom format for credit cards I tried 0000-0000-0000-0000 and it looks good like it should work. Somehow the last digit changes to 0 no matter what number I put in. Yes have tried it several times, on sheet the church sent me and again trying it on Excel 2003. Weird problem always ends in 0 ?? On Mon, 25 May 2009 12:56:59 -0400, "John McCabe" <jlmccabes@cfl.rr.com> wrote: >To format a custom format for credit cards I tried > 0000-0000-0000-0000 and it looks good like it should work. >Somehow the last digit changes to 0 no...

Auto Select Range?
Hey all, I am trying to get my macro to select all data (and perform calculations) if that data is included in a range of G/L numbers. I remember seeing something similar to this on this site, but I can't seem to find it again. Basically, I have G/L #s from 701000 to 999999. 701000 to 703104 are loan accounts. I want the macro to choose all of the accounts between and including 701000 and 703104. That way, if accounting adds G/L 702101, I won't have to change the macro. Thanks in advance for the help. How about a nice formula where a1 and a2 contain your numbers or use t...

3 leaflets in landscape on an A4 page. Any suggestions?
Microsoft has wonderful templates. Love them. Can't find a leaflet back and front which is on an A4 page which requires only 2 slices to make it in to 3 leflets. Max usage of paper, minimum cost and minimum environmental cost. Start with something close and modify it. Or remember that not everything needs to be started with a template. Creativity goes a long way. -- JoAnn Paules MVP Microsoft [Publisher] "Rosemarie" <Rosemarie@discussions.microsoft.com> wrote in message news:777EFC05-28DA-45A1-A6FA-687400391573@microsoft.com... > Microsoft has wonderful...

mail delivery #3
i just install new echange 2003 ent. to existing 2000 ad where is exchange 2000 standard. In my test enviroment all mail delivery succees in new server, but when i do exactly same in production, those one's which are in exchange 2003 they can't get any mails from "old server" or outside our corporate. They can send mail to users which mailboxes are inside our corporate, and they received mails from new servers. ...

Change orientation of data
Hello I have columns like this: Name, Company, Address, Telephone with more than a thousand rows. I need to present the data like this: Name Company Address Telephone Name Company Address Telephone etc An example might be: Liz Hurley Models Inc 1 The Avenue, London, SW1 6TT 020 8888 7777 Tom Jones Singers Ltd 2 The Avenue, London, SW1 6TT 020 8888 6666 etc etc How do I do that? ie is there some automated way? Angus Assuming your data is in A1:D1000: 1 - in an empty column, say F, enter this in F1:F4 xa1 xb1 xc1 xd1 2 - select F1:F5 (includes a blank cell) 3 - Drag the fill ha...

WU hangs on 3 of 3
Fresh SBS 2008 install, installed SP2 afterwards, reboot all ok, then executed windows update with all criticals, it downloads and installs succesfully. After reboot it starts windows with "configuring updates x of 3, don't turn off your computer" and the server hangs there on the "3 out of 3" on 0%, there's no lan or disk activity at all and the progress circle just keeps running. This already happened yesterday with a canceled WU(before installing SP2) and we had to format and reinstall thanks to this problem because safe mode worked but after th...

Windows Sharepoint Services 3.0 Migration.
Hello, I am in a bit of trouble here and need some help. We have a Windows 2003 Standard server (X86) running SQL 2005 and SharePoint Services 3.0. We need to migrate the SharePoint Services 3.0 to a Windows 2008 R2 server running SQL 2005 X64. The server name will be different than the original server. Since we have the server name, the operating system version and the processor architecture changing I am looking for clear instructions on how to migrate the SharePoint Application. I read a lot websites including this one here http://technet.microsoft.com/en-us/library/cc5...

Combination chart - Line
Hello! I am trying to create a combination chart that has bars and a line. The bars should all relate to one axis while the line should relate to the secondary axis. The problem is that when I make the chart, it relegates one of my bars to a line. I can change the axis to the primary where it should be but it is a line, not a bar. I have also tried making a column only chart and add in my line and secondary axis but to no avail. As soon as I put in the secondary axis, Excel converts one of my bars to a line. Any input would be a great help! Thanks, Debbie Hi, Make you own com...

math #3
How do I get the average of a group of years. Like 1999, 1998, 2000, etc. I think you need to provide much more detail to get a useful reply. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Bill" <Bill@discussions.microsoft.com> wrote in message news:044868EC-8884-4E40-9F93-EE9C5EFABCFA@microsoft.com... > How do I get the average of a group of years. Like 1999, 1998, > 2000, etc. maybe something like =AVERAGE(ROW(INDIRECT("1998:2005"))) as an array formula, so commit with Ctrl-Shift-Enter -- ...

Duplicate look_up values in VLOOKUP #3
Hi all, I've got a VLOOKUP formula functioning in the usual way. However, sometimes the look_up value is duplicated. When this occurs VLOOKUP always returns the first instance of the entry. Is there a means to get Excel to ignore entries it has already returned an entry for? The VLOOKUP is part of a bigger formula, being: =IF(AND('Dependency Log'!$A2=Matrix!$B3, 'Dependency Log'!$E2=Matrix!D$2),(VLOOKUP($B3,look_up,2,FALSE) ),"") Any ideas? TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Prof...

Upgrade #3
Hi Folks; If I am running MS Office 2000 on my XP Home Edition box can I upgrade just the Outlook portion of Office to Outlook 2003 or Outlook XP ????????????????? TIA Later, Malcolm On Sat, 19 Nov 2005 07:38:57 GMT, Malcolm <malcolm34465@yahoo.com> wrote: > >If I am running MS Office 2000 on my XP Home Edition box can I upgrade >just the Outlook portion of Office to Outlook 2003 or Outlook XP >????????????????? > Sure. Just know that you will not be able to use Word as your message editor in Outlook and you will not be able to use Word in a mail merge. -- <<&...

CCheckListBox #3
Dear Charles As you suggested to my question posted in ADO.Net news group I added the implimentation of the Create() function of CCheckListBox class. but it is still giving following error: error LNK2001: unresolved external symbol "public: virtual struct CRuntimeClass * __thiscall CMyCheckListBox::GetRuntimeClass(void)const " (?GetRuntimeClass@CMyCheckListBox@@UBEPAUCRuntimeClass@@XZ) Thanks Manjree "Manjree Garg" <garg@newsgroup.nospam> ha scritto nel messaggio news:F59BE84D-88CA-4994-8F1F-D6888AD8D45E@microsoft.com... > As you suggested to my qu...

more than 3 conditional formating in excel
Hi I am new to conditional formating in Excel. In row 2 I need to enter nos. between 1-5. I want each color to have a particular color. I have managed to do 4 (3 with conditional formating and the 4th retaining the default color). Is there a way i can do all 5 colors? Thanks You need to use VBA. Try this:- Private Sub Worksheet_Calculate() 'Code must be placed in the codemodule of the actual sheet you are working with. Dim oCell As Range For Each oCell In Range("A1:A20") Select Case oCell.Value Case Is < 1 oCell.Interior.Colo...

Data labels on chart
I'd like to add data labels to some of the data points on a line chart. I know how to do it manually, but I'd like to do it in a way that permanently associates the data label with the data point in the underlying data series. For example, I might have a graph of stock prices, which would show jumps or drops at certain points. I might want to label one point with "Yahoo rejects Microsoft offer" and another with "Apple introduces iPhone". I'm using Excel 2003. Thanks. Put the labels in an extra column. Then, check out Rob Bovey's nice Chart labeler:...

Paste cell value onto chart
Howdy all, I am creating Normal Probability plots to test normality assumptions and compare variances of two independent datasets. If the plotted data approximate a straight (diagonal) line, the data can be assumed to be normally distributed; and if the two plots are parallel, they can be assumed to have equal variances. I would like to add one additional bit of information to the finished chart, comparison of the Standard Deviation estimates from the two datasets (if larger SD/smaller SD is approximately >=3, then the SD's are probably "too different," and assuming equal var...

Recover Mailbox Data Feature not working as expected
Following the steps at http://www.microsoft.com/technet/prodtechnol/exchange/2003/RMD.mspx, no mailboxes show up under Servers => <servername> => Recovery Storage Group => Mailboxes. The right pane is just blank and I can't right click on a mailbox to use 'Exchange Tasks...' and do a 'Recover Mailbox Data'. Any ideas on how to get them to show up in there? The previous Admin didn't have any of the Recovery Storage Group stuff setup at all (I'm setting it up, but could have possibly made a mistake or something... ), and now I'm new to t...

OWA #3
Hi , we have the following setup and would like advice as to what others are doing. Internet-->Router->Cisco Pix-->(Internal Network->Exchange Srv) We would like to publish our Outlook Web Access the most secure way possible. We do not want to put a Windows 2k box in the DMZ or outside or Network to use as a front end server. Could we do the following: Internet-->Router->Cisco Pix with port 443 mapped to FrontEndSrv->(Internal Network->FrontEndSrv Exchange with tight security providing OWA for Internal Exchange server)...and of course ssl cert on the Exch box....

chart a time frame
I have a spreadsheet that contains a list of projects where each project has a start date and an end date. I need to chart these projects all into one chart showing the start date and end date for each different project in a monthly calander type of format with the months going across the top of the chart from left to right. I'm having trouble finding a chart that will plot these dates as point "A" and point "B" with a line in between for each different project. What type of chart should I use and how can I get the results that I'm looking for? Thank you ...

Macro Help #3
I turn to you guru's for help with a macro question. I have to worksheets in my workbook. Both have a coulmn that has a 6 digit number in each cell (an invoice number) What I need to do is have a macro that starts with the first invoice # in sheet 1, searches for it in sheet 2. If it finds it, delete that row from Sheet 2. Then it will look at the 2nd invoice number on sheet 1, Search from it in Sheet 2 If it finds it, delete that row from Sheet 2. etc, etc, etc so, my question to you is..... How? Modify this to suit. Sub Deletematches() On Error Resume Next For Each cel In [c14:...

Event id 1000 #3
Hello, I have office xp installed on pc with profing tools and sometimes when using outlook reply message, crash and open word with recouvering document. the error on event viewer is event id 1000 source microsoft office 10 . Please can help me! Thanks in advance. ...

Sorting data into a new column
I have three large sets of data (each column has up to 50,000 rows). The data are names (text) not numbers. I would like to create a fourth data set comprised of the names that are present in ALL three original columns. Basically, if a name is on list one, two, AND three, then it is placed on the fourth list. If the name is on just one or two of the three, then it will not be placed on the fourth list. Can I complete this sorting in Excel automatically? The datasets are too large to manipulate manually. Thanks. -- pbc444 ---------------------------------------------------------------...

Applying different formatting in the text box that has conditional
Is it possible to apply different formatting styles in a textbox that has reference to multiple fields and formulas? Thank you Yes. Show what you are putting in the text box and how you would want it formatted. -- KARL DEWEY Build a little - Test a little "olesja" wrote: > Is it possible to apply different formatting styles in a textbox that has > reference to multiple fields and formulas? Thank you olesja wrote: >Is it possible to apply different formatting styles in a textbox that has >reference to multiple fields and formulas? It's probably doable in a ...

print cel in column to one page
How can I setup an Excel spreadsheet column (of many rows) to print out each row of this column on a separate page? For instance: Column1 Column2 Column3 aa 001 print1 ab 002 print2 ac 003 print3 I would want "print1" to print on a page, by itself; "print2" to prin on a page by itself, etc... I only want the cels from Column3 to print; only those cels. How can I do that -- Steven Young You can click on "View/Page Break Preview" and, by dragging the page breaks, set each cell u...

Disclaimer #3
Hi We run Win2003, Exchange2003 and I would like to add a disclaimer on all outgoing emails. Is there an other way then buying a Add-on tool? If not what is good tool? Thanks Roman Sure. You can take a look at this: http://support.microsoft.com/default.aspx?scid=kb;EN-US;317680 or this http://support.microsoft.com/default.aspx?scid=kb;EN-US;317327 -- Martin Blackstone MVP - Exchange http://www.swinc.com/resource/exchange.htm http://www.swinc.com/resource/e2kfaq_appxc.htm "Roman" <roman@schwabbrothers.com> wrote in message news:OUj1pkBoEHA.4008@TK2MSFTNGP14.phx.gbl....