Dr. Mark C Gardener

Open University Associate Lecturer: S206/SXF206 Environmental Science

GO...
Mark's OU Students page
Gardeners Own Home

Spreadsheets are important for all science subjects:

Holding data
Managing data
Data manipulation
Basic calculations
Data summary
Graphs

Using spreadsheets and graphs in science

Your spreadsheet is a really important tool and it is vitally important to the "business" of science. Scientists use spreadsheets for various purposes:

  • Holding data.
  • Managing and manipulating data.
  • Basic calculations (and not so basic ones).
  • Data overview and summary.
  • Making graphs and charts.

Using a spreadsheet is an important skill that will stand you in good stead for the future. Using a spreadsheet well can save you a lot of time and effort.

The following notes form the basis of a tutorial for S206/SXF206 a second-level module in Environmental Science from the Open University.


Table of Contents

Click on a link to go directly to that section

See the list of practice data files here.

Top

Table of Contents

Click on a link to go directly to that section. You can click the Top links in the sidebar to return to the top anytime.


Excel can read:
.XLS
.XLSX
.CSV

Other file types may be readable

Top

Part 1 Using a spreadsheet – basics

The first thing is to get some data to work with. You can of course type data directly into Excel but often you'll want to transfer data from somewhere else.

Data input

There are two main ways to get a file of data into Excel.

  • Open the file directly.
  • Copy and Paste text from one file to another.

The route you take depends somewhat on the actual format of the file. Some files can be read easily by Excel, others might need a bit of work.

Files Excel can read

Excel can read .xls of course since this is the native format. Later versions can also read .xlsx files, which are Microsoft XML style of spreadsheet file. Essentially Excel needs to know where to place columns and break rows. Some text files can be read as long as the text contains delimiters. These are characters that are treated as a "new column" instruction. The most common type is .csv files, which are comma separated values. Excel sees a comma and jumps to the next column. A regular line break goes to a new row.

Excel can read files separated by other characters (such as Tab) but the file extension may not give a clue as to the type of file. Plain .txt files may be comma, tab or space delimited. Or a .txt file may not be be split in any sensible way!

Excel Open File Menu Window
The Excel read file menu window.

In the Open file menu window you'll see that the files Excel thinks it can read have the Excel icon. Native files have the bar chart and grid. CSV files have the letter a and a comma.

If the file is a regualr Excel file or a CSV then it will generally just load up. If your file is not "standard" you have a couple of options.


Excel can handle "odd" file formats via the Text Import Wizard

Top

Reading non-standard files into Excel

You can open data files that are non-standard (i.e. not .xls or .csv) in two main ways:

  • Open the file from the Open menu.
  • Use Copy/Paste

In general opening the file from the Open menu is the simplest way to go, as the Import Wizard will open directly. However, there are times when Copy/Paste is useful – such as when you have the data open in a web browser for example.

Import Wizard

When you open a file that is formatted in a way Excel finds "challenging", it will start the Import Wizard. This allows you to split the file into columns in an appropriate manner. The opening screen of the Wizard allows you to preview the data and select the type of separation – a character or fixed space.

Import Wizard Menu Start
The Text Import Wizard allows you to handle non-standard files.

The first screen allows you to decide which row to start importing from and to select the most appropriate type of column delimiter. In the figure shown you can see some data from the Met Office. These data are separated by spaces, each column being different in width.

If you select Fixed Width as the option you will be able to "drag" the column separators into the best spot for each column. However, you can choose to "combine" delimiters so that multiple spaces are treated as one.

Text Import Wizard Menu 2
Setting consecutive delimiters as one is useful for space delimited files.

The final stage allows you to specify the format of each column. In most cases the General format is fine (this is the default). You can also choose columns to skip (and so not import). Once you are happy you can click the Finish button to complete the import.


Use Paste to get data from clipboard into Excel:

You can access the Text Import Wizard from the Paste button to split data into columns.

You can access the Text Import Wizard on existing data using the Data > Text to Columns button.

Top

Copy/Paste

Copy and Paste operations can be used to transfer data into Excel. This can be helpful if you've opened the data in a web browser for example and do not want to save the file first and then import.

Once you've highlighted the data you want you can use the Paste button in Excel. There are two main options:

  • Text Import Wizard
  • Paste

Paste Menu Button Options
The Paste button gives options. Use the import wizard or simply paste.

If you just paste you'll get the text as one big block, which you can split into columns afterwards. You can also select the Text Import Wizard and follow the processes described earlier. The Paste Special option is not helpful here, you'll see options to paste as Unicode or regular text (in most cases there is no difference).

If you choose to simply Paste then you will end up with one big block of text. The rows will be split okay but each of these rows will contain all the columns in one chunk. You will need to split the columns separately.

Pasted data appear in one block, columns will need to be separated
Data pasted from a plain text file will be in one block with un-separated columns.

See also some notes about copy and paste that I wrote for S216. The notes concern a particular assignment but are generally about using your spreadsheet in conjunction with a word processor.


Text to Column

If your data appear in one big chunk you'll need to use the Data > Text to Columns button. You need to highlight the data you want to split before hitting the button.

The Text To Columns button
Highlight data then use Data > Text to Columns to split into columns using the Text Import Wizard.

This will run the Text Import Wizard and allow you to define how to separate the data into columns in much the same way as you saw previously.


Download a practice file:

Practice data

Here is a file you can use to practice the import process: MetOffice Datafile. The file is a plain text file, the first few rows are notes. The main bulk of the file needs to be in 7 columns. The data show some weather data for a Met. station in North Devon.


Set column width to optimal by double-clicking a column boundary in the header.

If multiple columns are selected all widths can be set to optimal by double-clicking any column boundary in the header.

Top

Keyboard shortcuts

Excel can handle large datasets with many rows and columns. Using the mouse to highlight data or to scroll around can be quite tedious. There are various shortcuts that can help.

Column width

Altering the width of columns is helpful as you want to be able to see all the data in a cell. If the column is too narrow you'll generally see ##### instead of the data.

You can alter the column width by dragging the boundary in the header bar or right-clicking in the header bar (then selecting column width). A shortcut is to double-click the boundary between two columns – this will set the column to optimum column width.

If you highlight several columns by clicking across several column headings you can set all the columns to optimal width by double-clicking any of the column boundaries in the header bar.

You can set multiple columns to optimal width simeltaneously
Set multiple columns to optimal width by selecting
then double-clicking on any column boundary in the header bar.


Use Ctrl and Shift keys with Arrows and mouse to help move around and select blocks of data.

Top

Moving around and selecting

If you have many rows it is tedious to scroll to the bottom. The Control key can be used in combination with the arrows to move to the end of a block.

The cursor will actually end up at the last cell in the chosen direction before a blank.

Use Ctrl+Arrow to move to the end of data in any direction
Use Ctrl+Arrows to move to the end of data in any direction.

In the figure you'll see some rows are longer than others. Using Ctrl+Rt.Arrow will go to column F for row 700 but will go to column G for row 701. So, be careful if there are blanks in the dataset. If you are unsure, hit the key combination again (you can always use the opposite arrow to go back).

If you use the Shift key as well as Ctrl you will highlight all the cells between the starting point and the end.

Using the Ctrl key and a click of the mouse allows you to select non-contiguous blocks of cells.

Multiple non-contiguous cells can be selected using the control key and the mouse
Use the Ctrl key and the mouse to select non-contiguous blocks of data.

A quick way to jump to a certain cell is to type its reference in the name box on the left of the formula bar.


Use Ctrl+A to highlight all data

Top

Selecting rows/columns and everything

You can select an entire row or column by clickng the header bar. Click and drag to select several rows/columns. You can also use the Ctrl key to select non-adjacent cells.

The Ctrl+A key combination selects all the data in a block.


When entering data:

Use Enter to move down
Use Tab to move across
Use any arrow key

Top

Entering data

If you enter data into a cell you can move to an adjacent cell and so "fix" the entry in different ways:

  • Arrow keys – move in the direction of the arrow.
  • Enter – move down in the same column.
  • Tab – move across the same row.

The default is for Enter to move down but this can be changed in the program settings.


You can drag and drop cells to a new location.

Use the "fill handle" at the bottom right corner of a cell to copy/paste the contents.

Use Named ranges from the Formulas menu to make named cells, which can be easier to manage in formulas.

Make blank charts, that is charts without any data. You can then build the chart you want.

Top

Miscellaneous Hints

Here are a few miscellaneous tips that may prove useful.

Drag 'n' drop

You can move a cell or a block of highlighted cells using drag 'n' drop. Move the cursor to near any edge, the cursor will change to a four-headed arrow. You can then click and drag the block to a new spot.

You can do a similar thing when using copy/paste. Hover over the bottom right corner of a cell or a block of cells. The cursor should change to a thin cross (like a +). You can then drag down to across to copy and paste.

Cell formatting

You can right-click a cell or selection of cells to bring up a menu, including formatting options. However, it is easiest to use the Home menu on the Ribbon.

Cell formatting via the Home menu
Cell formatting options are easiest to access from the Home menu.

Named ranges

Sometimes it is useful to have cells with given names, rather than their cell references. You can name a single cell or a range of cells. The buttons to achieve this are on the Formulas Ribbon menu.

Naming cell ranges commands are on the Formulas ribbon menu
Access Named Range tools via the Formulas Ribbon menu.

For a single cell click the Define Name button.

To make names for entire colums do the following:

  1. Click once anywhere in the data.
  2. Use Ctrl+A to highlight all the data.
  3. Click Formulas > Create from Selection button.
  4. Choose to takes names from the top row
  5. Click Ok to finish.

You can now use the names in formulas instead of having to select the cells e.g. =CORREL(tmax, sun)

The Formulas > Name Manager button allows you to view and edit names.

You can also type a name directly into the reference box on the left of the formula bar, this usually holds the row/coumn reference of the currently selected cell. Highlight the cells you want to name then type the name into thereference box.

Starting charts/graphs

The chart buttons are on the Insert ribbon menu. However, Excel makes decisions about your data based on the cells you select. It can sometimes take some sorting out. For this reason it is better to start with a blank chart, which you can then build to your own requirements.

When you go to make a chart Excel searches where your cursor is to see if any data is nearby. If you already highlighted data it will choose that. If you did not select any data Excel will search around the cursor and pick up any occupied cells. So, click in a blank area that is not adjecent to any occupied cells. Then use the Insert menu and click the graph type you want. Excel will make a blank chart. You can then use the Chart Tools menu to add data and alter the formatting as you want.

Starting from a blank chart makes it easier for you to build the chart you want, rather than the chart Excel thinks you might need. See more about this in Part 3 Graphs (Starting a chart).


Use Excel like a database

Top

Use Excel like a database

Excel has some powerful functions that allow you to use your spreadsheet like a database. Sort and Filter tools are helpful. You can also arrange and rearrange your data and apply various summaries using the Pivot Table functions. See a bit more about Pivot Tables here.


Click the fx button in the formula bar to start the function wizard.

Start typing a function directly by using =

Functions can be edited afterwards by clicking in the formula bar.

Top

Part 2 Functions

Excel has a wide range of mathematical (and other) functions that can be utilized. These functions can be used to carry out basic maths, allowing you to use Excel like a calculator, or to work out more complicated things like summary statistics.

You can enter functions in two ways:

  • Function Wizard – accessed via the fx button in the formula bar.
  • Type = in the formula bar (or cell) to begin a formula.

Function wizard

You can access the function wizard by clicking on the fx button in the formula bar.

The function wizard button in the formula bar
Clicking the fx button opens the function wizard.

This allows you to see the available functions.

The Insert Function Wizard opening screen
The function wizard allows you to browse or search for functions.

Once you have found what you want you can click the name of the function and click OK to move on.

The wizaed guides data entry to the chosen function
The function wizard guides you through data entry.

The Wizard now guides you through the data entry process. You can use the mouse to select ranges of cells or simply type the cell references or named range into the boxes.

Direct entry

If you type = into a blank cell or into the formula bar you also start the function entry process. The names box on the left of the formual bar shows the most recently used functions, and allows you to open the function wizard. If you start typing you'll see a drop-down box appear with candidate function names. The more you type the shorter the list.

List of functions appears as you type in the formula bar
As you type a function in the formula bar a list of functions appears.

Once you see the function you want you can double-click it and it will appear in the formula bar, along with a "hint" about the parameters required.

Double-clicking a suggested function enters it and gives suggestions about the parameters
As you type a function Excel gives hints.

You can enter the cell ranges directly, use the mouse or type a name if you have named ranges.

Function parameters are separated by commas. In OpenOffice the functions are the same as in Excel but the semi-colon is used instead of a comma. You need to add a closing parenthesis but if you omit it Excel usually just adds it for you.

Editing functions

To edit a function you can either:

  • Click in the formula bar.
  • Double-click the cell.

Using the formula bar is probably slightly easier.

To alter the formula you can simply re-type the parts you want to alter. You can also drag selection outlines like so:

  1. Click on the cell containing the formula.
  2. Click in the formula bar.
  3. You will now see selection outlines for the elements in the main data.
  4. Click and drag a selection outline to move it and to change the function.

When a function is selected outlines appear around the parameters in the main data
If you select a formula you see selection outlines around the cell ranges.
The outlines can be dragged (or resized) to alter the function.


Fix cell references using $ to prevent changes during copy/paste operations.

Top

Copy, Paste and fixing references

Copy and Paste operations can be very useful to help you create formulae without having to re-type the entire thing. However, Excel may alter the cell references in undesireable ways. Look at the example above – the columns C and F are selected for a correlation. If you copied the cell from H5 into G5 you would end up with a correlation between columns B and E.

You can "fix" cell references by preceding the row or column reference with a $. You can also fix rows and columns.

If the formula in H5 was edited to read =CORREL($C2:$C705, F2:F705) then when the formula is copied to the left the C reference remains unchanged.


Graphs are called Charts by Excel

Different graphs are suitable for different purposes

Graphs are important as they convey information concisely

Top

Part 3 Graphs

Graphs are very important in science. A graph is able to convey a lot of information is a concise manner. When carrying out research you should always aim to produce a graph to summarise your results. Scientists have to assimilate a lot of information and graphs allow them to do this more easily than reading tables or text. Graphs are also important to convey information to non-specialists, such as researchers in other disciplines or the general public.

Types of graph

There are various sort of graph (Excel calls all graphs charts so I will use the terms interchangeably) and each has strengths and weaknesses. Different graphs are suitable for different purposes – there is usually one clear choice for any particular situation.

In addition to choosing the most appropriate graph you need to ensure that your graphic contains appropriate elements, these include titles and labels (see more in Elements of a graph or chart).


Bar Charts show the magnitude of a response variable across categories represented by the predictor (or grouping) variable.

Error bars can be added to show variability if required.

Top

Bar charts

A bar chart (or column chart) is suitable for showing the size of a variable in one or more discrete categories.

The example chart to the right shows annual total rainfall (in milimetres) for each of several years.

The height of the bars represents the measured variable, this is sometimes called the response variable (or dependent).

The bars are split into categories, in this case year, which is sometimes called the predictor variable (or independent or grouping).

You can add error bars to show the variability of data if required. In this example that is not appropriate as we have a total rainfall. If the bars showed average monthly rainfall then error bars would be appropriate. See more in Error bars.


A bar chart shows values in various categories
A bar chart shows values in categories.
Error bars can be added to show variability.

Line plots show the magnitude of a variable at equally spaced categories.

Line plots are especially useful for time-related data.

Top

Line Plots

A line plot shows the magnitude of a variable for one or more discrete categories. In this respect it is analogous to a bar chart.

However, the function of a line plot is to "follow" a variable from one category to another. The categories are often time periods and are equally spaced.

The example line plot to the right shows the total annual rainfall (for North Devon). The height of each point (the points are actually hidden) represents the response variable. Note that the y-axis does not start at zero.

The position along the x-axis shows the year (there are missing years) and therefore represents the predictor variable.


A Line plot shows the magnitude of a variable across discrete intervals
A line plot shows values in categories that are equally spaced.
The line "joins the dots" and makes it easier to see patterns.

Scatter plots show the relationship between two continuous numeric variables.

The y-axis shows the response (dependent) variable.

The x-axis shows the predictor (independent) variable.

Lines can be added (straight or curved) to join the dots for special purposes. Do not confuse with a line plot (where the x-axis has fixed intervals).

Top

Scatter plot

A scatter plot shows the relationship between two continuous variables. They can be confused with line plots, especially if you "join the dots". The difference is that the x-axis is a continuous variable and not split into discrete categories like the line plot.

The example on the right shows maximum monthly temperature (degrees C) on the y-axis and total monthly hours of sunshine on the x-axis.

The y-axis represents the response variable (aka dependent). The x-axis represents the predictor variable (aka independent).

In environmental science the reponse is often a biotic variable and the predictor is often an abiotic variable. However, as you can see here, this is not always the case.

It is not usual to join the dots. However, there are times when this is useful. For example:

  • A depth profile – temperature through the atmosphere or ocean for example. Perhaps the concentration of a nutrient in the soil.
  • A cross section – you can plot altitude against distance to get a cross section, showing hills and valleys along a route.

You can "join the dots" with sections of straight line or bend the lines to make a curve, Excel permits either option.


A scatter plot shows the link between two continuous variables
A scatter plot shows the relationship between two continuous variables.

Trendlines or lines of best-fit can be added to scatter plots to help visualise the relationship between the variables.

Top

Trendlines (lines of best fit)

In addition to simply joining the points you can add a line of best fit. Excel calls this a trendline.

This assumes a linear relationship (linear does not necessarily mean a straight line) according to the equation y = mx + c where m is the slope and c is the intercept.

You can get Excel to display the equation and also the R-squared value. This is the square of the correlation coefficient, which can be calculated using =CORREL or =PEARSON functions.

The closer the value to 1 the better the fit and the closer to the line the points will lie.

It is possible to get Excel to produce curved lines of best-fit using logarithmic or polynomial equations. See more in Add a trendline.


A line of best fit added to a scatter plot
A trendline (line of best fit) added to a scatter plot.
The line shows the methematical relationship between the variables
according to the linear y = mx + c equation.

Pie Charts show proportional data.

You can always present the data more effectively as a bar chart.

Top

Pie charts

Pie chats are used to display porportional data. You can display the relative magnitude of a single variable across several categories.

In the example chart to the right you can see total rainfall for a weather station in a single year. The slices of pie show the percentage of the total for each of the months.

In general pie charts are not favoured in science. The human eye is not good at angular data and you can always display the same data as a bar chart.

Excel gives lots of ways to customise pie charts and there are uses for them, especially when presenting to the general public.


A pie chart shows the proportions of elements in categories
A pie chart shows proportions.

Box-whisker plots show sample data. The plots show the variability of the samples in a concise manner.

Top

Box & Whisker plots (boxplot)

The box-whisker plot is analogous to a bar chart in that it shows the magnitude of a variable across one or more categories.

Boxplots are designed for sample data, where you have multiple measurements. The point shows the median (a kind of average showing the middle value). The box shows the inter-quartile range (IQR) and the whiskers show the max-min (i.e. the range).

The boxplot therefore allows you to get an impression of the variability of the samples.

In the example to the right you can see two samples of jawbones. The response variable is the mandible length in milimetres. The predictor variable is the sex, there are male and female samples (the species is the golden jackal).

Note that the y-axis does not start from zero.

Boxplots are useful to help visualise differences between samples, if the boxes do not overlap there is a good chance that they are significantly different.


A box-whisker plot is analogous ot a bar chart but presents more information
A boxplot shows the magnitude of variables in categories.
The box and whiskers show the variability.

Histograms are superficially like a bar chart. They show the frequency of data in a sample across a range of values.

Histograms are used to assess the data distribution (its shape).

The data distribution affects the kind of summary and analytical statistics that can be used.

Top

Histograms

A histogram looks superficially like a bar chart. Histograms show the fequency of items in a sample across the range.

Each bar is the number of replicates in the sample that fall into a range of values. These ranges are called bins. Although the bins form discrete chunks the ranges are continuous so the x-axis is a continuous variable.

Histograms are used to help visualise the distribution of samples.

Data samples can be normally distributed (aka gaussian or parametric), where the middle bar(s) are tallest and the outer ones get progressively smaller in a symmetrical manner.

If data are not normally distributed they are said to be non-parametric (or skewed).

The distribution of data samples is important because it affects the kind of summary statistics you can use and other analyses.


A histogram shows the frequency of a variable over a continuous range
A histogram shows the frequency of a variable.
The bars are discrete intervals but the x-axis is a continuous range.

Download practice files:

Top

Practice data

Here are a some files you can use for practice:

You can use the files to create a variety of graphs.


All graphs need certain elements to make them most useful. Excel defaults are generally not helpful for scientific graphs.

Graphs elements to consider:
Type of chart
Clutter
Axis labels
Axis scale
Legend
Colour
Points and markeers
Fonts
Size & shape

Top

Elements of a graph or chart

All graphs have a purpose and in general it is to convey certain information clearly and concisely. Once you’ve got a basic chart you’ll need to ensure that it contains certain elements before it is ready for sharing with the wider world. The elements you need to consider are:

  • Type – Make sure you have the most appropriate type of chart for the data and the message you want to convey. In general simple graphs work better than complicated ones, so avoid the 3D charts.
  • Clutter – Consider making more charts if one is too busy; the purpose of the chart is to aid understanding not add confusion.
  • Title – You can incorporate an informative title on the chart itself or use a caption underneath. You can add a caption in PowerPoint or Word if you need to. Generally it is best to use a caption so that the chart area itself is devoted to the data, rather than a title.
  • Axis labels – You should make sure that axes are labelled clearly so that the reader can see what is being represented. Don’t forget to include the units of measurement. Any values should be formatted sensibly so if your data should be to two decimal places then set that format for the axis label.
  • Axis scale – Try to rescale axes so that the data fill the plot area. It is not always appropriate to start axes from zero but do make sure that the axes are labelled clearly (see previous).
  • Legend – if your chart includes data from more than one series you need to differentiate the series. A legend is the most sensible way to do this. You don’t need a legend if there is only a single data series. If you do include a legend place it in what seems like the best place, don’t just rely on the default setting.
  • Colour – Colour is the simplest way to help readers differentiate between data series. However, consider your readers’ requirements; red and green may not be a good combination if you have readers with a colour vision deficiency. The target may be a printed document, which will end up as monochrome. It is better for you to make a chart that is monochrome rather than hope that the colours you used can be differentiated later. You can use patterned fills for bars and different markers for points.
  • Points and markers – Excel has a range of default markers. Sometimes these come out with shadows or 3D effects. These tend to obscure the patterns rather than help illuminate so consider using plain markers.
  • Fonts – Some of the Microsoft Office fonts are not easily recognized by other programs. This is usually only a problem if you are intending to make your chart as a PDF. In the long run you are better off using standard Arial or Times New Roman, boring but safe. You are after legibility and portability.
  • Size and shape – Excel charts have a generally rectangular shape. You can resize your chart to give the best effect, so if you have a column chart you may wish to have a tall chart rather than a wide one. A scatter plot might be better if it is nearly square.

Excel provides a wide range of tools to help you prepare and edit your graphs. The default settings are generally not helpful for scientific graphs so be prepared to spend a little time getting your charts right. The graph you prepare is often the pinnacle of your work and the element that will be most often viewed, so it is worth getting right!


To make a chart start from a blank. Click in an empty cell and make an empty chart.

You can now add data as you require.

The Chart Tools menus appear once you have selected a chart.

Top

Starting a chart

The usual way of creating a chart in Excel is to highlight your data then click the appropriate chart button from the Insert menu. However, the resulting graph often appears in a layout that is not what you wanted and you have to spend time editing the graph. A cleaner approach is to create an empty graph and add the data to it and build the graph you want from scratch. This is more like the "old" way of doing things when you had the "chart wizard".

  1. Click once in your spreadsheet to "set" the current cell. Make sure you click away from any data as Excel will "search" around the current cell and automatically select anything it finds.
  2. Click on the Insert tab to bring up the graph options.
  3. Click the button appropriate to the chart you want.
  4. A blank chart now appears; use the Select Data button to begin the process of building your chart.

The chaert tools menu appears when you have selected a chart
The Chart Tools menus appear once you have selected a chart.

If you don't see the button then make sure you click once on the blank chart then select the Home > Chart Tools > Design menu. You are now able to select the data you want and can ensure that the x and y axes are the right way around for example.


The Select Data button on the Chart Tools ribbon menu allows you to choose your data.

You can edit a data series and select which axis each variable is applied to.

Top

Filling and editing charts

So, you've used the Insert menu item to select a chart type and need to add some data. Make sure that you click once on the blank chart and select the Home > Chart Tools > Design menu. Click the Select Data button and you will see a menu window that will allow you to "build" a chart. If your graph is blank you'll be able to select data and if your graph already contains data you'll be able to add extra data series and edit/modify existing data.

The select data source dialogue window allows you to populate a chart
The Select Data Source dialogue window allows you to populate a graph.

Use the Add button on the left to choose the data. You'll be able to select:

  • A name for the series (this will appear in any legend).
  • The values for the y-axis.
  • Values for the x-axis.

The Edit Data Series menu allows you to choose the graph data
The Edit Series dialogue window allows you to select the data you want.

If your data are a scatter plot with x,y values then the values will appear in the box on the right once you return to the Select Data Source menu. If the data are categorical (or you do not select any), you can select the data using the Edit button on the right side of the Select Data Source menu box.


The Chart Tools menus appear when you select a chart.

The Layout menu allows selection and editing of most graph elements in Excel 2010.

In Excel 2013 the Add Chart Element button replaces the Layout menu.

Top

Edit your charts

Once you have a rudimentary chart/graph you will need to spend a bit of time editing and tweaking it to suit. If you click once on a chart (even a blank one) you will see the Chart Tools menus on the Ribbon. There are three menus in Excel 2010:

  • Design – mainly for adding data and alrtering the general style of the chart.
  • Layout – the most useful, provides a means to edit most chart elements.
  • Format – less useful, gives a few options for style and shape options.

In Excel 2013 there are only two menus: Design and Format. The Layout menu has been reduced to an Add Chart Element button, where you can access the appropriate tools to add and edit the chart elements you need.

The Chart Tools > Layout menu gives you the most useful set of tools:

The Chart Tools Layout menu allows editing of most chart elements
The Chart Tools > Layout menu in Excel 2010 give access to tools to allow editing of most chart elements.

In Excel 2013 the Layout menu is replaced by the Chart Tools > Design > Add Chart Element button:

The Add Chart Element button in Excel 2013
The Add Chart Element button replaces the Layout menu in Excel 2013.

From the Layout menu (or the Add Chart Element button) you can add axis titles for example, this is an important step:

The Axis Titles button on the Layout menu
The Axis Titles button allows you to add axis titles.

On the left of the Layout and Format menus is a section where you can select and then edit any of the chart elements. This is useful because it is not always easy to click on the exact element you want from the chart direct. You can of course try double-clicking and right-clicking on the chart elements but it is generally easier to use the Current Selection tools on the Ribbon.

You can select and then edit any cart element using the Current Selection section in the Ribbon
To the left of the Layout and Format menus is a Current Selection section.
This allows you to select and edit chart elements.


The Trendline button adds lines of best-fit to scatter plots.

Using the More Trendline Options selection allows finer control over the resulting line.

You can draw linear, logarithmic or polynomial lines.

You can display the equation.

You can display the R-squared value.

Top

Add a trendline

A line of best-fit (a trendline) can be a useful addition to a scatter plot. In general you should only add a trendline when you have reasonable evidence that the relationship between the two variables is linear and that they are normally distributed. If you have non-parametric data then you should not add a trendline – this would be the case when you had conducted a Spearman Rank Correlation test for example.

The Trendline button is what you need, this is on the Layout menu in Excel 2010 and under the Add Chart Element button of the Design menu in Excel 2013.

The trendline button adds a line of best fit
The Trendline button allows you to add a line of best-fit to a scatter plot.

A basic (straight) line can be added using the Linear Trendline option. If you click on More Trendline Options you get... more trendline options!

More trendline options
Using More Trendline Options allows you finer control over the resulting line.

The options at the bottom of the menu window are useful, you can display the equation and the R-squared value on the chart. You can also use a logarithmic or polynomial fit should that seem like a good idea (you can always change your mind).


Error bars can be added to most plot types.

The Error Bars button allows you to specify cells containg values for the error bars.

Typical statistics for error bars are:
Standard Deviation
Standard Error
Range
Inter-quartile Range

On a scatter plot you can specify error bars for either (or both) variable.

Top

Error bars

Error bars are very useful when you are displaying sample data. Each of your samples will consist of several values and your main plot will show the average (eiither mean or median).

The error bars give an idea of the sample variability – small bars indicate low variability with values clustered close to the average. Large bars would indicate high variability with values widely scattered around the average. You can add error bars using the Error Bars button on the Layout menu (or via the Add Chart Element button on Excel 2013).

The Error Barsd button adds error bars to any chart
The Error Bars button adds error bars to any chart.

You can use various mesures of variability to display as an error bar. However, none of the options visible from the Error Bars button function correctly! You will need to use the More Error Bars Options selection to proceed.

Formatting error bar options
Formatting error bars gives you control over the error bars.

The only way to get reliable error bars is to use the Custom option. This will allow you to choose the magnitude of the error bars by letting you select cells containing the error values.

You'll need to use spreadsheet functions on the original data to work out the magnitude beforehand. The commonly used errors are:

  • Standard deviation – you can determine this using the =STDEV function.
  • Standard error – this is Std. Deviation divied by the square root of the sample size.
  • Range – this is just the max and min values.
  • IQR – the inter-quartile range. You can work these values out using the =QUARTILE function.

You can add error bars to most kinds of chart, most commonly they are used in bar charts and box-whisker plots. However, you can also add them to scatter plots, where you can use error values for either or both variables – that is y-axis and x-axis.


Top

blogger counter
 

 
Mark's OU Students page
Tips & Tricks for Excel Gardeners Own Home