Efficiency is key when working with spreadsheets. The more tricks you know, the faster you get your work done and the quicker the output.
Google Sheets is a free for all browser-based spreadsheet platform. So, it not only lets you crunch data, it allows you to share it, and work collaboratively on it, all free of cost.
To help you increase your productivity, we at Productivity Spot have put together 15 of the most useful Google Sheets Tips and Tricks, that will save you time and make you feel like a spreadsheet pro!
Quickly Inserting Multiple Rows
Let’s start with something quick and easy. If you’re somewhat familiar with Google Sheets, you might find it quite easy to insert a single row before a given row, right?
All you do is right-click on the given row, and select ‘Insert 1 above’ from the context menu that appears.
But what do you do when you want to insert multiple rows? Say 5,6 or even 100? It obviously wouldn’t be practical to insert them one row at a time! That would be really inefficient.
So, here’s a quick way to insert multiple rows before a given row. Let’s assume you want to insert 5 rows before row 6 in the worksheet shown below:
To insert 6 rows before row 5, you should:
- Select 5 row numbers, starting from row 5.
- Right-click on any of the selected row numbers.
- Select the ‘Insert 5 above’ option from the context menu that appears.
That’s it! You will get 5 new rows inserted between your rows 5 and 6.
For more information on this, you can take a look at our article on inserting multiple rows in Google Sheets.
Highlighting Every Other Row (for Better Visibility)
Very large datasets can sometimes look monotonous and it might be difficult to differentiate one row from another. One popular trick to improve readability of large datasets is to highlight every other row in a slightly darker or lighter color, as shown below.
With Google sheets, this is really easy to do. All you need to do is select all the rows containing data, and click ‘Alternating colours’ from the ‘Format’ menu.
You even have the option to change the colors from the ‘Alternating colours’ window that appears on the right side of your Google Sheets window.
Counting Only Unique Cells in a Column
There may be cases where you have a number of repeated values in a Google Sheets column. In such cases, taking a count would be erroneous, since the count would also include the duplicate values.
Here’s how you can get Google Sheets to count only the unique values in a column. Let us assume you have a list of names that may contain duplicates in cells A2: A12.
Simply select the cell where you want to display the number of unique names and enter the following formula:
This formula works because the UNIQUE function returns a list of unique names in the given range and the COUNTA function counts the number of text values in the list returned.
Note: The COUNTA function ignores any blank cells. So if the original list contains any blank values, the above formula will not count them.
Validating Email Addresses
There might be cases where you have a list of email addresses from an external source and you want to make sure that all the email addresses are valid, before further processing.
Google Sheets provides a handy little function, ISEMAIL, to validate the structure of email addresses. So, to find out if an email address at cell A2 is valid, simply use the formula:
This function will return a TRUE if the email address is valid, and a FALSE if the EMAIL address is invalid.
Inserting a Checkbox
Checkboxes provide a great way to add interactivity to your spreadsheet. You can use them to quickly select / unselect items from a list or dataset. You can also use them to create to-do lists, attendance sheets, interactive charts and so much more. There are a myriad of interesting ways in which you can use checkboxes in your spreadsheets.
What’s more, adding checkboxes in Google Sheets is really easy. Here are the steps you need to follow:
- Select the cell in which you want to insert the checkbox.
- Click the Insert option on the menu.
- Click the ‘Tick box’ option.
You should now see a checkbox in your selected cell.
Now whenever the checkbox in the given cell is selected, the cell basically contains the value TRUE. When the checkbox is unchecked, the cell contains the value FALSE. You can now use these checkboxes to add further functionality to your spreadsheet.
Vertically Looking Up Tables
Google Sheets provides the powerful VLOOKUP function that lets you look up a table for a value corresponding to a given value. In other words, VLOOKUP lets you search for a certain value in a column and fetch a value from a different column in the same row.
For example, say you have the following table, consisting of product names and their stock count:
If you want to find the stock count corresponding to a given product, you can easily use VLOOKUP as follows:
The above formula will return the stock count of the product corresponding to the ‘Blender’.
- The first parameter is the value that you want to search for in the table.
- The second parameter is the range in which you want to search.
- The third parameter is the index of the column from where you want to retrieve a value. Since we want to retrieve the stock count, which is the second column in the given range, we specified the index as 2.
- The last parameter simply specifies if you want exact matches. Since we want to consider the closest match, and not necessarily an exact match, we specified this value as FALSE.
Note: Another easy and more effective way to look up values in tables is by using the INDEX-MATCH functions. You can find more information about these functions here.
Importing Data from a Different Spreadsheet
Accessing data from a different file or spreadsheet in Google Sheets is not as intuitive as it is in Excel. This is because Google Sheets is a browser-based platform that works with page URLs. As such, you need a special function if you want to import data from a different spreadsheet.
For example, you may track sales data for a product in different workbooks for different company branches. To combine the data into one, you don’t need to open each file, copy the relevant data and put them together. That would be inefficient.
The smarter way to do this would be to use the IMPORTRANGE function to directly import your required data from their relevant files into the new file, as follows:
The first parameter should contain the URL of the spreadsheet / workbook (or just the Google key of the workbook). This is the location that you see on your browser’s location bar when your source spreadsheet is open.
The second parameter contains the sheet name and the range of cells that you want to import from the given sheet. The sheet name part is optional. So if you want to import, say, cells in the range A1:F9 from the sheet named “MySheet”, you would type “MySheet!A1:F9” in the second parameter of the IMPORTRANGE function.
Once you enter this formula and press the return key, you will notice an #REF! error. When you hover over the cell, Google Sheets will ask you if you want to ‘Allow access’ to connect the two spreadsheets. Click ‘Allow Access’.
You should now see your range of cells from the source sheet displayed at your desired point in the target sheet.
Importing Data from a Given URL
If you want to import data, such as a csv (comma separated value) or tsv (tab separate value) file from a given website or location on a disk, you can use the Google Sheets IMPORTDATA function.
This function takes in a URL or filename as its parameter and, in return, retrieves all the data from the corresponding address. It then formats the data into a format that can be displayed in your spreadsheet, and displays it in the cells following the cell containing the formula.
For example, if you want to display the contents of the csv file located in the URL https://sample-videos.com/csv/Sample-Spreadsheet-10-rows.csv, you can simply use the IMPORTDATA function as follows:
This saves a lot of time, as you directly get the entire contents of the file in your spreadsheet, instead of having to first download it and then upload or copy it.
Note: Make sure you add the http:// or https:// protocol and always remember to enclose the URL in quotes.
Converting a PDF to Google Sheets
If you need to work with data that is in a PDF, you will notice that it’s not possible to open the file directly in Google Sheets. Fortunately, there are a number of ways in which you can import the data into Google Sheets.
One way is to use online tools available on websites such as Convertpdf2excel and Altoconvertpdftoexcel. These web applications can help you quickly convert your PDF files to a format that you can use and edit in Google Sheets.
Besides these third-party tools, the Google Workspace Marketplace also offers some handy apps, such as the PDF Tables Extractor. This application is designed to extract tables from a PDF file, making sure it retains the row and column structure of the tables.
When working with large sets of data, you can use filters to get rid of distractions, so you can concentrate on only the necessary parts of the dataset.
Filters allow you to hide certain parts of your spreadsheet, so you can only view your rows of interest.
Let’s say you have a dataset consisting of Employee name, department, location, joining date and hours worked:
If you want to see only the rows that have Department=”Manufacturing”, you can apply a filter to your spreadsheet as follows:
- Select the Filter icon in your toolbar (or navigate to ‘Data’ under the ‘Filter’ menu).
- This will display filter icons next to the first cell of each column.
- Click on the filter icon of the column based on which you want to filter the data. In our example, click the icon next to ‘Department’.
- Select the value(s) that you want included in your filter, and deselect the values that you don’t want included. In our case, we want to see all rows related to ‘Manufacturing’. So we make sure that only the ‘Manufacturing’ option is selected and all the other options are unselected.
You should now see only the columns where Department=”Manufacturing”.
To remove the filters and get back to viewing the original dataset, you can simply click on the filter icon again to toggle it off.
Quickly Highlighting Rows Containing a Particular Value
Filters can provide a great way to quickly highlight rows containing a particular value, if you want to subsequently perform other actions on those rows or simply see them nice and clear.
For example, in our previous example with Employee name, department, location, joining date and hours worked, you might want to highlight all the rows where Department=’Manufacturing’.
You could manually do this by selecting the required rows, keeping the CTRL key pressed, and then using the ‘Fill color’ button but this would be painstaking if your dataset is large.
A more efficient way would be to simply apply a filter to display only the rows where Department=’Manufacturing’ (as shown in the previous section).
Now you’ll notice the rows appear one below the other, making it easy to select all the visible rows in one go.
When you remove the filter, you’ll notice all rows where Department=’Manufacturing’ selected. Now you can go ahead and select these rows, and change highlight them by selecting the ‘Fill color’ button:
When you remove the filters, you’ll see all the rows where Department=’Manufacturing’ highlighted in your selected color:
Deleting Empty Rows
Another great application of filters is in deleting empty rows. When you import data from elsewhere into Google Sheets, there’s always a possibility of getting random empty rows in your spreadsheet. If the datasheet is really large, it might be difficult to track them down and delete them manually.
A great trick to get this done quickly is to use filters:
- Select the Filter icon in your toolbar (or navigate to ‘Data’ under the ‘Filter’ menu).
- Click on the filter icon of any of the columns.
- Click on the ‘Clear’ link from the dropdown that appears. This will uncheck all the values from the list of values in the column.
- Check the value where it says ‘(Blanks)’.
- Click OK.
- You will now see that all the rows that had data are now hidden, and only the rows that were blank are visible.
- Select all visible rows (with blanks), right-click and select ‘Delete selected rows’.
- Remove the filters.
You should now find all the blank rows deleted.
Removing Rows with Duplicate Data
Another common data cleanup task involves removing duplicate rows. Google Sheets also provides a simple menu option to get this done quickly.
Consider the following dataset:
To get rid of duplicate rows, all you need to do is select the range of cells that you want to clean up, select ‘Remove duplicates’ from the ‘Data’ menu and then click on the ‘Remove duplicates’ button (after specifying any other settings in the dialog box).
You will see a message window telling you how many rows have been deleted.
When you click OK, you should find all duplicate rows removed from your selection.
Splitting Text to Columns
A common task when dealing with data that has been imported into Google Sheets is the task of splitting a cell containing a set of values or text. These values are usually separated by a given delimiter, like a comma or a space. So here’s a trick that makes use of these delimiters to split cells into separate columns.
Let’s say you have a list of full names and you want to separate into two columns – first name and last name.
Notice that each of the names have a space separating the first and last names. So we can use this as a delimiter and apply the ‘Split text to columns’ feature of Google Sheets to each name, as follows:
- Select all the names in the column
- From the ‘Data’ menu, select ‘Split text to columns’.
- You will see a small tooltip-like component next to your selection asking you to select the ‘Separator’ or delimiter.
- Click on the dropdown next to ‘Separator’ and select the ‘Space’ option.
You should now see the names separated into two columns.
Adding (or Subtracting) Days to a Date
There may be times when you need to add a given number of days to a date. For example, you might need to add a certain number of days to get a tentative delivery date, or subtract a number of days from a given date to trace back to a starting date.
These calculations are much easier to perform in Google Sheets than you may think. All you need to do is to perform simple addition or subtraction. So, if you have a date in cell A2 and want to add 10 days to it, you only need to type:
Similarly, to subtract 10 days from the date, type:
The reason for this simple solution is that dates are inherently represented as serial numbers in Google Sheets. The serial starts from 1, which represents the date December 31, 1899. For each day after this date, the serial keeps increasing by 1.
So the date June 20, 2021 is actually serial 44267, because it is exactly 44,367 days after December 31, 1899. When you add days to a date in Google Sheets, it essentially adds this number to the serial number it represents. Same goes for subtraction.
If you want to add a number of days, say 10 to the current date then simply add the number of days to the TODAY() formula, as follows:
The TODAY function returns the current date, depending on the current date and time settings of your browser.
Want even more Google Sheets tips and tricks? Explore our top tutorials on Productivity Spot! We are uploading new Google Sheets step-by-step tutorials every week.