Conor Meehan

Data tidying and figure generation practice tasks in Excel

  • The tasks below are examples to practice data tidying and cleaning in excel and generating figures from example data

Suggested prerequisite(s)

Dataset

This task uses the DataHandlingAndPresentation.xlsx dataset

Task 1: Data tidying and cleaning (Table sheet in dataset)

You have done an experiment where you determined the following things:

  • The minimal inhibition concentration (MIC) of a drug
    • Tested range: 1-64ug/ml
  • The presence or absence of a plasmid using PCR
  • The presence of a mutation in a specific gene using PCR

However, the data is not in a tidy format (samples in rows, measurements in columns, 1 entry per cell) and the data has not been cleaned of errors.

You should:

  • Tidy the data into a single table using the data tidying principles outlined previously
  • Remove any obvious errors from the data (i.e. clean the data)
  • Create a proper caption for this table and an informative legend

You can find a sample answer sheet here.

Task 2: Choose and create appropriate figures for different data (Figure 1 and Figure 2 sheets in dataset)

You have done 2 experiments:

  • Figure 1: A comparison study to see whether an improvement in S. epidermidis morbidity figures occurs when devices infused with different substances are used compared with when traditional devices are used
  • Figure 2: Looking at the relationship between MIC and qPCR CT values for a resistance-associated gene in E. coli

You should:

  • Create 2 figures, one per study (see links below for guides)
  • Select the correct chart type for each set of results
  • Create the charts in excel with informative axes labels and accessible colour schemes
  • Insert a linear regression line, if appropriate (e.g. scatterplot)
  • Create a proper caption for each figure with informative legends

You can find a sample answer sheet here.

Online guides for making charts in excel

Scatterplot

https://www.youtube.com/watch?v=gBbGBrHTMrM&ab_channel=Vertex42
https://www.youtube.com/watch?v=kLROcLFzH8o&ab_channel=BurkeyAcademy
 

Linear regression and scatterplot

https://www.youtube.com/watch?v=L_a8Z0BVjyM&ab_channel=MonaSchraer
https://www.youtube.com/watch?v=Cltt47Ah3Q4&ab_channel=JalayerAcademy (detailed)
[https://www.youtube.com/watch?v=9wX1a1J4WOI&ab_channel=TopTipBio(https://www.youtube.com/watch?v=9wX1a1J4WOI&ab_channel=TopTipBio)] (very detailed, with analysis pack)
[https://toptipbio.com/linear-regression-excel/(https://toptipbio.com/linear-regression-excel/)] (step by step guide with last video)
 

Bar chart

https://www.youtube.com/watch?v=U5MY9m9mG2c&ab_channel=TopTipBio
Bar charts using Excel worksheet  

Box and whisker

https://www.youtube.com/watch?v=39lsUsJsc2c&ab_channel=TopTipBio
Box plots using Excel worksheet

Search