Google Sheets is a cloud-based spreadsheet-making tool. It has almost all the features that a user gets in spreadsheet software, like Microsoft Excel. Most of you are aware of Google Sheets and use it on a daily basis to create and edit spreadsheets. In this article, we are going to share some Google Sheets Tips and Tricks that will help you do your work faster on Google Sheets.
Because it is a cloud-based tool, it offers many benefits to the users, like:
- All your data is saved in the cloud, which saves the disk space on your computer.
- You can access your spreadsheets on any device simply by signing in to your Google account.
- With the autosave feature, you need not worry about data loss due to a power breakdown or a sudden computer crash.
- You can share your spreadsheet with other users if you and other users are working on the same project.
The best part of Google Sheets is it is completely free to use. You just need a Google account and an active internet connection to use Google Sheets.
Google Sheets Tips and Tricks
In this article, we will cover the following Google Sheets Tips and Tricks:
- Insert images in your spreadsheet
- Color alternate rows
- Extract unique values from a data set
- Connect Google Sheets with Microsoft Excel
- View the Edit history
- Run a spell check
- Apply filters to your spreadsheet
- Protect cells to prevent unwanted changes
- Use Artificial Intelligence
- View the list of keyboard shortcuts
Let’s see all these tips and tricks in detail.
1] Insert images in your spreadsheet
Google Sheets lets you insert images in your spreadsheet. When you use this feature, you will have the following two options:
- Insert an image in cells
- Insert an image over the cells
The first option inserts the image in the selected cell, whereas, the second option inserts the image in its original dimensions that cover a certain number of cells. To insert an image, go to “Insert > Image,” then select the desired option. You can insert an image in Google Sheets from your computer by uploading it, from a webcam, by a URL, by using the Google Image Search feature, etc.
2] Color alternate cells
While working on a spreadsheet, sometimes, we require to color the alternate rows in our data. You can do it easily in Google Sheets by using the Alternating colors option. The steps to do this are as follows:
- Select the range of rows.
- Go to “Format > Alternating colors.”
- Select the color from the default styles. You can also add your own color to the header and alternate rows by clicking on the respective drop-down menus.
- Click Done.
3] Extract unique values from a data set
Suppose you have a data set that contains many duplicate values in a particular column and you want to extract only the unique values from that data set. You can do this by using the UNIQUE function. This function creates a new data set that does not contain duplicate values.
Let’s say, you have duplicate values in column A from cells A1 to A10 and you want to extract the unique values from this column. For this, you have to type the formula =UNIQUE(A1:A10). The following steps will show you how to do that:
- Select the cell in which you want to extract the unique values.
- Type =UNIQUE(A1:A10)
- Press Enter.
Apart from that, you can also delete duplicate values rows in Google Sheets.
4] Connect Google Sheets with Microsoft Excel
If you work on both Google Sheets and Microsoft Excel, you can connect Google Sheets with Microsoft Excel so that every time you update data in Google Sheets, the changes will automatically be reflected in Microsoft Excel.
5] View the Edit history
Every time you make changes in Google Sheets, Google saves it in the version history of your spreadsheets. You can view your editing history by clicking on the Last edit link at the top of your spreadsheet (see the above screenshot). When you click on that link, all your editing history timelines will open on the right side of the Google Sheets. If you want, you can restore any of the previous versions of your spreadsheet. For this, select the timeline of the version that you want to restore and then click on the three vertical dots next to it, and select Restore this version.
6] Run a spell check
Google Sheets also lets you run a spell check so that you can correct the misspelled words. After running the spell-check tool, Google Sheets will ask you to correct the misspelled words. You can take the appropriate action for the misspelled words. The instructions to use the Spell check tool in Google Sheets are as follows:
- Select the range of data, the spellings of which you want to check.
- Go to “Tools > Spelling > Spell check.”
You can also add words to your personal dictionary.
7] Apply filters to your spreadsheet
Like Microsoft Excel, Google Sheets also lets you filter the data in a spreadsheet. This is a very useful feature of Google Sheets that helps you save time when you have to view only specific results in a huge data set. The following steps will guide you on how to apply a filter to a spreadsheet in Google Sheets.
- Select the column on which you want to apply the filter.
- Go to “Data > Create Filter.” After that, you will see the first cell of the selected column has a filter icon on the right side.
- Click on that icon and apply the filter as per your need.
- Click OK.
You can apply a filter by color, by condition, like greater than, less than, equals to, etc.
8] Protect cells to prevent unwanted changes
In Google Sheets, you can protect specific cells or a range of cells to prevent unwanted changes. This feature is helpful when you are editing an existing spreadsheet or a shared spreadsheet. Moreover, in a shared spreadsheet, the protect cells feature prevents other users from editing the data in the protected cells.
The steps to protect a cell or a range of cells in Google Sheets are given below:
- Select a particular cell or a range of cells in Google Sheets.
- Go to “Data > Protect sheets and ranges.”
- Now, you can set permissions for the selected cell or the range of cells.
You can also protect the entire sheet to prevent unwanted changes.
9] Use Artificial Intelligence
You can use Artificial Intelligence in Google Sheets to calculate data and create charts automatically. To use AI in Google Sheets, first, select the range of data in your spreadsheet and then click on the Explore button on the bottom right side. After that, the AI window will appear on the right side of your spreadsheet. Now, you can generate charts, like Pie charts, Histograms, Bar graphs, etc., of the selected data with a single click. After creating the chart, drag and drop it to place it in your spreadsheet.
Apart from generating the charts, you can do calculations on the selected data by using AI. For example, if you want to find the Mode of the selected data, simply type Mode in the search field and then select the same from the search results. Google Sheets then show you the Mode of your data. You can also view the formula used to calculate the Mode. Similarly, you can do other calculations in Google Sheets by using the AI, like finding the Average, Median, Mean, etc., of the selected data.
10] View the list of keyboard shortcuts
The keyboard shortcuts are designed to make work easier and faster. The Microsoft Office applications have a lot of keyboard shortcuts that help users complete their work quickly. Similarly, Google Sheets also has keyboard shortcuts, using which you can perform specific tasks quickly. To view the list of keyboard shortcuts in Google Sheets, press the Win + / keys.
The Win + / keys will open the keyboard shortcuts window. You can search for a specific keyboard shortcut by typing the related phrase in the Search field. Or you can select a particular category from the left side to view all the keyboard shortcuts for that category.
How do I use Google Sheets for beginners?
Google Sheets is a free online spreadsheet-making tool. It comes with a Template Gallery which is useful for freshers. After opening Google Sheets, click on the Template Gallery on the top right side to view all the available templates. Select any of the available templates and start editing the spreadsheet. Some of the Google Sheets templates are Annual budget, Monthly budget, Finance Investment Tracker, Travel planner, Invoice planner, Purchase Order, Website Traffic Report, etc.
Google Sheets also help you learn formulae while working on a spreadsheet. This is a very useful feature for beginners. Simply type a formula and then click on the Question mark on the left side of the formula. After that Google Sheets will show you the details of that formula. you can also click on the Learn more link to learn in detail about that formula.
Read next: Google Docs Tips and Tricks everyone should know.
How do I make Google Sheets more effective?
There are certain things that you can do in a Google Sheets spreadsheet to make it more effective, like:
- You can use conditional formatting to color specific rows in your spreadsheet to identify data easily.
- If you are a beginner, you can use pre-designed templates to make a spreadsheet.
- You can use the Artificial Intelligence feature in Google Sheets to create and insert charts in your spreadsheet.