How to Calculate Days Between Two Dates in Google Sheets (With Examples)

With the help of Google Sheets, we can easily work with data and time functions and analyze the data in real-time. For example, if you want to calculate the number of days in between two dates, then you can easily perform the same with the help of Google Sheets without doing any manual calculations.

On this page, let us understand how to calculate the number of days between 2 dates along with the Google Sheets tips provided on this page. Read on to find more.

How to Calculate Total Days Between Two Dates?

Let us say you are working on a project and your manager has said that you will be working on this project from this date to this date. You can make use of Google Sheets to know how many days you will be working on this project. For this follow the steps as listed below:

  • Step 1: In the Google Spreadsheet, enter the start date of your project in Cell B1.
  • Step 2: In Cell B2, enter the end date of your project.

calculate-days-between-two-dates-google-sheets

  • Step 3: Now move to Cell B4 to know how many days you will be working on this project and enter the formula “=B2-B1”.
  • Step 4: Press the “Enter” button.

calculate-days-between-two-dates-google-sheets

Now you will see the results. This calculation is quite simple since we have just used the minus operator to know the number of days between two dates.

In this example, we have calculated the in-between dates between the start and the end date. For example, if you want to calculate the total number of days including the project start and end date, then you can simply replace this formula “=B2-B1+1” instead of “=B2-B1”

How to Calculate Number of Workdays Between Two Days?

Now let us say if you have Week-Offs on Saturday and Sunday. So if you want to calculate the number of working days excluding your week off from the given two dates, you can simply follow the steps as given below:

  • Step 1: In a Google Spreadsheet, enter your Project commencement date in Cell B1.
  • Step 2: In Cell B2, enter your project end date.
  • Step 3: Now move to the B4 cell and enter the formula as “=NETWORKDAYS(B1, B2)”.
  • Step 4: Press the “Enter” button and you will see the results in Cell B4.

calculate-days-between-two-dates-google-sheets

How to Calculate the Workdays Excluding Public Holidays?

The public or government holidays vary from country to country. So if you want to calculate the number of days excluding the public holidays in your project, you will need to list down the number of public holidays in a separate cell.

calculate-days-between-two-dates-google-sheets

After listing out the public holidays, follow the steps given below to calculate the workdays excluding the public holidays.

  • Step 1: In Cell B1, enter your project start date.
  • Step 2: In Cell B2, enter your project end date.
  • Step 3: Enter the number of public holidays in proper date format in the same sheet as shown in the image.
  • Step 4: Now move to Cell B4 and enter the formula as =NETWORKDAYS(B1,B2,E2:E27).
  • Step 5: Press the “Enter” button and you will results.

calculate-days-between-two-dates-google-sheets

How to Calculate the Workdays when Weekoffs are not Saturday and Sunday?

The Network function in Google Sheets will automatically assume that the week offs are Saturday and Sunday. However, the week off days may vary from country to country and also depending upon the project you are working on. In such cases, you can simply follow the steps given below to calculate the workdays between two dates.

  • Step 1: Enter the start date of your project in Cell B1.
  • Step 2: Enter the end date of your project in Cell B2.
  • Step 3: Now in Cell B4, enter the formula =NETWORKDAYS.INTL(B1,B2,7,E2:E27).Step 4: Press the enter key. You will see results.

calculate-days-between-two-dates-google-sheets

In the above formula, 7 represents Friday and Saturday as week off.

Refer to the list below to know the week off days based on the arguments. You can alter the formula using the arguments given below:

  • 1 = Saturday/Sunday are weekend days
  • 2 = Sunday/Monday are weekend days
  • 3 = Monday/Tuesday are weekend days
  • 4 = Tuesday/Wednesday are weekend days
  • 5 = Wednesday/Thursday are weekend days
  • 6 = Thursday/Friday are weekend days
  • 7 = Friday/Saturday are weekend days
  • 11 = Sunday is the only weekend day
  • 12 = Monday is the only weekend day
  • 13 = Tuesday is the only weekend day
  • 14 = Wednesday is the only weekend day
  • 15 = Thursday is the only weekend day
  • 16 = Friday is the only weekend day

How to Calculate Workdays with Non-Consecutive Off Days?

Assume you have a job that only requires you to work on certain days of the week (say, Monday, Tuesday, and Thursday).

Counting the number of working days between two dates gets considerably more difficult with this setup.

Thankfully, this was taken into consideration when the NETWORKDAYS.INTL function was created.

You must explicitly define which days are functioning and which are not when using the NETWORKDAYS.INTL method for something like this.

And you do that by utilising a string of seven digits in a row (where each number is either 1 or 0). The seven days of the week are represented by these numerals. Monday is represented by the first number in the series, Tuesday by the second, and so on.

It’s a working day if the number is zero, and it’s a non-working day if the number is one. So 0000011 denotes that the first five days of the week (Monday through Friday) are working days, while Saturday and Sunday are weekend days (or non-working days)

You may now build whatever week you like with a mix of working and non-working days. These don’t have to be in any particular order.

In our case, the code would be 0010111 because Monday, Tuesday, and Thursday are working days.

Also, as shown below, you may use this code in the NETWORKDAYS.INTL function:

=NETWORKDAYS.INTL(B1,B2,”0010111″,E2:E27)

calculate-days-between-two-dates-google-sheets

How to Calculate all Mondays between Two Dates?

You may just need to calculate the number of one single weekday rather than the number of working days.

Let us say you want to figure out how many Mondays there are in a certain calendar span (while also accounting for holidays).

This might be useful if you want to determine how many update calls will be made within the specified periods if the call takes place on Mondays.

Assume you have the following data collection and wish to calculate the number of Mondays between the start and end dates:

I will use the NETWORKDAYS.INTL function with a string of seven consecutive integers that indicate the days of the week to determine the number of Mondays.

This is only a variant of the previous example in which we calculated non-consecutive working days. We just need to identify Monday as the working day in this case, and the rest of the days are all weekend days.
As a result, the string of digits would be “0111111,” with 0 representing a Monday working day.

The formula below will calculate the number of working Mondays between the start and finish dates (while taking into account holidays):

=NETWORKDAYS.INTL(B1,B2,”0111111″,E2:E12)

calculate-days-between-two-dates-google-sheets

How to Analyze Data from Google Sheets with Examples

Google Sheets is a spreadsheet application that is part of Google’s Docs Editor suite. Google Drawings, Google Slides, Google Forms, Google Docs, Google Keep, and Google Sites are also included in this suite. Google Sheets provides you with the option of selecting from a wide range of pre-made schedules, budgets, and other spreadsheets that are designed to improve your job and make your life easier.

In this article, let us understand how to analyze the data in Google Spreadsheet with the hep of Sheet tips provided on this page. Read on to find more.

Table of Contents

Sheets Tips

Importance of Data Analysis in Google Sheets

In order to uncover patterns and trends from massive datasets, data analysts use complex statistical models and powerful computational techniques. Data analytics is mostly used to interpret data. A spreadsheet can help you collect and organize data, but it cannot assist you to grasp the subtleties of your data by itself.

To establish the regions of interest and derive meaningful actionable inferences from them, you will need to compare and filter the data with something. This is where the concept of functions comes into play. A quick check, for example, can give you an idea of which products in your portfolio of the company have already been performing well. However, Data Analysis is required to determine which goods are making more profits and growing sales.

This is why you need Google Sheets Data Analysis to make sense of your data to make data-driven actions to increase productivity and efficiency, hence improving business growth.

List of Data Analysis Function in Google Sheets

There are multiple data analysis functions in Google Sheets that help us to understand the dataset. However, on this page, let us understand 4 important data analyses which are extremely useful in Google Sheets.

VLOOKUP Data Analysis Functions in Google Sheets

Searching for information is a big part of Data Analytics. When you’re working with a single sheet with a few number items, the problem is really not apparent, and when you’re dealing with numerous spreadsheets with hundreds of lines of data, you will need a more efficient way of searching for information.

This problem is addressed by VLOOKUP. It stands for vertical lookup since it searches vertical columns for a certain value. As a result, it is recommended that you employ columns as fields to make VLOOKUP operations easier.

This function’s only drawback is that it requires a precise value. VLOOKUP should be avoided if you are seeking an approximate match.

Check Ultimate Guide for VLOOKUP Functions Here

ABS Data Analysis Function in Google Sheets

The absolute function is otherwise known as the ABS function in Google Sheets. This function is equivalent to modulo’s function. ABS function calculates a number’s absolute value while treating negative and positive numbers equally.

When ABS is applied to a group of cells containing a variety of numbers, the result is a consistently positive spread that differs only in magnitude, not polarity. This is especially important when combining data from multiple columns into a complex computation, as a single negative value might throw a wrench in your results.

Know How To Sort Data using Mulitple Functions in Google Sheets

INDEX and MATCH Data Analysis Function in Google Sheets

In the provided spreadsheets, the MATCH function can look for cells that contain an approximate value. It gives the target cell’s relative position inside a range, which is useful when sorting data.

The MATCH function works in conjunction with the INDEX function, allowing it to be used in place of the VLOOKUP function.

This is feasible since INDEX relays the value of the cell with the provided index, whereas MATCH allows you to locate the index of the value you require. Similar to the VLOOKUP function, you may use both of these functions to look for values in an area throughout the spreadsheet.

Know How to Use Index and Match Function in Google Sheets

Macros for Data Analysis in Google Sheets

Macros are user-defined functions that can do all manual activities. Macros are typically used to delegate any large-scale, repetitive job. As a result, Macros can assist you in reducing errors and freeing up time spent managing the spreadsheet.

Macros can be made in one of two ways: by scripting or by recording. Scripting macros require coding skills but give the user a lot of control and power. The simplest method of producing a macro is to record it; nevertheless, this method has several limitations.

You can start recording in a blank sheet by navigating to Tools > Macros > Recording Macro. This brings up a dialogue box where you can choose between two options:

  • Relative Reference: It adapts based on the cursor’s position. So, if you record a macro that edits the properties of two cells to the left of your cursor when the macro is called, it will affect those cells that are in the same relative position as your cursor.
  • Absolute Reference: This sort of reference saves the exact locations of the cells you alter, so the identical cells will be modified each time you execute the macro. As a result, this option is only relevant for configuring the header row or title cells.

Know How to Convert Excel to Google Sheets using Macros

How to Make Dot Plot in Google Sheets? Customizing Scatter Chart to Dot Plot

Google Sheets has various numbers of built-in charts such as Pie charts, Line charts, Bar charts. However, when it comes to Dot Plot, Google Sheets doesn’t have a built-in function for the same. And this is the reason we will have to build the Dot Plot chart in Google Sheets manually.

In this article, let us understand how to make a Dot Plot Chart with the help of a scatter chart along with the Google Sheet tips provided on this page. Read on to find more.

Table of Contents

What is Dot Plot in Google Sheets?

Dot plots are a fun and easy method to communicate very simple information. Instead of a solid bar, a dot plot would show distinct dots for each click. This is how a dot plot appears.

how-to-make-a-dot-plot-in-google-sheets

How to Create a Dot Plot in Google Sheets using Scatter Plot?

Consider the dataset which has surveying results as an example. When we insert a chart in Google Sheets, by default spreadsheet chooses a chart based on the dataset and displays the same as shown below.

how-to-make-a-dot-plot-in-google-sheets

 

But for creating a dot plot chart in Google Sheets, we need to create a scatter plot.  This means that first, we need to create a Scatter Plot chart in Google Sheets and then convert it into Dot Plot.

Now follow the steps as given below to create a scatter plot in Google Sheets:

  • Step 1: Select the dataset and copy it using Cntrl + C. Alternatively you can also right-click after selecting the dataset and select Copy from the sub-menu appearing on the screen.
  • Step 2: Now move to the adjacent cell and paste the selected data using Cntrl+V. Here we are copying data from Cell A & B and pasting it in Cell D & E respectively.
  • Step 3: Then modify the data entered in Cell E2 with the help of the formula “=Sequence(1, B2:B6)“.
  • Step 4: Now press the “Enter” button and you will see the results as shown in the image below.

how-to-make-a-dot-plot-in-google-sheets

  • Step 5: Now select the dataset in Columns D and E.
  • Step 6: Click on the “Insert” tab from the menubar and select “Chart” from the drop-down menu.

how-to-make-a-dot-plot-in-google-sheets

 

  • Step 7: By default Google Sheets will create a chart depending on your data. Now move to the Chart Editor Pane.

how-to-make-a-dot-plot-in-google-sheets

  • Step 8: Move to the Setup pane under the chart editor window.
  • Step 9: Click on “Chart Type” and select “Scatter” from types of charts in Google Sheets.
  • Step 10: By default, you will see dots being created on the Google Sheets. Now under the setup pane, move to the series.
  • Step 11: Click on the “Add Series” button.
  • Step 12: Now a window will open on the screen. Here enter the data range. Since we need to create a dot plot, we need to enter until the sequence has been created.  So our first data range would be f1:f6.
  • Step 13: Click on the “Ok” button. Now you will see a slight variance in Chart.
  • Step 14: Now continue Step 12 and Step 13, until the sequences are completed in your sheet by adding “Series” and “Data Range“.

how-to-make-a-dot-plot-in-google-sheets

Step 15: Soon after adding all the data range in the series you will see the dot plot chart being created on the Google Sheets as shown in the image below. how-to-make-a-dot-plot-in-google-sheets

Advantages of Dot Plot in Google Sheets

The advantages of Dot Plot in Google Sheets are explained below:

  • Your information is short and best comprehended visually.
  • The disparities between columns are more difficult to understand when using a graph.
  • You are seeking discrete information rather than patterns.
  • Bar charts and trend lines are some of the alternatives to dot plots that are incorporated into Google Sheets.
  • Dot plots, on the other hand, can be effective for displaying small amounts of discrete data.

How to Insert Multiple Rows in Google Sheets with Examples (3 Methods)

When working with Google Sheets, it is quite common where we might insert rows or columns in between the data. In Google Sheets, we can easily insert the rows in two methods namely using the insert tab and Right-clicking on the data. In this article, let us understand how to insert multiple rows with the help of Sheet tips provided on this page. Read on to find more.

Table of Contents

How to Insert Single Row Above and Below the Dataset?

Follow the steps listed below to insert a single row above or below the dataset.

Using Insert Tab to Single Row Above or Below

Let us consider the following dataset where we need to insert a row above the third row and below the third row. The steps to achieve the same using the INSERT tab is given below:

  • Step 1: Select the Row 3.
  • Step 2: Now click on the “Insert” tab in the menubar.
  • Step 3: Choose “Row Above“. This will insert a row above the dataset as shown in the image below.
  • Step 4: Choose “Row Below“. This will insert a row below the dataset as shown in the image below.

insert-multiple-rows-google-sheets

Using Right-Click Menu to Insert Row in Google Sheets

We will use the same dataset which we have used in the previous section. Now follow the steps as given to below to insert a row above the third row and below the third row using the Right-click menu option.

  • Step 1: Select Row number 3. (You can head and select the row number where you would like to insert multiple rows).
  • Step 2: Now Right-click anywhere on the spreadsheet.
  • Step 3: Click on “Insert 1 above” to insert a row above the third row.
  • Step 4: If you want to insert a row below the third row, then choose “Insert 1 below” and it will insert a row below the dataset.

insert-multiple-rows-google-sheets

 

 

How to Insert Multiple Rows in Google Sheets?

We can use both the INSERT tab and Right-click menu to insert the multiple rows in the Google Sheets. Alternatively, we can also use the Google Sheets keyboard shortcuts to insert multiple rows in the dataset.

Insert Multiple Rows using Insert Tab

The steps to insert multiple rows using the Insert Tab are given below:

  • Step 1: Open the Google Spreadsheet where you want to insert the multiple rows.
  • Step 2: Now select the number of rows you would like to insert. Suppose if I want to insert 4 rows above the 3rd row, then I am supposed to select 4 rows from row 3 as shown in the image below.
  • Step 3: Then click on the “Insert” tab
  • Step 4: Now choose “4 Rows above“. This will insert 4 rows above the third row.
  • Step 5: If you choose “4 Rows below“, it will insert 4 rows below the third row as shown in the image below.

insert-multiple-rows-google-sheets

Insert Multiple Rows using Right-click Menu

The steps to insert multiple rows using the Right-click menu in Google Sheets are given below:

  • Step 1: Select the rows where you wish to insert multiple rows in Google Sheets. If you want to insert four rows above the third row, select four rows from row three, as shown in the image below.
  • Step 2: Now Right-click anywhere on the screen.
  • Step 3: Click on “Insert 4 above“. This will insert 4 rows above the selected row in the dataset.
  • Step 4: To insert 4 rows below, click on the “Insert 4 below” from the sub-menu as shown below.

insert-multiple-rows-google-sheets

Insert Multiple Rows using Keyboard Shortcuts in Google Sheets

We can insert multiple rows that are not contiguous in Google Sheets with keyboard shortcuts. For instance, suppose you want to insert rows above rows #3, 5, and 8. You can do this all at once because the rows aren’t consecutive. One of your options is to insert each row one at a time using one of the methods discussed earlier.

Then there is another way to use the F4 keyboard shortcut. While this shortcut does not complete the task all at once, it does expedite the process.

The following are the steps to insert multiple rows in Google Sheets that are not contiguous (above row # 3, 5, and 8):

  • Step1: By clicking on the row header of row #3, you may select the row (or any cell in the dataset)
  • Step 2: Navigate to the Insert tab.
  • Step 3: Select the ‘Row above‘ option from the drop-down menu. This will add a row to the right of row #3.

insert-multiple-rows-google-sheets

  • Step 4: In row #5, select any cell.
  • Step 5: Press the F4 key.
  • Step 6: Any cell in row #8 should be selected.
  • Step 7: Press the F4 key.

The F4 key merely repeats the previous action, in this case, inserting a row above the selected row.

How to Use IMPORTFEED in Google Sheets: Fetch Feed from URL with IMPORTFEED

The IMPORTFEED function in Google Sheets helps to fetch or import RSS or ATOM feeds in a human-readable format to the desired Google Spreadsheet. The IMPORTFEED function comes in handy if you want to know the latest blog posts or new items of a particular website.

Let us understand how to use the IMPORTFEED function with the help of Google Sheet tips provided on this page. Read on to find more.

Table of Contents

Google Sheets IMPORTFEED Function Syntax

The syntax of IMPORTFEED function in Google Sheets is:

=IMPORTFEED(url[query][headers][num_items])

  • url – The RSS or ATOM feed’s URL, including the protocol (e.g., http://). The value for the URL must be either enclosed in quotation marks or a reference to a cell containing the relevant text.
  • query – [ OPTIONAL – “items” is the default value ] – Indicates what data should be retrieved from the URL.
    • “feed” returns a single row with feed information such as the title, description, and URL.
    • “items” returns a full table containing feed items. If num items are not specified, the feed returns all currently published items.
  • headers – [ OPTIONAL – by default FALSE ] – Whether column headers should be added as an extra row on top of the returned value.
  • num items – [ OPTIONAL ] – The number of items to return for item queries, starting with the most recent. If num items are not specified, the feed returns all currently published items.

Now let us understand how to use the IMPORTFEED function in Google Sheets with examples.

Fetching All Items from FEED URL using IMPORTFEED

Let us say that, I want to fetch all the latest published blog posts of www.sheetstips.com into a Google Spreadsheet. The steps to achieve the same are given below:

  • Step 1: Open the empty Google Spreadsheet.
  • Step 2: Move to the blank where you would like to import the data.
  • Step 3: Now type the formula as =IMPORTFEED(“https://sheetstips.com/feed”,,TRUE).

importfeed-google-sheets

  • Step 4: Press the “Enter” button and you will see the results as shown below.

importfeed-google-sheets

In the above example, we have used TRUE which is meant for headings, resulting in an additional row with the heading in the results.

Alternatively, you can also use the formula =IMPORTFEED(A1,,TRUE) to fetch the same feed. To get this done, you will have to paste the domain URL of the page that you would like to fetch into the sheet. For example, If I place the “https://sheetstips.com/” URL in cell 1 (A1) and use the formula “=IMPORTFEED(A1,,TRUE)” in another cell, it gives the same results that we got in the above section. Refer to the image below to see the example.

Fetching Specified Number of Items using IMPORTFEED in Google Sheets

In the previous section, the IMPORTFEED formula fetched all the data from the specified website.

What if you want to fetch only a specified number of feeds or items?

Google Sheets IMPORTFEED also allows us to fetch only a specified number of items. For example, If I want to fetch the latest 10 posts of sheetstips.com. We can get this done by following the steps given below:

  • Step 1: Move to the empty cell on the Google Spreadsheet.
  • Step 2: Now type the formula “=IMPORTFEED(“https://sheetstips.com/feed”,,TRUE,10)“.
  • Step 3: Press the “Enter” button.

Now you will see the results as shown below.

importfeed-google-sheets

Fetching Specific Items from Feed URL using IMPORTFEED

Google Sheets IMPORTFEED function allows us to specify which items from a feed we want to fetch. For example, you might only need the post title or URL but not the other columns like the author, creation date, or summary. We can get this done by following the steps as listed below:

  • Step 1: Move to an empty cell of Google Sheets where you would like to fetch the data.
  • Step 2: Now enter the formula “=IMPORTFEED(“https://sheetstips.com/feed”,“items title”,TRUE)“.
  • Step 3: Press the “Enter” button and you will see the results as shown in the image below.

importfeed-google-sheets

How to Add Labels to Legend in Google Sheets (Step by Step Procedure)

Google Sheets allows us to create a wide range of charts to visualize the data. With the help of Charts in Google Sheets, we can easily understand the differences, data trends, and much more things. However, simply creating a chart with colored columns would be insufficient to understand the dataset. Thus we can include legends in our chart which explain what each line, shape, color in the chart mean. In short, legends will help us to determine what a chart means in Google Sheets.

In this article, let us understand how to add labels to legends in a chart with the help of Google Sheet tips. Read on to find more.

Table of Contents

How to Create a Chart in Google Sheets?

First, let us create a chart in the Google Sheets to add labels to legends. The step to create a chart in Google Sheets are given below:

  • Step 1: Let us assume the following dataset. Now click on the “Insert” tab.
  • Step 2: Select “Chart” from the drop-down menu.

how-to-add-labels-to-legend-in-google-sheets

  • Step 3: Now based on the dataset, the chart will be created in Google Sheets as shown in the image below.

how-to-add-labels-to-legend-in-google-sheets

How to Add Labels to Legend in Google Sheets?

When you create a chart, it automatically includes a label legend. If the label legend is not already displayed, you can add it by performing the following steps:

  • Step 1: Click on the chart being created on the Google Sheet.
  • Step 2: Move to the “Setup” tab.
  • Step 3: Click on the Label section.
  • Step 4: Now select the data range.

how-to-add-labels-to-legend-in-google-sheets

  • Step 5: Click on the “Ok” button. Your chosen data should be displayed as labels in your chart.

how-to-add-labels-to-legend-in-google-sheets

To remove labels, uncheck the box next to ‘Use column as labels’ at the bottom of the chart editor.

By double-clicking on the chart at any time, you can bring up the Chart editor. Alternatively, you can right-click on the chart, then click on the three-dot icon in the upper left corner and select ‘Edit the chart’ from the menu that appears.

How to Set Position of Legend in your Chart?

If you don’t like how or where your legend appears in your chart, you can modify it as follows:

  • Step 1: Double click on the Chart. Now the Chart Editor will open towards the right side of the screen.
  • Step 2: Click on the “Customize” tab.
  • Step 3: Now move to the “Legend” section.
  • Step 4: Select a position from the dropdown menu.

how-to-add-labels-to-legend-in-google-sheets

You can also disable the legend by selecting the ‘None‘ option from the Position options.

How to Format Chart Legend in Google Sheets?

The Chart editor in Google Sheets gives you a lot of options for formatting the text in your labels. If you want to change the font size, color, or type of legend text, follow these steps:

  • Step 1: In the Chart editor, go to the Customize tab.
  • Step 2: Choose the Legend section.
  • Step 3: If you want to change the font of the legend, click the dropdown under ‘Legend font’ and choose a font.
  • Step 4: To change the font size, click the dropdown menu next to ‘Legend font size‘ and choose the font size you prefer.
  • Step 5: Select or toggle the B and/or I buttons under ‘Legend format‘ to make the legend text bold or italicized.
  • Step 6: To change the color of the legend text, select the desired color from the dropdown menu under ‘Text color.’

how-to-add-labels-to-legend-in-google-sheets

How to Add or Edit Text in Legend Labels?

You may want to change the legend label from the default labels to something else. Google Sheets allows you to change the text for each label separately.

  • Step 1: Within the chart, double-click the label you want to change.
  • Step 2: This will open the Chart editor’s ‘Text formatting‘ options for that specific label.
  • Step 3: You can enter the text you want to replace the default label text within the input box under ‘Text label.
  • Step 4: You can also change the formatting for that specific label at this point by selecting your preferred font family, font size, text style, and text color.

how-to-add-labels-to-legend-in-google-sheets

How to Use Slicer in Pivot Tables in Google Sheets (Add/Delete/Customize Slicers)

Slicers in Google Sheets are an effective tool for filtering data in Pivot Tables. With a single click, they make it simple to change values in Pivot Tables and Charts. Slicers come in handy when creating dashboards in Google Sheets. In this article, let us understand how to use Slicer in Pivot tables with the help of Google Sheet tips provided on this page. Read on to find more.

Table of Contents

Sheets Tips

Slicers in Google Sheets

Consider the following dataset. It consists of a small pivot table.

slicer-google-sheets

Now in the same dataset, you can see the slicer being created. When you modify this slicer filter, the pivot table will be modified.

How to Add Slicer in Google Sheets?

The steps to create a slicer in Google Sheets are given below:

  • Step 1: Open a spreadsheet on your computer by going to sheets.google.com.
  • Step 2: Click on the dataset to open the Pivot table. Check our article on how to insert a pivot table, if you haven’t created a pivot table in Google Sheets.
  • Step 3: At the top, select Data, followed by Slicer.

slicer-google-sheets

  • Step 4: Select a column to filter by on the right.
  • Step 5: Select your filter rules by clicking the slicer. Here you will be provided with 2 options and they are:
    • Filter by Condition: Select from a list of predefined conditions or create your own.
    • Filter by Values: Uncheck any data points you want to hide.

Now you will see the slicer being created on Google Sheets as shown below:

slicer-google-sheets

How to Edit Slicer in Google Sheets?

By default, the Slicers will show up in Black color. In order to edit or customize the slicers in Google Sheets, follow the steps as listed below:

  • Step 1: Click on the Slicer being created on the Google Sheets.
  • Step 2: Now click on the 3 dots and select “Edit Slicer“.

slicer-google-sheets

  • Step 3: Here a window will open on the screen. Here you will be provided with various options.
  • Step 4: To change the Title of the Slicer, modify the Title in the Title window.
  • Step 5: You can change the Title font and its size by moving to its respective window.
  • Step 6: Click on the “Title Text Color” window to change the title color.
  • Step 7: To change the title background color, make the necessary changes in the background color window. In this article, we have customized our Slicer in Google Sheets as shown in the image below:

slicer-google-sheets

How to Interact with Slicers in Google Sheets?

Now let us understand how to interact with Slicers in Google Sheets by following the steps as listed below:

  • Step 1: Click on the “All” drop-down menu in the Slicers.
  • Step 2: Here you will have two options – Filter by condition and Filter by value.

slicer-google-sheets

  • Step 3: Now select how the data needs to be filtered.
  • Step 4: Click on the “Ok” button and you will see the data being filtered in the Pivot table.

slicer-google-sheets

How to Delete Slicers in Google Sheets?

The steps to delete the Slicers in Google Sheets are given below:

  • Step 1: Click on the Slicers.
  • Step 2: Press the “Delete” button or “Backspace” button. Else click on the 3 dots and choose “Delete Slicer” to delete the slicers.

slicer-google-sheets

The slicers will be deleted.

How to Use Sparkline in Google Sheets? Create & Customize Sparkline with Examples

A sparkline chart is a short line chart that helps you visualize your data easily. It’s useful if, for example, you want to rapidly examine if share price data in a spreadsheet is increasing or decreasing.

You may insert these types of charts into a single cell on your spreadsheet using Google Sheets’ SPARKLINE function. While a sparkline is normally a line chart, you may use the SPARKLINE function to produce other types of charts, such as single-cell bar and column charts. In this article, let us understand how to create a Sparkline chart with the help of Google Sheet tips provided on this page.

Table of Contents

Sheets Tips

Sparkline Syntax in Google Sheets

The syntax of Sparkline Charts in Google Sheets are given below:

SPARKLINE(data, [options])

  • data – The data to plot is contained in a range or array.
  • options – [ OPTIONAL ] – A set of optional settings and values that can be used to modify the chart.

Options should be two cells wide when referring to a range, with the first cell representing the option and the second cell representing the value the option is set to.

The “charttype” parameter specifies the type of chart to be plotted, which can be any of the following:

  • “line” – A line graph is denoted by the term “line” (the default)
  • “bar” is the term for a stacked bar chart.
  • “column” is the term for a column chart.
  • “winloss” refers to a form of column chart that shows both positive and negative outcomes (like a coin toss, heads, or tails).

Creating Line Sparkline in Google Sheets

Let us consider the following dataset to create the Line Sparkline in Google Sheets. Now the steps tp create a Line Sparkline are given below:

  • Step 1: Visit the cell where you would like to create a Sparkline
  • Step 2: Now enter the following formula =SPARKLINE($B$2:$B$11). We simply had to define the range because the Line chart is the default chart type.
  • Step 3: Press the “Return” key and you will see the results as shown below.

sparkline-google-sheets

Customizing Line Sparkline in Google Sheets

1. “xmin” specifies the horizontal axis’s minimal value.

2. The variable “xmax” specifies the highest value along the horizontal axis.

3. “ymin” specifies the vertical axis’s minimum value.

4. The variable “ymax” specifies the highest value along the vertical axis.

5. “color” determines the line’s color.

=SPARKLINE($B$2:$B$11,{"charttype","line";"color","red"})
sparkline-google-sheets

6. “empty” specifies how empty cells should be handled. “zero” or “ignore” are examples of possible values.

7. “nan” specifies how non-numeric data should be handled in cells. “Convert” and “ignore” are the two options.

8. The value “rtl” defines whether the chart is shown right to left or not. True or false are the options.

=SPARKLINE($B$2:$B$11,{"charttype","line";"rtl",TRUE})
sparkline-google-sheets

9. The chart’s “linewidth” controls how thick the line will be. A thicker line is indicated by a greater number.

=SPARKLINE($B$2:$B$11,{"charttype","line";"linewidth",2})
sparkline-google-sheets

Creating Column Sparkline in Google Sheets

Let us consider the same dataset to create the column sparkline in Google Sheets. The steps to create a column  sparkline are given below:

  • Step 1: Go to the cell where you would like to create the Column Sparkline.
  • Step 2: Now enter the following formula “=SPARKLINE($B$2:$B$11,{“charttype”,“column”})“.
  • Step 3: Press the “Enter” button and you will see the results as shown below:

sparkline-google-sheets

Customizing Column Sparkline in Google Sheets

  • The color of the chart columns is controlled by “color.”
=SPARKLINE($B$2:$B$11,{"charttype","column";"color","red"})
sparkline-google-sheets
  • “lowcolor” changes the color of the chart’s lowest value.
  • “highcolor” changes the color of the chart’s highest value.
=SPARKLINE($B$2:$B$11,{"charttype","column";"lowcolor","red";"highcolor","green"})

sparkline-google-sheets
  • The color of the first column is set by “firstcolor.”
  • The color of the last column is set by “lastcolor.”
=SPARKLINE($B$2:$B$11,{"charttype","column";"firstcolor","green"})
sparkline-google-sheets
  • The color of all negative columns is set by “negcolor.”
=SPARKLINE($C$2:$C$11,{"charttype","column";"negcolor","red"})
sparkline-google-sheets
  • “empty” specifies how empty cells should be handled. “zero” or “ignore” are examples of possible values.
  • “nan” specifies how non-numeric data should be handled in cells. “Convert” and “ignore” are the two options.
  • “axis” determines whether or not an axis must be drawn (true/false).
  • The color of the axis is set by “axiscolor” (if applicable)
=SPARKLINE($C$2:$C$11,{"charttype","column";"negcolor","red";"axis",TRUE})
sparkline-google-sheets
  • “ymin” specifies a specific minimum data value to be used when scaling column heights (not relevant for win/loss).
=SPARKLINE($B$2:$B$11,{"charttype","column";"ymin",0})
sparkline-google-sheets
  • “ymax” specifies a specific maximum data value to be utilised when scaling column heights (not relevant for win/loss).
  • The value “rtl” defines whether the chart is shown right to left or not. True or false are the options.

Creating Bar Sparkline in Google Sheets

The steps to create a bar sparkline in Google Sheets are given below:

  • Step 1: Move to the cell where you would like to create the Bar Sparkline.
  • Step 2: Now enter the formula =SPARKLINE($B$2:$B$11,{“charttype”,“bar”})
  • Step 3: Press the “Enter” button and you will see the results.

sparkline-google-sheets

Customizing Bar Sparkline in Google Sheets

  • “max” specifies the highest value along the horizontal axis.

=SPARKLINE(B2,{“charttype”,“bar”;“max”,MAX($B$2:$B$11)})

sparkline-google-sheets

  • “color1” specifies the first color used for the chart’s bars.
  • “color2” specifies the second color for the chart’s bars.
=ArrayFormula(SPARKLINE(B2:C2,{"charttype","bar";"max",MAX($B$2:$B$11+$C$2:$C$11);"color1","red";"color2","green"}))
sparkline-google-sheets
  • “empty” specifies how empty cells should be handled. Among the possible values are “zero” and “ignore.”
  • “nan” specifies how cells with non-numeric data should be treated. There are two options: “convert” and “ignore.”
=SPARKLINE($B$2:$B$11,{"charttype","column";"nan","convert"})
sparkline-google-sheets
  • “rtl” specifies whether the chart is rendered right to left. True or false is the only option.

=SPARKLINE($B$2:$B$11,{“charttype”,“column”;“rtl”,TRUE})

sparkline-google-sheets

 

Creating Win-Loss Sparkline Charts in Google Sheets

  • Step 1: Navigate to the cell where you want to create the Win Lose Sparkline.
  • Step 2: Type in the formula =SPARKLINE($B$2:$B$11,{“charttype”,“winloss”}).
  • Step 3: Click the “Enter” button to see the results.

sparkline-google-sheets

Customizing Win Loss Sparkline in Google Sheets

Color Option: This allows you to change the color of the column bars.

=SPARKLINE($B$2:$B$11,{“charttype”,“winloss”;“color”,“red”})

sparkline-google-sheets

LowColor and HighColor Options: While the Win-Loss sparkline charts do not display the magnitude of the value, you can still highlight the maximum and minimum values in a different color.

SPARKLINE($B$2:$B$11,{“charttype”,“winloss”;“lowcolor”,“red”;“highcolor”,“green”})

sparkline-google-sheets

FirstColor and LastColor Options: With these options, you can use a different color to highlight the first and/or last point in the win-loss chart.

=SPARKLINE($B$2:$B$11,{“charttype”,“winloss”;“firstcolor”,“green”;“lastcolor”,“red”})

sparkline-google-sheets

How to Add & Use Calculated Fields in Google Sheets Pivot Tables

Calculated Fields are very useful when it comes to summarising the data in Google Sheet Pivot tables. The main purpose of Calculated Fields is that it allows you to process your data in order to create more tailored Pivot table results. With the help of Custom formulas, one can present summary metrics in the Pivot table using calculated fields without making any changes in the actual dataset. In this article, let us understand how to add and use the Calculated Fields with the Google Sheet tips provided on this page. Read on to find more.

Table of Contents

Sheets Tips

How to Create Pivot Tables in Google Sheets?

Let us consider the following dataset which represents the sales data of each division.

calculated-fields-google-sheets

Now follow the steps listed below to create a Pivot table for the above dataset.

  • Step 1: Click on the Data tab from the menubar.
  • Step 2: Now choose “Pivot Table” from the drop-down menu.

calculated-fields-google-sheets

  • Step 3: You should now see a window asking whether you want to place your pivot table into an existing sheet or create a new one. Choose your preferred option. It’s always advisable to make one in a new sheet for clarity.

calculated-fields-google-sheets

  • Step 4: Click on the “Create” button.
  • Step 5: Now the Pivot table will be created in the new sheet. The Pivot table will look like the following image at this stage.

calculated-fields-google-sheets

  • Step 6: A grid of ‘Rows, Columns, and Values‘ should be displayed. You may now begin populating your pivot table with the information you desire. A Pivot Table Editor should appear on the right side of the window. This will assist you in determining what should be included in your pivot table.

calculated-fields-google-sheets

How to Add Calculated Fields in Google Sheet?

Now let us understand how to add the calculated fields using the SUM function in Google Sheets with the help of the steps given below:

  • Step 1: Select the pivot table by clicking on it.
  • Step 2: Click Add next to “Values” on the side panel.
  • Step 3: Select the Calculated field box from the drop-down menu.

calculated-fields-google-sheets

 

  • Step 4: To use SUM to calculate a value, click SUM next to “Summarize by.
  • Step 5: To use a custom formula to calculate a value, follow these steps: Enter a formula in the field that displays. In our case the formula is =SUM(arrayformula(‘Number of Units’*’Price Per Unit’))/sum(‘Number of Units’)
  • Step 7: Then click Custom next to “Summarize by.

calculated-fields-google-sheets

  • Step 8: Click Add in the bottom right corner to add a new column.

Now you can see the results as shown below:

calculated-fields-google-sheets

Points to Note About Calculated Fields in Google Sheets

Pivot tables with calculated fields have a lot more flexibility and versatility. It does, however, have some restrictions. As a result, some considerations must be made while creating calculated fields.

  • Only cells from your original dataset are referenced in your computed field calculations. They can’t refer to the totals or subtotals in the pivot table.
  • In the calculated field formulas, you must use the field names from your dataset. Individual cells cannot be referred to by their address or cell names.
  • It’s critical that you use the correct variable name for each field in your formula. If your field name has more than one word with spaces between them, you must use single quotes to surround the variable name in the calculated field’s formula.

How to Use INDEX MATCH Functions in Google Sheets with Google Sheets

When we use the functions INDEX and MATCH separately we see that we have only limited options to perform operations. However, when both INDEX and MATCH are combined together, we can make most of it in Google Sheets.

On this page, we will look at how to utilize the INDEX and MATCH functions together, as well as some examples, with the help of Google Sheet tips. Read on to find out more.

Table of Contents

Sheets Tips

MATCH Function in Google Sheets

Google Sheets MATCH function returns an item’s relative position in a range that matches a given value. The syntax of the MATCH function in Google Sheets are given below:

=MATCH(search_key, range, [search_type])

  • search key – The value to look for in the search.
  • range – The search range is a one-dimensional array. MATCH function will return #N/A! if the height and width of the range are both greater than 1.
  • search type [ OPTIONAL – 1 by default ]- The method for doing a search.
    • 1 – MATCH assumes the range is sorted in ascending order and returns the greatest value less than or equal to the search key when set to 1.
    • 0 – In cases where the range is not sorted, a value of 0 indicates an exact match, which is necessary.
    • -1 MATCH assumes the range is ordered in descending order and returns the smallest value greater than or equal to the search key if -1 is specified.

INDEX Function in Google Sheets

Google Sheets INDEX function accepts a cell range, a row index, and a column index, and returns the value in the cell that is at the intersection of the row and column specified. The syntax of the INDEX function is:

INDEX(reference, [row], [column])

  • Reference: The range of cells from which we want to extract the item is referred to as reference.
  • Row: The row offset from which we wish to extract the item is row inside reference.
  • Column: column is the reference column offset from which we wish to extract the item. This parameter is not required.

How to Combine INDEX and MATCH Functions in Google Sheets?

When the two formulas are combined, they can search up a value in a table cell and return the same value in another cell in the same row or column. The general method to combine both INDEX and MATCH functions in Google Sheets are given below:

=INDEX(range2,MATCH(search_key,range1,0))

  • range 1: The value we want to look for in range1 is the search key.
  • search_key: The MATCH function determines the index for a value that matches the search key from a range of cells called range1.
  • range2: The INDEX function extracts a value corresponding to the position/index returned by MATCH from the range2 of cells.

In other words, the MATCH function supports the INDEX function in determining the value to return’s location.

Now that we understood the syntax and formula combination of INDEX and MATCH functions. In the next section, let us understand how both the function work together with examples.

Using INDEX and MATCH Function with Single Criteria References

Consider the following employee dataset. In the following dataset, we have employee name, ID, department, salary details. Now in the same dataset, towards the right side, we can see the employee Name and department box.

how-to-use-index-match-google-sheets

 

Now, if we want to change the employee departments’ names based on the employee name, we can combine both INDEX and MATCH functions in Google Sheets.

how-to-use-index-match-google-sheets

The steps to achieve the same are listed below:

  • Step 1: Move to the cell which is against the employee name cell and type the employee name whose department you would like to find.
  • Step 2: Then move the cell against the department name.
  • Step 3: Now enter the formula =INDEX(B2:B17,MATCH(H4,A2:A17,0))
  • Step 4: Press the “Enter” button. You will see the results as shown below. Now try to change the employee name and you will see department name changing as well.

how-to-use-index-match-google-sheets

 

Formula Explanation:

Let’s break down this formula to see how it worked. We’ll start with the formula’s inner function:

MATCH(H4,A2:A17,0)

This function searches the range A2:A17 for the value H4 and returns its position in the range.

Let’s have a look at the formula’s outer function next:

=INDEX(B2:B17,MATCH(H4,A2:A17,0))

This formula searches the range B2:B17 for the value in the 4th slot and returns that value, which is ‘Marketing.’

Using INDEX and MATCH Function with Multiple Criteria References

The range in the preceding example was a single column. The INDEX-MATCH operations, on the other hand, can give us additional versatility by allowing us to access a value from many columns.

Let’s assume that in cell G6 of our example worksheet, we can have either Department name, Salary, or even ID as a label. What if, in addition to the label in cell G6, the label in cell G6 was dynamic and subject to change?

In that instance, we would have to think about many columns, and the column we would go to would be determined by the label in cell G6. Well, this can be achieved by following the steps as listed below:

  • Step 1: Move to the cell against the salary.
  • Step 2: Now type the formula as =INDEX(A2:D17,MATCH(H4,B2:B17,0),MATCH(G5,A1:D1,0)).
  • Step 3: Press the “Enter” button.

If cell G6 contains the string “Oct Salary,” as shown below, this formula gives the salary associated with the name specified in cell H4:

how-to-use-index-match-google-sheets

This formula provides the monthly ID corresponding to the name specified in cell G6 if cell H4 contains the string “ID”:

how-to-use-index-match-google-sheets

How to Insert Spin Button in Google Sheets with Examples

The spin button is a type of scroll bar function. By using the spin button, one can change the cell value by clicking on it. However, this spin button function is not available in Google Sheets as it exists in Excel. Thus to overcome this issue, we can use the Google Script editor. In this article, let us understand how to create a spin button with the help of Google Sheet tips provided on this page.

Table of Contents

How to Create Spin Button in Google Sheets?

There are three parts in creating the Spin button in Google Sheets and they are:

  • Insert triangles like the Spin button.
  • Drafting the Google Script.
  • Assigning the Google Script to the shape triangle.

How to Insert Triangles Like Spin Button in Google Sheets?

The steps to insert Triangle like Spin button in Google Sheets are given below:

  • Step 1: Open the Google Sheets.
  • Step 2: Click on the “Insert” tab from the menubar.

spin-button-google-sheets

  • Step 3: Now select “Drawing” from the drop-down menu.
  • Step 4: Here the Drawing dialog box will open on the screen. Now click on the “Shape” icon.

spin-button-google-sheets

  • Step 5: Select the shape you want to insert from the ‘Shapes‘ menu. ‘Flowchart: Extract‘ and ‘Flowchart: Merge‘ were the tools used.
  • Step 6: The triangle will appear wherever you click in the dialogue box’s working area. The shape can be resized and formatted here.

spin-button-google-sheets

  • Step 7: Save and close the window.

This will add the shape to Google Sheets.

spin-button-google-sheets

Now you can resize the image by clicking on the image inserted in the Google Spreadsheet.

Adding Google Script to the Triangle Icon Inserted in Google Sheets

For each button, we must now add a Google Script code. We will do this by creating two functions: increment and decrement.

The codes for each function are as follows:

Increment Number by 1 Script Code

function increment() {

 SpreadsheetApp.getActiveSheet().getRange(‘d10’).setValue(SpreadsheetApp.getActiveSheet().getRange(‘d10’).getValue() + 1); }

Decrement Number by 1 Script Code

function decrement() { SpreadsheetApp.getActiveSheet().getRange(‘d10’).setValue(SpreadsheetApp.getActiveSheet().getRange(‘d10’).getValue() – 1);  }

Now we need to add these script codes to the shapes which we have created in Google Sheets. The steps to add the Script Codes to the shapes are listed below:

  • Step 1: Click on the “Tools” tab in the menubar.
  • Step 2: Now on the homepage, click on the “Script Editor” from the drop-down menu.
  • Step 3: Copy and paste both functions into the Code.gs window from the Script Editor backend.

spin-button-google-sheets

  • Step 4: Click on the “Save” icon. Now close the Script Editor.

In Google Sheets, the above steps will create two new functions.

Now we must assign these functions to the inserted shapes (triangles).

Assign Google Script to the Shapes Created in Google Sheets

The steps to assign a script to the shape in Google Sheets are given below:

  • Step 1: Click on the Triangle shape which we have created in Google Sheets to assign the increment functions.
  • Step 2: Click on the three dots in the triangle’s top right corner.

spin-button-google-sheets

  • Step 3: Select the assign script option.
  • Step 4: In the Assign Script dialogue box, type the name of the function. In our case, we are using the name “Increment“.
  • Step 5: Now click on another Triangle shape and choose three dots from the triangle’s top left corner.
  • Step 6: Choose assign script and type the function name in the script dialogue box. In our case, it is decrement.

spin-button-google-sheets

That’s it, the spin button is created in the Google Sheets. When you click on the triangle with the ‘Increment’ function assigned to it, the numerical value in cell d10 will be increased by one.

spin button in google sheets

Likewise, clicking on the other triangle will reduce the value in cell d10 by one.

spin button in google sheets