How to Merge or Split Cells and Tables in Google Sheets

We show you how to merge cells in Google Sheets, how to do the same with columns, and how to split cells, columns, and tables that have been merged before.

Featured - How to Merge and Split Cells and Tables in Google Sheets

Google Sheets stands out as a premier tool for data management, and among its vast features, merging and splitting cells and tables are particularly vital. Merging cells simplifies data presentation, making headers and titles more prominent. For instance, if you have a spreadsheet tracking monthly sales, you might merge cells to create a single header spanning all months of a year. Similarly, if you’re organizing an event, you might merge cells to create a unified title for all related activities on a given day.

Splitting columns and cells, on the other hand, allows for detailed data breakdown, ensuring each piece of information is clearly categorized. Imagine having a column with full names and wanting to separate them into first and last names for easier sorting. Or perhaps, you’ve imported data where addresses are in a single cell, and you need to split them into street, city, and zip code columns. Google Sheets makes these tasks straightforward.

Beyond individual cells, Google Sheets’ capabilities extend to entire tables. Merging tables is a boon when you need a unified dataset, especially when combining data from various sources. For example, if you have separate tables for customer details and their purchase history, merging can help you create a comprehensive customer profile. Splitting tables is equally valuable, allowing users to segment data for more focused analysis. If you have a large table with sales data spanning several years, you might want to split it into individual tables for each year to study annual trends more closely.

This tutorial aims to provide a clear and concise walkthrough of these functionalities.

How to Merge Cells in Google Sheets

Merging cells in Google Sheets allows you to combine two or more cells into a single larger cell. This is especially useful when you want to create headers or labels that span multiple columns or rows.

Benefit: Merging cells can enhance the visual presentation of your data, making it easier to read and understand. It also helps in organizing and grouping related data together.

  1. Select the cells you want to merge in your Google sheet
     
    Windows 11 - Google Sheets - Highlight Cells
  2. Select “Format”, “Merge cells” and select “Merge horizontally” or “Merge vertically”, depending on the case
     
    Using “Merge all” you can merge a block of horizontal and vertical cells into one.
     
    Windows 11 - Google Sheets - Highlight Cells - Format - Merge Cells - Merge Horizontally
  3. The merged cells will appear as one that spreads over the area of the previous single cells
     
    Windows 11 - Google Sheets - Highlight Cells - Format - Merge Cells - Merge Horizontally - Result
  4. You can use the alignment feature to show its content in the best way
     
    Windows 11 - Google Sheets - Align Content
  5. To split a cell in Google sheets you use nearly the same procedure
     
    First, select the merged cell you want to split into its original underlying cells.
     
    Windows 11 - Google Sheets - Highlight Cell
  6. Then, select “Format”, “Merge cells” and “Unmerge”
     
    Windows 11 - Google Sheets - Highlight Cell - Format - Merge Cells - Unmerge
  7. This will unmerge the cells again in your Google sheet
     
    Windows 11 - Google Sheets - Highlight Cell - Format - Merge Cells - Unmerge - Result

How to Split Cells Into Columns Using the Split Function

The SPLIT function in Google Sheets enables you to divide the content of a cell into multiple columns based on a specified delimiter (e.g., comma, space).

Benefit: The SPLIT function is particularly handy when you have data in a single cell that you want to distribute across multiple columns. For instance, splitting full names into first and last names.

  1. Click on the cell where you want the split data to begin and add the SPLIT formula
     

    • Enter the formula: =SPLIT(C3, ",")
    • Replace C3 from our example with the reference of the cell you want to split.
    • Replace “,” with the delimiter of the source cell you want to distribute content from (e.g., ";", " " for space, etc.)
       
      Windows 11 - Google Sheets - Split Columns
  2. Use autocomplete to apply the formula to the whole column
     
    Hover over the lower right corner of the first cell with the formula and drag it down until the end of the column.
     
    Windows 11 - Google Sheets - Split Columns - Copy Formula
  3. This will split and distribute the content of the first column into two separate columns
     
    Windows 11 - Google Sheets - Split Columns - Copy Formula - Result

How to Split Text to Columns in Google Sheets

As an alternative to the SPLIT function described above, Google Sheets offers a “Split text to columns” feature that automatically divides the content of a cell into separate columns using a chosen delimiter.

Benefit: The “Split text to columns” feature provides a quick and intuitive way to separate data without the need for formulas. It’s especially useful for processing imported data, like CSV files, where data points are often combined in single cells.

  1. Highlight the source column
     
    The source column will need to have text with clear and similarly formatted delimiters, which then you can automatically split and distribute across several other columns.
     
    Windows 11 - Google Sheets - Highlight Cell
  2. Select “Data” and then “Split text to columns”
     
    Windows 11 - Google Sheets - Highlight Cell - Data - Split Text to Columns
  3. Select the separator in the dialog box
     
    Google Sheets will try to detect the right separator automatically. If that does not work, you can select it manually.
     
    Windows 11 - Google Sheets - Highlight Cell - Data - Split Text to Columns - Result - Detected Automatically
  4. Google Sheets will then spread the content of the first column across the neighboring columns
     
    Windows 11 - Google Sheets - Highlight Cell - Data - Split Text to Columns - Result

How to Merge Tables in Google Sheets

Merging tables in Google Sheets involves combining two or more similar tables into a single table, either by appending rows or columns.

Benefit: This method is beneficial when you have related data spread across multiple tables and you want to consolidate it for better analysis or reporting.

Here we show a step-by-step approach for formatted tables and tables that are not perfectly aligned so you could just delete lines to merge them into one table.

  1. Highlight the header of the second table
     
    Windows 11 - Google Sheets - Highlight Row
  2. Delete the Header row
     
    Windows 11 - Google Sheets - Highlight Row - Delete Row
  3. Click on a cell in the principal table you want to merge into
     
    Windows 11 - Google Sheets - Click on Last Cell
  4. Use “Insert”- “Rows” to extend the table
     
    Do this to create the necessary additional rows you will need for the other table.
     
    Windows 11 - Google Sheets - Click on Last Cell - Insert - Rows - Below
  5. Cut/copy the data area of the other table
     
    Windows 11 - Google Sheets - Highlight Table - Cut
  6. Jump to the upper left cell of the created extra rows in the principal table
     
    Windows 11 - Google Sheets - Highlight Table - Cut - Click on Cell
  7. Right-click and select Paste
     
    Windows 11 - Google Sheets - Highlight Table - Cut - Click on Cell - Paste
  8. This will result in one combined table where there have been two before
     
    Windows 11 - Google Sheets - Highlight Table - Cut - Click on Cell - Paste - Result

How to Split a Table in Google Sheets

Splitting a table in Google Sheets means dividing a single table into two or more smaller tables based on specific criteria, such as values in a particular column.

Benefit: Splitting tables can help in segmenting data for more focused analysis. For instance, you might split a table of sales data into separate tables for each region or product category.

  1. Cut the data you want to show in a separate table
     
    Windows 11 - Google Sheets - Cut Rows
  2. Right-click a free cell outside the table and select “Paste”
     
    Windows 11 - Google Sheets - Cut Rows - Paste
  3. You now have a separate table
     
    If needed, you can copy the header cell from the first table on top of the second table.
     
    Windows 11 - Google Sheets - Cut Rows - Paste - Result

Extra: How to Insert a Text Box in Google  Sheets

The process to insert a text box in Google Docs and Google Sheets isn’t as intuitive. For whatever reason, Google has hidden the feature behind an entirely different menu that is rarely frequented. In our other guide, we show you how to insert a text box in Google Docs or Google Sheets, while disclosing a small trick to put a text box over an image.
 

Extra: How to Make a Graph in Google Sheets

Google Sheets offers a robust suite of tools to convert your data into meaningful graphs. Whether you’re looking to create a simple line chart or a more intricate pie chart, Google Sheets has got you covered. The platform’s intuitive interface ensures that even those new to data visualization can craft compelling graphs with ease. Our other tutorial will guide you through the nuances of creating and customizing graphs in Google Sheets.
 
Featured - How to Make a Graph in Google Sheets

Extra: How to Make a Google Form / Google Survey

Google Forms is a web-based application that allows you to create and share online forms and use as a free survey maker with multiple question types. With Google Forms, you can easily design your forms with different themes and settings, send them to your target audience via email or link, analyze the responses in real time or export them to Google Sheets for further analysis. In our other guide, we show you how you can create Google Forms, share your Google survey and how to generate a results view.
 
Featured - How to create a google form