Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Annotations sticking to data point & data table
Hello!
I know that many people have asked the question here, but I can't seem to figure it out. The data I use is a 'rolling' 12 month calendar (in my example below they only want to see a rolling 7 quarters). I add annotations currently with a textbox for specific months until that month 'rolls' off my chart when the month's data is no longer displayed. I have downloaded the free add-in for the XYCharting, but it doesn't seem to work for me. When I hide the column with the data I no longer want to display, the data label does not move as it is supposed to. My other issue is this, I need to display the data table with my numbers. I haven't been able to figure out how to display my hidden line with my labels and not display this information in the data table. Does this make sense? If not, here is some data to show you what I am working with. Also, I put my charts on one worksheet and keep my data separate on another worksheet, in case that matters :) Data Worksheet Information: (floating timeline) Q1-06 Q2-06 Q3-06 Q4-06 Q1-07 Q2-07 Q3-07 Data displayed: 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% # Received 97 68 35 40 34 42 46 # Eligible 97 68 35 40 34 42 46 So my chart basically shows the % graphically and then my data table displays my numbers. This chart needs to be dynamic as I add a Quarter and 'hide' the column of the old quarter I no longer want to display. For example, when I hide Q1-06 and add Q4-07 I need any annotations I might have made on, let's say Q3-06 to move automatically.... Thoughts or suggestions?? ~andrea :) |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Annotations sticking to data point & data table
The problem with the data labels is that each label, from 1 to N, is
attached to a point from 1 to N. If you hide the data for point i, then what was point i+1 is now point i. The label for point i+1 is still on point i+1, it doesn't move to point i. You would need to rerun the labeling macro. I just answered another post, describing how much I hate data tables. They are inflexible, unformattable, and just an overall PITA. Why not use a spare range of the worksheet, put into it the data you want to show, format it the way you like, and display it next to the chart? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "AndreaB" wrote in message ... Hello! I know that many people have asked the question here, but I can't seem to figure it out. The data I use is a 'rolling' 12 month calendar (in my example below they only want to see a rolling 7 quarters). I add annotations currently with a textbox for specific months until that month 'rolls' off my chart when the month's data is no longer displayed. I have downloaded the free add-in for the XYCharting, but it doesn't seem to work for me. When I hide the column with the data I no longer want to display, the data label does not move as it is supposed to. My other issue is this, I need to display the data table with my numbers. I haven't been able to figure out how to display my hidden line with my labels and not display this information in the data table. Does this make sense? If not, here is some data to show you what I am working with. Also, I put my charts on one worksheet and keep my data separate on another worksheet, in case that matters :) Data Worksheet Information: (floating timeline) Q1-06 Q2-06 Q3-06 Q4-06 Q1-07 Q2-07 Q3-07 Data displayed: 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% # Received 97 68 35 40 34 42 46 # Eligible 97 68 35 40 34 42 46 So my chart basically shows the % graphically and then my data table displays my numbers. This chart needs to be dynamic as I add a Quarter and 'hide' the column of the old quarter I no longer want to display. For example, when I hide Q1-06 and add Q4-07 I need any annotations I might have made on, let's say Q3-06 to move automatically.... Thoughts or suggestions?? ~andrea :) |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Annotations sticking to data point & data table
When you format and display the data next to the chart is their a way
to 'link' the displayed data to the chart? The main reason why they use data tables here is because they have a, let's say, a wide range of users with an even wider range of skills. With the data table they can click and print the chart as 'one piece' verses having to move 2 pieces of data around if they want to say, put the chart in a PowerPoint or something. Don't get me wrong, I agree with you completely on the stinkin' data tables. They drive me nuts! I never used them before this job because of that very reason. You can't even change fonts on certain numbers, etc. if you want to draw the eye to a certain piece of data. Total pain in the toosh to me! So, if I were to nix the data table, you would suggest using a row and just hiding the other data points, correct? Do you have a link to a post that describes that really well. I am sort of going brain numb with this I think :) I appreciate all your help. Your advice is a serious time saver!!! ~andrea |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Annotations sticking to data point & data table
You don't mean link so much as fasten together, I presume. What you can do
is copy the range, select the chart, and hold down Shift while selecting the Edit menu. Select Paste Picture (which replaces Paste when you've held Shift). Then select the picture, and in the formula bar, type = plus the sheet-referenced address of the range you copied, including dollar signs (without the dollar signs you get an irrelevant error message). This links the picture with the range, so any changes to the range are reflected in the picture. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "AndreaB" wrote in message ... When you format and display the data next to the chart is their a way to 'link' the displayed data to the chart? The main reason why they use data tables here is because they have a, let's say, a wide range of users with an even wider range of skills. With the data table they can click and print the chart as 'one piece' verses having to move 2 pieces of data around if they want to say, put the chart in a PowerPoint or something. Don't get me wrong, I agree with you completely on the stinkin' data tables. They drive me nuts! I never used them before this job because of that very reason. You can't even change fonts on certain numbers, etc. if you want to draw the eye to a certain piece of data. Total pain in the toosh to me! So, if I were to nix the data table, you would suggest using a row and just hiding the other data points, correct? Do you have a link to a post that describes that really well. I am sort of going brain numb with this I think :) I appreciate all your help. Your advice is a serious time saver!!! ~andrea |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Annotations sticking to data point & data table
Right, I mean fasten together ;) Sounds great, let me give that a try
and see if it will work for our purposes here. So if the user copied the chart this would also copy the data with it? They like to see the numerator and denom. with their charts. Many of their old charts with the data tables are often used in PowerPoints for the board here. Thanks for all your help and btw, your site has some great tutorials, you have saved me a TON of time! I appreciate it! ~andrea |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving a line chart data point revises data table value in Excel ' | Charts and Charting in Excel | |||
Formats and Data validations changes are not sticking | Excel Worksheet Functions | |||
Data point on line is not over the point/tick in X axis... | Charts and Charting in Excel | |||
Including data in data table but not charting as a series or point | Charts and Charting in Excel | |||
Data "Point" in graph not matching table | Charts and Charting in Excel |