Skip to main content
It looks like you're using Internet Explorer 11 or older. This website works best with modern browsers such as the latest versions of Chrome, Firefox, Safari, and Edge. If you continue with this browser, you may see unexpected results.

Loyola University Chicago Libraries

Spreadsheet Therapy: Defining columns

Principles of "data tidiness" to make spreadsheets easier to manage, use, and understand.

About Columns

Columns are the basis for recording your data and experimental observations. Each column should correspond to a single property, measurement, or "variable." Each row designates a single observation, entity, or instance, for which the data are recorded.

Many spreadsheet manipulations and analyses, such as sorting, looking up, and counting, are performed on columns. These can go more smoothly if you limit the content of each column to a single piece/type of information use names that are easily read by machines.

Using Columns

  • Each column should...
    • Correspond to a single "variable"
      • A thing you might measure, or that can change from measure to measure, instance to instance
  • Contain only a single "type" of data
    • Separate text from numbers
      • Instead of "4 pm", use "16:00", or separate columns for "4" and "pm"
      • Instead of putting units in the same cell as a numeric measurement, try...
        • Placing in a separate column
        • Making part of column header
        • Identifying in a separate file containing metadata
    • Don't put notes about your data in the same column as the data itself. Instead, try...
      • Defining a separate column for notes and callouts
      • Putting notes in a separate metadata file

Handling Dates

  • When you can, avoid using Excel's built-in Date format
    • Excel files from different regions may index dates differently, leading to inaccuracies when importing/exporting data
    • However, you may prefer the Date format if you expect to perform calculations on dates, such as
      • Calculating the number of days or weekdays between two dates
      • Automatically referencing the current date
  • Alternatives to the Date format
    • Store dates as plain text
    • Store dates in three columns for month, day, and year
      • Month Day Year or Month Day Year
        02 25 2020   February 25 2020
  • Whichever you choose, set column formats to text, numbers, or dates as appropriate before you begin recording data

Naming Columns

  • Keep column headers short and descriptive
    • A separate metadata file can give more detailed descriptions of what each column contains
  • Avoid spaces, symbols, and other special characters
    • These may not be read consistently by data processing tools
  • Feel free to use underscores and descriptive capitalization to improve readability. Some examples might look like:
    • myBestVariableName
    • cover_width_cm
  • Avoid starting with a number
    • This will confuse some analysis tools
    • try twiceReturnRate rather than 2xReturnRate