 Right a quick Excel tip on how to deal with outliers in Excel now. I do this in teaching labs a Lot, so let's have a look at what we've got here. I've got something that's a let's make this say It's a calibration graph. There's a concentration and absorbance We don't really care about units right now because we're not really talking about that I'm going to insert my scatter graph. So just a scatter graph fairly Standard I'm going to quickly tidy it up just to clear it We don't need those because we're copying it into a report. We're going to use figure caption I'm going to get rid of the grid lines. So usually get in the way Everything else is all right. And now I want to right-click add a trend line make sure it's linear add the equation and the R squared value Let's just stick that up there We could tidy that up a little later But that'd be from another video and what we can see is that value in the middle is an outlier It doesn't really fit on the line. Maybe it's dragging our line off in the wrong direction a little bit more So it's not very accurate. So what we do want to do ideally is get rid of it And we can see the R squared jumps up to 0.98. That's probably good enough But that's hidden a bit of data and that's not great You don't want to be hiding your data Because we could just keep that going we could actually hide that. Oh, this one doesn't fit anymore. Yay Now I've got a R squared of 999 And what I've done is just hidden data that doesn't fit the line. That's not not good practice So we're going to do it this in a way that we can get the right R squared value and the right trend line, but still show the data So I'm going to highlight the whole Graph here, so if you highlight the whole thing what you'll notice is that up here it highlights What data that graph is using are the purple being the x-axis the blue being the y-axis And if I find these little blue tags in the corner I can click on them and drag them to the right and that adds a new Data set to it now you can do that manually by right-clicking and going select data and adding And what you can see is it's added a new one We could keep adding more manually using that but I find it easy just drag this along and providing your data is Nice and coherent and table to properly Excel will do that if your data is disjointed and your x-axis and y-axis are in completely different locations It won't be smart enough to figure that out So now we've got a second data series. I'm going to take this cell here. That's got my point 35 It's clearly an outlier. It's actually less than the previous number. So that's probably not right and on the green line Hover over it you get the double headed arrows and just drag that to the right and There we have it. This is just the basic outline of it We've got in blue here the data that we want and the orange is the data that's been removed We're not going to include that but we are showing it, but it's not being used to calculate the R squared For instance now, I'm just going to tidy this up a little bit by Let's format that data series and come and have a look here And this is just a personal preference of mine. You don't need to do this I'm going to go to the marker options and I'm going to change this to an X So instead of the automatic circle, I'm going to change it to an X because it's X. It's crossed out Back into it. I'm going to go back to this paint bucket and the line The line to the marker options and the border Color and change it to a red color and that just says well, that's a data point But we've excluded it and I'll probably explain that in a figure caption copy and paste it into a report and underneath it will be figure 3 calibration graph the red X means I've excluded the data point and That would be how I would then go ahead and recommend that you present that works really nicely one problem is This will then break things like your line stats function. So if you're doing an array formula to do Line stats on it and you do known wise and known X's It stops working that to work there. That needs to be continuous data So what you could do to fix that is drag this down to the bottom all your outliers. You can stick On the bottom for instance And then Your data is then continuous contiguous at least and the line stats function will work because remember on a scatter graph It doesn't matter what order these data points come in. So if you've dragged and dropped your right ones That you don't want to include on the graph into that column Then you can reorder it and possibly even do it with a filter that might work And then you can run the line stats on them But that's a more That's actually more of a broader problem with using Excel when you're trying to do analysis and presentation The way that you analyze something and the way you present it are usually two completely different techniques so hopefully that's Covered a little bit how you might want to present something that has an outlier This I'm not going to go into stats about how you decide whether something's an outlier Usually most of these cases of we're just doing teaching like chemistry You'll just eyeball it off a trend line But it should be fairly obvious that That one you can exclude but you don't want to hide it and lie about it because you don't want to manipulate and lie about your data