如果您经常使用 Google 表格，您可能会熟悉那些经常使用的工具。然而，该电子表格应用程序的许多功能未被注意到且未得到充分利用。
在这里，我们将介绍几个很酷的Google 表格(Google Sheets)功能，这些功能可能会很快成为您的最爱。前往Google 表格，使用您的(Google Sheets)Google帐户登录，然后尝试其中一些隐藏的瑰宝。
1. 从智能芯片中提取数据
如果您已经利用了Google 应用程序中的智能芯片(Smart Chips in Google’s apps)，那么您会很高兴知道您可以用它们做更多事情。插入智能芯片(Smart Chip)后，您可以从中提取数据并将其放入工作表中，从而使芯片更加有用。
您当前可以从人员、文件(File)和日历事件智能芯片(Calendar Event Smart Chips)中提取数据。这包括姓名和电子邮件、所有者和文件名以及摘要和位置。
- 插入智能芯片(Smart Chip)后，将光标悬停在其上，选择它，或右键单击。然后，选择数据(Data)提取。
- 当侧边栏打开时，使用“提取”(Extract)选项卡标记要提取的项目的复选框。
- 使用提取(Extract)到字段输入或选择您想要数据的工作表位置。
- 选择提取(Pick Extract)，您将看到数据显示在您选择的位置。
如果需要刷新提取的数据，可以使用侧边栏中的
刷新和管理选项卡。(Refresh)
2. 创建二维码
二维码是共享信息、引导人们访问您的网站甚至提供折扣的流行方式。通过在Google 表格中(Google Sheets)创建您自己的二维码（(creating your own QR code)无需加载项或第三方工具），您或您的协作者可以快速采取行动。
要制作 QR 码，您将使用Google Sheets IMAGE函数和指向 Google 根(Google Sheets IMAGE)URL的链接：https://chart.googleapis.com/chart?。
在这里，我们将使用下面的公式链接到单元格 A1 中的网站。将公式放入您想要 QR 码的单元格中。
=IMAGE(“https://chart.googleapis.com/chart?chs=500×500&cht=qr&chl=”& ENCODEURL (A1))
使用以下参数来构建您的公式：
- CHS参数：定义 QR 码的尺寸（以像素为单位）（chs=500×500）。
- CHT参数：指定 QR 码 (cht=qr)。
- CHL参数：选择URL数据 (chl=”& ENCODEURL (A1))。
然后，使用与运算符 (&) 连接参数。
看到代码后，您可能需要调整行和/或列的大小才能查看其完整大小。然后，扫描二维码(scan the QR code)以确保其按您的预期工作。
您还可以使用可选参数以特定方式对数据进行编码或分配校正级别。有关这些论点的更多信息，请查看Google Charts Infographics 参考页中(Google Charts Infographics reference page)的 QR 码。
3. 插入下拉列表
下拉列表是很棒的数据输入工具。通过从列表中选择一个项目，您可以确保输入所需的数据，同时可以减少错误。
自从在 Sheets 中引入下拉列表(drop-down lists in Sheets)以来，该功能得到了增强，为您提供了一种更简单的方法来创建和管理这些有用的列表。
- 通过执行以下操作之一插入下拉列表：
- Select Insert > Dropdown从菜单中
选择“插入”>“下拉菜单” 。
- 右键单击并选择下拉菜单。
- 键入 @ (At) 符号并在“组件”部分中选择(Components)“下拉菜单”(Dropdowns)。
- 然后您将看到数据验证规则(Data Validation Rules)侧边栏打开。在“应用于(Apply)范围”框中输入列表的位置，并确认在“条件”(Criteria)下拉菜单
中选择“下拉” 。(Dropdown)
- 然后，在选项(Option)框中添加列表项，并可以选择在左侧为其选择颜色。
- 要显示帮助文本、选择无效数据的操作或选择显示样式，请展开“高级(Advanced) 选项”(Options)部分。
- 完成后，选择“完成”(Done)。然后，使用新的下拉列表在工作表中输入数据。
4. 验证电子邮件地址
当您的电子表格包含电子邮件地址（无论是Gmail、Outlook或其他地址）时，您可能需要确保它们有效。虽然表格不会显示地址是否合法，但它会显示地址的格式是否正确（包含 @ (At) 符号和域）。
- 选择要检查的单元格，然后转到菜单中的
Data > Data
- 当“数据验证规则”(Data Validation Rules)侧栏打开时，选择“添加规则”，确认或调整(Add)“应用(Apply)到范围”字段中的单元格，然后在“条件”(Criteria)下拉框中
选择“文本(Text)是有效电子邮件” 。
- （可选）选择高级(Advanced) 选项(Options)，例如显示帮助文本、显示警告或拒绝输入。选择“完成”(Pick Done)以保存并应用验证规则。
然后，您可以通过输入无效的电子邮件地址来测试验证和选项。
5. 制作自定义函数
(Are)您喜欢在 Google 表格中使用函数和公式(formulas in Google Sheets)吗？如果是这样，为什么不创建自己的呢？使用自定义函数(Custom Function)功能，您可以设置自己的函数并随时重复使用它。
- Select Data > Named从菜单中
选择数据 > 命名函数。
- 在打开的命名函数(Named Functions)侧栏中，使用底部的添加新函数来创建自定义函数。(Add)您还可以查看示例、观看演示或了解有关该功能的更多信息。
- 输入(Enter)函数名称、描述和可选的参数占位符。输入(Enter)要用于定义函数的公式，然后选择“下一步”(Next)。
- 查看函数(Function)预览，然后选择“返回(Back)”进行更改或选择“创建”(Create)以保存新函数。请注意，(Notice)如果需要，您还可以添加可选参数。
- 然后您将在侧边栏列表中看到该功能。将其输入到工作表的单元格中进行测试。
如果您需要进行编辑，请重新打开“命名函数”(Named Functions)侧边栏，选择函数右侧的三个点，然后选择“编辑”(Edit)。
6.使用切片器过滤图表
图表为您提供了方便有效的方式来显示数据。使用切片器，您可以过滤图表中显示的数据。这有助于在需要时查看图表数据的特定部分。
插入切片机
插入图表(insert your chart)后，选择它并转到菜单中的
Data > Add
当侧边栏打开时，打开“数据”(Data)选项卡，确认顶部的“数据范围”(Data Range) ，然后选择要用于过滤器的
列。(Column)
您将看到切片器显示为黑色圆角矩形，您可以根据需要移动或调整其大小。
使用切片机
获得切片器后，选择左侧的“过滤器”(Filter)按钮或右侧的下拉箭头。然后，选择您想要在图表中查看的数据，并在这些项目旁边放置复选标记。
选择“确定”(Select OK)，您将立即看到图表更新。
要将图表返回到显示所有数据的原始视图，请打开过滤器并选择全选(Select)> 确定。
7. 快速计算数据
有时您希望查看快速计算结果(see a quick calculation)，而不需要在工作表中添加公式。在Google 表格(Google Sheets)中，您只需选择值，然后选择要查看的计算，无需任何额外的工作。
- 选择要计算的数据，然后查看选项卡行的右下角。您将看到绿色的计算菜单，其中包含数据的
总和。(Sum)
- 打开该菜单并选择您要执行的计算。您将在该菜单中看到新结果。
- 您还可以简单地打开菜单来实时查看所有可用的计算。
如果您决定将计算包含在工作表中，请保持单元格处于选中状态，然后选择工作表选项卡右侧的
“浏览” 。(Explore)
边栏打开后，将要使用的计算拖到工作表中的单元格中。
8.探索展示数据的(Your Data)方法(Ways)
也许您的电子表格中有数据，但不确定显示或分析数据的最佳方式。通过“探索”(Explore)功能，您可以查看各种快速方式来呈现数据、查看有关数据的详细信息以及提出问题。
选择您的数据并选择右下角的
“探索” 。(Explore)
当探索(Explore)侧边栏打开时，您将看到数据选项。在“答案”(Answers)部分中输入问题，使用“格式”(Formatting)部分应用颜色，或插入“分析”(Analysis)部分中的图表。
完成后，只需使用侧边栏右上角的 X 将其关闭。
9. 请求表批准
如果您将Google Workspace帐号用于商业或教育用途，请查看审批功能。有了它，您可以请求其他人批准并跟踪哪些内容已获得批准，哪些内容未获得批准。
转到文件并选择批准。
当“批准”(Approvals)侧栏打开时，选择“提出请求”。
在弹出窗口中，添加您想要批准您的请求的人员以及可选的消息。您还可以添加截止日期、允许审批者编辑工作表或在发送审批请求之前锁定文件。完成后
选择发送请求。(Choose Send)
如果您尚未与审批者共享文档，系统会要求您这样做并分配权限。
然后，您可以返回“批准”(Approvals)侧栏来查看状态。
10. 设置自定义日期(Custom Date)和时间格式(Time Format)
虽然Google 表格(Google Sheets)提供了许多不同的方式来格式化日期和时间，但也许您想要一些特别的东西。您可以使用所需的结构、颜色和样式创建自己的日期和时间格式。
- 选择包含日期或时间的单元格，然后转到Format > Number > Custom日期和时间”。或者，您可以选择工具栏中的“更多格式”选项，然后选择“自定义(Custom)日期和时间”。
- 当窗口打开时，您将看到日期和/或时间的当前格式。选择顶部的现有元素以更改格式或将其删除。
- 要添加不同的元素，请选择右侧的箭头并从列表中选择一个。然后，您可以使用箭头格式化该元素。
- 完成后，选择“应用”(Apply)以使用自定义日期和时间格式，您应该会看到工作表更新。
借助Google 表格的(Google Sheets)这些功能，您可以利用数据做更多事情。请务必尝试一种或多种，看看哪一种对您有用。
有关相关教程，请了解如何(how to find duplicates in Google Sheets)使用条件格式选项在 Google 表格中查找重复项。
10 Hidden Google Sheets Features You Didn’t Know Existed
If yoυ uѕe Google Sheets regularly, yoυ’re probably familiar with those tоols уou use often. However, there are many features of this spreadѕhеet application that gо unnoticed and underused.
Here, we’ll walk through several cool Google Sheets features that might just become your fast favorites. Head 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 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.
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.
- After you insert a Smart Chip, hover your cursor over it, select it, or right-click. Then, choose Data extractions.
- When the sidebar opens, use the Extract tab to mark the checkboxes for those items you want to extract.
- Use the Extract to field to enter or select the sheet location where you want the data.
- 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 codes are popular ways to share information, direct people to your website, and even provide discounts. By creating your own QR code in Google Sheets without add-ons or third-party tools, you or your collaborators can quickly take action.
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?.
Here, we’ll link to the website in cell A1 using the formula below. Place 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 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)).
Then, use the ampersand operator (&) to connect the arguments.
Once you see the code, you may need to resize the row and/or column to view its full size. Then, scan the QR code to make sure it works as you expect.
You can also use optional arguments for encoding the data in a particular way or assigning a correction level. For more on these arguments, check out the Google Charts Infographics reference page for QR codes.
3. Insert a Drop-Down List
Drop-down lists are terrific tools for data entry. By selecting an item from a list, you can make sure you’re entering the data you want and can reduce errors at the same time.
Since the introduction of drop-down lists in Sheets, the feature has been enhanced to give you a simpler way to create and manage these helpful lists.
- Insert a drop-down list by doing one of the following:
- Select Insert > Dropdown from the menu.
- Right-click and choose Dropdown.
- Type the @ (At) symbol and choose Dropdowns in the Components section.
- You’ll then see the Data Validation Rules sidebar open. Enter the location for the list in the Apply to range box and confirm that Dropdown is selected in the Criteria drop-down menu.
- Then, add your list items in the Option boxes and optionally select colors for them to the left.
- To display help text, pick the action for invalid data, or choose the display style, expand the Advanced Options section.
- When you finish, select Done. Then, use your new drop-down list to enter data in your sheet.
4. Validate an Email Address
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.
- Select the cell(s) you want to check and go to Data > Data validation in the menu.
- 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.
- 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.
You can then test the validation and options by entering an invalid email address.
5. Make a Custom Function
Are you a fan of using functions and formulas in Google Sheets? If so, why not create your own? Using the Custom Function feature, you can set up your own function and reuse it whenever you like.
- Select Data > Named functions from the menu.
- In the Named Functions sidebar that opens, use Add new function at the bottom to create your custom function. You can also look at an example, watch the demonstration, or find out more about the feature.
- Enter the function name, description, and optionally argument placeholders. Enter the formula you want to use to define the function and select Next.
- Check out the Function preview and either select Back to make changes or Create to save the new function. Notice you can also add optional arguments if necessary.
- You’ll then see the function in the sidebar list. Enter it into a cell in your sheet to test it out.
If you need to make edits, reopen the Named Functions sidebar, select the three dots to the right of the function, and pick Edit.
6. Use a Slicer to Filter a Chart
Charts give you handy and effective ways to display your data. Using a slicer, you can filter the data that displays in the chart. This is convenient for reviewing specific portions of the chart data when needed.
Insert a Slicer
After you insert your chart, select it and go to Data > Add a slicer in the menu.
When the sidebar opens, open the Data tab, confirm the Data Range at the top, and then pick the Column to use for the filter.
You’ll see the slicer appear as a black rounded rectangle which you can move or resize as you please.
Use a Slicer
Once you have your slicer, select the Filter button on the left or drop-down arrow on the right. Then, select the data you want to see in the chart which places checkmarks next to those items.
Select OK and you’ll see your chart update immediately.
To return your chart to the original view showing all data, open the filter and pick Select all > OK.
7. Quickly Calculate Data
Sometimes you want to see a quick calculation without adding a formula to your sheet. In Google Sheets, you can simply select the values and then choose a calculation to view without any extra work.
- Select the data you want to calculate and then look on the bottom right of the tab row. You’ll see the calculation menu in green which contains the Sum of your data.
- Open that menu and choose the calculation you want to perform. You’ll see the new result in that menu.
- You can also simply open the menu to see all available calculations in real-time.
If you decide to include the calculation in your sheet, keep the cell selected and choose Explore to the right of the sheet tabs.
When the sidebar opens, drag the calculation you want to use to a cell in your sheet.
8. Explore Ways to Present Your Data
Maybe you have data in your spreadsheet but aren’t sure of the best way to display or analyze it. With the Explore feature, you can see various quick ways to present your data, review details about it, and ask questions.
Select your data and pick Explore on the bottom right.
When the Explore sidebar opens, you’ll see options for your data. Type a question in the Answers section, apply color using the Formatting section, or insert a chart from the Analysis section.
After you finish, simply use the X on the top right of the sidebar to close it.
9. Request Sheet Approvals
If you use a Google Workspace account for business or education, check out the Approvals feature. With it, you can request approvals from others and keep track of what’s approved and what isn’t.
Go to File and select Approvals.
When the Approvals sidebar opens, choose Make a request.
In the pop-up window, add those you want to approve your request and optionally a message. You can also include a due date, allow the approvers to edit the sheet, or lock the file before sending your request for approval. Choose Send request when you finish.
If you haven’t shared the document with the approvers already, you’ll be asked to do so and assign the 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 your dates and times, maybe you want something in particular. You can create your own date and time format with the structure, colors, and style you want.
- Select the cell(s) containing the date or time and go to Format > Number > Custom date and time. Alternatively, you can select the More Formats option in the toolbar and pick Custom date and time.
- When the window opens, you’ll see the current format for your date and/or time. Select an existing element at the top to change the format or delete it.
- To add a different element, select the arrow on the right side and choose one from the list. You can then format that element using its arrow.
- When you finish, select Apply to use the custom date and time format and you should see your sheet update.
With these Google Sheets features, you can do even more with your data. Be sure to try one or more and see which come in handy for you.
For related tutorials, look at how to find duplicates in Google Sheets using the conditional formatting options.
