Google 表格(Google Sheets)是 Google 最强大的产品之一，也是网络电子表格和数据管理的首选解决方案之一。然而，开箱即用，它也有它的弱点——我们将在本文中讨论这一点。
幸运的是，Sheets 支持自定义函数和公式，有助于弥补其中的一些缺点。如果用户精通公式或者可以编写一些脚本(scripting)，那么表格可以像他们所做的那样强大。
表格本身不支持的一项特殊功能是能够在Google 表格(Google Sheets)中查找重复项——无论是单元格还是行中的重复数据。虽然有一种方法可以快速删除重复条目，但使用“数据(Data)”菜单下的“删除重复(Remove duplicates)项”选项，没有办法只隔离这些重复项。好消息是有一种方法可以做到这一点。
在本文中，让我们讨论如何利用条件格式在Google 表格中查找重复项。(Google Sheets)
什么是 Google 表格中的条件格式？(What Is Conditional Formatting In Google Sheets?)
条件格式是许多电子表格编辑器中的一项功能，它允许用户根据某些条件将基于文本的格式和其他可视格式应用于单元格。
例如，用户可能正在使用电子表格记录他们的每月预算，想一目了然地知道他们是否超过了 500 美元的支出限额。在计算当月总数的电子表格列中，可以将条件格式规则设置为价值超过 500 美元的粗体行，以便轻松查看发生的位置。
在Google 表格(Google Sheets)中，条件格式允许用户根据一长串预设规则或通过输入自定义公式来应用格式。可用的格式样式更改包括粗体、斜体、下划线、删除线、文本颜色和单元格颜色。
此外，用户可以使用色标代替单一颜色，并定义最小值和最大值以及它们各自的颜色，以实现自动色标。
如何使用条件格式在 Google 表格中查找重复行(How To Use Conditional Formatting to Find Duplicate Rows In Google Sheets)
对于任何处理冗长数据表的人来说，重复的条目可能会成为有时会打断事情流程的问题。用户可能不想简单地删除表格本身支持的这些数据，而是分析和使用它。
使用具有条件格式的自定义公式，这是可能的。实现此目的的第一步是通过单击其上方的列标签突出显示需要搜索重复项的整个列。
突出显示后，右键单击并选择条件格式(Conditional formatting)。这将在窗口右侧打开一个面板，显示条件格式规则。选定的列范围将在“应用到范围(Apply to range)”字段中预填充，因此无需触摸。
接下来，您要单击Format rules下面的下拉菜单，将此条件更改为Custom formula is。完成后，下拉菜单下方将出现一个文本字段，应输入以下公式：
=countif(A:A,A1)>1
但是，需要对其进行修改以将“A”的所有实例替换为在Google 表格(Google Sheets)中搜索重复项的列字母，这将显示在“应用到范围(Apply to range)”字段中。
最后一步是设置格式以应用于该行中包含多次找到的数据的所有单元格。如果处理大量数据，设置的最佳格式可能是填充颜色。使用它，黄色、粉红色或柠檬绿色等明亮且引人注目的颜色将真正脱颖而出，并在滚动时一眼就能注意到重复项。
现在，包含至少在另一行中找到的数据的所有行都将以所选的任何方式突出显示或格式化。如果删除了重复行，则剩余的单行将自动删除其条件格式。
有人可能会问，“当Google(Google)支持通过两次简单的点击删除重复项时，为什么还要经历这个麻烦呢？” 嗯，这个系统是不灵活和不完善的。例如，无法更改删除哪个重复项。一些用户可能希望删除第一个实例，而另一些用户则希望删除最后一个实例——Google没有提供任何选项并删除除第一个之外的所有重复项。
使用这个简单的条件格式规则，用户可以完全控制管理重复项，而不会冒险破坏他们的工作表。最重要的是条件格式是完全动态的，因此如果您选择以某种方式删除所有重复项，则永远不需要删除格式。
但是，如果需要删除条件格式规则，只需单击“格式(Format)”菜单，然后单击“条件格式(Conditional formatting)”，将显示与以前相同的右侧菜单。用户可以将光标悬停在显示我们刚刚创建的自定义公式的行上，然后单击出现的垃圾桶图标将其删除。
当 Google 表格查找重复项以删除它们的本机选项不够强大或以无法承受的方式扰乱数据时，本文中突出显示的简单自定义公式允许用户自行处理。
您对使用此条件格式规则有任何疑问或意见吗？如果是这样，请在下面给我们留言。
How to Find Duplicates In Rows And Cells In Google Sheets
Google Sheets is one of Google’s most powerful products and one of the go-to solutions for spreadsheet and data management on the web. However, out of the box, it has its weak points—one that we’ll discuss in this article.
Luckily, Sheets supports custom functions and formulas that help make up for some of these shortcomings. If users are savvy with formulas or can do a little bit of scripting, Sheets can be as powerful as they make it.
One particular functionality that Sheets doesn’t natively support is the ability to find duplicates in Google Sheets – whether it’s duplicate data in cells or rows. While there is a way to quickly remove duplicate entries, using the Remove duplicates option under the Data menu, there’s no way to only isolate those duplicates. The good news is that there is a way to accomplish this.
In this article, let’s go over how to find duplicates in Google Sheets by taking advantage of conditional formatting.
What Is Conditional Formatting In Google Sheets?
Conditional formatting is a feature present in many spreadsheet editors which allows the user to apply text-based and other visual formatting to a cell based on certain conditions.
For example, a user may be recording their monthly budgeting using a spreadsheet, wanting to know at a glance if they go over a $500 spending limit. In the spreadsheet column where the total for the month is tallied, a conditional formatting rule can be set to bold rows with a value over $500, making it easy to see where this occurred.
In Google Sheets, conditional formatting allows users to apply formatting based on a long list of preset rules or by entering a custom formula. The formatting style changes available include bold, italics, underline, strikethrough, text color, and cell color.
Additionally, users can use a color scale, instead of a single color, and define a minimum and maximum value, and their respective colors, for automatic color scaling.
How To Use Conditional Formatting to Find Duplicate Rows In Google Sheets
For anyone working on a lengthy sheet of data, duplicate entries can become a problem that will sometimes break the flow of things. Users may not simply want to delete this data, which Sheets natively supports, and instead analyze and work with it.
Using a custom formula with conditional formatting, this is possible. The first step to achieve this is to highlight the entire column that needs to be searched for duplicates by clicking on the column label above it.
Once highlighted, right-click and select Conditional formatting. This will bring up a panel on the right side of the window that shows conditional formatting rules. The column range selected will be prefilled in the Apply to range field, so that won’t need to be touched.
Next, you want to click on the dropdown menu beneath Format rules to change this condition to Custom formula is. A text field will appear beneath the dropdown menu after this is done, and the following formula should be entered:
=countif(A:A,A1)>1
However, it will need to be modified to replace all instances of “A” with the column letter that is being searched for duplicates in Google Sheets, which will be shown in the Apply to range field.
The final step is to set the formatting to apply to all cells in this row that contain data found more than once. If working with a large set of data, the best formatting to set might be a fill color. Using it, a bright and noticeable color such as yellow, hot pink, or lime green will really stand out and allow for noticing duplicates at a glance while scrolling.
Now, all rows that contain data that is found in at least one other row will be highlighted or formatted in whatever way was chosen. If duplicate rows are deleted, the single row remaining will have its conditional formatting removed automatically.
One might ask, “Why go through this trouble when Google supports duplicate removal in two simple clicks?” Well, this system is inflexible and imperfect. For example, there is no way to change which duplicate is deleted. Some users may want the first instance deleted, and others the last—Google gives no option and removes all duplicates but the very first.
Using this simple conditional formatting rule, the user is in complete control of managing duplicates in a way that won’t risk breaking their sheet. Best of all is that conditional formatting is entirely dynamic, so you’ll never need to remove formatting if you choose to remove all duplicates in some way.
However, should the conditional formatting rule ever need to be deleted, it’s as simple as clicking on the Format menu and then Conditional formatting, which will show the same right-side menu as before. Users can hover their cursor over the row that shows the custom formula we’ve just created and click on the trash can icon that appears to remove it.
When Google Sheets’ native option to find duplicates to remove them just isn’t powerful enough or scrambles data in a way that can’t be afforded, the simple custom formula highlighted in this article allows users to take matters into their own hands.
Do you have any questions or comments about using this conditional formatting rule? If so, please leave us a comment below.
