While MS Excel still rules the spreadsheet world, Google Sheets has been slowly making inroads.
It has found some loyalists in freelancers, small business owners, and entrepreneurs.
Google Sheets comes with a great set of functions, highly useful functionalities and the ability to collaborate with others (something where it has an edge over Excel spreadsheets).
If you’re one of the growing fans of Google Sheets, here are six Google Sheets hacks that will save you time and make you more efficient.
#1 Quickly Color Alternate Rows
Do you know that coloring alternate rows in a spreadsheet can drastically increase its readability?
There is an inbuilt feature in Google Sheets that allows you to quickly color alternate rows in a dataset.
Here are the steps to quickly highlight alternate rows:
- Select the data set in which you want to highlight alternate cells.
- Go to Format tab and click on ‘Alternating Colors’ option. This will open the ‘Alternating colors’ pane on the right.
- Select the formatting style and specify if you data has a header and/or footer. If you don’t like the given formats, you can also create your own format.
This would instantly apply the selected color to the alternate rows in Google Sheets.
To remove alternate colors, select the data and click on the ‘Remove alternating colors’ option in the ‘Alternating colors’ pane.
#2 Freeze Rows/Columns with a Simple Drag and Drop Trick
One of the frustrations of working with large data set is that as soon as you scroll away from the headers, you lose track of the data.
Freezing rows/columns is a cool technique that will make sure these headers are always visible no matter where you go in the worksheet.
Here are the steps to freeze the top row:
- In the top-left part of the worksheet, you will see a gray empty box (as shown below).
- Place your mouse on the thick gray line below this box and you will notice that it changes to the hand icon.
- Left-click from the mouse and drag it down to one row.
This will freeze the top row in your Google Sheets.
If you have headers in more than one row (say 3 rows), you can drag the gray line and place it below the third row.
Similarly, if you want to freeze a column with headers, simply drag the gray line to the right.
#3 Insert Image from URL
Google Sheets has this amazing function that allows you to directly insert an image in a cell using the URL of the image.
It can be done using the IMAGE function in Google Sheets.
Below is the syntax of the IMAGE function:
=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. If you don’t specify an option, it takes 1 as default.
1 resizes the image to fit within the cell.
2 stretches the image to completely fill the cell.
3 leaves the image at the original size.
4 allows you to specify the height and width of the image.
- [height] – the height of the image.
- [width] – the width of the image
Here is the formula that will give you the google logo image in a cell:
Cool.. isn’t it?
#4 Use a Drop Down List for Faster Data Entry
A drop down list is really helpful if you want a user to enter only pre-defined items in a cell.
You must have seen these in many online web forms, where you can quickly select from the given options.
It also helps keep your data entry consistent and error free.
Suppose you have a list of items and shown below and you want to create a drop down list in Google Sheets so that a user can select from these items only.
Here are the steps to create a drop down list in Google Sheets:
- Go to the cell where you want the drop down list.
- Go to the Data tab and select the Validation option.
- In the Data Validation dialog box, make the following changes:
- Make sure Cell range refers to the cell where you want the drop down list.
- In criteria, make sure ‘List from a range’ is selected and specify the cells that have the items (A1:A6 in this case).
- Click on Save.
This would instantly create a drop down list in the selected cell. You will be able to see a small downward pointing arrow in the cell that indicates the presence of a drop down list.
Now you can click on the arrow and the drop down list would appear from which you can make a selection.
Note that this drop down is dynamic. For example, if I go and change the source data (in cells A1:A6), the drop down would automatically update.
Here is a video tutorial on creating a drop down list in Google Sheets:
#5 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.
Note that this is an array function and you will not be able to delete one item from the unique. If you have to, you will have to delete the entire array.
#6 Run Spell Check to Correct Misspelled Word
Nothing spoils the credibility of your work as fast as a spelling mistake does,
Thankfully, Google Sheets have you covered here.
If you work with text data in Google Sheets, here is a quick tip to run spell-check in Google Sheets 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.
These 6 Google Sheets Hacks save me a lot of time daily and makes me super-efficient.
I am sure you also have a couple of good Google Sheets tips up your sleeve. Would love to hear about it in the comments section.
About Author: This is a guest post by Sumit Bansal. Sumit is a spreadsheet geek and loves to share cool tips through his blogs and videos. Check out his blog on Google Sheets Tips.