How to Insert a Google Sheets Into Google Docs (Merge Google Sheets with Docs)

Google Workspace formerly known as G Suite comes with secure collaboration and productivity apps such as Google Docs, Sheets, Slides, etc., for all types of business. Google Sheets and docs are the productivity apps from Google Workspace which are used by individuals in day-to-day life. Because of this reason, in some instances, we might fall under a situation where we want to merge data from Google Sheets to Google Docs. Manually copy-pasting the data from sheets to docs might not help, if the data in sheets need to be updated from time to time. Thus to overcome this issue, we can directly sync Google Sheets data into Google docs which we will be discussing with the help of Google Sheets tips.

How to Insert Charts from Google Sheets to Google Docs?

Consider the following chart in Google Sheets. Now we want to insert this chart to Google Docs.

To insert a chart from Google Sheets to Google Docs, follow the steps outlined below:

  • Step 1: Open the Google Docs where you want to insert the chart.
  • Step 2: Now click on the “Insert” tab and choose Chart from the drop down menu.
  • Step 3: Here Chart submenu will open. Choose “From Sheets” from the drop down menu again.

insert-google-sheets-in-google-docs

  • Step 4: Now the Insert Chart window will open on your screen. Select the spreadsheet from where you want to insert the chart and click on the “Select” button.
  • Step 5: The window will show the list of charts available on the spreadsheet. Select the chart which you want to insert.

insert-google-sheets-in-google-docs

  • Step 6: Now click on the “Import” button.

insert-google-sheets-in-google-docs

  • Step 7: Your chart is imported to the Google Spreadsheet.

insert-google-sheets-in-google-docs

 

This Spreadsheet has No Charts Error

When you are trying to import a chart from the Google Sheets to Google Docs, you will be prompted with a message saying “This Spreadsheet has no charts“. This error occurs only if you choose a spreadsheet that has no charts.

So make sure to choose a spreadsheet that has charts in Google Sheets.

How to Insert Table from Google Sheets to Google Docs?

Google Sheets also allows the user to insert tables from Google Sheets to Google Docs. The steps to insert tables from Spreadsheet to docs are outlined below:

  • Step 1: Open the Google Spreadsheet from where you want to insert the tables.

insert-google-sheets-in-google-docs

  • Step 2: Now copy the table which you would like to insert. Select the table and right-click anywhere on the screen. Choose the Copy option from the drop down menu. Alternatively, you can use the keyboard shortcut “CNTRL+C“.
  • Step 3: Now open the Google Docs where you want to paste the table.
  • Step 4: Now Right-click and choose the Paste option to paste the table. Also, you can use the keyboard shortcut “CNTRL+V“.
  • Step 5: Here Google Sheets will show the Paste Table dialog box. Choose the “Link to Spreadsheet” option in the window and then click on the “Paste” button.

insert-google-sheets-in-google-docs

This will insert the data into Google Docs.

How to Update Data In Google Sheets with Single Click?

When you make changes to the data in the original range in Google Sheets which has been copied and put into Google Docs, you will be prompted to update the table in Google Docs. Also not that this appears only when you select any cell in the table.

When you are prompted with the “Update” button, just click on the same. This action will fetch the updated data from the table in Google Sheets to Google docs.

insert-google-sheets-in-google-docs

Change Table Cell Range from Google Docs

If you add more rows or columns to the Google Sheets table, you can get that updated in Google Docs by following the steps as listed below:

  • Step 1: In Google Docs, click anywhere in the table.
  • Step 2: Select ‘Linked Table Option’ from the drop-down menu (the icon at the top-right part of the table)
  • Step 3: Change the range by clicking on it.
  • Step 4: Here you must provide the new range.
  • Step 5: Select the “Ok”  button.

insert-google-sheets-in-google-docs

How to Unlink the Table in Google Docs Copied from Google Sheets?

To unlink the table from Google Sheets to Google docs, follow the steps as given below:

  • Step 1: Open the Google docs which have a table copied from Google Sheets.
  • Step 2: Here click on the “Linked Table Options” as shown in the image below.

insert-google-sheets-in-google-docs

  • Step 3: Choose unlink from the drop down menu. This will unlink the table.

insert-google-sheets-in-google-docs

How to Convert Currency in Google Sheets? – Google Finance Currency Conversion

With the help of the Google Finance function, we can easily convert the currency in Google Spreadsheet. The finance feature of Google Sheets lets users convert currency rates automatically without any manual calculations since Google Sheets fetches updated currency rates automatically as and when we enter the formulas.

In this article, let us discuss everything about how to convert currency on a specific date with the help of Google Sheets tips provided on this page. Read further to find more.

Google Sheets Currency Conversion Syntax

The syntax to convert currency in Google Sheets using the Google Finance function are explained below:

=GOOGLEFINANCE(“CURRENCY:<source_currency_symbol><target_currency_symbol>”)

Here,

  • source_currency_symbol: The three-letter code for the currency you want to convert from is the source currency symbol.
  • target_currecny_symbol: The three-letter code for the currency you want to convert to is the target currency symbol.

Google Finance Currency Codes: Country Names and their Currency Codes for Conversions

In order to convert the currency using syntax, we will have to enter the proper country code. The list country names and their currency codes are given below:

Currency Name
Currency Code
Canada Dollar CAD
Euro EUR
Hong Kong Dollar HKD
Indian Rupee INR
Iran Rial IRR
Japan Yen JPY
Russia Ruble RUB
Singapore Dollar SGD
United Kingdom Pound GBP
US Dollar USD

Now, for example, if you want to convert dollars to rupees then you will need to enter the following formula in Google Sheets without any space.

=GOOGLEFINANCE(“CURRENCY:USDINR”)

How to Change Currency in Google Sheets?

As discussed above we can easily change the currency in Google Sheets by using the GOOGLEFINANCE function. The steps to convert the currency is given below:

  • Step 1: Select the cell where you want to convert the currency. Here we are trying to convert the currency from USD to INR.
  • Step 2: Now enter the formula =GOOGLEFINANCE(“CURRENCY:USDINR”).
  • Step 3: Press the “Enter” button. You will see the results as shown below.

how-to-convert-currency-in-google-sheets

Alternatively, for the same dataset, we can follow the steps outlined below for currency conversion.

  • Step 1: Move to the cell where you want to get the currency conversion results.
  • Step 2: Now simply enter the formula =GOOGLEFINANCE(“CURRENCY:”&A2&B2)
  • Step 3: Press the “Enter” button and you will see the results as shown below.

how-to-convert-currency-in-google-sheets

How to Convert USD to INR Using GOOGLEFINANCE?

The methods above simply provide you with the exchange rates between two currencies; they do not convert money from one currency to another. Assume we have the following list of dollar pricing and wish to convert them to INR.

  • Step 1: Move to the cell where you want to convert USD rates into INR rates.
  • Step 2: Now simply enter the formula =GOOGLEFINANCE(“CURRENCY:USDINR”)*A2.
  • Step 3: Press the “Enter” button and you will see the results.
  • Step 4: Now drag the formula applied cells to other cells where you want to convert the currency.

how-to-convert-currency-in-google-sheets

To convert the price to INR, we simply multiplied the output of the GOOGLEFINANCE function by the cell value in column A.

Google Finance Currency Historical Data

You can modify the GOOGLEFINANCE function to obtain exchange rates for a longer period of time rather than just one day.

The GOOGLEFINANCE function can be modified to retrieve historical exchange rates using the following syntax:

GOOGLEFINANCE(“CURRENCY:<source_currency_symbol><target_currency_symbol>”, [attribute], [start_date], [number_of_days|end_date], [interval])

The steps to fetch historical data currency conversion are given below:

  • Step 1: Choose the cell where you want to fetch the historical currency exchange rate.
  • Step 2: Now type the formula: =GOOGLEFINANCE(“CURRENCY:USDINR”, “price”, DATE(2021,10,10), DATE(2021,10,20), “DAILY”)
  • Step 3: Press the “Enter” button and you will start seeing the results as shown below.

how-to-convert-currency-in-google-sheets

How to use GOOGLEFINANCE to Fetch Currency Exchange Rates Over the Past Week?

Instead of using the DATE method, you can use the TODAY function to dynamically display exchange rates for the past, say one week, based on the day the sheet is opened.

Consider the following scenario: we want to dynamically display exchange rates for the preceding 10 days, regardless of the day the sheet is opened on. The steps to get this done are given below:

  • Step 1: Open the spreadsheet where you want to fetch the currency conversion rates.
  • Step 2: Now enter the formula =GOOGLEFINANCE(“CURRENCY:USDEUR”, “price”, TODAY()-10, TODAY(), “DAILY”)
  • Step 3: Press the “Enter” button and you will see results as shown below.

how-to-convert-currency-in-google-sheets

How to Apply a Color Scale Based on Values in Google Sheets? (Conditional Formatting)

A Color Scale in Google Sheets helps us to add interactive color effects to the selected dataset. The color scale is highly useful when it comes to the analysis of web performance, sales data, user traffic on site, and so on. Also, Google Sheets automatically colors the cells based on the cell value.

So, if you are planning to incorporate color scale to your data set in Google Sheets then this page is for you. On this page, you will understand everything about Color Scale under Conditional Formatting rules with the help of Google Sheets tips provided. Read further to find more.

Google Sheets Conditional Formatting Color Scale Based on Another Cell

The following steps will answer you everything about “How do you make a cell change color based on value in Google Sheets?”

  • Step 1: Open the Google Spreadsheet where you want to change the color of a cell-based on value.
  • Step 2: Select the cells where you want to apply the color scale.
  • Step 3: Now click on the “Format” tab in the menubar and choose “Conditional Formatting” from the drop down menu.

how-to-apply-a-color-scale-based-on-values-in-google-sheets

  • Step 4: The Conditional Formatting window will open towards the left side of the screen.
  • Step 5: Click on the Color Scale to move towards the Color Scale pane.
  • Step 6: Now under “Apply to Range” select the range of the cells where you want to apply the Google Sheets color scale.
  • Step 7: Under “Format Rules” you will have default colors. Choose the color scale which you would like to apply.

how-to-apply-a-color-scale-based-on-values-in-google-sheets

  • Step 8: This will simply apply the color scale based on the cell value.

how-to-apply-a-color-scale-based-on-values-in-google-sheets

Now to apply the color scale based on the cell value using the conditional formatting follow the steps below:

  • Step 9: Now in the below section, you will see 3 options namely – Minpoint, MidPoint, Maxpoint. Under Minpoint, select the option suitable for your dataset and set the Minpoint with the desired number.
  • Step 10: Choose the “Color” which you would like to display for Minpoint by clicking on the Min Point Color.
  • Step 11: Repeat Steps 8 and 9 for Midpoint and Maxpoint.
  • Step 12: Click on the “Done” button.

how-to-apply-a-color-scale-based-on-values-in-google-sheets

That’s it, as per the conditions you have set under Min, Mid, and Max point, the cell color will change as and when the value of the cell is being changed.

How to Apply Custom Color Scale in Google Sheets?

In order to add the custom color scale to your Google Sheets based on the cell value, follow the same steps mentioned here:

  • Step 1: Select the cell where you want to apply the custom color scale based on the value.
  • Step 2: Click on the “Format” tab and choose “Conditional Formatting” from the drop down menu.
  • Step 3: Now click on the “Color Scale” pane.
  • Step 4: Under the “Format rules” choose “Custom color scale” and start customizing the colors which you want to display.
  • Step 5: Click on the “Done” button.

how-to-apply-a-color-scale-based-on-values-in-google-sheets

Google Sheets Color Scale Not Working

In some instances, the Google Sheets color scale may not work. To make it start working again, clear the formatting you have applied in your sheets and start creating the custom color scale again. To clear the formatting, choose the “Format” option in the menubar and choose “Clear Formatting” from the drop down menu.

How to Make Tables in Google Sheets? Using Google Sheets Formatting Options

Google Sheets is full of rows and columns which makes it much easier to understand the dataset. However, these rows and columns come with gridlines and all the cells in Google Sheets look common. So, if we want to highlight the dataset into an easily understandable format, we need to give a table-like look to cells with the dataset. This is where there is a need to create the table in Google Sheets. This article will tell you everything on how to create a table with the help of Google Sheets tips. Read further to find more.

Google Sheets Tables

Take a look at the following image. Our objective on this page is to know how to create or format a table in Google Sheets that resembles the following image.

how-to-make-a-table-in-google-sheets

How To Make Tables in Google Sheets?

To create a table in Google Sheets, follow these steps:

  • Step 1: Open the Google Spreadsheet where you want to create a table.
  • Step 2: Now choose the number of rows and columns and start adding the data in the required cells.
  • Step 3: Once the data has been added to the cells, select the dataset to create a table.
  • Step 4: Now click on the “Borders” icon and choose “All Borders”.

how-to-make-a-table-in-google-sheets

  • Step 5: Then select the headers of the dataset to Freeze. To Freeze the headers, click on the “View” tab and choose “Freeze” from the drop down menu. This will freeze the data as shown below.

how-to-make-a-table-in-google-sheets

  • Step 6: Now to Bold the Headings, click on the “Bold” icon. Alternatively, you can also use the keyboard shortcut “Cntrl+B” to bold the headings.

how-to-make-a-table-in-google-sheets

  • Step 7: Use the Alignment Options available in the menubar to align the data in the tables.

how-to-make-a-table-in-google-sheets

That’s it your Table is created in the Google Sheets.

How To Format Tables in Google Sheets?

Now that we have created the tables in Google Sheets. Let us start formatting the table in Google Sheets with various formatting options available.

Coloring the Table in Google Sheets

To Color the tables in the Google Sheets, follow these steps:

  • Step 1: Select the table cells where you want to color. It is always good to have headers in dark color and other cells in a light color.
  • Step 2: So table headers first and choose “Fill Color” and choose the color to fill the header.
  • Step 3: Now select other parts of the cells and hit on the “Fill Color” icon. This will display a palette of colors.
  • Step 4: Choose the color from the drop down menu to shade other cells of the table.

how-to-make-a-table-in-google-sheets

You can also apply custom colors to your table with the help of hex code. To apply custom colors to your table, just choose + icon under the Custom section.

Using Alternate Colors to Rows of the Table in Google Sheets

Google Sheets lets you color your table rows with alternate colors instead of applying colors manually to the rows. The steps to get this done in Google Sheets are explained below:

  • Step 1: Select the cells of the table you want to color with alternative colors.
  • Step 2: Now simply click on the “Format” tab and choose “Alternating colors” from the drop down menu.

how-to-make-a-table-in-google-sheets

  • Step 3: By default, Google Sheets colors the rows of the table with Grey and opens the “Alternating Colors” dialog box towards the right side of the screen.
  • Step 4: Now here you will see the default styles. Choose if you want to have default styles or move to the custom section to have custom colors in alternative rows.
  • Step 5: Click on the “Done” button. This will color the rows of the table in Google Sheets in an alternating format.

how-to-make-a-table-in-google-sheets

Remove Formatting in Table

Now we have applied so many formatting options to our table. However, if you want to remove all the formatting from your table and make it look like normal cells, then follow these steps:

  • Step 1: Select the table rows and columns to remove the formatting.
  • Step 2: Now click on the “Format” tab in the menubar and choose “Clear Formatting” from the drop down menu.

This will remove the formatting in the table.

How to Resize Rows and Columns in Google Sheets? (3 Easy Methods)

Google Spreadsheets software comes with default row and column size. However, this default row and column size won’t help if your text length is longer than the row and column size. Thus to overcome this problem, Google Sheets allows you to change the row and column size. There are multiple ways to resize rows and columns in Google Sheets which we will discuss on this page with the help of Google Sheet tips. Read further to find more.

Manually Resizing Rows and Columns in Google Sheets

One of the quickest ways to resize the rows and columns in Google Sheets is manual. The steps to change the rows and columns size manually are as follows:

  • Step 1: Open the Google Spreadsheet where you want to resize the rows and columns.
  • Step 2: Now, hover the cursor over the column header to resize the column. To resize the row, move to the row header.

how-to-resize-column-and-rows-in-google-sheets

  • Step 3: Start dragging the border to the new position. While dragging the border you will see the blue line moving.
  • Step 4: Stop the cursor once you reach the desired size. This will resize the desired row or columns in the Google Sheets.

how-to-resize-column-and-rows-in-google-sheets

Manually Resizing Multiple Rows and Columns

You can also resize multiple columns and rows in Google Sheets at one go. To do this, you will have to follow the steps listed below simply:

  • Step 1: Select the desired number of rows and columns which you want to resize.
  • Step 2: Now, simply drag the borders of the columns and rows.
  • Step 3: You will see a blue line moving while dragging the borders. Stop once you have reached the required size and remove the selection.

how-to-resize-column-and-rows-in-google-sheets

Now you will see the multiple rows and columns being resized.

Resizing Rows and Columns Automatically in Google Sheets

Google Sheets also enables you to resize the rows and columns automatically based on the size of the text. For example, if your text size is larger than that of the row and column, you can make the entire text visible by enabling the Google Sheets auto-resizing feature. The steps to automatically resize the rows and columns are given below:

  • Step 1: Open the Google Spreadsheet where you want to resize the rows and columns.
  • Step 2: Now paste the text inside the cell.
  • Step 3: To resize the cell, move to the header of row and column.
  • Step 4: Double click the header, and this will resize the rows and columns.

how-to-resize-column-and-rows-in-google-sheets

Using Resize Row or Column Tool in Google Sheets

We can also resize the rows and columns in Google Sheets with the help of Resize rows and columns tool. The steps to get this done are given below:

  • Step 1: Open the Spreadsheet where you want to resize the rows and columns.
  • Step 2: Simply click on the row or column which you want to resize.
  • Step 3: Now, right-click anywhere on the Google Sheet, which will open the sub-menu.
  • Step 4: Then click on the “Resize the row” from the drop-down menu.

how-to-resize-column-and-rows-in-google-sheets

  • Step 5: Now, the Resize box will open on the screen. Here you will have two options – Specify Row Height and Fit to Data.
  • Step 6: Move to the “Specify Row Height” section and enter the Row Height.
  • Step 7: Click on the “Ok” button, and this will resize the row height.

how-to-resize-column-and-rows-in-google-sheets

If you select Fit to Data, the row or columns will be resized based on the text length that you have entered in your cell.

How to Protect Your Google Spreadsheet Data? (3 Easy Methods)

Google Sheets allows us to share and collaborate the spreadsheet with various users online. However, sharing the Google Sheets with multiple users might lead to discrepancies in the data and might also result in formatting errors. Thus it is important for one to always protect the data in Google Sheets. On this page, let us discuss 3 easy methods with the help of which we can protect our data with the help of Google Sheet tips. Read further to find more.

Sheets Tips

Table of Contents

How to Protect Google Spreadsheet Data?

We can protect our Google Spreadsheet data with the help of the following three methods:

  1. Protecting Entire Worksheet
  2. Setting up Notifications if someone tries to edit cells
  3. Setting up Data Validation

How To Protect Entire Google Worksheet?

We can easily protect individual worksheets in the Google Spreadsheet. To do so, go to the Data menu and select Protect sheet. Similar to a Google Doc, you can specify permissions to allow editing rights to anyone invited as a collaborator; only you; or a list of collaborators. You can place those cells in a “Results” worksheet and make it read-only if you need to prevent collaborators from modifying them. Formulas can refer to any worksheet, allowing data entry cells to be “opened” and formula cells to be “closed” in a protected worksheet.

 

3-ways-to-protect-your-google-spreadsheet-data

How To Set Up Notifications in Google Sheets?

We can also set up notifications in Google Sheets when a user tries to make some modifications in our protected cells. To do the same, go to the Tools menu and click Notification rules. You can choose to get notified when changes are made to the spreadsheet, a single worksheet, a cell or cell range, collaborators, or when a user submits a form (if the spreadsheet is linked to a form). Notifications can be sent immediately after a change or as a daily summary which is also known as a daily digest.

how-to-turn-off-notifications-in-google-sheets1

How To Set Data Validation to Protect Google Sheets?

We can also set a value that the cell must contain to prevent individual cells, or a group of cells, from being edited. we can enter a number, text, date, or list of items that come with further settings such as:

  • A Number cell can be a range or maximum number
  • A Text cell can contain or not contain words
  • A Date cell can only be a valid date and
  • A List column can only be populated from a list

We can also alert the editor that the validation rules haven’t been satisfied but still accept the change, or block any modifications that don’t satisfy the criteria.

Data validation is a great way to verify that cells with formulas aren’t accidentally edited. To do the same, follow these steps:

  • Step 1: Choose the cell or range of cells you want to validate.
  • Step 2: Now click on the Data tab and choose “Data Validation“.
  • Step 3: Click the Text criteria and choose the 2nd drop down to “equals.
  • Step 4: Fill up the blanks with your cell formula.
  • Step 5: Check the box that says “Reject Input” against On invalid data.
  • Step 6: Click on the “Save” button.

3-ways-to-protect-your-google-spreadsheet-data

How to Manage Date in Google Spreadsheet with List View? (Replace List View with Freeze)

The List View option in Google Sheets displays the spreadsheet data in a standard manner. The Google Sheets list view is especially handy for individuals who merely have viewing access but need to manage a large volume of data. Also, Google Sheets will automatically route to list view if a user tries to open a spreadsheet with more than 50 users presently viewing it.

However, with the new update in Google Spreadsheet, there is no list view available. And to overcome this issue and find an alternative way of List View, one can lock or Freeze feature in the Google Sheets. This page will tell you everything about List View and teaches you how to use the Freeze feature instead of List View with the help of Google Sheet Tips. Read further to find more.

Latest: In Google Spreadsheets, List View is no longer available. You may use the Freeze function to lock rows and columns in place to make your spreadsheets easier to read and navigate.

Sheets Tips

Table of Contents

How to Use Freeze Option Instead of List View in Google Sheets?

When you have a large amount of data that needs to be scrolled up and down, you can use Google Sheets’ Freeze feature. The Google Sheet Freeze function allows you to freeze both rows and columns.

  • Step 1: Open the Google Spreadsheet and navigate to the menubar.
  • Step 2: Select “Freeze” from the “View” menu.
  • Step 3: Select the number of columns that need to be frozen in the Columns section.

how to quickly freeze rows in google sheets

Advantages of List View in Google Sheets

The advantages of Google Sheets List View are given below:

  1. On a slow Internet connection, you can view spreadsheets.
  2. Webkit-based browsers (iPhone, Android, Nokia S60, Palm Pre), Opera Mobile, or Opera Mini can be used to view spreadsheets on mobile phones.
  3. Get a basic view of your data that includes or excludes formatting.
  4. Data can be sorted and filtered without being changed.
  5. Enter data in a straightforward manner.
  6. Allow sorting and filtering on a read-only spreadsheet.

Disadvantages of List View in Google Sheets

  1. We cannot select cells in List View.
  2. Renaming sheets in List View is impossible.
  3. We cannot Format data in List View.
  4. Auto-fill is not possible in List View.
  5. We cannot edit formula in List View.
  6. Add, view, delete comments is not possible in List View.
  7. We cannot add new collaborators and viewers in List View.
  8. In List View, we cannot Insert images, charts and gadgets.

How to Gain Insights to Your Google Sheets Data via Explore: Charts, Pivot Tables

A Google Spreadsheet with a lot of data might be intimidating. Thanks to Google Sheets Explore. With a single click in Google Sheets, we can generate summaries and visualizations of the data. Google Sheets Explore is almost as if you had a data analyst sitting next to you. However, if you don’t know how to use Explore to gain insights into your data, then this page’s Sheet Tips will help you every step of the way about Google Sheets Explore. Read further to find more.

Sheets Tips

What Insights We Can Gain Using Google Sheets Explore Feature?

The uses of the Google Spreadsheets Explore feature are given below:

  1. The instant charts will help you to view the trends in your data.
  2. It will automatically generate a pivot table based on the dataset.
  3. One can also ask questions about the data and receive prompt responses from Google Sheets explore. Suggestions for answers include infographics, pivot tables, and formulas. (Only in English)
  4. We can select only the cells or columns we want to graph to graph a portion of your data quickly in worksheets.

explore in goole sheets2

How to Start Exploring Data using Google Sheets Explore?

The steps to explore data using Google Sheets Explore are given below:

  • Step 1: In Google Sheets, open a spreadsheet where you want to analyze the data.
  • Step 2: Select the range of rows, columns, and cells for which you want to get insights into the data.
  • Step 3: Now click on the “Explore” button which is towards the bottom of the sheets.
  • Step 4: Depending on the data in the sheet, you will be provided with options with the help of which you can get the insights for your data.

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

For example, you will be able to perform the following operations.

  1. Hover your mouse over a graphic to see the data it is based on in the spreadsheet.
  2. Enter a search term or select a recommended topic to ask about your data. You may add a formula to your answer by dragging it into a cell.
  3. Add a chart by hovering over it and clicking Insert chart.
  4. Insert a pivot table by hovering over it and clicking Insert pivot table.

Highlights of Google Sheets Explore Option

The highlights of Explore option are given below:

  1. You may see recommendations and suggestions by clicking the green symbol.
  2. There is no analysis to view while the icon is grey.
  3. Charts and analyses will appear, displaying trends and patterns in your worksheet’s data.
  4. Hover your mouse over a graphic to discover which data from the spreadsheet is being used.
  5. By choosing various regions of data in your spreadsheet, you may modify the suggestions.

explore in goole sheets2

How to Close Explore Option?

It is no rocket science to close the explore feature in Google Sheets. You will have to simply click on the X (Cross icon) in the Explore panel to close the Explore window.

explore in goole sheets2

Google Spreadsheet Limitations: File Size, Row, Columns, Cell Limit in Google Sheets

There are a handful of Google Sheets features that make it stand tall compared to other spreadsheets software. However, Google Sheets also have few limitations in file size, row or column limit, and so on.

On this page, let us discuss everything about Google Sheets Limitations so that you can use the spreadsheet accordingly. Also, with the help of Google Sheet tips, you will find one or another way to overcome these limitations.

Sheets Tips

Table of Contents

What are the Limitations of Google Sheets?

Although Google Spreadsheets are highly powerful and convert easily from Excel, they have certain disadvantages discussed below.

  1. Spreadsheets generated in or converted to Google Sheets can include only up to 5 million cells.
  2. The maximum number of columns in Google Sheets are 18,278.
  3. Number of Tabs: Each workbook can have only 200 tabs.
  4. Formulas for Google Finance: 1,000 Finance formulas on Google Sheets
  5. 50 cross-workbook reference formulas in ImportRange
  6. Formulas for ImportData, ImportHtml, ImportFeed, or ImportXml: 50 external data functions
  7. The maximum length of a string or word is 50,000 characters.

The above limitations are further discussed in detail in the below section.

Cell Limit in Google Sheets

The maximum number of cells in a Google Sheets file is 5 million. These can be included within a single worksheet or spread over many pages.

If you have reached the 5 million cell limit in fewer columns, you won’t be able to add more columns and vice versa. Also, if you have reached the 5 million cell limit in a specific number of rows, you won’t be able to add more rows.

google-sheets-limitations

 

Column Limit in Google Sheets

In Google Sheets, a spreadsheet may have up to 18,278 columns. Furthermore, even if your column number is less than 18,278, you will not be allowed to enter extra columns; if you currently have a large number of rows and exceed the 5 million cell limit.

google-sheets-limitations

Row Limit in Google Sheets

Previously, Google Sheets had a maximum of 40,000 rows, but that limit has been eliminated.

Tab Limit in Google Sheets

There is no tab limit, just like there is no row limit.

However, the number of tabs you may have in Google Sheets is limited to 5 million cells.

A new worksheet contains 26000 cells by default (100 rows and 26 columns). You can enter a maximum of 192 worksheets if you keep to the row and column limits in each sheet.

After that, you will get a dialogue box informing you of Google Sheets’ cell limit.

File Size Limit in Google Sheets

When converting a file to Google Sheets, the file size limit is 100 MB. The maximum number of rows in a file produced with Google Sheets is 5 million.

Maximum Value Limit in Google Sheets

In Google Sheets, the highest value permitted in a cell is 1.79769E+308. However, if you try to input a number larger than this; for example – 144*144, you will receive an error message stating that the highest value you may enter in a cell in Google Sheets is 1.79769E+308.

Formula Limitations in Google Sheets

Like row, column, and file size limit, Google Sheets also have formula limitations which are explained below:

  1. GoogleFinance formulas: 1,000 GoogleFinance formulas
  2. ImportRange formulas: 50 cross-workbook reference formulas
  3. ImportData, ImportHtml, ImportFeed, or ImportXml formulas: 50 functions for external data
  4. Maximum string length is 50,000 characters

How to Open Google Sheet on a Specific Tab? (Link or Share Specific Tab in Google Sheets)

Using a unique URL of an individual worksheet, you can open a Google Sheet on a specific tab.

We can use some smart ways to view and share our Drive files and sheets because everything in Google Sheets is accessible via a web connection i.e., URL. We use the unique URL of an individual worksheet in Google Sheets to discover a direct link.

In this article, let us understand everything about how to Open Google Worksheets on a specific tab with the help of Google Sheet tips. Read further to find more.

Table of Contents

How to Get a Unique URL for an individual worksheet in Google Sheets?

Below are the steps to get the unique URL for individual worksheet in Google sheets,

  • Step-1: From the Google applications, Select Sheets.
  • Step-2: Select Blank Sheet from the Start a new spreadsheet.
  • Step-3: Create new data in Sheet 1.
  • Step-4: Click on the + symbol (Add Sheet) which is available on the bottom left side.
  • Step-5: Create new data in Sheet 2.
  • Step-6: Unique URL for Sheet 1 will be available in the address bar as shown in the below image.

open sheet google sheets

 

The Unique URL is – https://docs.google.com/spreadsheets/d/1hOFIRWj6swqM5Cxikh7bm-5gDnOREanEWPdKg6JdV54/edit#gid=0

The URL is divided into individual parts,

  • Standard first part: https://docs.google.com/spreadsheets/d/
  • Sheet Unique ID: 1hOFIRWj6swqM5Cxikh7bm-5gDnOREanEWPdKg6JdV54
  • Worksheet ID: /edit#gid=0

Step-7: Click on Sheet 2 which is available at the bottom left-hand side and the Unique URL for Sheet 2 will be available in the address bar as shown in the below image,

open sheet google sheets

 

Here, The Unique URL is – https://docs.google.com/spreadsheets/d/1hOFIRWj6swqM5Cxikh7bm-5gDnOREanEWPdKg6JdV54/edit#gid=1082559135

The URL is divided into below individual parts,

  • Standard first part: https://docs.google.com/spreadsheets/d/
  • Sheet Unique ID: 1hOFIRWj6swqM5Cxikh7bm-5gDnOREanEWPdKg6JdV54
  • Worksheet ID: /edit#gid=1082559135

NOTE: If you have a Spreadsheet with multiple sheets, the ID at the end of the URL will change as you move between them.

How to Get a Unique URL for Filter Views in Google Sheets?

Below are the steps to get the unique URL for filter views in Google sheets,

  • Step-1: From the Google applications, Select Sheets
  • Step-2: Select Blank Sheet from the Start a new spreadsheet
  • Step-3: Create new data in Sheet 1
  • Step-4: Click on the + symbol (Add Sheet) which is available on the bottom left side
  • Step-5: Create new data in Sheet 2
  • Step-6: Place the cursor on the cell having the data and click on the Filter views from the toolbar
  • Step-7: Select Create a new filter view
  • Step-8: Now you get the Unique URL with the filter ID created as shown in the below image,

 

open sheet google sheets

Here, The filter id URL is – https://docs.google.com/spreadsheets/d/1hOFIRWj6swqM5Cxikh7bm-5gDnOREanEWPdKg6JdV54/edit#gid=0&fvid=1235084474

The URL is divided into below individual parts,

  • Standard first part: https://docs.google.com/spreadsheets/d/
  • Sheet Unique ID: 1hOFIRWj6swqM5Cxikh7bm-5gDnOREanEWPdKg6JdV54
  • Worksheet ID: /edit#gid=0
  • Filter ID: &fvid=1235084474

If you want to link someone to a certain sheet, simply copy and paste the URL into the address bar, and it will open on that sheet.

You may send these URLs to a colleague in an email or a chat message to send them exactly where you want them, providing a customized view for each user.

How to Add ColourFul Stripes in Google Sheets? Cell Shading in Google Spreadsheet

We can apply the colour for the specific rows or columns with the help of the Conditional Formatting option available in the menu bar. The data is easier to read when the alternate rows are coloured. These are also called zebra lines.

The Conditional Formatting tool in Google Sheets allows you to format cells based on certain criteria, such as whether they contain a specific word or number.

In this article, let us understand how to add colourful stripes in google sheets with the help of Google Sheet tips. Read further to find more.

Table of Contents

How to Colour Alternative Rows in Google Sheets?

We can colour alternative rows in google sheets with the help of the Conditional formatting tool. Below are the steps to colour alternative rows in google sheets,

  • Step-1: Select the cells in which you want to colour the rows
  • Step-2: Click on the Format tab and select the Conditional Formatting option from the toolbar
  • Step-3: Scroll down in the Format Rules box and Select ‘Custom Formula is‘ from the ‘Format cells if’
  • Step-4: Enter the formula =ISEVEN(ROW()) in the below field.
  • Step-5: Choose a formatting style to apply specific colours. You can either choose the default selection or use the toolbar below it to access other Fill colour possibilities.
  • Step-6: Click on Done to view the changes you have made.

color google sheets

In the above formula – =ISEVEN(ROW()),

It returns true for all cells with an even row number. This formula is checked in each cell through conditional formatting, and every cell that returns TRUE is filled with the given colour.

In order to colour the odd rows, then you could use formula – =ISODD(ROW())

How to Colour Alternative Columns in Google Sheets?

We can colour alternative columns in google sheets with the help of the Conditional formatting tool. Below are the steps to colour alternative columns in google sheets,

  • Step-1: Select the cells in which you want to colour the columns.
  • Step-2: Click on the Format and select the Conditional Formatting option from the toolbar
  • Step-3: Scroll down in the Format Rules box and Select ‘Custom Formula is‘ from the ‘Format cells if
  • Step-4: Enter the formula =ISEVEN(COLUMN()) in the below field.
  • Step-5: Choose a formatting style to apply specific colours. You can either choose the default selection or use the toolbar below it to access other Fill colour possibilities.
  • Step-6: Click on Done to view the changes you have made.

color google sheets

In the above formula – =ISEVEN(COLUMN()),

It returns true for all cells with an even column number. This formula is checked in each cell through conditional formatting, and every cell that returns TRUE is filled with the given colour.

In order to colour the odd column, then you could use formula – =ISODD(COLUMN())

How to Colour Every Third in Google Sheets?

Let us understand how to colour every third row or column with the formula below,

  • For colouring every third row, use the formula =MOD(ROW(),3)=0
  • For colouring every third column, use the formula =MOD(COLUMN(),3)=0

Below are the steps to colour every third row,

  • Step-1: Select the cells in which you want to colour the rows
  • Step-2: Click on the Format and select the Conditional Formatting option from the toolbar
  • Step-3: Scroll down in the Format Rules box and Select ‘Custom Formula is’ from the ‘Format cells if’
  • Step-4: Enter the formula =MOD(ROW(),3)=0  in the below field.
  • Step-5: Choose a formatting style to apply specific colours. You can either choose the default selection or use the toolbar below it to access other Fill colour possibilities.
  • Step-6: Click on Done to view the changes you have made.

color google sheets

The formula =MOD(ROW(),3)=0 returns TRUE for all cells that return 0 as the remainder, and that cell is highlighted with the given colour.

You may use the same logic to every fourth, fifth, sixth, and so on row.

Similarly, you can use the formula =MOD(COLUMN(),3)=0 for colouring every third column in google sheets.