Google Sheets is a popular Microsoft Excel alternative. As with other Google tools, the Sheets is a core part of Google Drive. In this article, we have taken the liberty to dive deep and unearth a handful of super useful Google Sheets tricks that you may have never heard before.
These Google Spreadsheet tricks are simple enough to learn and remember.
1. Using International Currencies on Your Spreadsheet
We have all come across situations where we need third-party calculators for exchange conversions. While there is no harm in doing so, it is a tedious way of getting stuff done. Google Sheets offers built-in currency conversion features that will help you convert from one currency to another in a jiffy. Let us see how to use this feature.
Use the following syntax:
=<CellAddress>*GOOGLEFINANCE("CURRENCY:<FromCurrencySymbol><ToCurrencySymbol>")Let’s break it down. The “from currency symbol” is the base currency that you want to convert. The “to currency symbol” is the currency that you want to convert the initial value to.
As an example let us convert the Indian price (in INR) for the latest iPhones into USD. So the syntax shapes up as follows:
=B2*GOOGLEFINANCE("CURRENCY:INRUSD")Ensure that you don’t use the actual currency symbol and instead stick to the three lettered conventions.
2. Use Spell Checks in Google Sheets
Spell Check is an everyday feature that helps you keep the spreadsheet free of spell-errors and typos. It is tiresome to spellcheck manually. In such cases, it is always better to rely on Google’s inbuilt spell-check feature and manually correct any discrepancy.
Here is how you can enable the Spell Check on Google Sheet,
- Select the cell range/columns that you want to check
- Select the Tools tab and click on Spelling
- Google’s Spell Check will automatically identify mis-spellings and typos
- You can choose whether to Change, Ignore or Add the word to the Dictionary
A word of caution, it is not wise to completely depend on the Spell Check feature. So, make sure you give it a second look before the change.
3. Translate Cells in Google Spreadsheet
Thanks to the Internet the international boundaries have shrunk. Just imagine this, you have just received a quotation in a foreign language. Translating every cell will involve a lot of donkey work. Google Sheets has got you covered with the Translate function.
The Google Translate function is capable of translating the content across hundreds of cells into multiple languages. Furthermore, this function will also help you detect which language is currently being used in the Google Sheets. This is how you can translate the individual cells in a spreadsheet from one language to another.
Use the following syntax:
=GOOGLETRANSLATE(<CellAddress>, "source_language", "target_language")Example:
=GOOGLETRANSLATE(A9, "en", "ar")In the above example, we have translated text (“Hey are you free for lunch”) from English to Arabic. If you don’t mention the “target_language”, Google will automatically convert the cell to the default language.
4. Autodetect Language and Translate
Google offers a nifty trick if you don’t know the source language. In the syntax above replace “source_language” with “DETECTLANGUAGE” and Sheets will automatically detect the source language and translate the cell into a language of your choice.
Use the following syntax:
=GOOGLETRANSLATE(A14, DETECTLANGUAGE(A14), "en")As part of this Syntax, the language in cell “A14” is automatically detected and translated to the “Target Language”(English in this case.) Since the GOOGLETRANSLATE function on Google Sheets is not an array function you can simply drag the result and translate other cells as well.
5. Convert Your Google Sheet Data Into a Heat Map
Heat Map involves representing data in the form of a map where the data values are represented as colors. The Heat Maps are particularly popular in scientific studies when a large amount of data is collected and the scientists create a heat map to identify trends and patterns.
Thanks to the power of conditional formatting, you can easily create a heat map on Google Sheets. Follow the steps below to create a heat map out of your data
- Select the data on the Google Spreadsheet
- Head over to Format>Conditional Formatting
- Choose the colors for Minpoint, Midpoint, and Maxpoint in the Conditional format rules panel
- Map your Midpoint to a Percentile
Note: The Google Sheets conditional formatting panel will also allow you to set the minimum or maximum values. Once this is done the heat map will extend to the values that exceed the minimum while the ones that are below the minimum will share the same shade of color.
6. Import HTML From Web Pages to Google Sheets
Copy pasting web data to your Google Sheets is not exactly intuitive and effortless. There’s a high chance that this might end up being a messy affair, especially if the data set is large. Thankfully, Google Sheets allows you to scrape websites and thus import the data automatically. The best part is that you don’t have to be a coder to do this.
Let us see how the Google Sheets web scraper works with a live example.
For the sake of demonstration let us consider a Wikipedia page. This particular page is titled “List of original films distributed by Netflix” and features multiple tables across different categories. I am interested in the Documentaries section. Use the ImportHTML syntax for scrapping the web page.
Use the following syntax:
=IMPORTHTML(URL, query, index)The URL in this Syntax corresponds to this webpage address:
“https://en.wikipedia.org/wiki/List_of_original_films_distributed_by_Netflix” in our case.
The Query is the part where you mention the item you intend to import, in our case the import element in the table. As you might have already noticed the Wikipedia page features multiple tables. The argument called index is a way of specifying which table you want to import.
In this case, it is Table 4. Finally, the syntax reads as follows:
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_original_films_distributed_by_Netflix","table",4)Lo and behold! The Google Sheets will automatically fetch the table from the Wikipedia page and the formatting is not botched up either.
Work With Google Sheets Every Day
The Google Sheets is a universe of its own. The online web tools offer a plethora of functions that can be leveraged to make your life easier. It isn’t only about productivity, as the quality of your data will also improve.
And for all of this to happen you don’t need to be a computer geek. I personally prep up Google Sheets with the help of different templates. Try Google Sheets for automatically sending an invoice every month or just automating repetitive tasks. The possibilities are endless.
Read the full article: 6 Google Spreadsheet Tricks That Are Easy to Learn and Remember
