Library Guides: Tools for Using Spreadsheets: Best Practices

Keeping the Data Organized

There are many ways to arrange or design your spreadsheets. These are a few suggested best practices to help keep your spreadsheets functional.

Each row is an "observation" at a consistent level (e.g., no totals mixed in).
Each column represents a variable - don't store more than one piece of information in one cell.
Only one table per sheet.
Blank cells indicate missing values, zeroes indicate observed zeroes.
Comments or notes should be integrated in a separate column or stored separately.
Keep in mind that color or other cell formatting may not transfer into a different program.
Excel's Data Validation feature (found under the Data tab) helps improve data quality with standardizing data entry.

Spreadsheet Functions

Excel offers a wide variety of formula options. After you enter a formula, the cell containing your formula will display the calculated results.

Functions in Excel are formulas where each function performs a specific calculation. Commonly used functions:

=SUM()
=AVERAGE()
=MEDIAN()

Sort and Filter Data

Sort and Filter functions can be found under the Data tab.

To sort your data by a specific column: Click a cell in your table > Data > Sort (make sure to check the box by "My Table has Headers") > In the Sort box select the column you'd like to sort by > Click "OK". You can sort by more than one field by clicking "Add a Level" and select another category.

To filter your data, click any cell in your data > click the "Filter" (the funnel symbol under the Data tab). This will add little arrows next to the column titles which will allow you to filter the fields by the titles.