Hi! Welcome to The Data Are Alright, a blog about tips and tricks for changemakers. According to my stats, most of you found your way here through Google. I hope you enjoy the post below. If you’d like to stick around, here are a couple of great options to read. You can even SUBSCRIBE, which would absolutely make my day! (I post approx 3x / month)
- How social justice makes me a better database admin
- Why changemakers need good data
- 4 part series on Mail Merge
- 3 part series on digital self-care
- Clipboards and Megaphones: a new perspective on Dr. King’s legacy
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…
- In Tables (as opposed to regular old spreadsheets), rows alternate colors, which makes them prettier AND easier for your eyes to track
- Tables offset your Column Titles in a darker color
- Tables make it super easy to sort and filter your data
- 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.
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!
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
You can actually change table name in the design tab.
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.
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?
Is there any way to make formula etc follow when a new row is created?
Yes, you can use the Arrayformula… this formula will start calculating the values vertically below in the same column automatically without entering any formula.
Unfortunately, there’s a lot more to Excel tables than just pretty formatting. For instance, what about formulas within tables that use structured reference syntax?
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
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 🙁
As many have commented, without the auto fill of formulas and named ranges, Google is way behind on tables.
I agree for auto-fill. However, what’s the problem with Google sheets’ named ranges or column references ? I use it all the time. As in Excel, you can give a name to a full column and use it in formulas, it will ‘intersect’ with the current row.
Joe78man
That’s my question. I need to format something as a table so that formula cells pull down automatically when entry cells are populated for a new row. That’s the main reason I use tables in Excel. I have a template in google sheets with some helper columns out to the right. The problem is that the users copy down the helper columns incorrectly. They would like these auto populated and locked down. Something I would do using tables in Excel but not seeing how this can be done in google sheet.
JB
Hi, I’d be happy to take a look at this! Can you send me a request through my Dear Spreadsheet Whisperer advice column?
You are so awesome! I too am an excel snob buy hated Google sheets would not table. Now I know your “secret”, I can start to love my Google sheets again!
Is there another way to do this? ctrl+T creates a new tab in Edge or Chrome. Something that Google should have thought of when creating the feature.
Thanks for the great post. While others may want more advanced features, I was currently looking to do what you detailed here.