Most people can do the basics on spreadsheet programs, like entering data and using simple formulas. But, Google Sheets is a powerful program that can do so much more.
Although mastering certain functions and inclusions can be difficult and take a long time, many ways to improve your spreadsheets are simple to learn.
Follow along with our Google Sheets tips to learn how to improve your spreadsheet skills in as little as a few clicks of your mouse.
Google Sheet Tips for Formatting
Changing the Number Format With the Menu
Not all numbers added to a spreadsheet are the same. Some represent a currency or a percentage, or even a date. Although Google is smart and predicts the number format using AI, often, it mispredicts it. To change the number format:
- Select the cell containing the number
- Click on Format in the top bar.
- Click on Number and then choose your desired number format.
Changing the Number Format With Shortcuts
Alternatively, you can find the number formatting options in the main bar. These are located between Zoom and Font Style. Here, you can format the numbers as currency and percentage and increase and decrease the decimal places. You can also view more options from this menu.
When using percentages, ensure the percentage is written as decimals before changing the format.
Displaying Formula as Text
Assume that you would like to display a formula as text. We often need to do this when creating these guides where we might want to show what the formula is doing. For obvious reasons, when you type the formula in, Google automatically executes it as a function.
A way to display the formula as a text is to type an Apostrophe “ ` ” before typing it. This tells Google not to execute any functions and treat it as a text string.
Wrapping Text in Cells
A lot of the text we enter inside a cell is larger than the space in the cells. You can use the wrap text feature in Google Sheets in these cases. This will make the text wrap inside the column.
To access this feature:
- Click on the cell(s) you wish to format.
- Click on Format in the top column.
- Click on Wrapping. This will open a smaller menu with three options: Overflow, Wrap, and Clip.
- Overflow is the default option used by Google Sheets if the cell to the right side is empty. The text will simply cover the cell to the right of it.
- Wrap means the space in the cell will be increased to accommodate the additional text.
- Clip means no changes will be made to the cell, and the remaining text will be cut off.
Change the Fill and Font Color
When you add data into your spreadsheets, most of the time, it’s just a bland, colorless page that looks boring and is incredibly tedious to go through. Adding color to important cells and changing the font color can make your most relevant data stand out, making it easy to go through and find.
To do this, click on either the Text color or the Fill color icon in the main top bar. This will open a color picker allowing you to select from several standard colors.
To pick a custom color, click Custom and either type in a hex number or choose from the color palette.
Changing Font Styles and Size
Similar to the one above, this Google Sheets tip allows you to personalize your spreadsheet to make it easier to read and less bland.
To choose the font, select the cell or the text and click on the Font box in the main bar. This will open a list where you can choose your desired font. Once selected, the font will be applied to the selected text.
To change the font size, click on the Plus “+” or the Minus “-” sign. You can also type your desired font size in the text box between the two symbols.
This allows you to use different font styles for other columns. For example, you can make the titles of each column as larger text with a different font.
Using the in-depth border editor in Google Sheets, you can also surround the cells in your spreadsheets with a border. To add a border, select the relevant cells and then click on the Borders icon in the main toolbar of the spreadsheet.
Here you can choose what type of border you want. You can add the border to all the cells’ sides or strategically add the borders to create a table-like appearance for your spreadsheet.
You can also customize the border design in the same menu. There are multiple types of lines and thickness levels to choose from. You can also change the colors of the lines.
Using Conditional Formatting
You can make Sheets format cells if they meet certain conditions. A good example of this would be to highlight negative figures in red. Here’s how to make Google Sheets look good with conditional formatting:
- Highlight the cells you wish to apply the formatting to
- Navigate to Format > Conditional formatting
- In the menu that pops up, choose the parameters you wish to apply. In the above example, we chose Less than 0 and made the fill color red.
Google Sheets Tips and Tricks with Shortcuts
Show Formulas Rather than the Results
Let’s assume that the calculations in your spreadsheet aren’t matching up, and you suspect that you may have put the wrong formula somewhere. Typically when checking, you would have to click on the individual cells one by one to see the formula in the fx bar.
Instead, you can enable the formula view by clicking on View > Show > Formulas. Once active, it will be indicated with a tick mark beside the option. Once finished using the option, disable it using the same steps.
Use the Spell Checker
It can be pretty embarrassing when a coworker points out a spelling mistake in your spreadsheet, right? Spell checker is not on by default as spreadsheets often use incorrect English such as shorthand names and product ids. But, like Google’s other services, you can still use a spell checker on Sheets.
To do this:
- Click on Tools in the main toolbar.
- Click on Spelling and then on Spell check.
If Sheets detect a spelling error, you will get the option to change it to the correct one or ignore it. If Sheets finds a correct word but shows it as wrong, you can add it to the dictionary so it won’t be detected as a mistake again.
Paste Values Rather than Formulas
When you want to copy the values from one spreadsheet to another, if you’re using a formula in the spreadsheet, then they might get copied and not give you the desired results. Instead, when you copy the values from your spreadsheet, paste them without formatting by using the Ctrl + Shift + V keyboard shortcut.
For more detailed options, right-click on the cell where you want to paste the data and then click on Paste special. There, click on Values only.
Copy the Formatting
Similar to the tip we discussed before, it is possible to copy the format from one cell to the other without changing the data making it one of the most useful tips for Google Sheets. Copy the cells containing the desired format. Now, click on the cell where you want to apply the format and paste it using the Ctrl + Alt + V shortcut. For more
For more detailed options, right-click on the cell where you want to paste the data and then click on Paste special. There, click on Format only.
View the Keyboard Shortcuts
Keyboard shortcuts are one of those Google Sheets features that you won’t think about much as a newer user, but they can make your life much easier. You can access the list of shortcuts by pressing Ctrl + /. This will open a window with the keyboard shortcuts.
Google Spreadsheet Tips for Formulas
Converting Numbers to Strings
A lot of times, you want to represent your data as strings. Now, it’s OK to type out the numbers from a few cells as text. However, things can get tedious fast when you have data in dozens of cells. You can use the Google Sheets TEXT function to convert the numbers into a string.
The syntax for the TEXT function is:
The num parameter defines the cell range, date, time, or number to format. The format parameter defines the pattern for the formatting. This is enclosed in quotation marks, and two characters can be used to determine this. “0” will force zeros to display if the number has fewer digits, and “#” won’t force zeroes to be displayed.
Note: The above example uses 35.77 as the first argument, but you could also use a cell reference or an array.
Use Google Translate
You can translate basic words from one language to another using Google Sheets’ built-in GOOGLERANSLATE function. In the formula, you just have to specify the cell holding the text and the source and destination languages.
Here is the syntax for the function:
=GoogleTRANSLATE(text, “source”, “target”)
The formula uses three parameters.
- The text parameter defines the cell range or the text to be translated.
- Source defines the letter code for the language of the defined text.
- The target defines the two-letter code of the language you wish to convert the text to.
Here’s an example of converting Swedish to English using the country codes SV for Swedish and EN for English.
Adding a full website address to your spreadsheet can make it look unorganized. The extra text on the address of the website can cause warping. You can use the Google Sheets HYPERLINK function to hyperlink the URL in plain text. The syntax for the formula is:
The URL parameter defines the link to the website your wish to hyperlink. Note that the URL should be written in quotation marks. The label parameter defines the text to display. Clicking on this text will open the website link on a new tab.
When writing names in the spreadsheet, it can often be annoying to fix the capitalization of words in the spreadsheet. You can use the PROPER function in Google Sheets to do this. Here is the syntax for the formula:
This formula requires one parameter. The text defines the text to capitalize. The first letter will be converted to uppercase, while all the rest letters will change to lowercase. Note that this tool is excellent for capitalizing proper nouns like names of locations or people. Therefore, you shouldn’t use this for paragraphs or longer text forms.
Related Reading: Mail Merge in Google Sheets
Google Sheets Tricks for Collaboration
Copy a Sheet to a New One
If you need to copy the data from one of your sheets to a new one, Google has made it easy.
To copy a sheet:
- Right-click on the name of the sheet. This will open a menu with several edit options for the sheet.
- Click on Copy to and then on New spreadsheet. This will keep the data on the existing spreadsheet and copy it to the new one. If you have multiple sheets in a single spreadsheet, you can also delete one of the existing spreadsheets.
Go Back to a Previous Version
You will see a “last edit was x time ago” link next to the name of your sheet while working on your spreadsheets. Clicking this will allow you to check your version history, which contains all previous versions of your current spreadsheet.
When you click the shortcut, it will open a sidebar towards the right side of the screen, listing all the previous versions.
To restore a previous version, click on the three vertical dots beside the version name and then click on Restore this version.
View Edit History
Not only does Google allow you to revert back to an older version of your spreadsheets, but you can also see the edit history for individual cells in your spreadsheet.
To do this, right-click on the individual cell and then click on Show edit history. This will show a small box beside the selected cell with two arrows, allowing you to scroll freely between the edit history.
Note that this won’t allow you to restore the data in the cell. However, you can do this manually or revert to a previous version, as shown in the previous tip.
When collaborating, we often wish to notify other collaborators of the changes we make or would like to be made. Google has added this feature in Sheets in the form of comments, which is one of the features of Google Sheets that make it extremely popular as a collaborative tool.
To add a comment:
- Select the cell first and right-click to open the edit menu.
- Click on Comment, and a small window will open beside the cell. Here, type in the comment and then click on Comment.
Note: You can also do this by using the Ctrl+ Alt+ M keyboard shortcut.
You can tag other collaborators using the “@” symbol and then write the person’s username.
Sharing Your Spreadsheets
One of the best cool things to do with Google Sheets is real-time collaboration. The process is highly streamlined. All you have to do is click on the green Share button on the top right side of the spreadsheet. This will open a small window in the middle of the spreadsheet. Here, you can do either of two things:
- Add a person’s email and add them to the spreadsheet directly
- Create a link for them to join with
Once you enter the mail of the person you would like to share your spreadsheet with, you can choose whether that person can just view, comment, or be fully able to edit the spreadsheet.
You can also add a custom message to let the person know why you are adding them to the spreadsheet and the permissions you are granting them.
You can even add notification options to check what others have changed in your spreadsheets.
Frequently Asked Questions
What Are the Three Most Helpful Features of Google Sheets?
- Sharing your spreadsheet with others using the Share button is a great way to collaborate with your coworkers. You can then use Comments in the spreadsheet for effective collaboration.
- Using conditional formatting makes your spreadsheets easier to read. You can access this through Format > Conditional formatting.
- Being able to edit and review version history is very helpful if you’ve made some mistakes but can’t figure out where. You can open an old version of the sheet instead of starting from scratch.
How Do I Make Google Sheets like a Pro?
One of the cool Google Sheets ideas you can use to make your spreadsheets look professional is conditional formatting. This will change the properties of a cell if it fulfills the defined requirements. You can do this by clicking on Format > Conditional formatting. Then you just have to define the conditions that have to be met to apply the formatting.
What Features Can You Use in Google Sheets to Analyze Data Efficiently?
The type of data you need to analyze will depend on which functions are the best to use. But, as a general rule, charts are much easier to see trends in. You can add charts by following these steps:
- Highlight the data
- Navigate to Insert > Chart
Sheets does a good job of inserting the correct chart type, but you can make changes by double-clicking the chart.
More Google Sheets Tips
We’ve barely scratched the surface of this powerful program’s functionality with our Google Sheets tips. There is still a lot to learn about the program, and you can even get add-ons to streamline the experience. Make sure you browse our other guides to learn more.
You May Also Like: