Create an X Y Scatter Chart with Data Labels

Подписаться 78 тыс.

Просмотров 454 тыс.

50% 1

How to create an X Y Scatter Chart with Data Label. There isn't a function to do it explicitly in Excel, but it can be done with a macro. The Microsoft Knowledge base article describes it. See the link to the article (along with the Macro code) at support.microsoft.com/en-gb/o...
If you have Excel 2013 or later, there is a function to add descriptive labels you can actually put labels without any VBA. See video at • How to Create and Cust...
If you have Excel 2010 or earlier and experiencing Error 91 this might help. To avoid 'error 91', you must have the object (chart) selected at the time you run the macro. Also see docs.microsoft.com/en-us/prev...
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

Опубликовано:

1 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 309

@eivihnd 9 лет назад

Actually, there is a function to add descriptive labels in Excel 2013: After you have created your XY Scatter Graph, selected X- and Y-values and clicked "Add Data Labels" (4:15 in this video), do the following: 1. Click on one point so that they are all highlighted 2. Right click on one of them (while all are highlighted), and click "Format Data Labels" 3. Under "Label Options" appearing on the right side (default), deselect "X Value" and "Y Value" 4. Select "Value From Cells" 5. Click on the button "Select Range" 6. Now, select the row containing data for your labels (for example the "items" column) 7. Voilà, you have custom labels with no coding needed.

@bigheadmonkeyboy 9 лет назад

***** +eivihnd Thanks to both of you. Helped a lot!

@davidfsnyder 9 лет назад

+Doug H I have Excel for Mac 2011 so I still had to use the macro. Thanks for saving me time!

@thomasmurphy6501 8 лет назад

+eivihnd THANK YOU!

@fortoday04 8 лет назад

+eivihnd real MVP

@excelnbafun7782 8 лет назад

+eivihnd oh wow so nice! Thank you sir.

@WorkDovePeace 7 лет назад

This was the BEST tutorial I found to figure out how to add data labels to a scatter plot chart. Doug -- Thank you so much for posting this!

@DougHExcel 7 лет назад

Wow.... thanks for the kind words!

@gregorypiasecki1589 8 лет назад

YOU ROCK. I have been using excel since 1996 and have been frustrated by my inability to add labels. Thank you!

@tha_rock7281 8 лет назад

You just saved my entire day. Been spending all day trying to add data labels, but with your video it only took 5 min's. Thank you very much!!

@DougHExcel 8 лет назад

You're welcome...glad it helped!

@DavidBrossardJ 7 лет назад

Your video was awesome! The right pace, the right explanation (except maybe for how to create the macro itself), and the end result! Thanks so much

@DougHExcel 7 лет назад

Glad you liked it!

@alexo9183 9 лет назад

I searched for an hour and this is the best explanation I found. Good work! (although you could skip the first 2:20 of the video). Thank you

@JohnMmbaga_ 7 лет назад

Thanks a lot Doug. I have successfully attached labels on my excel graphs thanks to your guidance. Much appreciation!!!

@DougHExcel 7 лет назад

Glad this helped. Thanks for the comment!

@elizabethnoordhoek4029 9 лет назад

Thank you so much I was so confused on how to create different series for scatter plots!

@excelisfun 10 лет назад

Thanks, Doug!!

@alannahvirtanen8431 7 лет назад

Thank you so much!! I've been trying to figure out how to do this and no-one in my office knew the answer. But you did - and made my life so much easier! Thanks very much!

@DougHExcel 7 лет назад

You're welcome...thanks for the comment!

@sandman_theory 9 лет назад

Fixed the problem I had by 'adding data manually' just like how you said.. Thanks man!

@prabodh231 8 лет назад

That's an excellent tutorial! Many thanks

@deanm1228 9 лет назад

Thanks for the great tutorial. Clear and concise instructions. Saved me hours of work.

@ivankitta9458 3 года назад

Thank you so much, Doug! I didn't know it was not possible to label data points in a scatter chart in Excel. I was desperate...

@DougHExcel 3 года назад

You’re welcome!

@debster0626 6 лет назад

I'm SOOO excited! Thank you SO MUCH for both the pointers!

@DougHExcel 6 лет назад

Hi Deb Phillips, glad you liked it, thanks for commenting!

@pandorahope9166 9 лет назад

Awesome. Thank you! Your explanation was v. clear and worked first time.

@jeff32407ify 9 лет назад

Thank You!!!! Manually added the data and it worked.

@r_b5951 9 лет назад

Thank you Doug. Appreciate your time and effort to create this tutorial. I'm running Excel 2010, so the 7-step process posted by eivihnd is not available to me. But since I am running Excel 2010 this tutorial is a great help.

@michaelnewman8708 6 лет назад

Wonderful! This helped me create a great scatter plot of the revenue and gross margin for our company's product lines. Thank you!

@DougHExcel 6 лет назад

Thanks Michael Newman, glad it helped!

@Rajeshshiva247 8 лет назад

I did same in my project with help of this video and I got award. thank you sir

@1Peezy1 8 лет назад

Good job man!!

@Rajeshshiva247 6 лет назад

Sir, please share you mail id because I have prepared one Excel VBA programming for automatically send mail from Excel when due date reached.but email continues trigger after reach due date. I need to stop the mail in the VBA programming. Please support

@DougHExcel 3 года назад

Thanks Rajesh Dakshanamoorthy, glad it helped!

@richardvaughan9798 8 лет назад

you're a hero, Doug! Fantastic. Thanks so much.

@DougHExcel 3 года назад

Hi Richard Vaughan, you're welcome!

@mattnicks 6 лет назад

Really helpful, thank you. Worked first time in Excel 2010.

@DougHExcel 6 лет назад

Thanks mattnicks, glad it helped!

@nlmxyz 5 лет назад

thanks so much. Extremely useful. Had been trying for several hrs

@DougHExcel 5 лет назад

Hi Indra Bishop, glad you liked it, thanks for commenting!

@ziyabg1461 5 лет назад

Thank you Doug! that is helpful. This option should be available through "Format data labels" by clocking on any data point.

@DougHExcel 5 лет назад

Hi Elton Mammadov, glad you liked it, thanks for commenting!

@Chakoando 9 лет назад

Thanks so much to both Doug and eivihnd!

@gangaramchoudhary3711 6 лет назад

Thank you very much my friend Mr. Doug H. I appreciate your video.

@DougHExcel 6 лет назад

Hi Gangaram Choudhary...you're welcome, glad you liked!

@mehdi5738 8 лет назад

Really nice. thanks dude. I learnt a lot of stuff with this vid

@thetribal 8 лет назад

excellent demonstration. Thank you.

@DougHExcel 3 года назад

Hi Ali Khan, thanks for the comment!

@valentinakalyonova4976 8 лет назад

Perfect! Thank you!

@dannykay4967 7 лет назад

first answer that makes sense - you are my hero! and the macro works! thank you so much!

@DougHExcel 7 лет назад

You're welcome; thanks for the comment!

@garykassabian151 8 лет назад

Excellent job ! Thank you so much for making this video.

@DougHExcel 3 года назад

Hi Gary Kassabian, you're welcome!

@astutidewiwarawati7670 8 лет назад

This vid helps a lot... Thank's so much :)

@joseph10171 4 года назад

What a life saver. Thank you

@DougHExcel 4 года назад

Hi Joseph George, thanks for the kind words!

@alejandrocastillo54 9 лет назад

You've saved me hours of work

@logen99999 7 лет назад

This was super helpful. thank you!

@DougHExcel 7 лет назад

You're Welcome! Glad you found it helpful!

@tiffanymitchell2703 9 лет назад

This was very helpful, thank you. When running the macro, you have to have the chart selected, or else it won't work.

@PernilleDahl 8 лет назад

Thank you! that was really good explaining! :D

@DougHExcel 3 года назад

Hi PernilleDahl, you're welcome!

@axetrac10 8 лет назад

Loved the video...very informative.

@DougHExcel 3 года назад

Hi Akshay Kamath, thanks for the comment!

@TonyK55 7 лет назад

Hi Doug Many thanks for this video. I managed to add labels for the first time with them all aligned properly. I did find a couple of issues with filtering data and the code was then mis-aligned. But as I said managed it for the first time with my usual Excel jiggery pokery, thanks again.

@DougHExcel 7 лет назад

You're welcome, glad it worked for you!

@marchansen528 7 лет назад

Super helpful, thank you!

@DougHExcel 7 лет назад

Glad you liked!

@waynebarthule 6 лет назад

Awesome, thanks Doug!

@DougHExcel 6 лет назад

+Wayne Barthule Glad you liked!

@stevevh4 6 лет назад

Thanks so much for the video! This is exactly what I have been trying to do for days! Now, if I could only control the labels...meaning show and hiding based on what I am sorting...

@DougHExcel 6 лет назад

Try using the NA() function for the second table that sources the chart. See this video for some insight. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-2pF7sKR8wRQ.html

@helenahall-manning335 7 лет назад

Thanks so much for this! It worked perfectly!

@DougHExcel 7 лет назад

Hi Helena Hall-Manning...you're welcome, glad you liked!

@volkerfremuth7746 8 лет назад

Thanks, was driving me nuts!

@DougHExcel 8 лет назад

Glad you liked!

@sr7882 8 лет назад

that was excellent thanks dude

@GreyFoxNinjaFan 7 лет назад

Great video. Really helped me out.

@DougHExcel 7 лет назад

Glad it helped you!

@agniandjani 8 лет назад

thank you so much! this is really helpful :)

@DougHExcel 3 года назад

Thanks tinypeaces, glad it helped!

@asyrafsalman5808 8 лет назад

Hi, thanks for the video! How does one edit the macro so that it displays name, x-axis figure, and y-axis figure as well?

@lagbaja3735 9 лет назад

Fantastic. Thanks very much

@JacobColegrove 6 лет назад

Thank you that worked wonders.

@DougHExcel 6 лет назад

Hi Jacob Colegrove, glad you liked it, thanks for commenting!

@likepiano 6 лет назад

thanks, very helpful video!

@DougHExcel 6 лет назад

Thanks like piano, glad it helped!

@nicbowles 7 лет назад

Thanks, very helpful

@DougHExcel 7 лет назад

You're welcome!

@alexrosen8762 7 лет назад

Very useful. Thanks!

@DougHExcel 7 лет назад

Hi Alex , thanks for the comment!

@cerenkaragul 8 лет назад

very helpful !

@yvkoay 8 лет назад

Thanks a lot. This is really good!

@DougHExcel 3 года назад

Hi Dadawa, thanks for the comment!

@PhelipeRabay 7 лет назад

Thanks a lot. Nice tutorial :)

@DougHExcel 7 лет назад

You're welcome!

@yem.t.3930 5 лет назад

Thank you very much Sir!

@DougHExcel 5 лет назад

You're Welcome!

@TheSpiralout11235 9 лет назад

Thank you very much Doug. Great tutorial. Do you happen to know how I can loop through each series in my chart?

@manuelsaenz7898 8 лет назад

Very useful!

@tengkutheo908 9 лет назад

very helpful, thanks

@xcharles1 8 лет назад

Thanks, Doug! My scatter has hundreds of points with dense concentrations in areas; the labels appear per your instructional, but are cluttered and cannot be read/presented. Any idea on how to do this, so that when you hover your mouse, the label appears ala a standard chart with auto labels?

@Fractus 8 лет назад

Nice macro, thanks.

@DougHExcel 8 лет назад

Thanks!

@Ratnabhsl 8 лет назад

thank you very much! very helpful :D

@DougHExcel 3 года назад

Thanks Ratnabhsl, glad it helped!

@PenpornKoanantakool 9 лет назад

Thanks for the script!

@jessicahalfmann7218 8 лет назад

How would a draw a line down the middle the perfectly split the chart? Thank you Doug H!

@RohitBedi8 7 лет назад

Thanks a lot this helped me

@DougHExcel 7 лет назад

Glad it helped; thanks for the comment!

@15Dalma15 7 лет назад

Thanks! It helped a lot! :)

@DougHExcel 7 лет назад

Hi Dalma dr. Geréné dr. Radványi...you're welcome, glad you liked!

@phillipschoe9518 8 лет назад

Wow thanks so much for this

@DougHExcel 3 года назад

Hi Phillips Choe, you're welcome!

@FreshlyBased 9 лет назад

Thanks, this just saved my project!

@arashrezaee3318 9 лет назад

***** hi Doug. could you please share the Macro that you used for data label. i can not find that!

@arashrezaee3318 9 лет назад

***** highly appreciated!

@arashrezaee3318 9 лет назад

***** hi Doug! i found sth else in this case. it is called "XY chart Label". i downloaded it for free also: [ www.appspro.com/Utilities/ChartLabeler.htm] it is very useful for those people who wanna labeling a lot of different data.

@rodrigolovaton797 7 лет назад

When you add the macro in VBA, you have to do it as "insert / module", otherwise it doesn't work. At least that fixed the problem for me. Thanks for the video!

@DougHExcel 7 лет назад

You're welcome!

@brianluczak7965 8 лет назад

worked great!

@DougHExcel 3 года назад

Thanks Brian Luczak, glad it helped!

@floydjared 7 лет назад

Nice job!

@DougHExcel 7 лет назад

Thanks!

@anindita8990 8 лет назад

Thank you!!

@DougHExcel 3 года назад

Hi Anindita Gandhi, you're welcome!

@zaientz1 7 лет назад

Thank you thank you thank you!

@DougHExcel 7 лет назад

You're Welcome!

@Robis1K 7 лет назад

Thank you very much. Very helpful! May be relevant to highlight that your excel file should allow macros. Sometimes it is not the case by default. Hence when saving the macro you receive a warning and you should click on "no" and choose a macro-enabled excel format. I wasted a hour because of that

@DougHExcel 3 года назад

Hi Roberto M, thanks for the comment!

@rameshraguan8848 8 лет назад

thank you

@riaanvanaarde 8 лет назад

Hi, Works great. I get the following error though. Using Excel 2010 Object variable or Block Variable not set Can you assist please.

@amjackson34 8 лет назад

Thank You!!!

@DougHExcel 3 года назад

Hi amjackson34, you're welcome!

@zoeabel8819 8 лет назад

Thank you!!!!

@DougHExcel 3 года назад

Hi Zoe Abel, you're welcome!

@icantdothisagain 9 лет назад

thanks!

@maxwellzt 9 лет назад

Dear Doug: your video helped me a lot. Thanks. My scatter plot has different legends(e.g. It has 2 series), then the code cannot add labels, and give "Object variable or with block variable not set" error. Could you please help me on this? Thanks

@MsCuzCuz2011 7 лет назад

Awesome Thanks!!!!

@DougHExcel 7 лет назад

Glad you liked!

@gabortamasmozol1410 4 года назад

thanks man

@DougHExcel 4 года назад

You're Welcome!

@sweehubers2649 7 лет назад

Hi Doug - great presentation and thank you very much - this was very helpful. Recently we needed to edit/change the label value. So we changed the label name in the Excel column, but the graph did not automatically update to reflect the new value. How do we do this so it's automatic and we don't have to do the whole thing over?

@DougHExcel 7 лет назад

if you've got Excel 2013, you can actually put labels without any VBA. See video at ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-NnoFXSFBtIg.html If you don't you'll have to do it over again :-(

@thuyhainsworth4739 7 лет назад

Thanks so much! I couldn't do this over my class, but now "IT WORKS". Btw, Do I need to select the data that I want to create a Scatter-plot?

@DougHExcel 7 лет назад

Glad it worked for you. Yes, you'll need to select the data to fill out the chart, that can be done before or after you Insert>Chart

@RogerFong 5 лет назад

Voilà! Thanks!

@DougHExcel 5 лет назад

You're Welcome!

@SuatATAN 8 лет назад

Thanks

@SuatATAN 8 лет назад

Thanks again. Definitely useful video. It's intreseting gap of excel but by this video i've solved it.

@kindredsoul1973 8 лет назад

Excel 2013 - I am trying to show results of two measurements (y is measurement one, x is measurement 2), each unit of measure is minutes spent playing video games. The x data is what my observer recorded (number of minutes each child spent playing video games, 20 children total). Y data is what observer 2 recorded (in minutes for each child spent playing video games). So - my coders viewed video tapes of areas in which children spent time playing video games. [interrater reliability] - and I want to show this in a scatterplot in which the first set of data (y) and the second set of data (x) are on the same scatter chart - but I get to the part in your video in which you manually add the chart (7:22) and my spreadsheet does not appear the same - my chart styles look completely different. Do I need to customize or add something to my access tools? Thanks - sorry if this totally confuses you - I'm totally stuck on this!

@marinabouzon2524 9 лет назад

Thanks a lot Doug H! It saved my dissertation thesis graphs! FYI, the macro code I took from the link you give here didn't work. I used this one instead: Sub AttachLabelsToPoints() 'Dimension variables. Dim Counter As Integer, ChartName As String, xVals As String ' Disable screen updating while the subroutine is run. Application.ScreenUpdating = False 'Store the formula for the first series in "xVals". xVals = ActiveChart.SeriesCollection(1).Formula 'Extract the range for the data from xVals. xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _ Mid(Left(xVals, InStr(xVals, "!") - 1), 9))) xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1) Do While Left(xVals, 1) = "," xVals = Mid(xVals, 2) Loop 'Attach a label to each data point in the chart. For Counter = 1 To Range(xVals).Cells.Count ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _ True ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _ Range(xVals).Cells(Counter, 1).Offset(0, -1).Value Next Counter End Sub

@itrefresh 9 лет назад

Hi, thank you it worked for me this code aswell. Thank you too Doug H

@carlosgull1654 6 лет назад

It's work Very Well, thanks for you code!!!

@123canuckfan 9 лет назад

Great Video Doug. How can I retain the labels when I filter?

@123canuckfan 9 лет назад

***** Thanks Doug. What I was attempting to do was filter one or two items and only show those on the scatter plot. However, when I filter, it still displays the first value, with the information from the item that I selected. For example, if I filter to only display Item 2, it will plot (60,48) but still show Item 1 as the label. Hope this makes sense. I would really appreciate the help. Thanks in advance. :)

@piyushsood3576 9 лет назад

Hi Doug, Great video and it helped me a lot. The problem I am facing is that there are a lot of data points on the scatter plot which make data labels overlap and it gets difficult to identify. Is there a way to remove the data labels and only show them when we hover over the data point? Thanks!

@timbuxton8977 7 лет назад

Just click and drag any overlapping data points to move them apart. excel puts in a little data pointer for you.

@lepermessiah01 9 лет назад

Do you know if the code works with negative values? I tried doing it, but it does not seem to work

@melc8895 9 лет назад

This worked great except for one thing. When I ran the macro, it also plotted a point (0,0) for the Item column heading. How do I fix that?

@carlastake4170 8 лет назад

THANKS

@DougHExcel 3 года назад

Hi Carla Apodaca, you're welcome!

@The7thFold 3 года назад

Thanks this really helped. But my labels have very long names. So if I just want each label to have a separate symbol/colour in the graph and then have the labels to appear as legends at the side, how do I do that? I have 14 such labels.

@DougHExcel 3 года назад

maybe something like this ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-sNQxUTs6lJA.html can give an idea. It'll need to be combined with a change in the series name to reflect the label name.

@wuqingli9255 7 лет назад

Thanks, it is really helpful! How to implement in power point?

@DougHExcel 7 лет назад

Thanks Wuqing Li! You can embed this as an object in PowerPoint or copy paste as an image.

@eduardocalderondelrivero7460 6 лет назад

gracias tigre

@DougHExcel 6 лет назад

De nada!

@esa2236 7 лет назад

You can right click a point on the scatter plot and click on "add data labels" then manually change the label of each point on the graph.

@DougHExcel 3 года назад

Hi Skid, thanks for the comment!

@richardrawlings3733 9 лет назад

Hi Doug - please could you post the code here, as the link no longer works. Thanks!

@arunswaminathan7375 5 лет назад

I have a scatter plot with 3 sets of data (series 1, series 2, series 3) but with the same set of data labels (the 'Items' column in your example). Is there a macro code for this kind of a problem?

@DougHExcel 5 лет назад

if you have Excel 2013 or later, you don't need the macro anymore. See ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-NnoFXSFBtIg.html