10 Hidden Google Sheets Features You Didn’t Know About

10 Hidden Google Sheets Features You Didn’t Know About.If you use Google Sheets regularly, you are probably familiar with these tools that you use frequently. However, there are many features of this spreadsheet application that go unnoticed and underused.Here we’ll take a look at a few cool Google Sheets features that might become your favorites. Head over to Google Sheets, sign in with your Google account, and try out some of these hidden gems.

1. Extract Data From a Smart Chip

If you’ve taken advantage of smart chips in Google apps, you’ll be pleased to know that you can do even more with them. Once you insert a smart chip, you can extract data from it and put it on your sheet, making the chips even more useful.Currently, you can extract data from People, Files, and Calendar Events smart chips. This includes the name and email address, the owner and filename, and the summary and location.

After you insert a smart chip, hover over it, select it, or right-click it. Then select Data Extraction.

When the sidebar opens, use the Extract tab to check the boxes for the elements you want to extract.

Use the Extract to field to enter or select the location on the worksheet where you want to place the data.

Select Extract and you will see your data displayed in the selected location.

If you need to update the retrieved data, you can use the Update and Manage tab in the sidebar.

2. Create a QR Code

QR codes are a popular way to share information, direct people to your site, and even offer discounts. By creating your own QR code in Google Sheets without add-ons or third-party tools, you or your collaborators can get up and running quickly.To create a QR Code, you will use the Google Sheets IMAGE feature and link to the Google Root URL: https://chart.googleapis.com/chart?.Here we will refer to the website in cell A1 using the below formula. Put the formula in the cell where you want the QR code.=IMAGE(“https://chart.googleapis.com/chart?chs=500×500&cht=qr&chl=”&ENCODEURL(A1)) Use the following arguments to build the formula:

CHS argument: specify the dimensions of the QR code in pixels (chs=500×500).
CHT Argument: Provide a QR code (cht=qr).
CHL argument: Select URL data (chl=”&ENCODEURL(A1)).

Then use the ampersand (&) operator to concatenate the arguments. When you see the code, you may need to resize the row and/or column to view it at full size. Then scan the QR code to make sure it works the way you expect. You can also use optional arguments to encode the data in a certain way or set a correction level. To learn more about these arguments, visit the Google Charts infographic help page for QR codes.

3. Insert a Drop-Down List

Dropdown lists are a great data entry tool. By selecting an item from the list, you can ensure that you are entering the correct data and at the same time reduce the number of errors.Since the introduction of drop-down lists in Sheets, this feature has been improved to make it easier to create and manage these useful lists.

Insert a dropdown list by doing one of the following:
Select Insert > Dropdown from the menu.
Right click and select Dropdown.
Enter the symbol @ (At) and select Dropdowns in the Components section.

The Data Validation Rules sidebar opens. Enter the location of the list in the Apply to Range field and ensure that Drop-Down is selected in the Criteria drop-down menu.

Next, add list items to the Option boxes, and optionally select colors for them on the left.

To display help text, select an action for invalid data or select a display style, expand the Advanced Options section.

When done, select Done. Then use the new dropdown to enter data into the sheet.

4. Validate an Email Address

If you have a spreadsheet containing email addresses, be it Gmail, Outlook, or whatever, you can verify that they are valid. While Sheets doesn’t tell you if an address is legitimate, it does show if it’s properly formatted with the @ (At) symbol and domain.

Select the cells you want to validate and go to Data > Data Validation.

When the Data Validation Rules sidebar opens, select Add Rule, validate or change the cell values in the Apply to Range field, and select Text is a valid email address in the Cree dropdown terii”.

If necessary, select additional options such as displaying help text, displaying a warning, or rejecting input. Select Done to save and apply the validation rule.

You can then test validation and options by entering an invalid email address.

5. Make a Custom Function

Do you love using functions and formulas in Google Sheets? If so, why not create your own? Using the “Custom Functions” feature, you can set up your own function and reuse it whenever you want.

Select Data > Named Functions from the menu.

In the Named Functions sidebar that opens, click Add New Function at the bottom to create a custom function. You can also view an example, see a demo, or learn more about this feature.

Enter the function name, description, and optional argument placeholders. Enter the formula you want to use to define the function and click Next.

View a preview of the function and either select Back to make changes or New to save the new function. Note that you can also add optional arguments if necessary.

You will see the function listed in the sidebar. Enter it in a cell on the sheet to check.

If you need to make changes, open the Named Functions sidebar again, select the three dots to the right of the function, and select Edit.

6. Use a Slicer to Filter a Chart

Charts provide a convenient and efficient way to display data. Using a slicer, you can filter the data displayed on the chart. This is useful for viewing specific parts of the chart data when needed.

Insert Slice

After inserting a chart, select it and go to Data > Add Slicer. When the sidebar opens, open the Data tab, confirm the data range at the top, and then select the column to use for the filter. You will see that the slicer looks like a black rounded rectangle that you can move or resize as you wish. Once you have the slicer, click the Filter button on the left or the arrow dropdown on the right. Then select the data you want to see in the chart by checking the boxes next to those items. Select OK and you will immediately see the chart update. To return the chart to its original view showing all the data, open the filter and click Select All > OK.

7. Quickly Calculate Data

Sometimes you want to see a quick calculation without adding a formula to a worksheet. In Google Sheets, you can simply select values and then select a calculation to view without any further action.

Select the data you want to calculate and look at the bottom right corner of the tab row. You will see a green color calculation menu which contains the sum of your data.

Open this menu and select the desired calculation. You will see a new result in this menu.

You can also just open the menu to see all available calculations in real time.

If you choose to include the calculation in your sheet, leave the cell selected and select Explore to the right of the sheet tabs. When the sidebar opens, drag the calculation you want to use into a cell on the worksheet.

8. Explore Ways to Present Your Data

Perhaps you have data in a spreadsheet but don’t know how best to display or analyze it. With the Explore feature, you can see various quick ways to present your data, view detailed information about it, and ask questions.Select your data and click Explore in the bottom right corner. When the Explore sidebar opens, you’ll see options for your data. Type a question in the Answers section, apply color in the Formatting section, or insert a chart from the Analysis section. Once you’re done, just click the X in the top right corner of the sidebar to close it.

9. Request Sheet Approvals

If you’re using a Google Workspace for Business or Education account, use the Approval feature. With it, you can request approval from others and keep track of what is approved and what is not.Go to File and select Assertions. When the Claims sidebar opens, select Make Request. In the pop-up window, add who you want to approve and optionally a message. You can also specify a due date, allow approvers to edit the worksheet, or lock the file before submitting the approval request. When you’re done, select Submit Request. If you haven’t already shared the document with approvers, you’ll be prompted to do so and assign permissions. You can then view the status by returning to the Approvals sidebar.

10. Set Up a Custom Date and Time Format

While Google Sheets provides many different ways to format dates and times, you may need something special. You can create your own date and time format with the desired structure, colors and style.

Select the cells containing date or time and go to Format > Number > Custom date and time. Alternatively, you can select the “More Formats” option from the toolbar and select Custom date and time.

When the window opens, you will see the current date and/or time format. Select an existing element at the top to change the format or remove it.

To add another element, click the arrow on the right and select the desired element from the list. You can then format that element using its arrowhead.

When you’re done, select Apply to use the custom date and time format and you should see the table update.

With these Google Sheets features, you can do even more with your data. Be sure to try one or more and see what works for you.For related tutorials, see how to find duplicates in Google Sheets using conditional formatting options.