Radial Bar Chart in Google Sheets Example – Learn How To Make Radial Chart in Spreadsheet

The Google Sheets Radial Bar Charts look great when you want to grab the attention of users. However, the radial bar charts in Google Sheets should be designed carefully since they are hard to read than a regular chart. This is because it is difficult to measure the lengths in Google Sheets. And that is why here is an article, which tells you everything about how to design a radial bar chart in Google Sheets with simple G-Sheet tricks. Read further to find more.

Table of Contents

 

How To Create Radial Bar Charts in Google Sheets?

In order to create a Radial Chart in Spreadsheet, we need to have a dataset.

For these three series, such as items with a number of units sold, we’ll require a column of values.

Following that, we’ll need an upper limit (maximum value) for our bars. This helps us to appropriately scale the bars.

Radial Bar Chart in Google Sheets

Finally, we require a helper column that computes the difference between the maximum and actual value. So our dataset for creating a radial bar chart is shown below:

Steps to Create Radial Bar Chart in Google Sheets

Follow the steps as outlined below to create Radial Bar Charts in Google Spreadsheet.

Step 1: Creating Inner Circle

The step towards creating a Radial Bar Chart is to create an inner circle.

  1. The first row of data should be highlighted, but the maximum value column should be left out.
  2. Now click on the Chart icon in the menubar. By default, Google Sheets will come up with a chart.
    Radial Bar Chart in Google Sheets
  3. Under the Chart Editor Pane, move to the Setup section.
  4. Click on the Chart Type drop-down menu and choose “Doughnut Chart“.
  5. Under Customize, make the following settings to the Doughnut Chart you have created.
    • Color of the background: none
    • Color of chart border: none
    • 67 percent of doughnut holes size.
    • Set the color of Slice 2 to none.
    • Remove the chart title and set none to the legend.
  6. Now the inner radial bar chart will look like the following image. That’s it the inner circle has been created in Google Sheets Radial Chart.

Radial Bar Chart in Google Sheets

Step 2: Creating Middle Circle

For the inner circle, repeat the steps above, but use the next row of data, a new color, and a 77 percent doughnut hole size. You may need to work about these percentages to get everything to line up in the end.

To produce the same, drag the second doughnut chart on top of the first and align the radial bar. So that our chart will look like the following image.

Radial Bar Chart in Google Sheets

Step 3: Creating Outer Circle

To make a third doughnut chart, repeat the procedures above from the inner circle, but this time use the third row of data, a change in color, and set the doughnut hole size to 81 percent. This may need to be tweaked to match everything up.

You can make a radial bar chart in Google Sheets by dragging this third doughnut chart on top of the previous two.

Because the charts are stacked on top of one another, you will only be able to alter the top chart. To get to the chart below, you will have to move it to the side, and then move that one if you want to go to the inner chart.

Radial Bar Chart in Google Sheets

Add the Data Labels in Google Sheets

Because using the chart editor to add data labels to each chart gets messy, I decided to use formulas to add my data labels to the cells adjacent to each bar of the radial bar chart.

Click on a cell outside of the chart area to view cells under the charts, then use the arrow keys on your keyboard to go to the required cell.

Add the following formula after that:

=E6&”: “&TEXT(F6,”#,0”)

In Google Sheets, the TEXT function is used to merge text and numbers.

Alongside each bar, the name of the series and its value are displayed.

To finish, remove the gridlines from your Sheet to give the chart a more professional appearance.

Subtotal Function in Google Sheets – How to Subtotal in Google Sheets?

The Google Sheets Subtotal function will enable users to subtotal the vertical range of cells using a specified aggregation function. A total of 11+ functions are available in Google Spreadsheet, with the help of which we can perform the subtotal operation. On this page, let us understand how to perform a subtotal function using Google Sheets Tips and Tricks. Read further to find out more.

Subtotal Function Syntax in Google Sheets

The syntax of Google Sheets’ Subtotal function is as follows:

Subtotal function syntax=(function_code, range1, [range2,…])
  • Function_Code: The function code here specifies the function to be used in subtotal aggregation.
  • Range 1: This specifies Range 1 for which the subtotal needs to be calculated.
  • Range 2: Additional ranges which we can specify to calculate the subtotals.
Function Code (includes hidden values) Code (ignores hidden values) Function Code Meaning
AVERAGE 1 101 The AVERAGE function returns the numerical average value in a dataset, ignoring the text.
COUNT 2 102 Returns the number of numeric values in a dataset.
COUNTA 3 103 Returns the number of values in a dataset.
MAX 4 104 Returns the maximum value in a numeric dataset.
MIN 5 105 Returns the minimum value in a numeric dataset.
PRODUCT 6 106 Returns the result of multiplying a series of numbers together.
STDEV 7 107 The STDEV function calculates the standard deviation based on a sample.
STDEVP 8 108 Calculates the standard deviation based on an entire population.
SUM 9 109 Returns the sum of a series of numbers and/or cells.
VAR 10 110 Calculates the variance based on a sample.
VARP 11 111 Calculates the variance based on an entire population.

How to Use the Subtotal Function in Google Sheets?

Let us understand more about the Subtotal function in Google Spreadsheet using an example. Consider the following dataset where we have student grade reports:

Student ID Student Name Major Class Year Midterm Grade Final Grade
N1304 David Computer Science 2012 78 81
N1008 Jason Math 2011 87 80
N1866 Mary Computer Science 2012 79 80
N1774 Rob Computer Science 2012 90 85
N1365 Jason Math 2011 90 96

Now let us perform a few subtotal functions such as AVG, Max, Min, STDEV, SUM, and VAR with the students’ grade reports. The steps to get this done in Google Sheets are as follows:

  • 1st Step: Open the Google Spreadsheet on your device.
  • 2nd Step: Now copy-paste the student grade report for which you want to perform the subtotal function.

SUBTOTAL function in Google Sheets

  • 3rd Step: Next, move to the cell where you want to perform the subtotal function. Here I am creating a dataset for the calculation of subtotals, as shown in the image below.

SUBTOTAL function in Google Sheets

  • 4th Step: Now in the “Result” column, enter the formula as =SUBTOTAL (B10, $F$2: $F$6).
  • 5th Step: Press the “Return” key and you will see the results as follows.
  • 6th Step: Now drag the formula-applied cells to other cell ranges and you will see the subtotals being calculated as shown below.

SUBTOTAL function in Google Sheets

How to Create a Subtotal Function for Hidden or Filtered Data?

Let’s say we have a huge data collection that has been classified and filtered by the department. To review the data in our reports, we have total cells. The purpose is to have the total cells adjusted as an outcome of the filter. So, if we’re looking at data about electronics, the total number of cells must reflect that.

Also, if a row is hidden, we want the data cells to reflect that. The first thing to keep in mind is that the SUM function does not adapt when the data is filtered or hidden. The converse is true with the SUBTOTAL function.

To do this, we select the green filter drop-down at the top of the Department column. Select the department where you would like to show up. You will now see that the data has changed, and many rows are filtered.

You will also notice that the SUM cells in the table below are unchanged. However, the filtered cells that use the subtotal function are adjusted only to show unfiltered cells.

SUBTOTAL function in Google Sheets

REPT Function In Google Sheets – How To Repeat Rows in Google Sheets?

Google Sheets REPT function will return the text specified times repeatedly. For example, if you want to repeat the term Sheets Tips, thrice, then we can simply use the REPT function to get this done. On this page, let us understand how to use the REPT function in Google Spreadsheet using the Google Sheet Tips provided on this page. Read further to find more.

Table of Contents

Sheets Tips

REPT Function Syntax in Google Sheets

To use the repeat function in Google Spreadsheet, we will have to use the syntax. The syntax of the REPT function is as follows:

=REPT(text_to_repeat, number_of_repetitions)

  • text_to_repeat: The letter or string to be repeated is text to repeat.
  • number_of_repetitions: Indicates how many times text to repeat should appear in the returned response.

The number of repetitions cannot exceed the 32,000-character maximum of a cell. REPT will return a #VALUE! error if the number of repetitions is larger than 32,000 characters.

How to Use REPT Function in Google Sheets?

Let us say, we want the term Sheets Tips to be repeated in Google Sheets thrice in a cell. We can get this done in Google Sheets.

Follow the steps as outlined below to use the REPT function in Google Spreadsheet.

  • 1st Step: Open the Google Spreadsheet on your device.
  • 2nd Step: Now on the homepage, move to the cell where you want the term to get repeated.
  • 3rd Step: Enter the formula =REPT(“Sheets Tips “, 3).
  • 4th Step: Press the “Return” key and you will see the results as shown below.

REPT Function in Google Sheets

If you observe, I have provided extra space after the term “Sheets Tips”. If I remove the space and apply the formula, then the REPT function will print the term without providing any spaces.

Using REPT Function to Repeat Numbers in Google Sheets

Google Sheets not only repeats the characters but also helps to repeat the numbers. To repeat the numbers in Google Spreadsheet, follow the steps outlined below:

  • 1st Step: Launch the Google Spreadsheet on your system.
  • 2nd Step: In the Sheet, head to the cell range where you want to apply the REPT function to a number.
  • 3rd Step: Now enter the formula =REPT(“4”, 8). Here I am using the number 4 to repeat 8 times using the REPT function.
  • 4th Step: Press the “Enter” button and you will see the results.

REPT Function in Google Sheets

Using REPT Function to Repeat Images in Google Sheets

Google Sheets also allows users to repeat images using the REPT function. The steps to get this done in Google Spreadsheet are as follows:

  • 1st Step: Open the Google Spreadsheet.
  • 2nd Step: Copy the IMAGE URL which you would like to repeat. Here I am using the Google Logo. So my formula will be “=ArrayFormula(IMAGE(SPLIT(REPT(“https://www.google.com/favicon.ico”&”♕”,5),”♕”)))”.
  • 3rd Step: Press the “Return” key and you will see the results.

Note: The Queen symbol “♕” is added at the end of the repeated value to serve as a unique value for the SPLIT function when it divides the string.

REPT Function in Google Sheets

Using REPT Function to Repeat Charts In Google Sheets

We can also easily generate the bar charts within the cell in Google Spreadsheet using the REPT function. The steps to generate the bar chart in Google Sheets using the REPT function are as follow:

  • 1st Step: Open the Google Spreadsheet on your device.
  • 2nd Step: Now on the homepage, in column A, enter the number of times a bar chart needs to be repeated.
  • 3rd Step: In column B, enter the REPT function formula (=REPT(CHAR(10074),A1)) as shown in the image below.
  • 4th Step: Press the “Return” key and you will see the results as shown below.

REPT Function in Google Sheets

Few Other Examples of REPT Function

A few other examples using the REPT function in Google Sheets are shown below.

REPT Function in Google Sheets

How To Add a Total Row in Query Function Table in Google Sheets?

This tutorial will talk everything about how to add total rows to the tables generated using the query function. Instead of using the array formulas, on this page, we will use the pair of curly braces with the help of which we will add the total row in a query function.

Well, most of you will be thinking that we can simply write the word Total and use the SUM(range) formula to draw the total results. One must note that this is the manual step. In order to create the dynamic total row in Google Sheets, we can simply use the {….} notation in an array formula. Let us understand how to do this in Spreadsheet using the Google Sheets Tips and Tricks. Read further to find more.

Google Sheets Query Total

We will use the same example which we have used in the Onion method for complex formulas.

In that example, what we would want to do is merge all of the organization rows into a single row with a total count for that organization. We are “grouping” our data into the categories in column A, then adding up all of the values in column B that belong to each group.

To do so, we combine the number of positions data for each agency using the QUERY function and a group by clause. The formula which we used for achieving this is as follows:

=QUERY($A$11:$B$61,”select A, sum(B) group by A order by sum(B) desc label sum(B) ‘Total Positions'”,1)

By applying this formula in Google Sheets we would have got the output as shown in the image below.

query-total-row

Now let us discuss how to add dynamic total in the Google Sheets Query function.

How Do You Add a Total Row in a Query Function?

So, we are doing the same thing as the simple example before, which is to create two different tables. One is a summary table, such as the one seen above, and the other is a total row, which we merge using an array formula.

The steps to get this done in Google Sheets are as follows:

  • 1st Step: Move to the cell where you want to add a total row.
  • 2nd Step: Now modify the previously used formula with the use of the Total function. So our formula here is
={
QUERY(
$A$11:$B$61,
“select A, sum(B) group by A order by sum(B) desc label sum(B) ‘Total Positions'”,
1
)
;
{
“TOTAL”,
SUM($B$11:$B$61)
}
}
  • 3rd Step: Press the “Return” key and now you will see the results as shown below.

query-total-row

Last Step to Make the Total Row in Query Function Dynamic

This is the final step where we want to generate the query function into a dynamic. As the table expands or contracts as we add or delete data, the total row goes up or down without any manual changes.

There are two things we must achieve:

  1. Change the range to include all of columns A and B, and
  2. Change the QUERY function to eliminate the blank row that appears after step 1.

Remove the references from the range so that all columns A and B are included.

query-total-row

Then add a WHERE clause to the QUERY function to get away from all the blank rows in column A. So our formula here is

={
QUERY(
$A$11:$B,
“select A, sum(B) where A is not null and A like ‘DEPT%’ group by A order by sum(B) desc label sum(B) ‘Total Positions'”,
1
)
;
{
“TOTAL”,
SUM(QUERY($A$11:$B,”select B where A like ‘DEPT%'”,1))
}
}

Enter the above formula and press the return key and you will see the results.

query-total-row

Onion Method for Complex Formulas in Google Sheets (Chain Rule)

A technique where we apply chain rule multiple times is known as Onion Method. This technique is called as Onion method since we perform one action per one step which is more or less like peeling the Onion. This Onion method comes in handy when we build complex formulas in Google Sheets. If you are unaware of how to adapt the Onion method to build complex formulas in Google Sheets, then this will tell you everything about it. Using the Google Sheets Tips and Tricks provided on this page, we can easily build complex problems in a spreadsheet.

Sheets Tips

How To Build Onion Method for Complex Formulas?

To build an onion method for complex formulas, we will have to follow the one action per step approach. This means we will have to build our formulas in a series of steps.

Confused about how to do this? Well, don’t worry. We have outlined how to build formulas using the Onion Framework here.

Onion Framework consists of three important elements and they are outlined below:

  • Firstly outline the step-by-step formula that you will be using in your Google Sheets and put each formula in each of the cells.
  • Now label the formulas with the step number in the adjacent cell. For example, if you want to use the IF function formula in the third step, you can simply name it Step 3.
  • Then enable the different background colors against each formula cell to make it more identical.

This allows you to observe the formula move in a step-by-step manner, which is extremely useful when creating or trying to grasp complex formulas in Google Sheets.

Example: Build Complex Formulas in Google Sheets using Onion Method

Let us consider the following example where we have the organization’s name and list of available positions. If you see the dataset, it is unorganized.

complex-formulas-onion-method

We can organize this dataset with the help of complex formulas using the Onion framework and summarise the job openings. The steps to get this done are outlined below.

Step 1:

Firstly let us use the Query function to summarise the data. Since we have two columns – A, B, we will select these two columns using the Query function. The formula to select these two columns using the Query function is as follows:

=QUERY(A1:B,”select A, B”)

Although this has no effect on the data, it’s usually a good idea to run a simple query first to confirm you’re using the proper dataset as the input to your QUERY function.

complex-formulas-onion-method

Step 2:

Now we are supposed to summarise the job position in Google Sheets using the GROUP BY clause. We are using the GROUP BY clause inside the QUERY function. The formula to get this done is Google Sheets is as follows:

=QUERY(A1:B,”select A, sum(B) group by A”)

complex-formulas-onion-method

Step 3:

Now the next step is to filter out the blank rows. To do this, we will have to use the WHERE clause. This WHERE clause will be used inside the query function and GROUP BY clause as shown below:

=QUERY(A1:B,”select A, sum(B) where A is not null group by A”)

complex-formulas-onion-method

Step 4:

Now the next step is to use the ORDER BY clause. The ORDER BY clause is used here to sort the selected dataset in descending order. So our formula in the 4th step will be as follows:

=QUERY(A1:B,”select A, sum(B) where A is not null group by A order by sum(B) desc”)

complex-formulas-onion-method

Step 5:

In this step, we will have to fix the header of the total column using the LABEL clause. The formula to be used in this step is as follows:

=QUERY(A1:B,”select A, sum(B) where A is not null group by A order by sum(B) desc label sum(B) ‘Total Positions'”)

Press the Return key after using this formula in Google Sheets and you will see the results being generated as shown in the image below.

complex-formulas-onion-method

Rather than utilizing a pivot table, we used the QUERY function to generate one. The key to making this work was to build it in steps, with the formula changing slightly with each step.

Step 6:

In this step, we are using the array literals. For this, we need to add a placeholder line in the total row and the formula for the same is as follows:

={QUERY(A1:B,”select A, sum(B) where A is not null group by A order by sum(B) desc label sum(B) ‘Total Positions'”);{“TOTAL”,”TBC”}}

complex-formulas-onion-method

Step 7:

To get the right total, we need to convert this placeholder into an actual formula. We leave the range reference open-ended, much as the data provided to the query function, to guarantee that it remains dynamic and will automatically incorporate fresh data. So our formula in this step will be as follows:

={QUERY(A1:B,”select A, sum(B) where A is not null group by A order by sum(B) desc label sum(B) ‘Total Positions'”);{“TOTAL”,SUM(B1:B)}}

complex-formulas-onion-method

Now that you will have an idea of how to build complex formulas in Google Sheets using the Onion Method Framework. If you are attempting to figure out complicated formulae in Google Sheets that someone else has provided with you, you can still use the Onion Method.

Simply peel back the layers until the deepest function is disclosed. Copy the formula into a new cell and work your way up to the whole formula again, starting from the inside.

Pi Function in Google Sheets: Discover & Generate π Function in Google Sheets

Google Sheets allows users to even perform the most complex mathematical calculations. We can also use this software to perform Circle related calculations, such as finding circumference, diameter, area of a circle, and many others. However, to perform Circle related calculation, we will have to use the Pi (π) functions. Pi (π) is one of the most important mathematical operations which is widely used in Circle related calculations. On this page, we will understand how to use the Pi function to find the Circle related calculations using Google Sheets Tips. Read further to find more.

Sheets Tips

Pi Function for Circle Calculations – Syntax

To enable the Pi function in Google Sheets, we will have to use the syntax. The syntax to use the Pi function in Google Sheet is as follows:

=PI()

It is to be noted that there are no arguments in between the brackets. If you use any arguments in the bracket, then the Pi function will return an error.

Using Pi Functions in Mathematical Calculations

In order to perform mathematical calculations, you will have to know the circumference of the circle. If you know the circumference of the circle, then you can easily calculate the diameter and other calculations of the circle.

Calculating Circle Circumference in Google Sheets using Pi Function

To calculate the circumference of the circle in Google Sheets, you will have to know the diameter of the circle. Let us assume that you want to find the circumference of the circle, whose diameter value is 20 cm. The steps to find the circumference is as follows:

  • 1st Step: Open the Google Sheets where you want to calculate the circumference of the circle.
  • 2nd Step: Move to the cell where you want to draw the results.
  • 3rd Step: Now simply enter the formula = 20 * PI().
  • 4th Step: Press the Return Key and you will see the results as shown below. The value returned is the circumference of the circle.

pi-function

Calculating Circle Diameter in Google Sheets using Pi Function

To evaluate the diameter of the circle, we will have to know the circumference value. Suppose if your circumference value of the circle is 20cm, then you simply follow the steps outlined below to know the diameter of the circle.

  • 1st Step: Launch the Google Spreadsheet on your device.
  • 2nd Step: Now move to the cell where you want to find the diameter value of a circle.
  • 3rd Step: Now use the formula =20*PI().
  • 4th Step: Press the “Enter” button and you will see the results as shown in the below image.

pi-function

Calculating Area of Circle in Google Sheets using Pi Function

Google Sheets also allows the user to calculate the area of the circle using the Pi function. To calculate the area of the circle using the Pi function, we will have to first know the area of the circle formula. The area of the circle formula is π * r².

Thus we have to know the radius value. To find the radius of the circle, we will have to divide the diameter by 2. In the previous sections, we have learned how to find the diameter of the circle. Use those formulas to find the diameter of the circle.

Now let us assume that the diameter value of the circle is 20cm. The steps to find the area of a circle in Google Sheets using this data is as follows:

  • 1st Step: Open the Google Sheet on your device.
  • 2nd Step: Now move to the cell where you want to find the area of the circle.
  • 3rd Step: Enter the formula = PI() * ( 20 / 2 )^2.
  • 4th Step: Press the Return key and you will find the results as shown below.

pi-function

How to Make a PI Symbol in Google Sheets?

We can easily generate the PI symbol in a Google Spreadsheet using the CHAR function. Follow the steps as listed below to generate the PI symbol in Google Sheets:

  • 1st Step: Open Google Sheets on your device where you want to generate the PI symbol.
  • 2nd Step: Move to the cell where you want to generate the PI symbol.
  • 3rd Step: Enter the character function formula as =CHAR (960).
  • 4th Step: Press the “Enter” key and you will see the results as shown below. Here you will see the PI symbol being generated in Google Sheets.

pi-function

Fun Fact: Google Sheets also lets users generate different pies. Want to know how to do this. Well, follow the steps outlined below:

  • 1st Step: Move to the cell where you want to generate the pie in Google Sheets.
  • 2nd Step: Simply enter the formula =CHAR(129383).
  • 3rd Step: Press the return key and you will see the results.

Google Sheets Formula Clock – Know How To Make A Clock in Google Sheets

Have you ever wondered about creating a real time clock in Google Sheets? If yes, then you have landed on the right page. In this tutorial, let us learn how to create an analog clock in Google Sheets with the help of Google Sheets Tips. Scroll further to find more.

How To Create Google Sheets Time Clock Template?

It’s no surprise that you are thinking to use of Script code, Widgets, Add-Ons to create Real Time clock in Google Sheets. However, you got it wrong. But to create a clock in Google Sheets, all you have to do is to use a simple formula which we have provided on this page.

Steps to Build Real Time Clock in Google Sheets

The Steps to build real time clock in Google Spreadsheet are explained below:

  • 1st Step: Open a blank Google Sheet on your device. Alternatively, you can also create a new Google Sheet.
  • 2nd Step: Move to the Cell where you want to create a Real Time clock.
  • 3rd Step: Now simply copy-paste the following formula in the blank cell.
=SPARKLINE( ArrayFormula({ QUERY(ArrayFormula({ 0, 0, 1 + N(“See Comment 1”); 0, 0, 0.8 + N(“See Comment 2”) ; SEQUENCE(37,1,0,10), SIN(RADIANS(SEQUENCE(37,1,0,10))), COS(RADIANS(SEQUENCE(37,1,0,10))) + N(“See Comment 3”) ; SEQUENCE(12,1,30,30), 0.9 * SIN(RADIANS(SEQUENCE(12,1,30,30))), 0.9 * COS(RADIANS(SEQUENCE(12,1,30,30))) + N(“See Comment 4”) ; SEQUENCE(12,1,30,30), SIN(RADIANS(SEQUENCE(12,1,30,30))), COS(RADIANS(SEQUENCE(12,1,30,30))) + N(“See Comment 5”) ; SEQUENCE(4,1,90,90), 0.8 * SIN(RADIANS(SEQUENCE(4,1,90,90))), 0.8 * COS(RADIANS(SEQUENCE(4,1,90,90))) + N(“See Comment 6”) ; SEQUENCE(4,1,90,90), SIN(RADIANS(SEQUENCE(4,1,90,90))), COS(RADIANS(SEQUENCE(4,1,90,90))) + N(“See Comment 7”) }), “SELECT Col2, Col3 ORDER BY Col1”, 0 + N(“See Comment 8”) ) ; IF( MINUTE(NOW()) = 0, 0, SIN(RADIANS(SEQUENCE(MINUTE(NOW())/60*360,1,1,1))) ), IF( MINUTE(NOW())=0, 1, COS(RADIANS(SEQUENCE(MINUTE(NOW())/60*360,1,1,1))) ) + N(“See Comment 9”); 0, 0 + N(“See Comment 10”) ; 0.75 * SIN(RADIANS((MOD(HOUR(NOW()),12)/12 * 360) + MINUTE(NOW())/60 * 30)), 0.75 * COS(RADIANS((MOD(HOUR(NOW()),12)/12 * 360) + MINUTE(NOW())/60 * 30)) + N(“See Comment 11”) }), {“linewidth”,2 + N(“See Comment 12″) + N(” “)} )
  • 4th Step: Press the “Return” key. Initially, your Google Sheets clock will look like the following image.

Google Sheets Formula Clock

  • 5th Step: Now the next step is to make the row wider. For this place, the cursor in the row border and enlarge it using the fill handle. Enlarge the row as per your clock size.
  • 6th Step: Now the Real Time Clock has been enlarged in the Google Sheets.

Google Sheets Formula Clock

Now that we have created the Real Time Clock in Google Sheets. The next step is to make the clock tick.

Steps to Make Real Time Clock Tick in Google Sheets

In this step, we will have to make the real time clock to tick. The steps to make the clock tick has been provided below:

  • 1st Step: Click on the “File” tab in the menubar.
  • 2nd Step: Choose “Settings” from the drop-down menu.

Google Sheets Formula Clock

  • 3rd Step: Click on the Calculation section and move to the “Recalculation” section.
  • 4th Step: Under the Recalculation drop-down menu choose “On change and every minute“.

Google Sheets Formula Clock

  • 5th Step: Click on “Save Settings“.

Google Sheets Formula Clock

These steps will help us to refresh the NOW function that we have used in the formula every minute.

Now you must have noticed the hands of the clock moving every minute.

Formula Explanation of Real Time Clock

  • Line 1: The initial (0,1) coordinate at the top of the circle. A 0 was added for sorting.
  • Line 2: Coordinates for making a mark at 12 o’clock.
  • Line 3: The beginning circle’s coordinates. Joins mark every 10 degrees beginning at the top of the circle, e.g. 0, 10, 20, 30,…360.
  • Line 4: Coordinate sequence every 30 degrees to generate little markers for hours 1, 2, 4, 5, 7, 8, 10, and 11.
  • Line 5: Coordinate sequence for connecting the 30-degree tiny markers. It was necessary to appropriately put them on the circle.
  • Line 6: Coordinate sequence every 90 degrees to generate huge marks for hours 12, 3, 6, and 9.
  • Line 7: Coordinate sequence for connecting the 90-degree big markers. It was necessary to appropriately put them on the circle.
  • Line 8: The QUERY function was used to sort the circular data by the degrees column, then only the (x,y) coordinate columns (numbers 2 and 3) were utilized.
  • Line 9: Coordinates for making the minute hand An IF statement is included to prevent an error when the minute hand reaches the 12 mark.
  • Line 10: Coordinates to return to the center of the clock at (0,0) after drawing the minute hand, in order to create the hour hand.
  • Line 11: Coordinates for making the hour hand
  • Line 12: Set the Sparkline’s line width to 2.

How to Alphabetize Comma-Separated Strings in Google Sheets?

Google Sheets is an amazing tool that helps us perform incredible tasks. One of the incredible tasks that we can perform in Google Sheets is to arrange the strings in alphabetical order. To arrange the strings in alphabetical order, we will have to use various Google Sheets functions such as SPLIT, TRANSPOSE, and JOIN.
With the help of Google Sheets Tips, we will learn how to alphabetize the comma-separated strings. Read further to find out more.

Alphabetizing Comma-Separated Strings in Google Sheets

Consider the following dataset in Google Sheets:

How to Sort Alphabetically in Google Sheets and Keep Rows Together4

Now we need to arrange the following dataset in an alphabetical manner. The step-by-step procedure to get this done is explained in the following section.

How to Sort Alphabetically in Google Sheets and Keep Rows Together?

The simple steps to sorting the data in alphabetical order are as follows:

Step 1. The Split Function Is Used in the First Step

  • We have the list of strings arranged in Cell A1.
  • Now move to the cell where you want to alphabetize the string.
  • Enter the formula =SPLIT(A1, “,”) here and hit the “Return” key.
  • Now the data is being split as shown in the image below.

How to Sort Alphabetically in Google Sheets and Keep Rows Together4

Step 2: Making Use of the Transpose Function

Now we will have to use the Transpose function to change the row directly into the column direction. We need to use the transpose function in the row since we will have to use the SORT function. The steps for using the TRANSPOSE function in Google Sheets are explained below.

  • Move to the cell where you have used the SPLIT function.
  • Now modify the SPLIT function with the TRANSPOSE function and enter the formula as =TRANSPOSE (SPLIT (A1, “,”)).
  • Press the “Enter” button and now you will see the results.

How to Sort Alphabetically in Google Sheets and Keep Rows Together4

Step 3: Applying the SORT Formula

Now the next step is to SORT data using the SORT formula. To SORT data using the SORT function is as follows:

  • Move to the cell where you have applied the SPLIT and TRANSPOSE formulas.
  • Now modify the formula with the SORT function and enter the formula as =SORT(TRANSPOSE (SPLIT (A1, “,”)))
  • Press the “Enter” formula and you will see the results as shown below.

How to Sort Alphabetically in Google Sheets and Keep Rows Together4

Step 4: Using the JOIN Function

Now we come to the final step, with the help of which we can sort the data in alphabetical order. The steps to using the join function to arrange the data in alphabetical order are as follows:

  • Move to the cell where you have used the SORT Formula.
  • Now modify the formula using the JOIN function. The formula which needs to be adjusted is as follows: “=JOIN(“,”,SORT(TRANSPOSE(SPLIT(A1,“,”))))
  • Press the “Enter” button and you will see the results.

How to Sort Alphabetically in Google Sheets and Keep Rows Together4

That’s it. Our data is being sorted in alphabetical order, as shown in the image below.

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.