How to Find the Cell Addresses of the Merged Cells in Google Sheets

Have you ever been faced with a table in Google Sheets filled with merged cells and wondered how to quickly find their cell addresses? In this tutorial, I will show you a simple solution that will help you locate merged cells effortlessly.

Merging and Unmerging Rows and Columns

Before we dive into finding merged cells, let’s quickly go over how to merge and unmerge rows and columns in Google Sheets. While merging cells can be useful for certain formats like salary slips and dashboard reports, it’s important to note that it may affect some formulas, especially database functions.

To merge cells vertically or horizontally, simply select the desired range and navigate to the Format menu. From there, you can choose between options like “Merge all,” “Merge horizontally,” and “Merge vertically.” It’s worth mentioning that the “Merge cells” option will be grayed out until you select multiple cells.

Vertically (Merging Rows)

If you want to merge rows, select the values in the first row and merge them with the second row using the “Merge vertically” option. This is especially useful for merging header rows in a table.

Horizontally (Merging Columns)

To merge columns, use the “Merge horizontally” option when the values are in a column. This will combine two or more adjoining columns effortlessly.

Merging All Cells in Google Sheets

The “Merge all” option allows you to merge all the selected cells into one, creating a seamless and visually appealing layout.

Finding Merged Cells by Checking Cell Border

In small tables, you can easily identify merged cells at a glance by applying borders. By selecting the range, going to the Help menu, and searching for “Border,” you can choose the “Border type: All borders” option. This instantly applies borders to the selected range, making it easier to visually spot merged cells.

However, this method is not suitable for large tables. So, let’s move on to a unique and efficient method that will enable you to find the cell addresses of all merged cells in Google Sheets.

Find the Cell Addresses Using a Simple Formula

To find the cell addresses of merged cells, we will first replace all the blank cells, excluding merged cells, with a hyphen. Then, we can easily extract the cell addresses of the remaining blank cells.

Here’s a step-by-step breakdown of the process:

Step 1: Replacing Blank Cells with a Hyphen Except in Merged Cells

• Select the range where you want to find merged cells.
• Go to the Format menu and choose “Number” followed by “Plain Text” (don’t worry, we’ll restore the formatting later).
• Click on “Find and Replace” under the “Edit” menu.
• In the search window, enter the regex ^([t]*)$ and replace all. This action will replace all blank cells, excluding merged cells, with hyphens.
• Finally, select the range again and apply the “Automatic” format to restore the original formatting.

Step 2: Formula to Find the Cell Addresses of Merged Cells

• In a blank sheet (e.g., ‘Sheet2’), insert the following formula in cell C2:
  =ArrayFormula(if(Sheet1!C2:G7="",address(row(Sheet1!C2:C7),column(Sheet1!C2:G2)),))
• This formula will return the cell addresses of the merged cells in ‘Sheet2’.

And that’s it! With these simple steps, you can effortlessly find the cell addresses of merged cells in Google Sheets.

To experience the convenience of merging and unmerging cells in Google Sheets firsthand, check out the Mr Reviews website.

Remember, mastering Google Sheets can greatly enhance your productivity and efficiency. So why not explore other helpful tips and tricks like merging two tables, copying and pasting merged cells without blank rows, and much more?

Enjoy exploring the endless possibilities Google Sheets has to offer!