您可能不知道的 20 个有用的 Google 表格提示和技巧

如果单元格包含长句子或字符串，则它往往会溢出到相邻的单元格。您可以通过在 Google 表格中使用 wrap 函数来避免这种情况。为此，首先，选择要换行的单元格，然后转到 格式化 → 文本换行 →换行.另一种换行方法是单击工具栏中的“Wrap Text”选项。

我们都知道专有名词以大写字母开头，但在输入大量名称时忘记大写是很自然的。但别担心！Google 表格有一个名为 PROPER 的内置功能，可帮助您将名称大写。要使用此功能，您可以使用公式 =UPPER（“cell reference”）。这将自动为您选择的单元格添加适当的大小写。

您可以使用主工具栏轻松更改数字的格式。例如，如果您刚刚在列中键入了一些数字并希望将其更改为百分比，则不必手动键入 % 标记。您只需选择主工具栏中的百分比图标即可。主工具栏中有许多不同的数字格式，包括百分比、小数和货币。

Sometimes, you might want to add checkboxes, which usually help you to add yes or no (or true or false) answers. To add these, first select the cell, then go to Format, and select Checkbox. Checkboxes can make your sheet more fun and interactive.

如果某个列中的条目选择数量有限，您可以通过在 Google 表格中创建下拉菜单来轻松限制可能的选项并节省时间。首先，选择要在其中创建下拉框的单元格。接下来，单击 Data → Data Validation。然后，您将必须填写信息。

在 'Criteria' 部分，您可以选择适合您的选项。如果您选择“从范围中列出”，则必须选择列出要包含在下拉框中的选项的单元格。您还可以选择“项目列表”并输入选项，以逗号分隔（无空格）。

When you are looking at a long spreadsheet, you might sometimes forget what the individual columns are for. But you can easily freeze or lock the header row with a few clicks of the mouse. Take your cursor to the top-left corner of the worksheet, where there is a blank grey box with two thick grey lines. When you hover over the horizontal grey line, your cursor will change to a hand icon. Now hold the left mouse key and drag this grey line down. Since you only want to lock the header row (top of the row), you have to drag this line just below the first row. If you want to freeze the first few rows, you can also drag the line to the end of the first few lines.

还有另一种方法可以冻结 Google 电子表格中的单元格。单击菜单上的“查看”，然后选择“冻结”。然后您可以选择 '1 row'。

When you copy and paste a value you obtained using a formula to another Google sheet or a cell that is far away from the original cell, the value can change. This is because what you are copying is the formula, not the value itself. You can easily copy and paste a value by using the keyboard shortcut: CTRL+C / CTRL+ Shift +V. Another way to copy and paste values is right clicking on the cells where you have to paste the value and select 'Paste Special' from the menu that appears. This will open a sub-menu with different paste options. Select 'Paste values only' from these options. Now your value will automatically be pasted.

In Google sheets, you can easily copy the format of one cell on another cell without changing the data in both cells. To do this with keyboard shortcuts, first select the cells you want to copy, press Ctrl + C, click on the destination cells, and click Ctrl + Alt + V.

Google 表格为您提供了条件格式功能，它允许在满足某些条件时以某种方式格式化单元格。这种格式设置通常涉及视觉变化 - 高亮、加粗、斜体等。要设置条件格式，请选择要格式化的单元格，然后在导航菜单中选择“格式”，然后单击“条件格式”。然后设置您的 格式规则 和 格式样式.

如果您想将数组复制到 Google 表格中的另一个位置，但希望它翻转行和列，您可以轻松复制所需的单元格，然后右键单击要粘贴数组的第一个单元格。然后你会得到右键菜单。在此处，选择 选择性粘贴 → 转置。您的数组将自动粘贴，并翻转行和列。

Google 表格有许多键盘快捷键。但要记住他们所有人并不容易。您可以通过按 Ctrl + / 简单地访问 Google 表格中的快捷方式列表。此快捷方式将打开下面的窗口，其中显示了按类别分组的所有快捷方式。

Did you know that Google Sheets has a built-in google translate function that you could use to translate simple words? All you have to do is select the cell you want to translate, then use the following formula in another cell to translate it.
=GOOGLETRANSLATE(cell with text, "source language", "target language")

Here, the source language is the language your text is currently in, and the target language is the language you want to translate to.

If you are typing email addresses in Google Sheets, you have to make sure that the data you entered is correct. The easiest way to do this is by confirming that the email address format is correct. You can do this by Data Verification. First, select the cells where you want to apply data validation. Then, select Data → Data Validation from the navigation menu. Then you will see a small window you have to fill in. Make sure the Cell range is correct and choose 'Text' and 'contains' for 'Criteria.' Next, enter the @ symbol in the box next to 'contains.' Next, for 'On Invalid Data' you can either choose 'Show Warning' or 'Reject Input.'

Google 表格具有内置的超链接功能，可以链接到外部网站。要添加超链接，请选择要插入链接的单元格，然后键入以下公式：

=超链接（URL， LINK_LABEL）

Here, URL is the link to the external site, and the link label is the text you want to display on the cell.

您可以使用拼写功能轻松更正 Google 表格中的所有拼写错误。首先，选择要检查的单元格范围。然后单击 工具 选项卡并选择 拼写。然后 Google 会自动检测您的错误并提出更正建议。

在 Google 表格中，您可以轻松地以说明趋势的细线图的形式可视化您的数据。我们称之为 Sparkline。要将迷你图添加到数据中，您所要做的就是单击要插入它的单元格并键入 =SPARKLINE，然后输入括在括号中的值范围。当您 ENTER 时，您将获得一个可视化数据的小折线图。

当您向 Google 表格添加评论时，您还可以向某人发送评论的电子邮件通知。为此，首先右键单击要评论的单元格，然后选择“评论”。在出现的小弹出窗口中键入您的注释，然后键入@email地址。通过执行此作，您提到的人将收到电子邮件通知。

如果您想使用从 Google 表单获取的数据在 Google 表格中创建表格，则不必手动执行此作。您可以通过单击表单右上角的工作表图标将数据从 Google 表单直接发送到 Google 表格。然后输入表单的名称，Google Sheet 选项卡会自动打开，其中包含表单的所有数据。工作表还将具有时间戳，它显示输入数据的时间。

没有多少人知道 Google 表格提供了从 HTML 页面或站点上的表格或列表导入数据的特殊功能。此函数称为 IMPORTHTML。它会自动将数据从网站提取到 Google 表格中。

First, copy the URL of the website you want to extract data from. Next, go to Insert tab → Function→ Web →IMPORTHTML. This will open a pop-up window. Next to IMPORTHTML, you will see brackets. Put inverted commas inside the brackets and paste the URL you copied earlier between them. Type another set of inverted commas and specify the query. Hit ENTER, and the entire table from the website will be imported to the sheet within seconds.

Sometimes, you might want to display a formula on a cell to explain the calculations you have made on another cell. But once you enter this formula on a cell, Google Sheets will automatically run it as an actual formula.
To avoid this processing, you can insert an apostrophe before typing the formula. This will display your formula as a text string.