10 Best Practices for SHARED Spreadsheets

Jeff -

Resources

  • Download my FREE shared spreadsheet template here

Watch it in action

Tip #1 - Add Explanation to Every Tab

Think—if a new hire were to join today and open the spreadsheet, would they be able to understand what’s going on? 🤔

It’s very useful to include 1-2 sentences on the top left hand corner explaining what each tab contains. This could also be used if you need to add instructions that other people need to input information.

Tip #2 - Make Deadlines Obvious

If there’s a deadline for when action needs to be taken, make it LARGE and OBVIOUS.

The sales team can never use the excuse "Oh I didn't know" again 😉

If you want to make sure someone lands on a specific tab, make sure to copy the URL of that specific tab.

💡
Pro Tip: CMD+C (or CTRL+C for Windows) to copy the URL and CMD+K (or CTRL+K for Windows) to hyperlink it directly without opening up a new window

Tip #4 - Use Descriptions below Header Rows

It’s helpful to include a row below your header that explains what should be filled in. I like to call this the example row, or the “helper row”.

Tip #5 - Use Data Validation to prevent human error

For example, if you only want to include 3 input options for a column:

  1. Highlight the column
  2. Click “Data” in the menu tab > Data validation
  3. Click the dropdown menu and select “List of items”
  4. You can now enter the 3 options (e.g. not started, WIP, done)
  5. Select “Reject input” for On invalid data
  6. Save

This is essential if another formula is dependent on the value of these cells!

💡
Pro Tip: Go to Format > Conditional formatting to create rules and make the cells look exactly the way you want!

Tip #6 - Use Formulas Whenever Possible

Let’s say we want to forecast 3 possible scenarios—ad spend increase of 20%, 30%, and 40%.

  1. Type in the percentages directly as your header (e.g. 20%)
  2. Type “=value FN+F4+(3x)” to lock the column, multiplied by “(1+ the percentage)”, “FN+F4+(2x)” to lock the row
  3. Hit ENTER > highlight the rest of the row and CMD+R (or CTRL+R for Windows) to apply the formula to the rest of the cells
💡
Pro Tip: Double click the small square at the bottom right of the cell to apply the formula to the rest of the table

Tip #7 - Use ImportRange when working with data

I recommend using the ImportRange function to import raw data. If someone were to accidentally overwrite the data, it won’t mess up your spreadsheet! I talk more about it here.

Tip #8 - Key Info Tabs

Think—if I had to show my manager the most important information about this project in 5 minutes, what would I prioritize? 🤔

For an event, we might prioritize the number of sign ups and topics to be shared as the most important tabs. For more complex projects and spreadsheets, it’s good to have a “Read Me First” tab that breaks down instructions, definitions and links to relevant files.

Tip #9 - Raw Data Tabs

It may be helpful to separate your raw data tabs so only the core working group can make edits.

  1. Right click the tab > Protect sheet
  2. You can enter a description (optional)
  3. Set permissions and select the people that are allowed to edit

Tip #10 - Share Spreadsheet with Teammates

A perfect spreadsheet is useless if your teammates don’t know how to use it or access it. 🤷🏻‍♀️

During team meetings or in follow-up emails, always hyperlink the spreadsheet with the appropriate URL

If you haven’t already…

Check out these Google Sheets tips to really hammer home your spreadsheet knowledge!