As Google has added more sophisticated features to Sheets, Microsoft has been beefing up Excel’s collaboration abilities. See which spreadsheet app wins in today’s multiplatform environment.
Microsoft Excel and Google Sheets are the two best-known spreadsheet applications available today. Both are polished and very useful — so much so that it’s easy to cling to the application you’re currently using without learning how the other has improved over the years. If you (or your business) chose one spreadsheet app and rejected the other years ago, there may be good reasons to reconsider.
To find out where Excel and Google Sheets stand today, both individually and compared to each other, I tested them by trying out the most common tasks users perform, including starting a new spreadsheet, inputting data and formulas, formatting cells, creating charts, adding extras such as links to external data sources, and collaborating with others.
To test all that, I decided to create a typical spreadsheet that many business professionals might need to assemble: a budget tracker. I built one that tracked eight months of monthly income and expenses for an imaginary company, including both results and projections.
Because it’s a multiplatform world, I worked on the spreadsheet using a Windows PC, a Mac, an iPad, an Android tablet and an iPhone. I used both the local clients and the online version of Microsoft Excel. Google Sheets is web-based but also has client versions for Android and iOS, so I tested those as well.
To keep things simple, the descriptions that follow are all based on the Excel 2016 Windows client (the current, stable version that is part of Microsoft Office 365). Some of the features may appear differently on a Mac. Naturally, the instructions for the browser-based Google Sheets are the same for both Windows PCs and Macs.
[ Related: Microsoft Word vs. Google Docs: Which works better for business? ]
And before I forget — there is one glaring difference between the two that should be mentioned: price. Although Google Sheets is part of Google’s licensed G Suite package for businesses, it remains free for individual use.
Microsoft Excel is available as part of Microsoft Office, which has a variety of different iterations for personal or business use, and is available as either an annual subscription or a one-time purchase. For example, Office 365 Home costs $100 per year and can be used on up to five PCs or Macs, five tablets, and five phones; it includes Word, Excel, PowerPoint, OneNote, Outlook, Publisher and Access, along with 1TB of OneDrive cloud storage. If you don’t like subscriptions, Office Professional 2016 offers the same applications for a single PC at a cost of $400. There are other packages available as well. And, of course, there are a number of plans for business that have per-seat annual licensing fees.
[ To comment on this story, visit Computerworld’s Facebook page. ]
TABLE OF CONTENTS
Creating a spreadsheet
Creating a spreadsheet
Perhaps what’s most important about a spreadsheet is how easy it is to create one, and then to input data and formulas. How the spreadsheet looks is important as well, especially if you use it to present data to others. So I began by looking for a usable, editable template I could turn to my purpose. Then I edited it, input the data, and added and tweaked formulas. If no suitable template was available, I started from scratch.
When it comes to budget templates, Excel has an embarrassment of riches, whether it’s a business budget or a special-purpose budget, such as for a marketing event. There’s a good chance that you’ll find one that fits what you’re looking for and that can be easily edited.
When you create a new spreadsheet, you are presented with a list of 30 templates, including several for a variety of budgets. And that’s just a small selection of what’s available — you can also search from within Excel through Microsoft’s collection of thousands of online templates. I searched using the term “budget” and found 93. When I clicked each template, I got more details, including its purpose, what it’s suited for and a snapshot of a sample spreadsheet.
When I found one perfect for my budget, I downloaded, saved and named it. And within a few minutes I had a well-designed spreadsheet, ready to go.
Microsoft Excel templates
Preston Gralla / IDG
Here’s an example of one of the dozens of budget spreadsheet templates available in Excel. It’s the one I used to create the test spreadsheet. (Click on any image in this story to enlarge it.)
The template included not just budget categories, such as operating expenses, personnel expenses and income, but also sample data, working formulas and very nicely formatted headings and text, ideal for presentations.
Not that I could just start entering data. I had to do some editing first, because I wanted to create a month-by-month running report about each month’s estimated versus actual spending, and this template had only one month in it. And, of course, my categories for income and spending were different from the template’s.
It was easy enough to do by renaming some categories and deleting others. Especially useful was the fact that the formulas were intelligent enough to take things into account when I deleted a category. For example, when I deleted a category called Asset Sales from the Income section, Excel automatically deleted the Asset Sales section of the formula that calculated total income. I didn’t have to make manual adjustments to the formula. I also added data and formulas for a new section called Net Income that would subtract total operating expenses and personnel expenses from income — one that was important to me but surprisingly not in the template.
Even though the template’s headings and text were beautifully formatted, I tried changing them, just to check out Excel’s formatting capabilities. Excel shines here: The Home tab in the Ribbon offers great tools for formatting text by changing fonts and their attributes, as well as for adding colors to cells.
I then copied the first monthly budget I had created by clicking its tab, selecting Move or Copy from the screen that appeared (making sure to check the box “Create a copy”) and then clicking OK. I did this several times to make several copies, then renamed each new tab so it represented a month: January, February, March, April, May, June and July. Then I went into each tab, and changed the data to fit the appropriate month.
Voila! With only a little effort, I had built a handsome-looking eight-month budget detailing expenses, income and net income, and comparing all of that to projections.
If you’re looking for a great selection of templates, Google Sheets isn’t the place to go. I found only 16 templates — period. Considering that Excel has 93 templates for budgets alone, Google Sheets’ pickings are pretty slim.
Google had only a single business budget available, named “Annual business budget” and created by Intuit QuickBooks. At first glance, it looked ideal for my task. It has separate tabs for income, expenses and a summary. For each tab, I would only need to make edits to the categories. However, as I examined it, I realized it had no way of tracking estimated versus actual spending. And customizing the template was extremely difficult: There were so many internal links to formulas and calculations that tracking them down and editing them seemed almost impossible. So I finally abandoned the template and instead started from scratch.
Google Sheets templates
Preston Gralla / IDG
The template selection in Google Sheets is underwhelming.
It was an easy but time-consuming process. Formulas are available via Insert > Function, or can be input by hand. For a full list of what’s available, there is a Google page titled Google spreadsheets function list. You can also get there within Sheets via Insert > Function > More.
Google Sheets doesn’t offer nearly as many tools for dressing up your spreadsheets as does Microsoft Excel. Excel has dozens of fonts available; Google has one. Excel has many pre-set ways of formatting cells with color, text headings and more; Google has only the most basic of tools. The end result: Google Sheets spreadsheets look basic and bare-bones.
Sheets does do a good job of importing spreadsheets from Excel. Simply upload the spreadsheet and open it in Sheets. In my admittedly straightforward spreadsheets, formulas, charts and formatting came through, including the Excel spreadsheet I created from a template for this story.
Creating a spreadsheet: Bottom line
Microsoft Excel is worlds ahead of Google Sheets when it comes to template selection and tools for cell and text formatting. If how a spreadsheet looks is important to you, the choice is clear: Choose Microsoft Excel. With Google, you’ve got only the basics. However, for inserting formulas, there’s no real difference between the two.
Since charts are one of the best ways to clarify the meaning of data, I created a variety of them for my test budget: Pie charts to show categories of income and spending, line charts to show income and other data over time, and bar charts to compare categories for a single month. I also explored each program to see what other kinds of data visualization tools they offered.
Creating charts in Excel is simplicity itself. The fastest way is to highlight the data you want to chart, and from the Insert tab choose “Recommended Charts.” Excel then shows you thumbnails of the types of charts that are most suitable for the data you’ve chosen. The thumbnails use the data you’ve highlighted, so you can see precisely how the chart will look. Scroll through the recommended charts, click the one you want to use, and it gets created. It’s that simple. I created half-a-dozen charts and found the recommendations were always on target.
Microsoft Excel – insert chart screen
Preston Gralla / IDG
Creating charts in Excel is exceptionally easy. As you look at each chart type, it previews the data you’re charting.
You don’t have to stick with the recommendations, though. If you haven’t found an appropriate chart type after you highlight the chart data and choose Insert > Recommended Charts, you can click on All Charts and scroll through Excel’s vast selection.
Excel has 17 different chart types, including more popular ones such as column, line, pie, bar and area; more complex ones such as radar, surface and histogram; and some that are known mainly to data professionals, like box & whisper. And many chart types have multiple subtypes — for example, among the bar charts you’ll find clustered bar, stacked bar, etc., and each of those has two variations.
The upshot? It’s highly unlikely that Excel won’t find the chart type you want to use. (For more information about some new chart types available in Excel 2016, see our “Excel 2016 cheat sheet.”)
As with the recommended charts, the thumbnails in All Charts use your data, so you get a preview of the chart you’re going to create. You can also simply highlight your data, select the Insert tab, click the down arrow next to the chart type you want to create (pie, line, bar and so on), then choose the precise chart you want from the charts that display.
Note that you don’t have to highlight data before creating a chart. From the Ribbon’s Insert menu, you can choose a chart type you want to create, and after the chart is inserted into the spreadsheet using default data, you can right-click the chart and choose Select Data. From there, you select the data you want to include from your spreadsheet.
Once you’ve created a chart, you get countless options for editing and customizing it, or for changing the chart type. Right-click the chart to select different data for the chart, format the chart and add borders or fills, etc. Click the chart and then go to the Design tab and you get far more capabilities, such as choosing variants of the basic design or changing colors.
Excel also creates what are called Sparkline charts, which insert very simple line, column or win/loss charts into a single spreadsheet cell. Sparklines are great for quickly showing trends graphically in a compact way — in my instance, showing data such as net income and total expenses over time. To create a Sparkline, you click in the cell where you want to insert it, choose the type of Sparkline you want to create from the Insert menu, then select the data you want to chart.
Microsoft Excel – Sparkline chart
Preston Gralla / IDG
Tiny Sparkline charts are good for showing trends in a compact space.
In short, Excel is a superb chart-creator, mixing ease of use with almost infinite customization and the intelligence to help you choose the right chart for best presenting your information.
One final note: Excel also has considerable capabilities not just for presenting data, but using visual presentation to analyze data using pivot tables, which help you find and display underlying relationships between data that you might otherwise not be able to find. But advanced capabilities like that are beyond the scope of this article.
Google Sheets doesn’t quite have Excel’s chart-making prowess, but it still does quite a good job. The easiest way to create a chart is to select your data, then select Insert > Chart from the menu at the top of the screen. The Chart Editor screen appears with three tabs across its top: Recommendations, Chart types, and Customization. I found that the best bet was to choose Recommendations, which shows the chart types that work best with the data selected. As with Excel, the thumbnails use the data you’ve highlighted, so you get a preview of how your chart will look when you choose any type. Click Insert when you’ve chosen the one you want. That’s all it takes.
I found, though, that the chart recommendations didn’t always work. For instance, I selected two columns, one for labels (months of the year) and another with the actual data (net income). When I did that, Google Sheets choked; it came up with no recommendations, even though the data was clearly well-suited for either a bar or line chart.
Google Sheets – insert chart
Preston Gralla / IDG
It’s simple to create charts with Google Sheets, but you don’t have as many ways to customize them as you do with Excel.
As with Excel, you don’t need to use the recommendations, and can instead select chart types from the Chart Editor screen. As for creating a chart without selecting data first, the first few times I tried it, Google Charts told me it couldn’t be done. However, I found that if I inserted my cursor in a cell that was contiguous to a row or column of data, Google Charts would automatically assume I wanted to create a chart using nearby data. If you have a relatively simple spreadsheet with not many separate data areas, this doesn’t appear to be a problem. But with complex spreadsheets, I found that Google Sheets sometimes was confused about what data I wanted to use.
Once you’ve inserted the chart, Google Sheets lets you change and customize it by either right-clicking or left-clicking inside to do things such as changing the legend, title, axes, chart type or color. But those basic edits don’t approach the wealth of Excel’s considerable layout and design options — for example, you can’t choose many different layouts and you don’t get as many formatting options or color choices.
Google Sheets does have a large selection of charts, including not only the common pie, line, bar, area and column charts, but more esoteric ones as well, like radar and histogram. It also offers a Sparklines capability (choose Insert > Chart > Chart Types, scroll down to the Other area, and select Sparkline), but it won’t insert a chart into a single cell. To test it, I selected a column of data that listed net income by month Excel. However, when I created it, instead of a Sparkline taking up one cell, it created a normal line chart.
Charts: Bottom line
Excel wins here. Microsoft’s tool makes it easier and less confusing to create charts, and you have far more options for changing the design, layout and colors.
Sometimes you want more than just basic charts and data. You might want to dress your spreadsheet up with pictures, links to external data, special charts and more. So after I created my report, I checked out Excel and Google Sheets for the extras they offer. Here’s what I found.
Excel has plenty of those extras for anyone looking to make their spreadsheet come alive, ranging from unique charts and graphs to photo tools, links to external data and more.
My budget report included a section about the sales staff’s monthly sales. I decided to try Excel’s “People Graph” (accessed via the Insert tab in the Ribbon), which lets you create charts using graphics and themes designed for data having to do with people. I used it to create a bar graph showing how much in sales each member of the staff booked for a single month. The results are eye-popping visuals that can add oomph to any spreadsheet.
Microsoft Excel – People Graph add-in
Preston Gralla / IDG
Excel’s People Graph add-in creates striking visuals.
To create a People Graph, you’ll have to agree to install an add-in. After a moment or two, it launches. Click the Data button on the upper-right of the chart, fill in the title, and click Select your data. Then click Create, and the app inserts a vivid, somewhat customizable chart — a Settings button lets you change the layout, graphics, colors and so on. Keep in mind, though, that the customizations are limited when compared to other Excel charts. Also, at first you may think you can’t move the graph after you create it. But if you fiddle around a bit, you’ll be able to do it — move your cursor to the edge until the cursor looks like a cross, and then move the graph.
Another add-in, Bing Maps, lets you layer data on top of a map. As with People Graph, it’s available directly on the Insert menu. I wanted to use it to show how much income was generated for my business in a specific month, by location. It took me several tries using it to get it right. It’s best to first select the data you want to chart, and then click the Bing Maps button. Even then, though, I had to do some extra work to get it right.
The add-in only works if the first column has the location data. So I had to redo the data on the spreadsheet. Once I did, it correctly mapped the data, but not in a particularly useful way. It showed all the locations of my business as circles on a map, but nothing more. To see the sales figures, I had to click each individual circle. It would have been much more useful to have bars on each location, representing the sales at that location.
Microsoft Excel – Bing Maps add-in
Preston Gralla / IDG
The Bing Maps add-in lets you layer data on top of a map, but the display isn’t terribly useful.
You move the Bing Maps chart the same way you move the People Graph.
There’s plenty other things Excel offers for dressing up reports, though. I wanted to add photos, so I chose Insert > Online Pictures, which brought up a screen for searching through Bing Images. I tried multiple searches to see what I could add. Whether it was oil wells, snakes, robots or anything else I tried, I found many usable images, all of which were tagged with the Creative Commons license for reuse. (You can also expand the search by looking for all images, not just those with a Creative Commons license.) You can also insert your own photos in the same way, by choosing Insert > Pictures and navigating to the image you want to use.
Whether you’re adding an online image or your own, inserting it in the spreadsheet is simple: Click it, then click Insert. You can also edit the image in multiple ways, including basic photo editing such as color correction and adding artistic effects, adding borders and more. To do that, click the photo, and a Picture Tools tab appears in the Ribbon with a variety of photo editing tools.
Next, I decided I wanted to insert stock quotes into the report that would update on a constant basis. That capability isn’t built directly into Excel, but is available for free with the Stock Connector add-in from the Microsoft Store. I tried it with a couple of stocks, and it updated every 15 minutes.
There are many other features you can add that are available from the Data tab, including live data from SQL databases, tables on the web, and other sources.
You can also use drawing tools, available from Insert > Smart Art.
In short, if you’re looking to dress up spreadsheets and reports, Excel is a vast, useful universe – although you might find some extras more useful than others.
Google Sheets has a superb extra that does a lot more than make a spreadsheet or report look prettier. Google Forms lets you easily create forms to get feedback on the data, the format or the presentation — in fact, on anything.
To use Google Forms, you select Insert > Form, and you’re sent to a new page that lets you create a questionnaire using several formats, including multiple choice, checkboxes, freeform text entry and more. You can add graphics and videos, add the date and time, require that questions be answered or not, etc.
Once the form is done, it doesn’t live in the spreadsheet itself. Instead, you send the form via email (you can either send a link or the form itself) to those you’re soliciting feedback from. They fill out the form, and the information is tabulated and the results charted on a page in your spreadsheet that only you have access to.
google sheets google forms survey feature
Preston Gralla / IDG
You can easily create questionnaires to get feedback using the Google Forms feature in Google Sheets.
In addition to Google Forms, Google Sheets has a handful of ways to dress up the spreadsheet. You can insert images from your computer, your Google Drive or the web. Its image search is superior to Excel’s, because you can filter the search by photos, clip art, line drawing or any type. The feature automatically looks for images are available for free for commercial reuse with modification. (Note that it’s always a good idea to check before using one for anything but personal use, no matter the labeling.) However, Sheets has no image-editing tools.
As with Microsoft, there’s a library of add-on tools (choose Add-ons > Get add-ons), but I found little there for dressing up spreadsheets.
Extras: Bottom line
If you’re looking to gather feedback about your spreadsheet, Google wins this round by a mile. The Google Forms feature is an exceptionally useful tool for quickly and easily gathering feedback. All the data collection is automatically done for you and tabulated on tabs on your worksheets.
Google’s image search is also better than Excel’s, although Excel has excellent image editing tools, something Google lacks. Excel also has a number of other features that can add value to a spreadsheet, although some — such as Bing Maps — still need work.