Google sheets are an awesome online platform for data entries. This smart tool is getting famous with each day passing by. Google has always been innovative with its product ideas and the Google sheets are a perfect example of that.
You can even share these sheets with your partners so that they are aware of whatever is being done on the data. Google is keenly updating this with relevant features that will make us more compatible with this facility.
Today, we are going to learn about 15 awesome tricks that Google has for us to make our data more precise and presentable.
1. Smartly opening the sheets
This era is known for shortcuts because we don’t have time for traditional long methods.
How do you usually open Google sheets? You go to your browser and go for the main site “docs.google.com/spreadsheets”. But that’s for newcomers. Did you know you can directly open a Google sheet and start working?
Well, you can. Just type “sheets.new” in the search bar of your Google and a blank sheet will appear in front of you. What are you waiting for? Start quick style working.
2. Adding images in the sheets
How do you make your data more explicable and presentable? The answer is by adding images and videos to your Google sheets.
If you want to insert an image then use =IMAGE function. You need to drop the URL of the image into the image function and the image will show up. For example =IMAGE(“https://dataacts.com/wp-content/uploads/2019/09/soccer-field.jpg”).
3. Adding videos to your data
Yes, you read it right. You can add videos to the data too.
To add a video to your Google Sheets just need to follow this procedure.
First of all, you need to copy that video. After that,
a. Go to File and click on new then click on presentation.
b. Name this presentation whatever you want by simply clicking on Untitled Presentation.
c. Click on the layout and choose a blank layout.
d. First, click on insert then on video. And now, search the video and click on select.
e. Copy the video by right-clicking on it.
Now, you are going to paste the video.
a. Now, go back to the sheets and click on insert.
b. Now click on Drawing and paste your video.
c. Then click Save.
4. Import data from other Google sheets
Sometimes we need data from other documents during our presentation. There is no problem in opening different sheets all the while during the presentation but to be honest, it looks a little clumsy.
Why not import data from other sheets and look like an expert?
You can use IMPORTRANGE function to do so. Just follow the syntax to import data from other sheets as =IMPORTRANGE(“URL of the sheet you want to import data from”, “sheet reference and range”).
You can also use the IMPORT function to import data from other websites.
a. IMPORTHTML for HTML tables
b. IMPORTFEED for RSS entries
c. IMPORTDATA for a web-based CSV file.
d. IMPORTXML for a custom section of a webpage
5. Translate texts from various languages
Yes, your very own translator just in a click. To use this function, you need to know the codes for languages. That means the 2 letters we use to denote a language. For example en for English, es for Espanola, ko for Korean, etc.
Follow the syntax to translate the text:
=GOOGLETRANSLATE(“The row and column you want to translate”, “Code for the language it is or auto for English, “Code for the language you want to translate to”)
For example, I want to translate “Hello! How are you.” in Spanish and let’s say it is in the B column of row 2, so syntax will be following.
=GOOGLETRANSLATE(B2, “en”, “es”)
The resultant word will be “¡Hola! Cómo estás.”
6. Check for valid e-mail addresses
Has it ever happened to you that you asked someone to give their e-mail address and they gave you the wrong one?
It won’t happen again because now you can quickly check the validity of the address on Google sheets. For this, you need to use ISEMAIL function.
Use the following syntax:
=ISEMAIL( the row and column the address is kept in).
7. Sending e-mail directly from the comments
Now, that you have checked the validity of the e-mails, you are ready for the shortcut way to send e-mails.
To comment, select the text and right-click on it. Click on “+ comment” to comment on the text. While adding a comment, simply add “+ e-mail address of the receiver” and the e-mail will directly show up in his inbox.
Google sheets are real-time but this feature can be used as an urgent call for someone to check out something they need to.
8. Applying filters on data
Suppose that there is a huge amount of data in your Google sheets and it is kind of messy. Now, you want to view the data of specific kind only, how would you do that?
You can use the smart function of filtering data in Google sheets.
To do so, follow these simple steps:
- First of all, choose the column you want to apply the filter on and select on it.
- Now, click on data and then click on “create a filter”.
- You will see three lines in the selected column, click on them.
- Choose the way you want your data to be sorted alphabetically.
- Also, select the type of filter you want to use.
In the image above the data is filtered with condition where Bid Strategy is equal to Manual CPC
9. Validate your data
Validation of data means that you are ensuring that only valid data is input in the particular cells or whole sheet and this can be done with the “Data Validation” feature.
Select the cells you want to apply the feature and then right-click on them. Then click on data validation. Select among the various options regarding what you want to do with the cells.
10. Protect your data
Aside from validating your data, you can also do more to protect it. If you want that nobody should change your data then you can use the “Protect range” feature to protect your data.
Select the cells and right-click and then select the “Protect range” feature. Both warnings and restrictions can be implemented. In the above example, I can project the names column that no one should be able to edit it except myself.
11. Clean your data
When you have a large data set then it becomes necessary that your data is as formatted and as meaningful it can be.
You can trim the unnecessary whitespaces and also keep the letters in the upper and lowercase. It is required because half of your frustration is due to the mess in the text.
Select the cells and go to data and then click on trim white spaces. You can also use upper and lower functions to get the letters into upper and lower cases.
Besides this, sometimes there are duplicate entries in the sheets that lead to double counting and every business person knows the meaning of that.
Select the cells and click on the data. Now, simply click on remove duplicates.
12. Visualize your data smartly
Data visualization refers to the presentation of data in a way that everything becomes crystal clear to the viewer. It can mean using a range of functions like inserting charts, conditional formatting, data sorting, etc.
If you want to add a chart then you need to make sure the data is the result you want to show. That means everything is already meaningful and adding a chart will enhance it.
Select the rows and columns and then click on insert and then chart. You need to select among various options where you are asked to select the chart type etc.
You can also use conditional formatting to analyze your data.
13. Learning keyboard shortcuts
Everybody loves shortcuts but who will remember those already defined shortcuts. There are a number of them and well, it is humanistic to forget some of them.
So, what now? You have two options.
- Cram all of them like a machine.
- Click command and / on your windows screen and learn the shortcuts you need to learn specifically. You can find them here anytime you want.
14. The add-on features
As if Google isn’t providing you enough? Now, you can add on more relevant features to your Google sheets. This way your sheets will become more and more compatible with the kind of work you do.
Click on add-on and get the new feature instantly. You can addons like Google Analytics, Supermetrics to pull data from Facebook Ads into Google sheets etc.
15. Don’t repeat repetitive tasks anymore
If there is something that you always do with your data, for example, always sorting your data in a way or calculating total or something, then now Google sheets will do it for you.
Click on Tools and then click on Macros. Select the Record Macros button and now you need it to show it to the Google sheets how you do it. It will record everything and when you are done click on save and name your macro.
Next time, when you are done with your work and you have to do that repetitive task just click on Macro. You can make more than one macro here.
Conclusion: In this article we’ve learned about some cool tricks that can make our data reasonable and acceptable. We learnt how smartly we can work on Google sheets and make our life easier. If you have any more important tricks to show, you can mention in the comments below. I just picked the best 15 that I think everybody should know. That was all, stay tuned for the next article.
