How to Convert Excel to Google Sheets: Saving Excel Sheet in Google Drive

Conversion of Excel to Google Sheets: There are many instances, where you would like to convert the excel files into Google sheets. Conversion of excel into Google sheets acts as a great advantage, if you want to collaborate with fellow mates or others easily. Also, Google sheets have amazing features which will help you to save time.

Converting Excel to Google Sheets can be done in a few different ways which are explained in detail. Also at the end of the page, don’t miss out to check the best Google sheet tips to work like a sheet expert.

Methods to Convert Excel To Google Sheet

We can convert excel files to Google sheets in the following three ways:

  1. Importing Excel File to Google Sheet
  2. Uploading File to Google Drive
  3. Uploading Excel File and Converting into G-Sheet

Converting Excel File To Google Sheet by Importing

This is one of the easiest methods. All you have to do is import an excel file to convert the file into a Google sheet. Follow the steps listed below to import excel files for conversion of Google sheet.

  • Step 1: Open the new Google sheet.
  • Step 2: In the menubar, click on the tab “File
  • Step 3: Select “Import” from the drop-down menu.

excel sheets to google sheets

  • Step 4: A new Import file pane opens. Now click on the option “Upload“.
  • Step 5: You can either drag the excel file or click on the option “Select a file from your computer” to upload the file.

excel sheets to google sheets-min

  • Step 6: If you have chosen “Select a file from your computer“, after uploading the file, click on the “Select” button.
  • Step 7: A dialogue window will appear when the file has been uploaded, as shown below. You can start a new spreadsheet, add new sheets to an existing Google Sheet, or replace an existing Google Sheet with the imported file.
  • Step 8: Select the button “Replace Spreadsheet“.
  • Step 9: Click on “Export Data“.

excel sheets to google sheets

That’s it, Excel is converted to a Google spreadsheet.

This method is absolutely perfect if your excel file has formulas of conditional formatting. Since the file doesn’t throw any formatting error when imported. However, this method of converting an Excel file into Google sheets will result in some functionality losses such as:

  1. Google sheets do not support macros.
  2. Interactive controls would be impossible to implement.
  3. There are several Excel capabilities that aren’t available in Google sheets such as a split worksheets, linked shapes, charts and so on.

When you have a couple of Excel files that you want to convert to Google sheets, this method is best.

Uploading An Excel File to Google Drive and then Convert to Google Sheets

If you already have some Excel files in Google Drive and don’t like the thought of Google Drive automatically converting your files to Google sheets format, then this method is perfect for you.

With the help of this method, one can choose the selected Excel file into Google sheets. The steps to convert Excel file from Google Drive into Google sheets are explained below:

  • Step 1: Open the Google Drive which consists of Excel files.
  • Step 2: Select the file and Right click on it.
  • Step 3: Now move the cursor to “Open With” and select “Google Sheets” from the drop down menu.

excel sheets to google sheets

  • Step 4: This will open the selected Excel file into the Google Sheet.

Note: The Excel file in your Google Drive will continue to remain in Excel format.

Convert Excel File to Google Sheet While Uploading to Google Drive

This method works best when you have a large number of Excel files to convert to Google Sheets.

The steps to convert the excel file to Google Sheets while uploading to Google Drive are explained below:

  • Step 1: Login to your Google Drive homepage.
  • Step 2: Move towards the Gear Icon which is present on right side of the screen.
  • Step 3: Click on “Settings” icon.

excel sheets to google sheets

  • Step 4: Now select the option “Convert uploaded files to Google Docs editor format“.
  • Step 5: Click on the “Done” button.

excel sheets to google sheets

All the files whichever you start uploding will convert them into Google Docs editor format. However, if you don’t want certain files to be edited in Google format, then just uncheck this option and update the done button.

How to Convert Google Sheet to Excel File?

You can easily convert the Google Sheet back to the Excel file by following the steps listed below:

  • Step 1: Open the Google sheet which needs to be converted to the Excel File.
  • Step 2: Click on the “File” tab under the Menu bar.
  • Step 3: Click on the “Download” option.
  • Step 4: Select “Microsoft Excel (.xlsx)“.

excel sheets to google sheets

  • Step 5: The Google sheet will be downloaded in the excel format on your device.

How to Count Cells If Not Blank in Google Sheets

Google Sheets If Not Blank: It is really difficult for one to find the cells which are not blank  in the numerous datasets mannually. To find the non blank cells in the given dataset on Google sheet, one can make use of Google Sheet count functions. The Count functions of the Google sheet will help us count the non-blank cells. In this article let’s discuss everything about Google sheets Count Empty cells function in detail.

Also check our page Google Sheet Tips & Tricks which will help you with various formulas to learn features of Google Sheet like a pro.

Count Cells If Not Blank Using COUNTA Function

In Google Sheets, COUNTA function counts all cells with a certain value or text string and does not take into account the cells that are blank.

Let’s consider that you have the following dataset. Now you will have to count the cells which are not blank using COUNTA.

Google Sheets IF Not Blank Steps

  • Step 1: Select the cell range where you will have to count the non blank cells.
  • Step 2: Now type the formula “=COUNTA(Cell Range)
  • Step 3: Press “Enter”.

The results will be displayed on the screen as shown below.

Count-Cells-If-Not-Blank-in-Google-Sheets-Countif

While COUNTA functions works most of time, it fails to fetch the accurate results if the cell contains the “Empty Strings” or “Apostrophe”.

In the above example, the actual result should 14. But, since the “Apostrophe” is present in the cell A7 & A12, the count is showing as 16.

Count-Cells-If-Not-Blank-in-Google-Sheets-Countif

Hence to overcome this and to find the accurate non-blank cells, we can use the COUNTIF function which gives accuarte results.

Count Cells If Not Blank Using COUNTIF Function

COUNTIF function on the Google sheets find the non empty cells when certain conditions are met. One can use the formula “=COUNTIF($A$1:$A$13,”?*”)+COUNT($A$1:$A$13)” to find the cells that are blank.

Let’s understand how the COUNTIF function works with an example now.

  • Step 1: Select the cell range to count the non-empty cells.
  • Step 2: Now enter the formula “=COUNTIF($A$1:$A$13,”?*”)+COUNT($A$1:$A$13)
  • Step 3: Click on “Enter” button.
  • Step 4: You will find the accurate count.

Count-Cells-If-Not-Blank-in-Google-Sheets-Countif

If you see the results here, the COUNTIF function has ignored the “Empty Strings” or “Apostrophe” and resulted the correct answer.

Count Cells IF Not Blank Using SUMPRODUCT Formula

Other easy method to check if the cell is not empty is to apply SUMPRODUCT formula. Follow the steps listed below to apply the SUMPRODUCT Formula for Google sheets count if cell contains text:

  • Step 1: Select the cell range.
  • Step 2: Enter the formula =SUMPRODUCT(LEN(First Cell Value:Last Cell Value)>0)), i.e., =SUMPRODUCT(LEN(A1:A17)>0)).
  • Step 3: Press “Enter“.

How to Count Cells If Not Blank in Google Sheets

The Google sheets gives you the count of the cells which consists the text.

However this method is not reliable since it counts the value of “Empty Strings” or “Apostrophe” as well.

How to Quickly Merge Cells in Google Sheets: Merge & Unmerge Without Losing Data

Google sheets Merge Function is one of the useful features which allows the user to Merge the Cells. One can make use of the Google Sheet Add-on function to merge the cells in one go. However, if you want to merge the cells manually, then you can use the options which are available under the “Format” option. On this page, we have explained all details on how to merge cells manually without losing data. Read on to find out more.

How to Merge Cells in Google Sheet?

Let’s consider the following example to merge cells on G-Sheet.

merge cells in google sheets

Steps to Merge Cells on Google Sheets:

  • Step 1: Select the Cells.
  • Step 2: Click on the “Format” option.

How to Quickly Merge Cells in Google Sheets

  • Step 3: Select the “Merge Cells” and click on “Merge All” from the drop-down menu.
  • Step 4: A warning message “Merging cells will only preserve the top-leftmost value. Merge anyway?” will be shown. Click on “OK“.

How to Quickly Merge Cells in Google Sheets

  • Step 4: The cells will be merged.

How to Quickly Merge Cells in Google Sheets

Note: If you click on “Merge All”, only the top-most value will be retained.

List of Options Available to Merge Cells on Google Sheets

On Google sheets, we have 3 options that help us to merge cells. The 3 different methods through which we can merge cells on Google Sheets are:

Merge All: This option merges all the selected cells into a single cell. Note that you can only combine contiguous cells. You will not be able to use merge options if you have non-contiguous cells. 

Merge-Cells-in-Google-Sheets-Merge-All

Merge Horizontally: This would merge all the cells into a horizontal cell. Even if you select multiple rows of cells, this function only merges the cells horizontally.

Merge-Cells-in-Google-Sheets-Merge-Horizontal

Merge Vertically: This would merge all the cells into the vertical cell. Even if you select multiple columns of cells, this function only merges cells into a vertical cell.

Merge-Cells-in-Google-Sheets-Merge-Vertical

How to Unmerge Cells in Google Sheets?

Follow the steps as listed below to unmerge cells in Google Sheets:

  • Step 1: Select the Merged cells on the Google sheet.
  • Step 2: Click on the “Format” option.
  • Step 3: Select the “Merge Cells” and hit on “Unmerge“.

unmerge cells on google sheet

  • Step 4: The cells will be unmerged.

What is the Shortcut for Merge Cells in Google Sheets?

Follow the steps listed below to merge cells in google sheets in a shortcut way.

  • Step 1: Move to the Google sheets menu bar.
  • Step 2: You will see a list of Google sheet shortcuts.
  • Step 3: Click on the Merge Icon. Refer to the image below.
  • Step 4: As soon you click on the “Merge” icon, you will find 4 options. Select your desired option.
  • Step 5: Click on “Enter“.

How to Quickly Merge Cells in Google Sheets

Merge Cells in Google Sheets without Losing Data

Let’s consider the following example. Here I want to merge the employee ID against the employee department under the department ID.

How to Quickly Merge Cells in Google Sheets

Follow the steps listed below to merge cells in Google Sheets without losing data.

  • Step 1: Select the cell where you would like to merge the column without losing data.
  • Step 2: Enter the formula in the selected column as “=CONCATENATE(Cell No,” “,Cell No)”
  • Step 3: Now drag down the cursor and the formula will be applied for all the cells.

How to Quickly Merge Cells in Google Sheets

Important Formulas to Merge Cells on Google Sheets

  • To merge rows without space and without losing data: “=CONCATENATE(Cell No,Cell No,Cell No)”
  • To merge rows with space and without losing data: “=CONCATENATE(Cell No,” “,Cell No,” “,Cell No)”
  • To merge rows without losing data separated by a comma: “=CONCATENATE(Cell No,”, “,Cell No,”, “,Cell No)”

FAQs on Merge Cells on Google Sheets

Question 1.
How do you merge cells without losing data?

Answer:
To merge cells without losing data, one will have to use the “CONCATENATE” option.

Question 2.
What is the message “Merging cells will only preserve the top leftmost value. Merge anyway?” shown on google sheets.

Answer:
When you merge 2 or more cells on the Google sheet, the sheet will retain only the topmost left value of a cell in the Google sheet. To confirm this, the sheets show this message.

Question 3.
Can I merge two cells and keep both data?

Answer:
Yes, with the help of the “CONCATENATE” option, one can merge two cells and keep both the data in one cell.

How to Quickly Transpose Data in Google Sheets: TRANSPOSE, Paste Special Method

Transpose Data on Google Sheets: We can convert the rows into columns and columns into rows with the help of Google sheets transpose functions. There are 2 methods by which we can transpose the data in Google sheets and they are: Using TRANSPOSE function, Paste function.

However, the method you choose to interchange the rows and columns will depend on whether you want your data to be set in static or dynamic. In this article let’s discuss everything about transposing data in Google sheets in detail.

Also, check our article Google sheets tricks and tips which will serve you as a great help to deal with sheets.

Transpose Data Using Paste Special

Let’s understand how to transpose data from one sheet to another with the help of the following example.

transpose data in google sheets

In the above example, by using the paste special, we cannot convert the columns into rows. Where row 1 will have the names and row 2 will have marks.

Google Sheet Transpose Functions Steps

  • Step 1: Select the data which you want to transpose.
  • Step 2: Copy the data. Use the keyboard shortcutCntrl+C” to copy the data.

transpose data in google sheets

  • Step 3: Now select the cell where you would like to transpose data.
  • Step 4: Now Right-click and hover over the “Paste Special“.
  • Step 5: Select the “Paste Transposed“.

transpose data in google sheets

  • Step 6: Click “Enter“.

The data will be transposed as shown in the image below.

transpose data in google sheets

Note that it transposes all the values, data and formatting. Also the data transferred is static. If the data changes, you will have to repeat the steps above in order to get the new data transposed.

For dynamic transposition of the data, we will have to use the TRANSPOSE function which is explained below.

Transpose Data Using TRANSPOSE Function

With the same data set shown above, we will try to transpose the function. Now to transpose the data on Google Sheets by using the TRANSPOSE Function are given below:

  • Step 1: Select the Cell.
  • Step 2: Now enter the formula “=TRANSPOSE(Cell Range)“. Here we have selected the cells from A1:C15.

transpose data in google sheets

  • Step 3: Press the “Enter” button.

The data will be transposed as shown below.

transpose data in google sheets

Note: In the above image, you can see that only values are pasted and formatting is not pasted.

The number of rows/columns of the data set automatically identifies Google Sheets and the number of rows/columns transposed in the transposed data takes up the relevant number.

All you have to ensure that the transposed data is sourced in empty cells. When a cell has a text/number or even a space character, the formula TRANSPOSE would not work and result in the Reference error (“REF” error). If you click on the “REF” error, it will mention what kind of error i.e, which cell is empty and so on.

Note also that some of the transposed data arrays cannot be removed. If the transposed data is to be deleted, the entire data set must be deleted.

FAQs on Google Sheet Transpose Functions

Question 1.
How to transpose multiple rows on Google sheets?

Answer:
Select the cells where you would like to transpose the data and enter the formula as “=TRANSPOSE(Cell Range)”.

Question 2.
How do I transpose columns and rows in Google Sheets?

Answer:
In order to transpose columns and rows on Google Sheets, we can use either “Paste Special” or “TRANSPOSE” functions.

Question 3.
For Google sheets transpose with formatting, which method should be used?

Answer:
We should use the “Paste Special” method to transpose the data with formatting.

Using IF Function in Google Sheets: IF Statement Formula & Examples

Google Sheets IF Functions: When you want conditions to be checked on the Google sheet, you can use the IF ELSE functions. Usually, the IF function on Google sheets will determine the value and returns True or False based on the conditions set in a particular cell.

For example, let’s consider that we have a student’s scoreboards. Now we will have to check which students have cleared the exams and who got failed in the examination. To check the student’s results status from the scoreboard, we can set IF conditions on Google sheet, which helps us to find which students have cleared the examination and who hasn’t. Let’s understand how to find students results status with the help of the Google sheet tips for IF statement in detail.

How to Insert IF Function on Google Sheets?

One must be aware of IF Statement Syntax, in order to insert IF functions inside the cells of the Google sheet. The Google sheet IF Statement Syntax will contain the following attributes.

IF(logical_expression, value_if_true, value_if_false)

  • logical_expression: this is the condition for the function to check. It’s a cell expression that contains a statement that gives a logical value, namely TRUE or FALSE.
  • value_if_true: Returns the value if logical statement is TRUE.
  • value_if_false: If the conditions are not met and if you don’t specify the statement “value_if_false” argument, then the functions would return “FALSE”. However, this statement is completely optional.

Let’s see some examples of how the IF function can be used in real-life scenarios in Google Sheets.

Google Sheets IF Statement for Single Condition

Now we have a students scoreboard and we apply the IF conditions which say that students who secured below 40 marks are Fail and the students who secured above 40 marks are Pass.

if function in google sheets

Google Sheet IF Statement Steps:

  • Step 1: Select the Cell. Here we are selecting the cell “C2” on the sheet.
  • Step 2: Now enter the formula “=IF(B2>40,”Pass”,”Fail”)“.

if function in google sheets

  • Step 3: Click “Enter“.
  • Step 4: You will see the result.

if function in google sheets

Google Sheet IF Statement for Multiple Conditions

Using the same example as above, you must chalk up a grading system to a student based on the marks that were made. For example, if the student has secured between 35 to 50, then a D grade should be assigned. Likewise, C grade for the marks between 50 to 70, B grade for 70 to 90, A grade for above 90 and F grade for below 35.

google sheet if condition

So the multiple IF conditions can be applied on Google Sheet as follows:

  • Step 1: Select the “Cell” where you have to apply multiple if conditions.
  • Step 2: Enter the formula “=IF(B2<35,”F”,If(B2<50,”D”,If(B2<70,”C”,If(B2<90,”B”,”A”))))“.

if function in google sheets

  • Step 3: Click on enter. You will see the results.

if function in google sheets
Calculating Commissions Using IF Function in Google Sheets

If Function allows you to calculate in the value section of Google Sheets. The sales commission for sales representatives using the IF function is a good example.

if function in google sheets
For example, if the sales are less than 50k, a sales representative gets no commission. If the sales are between 50-80k, the salesperson gets 4% commission and 10% if the sales are more than 80k. The following is an illustrative example.

The calculation is carried out within the IF function itself in the formulation used in the example above. When the value for sales ranges from 50 to 100 K, it returns B2*4%, the commission of 4% based on the price.

  • Step 1: Select the Cell.
  • Step 2: Enter the formula “=IF(B2<50,0,IF(B2<80,B2*4%,B2*10%))“.
  • Step 3: Click on “Enter“.

if function in google sheets
Using And/OR Operators in IF Function

For example, if the sales are less than 50k, a sales representative gets no commission, if the sales are between 50-80k the person will get 4% and 10% if the sales are more than 80k. The following is an illustrative example.

if function in google sheets

The calculation is carried out within the IF function itself in the formulation used in the example above. When the value for sales ranges from 50 to 100 K, it returns B2*4%, the commission of 4% based on the price.

  • Step 1: Select the Cell.
  • Step 2: Enter the formula “=IF(AND(B2>80,C2>80%),”Yes”,”No”)“.
  • Step 3: Click “Enter“.

using if functions in google sheets

Conditional Formatting Based on Another Cell Value in Google Sheets

Google Sheet conditional formatting functions analyze the value entered inside the cell and formats the cell according to specific conditions. In most cases, the current cell value is used to apply the conditional formatting there, but it can also be used to implement conditional formatting based on another cell value.

For example, let’s consider we have a bunch of students whose scoreboard is prepared using Google Sheets. Now we have to separate the students who have secured below 35 and the students who have scored more than 80 marks. In such cases, we can use conditional formatting on one cell with the name in another cell based on the student’s marks. Also at the end of the article, we have provided few Google Sheets tips on conditional formatting. Read more to find out.

Highlight Cells Based On Another Cell Value

As discussed above, let’s take student scores as an example and see how students’ names can be highlighted based on their scores. Suppose you have the following data set and would like to highlight names that have a score of less than 35.

conditional formatting in Google sheets

How Do You Conditional Format Cells Based On Another Cell Value?

We can use conditional formatting in Google sheets based on another cell value to highlight names based on the student’s score. To apply the conditions, follow the steps as listed below:

  • Step 1: Choose the cells you wish to highlight. We are highlighting students names here
  • Step 2: Click on the “Format” option.
  • Step 3: Now select “Conditional Formatting” from the drop-down menu. Now a Conditional formatting pane will open towards the right side of the screen.

conditional formatting in Google sheets

  • Step 4: Now select the “Single Color” as shown in the image below.
  • Step 5: Now click on the “Format Rules” and select “Custom Formula” from the drop-down menu.

conditional formatting in Google sheets based on another cell

  • Step 6: In the Formula files, enter the formula as “=B2<35“. (We are considering 35 as the number here.)
  • Step 7: Under the Formatting style option, select the formatting options which you would like to apply. Here we are choosing the Blue colour.

conditional formatting in Google sheets based on another cell

  • Step 8: Now, click on “Done“. You will see the results.

conditional formatting in Google sheets based on another cell

How Does Conditional Format Cells Based On Another Cell Value Works?

Here we have chosen the cells with names and thus the cells with names are highlighted. Now to determine whether the selected formatting is to be applied to a cell or not, the condition specified simply must be checked. If TRUE is returned for the cell, the formatting will be used, otherwise, the condition will not.

Here we have used the formula = B2<35 for each cell. Therefore, if conditional formatting is the cell A2, the condition is checked to B2<=35, and when cell A3 is checked, this condition is checked to B3<=35 and so on.

Since the value of B3 is 26, the cell returns True and highlights the cell in blue colour as per our colour specifications.

Highlight Cells Based On Another Cell Value In Multiple Cells on Google Sheets

In the above example how to highlight a value-based cell in another cell. The same logic can be extended and the values in multiple cells can be used to highlight a cell on Google Sheets.

For example, consider the data set below and now we should highlight all student names in each of the three topics, whose score is less than 35. For this to work, three different cell values should be analysed and the name of the student should be highlighted if any cell value (score) is less than 35.

conditional formatting in Google sheets

Follow the steps listed below to highlight cells using Conditional Formatting based on multiple other cell values in Google Sheets.

  • Step 1: Select the cells which you would like to highlight. (Here we are highlighting the students’ names.)
  • Step 2: Now click on the “Format” tab and select “Conditional Formatting” from the drop-down menu.

conditional formatting in Google sheets

  • Step 3: Now “Conditional Formatting Pane” will open on the screen. Make sure, you are under the “Single Color” tab as shown in the image below.
  • Step 4: Click on the “Format Rules” and select the “Custom Formula is“.
  • Step 5: Now in the “Value or Formula” field, enter the formula as “=OR(B2<35, C2<35, D2<35)

conditional formatting in Google sheets based on another cell

  • Step 6: Move to the “Formatting Style” window and select the formatting types such as colours which you would like to apply. Here we have chosen the “Blue” colour.
  • Step 7: Click on “Done“.

The results will be displayed on selected cells as shown below.

conditional formatting in Google sheets based on another cell

How To Highlight Duplicates In Google Sheets?

Highlighting Duplicates in Google Sheets: When you’re working on a Google sheet that has numerous duplicate entries, then it would be difficult for you to sort duplicates entries. Manually finding the duplicates and deleting the entries one by one is a time-consuming process. Thus to overcome this, one can make use of the conditional formatting option in Google Sheets which easily highlights and delete the duplicates. On this page, we have provided Google sheet tips and tricks on how to find duplicates and delete them easily in one go. Read on to find out.

How to Find Duplicates in Google Sheets?

Consider you’re already having data set in a column and now you want to find the duplicates for the same. Let’s consider the example of an image given below. Here we will try to highlight the duplicates which are present in Column A. Follow the steps listed below to highlight duplicates in a column.

find duplicates in Google Docs
  • Step 1: Select the Column A names (excluding headers)
  • Step 2: Click on the tab “Format” as shown in the image below:
highlight duplicates in google sheets
  • Step 3: Now clicking on the “Format” tab, select the “Conditional Formatting” from the drop-down menu.
highlight duplicates
  • Step 4: As soon as you click on “Conditional Formatting“, a new tab will open. Here click on “Add another rule” as shown in the image below.
how to highlight duplicates in google sheets
  •  
  •  

 

  • Step 5: Make sure the range is right (where the duplicates need to be highlighted). If not, you can modify it from the “Apply To Range” section.
  • Step 6: Click the “Custom Formula is” option on the “Format if Cells” button.
google sheets duplicate row
  • Step 7: Enter the formula “=countif($A$2:$A$15,A2)>1” in the field below:
how to find duplicate entries in google sheets
  • Step 8: Specify the formatting of the duplicate cells from the “Formatting style” options. By default, green colour is used, but other colours and styles such as bold and italics can be specified.
  • Step 9: Click on the “Done” option.
  • Step 10: All the cells with duplicate names are highlighted in the colour given as shown below.
how to find duplicate entries in google sheets

How to Highlight Duplicate Cells in Multiple Cells?

We had all the names in a single column in this example. However, what if the names are in several columns?

You can still make the duplicate names (which is a name that happens more than once in all three columns combined) via conditional formatting. The steps below explain how to find duplicates in several columns:

  • Step 1: Choose dataset names (excluding the headers)
  • Step 2: Click on the Format menu option
  • Step 3: Click on Conditional formatting from the drop-down menu
  • Step 4: Click “add another rule“. Refer to the image below:
how to find duplicate entries in google sheets
  • Step 5: Make sure the range is right (where the duplicates need to be highlighted). If not, you can modify it from the “Apply to Range” section
  • Step 6: Now click on “Format Rules” and select the “Custom Formula“.
  • Step 7: Enter the formula as “=countif ($a$2 : $c$10, a2) > 1“.
  • Step 8: Specify the formatting of the duplicate cells from the ‘Formatting style‘ options. By default, green is used, but other colours and styles such as bold and italics can be specified.
  • Step 9: Click Done. The sheet will highlight the duplicate entries.
 duplicate entries in google sheet

 

How to Highlight Duplicate Rows/Records?

If the value is exactly the same in every cell in the row, the record will be a duplicate and finding the data entries manually will consume a lot of time. For example, you have the following dataset and want the duplicate records to be highlighted, then follow the steps listed after the image to find the duplicate entries.

row duplicates entires in google sheet

 

  • Step 1: Choose the dataset (excluding the headers)
  • Step 2: Click on the Format menu option
  • Step 3: Click on Conditional formatting for the options that appear.
  • Step 4: Click “Add a different rule
  • Step 5: Click the ‘Custom Formula is‘ option on the ‘Format if Cells‘ button
highlight duplicates in rows

 

  • Step 6: Now enter the formula as “=COUNTIF(ARRAYFORMULA($A$2:$A$10&$B$2:$B$10&$C$2:$C$10),$A2&$B2&$C2)>1“.
  • Step 7: Click on “Done”. The Google sheet will show the duplicate entries.
highlight duplicate in rows