Google Sheets is increasingly being adopted as the spreadsheet tool of choice for small business, freelancers, and entrepreneurs.
It has a great set of formulas (some even better than Excel), an impressive choice of functionalities, and the added advantage of being able to share and collaborate with others.
If you’re one of the increasing loyalists of Google Sheets, here are some time-saving tips that will make you more efficient.
#1 Freeze Rows/Columns with this Simple Drag and Drop Trick
If you work with a lot of data in Google Sheets, here is a cool technique that will freeze the rows and columns with headers.
This way, these headers will always be visible no matter where you go in the worksheet.
Here are the steps to freeze the top row:
- Go to the top-left part of the worksheet where you will see a gray box (as shown below).
- Place your mouse on the thick gray line below this box, left-click from the mouse and drag it down to one row.
This will freeze the top row in your Google Sheets.
Similarly, if you want to freeze a column with headers, simply drag the gray line to the right.
#2 Get a list of Unique Items
If you have a dataset that contains duplicates, you can quickly get all the unique items using the UNIQUE function.
Suppose you have a data set as shown below where there are duplicates:
To get all the unique names from this, select the cell where you want the names and use the following formula:
This would instantly give you all the unique items (in this case names) from the list.
#3 Insert Image from URL
Google Sheets allows you to directly get an image in a cell using the URL of the image.
This can be done using the IMAGE formula.
Below is the syntax of the formula:
=IMAGE(url, [mode], [height], [width])
It takes four arguments:
- url – this is the URL of the image you want to insert.
- [mode] – this is an optional argument where you can specify values between 1 to 4. 1 resizes the image to fit in the cell. 2 stretches the image to fill it in the cell. 3 leaves the image at the original size, and 4 allows you to specify the height and width of the image.
- [height] – the height of the image
- [width] – the width of the image
If you don’t specify the [mode] argument, it takes 1 by default.
Here is the formula that will give you the google logo image in a cell:
#4 Run Spell Check to Correct Misspelled Word
If you work with text data in Google Sheets, here is a quick tip that will help you make your worksheet free of any misspelled words.
Here is how to run the spell-check in Google Sheets:
- Select the data on which you want to run the spell-check (or select the entire worksheet).
- Go to Tools tab and click on the Spelling option.
This will run the spell check on your data and show you any misspelled words in finds. You can then choose to correct it (manually or based on the suggestions) or ignore it.
#5 Color Alternate Rows
If you have a large data sets, coloring alternate rows increases the readability tremendously.
Google Sheets has an inbuilt functionality that allows you to do this.
Here are the steps to quickly highlight alternate cells/rows:
- Select the data set.
- Go to Format tab and click on ‘Alternating Colors’ option. This will open the ‘Alternating colors’ pane on the right.
- Select the formatting style (or create your own by using the custom option).
This would instantly apply the selected color to the alternate rows in Google Sheets.
What are your favorite Google Sheets tips?
Share with us in the comments section.