When biology adds up, at last…

Excel -3- Figures and graphs

Creating a graph or a figure in MS Excel isn’t very complicated… as long as you have an idea of how the result should look like. Do you want to create a histogram? a bar graph? a pie chart? a scatter plot? Will you have one or several datasets to show in this graph? Will you need a secondary Y-axis? error bars? labels?

graph terms — Before we start discussing details about creating graphs in MS Excel, it is best that we agree on a few terms and concepts. What you will learn to create in this section is an embedded graph or chart. “Embedded” means that the chart appears next to your data in your […]

1. Familiarize yourself with a few terms.

in Excel -3- Figures and graphs / MS Excel 2010

table — As you will soon discover, the tools that MS Excel offers in order to let you build a chart work best when your data are “packed” in tables, rows and columns. Anyway, it is always good practice to organize your dataset so that data which depend on common variables (such […]

2. Organize your data/series in rows, columns or tables

in Excel -3- Figures and graphs / MS Excel 2010

Axis Labels — Since it is your first chart, we’ll take it easy and use a single dataset. Let’s use the previous example, concentrating on temperatures in Bergen between Sept. 19th and Sept. 27th 2015. The “easy way” consists in selecting the data which has to be contained in the graph: this means […]

3. Make your first line chart

in Excel -3- Figures and graphs / MS Excel 2010

Format — Now that the graph has been created, you might want to personalize its appearance by adding titles, changing colors, putting a background, and so on. Let’s start by adding colors to the background of the chart area and of the plot area. First, colors: right-click on the chart area, preferably […]

4. Add title, axis names, legend, colors…

in Excel -3- Figures and graphs / MS Excel 2010

Two lines — Now that you have managed to make your first line chart, you might want to make things a bit “spicier” by adding an extra line chart to the existing one (Yeah, I know… not really what you consider “spicy”). If you had previously opted for creating your graph using the […]

5. Add a series to an existing chart

in Excel -3- Figures and graphs / MS Excel 2010

choose style — In the example that we have just built here, we want to keep the data series which represents temperatures as a line chart and show the rain data as vertical bars. First we need to select the data series “rain” and call the option to change chart type. Many ways […]

6. Modify the chart style (lines, bars, columns…)

in Excel -3- Figures and graphs / MS Excel 2010

Format data series — Here, our goal is to keep using the left Y-axis for the temperature chart, while we set up a new (secondary) Y-axis to the right side of the graph that will be dedicated to the rain chart. Right-click directly on the rain data series in the plot area to open […]

7. Add a secondary Y-axis

in Excel -3- Figures and graphs / MS Excel 2010

line and bar graph — Let’s now take care of the fact that the temperature line is located quite high in the plot area. Most of the values are comprised between 12 and 16, but the chart using a range from 0 to 18 to display the chart. If we want to make better use […]

8. Ajust the range/units of the Y-axis

in Excel -3- Figures and graphs / MS Excel 2010

Trendline — MS Excel offers an option to draw a trendline on top of your data series. This can be quite helpful to show a trend when the data is a bit to scattered to draw conclusions or when you want to simplify the “look” of your chart. To do so, click […]

9. Add a trendline to a data series

in Excel -3- Figures and graphs / MS Excel 2010

error bars on graph — It is often useful or required to show the variability of your data when making a chart. This variability can easily be translated by error bars added along your plot line or bars. The bars may represent standard deviation, standard error of the mean, confidence intervals… you choose. Let’s take […]

10. Add error bars to a data series

in Excel -3- Figures and graphs / MS Excel 2010

pie chart — It is not so often that one gets to use a pie chart… and it is not so often that such a chart is used for the right purpose. Pie charts can be used to represent different categories in a population, election results, polls, data based on proportions and percentage. Let’s learn how […]

11. Make a pie chart

in Excel -3- Figures and graphs / MS Excel 2010

legend box — You may realize by quickly checking the pie chart that we just made previously that not all of the parties are represented in the legend. This is due to a default setting that MS Excel uses and which restricts the size/display of the legend box. To display all categories or […]

12. Resize the legend box

in Excel -3- Figures and graphs / MS Excel 2010

Extract a slice — If you wish to highlight a specific data series in a pie chart, you may want to “extract” the corresponding quadrants/slice. Click once on the pie to activate the plot area and click once more on the slice that you want to extract. Simply drag it away from the center […]

13. Extract a slice from the pie

in Excel -3- Figures and graphs / MS Excel 2010

format data labels — The pie chart that you’ve created previously and modified until now still looks a bit “naked” as there is no display of numerical data or scale. If you want to add the actual values from the dataset on top of the corresponding slices, select the pie by clicking on it, […]

14. Add labels to the pie chart.

in Excel -3- Figures and graphs / MS Excel 2010