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, doubleclick on the data label to select it. Then use the fillcolor 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 dropdown 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. Doubleclick 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, doubleclick on the data label to select it. Then use the fillcolor 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 dropdown 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. Doubleclick 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 Ybars 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, doubleclick on the data label to select it. Then use the fillcolor 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 dropdown 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. Doubleclick 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 Ybars 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, doubleclick on the data label to select it. Then use the fillcolor 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 dropdown 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. Doubleclick 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*(xe)+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, doubleclick on the data label to select it. Then use the fillcolor 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 dropdown 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. Doubleclick 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  

