This post is last updated on August 9, 2017
When it comes to functionality in the Google Sheets vs. Excel battle, Google prevails. Sorry Microsoft Office fans, it may be time that you and your spreadsheet program have a little chat.
I want to tell you how to make your breakup with Excel a little smoother, and fall in love with Google Sheets. I’m not going to lie, you might need a pint of ice cream or a bottle of wine to get you started, but soon enough you’ll realize your old spreadsheet system wasn’t as cute as you remember.
Google Sheets provides a free, collaborative environment to manage most of your database needs, and with Google’s goal to eventually reach 85-90% functionality of Office products, the switch is a little easier to swallow (Business Insider). When I first started with Vendasta I had used Google Sheets a handful of times and stumbled over the switch. Google sheets vs Excel became a huge internal struggle for me, but I got through it. Let me share with you a few tips I learned the hard way, so your breakup can be a little smoother than mine.
Google Sheets vs. Excel: 3 Tips to Making Your Breakup with Excel a Little Easier
Tip 1: Google Sheets Add-ons
When Google Sheets don’t appear to have the same functionality as Excel, check the Add-ons section of the tool bar. If you’re experiencing a road bump, there is a good chance someone else has as well, and has already created a solution! There are a lot of really impressive things you can do with Add-ons, depending on what you’re using the program for, but here are a few basic ones to start with:
What is it: This tool creates separate columns for when you want to divide first name and last name. A few clicks and your whole 5,000 row sheet is magically cleaner!
Pros: Huge time saver, makes your sheets look much cleaner and even those scared of formulas can capitalize on its amazing powers.
Cons: Does not come close to the functionality of the SPLIT formula, so I would recommend using the formula instead of the add-on if you have a real mess of a column you need to split.
Sort by Color
What is it: One built-in feature Google Sheets is missing is the ability to sort the sheet by color. This particular tool allows you to sort by both cell and font color.
Pros: Visually digestible, easy to categorize and if you are an Excel fanatic, you definitely already rely on this tool—now you don’t have to go without.
Cons: None, this tool is great!
What is it: This Add-on allows you to either find duplicates in a set of data or compare two sets of data to highlight duplicates.
Pros: Timesaving hack, helps organize data and provides an easy solution for picking out duplicate values.
Cons: The tool is available in the Power Tool add-on (another great one to start with!) but only for “pro” users. But don’t fret, there’s another add-on for just removing duplicates that doesn’t discriminate against us non-pro users.
💡 Hint: Use this in conjunction with sort by color to maximize your add-on magic 😉
Tip 2: Grouping Data in Google Sheets Pivot Tables
Excel provides a grouping option that allows you to roll up pivot table data into summaries by month, year, quarter, whatever you need. Google Sheets does not have this functionality. Again, Excel appears to win in the Google Sheets vs Excel battle, but I don’t give up that easily.
All that is needed is an adjacent column (Column F below) with a date formula to extract the date range you would like to sort by. The formula used below =TEXT(E2,"yyyy-mm") allows us to roll up the data into month/year.
The next step is to use a google sheets pivot table to extract the data and group by the rolled up column (Month Created below).
The solution isn’t as quick, or as pretty, as the Excel alternative but it does the job. You can even hide the rolled up column in the raw data and pretend it doesn't exist!
Tip 3: Google Sheets vs Excel Charts
I used to spend many sleepless nights worrying about my lackluster charts made with Google Sheets, succumbing to dreams of drool-worthy 3-D pie charts from Excel. I still don’t find Google Sheets comparable in this area but I do have two pointers that will help you appreciate them a little more.
*UPDATE: In June 2017 charts in Google Sheets got much sexier. My dreams of 3-D pie charts have been answered, and the case for Google Sheets vs Excel got much easier for me. On top of that you also now have more editing power. Think advanced style editing (custom colors, THANK GOODNESS), and a much more user-friendly editor.
Add annotations to your charts
By adding a column with your annotations to the right of your x or y-axis data you’ll get different forms of annotations, see example graphs below.
Annotations added by including a notes column to the right of y-axis data.
Annotations added by including a notes column to the right of x-axis data.
Link charts to Google Docs
Here’s where our collaborative friend wins hands down, in the debate between google sheets vs excel. Google Sheets and Google Docs go hand in hand so when you copy and paste a chart from Sheets to Docs it updates when it’s changed, or not - it’s totally up to you!
So what's the verdict in the Google Sheets vs. Excel debate? While Google Sheets isn’t the knight in shining armour you always dreamed of, it is still a really nice alternative. It is an amazing tool for collaboration and has a lot of tricks I didn’t mention that helps increase its functionality. It took me a while to come around, but I’m starting to feel the love. Excel can be a bit daunting and unapproachable at times, but Google Sheets is a little more cuddly and friendly. This is the kind of spreadsheet program you’ll want to take home to meet mom!