Spreadsheet Navigation

When you’re staring at a spreadsheet for hours on end, a little organization can go a long way. To stay organized, I create new sheets, freeze panes, and insert filters.

Creating, Editing, and Protecting Sheets
Keep raw data and clean data in separate sheets, create new sheets, color-code your sheets, protect your sheets, and hide/unhide your sheets.

Freezing Panes
Freeze the top row(s) so that your variable names stay in view as you scroll up or down.

  • Select the row directly underneath the variable names (the fourth row in this dataset) by clicking on the 4 on the far left of your screen.
  • Click on the View tab.
  • Go to the Freeze Panes icon and select the first option, Freeze Panes.
  • Now, try scrolling up and down. The first three rows will stay in view.

Filtering Data
Use filters to quickly sort your data.

Formatting Numbers
Convert numbers with decimal places into percentages and adjust how many decimal places are shown.

Formatting Text: Lower, Upper, and Proper
Use =lower to transform uppercase letters to lowercase letters, use =upper to transform lowercase letters into uppercase letters, and use =proper for proper names.

Formatting Names: Proper and &
Combine =proper and the & operator to combine and format text from different cells, like first and last names.