Hi!   Welcome to The Data Are Alright, a blog about tips and tricks for changemakers.  Most of you found your way here through Google (this is my most popular article, so it seems like LOTS of people need help with this feature) – and you’re in the right place!  While you’re here, why not check out some of my other popular posts and consider subscribing?

Okay, okay, I admit it, I used to be a staunch Excel snob who would turn up her nose at Google Sheets… so much so that I didn’t even realize that core Excel functions are now available in Google Sheets (like pivot tables! and vlookups!).  My, my, my how the … tables have turned!   So let’s dig into the Tables functionality and why they’re important for changemakers managing a spreadsheet…

  1.  In Tables (as opposed to regular old spreadsheets), rows alternate colors, which makes them prettier AND easier for your eyes to track
  2.  Tables offset your Column Titles in a darker color
  3.  Tables make it super easy to sort and filter your data
  4.  When you sort and filter your data, you can find blank cells and inconsistencies (and more!)  I’ll save sorting and filtering for another blog post, but trust me, you can do all kinds of awesome things with this feature combo.

Tables in Excel (refresher)

Excel makes “Format as table” really simple.  All you have to do is select the data that belong in your table, and then click “CTRL + T” (Windows) or “Apple + T” (Mac).  Alternatively, there’s a Format as Table button in the standard toolbar.

Tables in Google Sheets

Unfortunately, Sheets doesn’t have a “one stop shop” for Tables.  Here are two very simple steps that give you a lot of the same functionality, but with added Google benefits, like being free, cloud based, and shareable with multiple collaborators.

sample data no formatting
Select the data that belong in your Table (in this case, my sample Phonebanking data).  After you select your cells, Google Sheets will display a green border around the data.
sample data with filters
After selecting the data, click the the Filter button (funnel icon) in your toolbar.  If your screen is not maximized, you may need to click a small triangle button to see more options.  Then, you can click, “Filter”.  The Column Labels and Row Labels (A, B, C, 1, 2, 3, etc) will highlight green and little triangles will appear in your Column Names.  You should always select the Filter button after you have given your columns names (like Name, Called, and RSVP?
sample data with filters and alternating colors
Finally, select your data one more time and click the Format menu in your tool bar.  Then, select Alternating Colors.  Google Sheets will open an Alternating Colors dialogue box in the side bar.  You can select from pre-defined alternating color pallets or design your own.

From this point forward, as you add additional data to your rows or columns, Google Sheets will automatically incorporate your data into the alternating color theme.

Also, you can use those handy striped triangles in your column header to select, sort and filter down your data so that you can see just the important segments at any given time.  This becomes really useful if you want to see JUST the “RSVP Maybes” or just the people who haven’t been called.

I hope you all have a wonderful week!  I have some exciting TDAA projects up my sleeves, so stay tuned for more!

Published by Samantha Shain

I work as a Salesforce system administrator and declarative developer for a philanthropic foundation and I organize for social justice with EQAT's Power Local Green Jobs campaign in Philadelphia. When I am not working, organizing and data wrangling, I am a prolific knitter. I am opinionated about potlucks, philanthropy, public transit, social movement strategy and gender equity. I fantasize about getting around on a motor scooter. www.pronoun.is/she.

13 thoughts on “How to build a “table” in Google Sheets”

  1. Pingback: The Mail Merges Are Alright – The Data are Alright
  2. Pingback: Data cleaning is a “contact” sport – The Data are Alright
  3. Pingback: a seat at the table – The Data are Alright

  4. Sadly, thats just the tip of the iceberg of the Excel “Format as Table” function. AFAIU, its not possible to assign a name to the table or use filters. Please, do correct me if I am wrong

    Like

    Reply

  5. Hey, this is a great post, thanks for sharing it!

    Heads up I used to work for MSFT and there is a free, coauthorable/sharable version of Excel Web Access and folks in school can usually get free or nearly free desktop version of excel which also supports co-authoring. Let me know if you want help getting more info and I can put you in touch with the Excel team.

    Liked by 1 person

    Reply
  6. Pingback: my people – The Data are Alright

  7. Is it possible to create a table that automatically formats and carries forward calculations in new cells? This is very easy in Excel, but I can’t seem to make it happen in sheets. That’s the whole point of a true table, correct?

    Like

    Reply
  10. Pingback: borders / no borders – The Data are Alright

  11. Is it possible to have automatically filled formulas? in excel you can add a formula to a cell and a new line in the table would replicate the formula/s. I don’t see this happening in “alternate colors” version

    Like

    Reply

  12. The thing about Excel Tables is that it allows you to reference table columns instead of fixed ranges, which adds flexibility to your formulas. I’ve just finished working on a simple budget tracker in excel. It relied heavily on column references and named ranges for data validation. I thought I’d upload it to Sheets and see how that works. Unfortunately, the column references were replaced with fixed ranges, and the named ranges I used to make my data validation lists dynamic got dropped too 😦

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s