Graphing individual error bars on scatter plot in Google Sheets (new)
Vložit
- čas přidán 30. 06. 2024
- Google sheets has a new graphing interface. Many people have been frustrated by the limited options for adding error bars on scatter plots. Here I show a workaround that will allow you to put error bars with a different magnitude on each data point on your graph.
Update: To add a trendline to the same graph, go back to Data section of the chart editor, and select "add series" then select your mean values in a column, including the header 'mean' ie in my video, I'd select G1:G6 as series 6. It will just overlay another set of dots on top of your existing ones. Then Customize, Series, click the Apply To: dropdown menu and select the series that you just added, and you should be able to add a trendline to that series
Thank you! I'm currently writing my IB Biology HL IA and this is soooooo helpful!!!!
What the heck I'm writing mine now too!! lol how is this video helping us all out!? But mine is due tomorrow and it's like midnight so honestly I might give up
IB BIO HL as well, but it's the start of the year though and my teacher is reviewing statistics and data
I guess we all end up here eventually... currently writing my bio hl ia as well
here for Physics IA lol
ahahaha same i chocked when he said "ib"
It's 2023 and sadly this video is still very much needed. Thanks for the help!
There is a special place in heaven for this man.
Thank you.
Okay guys, let's be real. Who else is here because they put their data into Spreadsheets even though the lecturer recommended Excel, and you thought "Pfft it'll be fine they're practically identical except one is saved in the cloud", only to try and make your graph to find that you can't assign custom error values, and you just don't have the time or patience to copy your entire dataset into Excel you needed to emergency google search how to do this???
Happy you found it useful! Good luck to all the IB students out there, especially now that your IAs are extra important :)
Thank you! this video was a big help. Also, thanks to those in the comments who helped me figure out how to add a trendline.
Thanks so much for this, having to switch an Excel based stats class to online format because of COVID and many students don't have paid Excel access! This was super helpful and straight forward.
This is a nice workaround for five data points. But how would you deal with 1,000 data points? Or the need to have asymmetrical error bars, with a different + and - value? It's sad that Google hasn't done anything about this for years.
This is a great work around. Thanks so much for the video and the comment about adding a trendline (I had the same question!).
Thank you!! I am editing my Bio EE and this was soo helpful!
I was knocking my head against the wall until I found this video. Thank uuu!!!
I have been struggling to teach my IB biology kids how to do this for years and every new year brings new swear words (in my head, of course). I finally resorted to having them plot error bars in pencil on printed graphs. Thanks for helping us all out.
facts literally using this video for ib bio
aww that's so nice that you actually teach your kids things!! My teacher doesn't teach us anything and I have to watch this video and figure it out for myself. Oh and he also just told us today, the day before our IA is due, that we need to have std dev bars.... like okkkkkkk
@@MrCommentor2 lmao what up rushil
@@raysdesk LMAOO hello 😂😂😂 nm u
Thanks man!! Huge help, and clear explanation.
Still working in 2022. Note to anyone dealing with x and y values that have completely different scales (i.e. one is orders of magnitude larger or smaller): you have to manually go in and change the horizontal and vertical axis minimums and maximums in order to have a graph that actually functions.
This video just saved my life. Thank you.
Yes! AP Biology teachers thank you. I know how to do this in Excel, but have never figured out the individual error bars in Google Sheets.
This video was sent down from heaven from Jesus Christ himself. I was losing hope of ever completing my insignificant grade 12 biology lab report. After countless days and many awful tutorials, I finally found this incredible solution. This video cured me of depression.
This is why i love the internet. THANK YOU for this.
"You are an angel sent from above" said by my groupmate after i did the graph by watching this video. YOU are an angel sent from above :)
Thank you very much. Exactly what I was looking for.
Thank you so much! This was a life saver.
There was very helpful. Thank you so much!
I just found this video after having issue with excel and then discovering sheets didn't have a separate error bar system. TYSFM!
Saved my life, thank you
This helped me so much, thank you
Thank you! Super helpful!!
You're welcome! Glad you found it useful :)
Hahaha, I'm doing my IB Chemistry HL IA right now, and for me also, this was extremely helpful. Thanks for the clever trick.
I love this sooooo much
Google sheets has a transpose() function that automates the process of setting up the table, and as someone already noted, you can fill the table in a way that references the cells of the original column with the mean such that everything auto-updates. But it looks like you still have to manually enter the standard deviation values into the chart, so that won't auto-update. Also, shouldn't you be using the standard deviation of the mean (sigma/sqrt(N)) for the error bars?
thank you, grade saver 🙌🙌
Hi how do you add a trendline to the values after separating them into different series?
I was just wondering the same thing. It is extremely frustrating that this is the way you have to do things in Google Sheets. I would add an additional series containing all values and add the trendline to that, then hide the values (make the series transparent)
can you add a trend line along with the error bars because I cannot figure that out!
you are a lifesaver thank you
Thank you for the good work. Please how can i do it for multiple bar graph for different categories of observations.
The hero we didn't deserve but needed
susbscribed becasue this video was so so helpful. Helped a lot in my iA
What you want starts at 4:20
Nice
Thank you so much!!
Thank you, this makes my Physics SL lab so much more managable lol
I'm literally doing my IB Physics IA right now 😭
@@scarsmakestars good luck, I got a 7 lol, so this must be a good method lol
@@braedenlarson9122 did you create a line of best fit on your graph too? I'm using google sheets right now and I don't know how to create one when you have individual error bars. If you created a line of best fit, please tell me how you did it!!
@@scarsmakestars yeah you can do both, just do what the video says and make an extra series for the fit and then do individual series for each point
Nice tips! Thank you!
im currently trying to insert an equation for the trendline i just added (following the steps you told the other guy in the comments), but when i try to label the new data series (the one with the trendline), the option Label - Use equation is not available.
Is there something i can do about this?
You may need to turn on the legend first. Unfortunately though, it will then show a dot for each of the separate series that corresponds to each data point, which looks ugly and confusing. What I recommend to my students is to do that temporarily so that you can see the equation of the line, then turn it off, and instead just manually type the equation of the trendline either as subtitle text for the graph title, or you could screenshot the graph and then edit the image with any image-editing software to just add a text box with the equation wherever you want it. All very clunky solutions, but until Google supports it better its the best solution I've found :)
Ben Paulson Thanks a lot! I've never tried plotting these kind of charts, and so far, your videos helped me a lot! 😇
How can I add error bars for x values?
Thank you so much
smart workaround... not sure how useful it is in practice though considering the tediousness... especially with a data set comparing multiple variables. this also leaves you with a bunch of points which cannot be connected by a best fit line anymore. really unfortunate that they haven't implemented a way to do this as a standard feature...
Thanks!
wow this is so much work to do, I have 39 data points to do hahahaha
60 for me XD.
I'm laughing because otherwise I'd be crying.
@@isaakvandaalen3899 ah damn I'm looking at 501 data points for 5 different samples - honestly google sheets just doesn't work for this
Thank you, very useful :)
You're welcome! Glad you found it useful :)
How can I add a trendline to this kind of graph? Clicking trendline and applying to all series isnt working
How is this not a normal function in Sheets/excel etc.?
This is vital to any decent measurement.
edit: it is possible to do on excel, have the stdev in a column besides the values and use the personalized error option and pick the whole area/column.
Yeah just google sheets that didn't consider to add a feature like that.
Is this really the best way? Man this is painful ...
But, of course, thanks for the video!
how do you include error bars for both axis?
Hi, thanks for this video, it's really helpful, however, it was made in 2017 and I'm here in 2021 trying to do my IB Bio IA and the program has slightly changed. I could follow you up to the point where you selected each individual point to but the error bar. I could do this, but then it added the error bar to every single point, with the designated color to all the different points, so then when I added the individual error bars for each point, each point had 5 error bars! Any way you know how I can fix this, or another way to do it now? Thanks!
It’s sounds like you are applying the changes to all series, instead of editing the options for each “series” one at a time. Each series in this case represents each individual data point.
Thanks Bhruv
Thank you
Muchas gracias!!!
Ben I love you thankyou
I have two different values for mean, so I want two different lines but on the same graph. Does anyone know how to format that?
Does anyone know how to put error bars in both horizontal and vertical directions - to make a zone of uncertainty?
How do I add an error bar for the x axis
how do I add error bars in the x values??
Can we do separate error bars like this AND put a trendline connecting the points on the same graph? I can't find a way to do both, only one or the other...
Great question! Go back to Data section of the chart editor, a select "add series" then select your mean values in a column, including the header 'mean' ie in my video, I'd select G1:G6 as series 6. It will just overlay another set of dots on top of your existing ones. Then customize > series, click the Apply To: dropdown menu and select the series that you just added, and you should be able to add a trendline to that series.
Relevant part starts at around 4:16
Is there a way to do it when you have two data sets graphed?
Also looking for this answer.
is there a way to add a trend line?
This rocks, except then you cant add a trend line normally! what were they THINKING?!
figured this out! add one more column at the end with all of the values, and then it can create a trend line based on that set
What to do when stdev
Does anyone know how to add a trendline to this multiple series stdev error bar graph? On my google sheets it will not add it.
The answer below but it is so clunky.... google please fix this!!!!!!!!!!!!
Thanks so much, IA due in 3h lol
what about horizontal error bars??
you can't do them in google sheets
this did not work for me!! I was able to put in all the different values but they were applied to every graph??? idk how to differentiate them bc my excel does not look like this :(
maybe check that your data is listed as separate “series” and then as you edit each series also make sure that when adding standard deviation that you select “constant” rather than percent before typing in the desired std dev for each respective data point (which should each appear as its own series)
I've been using python libraries to plot stuff for the last 15 years. A new job forced me to learn how to use Google Sheets. When it comes to drawing plots, I miss python A LOT!
I was looking for drawing error bars in student's grade books. I can't do this manually with 40+ students per subject. Google sheets is quite cool, but the chart part is deeply shameful.
Why we do have to insert STD manually? I really don't understand why I cant mark a cells with STD, like in excel.
I totally agree! it is frustrating that google hasn’t built in this functionality like Excel has. This is certainly not an intuitive or simple but it does work if you can beat with how clunky it is.
So we can't have a trend line and individual error bars?
I’ve included a description of how to add a trendline in the video’s description :)
Love this workaround, though obviously I wish it wasn't necessary. One addition I would make, is make the new (staggered) data set that you chart by using "=$AA##" so that if you need to change the values in the original trials (like if you're repeating the experiment, and want to copy the same spreadsheet with new data), then you don't have to manually change it all again. You can just put in the new data and voila - all the stats/charts are automatically adjusted
why is the error bars +/- "1" and not another number at 9.06 minutes ?
This means +/- one of the standard deviation, whatever it was calculated to be. So it’s not a value, it’s like saying + 1x where x is the standard deviation, because the value varies for each data series. So adding and subtracting one times the value of the standard deviation. That range, the mean +/- 1 SD is where 68% of the individual values in the population are expected to fall, if the population is normally distributed. Error bars can convey a variety of statistics, Standard deviation (SD) standard error (SE), confidence interval (CI) etc so it’s necessary to tell the reader where you got the values for your error bars.
how do u put a trendline using this method?
Please read the video description, I’ve included instructions there :)
Ahhh help!! How can I get a trendline now???
You can have anoter series of data, with the same values, regularly noted in the spreadsheet. You then ask for trendline for this new data series. The sheet applies one trendline per series of data. You may want to make the dots with the same color as the other series.
Using this method, you may insert results of two (or more) different experiments on the same chart. The trick for having error bars used separatedly on each series of data. Of course, this is TEDIOUS, and as much as I love the google suit - in this case using excel is oh so much simpler...
Also, look 17 comments below, it was already suggested...
but how do you add a trendline on it all tho??
I’ve included a description of how to do it in the videos description :)
@@BenPaulson thanks man but i done graduated already, thanks tho :)
But how can I do this with a column chart?
It seems to work the same, but the bars are just super skinny.
@@B3G_CRY_ What about with two data sets?
This is from an older version of sheets but the method is the same: czcams.com/video/XeRZyNvU6JQ/video.html
ok but now u cannot do the trendline :((
But now I have a question about how to add a line of the best fit to this chart 🤣🤣
Please see the description of the video for an explanation of how to do it :)
@@fatemaghwich1629 Please see the video's description for an explanation of how to do this :)
Oh common, it's completely useless in real life. This is an example how to waste your time when you have something bigger that 5-by-5, that all. If you really need something like scatter plot with error bars, just use candles chart for that. For your row data (which have to be a flat table) build a pivot table and add a target column 2 times (as STD DEV, as AVG) -- it almost the same as the first part of the video, but without manual efforts on this step. There's only two columns you have to create manually -- MIN as avg minus std dev, and MAX as avg plus std dev. In candles chart use MIN-column for minimal values, MAX-column for maximal values, and finally use AVG-columns 2 times as opens and closes values. Profit
i hate google sheets
fix your mouse/trackpad, it's killing my ears
Shame google hasn't implemented an option to specify a range to look up error bars for different points on the same series. This may work for people who want to do a lot of work that is completely automatic in excel, but it's a workaround for poor functionality that doesn't have to be this bad.
is there any way to switch the axes around?