Publication Quality Bar Graph in Excel for Research
Okay so I'm gonna show you now how to take this bar graph that we made before and I have a video on how to make this. I'll put the link to that video right here so I'll put a link to that in the in the description below but I'm going to show you how to make this graph look a lot closer to what you would see in a scientific publication specifically in my area which is in. Physiology so right now you look at this and it looks like a fairly generic graph that was made in. Excel there's a lot of things that Excel does that researchers who published tend to not do or they do in slightly different ways or Excel does. Oh it doesn't do some things that publications would require and so. I'm going to show try to show you how to best get something from Excel to something that would be adequate for publication purposes all right so first thing that I would do is I would add some sort of error bars to this so error bars. Tell us something about the variability of the data and it's something that some people use standard deviation but more often in my field at least it's going to be standard error of the mean so. I want to quickly calculate standard error with mean for for this average to average supine heart rate which is 60 in the average standing heart rate which is 67 so. I'm just gonna move this t-test result out of the way for now so I'm just dragging it down and then let's do let's put new standard deviations and let's do sample size so n value so in order to do standard deviation you can just do. Excel has a function for that so you do equal standard deviation or start typing it out and you're gonna see the second one once you get stn you're going to see standard. Eve for population and standard. If for first sample we are not doing population. Statistics here so we should be doing. Sam so I'm gonna double click this and then I'm going to highlight the data that we want. So this is under the supine heading here. So let's do all the supine data and you can either hits and pregnancy and then enter or Excel knows that if you don't do that in parentheses that just means you're essentially being lazy but it will put it in for you so I hit enter and then here is our standard deviation using the sample the sample equation for our supine group now.
Let's do the same thing for standing so equal. Start typing it. Double-click the standard EV. For the sample which is the dot s highlight all the standing data entered ok so here our standard deviations now. What we need to do is figure out how many people are in each of these so they should be the same but we'll do it independently anyways so an easy way to do that so we're gonna do another basic. Excel function here so type in the equal sign and start typing count and so you can see this description here of the standard count equation. It counts the number of cells in a range that contain numbers and because all these are numbers so all of our data is numbers that will tell us how many numbers we have. So let's go ahead and use that so if we can just double click that or we could have just did the open parenthesis and highlight all the data in this column enter. So that tells us we have 24 people 24 people's worth of data and that's true if you look at our participant. ID it goes from one counting every number up to 24 and we can do the same thing for the standing column so an easy way if you have the same exact formula if is you can just sort of click on this little box in the bottom of the sort of outlined it cell and just drag it over and you can see that it did a relative referencing which means it moved our referencing over 101 column because we've dragged of the formula over one column so now we have everything we need in order to calculate standard error of me unfortunately to the best of my knowledge there is no centered error mean calculation in. Excel I wish there works. It's really not that difficult of a calculation. I'm going to drag this down some more so standard error mean usually breathe visit SEM.
So let's put that in and now what we need to do is we need to do a custom formulas again. Excel does not have this formula already programmed in the way it did for the other ones so type in the equal sign and then we are going to take the standard deviation and we are going to divide that by the square root of our sample size so our n size so it where you're going to use the equation that Excel does have pre-programmed in for doing square roots. So if you start typing in square all right right there the first thing it pops up so sqr RT so square roots. Tell that we want the square root of this n value do the end parenthesis and hit enter so our standard error the mean for our supine data is 1.96 and again. I'm just gonna copy this over for to save some time so now we have our standard errors for our two averages for supine standing blood soup and standing heart rate notice that they are not the same because it is standard errors calculated from the data within a category or within a condition so Excel does not make standard errors very easy to do. It's not that hard but it's not as intuitive as a lot of the other things that it does so if you click on our chart here it's going to open up this chart tools tab that wouldn't have been there otherwise so if you go to design within that and go to you add chart element so go down. You'll see something that says error bars and we'll just go ahead and hit this more options because we're gonna have to go into that anyways and so what it's going to do is throw up some random error bars here. So that's what these black lines are and it's just whatever number. I guess it looks like it's two that they put into that so that is not our error so just putting up. That is not what we need for the again. We need one point nine six and two point six one as our error bars for supine and then standing heart rate so what we can do is we can hit this custom bubble and then hit specified values. And it's going to ask us what we want the positive error and the negative error to be so by positive their meaning.
If you can look in here there's a there's a error bar that goes upward and then there's an error bar that goes downward so above and below the mean for us it's the same. The positive value and the negative value are the same. So we're just put in the same data for both however again it's different for the two conditions so if we click this little box here it's going to want us to highlight our data so let's go and highlight both of these at the same time. It's important that you do go at the same time. And then do the same thing for the negative highlight both the same time and hit so now. You're gonna notice that. These bars are slightly different from one another because it's now showing the standard error that we calculated for each one individually all right so we can do a lot of other things here who pretty this graph up as well. Let's keep going with the standard error bars. Some people like having a what they call an in. Excel having both directions for the error bar so it going up and down if we wanted we can just make it one or the other so let's make it just up for a second so I hit plus and see that it got rid of the error bar going below the mean you can do - which goes just below I'm gonna go ahead and keep both because I think that's a little more typical in my area of science you can also get rid of her change the cap which means you had this a bar like this so it gets rid of the sort of T going across the top of the bar again. I think having the cap looks a little nicer and it's probable more common but some people do make error bars without the cap so I just wanted to quickly show you that. Now let's go ahead and get out of error bars for a moment and excuse me and then let's change the color of these bars so most the time you're not going to see people publishing graphs in color and the reason for that is most journals are going to charge you if you publish a graph in color and it's not a small fee and so it's kind of unnecessary to have this color in there so most people are going to want to make it something on along the grayscale so white black or gray essentially another reason for getting rid of the color is a lot of people do still print documents they don't read them digitally and most people at work don't have access to color printers just because most work places are trying to be cheap and so they they gave everybody just black ink so if you were to print something in color you wouldn't be able to distinguish the colors anyways so the colors are pretty much unnecessary so let's go ahead and change that though so click on the bar itself there's this button here that says change colors and let's just pick something great for now yeah that's probably fine.
I think if it was if I got the perfect so I don't particularly love that so let's actually change that up. Let's do no fill so we have a whole have area no fill so we're gonna have white bars and then let's do a nice solid lines here. So let's do black for the lines. And then let's increase the width here so 0.75 pretty skinny lines. So let's make it a point - looks pretty good and let's see if it'll let us change the error bars it will so that's great because those error bars also look a little skinny for me so it's also point zero. Oh 0.75 so let's increase that to 2 as well there we go and so we're getting a little closer here. We can also make just to distinguish between the bars a little easier. Let's maybe make them different colors. Let's see if it'll let me through. That should yes so. I just have one bar highlighted instead of both of them this time and one less. Let's make this one light grey and then the other one is white all right so that's or getting a little closer now. Most the time in sort of educational setting you would want to have a graph title. Most students as well as teachers enjoy seeing the graph title. Let you know what you're looking at quickly but in a scientific publication this the graph is going to be referred from the text.
There's also going to be a description below the graph that tells you what it is so. The graph title is redundant at that point. So we don't really need it so let's just click it and hit delete so that it gets rid of that and gives us a little more space for the bars themselves another thing. I would do is get rid of these horizontal bars. Especially since we don't have a fill here for this bar. We could have made it a white fill and that wouldn't show the these horizontal bars through it so it doesn't really matter because we're just gonna click these so make sure that you have them highlighted like it shows now with these circles and the hit delete all right so getting much much closer. Let's try to change up some of these these titles the everything in. Excel in my opinion is a little too too thin we need to increase the thickness so that they print well and so they stand out from the page a little bit so I'm going to increase right now increasing the thickness of the century of the the the bottom of the bars. This make it office so you can see. I'm talking about this bar right here that goes across below the the bars so increase the thickness of that it also has it as it is a strange gray color for some reason again since it's going to be printed in black and white black which. I just changed it to is going to print an awful lot better than that gray bar so let's do the same kind of thing with the y-axis so I'm going to click the y-axis and let's see if we can change so I guess for the numbers it has the e the controls up here so let's make its. I don't want that change the letters to again to make them black instead of that gray that it chooses by default and let's do go bold so that looks pretty good. Let's do the same thing for all the titles so make everything black bold it and then these titles down here so black and bold alright so now at this point I would say that's a pretty good graph.
It would be nice if we could expand the width of these bars. Just so we don't have all this white space. Let me see if it'll let me do that. So yeah gap gap width. So maybe this will do it for us. All right so that's going the wrong way. So let's just go the other way area much much better and now looking at this with the thicker bars these these titles both on the X and Y axis they just look a little too small firm for the bars so let's increase their size. 14 looks pretty good. Let's do the same thing for the y-axis label imports u n again so here. I would say at this point. We have a pretty good graph going not a big fan of the fact that it doesn't have a y-axis bar kind of out like it has this x-axis bar what we do need to put in here is something to tell us what the statistical values or with a statistical test told us so. I showed it in a previous video. I did a t-test and our our p-value is below point zero five so there is a statistical difference between the supine and standing heart rates of this data. So let's add to this an asterisk so that. Asterix is the most commonly used symbol to show significance so I just added. I went to insert and I added a text box so in that text box. I'm just going to type a single asterisk and that's it it needs to be again a little big and bold so it prints well so let's do through something like 18 that looks good and let's change it to bold and so oops what we typically do is we put it up above the error bar just like that is close to centers you can get it and what that tells the reader and you would describe this below the the below the figure but it tells the reader that this bar is different from this bar so again the staining heart rate is significantly greater than the supine the sort of control condition for this set of data and so at this point what. I would typically do is try to export this in some very high quality format so it. It's kind of one of the drawbacks of using the Microsoft packages so Excel PowerPoint any of those to make graphs is they don't export that well which is the main reason why most people in research do not use Microsoft Office PAC or Microsoft Office software in order to do their graphs.
Well there is a workaround to this. I'm going to put a description or a link to this in the description below I might even copy and paste some of those instructions. It is definitely a workaround. It's not an ideal situation but it will allow you to get more dots per square inch for your exported image which will take this from being something very grainy. Once it gets to a finished. Product is something that looks a lot more crisp and clear and it's a lot more like what you would get from my professional professional grade graphing program like what a lot of researchers use but. I hope that you enjoyed this in that. You got something out of this and now that you can potentially go and make graphs that look just like publication quality grass.
Let's do the same thing for standing so equal. Start typing it. Double-click the standard EV. For the sample which is the dot s highlight all the standing data entered ok so here our standard deviations now. What we need to do is figure out how many people are in each of these so they should be the same but we'll do it independently anyways so an easy way to do that so we're gonna do another basic. Excel function here so type in the equal sign and start typing count and so you can see this description here of the standard count equation. It counts the number of cells in a range that contain numbers and because all these are numbers so all of our data is numbers that will tell us how many numbers we have. So let's go ahead and use that so if we can just double click that or we could have just did the open parenthesis and highlight all the data in this column enter. So that tells us we have 24 people 24 people's worth of data and that's true if you look at our participant. ID it goes from one counting every number up to 24 and we can do the same thing for the standing column so an easy way if you have the same exact formula if is you can just sort of click on this little box in the bottom of the sort of outlined it cell and just drag it over and you can see that it did a relative referencing which means it moved our referencing over 101 column because we've dragged of the formula over one column so now we have everything we need in order to calculate standard error of me unfortunately to the best of my knowledge there is no centered error mean calculation in. Excel I wish there works. It's really not that difficult of a calculation. I'm going to drag this down some more so standard error mean usually breathe visit SEM.
So let's put that in and now what we need to do is we need to do a custom formulas again. Excel does not have this formula already programmed in the way it did for the other ones so type in the equal sign and then we are going to take the standard deviation and we are going to divide that by the square root of our sample size so our n size so it where you're going to use the equation that Excel does have pre-programmed in for doing square roots. So if you start typing in square all right right there the first thing it pops up so sqr RT so square roots. Tell that we want the square root of this n value do the end parenthesis and hit enter so our standard error the mean for our supine data is 1.96 and again. I'm just gonna copy this over for to save some time so now we have our standard errors for our two averages for supine standing blood soup and standing heart rate notice that they are not the same because it is standard errors calculated from the data within a category or within a condition so Excel does not make standard errors very easy to do. It's not that hard but it's not as intuitive as a lot of the other things that it does so if you click on our chart here it's going to open up this chart tools tab that wouldn't have been there otherwise so if you go to design within that and go to you add chart element so go down. You'll see something that says error bars and we'll just go ahead and hit this more options because we're gonna have to go into that anyways and so what it's going to do is throw up some random error bars here. So that's what these black lines are and it's just whatever number. I guess it looks like it's two that they put into that so that is not our error so just putting up. That is not what we need for the again. We need one point nine six and two point six one as our error bars for supine and then standing heart rate so what we can do is we can hit this custom bubble and then hit specified values. And it's going to ask us what we want the positive error and the negative error to be so by positive their meaning.
If you can look in here there's a there's a error bar that goes upward and then there's an error bar that goes downward so above and below the mean for us it's the same. The positive value and the negative value are the same. So we're just put in the same data for both however again it's different for the two conditions so if we click this little box here it's going to want us to highlight our data so let's go and highlight both of these at the same time. It's important that you do go at the same time. And then do the same thing for the negative highlight both the same time and hit so now. You're gonna notice that. These bars are slightly different from one another because it's now showing the standard error that we calculated for each one individually all right so we can do a lot of other things here who pretty this graph up as well. Let's keep going with the standard error bars. Some people like having a what they call an in. Excel having both directions for the error bar so it going up and down if we wanted we can just make it one or the other so let's make it just up for a second so I hit plus and see that it got rid of the error bar going below the mean you can do - which goes just below I'm gonna go ahead and keep both because I think that's a little more typical in my area of science you can also get rid of her change the cap which means you had this a bar like this so it gets rid of the sort of T going across the top of the bar again. I think having the cap looks a little nicer and it's probable more common but some people do make error bars without the cap so I just wanted to quickly show you that. Now let's go ahead and get out of error bars for a moment and excuse me and then let's change the color of these bars so most the time you're not going to see people publishing graphs in color and the reason for that is most journals are going to charge you if you publish a graph in color and it's not a small fee and so it's kind of unnecessary to have this color in there so most people are going to want to make it something on along the grayscale so white black or gray essentially another reason for getting rid of the color is a lot of people do still print documents they don't read them digitally and most people at work don't have access to color printers just because most work places are trying to be cheap and so they they gave everybody just black ink so if you were to print something in color you wouldn't be able to distinguish the colors anyways so the colors are pretty much unnecessary so let's go ahead and change that though so click on the bar itself there's this button here that says change colors and let's just pick something great for now yeah that's probably fine.
I think if it was if I got the perfect so I don't particularly love that so let's actually change that up. Let's do no fill so we have a whole have area no fill so we're gonna have white bars and then let's do a nice solid lines here. So let's do black for the lines. And then let's increase the width here so 0.75 pretty skinny lines. So let's make it a point - looks pretty good and let's see if it'll let us change the error bars it will so that's great because those error bars also look a little skinny for me so it's also point zero. Oh 0.75 so let's increase that to 2 as well there we go and so we're getting a little closer here. We can also make just to distinguish between the bars a little easier. Let's maybe make them different colors. Let's see if it'll let me through. That should yes so. I just have one bar highlighted instead of both of them this time and one less. Let's make this one light grey and then the other one is white all right so that's or getting a little closer now. Most the time in sort of educational setting you would want to have a graph title. Most students as well as teachers enjoy seeing the graph title. Let you know what you're looking at quickly but in a scientific publication this the graph is going to be referred from the text.
There's also going to be a description below the graph that tells you what it is so. The graph title is redundant at that point. So we don't really need it so let's just click it and hit delete so that it gets rid of that and gives us a little more space for the bars themselves another thing. I would do is get rid of these horizontal bars. Especially since we don't have a fill here for this bar. We could have made it a white fill and that wouldn't show the these horizontal bars through it so it doesn't really matter because we're just gonna click these so make sure that you have them highlighted like it shows now with these circles and the hit delete all right so getting much much closer. Let's try to change up some of these these titles the everything in. Excel in my opinion is a little too too thin we need to increase the thickness so that they print well and so they stand out from the page a little bit so I'm going to increase right now increasing the thickness of the century of the the the bottom of the bars. This make it office so you can see. I'm talking about this bar right here that goes across below the the bars so increase the thickness of that it also has it as it is a strange gray color for some reason again since it's going to be printed in black and white black which. I just changed it to is going to print an awful lot better than that gray bar so let's do the same kind of thing with the y-axis so I'm going to click the y-axis and let's see if we can change so I guess for the numbers it has the e the controls up here so let's make its. I don't want that change the letters to again to make them black instead of that gray that it chooses by default and let's do go bold so that looks pretty good. Let's do the same thing for all the titles so make everything black bold it and then these titles down here so black and bold alright so now at this point I would say that's a pretty good graph.
It would be nice if we could expand the width of these bars. Just so we don't have all this white space. Let me see if it'll let me do that. So yeah gap gap width. So maybe this will do it for us. All right so that's going the wrong way. So let's just go the other way area much much better and now looking at this with the thicker bars these these titles both on the X and Y axis they just look a little too small firm for the bars so let's increase their size. 14 looks pretty good. Let's do the same thing for the y-axis label imports u n again so here. I would say at this point. We have a pretty good graph going not a big fan of the fact that it doesn't have a y-axis bar kind of out like it has this x-axis bar what we do need to put in here is something to tell us what the statistical values or with a statistical test told us so. I showed it in a previous video. I did a t-test and our our p-value is below point zero five so there is a statistical difference between the supine and standing heart rates of this data. So let's add to this an asterisk so that. Asterix is the most commonly used symbol to show significance so I just added. I went to insert and I added a text box so in that text box. I'm just going to type a single asterisk and that's it it needs to be again a little big and bold so it prints well so let's do through something like 18 that looks good and let's change it to bold and so oops what we typically do is we put it up above the error bar just like that is close to centers you can get it and what that tells the reader and you would describe this below the the below the figure but it tells the reader that this bar is different from this bar so again the staining heart rate is significantly greater than the supine the sort of control condition for this set of data and so at this point what. I would typically do is try to export this in some very high quality format so it. It's kind of one of the drawbacks of using the Microsoft packages so Excel PowerPoint any of those to make graphs is they don't export that well which is the main reason why most people in research do not use Microsoft Office PAC or Microsoft Office software in order to do their graphs.
Well there is a workaround to this. I'm going to put a description or a link to this in the description below I might even copy and paste some of those instructions. It is definitely a workaround. It's not an ideal situation but it will allow you to get more dots per square inch for your exported image which will take this from being something very grainy. Once it gets to a finished. Product is something that looks a lot more crisp and clear and it's a lot more like what you would get from my professional professional grade graphing program like what a lot of researchers use but. I hope that you enjoyed this in that. You got something out of this and now that you can potentially go and make graphs that look just like publication quality grass.