Merge Tables In Google Sheets

So, you want to make a 'table' in Google Sheets. Here's how you do it!

Microsoft Excel has long (for over ten years) had a type of data called a 'table'. Tables in Excel are cool because you can name them and then write really easy-to-parse formulas with them.

In this Microsoft Excel table, if I named it MyTable, instead of doing a formula like =sum(c4:b12)2, I could do =sum(MyTable[Oranges]). See how easy that is to read??

Aside from that, Microsoft Excel tables

  1. Look nice, and
  2. Are easy to sort and filter and do things like remove duplicates.

Here's a way to merge that works pretty well for all but the most complex tables. Start with your two tables. Add a new blank row to the first table. Select the entire second table and copy it (Ctrl+C). Put your cursor back into the first cell of the blank row you created (do not select the entire row), then paste (Ctrl+V). This launch adds the ability to merge the cells of a table in Google Docs. Merged table cells can span more than one row and/or column. To merge table cells, highlight the cells to be merged in your table and select Merge cells from the Table menu (or right click and select Merge cells). In this extension, you will format titles and tables in your spreadsheet using Google Sheets formatting tools. Formatting makes your spreadsheet more interesting for your viewers and helps show your data more clearly. To start, open your probability spreadsheet and make a copy. Then, create a title for the table you made.

So how do you make a table in Google Sheets?

There are three parts to making tables in Google Sheets:

  1. Making it look pretty, and
  2. Giving it a name so you can manipulate it easily.

Creating a table in Google Sheets you can filter

The first part of creating a table in Google Sheets is making a range you can filter.

Here's the basic, unfiltered table.

All you have to do is hit the filter button on the toolbar.

If you don't have the toolbar, go to the menu and from 'Data' choose 'Create a filter'.

Your list is now filterable, like this.

One important note is that you can only have one filtered table per sheet. This is a disadvantage compared to Tables in Microsoft Excel.

Making your Google Sheets table pretty

This part is easy — it's easy to add stripes to your Google Sheets table to make it look like an Excel table.

Choose the area of your table. Then go to the Format menu and choose Alternating colors.

Your table will then look nice and pretty!

You can use one of the preset themes, or you can define your own (I defined my own below).

Naming your Google Sheets table and accessing it via the name

This is the hardest part, and the part where Microsoft really made it easy to make tables. They must have patented this design, because otherwise I can't imagine why Google Sheets hasn't implemented the same thing.

The good news is that you can name ranges in Google Sheets and access them by name.

The bad news is that even if you apply every 'hack' in the book, the formulas will never look as clean as they do in Microsoft Excel.

Here is my favourite way to simulate Microsoft Excel tables in Google Sheets: using query().

My second favourite way is to give individual columns names.

To do it the query() way, first, give your table a name. Select the area and choose Data --> Named ranges.

Now on the right toolbar, give the table a name.

You can now query your data using =query(). For example, to get the sum of all Apples sales, you want the sum of all the data in column B.

If this is your first time seeing query() in Google Sheets, that's a whole other topic!

Another easier way that you might also find useful is to give names to individual columns. That way, if your columns move around, your name still applies to just that column.

In that example, I gave the Oranges column the name MyTable_Oranges.

This means that to get the sum of all Oranges sales, I use the formula:

A tip to make both of these work very well: Make the whole sheet the table — trim off all other rows and columns.

Create and distribute visually rich mail merges with Gmail and Google Sheets.

Last updated: February, 2021

Contributed by Martin Hawksey, follow me on Twitter @mhawksey or read my Google Apps Script related blog posts.

Simplify the process of producing visually rich mail merges using Gmail and combining it with data from Google Sheets. With this solution you can automatically populate an email template created as a Gmail draft with data from Google Sheets. Merged emails are sent from your Gmail account allowing you to respond to recipient replies.

Technology highlights

  • Uses the Gmail Service to read drafts and to send emails.

  • Uses SpreadsheetApp service to read and write data.

Merge Tables In Google Sheets Free

Tables

Merge Google Sheets Files

Try it

Merge Tables In Google Sheets

  1. Create a copy of the sample Gmail/Sheets Mail Merge spreadsheet.

  2. Update the Recipients column with email addresses you would like to use in the mail merge

  3. Create a draft message in your Gmail account using markers like {{First name}}, which correspond to column names, to indicate text you’d like to be replaced with data from the copied spreadsheet.

  4. In the copied spreadsheet, click on custom menu item Mail Merge > Send Emails.

  5. A dialog box will appear and tell you that the script requires authorization. Read the authorization notice and continue.

  6. When prompted enter or copy/paste the subject line used in your draft Gmail message and click OK

  7. The Email Sent column will update with the message status.

Next steps

Additional columns can be added to the spreadsheet with other data you would like to use. Using the {{}} annotation and including your column name as part of your Gmail draft will allow you to include other data from your spreadsheet. If you change the name of the Recipient or Email Sent columns this will need to be updated by opening Tools > Script Editor.

The source code includes a number of additional parameters, currently commented out, which can be used to control the name of the account email is sent from, reply to email addresses, as well as bcc and cc'd email addresses. If you would like to find out more about the features of this solution including some modifications you can make for additional functionality like setting up scheduled sending here is a related blog post.

For more information on the number of email recipients that can be contacted per day you can read the Current Quotas documentation. If you would like to find out more about the coding pattern used to conditionally read and write Google Sheets data here is a related blog post.

To learn more about Google Apps Script, try out the codelab which guides you through the creation of your first script.

You can also view the full source code of this solution on GitHub to learn more about how it was built.

Feedback

Were you able to get the solution up and running?

Merge Tables In Google Sheets

Great! Check out the 'Next steps' section above to learn more.
Bummer, let us know what went wrong. If you have found a bug in the code, report the issue on GitHub or submit a pull request.

If you have an idea for another solution you'd like to see featured in ourgallery submit a request on theGitHub issue tracker.