1. Introduction

Collating and presenting data are important aspects of any academic discipline. Microsoft Excel is a powerful application that can help you to collate and present data accurately and appropriately. However, data collation and presentation depend on other aspects of your research, including:

• study design;
• data collection;
• data quality;
• sample size.

If you need guidance on these other aspects there are a number of resources on our website which will help, additionally you will need to seek guidance from your department.

The collation and presentation of data are part of the broader area of descriptive analysis. This is different from statistical analysis which is beyond the scope of this guide (for additional help on statistics see ‘statistics’ in the ‘Help with…’ section of our website). This guide focuses on the basics of using Microsoft Excel to collate and present data (for guidance on other aspects of Microsoft Excel see the resources available here).

1.1. Notation used throughout this document

Menu commands are written, e.g. File | Open, this means select the File option from the Menu Bar and then Open from the drop down menu. All key presses are included within < > e.g. <Enter> means press the Enter key. <Ctrl A> means hold down the Control key and press the A key.

1.2. Use of this document

This guide is to supplement face-to-face training but can also be used as a self-directed resource.

1.3. Sample files

This guide refers to a sample file that is required for some of the exercises. If you want to do the exercise you will need to download the sample file.

If you have any questions or comments about this documents please contact Stuart Johnson – sj88@le.ac.uk

2. Excel basics

For guidance on the basics of using Microsoft Excel see the online tutorials

3. Collating numerical data

Excel has a vast range of functions to collate numerical data. We are going to look at the most commonly used ones; sum, average, maximum and minimum.

3.1. Sum – addition

1. Open sample.xls.
2. Make sure you are in Sheet1.
3. Go to cell B8.
4. Click the AutoSum button on the Standard Toolbar (Figure 1), and then press <Enter>.

Figure 1: AutoSum

5. Click in cell B8 again and look in the formula bar – you should see the formula =SUM(B3:B7) (Figure 2).

Figure 2: Formula bar

3.2. Sum – other formulas

Addition is the simplest formula but Excel can also do many others. Simply use the appropriate symbols for subtraction (-), division (/) and multiplication (*) in the formula bar. Imagine in the sample file you wanted to subtract the yield for nutrient A from the total, you do this as follows:

1. Go to cell B9 (i.e. below where the total yield figure is).
2. Click on the AutoSum button (Figure 1).
3. In the formula bar click after B8 and type –B3 and press <Enter>.
4. If you click back in cell B9 the complete formula in the formula bar should read =SUM(B8-B3).

3.3. AutoFill

Often you need many formulas in a sheet. You can use the copy and paste feature to copy formulas, but it’s easier if you use AutoFill.

1. Go to Sheet2 of sample.xls – you will now see more information in columns C, D and E representing seed varieties 2, 3 and 4.
2. Type Total in cell A8, and then sum the yield of Seed variety 1 (the same way as you did in ‎3.1 above).
3. You then need to sum the yields of Seeds 2, 3 and 4 – do this by clicking in cell B8 and dragging the fill handle (the black square at the bottom right hand corner of cell B8 – see Figure 3) to the right to cell E8.

Figure 3: Fill handle

4. You will then have totals in cells B8 to E8 (Figure 4).

Figure 4: AutoFill 1

5. Now add a sum in cell F3 using the AutoSum feature (‎3.1 above) and then use the fill handle to put sums in cells F4 to F7 (Figure 5).

Figure 5: AutoFill 2

3.4. Average

1. Still in Sheet2 of sample.xls – type the word Average in cells A9 and G2 (Note – this doesn’t do anything – it just makes it clearer what formulas you are going to put in row 9 and column G).
2. Click in cell B9 then click on the chevron to the right of the AutoSum button and click on Average (Figure 6)

Figure 6: Average

3. Excel will default to averaging cells B3 to B8 – but you don’t want the average to include cell B8 because cell B8 is the total. You therefore need to amend the formula in the formula bar to stop at B7 (Figure 7).

Figure 7: Amending the cells included in the average calculation

4. Then press <Enter>.
5. You can now use the fill handle to put average formulas in cells C9 to E9 (Figure 8).

Figure 8: AutoFill 3

6. Do the same in column G to put averages in cells G3 to G7.

3.5. Max and Min

The MAX and MIN functions simply return the maximum or minimum value of a specified range.

1. Still in Sheet2 of sample.xls – type Max in cell A10 and H2 and MIN in cells A11 and I2.
2. Click in cell A10 then go to click on the chevron to the right of the AutoSum button and click on Max (Figure 9)

Figure 9: Max

3. Excel will default to choosing the Max from cells B3 to B9 – amend this to B3 to B7 (because you don’t want to include the total and the average in the Max).
4. Use the fill handle to put Max formulas in cells B10 to E10 (Figure 10).

Figure 10: AutoFill 4

5. Now put minimum values in cells B11 to E11 using the same method but choosing Min instead of Max from the AutoSum toolbar button.
6. Then add Max formulas to cells H3 to H7 and Min formulas to cells I3 to I7 (Figure 11). Don’t forget to amend the formulas so the range is finishes at column E rather than the default which will be column G.

Figure 11: Complete example

4. Collating text-based data

Often you will need to collate data that is made up of text rather than numbers, or numbers that represent text answers (e.g. (1=strongly agree, 4 = strongly disagree). Excel has a number of features to enable you to collate data in this format too.

4.1. AutoFilter

A useful feature to view your data is the Filter tool.

1. Go to Sheet3 of sample.xls.
2. Click on Data | Filter | AutoFilter (Figure 12).

Figure 12: AutoFilter 5

3. You will now see chevrons in each header row which you can click on to filter the data by a particular response. For example, in the Hair colour header you can select to filter your data by all your respondents with ginger hair (Figure 13).

Figure 13: Using AutoFilter

4. To un-filter the data simply click on the (All) option.
5. To turn off AutoFilter go to Data | Filter and select AutoFilter (which will unselect it).

4.2. Count If

To count the number of times specific text appears in a certain range use the Count If function.

1. Still on Sheet3 of sample.xls – go to cell B27 and click on Insert | Function (or click on the Insert Function button on the formula bar – Figure 14).

Figure 14: Insert Function

2. In the Search for Function box type in Count if then press <Enter> and click OK (Figure 15).

Figure 15: Finding the Count If function

3. Click on the button to the right of the Range field (Figure 16) and then select the range C2 to C26 by dragging the cursor.

Figure 16: Selecting the range

4. Then click on the button on the right of the Function Arguments box (Figure 17) to take you back to the original function arguments box.

Figure 17: Function Arguments

5. In Criteria type <10 and then press <Enter> (Figure 18). The formula in the formula bar should read =COUNTIF(C2:C26,"Under 10") and you should have 4 Under 10s.

Figure 18: Completing the function Argument

6. Do the same Count If functions for cells C28 to C31.
7. You can see the completed version, including the responses to the remaining questions) in Sheet4.

5. Presenting data in charts

5.1. Basic principles

You have a whole range of options to choose from when you get to the point of presenting your data. You can simply present the data in text form, or table form or as some kind of chart. For more information on each of these options see our workshop on Presenting quantitative data effectively or the study guide on Presenting numerical data. Assuming you have decided to use a chart of some kind (you need to be deliberate about this decision rather than just defaulting to it), Table 1 shows some information to help you choose between line graphs and bar charts.

Table 1: Line graph or bar chart – how to choose

When it comes to formatting line graphs or bar charts consider the principles summarised in Table 2.

Table 2: Line graph or bar chart formatting issues

5.2. Inserting charts

Bearing in mind the guidelines in Table 1, a bar chart would be most appropriate for the data represented in Sheet1 of sample.xls. To insert a bar char for the data on Sheet1, do the following.

1. Go to Sheet1 of sample.xls.
2. Highlight the data you want to form a chart from by dragging the cursor over the appropriate cells (i.e. A2 to B7 – Figure 19).

Figure 19: Highlighting appropriate cells to form a chart

3. Then go to Insert | Chart and follow the Chart Wizard Instructions (Figure 20).

Figure 20: Chart Wizard

4. Alternatively, once you have highlighted the appropriate cells, you can press the short cut key <F11> (which is much faster but doesn’t give you the edit options – but you can always come back to it later).
5. The default formatting will produce a chart like the one in Figure 21.

Figure 21: Default formatted chart

5.3. Modifying charts

A more appropriately formatted chart is shown in Figure 22. The following steps will show you how to modify the formatting.

Figure 22: More appropriately formatted chart

1. In sample.xls go to Chart1 (the one you just created in ‎5.2 above).
2. Right click on the chart and choose Chart Options (Figure 23), or, alternatively, go to Chart | Chart Options.

Figure 23: Chart Options

3. On the first tab (Titles) enter an appropriate title and also labels for the X and Y axes (Figure 24).

Figure 24: Titles

4. Leave the information in the Axes and Gridlines tabs unchanged.
5. In the Legend tab, uncheck the Show legend box (Figure 25).

Figure 25: Legend

6. Click OK.
7. Now right click the chart again and this time choose Format Plot Area (Figure 26).

Figure 26: Format Plot Area

8. Select the None in the Area section (Figure 27).

Figure 27: Area

9. Finally, to change the colours of the bars, double click a bar and then choose a colour.

5.4. Selecting non-adjacent ranges

Sometimes you will want to plot more than one set of data on a chart. To do this simply highlight the data you want to plot and insert a chart as in ‎5.2 above, or as follows.

1. Go to Sheet2 of sample.xls.
2. Select cells A2 to E7.
3. Press <F11>.
4. You will get a chart like the one in Figure 28. You can then modify the chart as described in ‎5.3 above.

Figure 28: Plotting multiple sets of data

Sometimes you will not want to chart all the data but only some. This is straightforward if you are plotting data ranges that are adjacent to each other in the data sheet, but what if they are not, e.g. if you wanted to plot nutrient A against nutrient E and miss out B, C and D? Here’s how.

1. Select cells A2 to E3.
2. Then hold down <Ctrl> and select cells A7 to E7 (as in Figure 29).

Figure 29: Selecting non-adjacent ranges

3. Now press <F11>. You should get a chart the same as represented in Figure 30.

Figure 30: Plotting non-adjacent ranges

5.5. Changing the series from columns to rows

By default (assuming you select adjacent ranges) Excel will plot data from the left hand column of the range along the X axis. Occasionally, however, you will want to change this. This is how you do it.

1. Right click on the chart you created in ‎5.4 above and select Source Data (Figure 31), or go to Chart | Source Data.

Figure 31: Source data

2. Then in the Source Data window change the radio selection button from Columns to Rows (Figure 32).
3. Click OK.

Figure 32: Columns to rows