10 Features in Sheets Excel Needs
Excel is the behemoth of the spreadsheet kingdom. Across the length and breadth of every nation, it reigns supreme. As the market leader, the program has enjoyed a long stranglehold on its competition since dethroning the now-defunct Lotus 1–2–3 in 1995.
Despite its monopolistic dominance, others haven’t given up. Alternatives include Apple Numbers, LibreOffice Calc, Zoho Sheet and Quip Spreadsheets.
The one I wanted to focus on though is Google Sheets.
My first real experience of Sheets was at university in 2012, where I recall using it for a group assignment that was part of my web development degree. It helped us manage the project, delegate tasks and track progress.
Its collaboration features came in handy, as each of us routinely updated the workbook to cross off what we had completed and accompanied it with notes. We could also communicate with one another in real-time without resorting to another platform. These were simple needs, but it did the job.
I have rarely used it since, however. Whether at home or the workplace, Excel is always the first port of call.
Having heard some promising things about Sheets in recent months, it’s back on my radar. People talk about how it’s ‘closing the gap’, and what started as a glorified calculator has now become a genuine Excel challenger.
How much of this is true though?
I wanted to explore Sheets to see what it did well and how it might make its arch-nemesis envious. So I’ve compiled a list of 10 things it does better…
Sheets Workbook
Follow along by downloading the Sheets workbook here:
10. Images in Cells
Refers to Part 1 worksheet.
There are many instances where having images in cells is useful. Perhaps you have a table of employees and you want a photo of each person, or you have a stock list and want to easily identify each item. Sheets makes it very simple.
You can either:
- go to Insert > Image > Image in cell and select a photo locally or remotely
- use the IMAGE function, which allows you to specify a URL and determine the sizing
The latter option is preferred, as you have more control over the image. However, the only downside is local files are not permitted, so they need to be housed on the web.
Here’s an example of the IMAGE function in action:
=IMAGE("https://upload.wikimedia.org/wikipedia/en/thumb/b/be/Flag_of_England.svg/1920px-Flag_of_England.svg.png",1)
The England flag has been sourced from Wikipedia and the mode argument has been set to 1, which means the image maintains its aspect ratio. There are three other options: 2 stretches the image to fill the whole cell, 3 leaves the image at its original size, and 4 allows you to use custom dimensions. With 4, you must specify a height and width value.
You can use a standard INDEX and MATCH formula to lookup an image from a set based on a text value.
Here, the dropdown contains a list of Manchester United players, with Bruno Fernandes selected. The player and flag images update accordingly.
Player: =INDEX(I2:I27,MATCH(M6,D2:D27,0))Flag: =INDEX(J2:J27,MATCH(M6,D2:D27,0))
Although Excel doesn’t allow in-cell images, there is a workaround. You can use named ranges that reference images, and then a linked picture to dynamically display the appropriate one. The INDIRECT function is required to do this.
9. In-Cell Dropdown Lists
Refers to Part 1 worksheet.
Excel’s in-cell dropdowns have always been a pet peeve of mine, which is why I’ve often opted for dropdown controls instead.
One of the reasons is visibility. There’s no way of knowing if a cell contains a dropdown — unless there’s a label accompanying it or it’s the active cell.
The other reason is the lack of a pop-up suggestions list as you start typing. When you have many choices, there is a greater yearning for this.
In-cell dropdowns in Sheets are more polished, however.
You’ll know if a cell contains a dropdown because of the small triangle on the right.
There is also a predictive search feature. After testing it extensively, I realised it was only evaluating the initial character of the first and last name.
The anomaly was Victor Lindelof, who appeared in the search results when I typed ‘f’. In his case, the final letter of his name was picked up for some reason. This is inconsistent behaviour, but an interesting observation nevertheless.
Another advantage is the previous selection will be promoted to the top after clicking on the dropdown again.
8. SPLIT Function
Refers to Part 1 worksheet.
In Excel, you can use Text to Columns, Flash Fill or Power Query to separate the contents of cells. However, there is no easy way to do this using formulas.
Sheets makes it a lot easier because of its native SPLIT function. All you have to do is select a single cell (ranges won’t work) and choose a delimiter.
Say you wanted to split up a list of full names. Just use a formula like =SPLIT(D2," ") and the result will spill across. Be careful about certain names though, because it can go pear-shaped!
7. Checkboxes
Refers to Part 1 worksheet.
Whilst Excel has a checkbox control — found in the Developer tab — it requires a cell link to determine if its value is TRUE or FALSE. Another drawback is not being compatible with the web app.
Similarly to what I showed you with images, Sheets also has in-cell checkboxes. The TRUE or FALSE values are contained in the same cell and correspond to the checkbox status. This makes it easy to reference directly.
6. Automatic Pivot Tables
Refers to Part 2 worksheet.
Updating pivot tables in Excel is a manual process. Any time there’s a change in the source data, you are required to hit the Refresh button to retrieve the latest changes.
This is annoying and inconvenient. It’s the equivalent of setting workbook calculations to manual. It’s easy to forget about.
Thankfully Sheets has pivot tables that update automatically, so no additional user input is required.
5. Ranges
Refers to Part 3 worksheet.
I often see people using full column ranges like A:A in their formulas. I wouldn’t recommend them most of the time because you’re evaluating far more cells than you’re likely to be using. This can slow down the processing speed of calculations, but it’s also bad practice to include the header in a calculable range. Structured references and named ranges are better alternatives.
Regardless, say you did want to reference the whole of column A but exclude the header. In Excel, you’d have to use A2:AA1048576 or an unwieldy formula, but Sheets conveniently allows A2:A.
Excel also has the annoying habit of dumping zero values in referenced blank cells. This can be rectified with formulas, custom formatting or turning them off in the settings. Nevertheless, this is a bit of a faff, to say the least.
4. Non-Contiguous References
Refers to Part 4 and Part 1 worksheets.
Sheets provides a convenient way of referencing non-adjacent ranges, by wrapping them between a pair of curly braces.
=SUM({A2:A8,C2:C8,E2:E8})
Excel does allow you to do this using standard parentheses for many functions. The problem is some notable functions will not accept this method.
=SUM((A2:A8,C2:C8,E2:E8))Take FILTER, for example. In cell M10 of the Part 1 worksheet, a list of players along with their squad numbers is displayed, providing they have been selected for the starting line-up.
The formula =SORT(FILTER({A2:A27,D2:D27},K2:K27=TRUE)) works in Sheets, but the equivalent in Excel doesn’t.
Instead, =SORT(FILTER(FILTER(A2:D27,{1,0,0,1}),K2:K27=TRUE)) has to be used. This consists of a sorted FILTER function nested in another to allow for two include arguments. The first one determines which columns in the range should be displayed ({1,0,0,1}), and the second (K2:K27=TRUE) checks for TRUE values in the Starting Line-Up column.
You would usually define all conditions in the include argument of a single FILTER function. The problem, however, is you cannot combine an array constant with something else, like this: ({1,0,0,1})*(K2:K27=TRUE). Hence, the reason for the double FILTER.
3. Arrays
Refers to Part 5 worksheet.
Excel only allows you to use arrays for constants, which are static values that can be numeric or string-based. For example: {1,2,"three","four"}.
In Sheets, cell references and functions are permitted, so you can construct multiple formulas from one cell.
A list of numbers is present along with four calculations.
={SUM(A2:A8),AVERAGE(A2:A8),MAX(A2:A8),MIN(A2:A8)}
The Excel formula for this is:
=CHOOSE({1,2,3,4},SUM(A2:A8),AVERAGE(A2:A8),MAX(A2:A8),MIN(A2:A8))2. QUERY function
Refers to Part 6 and Part 2 worksheets.
The QUERY function allows you to query the data in your worksheet in a similar way to how you would with a database.
If you’re familiar with Structured Query Language (SQL), you’ll easily make the transition to Google’s Visualisation API Query Language.
You can use some of the standard clauses like SELECT, WHERE and ORDER BY to retrieve data comparable to a SORT and FILTER formula.
In this example, I want to list all players in the squad who are goalkeepers, return their squad number and name, and sort them ascendingly by their number.
=QUERY(A2:K27,
"SELECT A,D
WHERE B='Goalkeeper'
ORDER BY D"
)
One of the most impressive things you can do is to create a pivot table with a formula alone.
Say I wanted to replicate the regular pivot table containing a count of the player nationalities. I can do that using:
=QUERY(A1:K27,
"SELECT C, COUNT(C)
GROUP BY C
PIVOT D"
)
I know it’s not quite the same because you don’t get the presentation and additional features like the mini dropdowns. But in terms of the data that is outputted, it’s identical.
If I just wanted the total representation of each nation, I could use:
=QUERY(A1:K27,
"SELECT C, COUNT(C)
GROUP BY C
ORDER BY COUNT(C) DESC
LABEL COUNT(C) 'Players'"
)
1. Importing Data
Refers to Part 7 worksheet.
If you want to pull in data from a website into an Excel worksheet, typically you would copy and paste it or use the From Web feature in the Data tab.
Sheets allows you to do this with one formula, thanks to the IMPORTHTML and IMPORTXML functions. The best thing about these is the data will automatically update whenever there’s a change.
IMPORTHTML
IMPORTHTML is best used when you want a simple way of extracting a list or table from a webpage.
It has three arguments:
url: web address containing the list or tablequery: "list" or "table" (according to its HTML element)index: the nth list or table to be returned (in order of placement)
Let’s pull in the latest Premier League table found on Wikipedia.
=IMPORTHTML("https://en.wikipedia.org/wiki/2020%E2%80%9321_Premier_League","table",5)Voila!
IMPORTXML
If you want a more sophisticated method to extract something on a page, then opt for the IMPORTXML function.
The two arguments are:
url: web address to examinexpath_query: XPath code to select nodes
To construct an XPath query, you need to view a webpage’s HTML structure first. Do this by right-clicking on the desired object and pressing Inspect (or the equivalent).
This formula retrieves the same table as the IMPORTHTML method in a more precise way:
=IMPORTXML("https://en.wikipedia.org/wiki/2020%E2%80%9321_Premier_League","//table[@class='wikitable'][1]/tbody/tr")The XPath code is: //table[@class='wikitable'][1]/tbody/tr.
//table selects all table elements, whilst [@class='wikitable'] narrows it down by only selecting the ones with a class name of ‘wikitable’. As there is more than one wikitable class, [1] ensures only the first is considered.
At this point, the correct table has been accessed so the final part selects tbody, which houses the 21 tr elements that make up the table.
I’ll say it again…voila!
Pretty amazing, eh?
It’s worth noting that whenever you’re scraping data from websites, you are not in control of how they are structured. You must adapt to what is present.
I’ve experienced problems where I’ve scraped data perfectly one day, only for the webpage to change the next, which messes everything up.
With IMPORTHTML, if you select the fifth table of a page, you run the risk of that becoming the sixth another day. Thanks to the flexibility of XPath queries though, IMPORTXML is more robust to changes.
Final Thoughts
Excel has been around considerably longer than Sheets, so of course its head start has been advantageous. Rather impressively though, Sheets has bridged the gap in a short space of time to arguably become the second-best spreadsheet application. It helps to have a competitor set a benchmark to follow, though.
In many ways, Microsoft has allowed Google’s alternative to catch up by only releasing updates quadrennially through perpetual licenses. It was in 2017 when they decided to change tack; updates started to be released far more frequently. Only for those with Office 365 subscriptions, however.
Most of the functions in Excel are the same in Sheets. As are its features, but Excel still has a lot more to offer on balance. More scalability, structured tables, better charts, Power Query, Power Pivot and the Data Model to name a few.
Being a cloud platform is a performance disadvantage. Although Sheets does have an offline mode, there’s nothing to gain from it like there is with Excel’s desktop version.
Competition is good, and the better Sheets gets, the more pressure there’ll be on Excel to respond. We’ve already seen instances of this, like when Microsoft announced its ‘revolutionary’ dynamic array functionality. Despite Sheets already having it…
There are plenty of ways that both can learn from each other. However, playing around with Sheets has stimulated my desire to see some of these features in Excel at some point.
