I recently had the chance to talk with my longtime friend, Ben L. Collins, who is THE Google Sheets instructor.
Ben joined as a special guest at Office Hours (special live sessions for full course students where we share ideas and give feedback on each other’s projects). Ben shared about his consulting and training work with Sheets, and his personal take on Sheets – favorite features, surprising features, and so on. The personal insights and reflections you can’t learn just by googling something.
Watch Our Conversation
Ben’s Career Trajectory
Ben is originally from England and came to the US about 8 years ago. He started his own business 5 years ago teaching online courses using Google software. Prior to that he was an accountant which is how he got his start with spreadsheets.
He and Ann met on Twitter about a year after Ben moved here. They met in person for lunch in Washington, D.C. when they were both living there and have been friends since.
He initially wanted to be a web developer and spent a lot of time transforming his Excel knowledge into coding knowledge. At the end of his first year working for himself, he didn’t have the coding job he was hoping for. But he did have a blog which led to freelance client work including teaching classes.
Ben’s Intentional Shift to Google Sheets
Ben shared that when he first started his business, he was given the advice to start a blog and begin writing about a variety of topics which for him included Excel, Sequel, how to do unit testing, etc.
“I had a dashboard post in Excel that showed the political orientation of the US over 100 years. I thought it was a great project, but nobody ever looked at it,” he said.
“Then I did a fairly simple dashboard in Google Sheets that got pretty popular on Google search. That post actually led to quite a few client requests to build dashboards for them in Google Sheets. What I then realized was that Excel has this industry of professionals who create courses and tutorials, teach and do consulting work. There’s a pretty big industry on the back of Excel to help businesses be better at Excel and provide solutions.” Ben decided to create something similar for the Google Sheets community.
What’s your favorite thing about Sheets?
I also asked Ben what his favorite thing was about Sheets, but he didn’t have just one answer but several. Ben’s favorite things about Sheets are:
- First off that it’s so easy and accessible (TIP: just type sheet.new into your browser to start a new Google Sheet). It’s quick to get started and to use.
- The other is that you can take a Google Form link it to a Google Sheet, collect the data and do your analysis. Then you create a chart and embed that into a slide. What I love is that the pipeline is all linked so if someone fills out the form again, that data flows all the way through to the slide and it updates. It’s a great way to collect data.
- I remember when I was an accountant and as deadlines loomed and changes came through quicker and quicker and quicker. You’d have to take your slide, delete your chart, copy and paste your new chart in, send it to your for review and then wait for the new round of changes. And you’d repeat this up until the deadline when you were forced to stop.
- There’s a function called the Query function that you do not have in Excel. It’s very powerful for data analysis and uses a data-based language to analyze your data. I think of it as a pivot table in a function. It’s challenging to learn at first but is definitely one of my favorite things about Sheets.
- The last thing is that because it’s a web based, cloud-based product it really integrates well with other software online. Also, the sharing and collaboration where you can see all the changes right there and you can see the version history if you do need to go back in time for a mistake.
Ben’s Biggest Surprise about Using Google Sheets
Ben’s biggest surprise about switching from Excel to Google Sheets is how similar they were. He said he had this misconception that it was a toy spreadsheet program at best.
He was also surprised that when you need to vlookup between two Google Sheets, you can’t click across like you do in Excel.
When you have to desktop Excel files and you click one into the other, your computer can find the file path between the two and do the connection for you. In Google Sheets that’s two cloud files, they don’t know the connection path so you have to use a special formula to bring data from one Google Sheet file into another.
Differences Between Sheets and Excel
Ben walked us through three big differences where Excel has an advantage over Sheets:
Data Size
Excel has a million rows and tens of thousands of columns where you can have pretty big datasets. Sheets is 5 million cells total for your workbook. They keep increasing that limit, ten million is rumored at some point. Sheets is catching up there but it still lags behind Excel. TIP: If you’re bumping up against having slow Excel or Sheets files, that’s probably a good indication that your dataset is too big for the spreadsheet format and you should move to a database. Spreadsheets aren’t meant to be stores for gigantic dataset. They’re meant for working with smaller datasets and giving you flexibility to do your analysis.
Data Analysis Tools
Excel has an impressive data analysis tool feature called Analysis Toolpak. Google Sheets has data analysis add-on’s but they’re not as comprehensive as their Excel counterparts yet.
Charts
The third is charts. You can pretty much customize anything in an Excel chart and Sheets doesn’t have that level of granular control. But Google Sheets is always improving and is catching up.
You can do pretty incredible visualizations with Excel if you’re willing to put the time effort in.
Ben’s Favorite New Google Sheets Feature
Ben loves the new feature called Connected Sheets which allows you to work from datasets that have billions of rows of data and still write formulas in Google Sheets. Your information sits in a database and you use Sheets to interact with that dataset.
He said that the charts and pivot tables have dramatically improved and thinks we’ll continue to see them improve and improve.
“I think another thing that we’ll see is the integration of AI,” he shared. “It already has an explore feature that allows you to take the hands of the wheel per se and see what it gives you. If you’re learning how to use pivot tables or aren’t sure what your data will show, it’s helpful.”
Hidden Gems of Google Sheets
Ben said that people would be surprised at what you can do and how proficient Sheets is as a product. He sees a resistance from people who aren’t sure they’ll be able to do the analysis they need to do. Sometimes that is true, but for the vast majority of spreadsheet work, Sheets works as well.
One feature he loves is the query function. He said it’s hard for him to use Excel and not have it available. He said, “Anytime you’re doing data manipulation, it replaces about 10 of the functions which is pretty fantastic.”
Q&A from audience members
Would you have some data viz examples you’ve created in Sheets to show us? Or dashboard examples?
Ben shared an example of a Google Sheet he uses to track his business metrics. It connects with a script he’s written that takes that data and turns them into a dashboard that’s automatically sent to him each morning.
Would be curious about the security/privacy of content in Google Sheets? Does Google read or capture content for its own purposes if content is stored in Google Cloud?
The data is obviously on Google servers. GSuite falls under the Google Cloud company and they do not read any of your data in Google Cloud. If you’re comfortable using Gmail, then I wouldn’t feel uncomfortable using Google Cloud services. The other thing that always gives me comfort is that Google is very unlikely to get hacked because their security is so good.
Have you used Sheets in other languages? How does Sheets fare in other countries, specifically Latin American ones?
I can’t speak to using Sheets in Latin America. It is there and available though. The syntax for formulas is a little different for certain locales and locations, so what you can do is change the location of your Google Sheets. In Europe, they use a comma for a decimal instead of a period like in the US. So, the formulas will be different.
What is the best way to learn about new features in Sheets?
Every Monday, I sent out a weekly tip for Google Sheets. It’s not brand-new stuff, but it’s to try and improve everyone’s abilities with Sheets. Occasionally there’s a formula challenge to try and answer. They’re almost like mini blog posts in an email.
Connect with Ben L. Collins
Website: https://www.benlcollins.com/
LinkedIn: @benlcollins
Twitter: @benlcollins
Email list: https://www.benlcollins.com/google-sheets-tips/
Courses: https://courses.benlcollins.com/
Blog: https://www.benlcollins.com/blog/
Exceltosheets.com – Specifically to help people as a guide to moving from Excel to sheets. It’s a good place to start.
Sheets Con – A two-day, online conference on Google Sheets in March or April each year.
Your Turn
Comment with your favorite thing you learned today. What tip or trick did you not know?
Leave a Reply