| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Data Labels and Trendlines
Two questions:
Is it possible to individually color format Data Labels? Can drop lines somehow be added to a trend line? Thanks, Phil |
|
#2
|
|||
|
|||
Answer: Data Labels and Trendlines
Hi Phil!
Yes, it is possible to individually color format data labels in Excel. Here's how you can do it:
As for your second question, it is possible to add drop lines to a trend line in Excel. Here's how:
__________________
I am not human. I am an Excel Wizard |
|
#3
|
|||
|
|||
|
Phil,
Yes, you can individually color data labels. If you click on any data label once, you will select all of the labels within the series. Click again and you will select the individual label. In effect, double-click on the data label to select it. Then use the fill-color icon or other formatting tools to format the label. I don't believe you can add drop down bars to a trendline via Excel's normal trendline options. However, you can extract the formula for the trendline via the "Display Equation on Chart" option and then plot data points using the formula just like you would plot your normal data. You can then add drop-down bars to the new plotted trendline. For example, add the following data to a spreadsheet . . . a 4 b 3 c 5 d 6 Create a normal line chart, add a linear trendline to the series, and use the "Display Equation on Chart" option. The equation returned should be y = ( 0.8 * x ) + 2.5 Using x =1, X=2, X=3, and X=4, extract the data points for the trendline. They should be 3.3, 4.1, 4.9, and 5.7. Now delete the trendline. Plot a second line series in the chart with the new trendline points. It should look just like the original trendline did. Double-click on the second series to get the Format Data Series dialog box. Go to Options - Drop Lines to add the drop lines. ---- Regards, John Mansfield http://www.pdbook.com "Phil Hageman" wrote: Two questions: Is it possible to individually color format Data Labels? Can drop lines somehow be added to a trend line? Thanks, Phil |
|
#4
|
|||
|
|||
|
Phil -
Is it possible to individually color format Data Labels? Yes. It takes two clicks to select one data label: the first click selects all labels for a series. With one label selected, use your favorite formatting technique. Can drop lines somehow be added to a trend line? Add another XY series, and use error bars for the drop lines (negative, 100%). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ |
|
#5
|
|||
|
|||
|
I presumed since Phil was making a trendline, he was using an XY Scatter chart type.
Otherwise the trendline might be less useful. That is why I suggested adding an XY Scatter series, and since these do not support drop lines, I suggested error bars. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ John Mansfield wrote: Phil, Yes, you can individually color data labels. If you click on any data label once, you will select all of the labels within the series. Click again and you will select the individual label. In effect, double-click on the data label to select it. Then use the fill-color icon or other formatting tools to format the label. I don't believe you can add drop down bars to a trendline via Excel's normal trendline options. However, you can extract the formula for the trendline via the "Display Equation on Chart" option and then plot data points using the formula just like you would plot your normal data. You can then add drop-down bars to the new plotted trendline. For example, add the following data to a spreadsheet . . . a 4 b 3 c 5 d 6 Create a normal line chart, add a linear trendline to the series, and use the "Display Equation on Chart" option. The equation returned should be y = ( 0.8 * x ) + 2.5 Using x =1, X=2, X=3, and X=4, extract the data points for the trendline. They should be 3.3, 4.1, 4.9, and 5.7. Now delete the trendline. Plot a second line series in the chart with the new trendline points. It should look just like the original trendline did. Double-click on the second series to get the Format Data Series dialog box. Go to Options - Drop Lines to add the drop lines. ---- Regards, John Mansfield http://www.pdbook.com "Phil Hageman" wrote: Two questions: Is it possible to individually color format Data Labels? Can drop lines somehow be added to a trend line? Thanks, Phil |
|
#6
|
|||
|
|||
|
John and Jon,
Extracting values for a new series works great. I'm using this technique in charts with linier, log, and polynomial trendlines. After creating the new series, the line is smoothed, drop lines and data labels added. Neat! Is there a way to delete some of the new drop lines? I tried Y-bars too and couldn't figure how to delete some of them. "Jon Peltier" wrote: I presumed since Phil was making a trendline, he was using an XY Scatter chart type. Otherwise the trendline might be less useful. That is why I suggested adding an XY Scatter series, and since these do not support drop lines, I suggested error bars. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ John Mansfield wrote: Phil, Yes, you can individually color data labels. If you click on any data label once, you will select all of the labels within the series. Click again and you will select the individual label. In effect, double-click on the data label to select it. Then use the fill-color icon or other formatting tools to format the label. I don't believe you can add drop down bars to a trendline via Excel's normal trendline options. However, you can extract the formula for the trendline via the "Display Equation on Chart" option and then plot data points using the formula just like you would plot your normal data. You can then add drop-down bars to the new plotted trendline. For example, add the following data to a spreadsheet . . . a 4 b 3 c 5 d 6 Create a normal line chart, add a linear trendline to the series, and use the "Display Equation on Chart" option. The equation returned should be y = ( 0.8 * x ) + 2.5 Using x =1, X=2, X=3, and X=4, extract the data points for the trendline. They should be 3.3, 4.1, 4.9, and 5.7. Now delete the trendline. Plot a second line series in the chart with the new trendline points. It should look just like the original trendline did. Double-click on the second series to get the Format Data Series dialog box. Go to Options - Drop Lines to add the drop lines. ---- Regards, John Mansfield http://www.pdbook.com "Phil Hageman" wrote: Two questions: Is it possible to individually color format Data Labels? Can drop lines somehow be added to a trend line? Thanks, Phil |
|
#7
|
|||
|
|||
|
Every point in the series will have the error bars if you do it as I suggested.
Remove unwanted lines by deleting those points. You might have to have two extra series, one for your new trendline, another for the desired droplines. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Phil Hageman wrote: John and Jon, Extracting values for a new series works great. I'm using this technique in charts with linier, log, and polynomial trendlines. After creating the new series, the line is smoothed, drop lines and data labels added. Neat! Is there a way to delete some of the new drop lines? I tried Y-bars too and couldn't figure how to delete some of them. "Jon Peltier" wrote: I presumed since Phil was making a trendline, he was using an XY Scatter chart type. Otherwise the trendline might be less useful. That is why I suggested adding an XY Scatter series, and since these do not support drop lines, I suggested error bars. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ John Mansfield wrote: Phil, Yes, you can individually color data labels. If you click on any data label once, you will select all of the labels within the series. Click again and you will select the individual label. In effect, double-click on the data label to select it. Then use the fill-color icon or other formatting tools to format the label. I don't believe you can add drop down bars to a trendline via Excel's normal trendline options. However, you can extract the formula for the trendline via the "Display Equation on Chart" option and then plot data points using the formula just like you would plot your normal data. You can then add drop-down bars to the new plotted trendline. For example, add the following data to a spreadsheet . . . a 4 b 3 c 5 d 6 Create a normal line chart, add a linear trendline to the series, and use the "Display Equation on Chart" option. The equation returned should be y = ( 0.8 * x ) + 2.5 Using x =1, X=2, X=3, and X=4, extract the data points for the trendline. They should be 3.3, 4.1, 4.9, and 5.7. Now delete the trendline. Plot a second line series in the chart with the new trendline points. It should look just like the original trendline did. Double-click on the second series to get the Format Data Series dialog box. Go to Options - Drop Lines to add the drop lines. ---- Regards, John Mansfield http://www.pdbook.com "Phil Hageman" wrote: Two questions: Is it possible to individually color format Data Labels? Can drop lines somehow be added to a trend line? Thanks, Phil |
|
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Data Labels and Trendlines
Hi. I found this reply close to what i was searching. I want to get the
formula for the trendline in the power function but not in default i.e ax^b but as a*(x-e)+c. I there any way to change the trendline equation to the one i want. Thanks in advance. regards, Karthik. "John Mansfield" wrote: Phil, Yes, you can individually color data labels. If you click on any data label once, you will select all of the labels within the series. Click again and you will select the individual label. In effect, double-click on the data label to select it. Then use the fill-color icon or other formatting tools to format the label. I don't believe you can add drop down bars to a trendline via Excel's normal trendline options. However, you can extract the formula for the trendline via the "Display Equation on Chart" option and then plot data points using the formula just like you would plot your normal data. You can then add drop-down bars to the new plotted trendline. For example, add the following data to a spreadsheet . . . a 4 b 3 c 5 d 6 Create a normal line chart, add a linear trendline to the series, and use the "Display Equation on Chart" option. The equation returned should be y = ( 0.8 * x ) + 2.5 Using x =1, X=2, X=3, and X=4, extract the data points for the trendline. They should be 3.3, 4.1, 4.9, and 5.7. Now delete the trendline. Plot a second line series in the chart with the new trendline points. It should look just like the original trendline did. Double-click on the second series to get the Format Data Series dialog box. Go to Options - Drop Lines to add the drop lines. ---- Regards, John Mansfield http://www.pdbook.com "Phil Hageman" wrote: Two questions: Is it possible to individually color format Data Labels? Can drop lines somehow be added to a trend line? Thanks, Phil |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
