16 October 2014

How to Color Alternate Rows in Google Sheets

Microsoft Excel and other programs in Microsoft Office provide a handy feature called “Quick Styles” to help you quickly format a selected range as a striped table. The table can have zebra lines meaning alternating rows are formatted with different colors (see example).

Alternate Row Colors in Google Sheets

Google Sheets do not support zebra stripes (yet) but you can use conditional formatting combined with a simple Google Formula to create a formatted table. You can apply alternating colors to both rows and columns in Google Sheets easily.

Here’s the trick.

Open a Google Sheet and choose Conditional formatting from the Format menu. Select Custom Formula from the dropdown and put this formula in the input box.


Select a Background color for the rule and set the range in A1 notation. For instance, if you wish to apply alternating colors to rows 1 to 100 for columns A to Z, set the range as A1:Z100.

Click the “Add another rule” link and repeat the steps but set =ISODD(ROW()) as the custom formula and choose a different background color. Save the rules and the zebra stripes would be automatically applied to the specified range of cells.

Google Formulas with Conditional Formatting

Tip: If you wish to extend this technique to format columns with different colors, use the =ISEVEN(COLUMN()) formula. Simple!

This story, How to Color Alternate Rows in Google Sheets, was originally published at Digital Inspiration on 16/10/2014 under Google Docs, Internet

Use Google Sheets for Multilingual Chat with Speakers of Different Languages

You can only speak and write English so how do you converse with a person in China who writes Mandarin but doesn’t understand a word of English? Google Translate is no doubt a good option but it is going to be tedious for you (and your Chinese friend) to translate each and every sentence manually before sending them through any messenger.

How do you break down the language barrier and chat with someone in another country when there’s no ‘common’ language between the two parties? Microsoft is building a version of Skype for that will allow real-time translation for voice and video calls. You can watch the video demo though the actual software is expected to become available for Windows 8 later this year.

There’s another option now for people looking to have multi-lingual text chats – Google Sheets. Since two people can work on a Google Sheet simultaneously, it can actually work as a simple chat client. And if you integrate the same sheet with Google Translate – which is easy – the text typed inside Google Sheets can be translated in real-time and automatically (Demo GIF).

Google Chat - Multi Languages

Multi-lingual Chat with Live Translation

Here’s the idea. You have two participants speaking different languages that have opened a Google Sheet at the same time. There are 2 columns in the sheet for each participant. Now Participant A can write text in his own language in column A and the translated version in Participant B’s language will appear instantly in the second column. And vice-versa.

To get started, open this Google Sheet and choose File -> Make a copy to make your own copy of the sheet in your Google Drive. Now hit the share button in your sheet and share it another person with “edit” permissions (since he or she would write the text inside your sheet).

That’s pretty much it. All you have to do now is put your name and your friend’s name in cells C4 and G4 respectively. Also select your native languages from the drop-down in cell C5 and C6. Now type anything in the yellow cell and it will appear in the second column in the participant B’s language. Similarly they can write in the green cell and the translated text would show up in your column.

Multi-lingual Chat in Google Sheets

Internally, the sheet is powered by Google Scripts. And it supports all language pairs that are supported by Google Translate from Hindi to French to German to Tamil. Give it a try!

This story, Use Google Sheets for Multilingual Chat with Speakers of Different Languages, was originally published at Digital Inspiration on 16/10/2014 under Google Translate, Internet