A Guide to Using Google Sheets More Effectively

google-sheets-blog-770

There are many uses for spreadsheets beyond making tables and sorting data. From creating calendars, project plans, sitemaps, lexicons, competitive analyses, and more, Sheets is the ideal collaboration tool for a project with multiple people. Here are tips, tricks and best practices to help you take advantage of all Sheets has to offer.

View version history for the whole document or a single cell.

See what’s changed in a file since you last looked at it, and who made those changes. An edit status is always shown at the top of the file, often reading “Last edit was…” or “See new changes”. Clicking on this link opens the version history for the entire document (sometimes it’ll just open the most recent edits and then you have to select “See full version history”).

To view the version history for a single cell, simply right-click on the cell and select “Show edit history” and you can toggle between the different edits made to that cell. This is really helpful if the content for a cell gets deleted accidentally—you can revert the cell back to any previous version.

Copy and auto-fill content to streamline data input.

When you select a cell you’ll notice a blue square in the bottom right corner. This is called the fill handle, which can be used to copy or auto-fill content.

When hovering over the fill handle, your cursor will change to a cross. Drag the handle over the cells you want to fill and then release.

Whenever the content of a row or column follows a sequential order—like numbers (1, 2, 3), days (Monday, Tuesday, Wednesday), or content patterns—the fill handle will guess what should come next in the series and auto-populate the selected cells.

Spell check all sheets before transferring content to another application.

Google Sheets doesn’t automatically spell check, so many errors and typos go unnoticed. Go to Tools > Spelling > Spell check. Then it will run a Spell check on the selected sheet, and you can select “Search all sheets'' to check each tab.

Copy color, font, and alignment formatting to multiple cells.

There’s no need to format cells individually or copy and paste cells only to delete the text because the formatting was all you needed. Follow these simple steps.

Select the cell whose format you want copied.
Click Paint Format.
Click the cell you want the formatting applied to.

Unlike Google Docs, there is no Paint Format mode where you can apply formatting to multiple nonadjacent cells without having to click the paint roller icon each time. However, there is a workaround.

First, follow the three steps above.
Then, while holding down the CTRL key (Command if Mac) select any other cells you want that same formatting applied to.
When all are selected, press CTRL + Y (Command + Y if Mac), and the formatting will be applied to all.

Freeze rows and columns to keep category titles visible.

There are Freeze options under View, but the easiest way is to simply drag the “freeze lines” as shown below.

Drag freeze lines to whichever rows and columns you want pinned in place.

Merge cells with one click.

Select the cells you want to merge and click the Merge icon Merge in the toolbar.

Merge toolbar

Insert individual cells vs. a row or column.

When you don’t want to disrupt other rows or columns, insert a single cell.

Right-click a cell.
Select Insert cells.
Choose “Shift right” or “Shift down” and a new cell will be inserted, moving the cell you selected to the right or down.

Double-click a row or column line to size-to-fit.

If you want text in a certain area to auto-fit, instead of dragging the Row or Column line to adjust the size, double-click on the line. If you want all cells to auto-fit content, highlight all cells by selecting the blank rectangle in the top left (above Row 1 and to the left of Column A) and double-click on a row or column line to apply auto-fit across all.

Adding notes to a cell vs. comments.

While similar, Notes and Comments each have their own advantages. Adding a comment to a cell is ideal for making a suggestion since everyone will get an email notification and someone can easily accept or reject it to clear the comment—and you can always see who did what.

Notes are better if you have a suggestion for replacing text since the text from the note can easily be copied and pasted into the cell. And in general, notes are the best option if you have a non-actionable comment. Since there is no option to accept or reject it, no one shared on the file will receive a notification and it is less visually intrusive.

To clear a note, you can either delete the text inside the note or right-click and select “Clear notes” which will delete all notes within the document.

Saving or inserting images.

To save an image to your computer, hold down the Shift key while right-clicking on the image and select “Save As”.

To insert an image, go to Insert > Image. You’ll be given the option to put the image “in cell”—which locks the image in that cell—or “over cells”, which displays the image in its original size, not linked to any specific cell.

“In cell” is normally the best option since it keeps the image in place while you add, delete, move or resize other cells within the Sheet. Otherwise, you’ll have to constantly move and resize the picture with every change. Besides, if at any point you don’t want the image in a cell, you can simply right-click and select “Put image over cells”. Note there is no way to reverse this though once it’s done.

We hope these tips and best practices help you navigate Google Sheets better and work more efficiently! Another tip? Most of these work in Microsoft Excel as well—there are only slight differences in how icons and commands look and where they’re located. You can also check out our Google Docs blog for more tips that also work in Sheets.