40 Essential Google Sheets Tips and Tricks [2023 Edition]
In this Google Sheets tips and tricks 2023 edition, find 40 tips and tricks to quickly master Google Sheets
Even if you regularly use spreadsheets, you might not be familiar with the breadth and depth of their capabilities. This rings especially true with Google Sheets!
Google has been developing Google Sheets as part of its G Suite since 2006; and with today’s need for quick and easy ways of collaborating across the world, Google Sheets is an attractive solution to quickly analyze and process data.
The original list of 32 tips and tricks has now been boosted with eight (8) new tips and tricks in this 2023 edition of this list! Are you ready?
1. Quickly change number format
Typing a number that’s supposed to be a currency or a percentage? You can quickly change the number format of a cell by selecting it and then going to the main toolbar. Look for the following symbols:
Click the dollar symbol to change the number format to currency, automatically inserting a currency symbol and adding (or reducing) the decimal digits to two digits.
Click on the percentage symbol to change the number format to percentage. Make sure the percentage is written in decimal form before doing this action.
2. Display formulas as text strings
Say you'd like to show a formula on your sheet to explain what you're doing in another cell... once you type it in, however, Google Sheets automatically runs it as an actual formula. What do you do now?
A simple trick to enter the formulas without Google Sheets processing them is by inserting an apostrophe ‘ before typing it. This tells Google Sheets to preserve the succeeding characters into a string.
You can also do this using a recently-released function in Google Sheets. Scroll down to Tip 27 to read more.
If the cell contains a string that does not fit its size, you can make the text wrap inside the column by clicking Format on the main menu and then clicking Text wrapping in the drop-down menu. Another smaller drop-down menu will appear. Select Wrap.
5. Use Google Sheets themes
Google Sheets has a set of themes you can apply to make any spreadsheet more visually appealing.
To access the themes, click Format on the main menu. The Themes option is listed at the top of the drop-down list. A sidebar on the right side of the spreadsheet will appear, where you can choose the theme to apply.
Besides adding fill colors and adding fonts and font styles, you can also add borders with almost the same functionality and flexibility as that of Microsoft Excel. You add borders by selecting the range of cells and then choosing the side or sides of the cells where you want to add a border.
Border colors and styles can also be customized, so you can have dotted red borders or dashed blue borders.
7. Rotate the text in the spreadsheet
You can also rotate the text in the spreadsheet to give it a more aesthetic feel! Find the icon for Text Rotation on the toolbar, then select the preset angles visually shown as options. Additionally, you can input the specific angle to a textbox that will appear as well.
8. Set font size of the text
You can make the text larger or smaller in Google Sheets. Simply look for a textbox with a number “10” along the main toolbar. You can either click on the arrow on the right side to choose from preset font sizes or type a value directly to the textbox.
9. Change the number of decimal places of a value
You can increase or decrease the number of decimal places displayed for a certain value in Google Sheets. The icons are shown below. The icon of .0 with a left arrow underneath it is for decreasing the decimal places while the icon of .00 with a right arrow underneath is for increasing the decimal places.
Simply drag down the thick gray bar in the upper-left corner of the spreadsheet until it is near the bottom of Row 1.
14. Apply conditional formatting to highlight cells that fulfilled a condition
It is much easier to see which cells have fulfilled a certain condition if they are filled with a certain shade of color. To do so, we need to apply conditional formatting. Click Format on the main menu, then click Conditional formatting on the drop-down menu.
A sidebar will appear on the right side of the spreadsheet where you can include the condition that needs to be checked.
The new Slicer feature by Google Sheets allows you to filter a long sheet to see entries that you are looking for. The Slicer automatically analyzes the sheet for common patterns in the entries and uses them as potential filters for slicing the data, with the relevant entries remaining.
A handy tool to summarize the data in a large spreadsheet is by using the Pivot Table feature of Google Sheets. Click on any cell containing the relevant data, then click Data on the main menu then click Pivot table in the drop-down menu.
A pop-up box will appear, asking you whether to add the Pivot Table in the new sheet or insert it in the existing sheet. A pivot table will now be produced, where you can select which values to display and aggregate.
If you want to check the consistency of the formulas entered in the spreadsheet without modifying the spreadsheet, click View in the main menu and then click Show formula in the drop-down menu. The formulas will be shown instead of the results.
18. Ensure that spelling is correct
Google Sheets allows you to check the spelling of the words. Click Tools on the main menu, then look for Spelling. Click on it to find Spell check. Click Spell check and Google Sheets will automatically check the spelling of the words in the sheet.
19. Paste the values instead of the formulas
Often we need to copy the resulting values of an array of formulas to another sheet. To paste the values instead of the formulas, use the keyboard shortcut Ctrl+Shift+V.
Another way is to right-click on the cell where you want to paste the values, then click Paste special, then click Paste values only.
20. Copy an array, then paste it with rows and columns flipped
If you have an array that you need to copy, but with rows and columns flipped, simply copy the array, then right-click on the first cell where you want to paste the array. Click Paste special, and then click Paste transposed on the list of options that will appear on the right side (or sometimes left side).
21. Copy the formatting of one cell to another cell
It is also possible to copy the formatting of one cell to another cell without changing the data in the destination cell. Copy the cell containing the desired format, then click on the destination cell, then use the keyboard shortcut Ctrl+Alt+V.
22. Quickly check how a formula works
There is a trick for situations where you know the formula to use but you suddenly forgot its syntax. When you type a formula in Google Sheets, a small blue box with a question mark will appear on the formula bar. Click on it to see a short description of the formula including how the input should be written.
23. Use Filter View to filter out irrelevant data for analysis
Google Sheets has a function called Filter View, where you can apply certain conditions to filter out rows that do not fulfill these conditions. The main advantage of Filter Views is that its changes will not modify the original sheet; they only appear to the user while browsing the spreadsheet and will be discarded once the spreadsheet is closed.
When starting to analyze a huge dataset in Google Sheets, you may need a little nudge to look in the right places for meaningful patterns. Google Sheets’ Explorer feature helps jumpstart your analysis. To use it, click anywhere in the data, then either click the Explore button on the lower-right corner of Google Sheets, or simply press Alt+Shift+X on your keyboard. A sidebar will appear on the right side of Google Sheets, with preliminary analysis of your data. Often this includes a Pivot Table and a few charts.
25. Access a list of keyboard shortcuts
You can access a list of keyboard shortcuts while using Google Sheets by pressing Ctrl + /. This will open a window containing the keyboard shortcuts grouped by category.
Using Special Formulas
26. Check if the cell is really blank
Characters that do not show up in the spreadsheet, such as apostrophes and stray spaces can mess up the results of the COUNTAfunction which you may use to check the number of cells with existing entries. The solution is to use the ISBLANK function to spot which cells contain the offending characters.
27. Translate words using Google Translate
Google Sheets has a built-in GOOGLETRANSLATE function that you can use to translate simple words from an original language to a certain target language. Specify the cell containing the text, and the original and the target language.
28. Verify if the text is in a valid email address format
If a certain column is designated for containing email addresses, you can use the ISEMAIL function to check if the syntax of the email is in a valid email address format. This will help you filter out the obvious responses that do not fit the syntax of a valid email address.
29. Insert links to external sites
To insert links to external sites, Google Sheets has a built-in HYPERLINK function. The syntax is as follows:
For our example below, we used the following syntax:
And we got the following result:
30. Properly capitalize names
Google Sheets has a built-in function called PROPER that you can use to properly capitalize names. It does not just capitalize the first letters of the names but also changes to lowercase the letters that are not the first letters of the names.
31. List down unique entries in a selected array
You can use the UNIQUE function to list down all unique entries in a selected column. Type in the function, and then select the column you want to filter.
32. Display formula using FORMULATEXT function
Back in Tip 2, we demonstrated a simple workaround of inserting an apostrophe in a formula so it is converted into a string. What if you want to keep the formula working but want to display it as a string in a different cell? Simply use the FORMULATEXT function. The syntax is simple: simply point the FORMULATEXT to the cell containing the formula. For example, if the formula is in A1, simply add:
And the formula used in A1 is displayed as a string.
Expanding the Capabilities of the Spreadsheet
33. Add Custom Functions via the Script Editor
Are the current functions not enough for what you want to do? Google Sheets allows you to add custom functions through its Apps Script.
Collaborating through the Spreadsheet
35. Copy a specific sheet to a new spreadsheet
If you want to copy a specific sheet to a new spreadsheet (for example, to preserve the data at the moment), just right-click on the tab of the sheet on the bottom of Google Sheets. A list of options will appear. Click Copy to, and select New spreadsheet.
36. Revert to a previous version of the spreadsheet
Google Sheets allows you to revert to a previous version of the spreadsheet seamlessly. To do so, click on the link on the upper part of the Google Sheets that begins with “Last edit was…”
This will bring you to a page with a right sidebar listing the previous versions of the spreadsheet while displaying it on the left side of the page.
37. See edit history
Google Sheets also includes a way to see the edit history for an individual cell. Right-click on the target cell, then select Edit History. A small box will appear listing down each edit done, with a set of arrows on the top for going back and forth through the edit history.
38. Add comments
If you are reviewing a sheet and want to place comments on certain cells without changing the content (or you don’t have access rights to outright edit them), you can use the comments feature of Google Sheets. Select a cell where you want to place a comment, then press Ctrl+Alt+M.
A small window will appear where you can write your comment. It will appear alongside the name on the email address you used in adding the comment.
39. Modify the link to spreadsheet to automatically copy it to the user’s Google Drive
If you need to make the spreadsheet accessible to other users but also make them copy it instead of modifying the original one, you can simply edit the ending of the link to your spreadsheet by changing the “edit” portion to “copy”:
Upon clicking the new link, you will arrive at the following screen:
40. Share the spreadsheet with other users
To share the spreadsheet for real-time collaboration, click the Share button on the upper-right corner of the Google Sheets. A pop-up box will load where you can enter the email addresses of the members of your team who need to access the spreadsheet for collaboration.
Once you enter the email address, you can choose whether the user can just view, comment, or outright edit the spreadsheet. You can also add a custom message alongside giving the user access as Google Sheets will send an email to that user to notify them that they have access rights to the spreadsheet you are using.