2.1 Excel Standards for Organizing and Cleaning Your Data – Intro to Excel 2016 Data Analysis

2.1 Excel Standards for Organizing and Cleaning Your Data

Organize Your Data

  • Leave at least one blank column and one blank row between a related data range and other data on the worksheet.
  • Avoid putting blank rows and columns within a range of data. In fact, you should avoid all empty spaces, unless you have a specific reason for using them.
  • Make sure that any hidden rows or columns are displayed before you make changes to a range of data. When rows and columns in a range are not displayed, data can be deleted inadvertently.

Format Your Data

  • When you want to separate labels from data, use cell borders — not blank rows or dashed lines — to insert lines below the labels. 
  • Avoid inserting spaces at the beginning or end of a cell to indent data. These extra spaces can affect sorting, searching, and the format that is applied to a cell. Use the Increase Indent icon if you must apply spacing rather then entering a series of spaces.
  • When you add new rows of data to the end of a data range, Excel extends consistent formatting and formulas. Three of the five preceding cells must use the same format.
  • You can turn a contiguous range of cells on your worksheet into an Excel table. Data that is defined by the table can be manipulated independently of data outside of the table, and you can use specific table features to quickly sort, filter, total, or calculate the data in the table.

Clean Your Data (see following page concerning tools mentioned below)

  • Perform a sort on your data; this shows empty rows that could be missed otherwise.
  • Look for any unnecessary empty columns, rows, cells, and delete them.
  • Check and remove any duplicate data (Remove Duplicates tool).
  • If there are multiple pieces of data in one column (I. E. City, State, Zip), remove each piece and place in a separate column (Text to Columns tool).
  • There are several functions that can be used to specifically clean your data.