{"id":320,"date":"2019-01-17T16:17:40","date_gmt":"2019-01-17T16:17:06","guid":{"rendered":"http:\/\/www.lib.uiowa.edu\/data\/?page_id=320"},"modified":"2026-02-16T23:33:54","modified_gmt":"2026-02-16T23:33:54","slug":"data-structure","status":"publish","type":"page","link":"http:\/\/www.lib.uiowa.edu\/data\/manage\/data-structure\/","title":{"rendered":"Spreadsheet Data Structure"},"content":{"rendered":"

Best Practices<\/a> | Helpful Tools\u00a0<\/a><\/strong><\/p>\n

Well-organized spreadsheets, sometimes referred to as \u201ctidy data,\u201d make data analysis and visualization significantly easier and save the researcher time. Use these best practices when setting up your spreadsheet and inputting your data and you will spend less time cleaning and re-organizing your data later.<\/p>\n

In addition to the recommendations on this page, creating a data dictionary can help as well. See our information about data dictionaries<\/a> for specifics.<\/p>\n

Each recommendation below is numbered. See corresponding examples of \u201cmessy data\u201d in the images that follow, as well as the \u201ctidy data\u201d example at the bottom of the page.<\/a><\/p>\n

\"\"Make your Spreadsheet a Rectangle<\/h3>\n

Use only simple rows and columns for data so the spreadsheet is a single rectangle. Don\u2019t have multiple tables on one spreadsheet and don’t add graphs, images, etc. on the same sheet. Instead use one table on each spreadsheet, with columns as variables and rows as observations.<\/p>\n

\"\"Put One Piece of Information in a Cell<\/h3>\n

Don\u2019t put more than one thing in a cell; it makes sorting and analysis difficult. Put each data point (even\u00a0 measurement units) in separate cells or provide the units in the column heading and offer a definition in your data dictionary<\/a>. If you need to make notes about data in a specific cell, create a \u201cnotes\u201d column in your spreadsheet.<\/p>\n

\"\"<\/p>\n

\"\"Be Consistent with Variables<\/h3>\n

When entering data, be consistent with how you enter variables into the spreadsheet. For instance, always use \u201cmale\u201d for male<\/em>, rather than sometimes using M<\/em>, m<\/em>, or Male<\/em>.<\/p>\n

\"\"Watch the Date Format<\/h3>\n

Use the date format YYYY-MM-DD (also known as ISO 8601 <\/i><\/a>) or use separate columns for Year, Month, and Day. This reduces confusion for an international audience and avoids problems with how Microsoft Excel treats dates. (More information about formatting dates in Excel can be found in the support documentation on their website. <\/i><\/a>)<\/p>\n

\"\"Fill in all Cells<\/h3>\n

There are different schools of thought on how to treat missing data (see Broman & Woo and Hook, et al articles linked at the bottom of the page for more info<\/a>). In general, best practice is to use a common code for missing data, such as a hyphen (–) or NA, to make clear the data is missing rather than unintentionally left blank. Don’t just leave the cell blank unless you define this in the data dictionary. Make a separate column for notes if you need to explain why data is missing.<\/p>\n

\"\"<\/p>\n

\"\"Avoid Special Characters and Extra Spaces<\/h3>\n

Special characters like @, *, and ^ mean specific things in programming languages, so using them in spreadsheets makes the data hard to work with later on. Similarly, spaces in column headings often require extra work when referencing them in programming scripts or code, so it’s best to use underscores to separate words (like_this) or use camel case (LikeThis).<\/p>\n

\"\"Don\u2019t Use Colors, Fonts, Italics, or Visuals as Data<\/h3>\n

Colors, fonts, italics, and visuals are either ignored by the computer or just confuse it. It doesn\u2019t know a bolded row is suspicious data, or a yellow row should be ignored. If you need to emphasize data to make it easier for a human reader, add another column with an indicator variable (e.g., \u201ctrusted\u201d with values of TRUE or FALSE.)<\/p>\n

\"\"<\/p>\n

Make Your Data Tidy<\/h3>\n

The image below shows best practices for spreadsheet data: it’s a single rectangle, there’s one piece of information in each cell, variables are entered consistently, date format is YYYY-MM-DD, missing data uses a common code, no special characters or extra spaces are used, and it doesn’t use color, font, or italics to communicate information.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

Helpful Tools<\/span><\/b>\u00a0<\/span><\/h3>\n

OpenRefine<\/span><\/b>\u00a0<\/span><\/h4>\n

OpenRefine<\/span> <\/i><\/a> is a free, open-source tool for Windows, Mac, and Linux you can use to review and clean up (tidy) spreadsheet data, split or combine data columns, and insert linked data from other sources. <\/span>\u00a0<\/span><\/p>\n

See the download and installation instructions here.<\/span> <\/i><\/a> \u00a0<\/span><\/p>\n

The Libraries provide individual, team, and class-based training on OpenRefine (request via\u00a0<\/span>lib-data@uiowa.edu<\/span><\/a>)<\/span>\u00a0<\/span><\/p>\n

REDCap<\/span><\/b>\u00a0<\/span><\/h4>\n

REDCap is a useful tool for creating and defining a data structure by basing it on a codebook or survey tool. Use REDCap for data entry for tabular data and surveys. It can be set up for HIPAA data and is frequently used for biomedical research.<\/span>\u00a0<\/span><\/p>\n

REDCap is supported by the Institute for Clinical and Translational Sciences (ICTS). Request an account <\/span>by emailing ICTS<\/span><\/a>.<\/span>\u00a0<\/span><\/p>\n

REDCap was created by Vanderbilt University. For more info, see their <\/span>introductory videos.<\/span> <\/i><\/a> \u00a0<\/span><\/p>\n

References<\/h3>\n

If you’d like more information on best practices for organizing spreadsheet data, the following resources may help:<\/p>\n