You all know I'm a big fan of Excel, but there are other programs
like Excel out there. One that I get questioned a lot about is Google Sheets. And often I get comments like this one, or this, and this. So, decided I need to dig deeper and find out more about Google Sheets. And for me the best way to do this was to switch my work from Excel
to Sheets for a few months. Now, of course there were
things I had to do in Excel, which I continued to do, but I did switch the majority of my work and this forced me to
really learn all I can about Sheets, to the point where I decided to create a head to head
contest between the two. In this video we're going
to cover the features where Sheets has an edge over Excel. (upbeat music) Keep in mind, this is just
an overview of features where I believe Sheets is better. And to be clear, I'm comparing Sheets to the current version of
Excel in Microsoft 365. Before we get started, I'd like to thank Express VPN who's the sponsor of this video. I have a special offer for
you in the description below, check it out when you're ready, I'm going to chat more
about them towards the end. Now let's get to the
superpowers of Google Sheets. Number one, searchable dropdown lists. In Sheets, drop down data validation lists are automatically searchable. Let's say here, I want to add
a drop down list for position, I have here a list of names and position that currently goes up to line 43, all I have to do here
is right mouse click, go to data validation, list from a range, select the range, that's the position. Now I'm also going to
add some additional rules to account for future data, and let's click on okay. Let's reject input in case
that input isn't from the list and click on save. Now, I get my dropdown list added here, and the great thing is, it's searchable. When I type assistant my list
is restricted to the positions that include the word assistant, if I type sales, I get the positions that
include the word sales. Now, in addition to this, we also get two side benefits. Remember, I added some
empty rows on the bottom, they're not included in the dropdown list, but the movement I get data there, so let's say I have a new position, that position is automatically included. Another sides benefit is that duplicates are automatically removed. Notice in my dataset I have duplicates for accountant and for
sales representative, but I get unique values in my dropdown. This makes creating, maintaining, and using dropdown lists a lot easier. Number two, the split function. With the split function you get to dynamically split your text into multiple columns. All you have to do type in split, open the bracket, reference the cell that you want to split and then define the delimiter, so in this case, my delimiter is a space, so quotation, space, quotation, close bracket, press Enter, and that's it, your results spill, you don't have to worry about
using left or right functions, you can just use the split function. Now, in addition to this, you can also define multiple delimiters, so if you wanted to split these
names into separate columns, notice that they are using
different delimiters, that's not a problem for split. Our texts is right here, our delimiters can all be put together inside the delimiter arguments. So, here I have a slash, I have a comma, and down here I'm using a dash, so let's just put them all in, close bracket, press Enter, and that works great here. Let's send this down and our results spell, and are split correctly. Number three, automatic
refresh in pivot tables. In Google Sheets your pivot tables automatically refresh the moment your source data changes. So check this out, here I have a pivot table showing the average salary employee count and unique positions based on this dataset. Now this part, unique positions, this is also something
that Excel doesn't have, they have the ability
to do account unique. Now, check this out, I'm going to add more data to this, and this is going to
automatically refresh. Now, just so that you can see it better, let's hide these rows here and let's add a new record on the bottom, but keep your eyes on the pivot table, I have a new department legal, it automatically shows
up in my pivot table, and check this out, I'm going to press Enter, and it's inside the pivot table. I remove it, it's gone from the pivot table. Number four, IMPORTXML to import any data from any webpage, even if it's not a table. So for example, on this page here we have the exchange rate table, there are actually two tables, one is up here, another one is down here, and we have a third data
set that's on the side. Now, officially only these two are tables, Excel power query will only see these two, it's not going to see
this data set on the side because this is not officially a table. With IMPORTXML we can
bring it into Google Sheets because this function enables
us to look into the HTML code and grab the note name
or the attribute name from the source code. This gives us the ability to pretty much bring any part of a webpage into Sheets. So, here to grab this part all I have to do is type in IMPORTXML, put in the URL and then note name and the attribute name from the source code, close bracket, press Enter, and I grab that information. Now in case you're wondering how we can bring the table information, well, for that, we can easily use the IMPORTHTML function instead, put in the URL, the query type, the table index, and we have our table imported. If we want the second table we just have to change this to a two and we get the second table imported Number five, lookup on images. In Sheet, you get the
ability to insert an image in a cell or over cells. If you for example,
have an image over cells that you want to put inside a cell you just have to click on the options here and put image in selected cell. The moment an image is in a cell it kind of acts like a value, you can reference it and you
can also use formulas on it. So for example, in this dashboard here, I want to take a look at a department and then I want to show the
picture of the department head. Here. I've done a VLOOKUP
on the picture names here. So, when I switch this
from finance to marketing the picture for the department
head updates as well. The great thing is that you can also get this
spill effect on pictures. So for this department
we just have one manager, but if I switch to, for example, the finance department, I have two managers, formula spill and so do images. Number six, search and replace with REGEX. REGEX stands for regular expression. With regular expression you get the ability to
search for a pattern and replace that with something else. So, for example here, if you wanted to remove the middle names all we have to do is highlight our area, press Ctrl + H to bring
a find and replace, now we are looking for a pattern, we're not looking for specific texts, so I'm just going to put in that pattern and replace it with this expression. This basically defines
what I want to keep, then I need to activate the search using regular expressions, and if you want to read more about them you can do that here. But check this out, before I replace this when I click on find, it's only finding the names
that have middle names, and when I click on replace all, that middle name is gone. That's how easy it is to
use search and replace with regular expressions. Now, in addition to this, you also have functions that use REGEX, for example, REGEXREPLACE,
REGEXEXTRACT and REGEXMATCH. Number seven, insert QR codes
with the image function. The image function allows
you to retrieve an image by typing a URL, so for example, if I put in this URL, close bracket, press Enter, I get the image returned, and this is kind of like a value, I could reference it like this. In addition to this, I get the ability to
use the image function to automatically insert QR codes. You just have to type in
the link for the Google API, and then there are some requirements, so you have to add in the size
of the QR code that you want, you also have the ability to
define the output and coding, so default is UTF-8. You can also specify the error correction, default is low. And then you can just reference the cell where you have the link and that's it, you get your QR code
automatically retrieved, and because this is a function you can send this down and it automatically
applies to the other links. Number eight, translate with a function. You get the ability to create spreadsheets in different languages
using Google Translate. Let's say, for example, I want to translate this
question into another language, all I have to do, type in Google Translate, select the text I want to translate, the source language, so this question is in English, so I'm going to put en and then to target language
I want to translate it to. So let's first go with de, closed bracket, press Enter, and I get the question in German. If I wanted it in another language, I just have to put the
language code in here, so let's say I want it in Japanese, ja, press Enter, and I get the question in Japanese. This gives you the
ability to create reports that support different languages. So let's say I had created this report and I want to send it to the companies that are in other locations, I can give the users the
ability to switch the language to another language, so let's go with French for example, or Russian or any language that's
supported by Google Translate. Number nine, chat inside your spreadsheet
as you collaborate. Sheets makes it really easy to collaborate with your colleagues. So let's say I wanted
to invite my colleague to collaborate with me on this Sheet, all I have to do is share this with them, put in their email address, notify them by sending
them a message if I want and click on send. When your colleague is in the spreadsheet you can see it up here and you get the ability to chat with them. This makes collaboration a lot easier. In addition to chat, you also get to add comments and @mention someone who you're collaborating with. Number 10, query function to
supercharge your data analysis. With the query function you get to create complex
reports with a single formula. So for example, here I have information on
three different divisions, I have information by region, app, actual sales and budget sales. Let's say in the first example, I want to get total actual sales, by division in the columns
and region in the rows, so, I need to pivot the division column, and I also need to
summarize the sales column. I can do all of this
with a single function. All I have to do is type
in the query function. This function uses query language which is very similar to SQL language, this gives us a lot of flexibility, I can select what I want, how I want it summarized, grouped by and in case I want to pivot information I just have to put it
in here, press Enter, and I get everything back including the headers. Let me show you some other examples, here I've made the division flexible, so I have the ability to select
between different divisions. You can even take this
function to another level by creating automatic totals that move with our dataset. I mean, this looks a bit crazy but it's not difficult to do once you get the hang of this function, when I change this from
game to productivity, notice my total shifts as well. It's like a pivot table, but all of this is just this formula, even the difference column is calculated inside this function. Now, in addition to these, there are some functionality
that are similar both on the Sheet side as well as Excel, but I find they're more
flexible and the Sheet side. So for example, Google Finance Function, this gives us the ability
to retrieve current, as well as historical stock information. In Excel, we have a new function that's not fully rolled out yet, it's called stock history, it's similar to this except Google Finance allows
us to retrieve more attributes about the stock. Another function that I find
to be more flexible in Sheets our sparklines, because sparklines are
written by typing a function, this means you can embed
other functions inside them. So, here I'm directly
retrieving Google Finance data inside the sparkline. When I switch my view from Amazon to Tesla this information updates automatically. So, that's my list of the
superpowers of Sheets, if you'd like to learn more
about any of these features, comment below and let me know. Many thanks to Express VPN
for sponsoring today's video and supporting my channel. A VPN is basically a private connection between your computer or mobile and a virtual server. This has several advantages. The most important one for me is security, I often work in his hotels,
coffee shops or at airports, and most of the time this means connecting to an
unencrypted WiFi network. A hacker connected to the
same unencrypted WiFi network can easily steal your
personal information. It literally just takes
basic computer knowledge to gain access to your
passwords, financial details, or even your emails. By using Express VPN I can
secure my internet connection with a click of a button using the highest standards of encryption currently available. This not only gives me peace of mind but also privacy. With Express VPN you can also watch geographically restricted contents by connecting to any of their servers in 94 different countries. So, this way you can still watch your favorite shows or channel even when you're traveling abroad. And because Express VPN
sponsored today's video, I have a special offer for you. Find out how you can get three months free by clicking the link in
the description box below that's expressvpn.com/xelplus. So, that's it for today. In the next video, we're going to cover Excel superpowers, so stay tuned for that. I hope you enjoyed today's video. Don't forget to give it a thumbs up and subscribe if you
haven't done so already so that I can see you in the next video. (upbeat music)
