Using spreadsheet programs for data organisation

Formatting data tables in Spreadsheets

Figure 1

spreadsheet setup

Figure 2

multiple-info example

Figure 3

single-info example

Formatting problems

Figure 1

Screengrab of spreadsheet showing formatting errors - multiple tables in one sheet

Figure 2

formatting

Figure 3

good formatting

Dates as data

Figure 1

Many formats, many ambiguities

Figure 2

You can see that even though you wanted the year to be 2015 for all entries, your spreadsheet program interpreted two entries as 2017, the year the data was entered, not the year of the workshop. dates, exersize 1

Figure 3

So, so ambiguous, it's even confusing Excel

Figure 4

Kill that ambiguity before it bites you!

Basic quality assurance and control

Figure 1

Image of Data Validation button on Data tab

Figure 2

Image of Data Validation window

Figure 3

Image of Data Validation window for validating plot values

Figure 4

Image of error when trying to enter invalid data

Figure 5

Image of Error Alert tab

Figure 6

Image of Input Message tab

Figure 7

Image of drop-down menu

Figure 8

Figure of Sorting menu

Figure 9

Note how the odd values sort to the top. The cells containing “min” or “hour” are found towards the top. Larger values like 90, 60 and 15 also are sorted so you can evaluate them. This is a powerful way to check your data for outliers and odd values. Sorted data

Figure 10

Conditional Formatting

Exporting data from spreadsheets

Figure 1

Saving an Excel file to CSV

Figure 2

CR LF

Figure 3

no CR LF

Caveats of popular data and file formats

Figure 1

Issue with importing csv format