Top 10 Features in Google Sheets for Advanced Users

By | July 28, 2023
0 Comment

Google Sheets is a web-based application that enables users to create, update and modify spreadsheets and share data online in real-time. Sheets was designed with the needs of agile organizations in mind. AI features mean you can tap into the right insights to make meaningful business decisions. A cloud-based architecture enables you to collaborate with anyone, anytime, anywhere.

Google Sheets

Compatibility with external systems, including Microsoft Office, removes the friction of working with multiple data sources. And built on top of Google’s infrastructure, Sheets gives you the freedom to create, while helping to keep your information secure.

1. Extract Data From a Smart Chip

By clicking or right-clicking on a file smart chip in a Sheet cell, you can select the new ‘Data extractions’ option to open up the side panel and begin well, extracting data from the source document for use directly in the active Sheet. You can currently extract data from People, File, and Calendar Event Smart Chips.

This includes name and email, owner and filename, and summary and location. If you’ve taken advantage of the Smart Chips in Google’s apps, then you’ll be happy to know you can do even more with them. After you insert a Smart Chip, you can extract data from it and place it in your sheet, making chips even more useful.

  1. After you insert a Smart Chip, hover your cursor over it, select it, or right-click. Then, choose Data extractions.
  2. When the sidebar opens, use the Extract tab to mark the checkboxes for those items you want to extract.
  3. Use the Extract to field to enter or select the sheet location where you want the data.
  4. Pick Extract and you’ll see your data display in your selected location.

If you need to refresh the extracted data, you can use the Refresh & manage tab in the sidebar.

2. Create a QR Code

QR code stands for quick response code. This is because QR codes hold information both in the vertical and horizontal patterns of squares. QR codes have a wide range of uses, but one of the most popular uses is providing a quick way to link to a specific website or webpage.

To make the QR code, you’ll use the Google Sheets IMAGE function and a link to Google’s root URL: https://chart.googleapis.com/chart?.

To create QR codes in Google Sheets:

  • Open a Google Sheets document.
  • Select a cell and type the URL that you want your QR code to link to.
  • Select a different cell, and type the following: =IMAGE(“https://chart.googleapis.com/chart?chs=200×200&cht=qr&chl=”&ENCODEURL(B2))

Use the following arguments to build your formula:

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

3. Seamlessly connect to critical data

Pull in and analyze data from the other tools you use, like customer data from Salesforce. Enterprise customers can also use Connected Sheets to analyze billions of rows of BigQuery data in Sheets – without writing any code.

Integrating Seamless AI with Google Sheets can enhance your productivity and streamline your workflow. By connecting these two apps using Appy Pie Connect, powered by AI, you can automate repetitive tasks, reduce manual effort, and achieve better collaboration between teams.

4. Email Address Validation

When you have a spreadsheet that contains email addresses, whether Gmail, Outlook, or something else, you may want to make sure they’re valid. While Sheets doesn’t show you if an address is legitimate, it does show you if it’s formatted correctly with the @ (At) symbol and a domain.

  1. Select the cell(s) you want to check and go to Data > Data validation in the menu.
  2. When the Data Validation Rules sidebar opens, select Add rule, confirm or adjust the cells in the Apply to range field, and choose Text is valid email in the Criteria drop-down box.
  3. Optionally select the Advanced Options such as showing help text, displaying a warning, or rejecting the input. Pick Done to save and apply the validation rule.

5. Create a Custom Function

Google Sheets offers hundreds of built-in functions like AVERAGE, SUM, and VLOOKUP. When these aren’t enough for your needs, you can use Google Apps Script to write custom functions — say, to convert meters to miles or fetch live content from the Internet — then use them in Google Sheets just like a built-in function.

Creating a custom function

  1. Create or open a spreadsheet in Google Sheets.
  2. Select the menu item Extensions > Apps Script.
  3. Delete any code in the script editor. For the DOUBLE function above, simply copy and paste the code into the script editor.
  4. At the top, click Save.

6. Use a Slicer to Filter a Chart

If you create a custom dashboard, you can add a slicer to filter your tables, charts, or pivot tables.

Add a slicer in Google Sheets,

  1. On your computer, open a spreadsheet at sheets.google.com.
  2. Click the chart or pivot table you want to filter.
  3. At the top, click Data and then Add a slicer.
  4. At the right, choose a column to filter by.
  5. Click the slicer and choose your filter rules:
    • Filter by condition: Choose from a list of conditions or create your own.
    • Filter by values: Uncheck any data points that you want to hide.

Save your slicer filtering selections

To keep the filters you apply on your slicer after you close your spreadsheet, set them as default. If you share your spreadsheet with others, they’ll see the default filters.

  1. On your computer, open a spreadsheet at sheets.google.com.
  2. On the right side of the slicer you want to make default, click More -> Set current filters as default.

7. Quickly Calculate Data

You can quickly calculate the sum, average, and count in Google Sheets.

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Highlight the cells you want to calculate.
  3. In the bottom right, find Explore Explore. Next to Explore, you’ll see “Sum: total.”
  4. To see more calculations, click Sum.
    • Average
    • Minimum
    • Maximum
    • Count
    • Count numbers

8. Explore Ways to Present Your Data

Please note that this functionality is also available on Google Docs and Google Slides. However, we will focus our discussion within the confines of Google Sheets. The Explore feature works differently in Google Sheets than in Google Docs. You can use it to get useful details about your data to help you analyze it. You can also use pieces that the tool provides in your spreadsheet.

9. Request Sheet Approvals

If there are multiple people collaborating on a document, it can be difficult to know when everyone is happy with a final version of the file. By requesting approval from everyone working on the file, you will receive a tracked response from each approver, and you can even set a date by which all approvals should be confirmed.

10. Set Up a Custom Date and Time Format

You can format your data in several different ways in Google Sheets so that your spreadsheet and its contents are displayed how you want them. While Google Sheets provides many different ways to format your dates and times, maybe you want something in particular.

To apply a custom date or time format to your spreadsheet:

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Highlight the data you want to format.
  3. Click Format and then Number.
  4. Click Custom date and time.
  5. Search in the menu text box to select a format. You can also add your own custom date or time format into the text box.
  6. Click Apply.
Sponsored Links

Post Comment