How To Unpivot Table in Google Sheets? – Flatten or Transpose Google Sheets Pivot Table

The wide tables in Google Sheets are pretty common. However, with the help of wide tables in Google Sheets, it is extremely difficult to perform convenient analysis, since we have to keep scrolling the scroll bar horizontally. Also, the wide data in Google Sheets comes handy only when we want to create charts and not for any other purposes. And to overcome these issues, we need to simply convert the wide tables into tall tables which means unpivot the tables in Google Sheets. In order to transpose Google Sheets Pivot Table, we can either use the AppsScript or FLATTEN function.

Let us discuss everything about Google Sheets Flatten Table with the help of Sheets Tips provided on this page. Read further to find more.

Table of Contents

How To Unpivot a Table in Google Sheets?

Let us say, we have created a wide table as shown in the image below.

unpivot-in-google-sheets/

Now we need to convert this wide table into a tall table as shown in the image below.

unpivot-in-google-sheets/

To transpose the pivot table in Google Sheets, we need to use the flatten function which is discussed in detail in the next section.

Reverse Pivot Table in Google Sheets

The steps to reverse a pivot a table in Google Spreadsheet are explained below:

  • 1st Step: Open the Google Sheets where you want to unpivot the tables.
  • 2nd Step: Now on this page, we are using the table which is shown in the following image as an example. Similarly, create a table that needs to be unpivoted.

unpivot-in-google-sheets/

  • 3rd Step: Move to the cell where you need to unpivot the table in Google Sheets.
  • 4th Step: Here we need to combine the data i.e., combine column headings. For this, we need to add special characters between the sections that can be split later. Here I am choosing the special character as “😀” smiley to make this more interactive. Choose the special character as per your choice.
  • 5th Step: Simply enter the formula =ArrayFormula(B1:E1&”😀”&A2:A4&””&B2:E4).
  • 6th Step: Press the “Enter” button and the output result will be as follows.

unpivot-in-google-sheets/

  • 7th Step: Now we need to modify this array formula with the help of Flatten function. So our formula will be as follows – =ArrayFormula(FLATTEN(B1:E1&”😀”&A2:A4&”😀”&B2:E4)).
  • 8th Step: Press the “Enter” button and you will see the results as shown below.

unpivot-in-google-sheets/

  • 9th Step: Now we have to use the Split function to split the columns and make it as a tall table.
  • 10th Step: So our formula will be “=ArrayFormula(SPLIT(FLATTEN(B1:E1&“😀”&A2:A4&“😀”&B2:E4),“😀”)) ” and press the “Enter” button.

Now you will see the wide table is transformed into a tall table as shown in the image below.

unpivot-in-google-sheets/

We can also unpivot the table in Google Sheets using the Apps Script code.

How To Use Sequence Function To Build Numbered List In Google Sheets?

There are multiple methods with the help of which we can easily build numbered lists in Google Sheets. But when we are using the Sequence function to build numbered lists, our work gets easier. Just by entering the Sequence function formula in Google Sheets, we generate various types of number lists such as ascending numbers, 2 dimensional numbers, descending numbers, and so on. However, to generate all these numbers, we need to tweak the Sequence function formula which is discussed in detail on this page. Read further to know how to generate the sequence numbers using the Google Sheets Tips. Read further to find more.

Table of Contents

Sequence Function Syntax in Google Sheets

The syntax or formula to use the sequence function syntax are explained below:

=SEQUENCE(rows, columns, start, step)

  • Rows: The number of rows that should be returned.
  • Columns: The number of columns that should be returned. If this parameter is missing, the returned array will only have one column.
  • Start: The number at which the series should begin. If this parameter is left blank, the series will begin at 1.
  • Step:  The amount by which each number in the sequence should be increased or decreased. If this parameter is left out, the sequence will be increased by one.

How To Use Sequence Function in Google Sheets?

We can generate various types of number lists using the Sequence function in Google Sheets. Let us see some of the examples with the help of which we can generate the numbered lists in Google Sheets.

Generating Numbered List Using Sequence Function in Google Sheets

The steps to generate the numbered list in Google Sheets using the Sequence function are as follows:

  • 1st Step: Open the Google Spreadsheet where you want to generate the Sequence numbers.
  • 2nd Step: Now move to the cell where you want to generate the numbered list.
  • 3rd Step: Enter the formula “=sequence(4)“.
  • 4th Step: Press the “Enter” button and you will see the results as shown below.

sequence function in google sheets

Here we have used the number 4. If we have used the number sequence as 5, then 5 sequences of numbers will be generated.

Generating Horizontal Number List Using Sequence Function in Google Sheets

The steps to generate the horizontal number list in Google Sheets using the sequence function are as follows:

  • 1st Step: Open the Google Sheets where you want to generate the horizontal numbered list.
  • 2nd Step: Now on the homepage, move to the cell where you want to generate the numbered list.
  • 3rd Step: Here enter the formula =Sequence(1,5).
  • 4th Step: Press the “Enter” button and you will see the results as shown below.

sequence function in google sheets

Generating Two-dimensional Array Numbers Using Sequence Function in Google Sheets

We can also easily generate the 2 dimensional array numbers in Google Sheets using the Sequence functions. The steps to get this done are as follows:

  • 1st Step: Open the Google Sheets where you want to generate the Two Dimensional Array.
  • 2nd Step: Now on the homepage, move to the cell where you want to generate the 2 dimensional array of numbers.
  • 3rd Step: Enter the formula =Sequence(10,5).
  • 4th Step: Press the “Enter” button and you will see results as shown in the image below.

sequence function in google sheets

Generating Specific Numbered List Using Sequence Function in Google Sheets

Using the sequence functions in Google Sheets, we can also generate the numbers as per our specification. For example, if we want numbers to be generated in sequential order after the term 100 such as 100, 101, 102, etc., then we can get this done in Google Sheets using the steps given below:

  • 1st Step: Open the Google Sheets on your device where you want to generate the numbered list as per your specifications.
  • 2nd Step: Now move to the cell where you want to generate the specific sequential number.
  • 3rd Step: Enter the formula as =Sequence(5,1,100).
  • 4th Step: Press the “Enter” button and you will see the results as shown below.

sequence function in google sheets

Generating Custom Numbered List Using Sequence Function in Google Sheets

Let us say that I want to generate a numbered list in such an order as 1, 11, 21 which is a kind of customization. Now the steps to generate this in Google Sheets are as follows:

  • 1st Step: Open the Google Sheets to generate the custom numbered list.
  • 2nd Step: Now on the homepage, move to the cell where you want to generate the customized numbered list.
  • 3rd Step: Enter the formula =Sequence(5,1,1,10).
  • 4th Step: Press the Enter button and you will see the results as shown in the image below.

sequence function in google sheets

Generating Descending Numbered List Using Sequence Function in Google Sheets

Sequence function in Google Sheets allows us to generate the descending numbered list. The steps to generate the numbered list in reverse order are as follows:

  • 1st Step: Open the Google Sheets to generate the descending numbers.
  • 2nd Step: Now move to the cell where you want to generate the descending numbers in sequential order.
  • 3rd Step: Enter the formula “=sequence(5,1,5,-1)“.
  • 4th Step: Press the “Enter” button and you will see the results as shown below.

sequence function in google sheets

Generating Negative Numbers List Using Sequence Function in Google Sheets

We can also generate the negative numbers list in Google Sheets using the Sequence function. The steps to get this done in Google Sheets are as follows:

  • 1st Step: Open the Google Sheets on your device.
  • 2nd Step: Now on the homepage, enter the formula “=sequence(5,1,-1,-1)” in which you want to generate the negative numbers.
  • 3rd Step: Press the “Enter” button and you will see the results as shown below.

sequence function in google sheets7

Generating Decimal Numbers List Using Sequence Function in Google Sheets

The steps to generate decimal numbers in sequential order in Google Sheets are as follows:

  • 1st Step: Open the Google Spreadsheet on your device.
  • 2nd Step: Now on the homepage, move to the cell where you want to generate the negative number list.
  • 3rd Step: Here enter the formula “=ArrayFormula(SEQUENCE(5,1,10,1)/10)“.
  • 4th Step: Press the “Enter” button and you will see results as shown below.

sequence function in google sheets7

Suprising Things You Can Do With Google Sheets and Google Apps Script

Google Apps Script is a strong scripting language that is frequently used with Google Sheets. It allows people to create lightweight web applications that run on Google’s servers in the cloud. There are many surprising things that we can do in Google Sheets such as performing a Google Search, website monitoring, sending SMS, and many other things.

On this page, let us discuss all the surprising things that we can do in Google Sheets and Google Apps Script with the help of Google Sheets Tips. Read further to find more.

Table of Contents

Sheets Tips

Surprising Tips We Can Do With Google Sheets

There are many surprising tips that we can do in Google Sheets and they are discussed below:

1. Get SMS or Email When Website Goes Down

Commercial website-monitoring services will check a website you’re responsible for on a regular basis and send you an SMS message if it ever goes down. This helps you to respond quickly and fix the problem.

This is done using Google Docs via the Website Uptime Monitor script. It monitors your website every five minutes. If it is down, it adds a calendar event to your Google Calendar with a reminder, and the remainder is programmed to send you an SMS message right away. It uses Google Calendar’s SMS-reminder feature to automatically send you an SMS – the sending part is free, but receiving an SMS on some cellular carriers may cost money.

2. Perform a Mail Merge

A “mail merge” may appear to be a relic of the past, yet it can still be beneficial. By creating a template, you can send personalised emails to several persons using a mail merge. “Hello $PERSON, how are you?” can be an example of a template. You might have a list of email addresses and person names, and everyone would get a personalised email that started with their name if you used mail merge.

3. Track Products on a Website

The Amazon Price Tracker script accepts a list of URLs (website addresses) of products on Amazon.com or another Amazon website, such as Amazon.ca or Amazon.co.uk, and compares them. It will monitor these pages once a day and send you a daily email digest with a list of pricing changes. This can be useful if you want to keep an eye on a product and buy it when it’s cheaper.

4. Know Read Recipients in Gmail

The “read receipts” feature in Outlook can notify you when your email has been read by the receiver. This tells you whether the recipient of your email has opened it or not, though it isn’t always accurate.

Although the capability isn’t available in Gmail, the Email Tracker script integrates with Google Analytics and allows you to track email openings through Google Analytics when you use the script to send an email. It inserts a tiny 11-pixel picture, and Google Analytics tracks how many times this image is accessed. This should be much more accurate now that Gmail is loading graphics in emails automatically.

5. Schedule a Gmail Message in Google Sheets

The Gmail Sheet Scheduler lets you make a custom spreadsheet that imports drafts emails from your Gmail account and assigns dates and times to them. After that, select a menu option, and the script will deliver the messages at the time you specify. You don’t have to trust a third-party service or leave a web page open because it all happens through a simple script running in your Google account.

How To VLOOKUP Left in Google Sheets? (Reverse VLOOKUP Right to Left)

Google Sheets VLOOKUP function is one of the classy functions which helps to find a value or an item in a table or a row and returns it. But one of the major drawbacks with the VLOOKUP function is that we cannot VLOOKUP in the opposite direction i.e., VLOOKUP Left is impossible.

However, with the help of sneaky tricks, we can simply perform a reverse VLOOKUP right to left in Google Sheets. So why wait? Read further to know how to perform the VLOOKUP Left function using the Google Sheets Tips provided on this page.

Table of Contents

How Do You VLOOKUP The Opposite Direction in Google Sheets?

In order to enable the VLOOKUP function to work in an opposite direction, firstly we will have to create a virtual table. The virtual table must be created in such a way that the columns are switched in reverse order.

How To VLOOKUP To The Left in Google Sheets?

Consider the table in the following image where you want to perform the VLOOKUP Left. Now the steps to perform the VLOOKUP left is as follows:

  • 1st Step: Open the Google Spreadsheet where you want to perform the VLOOKUP left.
  • 2nd Step: Now in this example, we have a table and now we need to create a virtual table.
  • 3rd Step: Move to the cell where you want to create a virtual table. The array formula to create a virtual table is ={B1:B10,A1:A10}.

vlookup-left

  • 4th Step: Press the “Enter” button and the virtual table has been created in the Google Sheets. Curly brackets signify an array in this formula, and the two columns are switched within. This produces an array with the two columns reversed.
  • 5th Step: Now we need to alter the array virtual table formula with the VLOOKUP function. The modified formula is =VLOOKUP(D2,{$B$1:$B$10,$A$1:$A$10},2,FALSE).
  • 6th Step: Now use this formula and press the “Enter” button.

vlookup-left

That’s it. The VLOOKUP left function has been executed in the Google Sheets. This method is much easier when compared to INDEX and MATCH functions in Google Sheets.

How To VLOOKUP In Google Sheets Using Wildcards For Partial Matches?

Wildcards are an extremely versatile collection of symbols that allow you to choose a group of comparable strings at the same time. Google Sheets allows us to use the Wildcards with the VLookup function to find a value or symbol that is not an exact match but the partial match.

For example let us say, you want to extract all the URLs with the name amazon. You can do this manually when your dataset is small, however, if your dataset is huge, then you can simply use the VLookup function with Wildcards to extract all the URLs having the term amazon.

On this page, let us understand how to use the VLookup wildcards in Google Sheets with the help of Google Sheets Tips. Read further to find more.

Table of Contents

VLOOKUP Wildcard Google Sheets

The list of wildcards that we can use along with the VLOOKUP function is as follows:

  • The asterisk (*): When we use the asterisk as a wildcard, it finds all the matches related to it. For example, if you use the asterisk to find the word Ama, then it will match all the partial matches such as amaz, amazon, amazons, and so on.
  • The Question Mark(?): A question mark is used to represent a single character. For example, if you use “a?a”, then the possible results would be ama, aha, aia, aba and so on.
  • The tilde(~): This wildcard generally comes before one of the above wildcard characters (* or?) and tells Google Sheets that the next character should be treated as a regular character symbol rather than a wildcard. For instance, the string (~) indicates that we wish to find all strings that include the identical text t.

How To Use VLOOKUP Wildcard Partial Match in Google Sheets?

So here is a list of URLs from various websites.

wildcards-google-sheets

Now we need to find the value in the source table which is a partial match but not the exact match. Let us say that we need to extract the URL with the name Amazon. Here in the table, there is no string with the name amazon as an actual match, but with the help of the VLOOKUP wildcard, we can extract the partial match. The steps to get this done in Google Sheets are as follows:

  • 1st Step: Open the Google Spreadsheet on your device and move to the dataset where you want to perform the partial match with the help of Google Sheets VLookup Wildcards.
  • 2nd Step: Now on the homepage, move to the cell where you would like to extract the results.
  • 3rd Step: Simply enter the formula =VLOOKUP(“*”&C2&”*”,$A$2:$A$6,1,FALSE) – {modify the formula as per your datarange}

wildcards-google-sheets

  • 4th Step: Now press the Return key and you will see the result.
  • 5th Step: Simply drag the fill handle and you will see the partial matches being loaded as shown in the image below.

wildcards-google-sheets

Google Sheets VLookup Wildcard in Table Array Formula Explanation

Rather than utilizing the standard VLOOKUP formula “=VLOOKUP(C2,$A$2:$A$6,1,FALSE)”, We used wildcards by placing an asterisk on both sides of the cell reference C2, as seen below.

=VLOOKUP(“*”&C2&”*”,$A$2:$A$6,1,FALSE)

This ensured that the VLOOKUP function searched the source table for any text that contained the term in cell C2, regardless of whether it was preceded or followed by certain letters.

As a result, the formula will seek a match, and if one is found, it will provide the whole URL for the given term.

How To Save Data on Google Sheets? – Save Data in Google Sheets using Apps Script

Google Sheets is known for accessing data from anywhere at any time on any device because of which saving data in spreadsheets is extremely beneficial. But if someone edits the file, then the savings might be lost and the data in the Sheets might be transformed to a new version. This is the reason, we need to save our data in Google Sheets. We can easily save the data in the Google Spreadsheet using Apps Script. On this page, let us understand everything about how to save data in a spreadsheet using PHP using Google Sheets Tips. Read further to find more.

Table of Contents

How To Save Data in Google Sheets?

To save the data in Google Spreadsheet, we need to write a short script that is provided on this page. To get this done, we will assume an example where we want to fetch the published posts of SheetsTips into a Google Spreadsheet and save it in a Google Sheets. To get this done, we can simply use the “IMPORTFEED” function and fetch the data as shown in the image below:

importfeed-google-sheets

Creating Save Data Function Using Apps Script to Save Data in Google Sheets

Now we have to save the data and to get this done, we need to write a shortcode in Apps Script. The steps to save the data in the Google Sheets are as shown below:

  • 1st Step: Open the Google Sheets on your device,
  • 2nd Step: Now on the homepage, in cell A1, import the data which you would like to save. Here we are using Sheetstips blog post data.
  • 3rd Step: Now in Cell B1, enter the formula =now() for the timestamp purpose.

how to save data in google sheets

  • 4th Step: Here you will be shown the time. Now click on the “Extensions” tab and choose “Apps Script” from the drop down menu as shown in the image below.

how to save data in google sheets

  • 5th Step: In your browser, this will open a new tab. In this window, delete the existing code such as (function increment() { SpreadsheetApp.getActiveSheet().getRange(‘d10’).setValue(SpreadsheetApp.getActiveSheet().getRange(‘d10’).getValue() + 1); })
  • 6th Step: Now simply paste the code tabulated below and in the Apps Script window.
// custom menu function
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Custom Menu’)
.addItem(‘Save Data’,’saveData’)
.addToUi();
}// function to save data
function saveData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var url = sheet.getRange(‘Sheet1!A1’).getValue();
var follower_count = sheet.getRange(‘Sheet1!B1’).getValue();
var date = sheet.getRange(‘Sheet1!C1’).getValue();
sheet.appendRow([url,follower_count,date]);
}

how to save data in google sheets

  • 7th Step: Click on the “Save Project” button.
  • 8th Step: Now return to the spreadsheet and refresh your spreadsheet by clicking on F5.
  • 9th Step: Here you will see a new tab “Custom Menu” as shown in the image below.

how to save data in google sheets

  • 10th Step: Click on the “Custom Menu” tab and choose “Save data” from the drop down menu.
  • 11th Step: Now the script will start running and the spreadsheet will ask for your permission. Now allow the permission by clicking on the “Continue” button.

how to save data in google sheets

  • 12th Step: Now provide all the necessary access by clicking on the “Allow” button. 
  • 13th Step: When it’s finished, it will add a new row beneath your existing entries with a copy of row 1’s data. You may now re-run this at any moment, and it will store a copy of the current settings and timestamp to the same file.

how to save data in google sheets

How To Automatically Save Data in Google Sheets?

To automatically save the data in Google Sheets, we need to follow the steps given below:

  • 1st Step: Return to your script editor.
  • 2nd Step: Select Edit > Triggers for the current project.
  • 3rd Step: The triggers dashboard window appears now. In this window, in the bottom right corner, click + Add Trigger.
  • 4th Step: Select the Save Data option from the first drop-down menu.
  • 5th Step: Select Head, then Time Driven, and then the time period you are interested in for the remaining drop down selections.
  • 6th Step: After some time, you should have historic data in your spreadsheet. You may now need to refresh your browser to reload the sheet.

The above steps will help Google Sheets to save the data automatically.

How To Add Subscript and Superscript In Google Sheets? (3 Easy Methods)

When we are working with Google Sheets, we might need to add chemical formulas or statistical data for some or other purposes. However, adding subscript or superscripts in Google Sheets is not frequent since we don’t use this feature regularly. Though this is not a commonly used feature, we might fall under circumstances where we might need to add Subscript or Superscript.

As Google Sheets doesn’t have an inbuilt feature of inserting subscript or superscript, we can simply get this done using mathematical formulas. On this page let us understand everything about how to add subscript and superscript with the help of Google Sheets Tips. Read further to find more.

Table of Contents

What are Subscript and Superscript?

Subscripts and superscripts are smaller characters than the body of the book. Superscripts are placed above the normal text, whereas subscripts are placed below the normal text.

Example of Subscript and Superscript:

  • CH3 is a superscript
  • 4² is a superscript

Using Google Sheets CHAR() Function To Add Subscript and Superscript

We can simply add subscript and superscript in Google Sheets using the Character function. By incorporating ASCII code with the CHAR function, we can simply create subscript or superscript. Let us understand how to do this in the below section.

How To Add Subscript in Google Sheets?

Let us say, we need to add generate a subscript text as CH3 in Google Sheets. The steps to get this done are as follows:

  • 1st Step: Open the Google Spreadsheet.
  • 2nd Step: Now on the homepage, move to the cell where you need to create a subscript text – CH3.
  • 3rd Step: Here type CH normally.
  • 4th Step: To add 3 as a subscript, we need the ASCII code of 3. The subscript code of 3 is 8323.
  • 5th Step: Now type the formula as =char(8323).
  • 6th Step: To get the formula CH3 in the cell, type the formula =”H”&”H”&CHAR(8323).

Adding Subscript and Superscript in Google Sheets

That’s it, The subscript has been generated.

ASCII Codes To Add Subscript In Google Sheets

To add numbers as a subscript, use the codes tabulated below:

Character Code Function
0 8320
1 8321
2 8322
3 8323
4 8324
5 8325
6 8326
7 8327
8 8328
9 8329
+ 8330
8331
= 8332
( 8333
) 8334

To add characters as a subscript, use the codes tabulated below:

Character Code Function
a 8336
e 8337
o 8338
x 8339
i 7522
r 7523
u 7524
v 7525
y 7527

How To Add Superscript in Google Sheets?

The steps to add superscript in Google Spreadsheet are as follows:

  • 1st Step: Open the Google Spreadsheet where you want to insert the Superscript.
  • 2nd Step: Now on the homepage, move to the cell where you want to insert the Superscript.
  • 3rd Step: In this example, we are trying to add . Since we need to add 2 as superscript, we need to use the ASCII code of the 2. The ASCII code of 2 is 178.
  • 4th Step: Now type the formula =”4″&CHAR(178).

That’s it. The superscript has been added in the Google Sheets as shown below.

Adding Subscript and Superscript in Google Sheets

ASCII Codes To Add Superscript in Google Sheets

To add numbers as a superscript, use the codes tabulated below:

Character Code Function
0 8304
1 185 ¹
2 178 ²
3 179 ³
4 8308
5 8309
6 8310
7 8311
8 8312
9 8313
+ 8314
8315
= 8316
( 8317
) 8318

To add characters as a superscript, use the codes tabulated below:

Character Code Function
a 7491
b 7495
c 7580
d 7496
e 7497
f 7584
g 7501
h 688 ʰ
i 7588
j 690 ʲ
k 7503
l 737 ˡ
m 7504
n 8319
o 7506
p 7510
r 691 ʳ
s 738 ˢ
t 7511
u 7512
v 7515
w 695 ʷ
x 739 ˣ
y 696 ʸ
z 7611

Using Unicode Symbols To Add Subscript and Superscript in Google Sheets

Apart from ASCII codes, we can also add Subscript and Superscripts using the Unicode symbols. The steps to add Subscript or Superscript using the Unicode symbols are as follows:

  • 1st Step: Visit third-party websites such as www.unicode-table.com.
  • 2nd Step: Now on the homepage, click on the “See All” tab.

Adding Subscript and Superscript in Google Sheets

  • 3rd Step: Scroll down and move to the section “Superscript and Subscript Numbers“.

Adding Subscript and Superscript in Google Sheets

  • 4th Step: Click on the subscript or superscript which you would like to copy and click on the “Copy” button.

Adding Subscript and Superscript in Google Sheets

  • 5th Step: Now the Subscript or Superscript has been copied to the clipboard. Move to the Google Spreadsheet where you want to paste the Subscript or Superscript,
  • 6th Step: Press “Cntrl+Shift+V“. That’s it the subscript or superscript has been pasted in the Google Sheets.

Using Subscript or Superscript Generator in Google Sheets

The steps to add Subscript or Superscript in Google Spreadsheet using the Subscript and Superscript generator are as follows:

  • 1st Step: Open the search engine and type Subscript or Superscript generator. I mostly use the website “Subscript/Superscript Generator” – https://lingojam.com/SuperscriptGenerator.
  • 2nd Step: Now type the character or number which needs to be converted as Subscript or Superscript.
  • 3rd Step: The Subscript or Superscript is generated to the right side of the window.
  • 4th Step: Copy the character and move to the Google Spreadsheet where you want to paste.
  • 5th Step: Paste the character or number using “Cntrl+Shift+V“.

Adding Subscript and Superscript in Google Sheets

That’s it. The Subscript or Superscript has been inserted into the Google Sheets.

How To Create Multi Colored Line Charts in Google Sheets? (Multiple Color Chart in G-Sheets)

Anyone knows how to create line charts in Google Sheets. But do you know how to create Multi Colored Line Charts in Google Sheets? If not, then you don’t have to worry. In this tutorial, we will learn everything about how to create a multi colored Line Charts with the help of Google Sheets Tips and Tricks. Read further to find more.

Table of Contents

Prerequisites to Create Multi Color Chart in Google Sheets

To create a multi color line graph in Google Spreadsheet, we must first devise the dataset for which the line chart needs to be created. In this article, we are creating a dataset as shown in the image given below:

multi-colored-line-charts

So when you try to create a Line chart using the above dataset, we will simply get a Line Chart as shown below.

multi-colored-line-charts9

Now to create a Multiple Color Line Chart, we will need helper columns. The steps to create helper columns to generate the Multi Color Line Chart are as follows:

  • 1st Step: Move to the third column which are next to the first 2 columns/
  • 2nd Step: Here apply the formula “=if(or(B3>B4,B3<B2),B3,“”)“. 
  • 3rd Step: Now drag the formula applied cell to other cells to apply the same formula.

multi-colored-line-charts

  • 4th Step: In the fourth column, enter the formula “=if(or(B3=max(B2:B4),B3=min(B2:B4)),B3,“”)” and press the “Enter” button.
  • 5th Step: Now drag the formula applied cell to other cells to apply the same formula.

multi-colored-line-charts

That’s it, the helper columns have been attached. Now the dataset should like the following image.

multi-colored-line-charts

Steps To Create Multi Colored Line Chart

Now the dataset is ready. Let us start creating a Multi Colored Line graph. The steps to create a multi colored line graph are as follows:

  • 1st Step: Select the dataset for which you want to create a Multi Colored Line Chart.
  • 2nd Step: Now on the homepage, click on the “Insert” tab from the menubar.
  • 3rd Step: Choose “Charts” from the drop down menu.

multi-colored-line-charts

  • 4th Step: Based on the dataset, Google Sheets will automatically insert a chart or graph. Luckily, in this example, the Google Sheets have inserted the Multi Color Line Chart.

multi-colored-line-charts

  • 5th Step: However, if the multi color line chart has not been inserted by default, then we will change the chart style by moving to the Chart Editor section.
  • 6th Step: Under the Chart Editor pane, click on the “Setup” section.

multi-colored-line-charts

  • 7th Step: From the Chart Style drop down menu, choose “Line Chart” as shown in the image below.

multi-colored-line-charts

That’s it. The line chart has been inserted in the Google Sheets.

Customizing Multi Color Line Chart in Google Sheets

We can simply customize our Multi Color Line Chart by following the steps as outlined below:

  • 1st Step: Double click the chart or click on the chart and click on the 3 dots icon.
  • 2nd Step: Now choose “Edit Chart” from the drop down menu.
  • 3rd Step: In the Chart Editor window, move to the Customize tab.

multi-colored-line-charts

Based on the options available, customize your Multi Color Line Chart in Google Spreadsheet.

How To Create a Waterfall Chart in Google Sheets? (Stacked Waterfall Charts)

There are various types of charts that can be created on Google Sheets to make our data in a more easy and understanding manner. A waterfall chart could show the number of leads, traffic sources, or blog visits over a period of time. For example, a waterfall chart might be used to indicate how your blog traffic has climbed or dropped over the last year, with values given month by month. Waterfall charts are more or less like a line or graph chart but present the data in a more advanced manner.

So if you are looking at how to create a Waterfall Chart in Google Spreadsheet, then this page will tell you everything about it. With the help of Google Sheets Tips provided on this page, you will understand the simple steps to create a stacked waterfall chart. Read further to find more.

Table of Contents

How To Format Data in Google Sheets to Create Waterfall Chart?

In order to create a waterfall chart, first, we need to devise the data in the Google Sheets. The steps to format the data in Google Spreadsheet are as follows:

  • Fill up the first column with a label for each row. The horizontal axis displays the labels from the first column.
  • Other columns: Fill up the blanks with numbers. A category name can also be added here.
  • Rows: Each row on the chart represents a separate bar.

If you enter numeric data in two or more columns, you can choose whether to view the data sequentially or stacked.

So in this example, we have formatted our data as shown below:

waterfall-charts

Steps to Create Waterfall Charts in Google Sheets

The steps to create waterfall charts in Google Sheets are given below:

  • 1st Step: Open the Google Spreadsheet on your device.
  • 2nd Step: Now on the homepage, select the dataset for which you want to create waterfall charts.
  • 3rd Step: Click on the “Insert” tab and choose “Chart” from the drop down menu.

waterfall-charts

  • 4th Step: By default, the Google Sheets will insert a chart here based on your dataset. If it hasn’t inserted the waterfall chart, then move to the chart editor pane which is towards the right side of the screen.
  • 5th Step: Now in the Chart Editor window, move to the “Setup” section.
  • 6th Step: In the Setup section, click on the “Chart Type“.
  • 7th Step: Now various types of Chart options will open on the screen. Scroll down and move to the “Other” section.

waterfall-charts

  • 8th Step: Here choose the first option named Waterfall and that’s it. The waterfall chart has been inserted into your sheets.

waterfall-charts

Customizing Waterfall Charts In Google Sheets

Now the Google Sheets has picked up the default colors and created a Waterfall chart on its own. We can also customize the waterfall chart in Google Spreadsheet by moving to the “Customize” section under the “Chart editor” pane. The steps to customize the waterfall charts in Google Sheets are explained below:

  • 1st Step: Open the Google Spreadsheet where you would like to customize your waterfall chart.
  • 2nd Step: Now on the homepage, click on the waterfall chart which needs to be customized.
  • 3rd Step: Move to the “Customize” section.
  • 4th Step: Here you will find various options with the help of which you can customize your waterfall chart.
    • Chart style: Change the appearance of the chart, or add and edit connector lines.
    • Chart & axis titles: Edit or format the title text for the chart and axis.
    • Series: Change column colors, add and edit subtotals, and data labels in this series.
    • Legend: Change the position and content of the legend.
    • Horizontal axis: Reverse axis order or edit or format axis text.
    • Vertical axis: Edit or format the axis text, specify the min and max value, or use the log scale on the vertical axis.
    • Gridlines: You can add and edit gridlines in this section.

waterfalls-chart-in-google-sheets5

How To Reverse Text in Google Sheets? Write Name Backwards with Formula

Google Sheets are not only used to perform financial or budget analysis operations but also used to perform some fun operations such as reversing text, rotating the text, and so on. Hard to believe right? Yes with the help of Google Sheets, we can perform various operations which are useful for professional and unprofessional purposes. Reversing text is such a feature that is not widely used by every spreadsheet user but when it comes to creating innovative things such as creating quizzes, puzzles, patterns we can use this feature. However, there is no built-in option in Google Sheets like we have text rotation. But still, with the help of formulas, we can simply reverse the texts in Google Sheets.

On this page, let us understand how to reverse the text in a spreadsheet using the Google Sheets Tips. Read further to find more.

Table of Contents

What is the Formula to Reverse Text in Google Sheets?

The Google Sheets formula to reverse text are is given below:

Google Sheets Reverse Formula: =ArrayFormula(IFERROR(PROPER(CONCATENATE(MID(A1,LEN(A1)-ROW(INDIRECT(“1:”&LEN(A1)))+1,1))),””))

An alternative formula for Google Text Reverse: =JOIN(“”,ARRAYFORMULA(MID(A1,LEN(A1)-ROW(INDIRECT(“1:”&LEN(A1)))+1,1)))

How to Reverse Names in Google Sheets with Example?

The step by step to reverse a text or names in Google Spreadsheets are explained below:

  • 1st Step: Open the Google Sheets on your device.
  • 2nd Step: Now enter the names or the text which you would like to reverse. In our example, we are using column A.
  • 3rd Step: Then move to the cell where you want to reverse the entered name or text. In our example, we are using column B to reverse the texts that are entered in column A.
  • 4th Step: Now simply enter the formula =ArrayFormula(IFERROR(PROPER(CONCATENATE(MID(A1,LEN(A1)-ROW(INDIRECT(“1:”&LEN(A1)))+1,1))),””)). Here we are using A1 since, it is the column where we want to reverse the text.
  • 5th Step: Press the “Enter” button and you will see the text is reversed. Also now you will have the auto fill feature enabled, click on the tick button to auto fill.

reverse-text

  • 6th Step: If it is not enabled, then drag the formula applied cell to other cells using the fill handle and this will simply reverse the text in Google Sheets.

reverse-text

Alternative Formula to Reverse Text

With the help of alternative formula, we can simply reverse the text. Here is another option that also reverses the capitalization.

reverse-text

Working of Reverse Text Formula In Google Sheets

Essentially, we create an array of integers equal to the number of letters in the original text string. Then we reverse the order such that the greatest number appears first in the array. Then each letter in that position is extracted. As a result, the greatest number extracts the final letter, the second largest extracts the second-to-last letter, and so on, until the smallest number extracts the first letter. The separate letters are then concatenated.

Google Sheets Features 2021: Check Five Most Commonly Used Functions in Google Sheets

Google Sheets is free spreadsheet software that can be accessed using the Chrome web browser or the Google Sheets app for Android and iOS. To get started with Google Sheets, users will simply need a free Google account.

All the latest features and updates will be updated in Google Workspace and anyone can access it to know all the functions of the Google Sheets. Even if we read the Google Workspace updates regularly, we might tend to forget or use the features while working in the Google Sheets. However, there are few functions in Google Spreadsheet which are used most often. Are you wondering what are those functions? If yes, then this page is for you. This article will tell you everything about Google Sheets Tips and their advanced features. Read on to find more.

Table of Contents

What Are the Features of Google Sheets?

The 5 best features of the Google Sheets are explained below:

1. Editing

One of the most useful features of Google Sheets is the ability to edit spreadsheets collaboratively in real-time. Rather than sending several copies of a document via email, a single document can be opened and changed by multiple individuals at the same time. All modifications made by other contributors are visible to users, and all changes are automatically stored to Google servers.

A sidebar suggestion or comment feature in Google Sheets allows collaborators to discuss edits in real-time and give recommendations on specific changes. The Revision History tool allows you to trace any changes made by your collaborators. An editor can go back over previous revisions and undo any changes they don’t like.

how to highlight blank and error cells in google sheets2

2. Explore

Google Sheets Explore feature was originally introduced in September 2016, and it uses machine learning to provide new functionality. This function delivers a great deal of information based on the data entered into the spreadsheet, and it can automatically update itself based on the data entered.

Users can utilise the Explore feature to ask questions, generate charts, visualise data, construct pivot tables, and color-code the spreadsheet. For example, if you are creating a monthly budget and have entered all of your spendings into the spreadsheet, you may utilise the Explore function to find out how much food, travel, and clothing cost.

There is a box on the sidebar where you may type your question and get an answer. A list of suggested graphs that are indicative of the data input in the spreadsheet appears as you scroll down the Explore panel and that you can choose a pivot table, bar graphs, or pie chart.

gain-insights-in-sheets-data-via-explore

3. File Formats

Google Sheets accepts a variety of spreadsheet file formats and types. Google Sheets allows users to open, edit, save, and export spreadsheets and document files. The following are some of the formats that may be seen and changed in Google Sheets:

.xlsx .xltxm
.xls .ods
.xlsm .csv
.xlt .tsv
.xltx

4. Integrating Google Sheets with Other Products

Other Google products, such as Google Forms, Google Finance, Google Translate, and Google Drawings, can be connected with Google Sheets. For example, if you want to make a survey or inquiry, you can use Google Forms to generate the queries and then integrate the Google Forms into Google Sheets.

how-to-use-google-translate-directly-in-google-sheets

5. Offline Editing

Offline editing is available in Google Sheets, and users can edit the spreadsheet on their computers or mobile devices in offline mode. To allow offline editing for Google Sheets and other Google applications on the desktop, users must use the Chrome browser and install the “Google Docs Offline” Chrome extension. Users must utilise the Google Sheets mobile app for Android and iOS, which supports offline editing, while using a mobile device.