Tips and Tricks to Improve Google Sheets

Among the several add-ons available for Google Sheets, we found these helpful.

110314 googlesheets 1
Google Sheets add ons

These add-ons are helpful when putting together, changing or editing a spreadsheet -- especially if it was originally and poorly designed by another person and you need to clean it up.

110314 googlesheets 2
Add Rows & Columns: Insert multiple columns and rows at once

Select a cell in your spreadsheet, activate this add-on, and choose Add Rows Below, Add Rows Above, Add Columns Right, or Add Columns Left. Enter the number you want, and they will be inserted.

110314 googlesheets 3
Advanced Find and Replace: More robust search-and-replace

This provides a few more capabilities over Google Sheets’ search: Search for a formula, hyperlink, note or value across all the sheets, or selected sheets, in your spreadsheet at once. Found items will be listed in this add-on’s tool window, where you can then replace the results with other characters.

110314 googlesheets 4
Remove Blank Rows: Automatically delete all empty rows

This is helpful if you’re cleaning up a spreadsheet that wasn’t designed compactly. It will delete all the blank rows in a sheet, or the blanks within a range of cells that you select. Or, this add-on lets you hide the blank rows, so you can just see what a sheet looks and works like without them.

110314 googlesheets 5
Mapping Sheets: Pin addresses from a spreadsheet to Google Maps

This will take street addresses and the information corresponding to them (e.g. name, phone number, category such as business type) listed on a sheet, and pin them onto Google Maps. If you’re planning a trip, and want to create a map that has filtered list interactivity of the locations on your journey, try out this tool.

110314 googlesheets 6
Merge Values: Join values from cells together into one cell, column or row

Select multiple cells on a sheet, turn on this tool, and you can choose to throw together the values inside these cells into a single cell, column or row. The values in their new location can be set apart from one another with commas, line breaks, semicolons, or spaces.

110314 googlesheets 7
Power Tools: A suite of convenient editing tools

This add-on includes six tools to help you edit spreadsheets faster. Some of their tasks: Remove characters, formatting or other elements; change the capitalizations of text, or formats of dates or other numbers; convert formulas between absolute and resolute; and split values in a column.

110314 googlesheets 8
Random Generator: Generate random numbers or characters

This fills in a cell with randomly generated numbers and/or characters. Its initial purpose can be to test formulas, but these strings can also serve as passwords: Build a spreadsheet that lists co-workers and generate unique passwords for each to log-in to a network, or websites you visit for which you want to make more secure passwords for your personal accounts.

110314 googlesheets 9
Remove Duplicates: Find identical values between tables or among columns

Two tools come with this add-on: One will scan the tables between two sheets for duplicate values, and let you mark them with a color or delete them. The other will do the same thing -- or find uniques -- but for columns on a single sheet.

110314 googlesheets 10
Table Styles: Apply and create styles to tables

Instead of making tables and applying colors to them from scratch, you can use this tool to insert pre-designed ones onto a sheet. Three color styles are available, and you can adjust the number of columns and rows designated to aspects of the table (like its header or stripes). You can also select a table on a sheet to copy its style and reuse it.

110314 googlesheets 11
Workflows: Get approvals for your spreadsheets

When you need to have a spreadsheet approved -- not edited -- by others, Workflows can coordinate this and keep tally. After a designated person has looked over the spreadsheet, they then click the “Approve” button and can add a brief comment. (This add-on is also available as a separate version for Google Docs.)

110314 googlesheets 12
Yet Another Mail Merge: Send mass emails personalized with Google Sheets from Gmail

With this add-on installed on Google Sheets, you can then compose an email in Gmail containing fields that will be filled in automatically with the appropriate information from a Google Sheets spreadsheet you’ve linked the email to. You are limited to sending up to 100 emails at once.