We're going to be doing something a little bit different today.
What we're going to do is look at editing an Excel spreadsheet, like the
one on the left that's running in Excel, in Google Sheets. Before we go into it too
much, I do want to touch on one thing. You see green rounded rectangle
here. That is telling me that that's a new ability for Google Sheets to take an
Excel file and leave it in the Excel file format, let you edit it, and then you
can download it in the end still in an Excel file format. If you go to
drive.google.com then one thing that you do need to do is look in your settings.
Go to the gear icon, left click on it - settings, and make sure that the Convert
uploaded files to Google Docs editor format is turned off. I turned it back on
when I was messing around, but this should be unchecked. Click done.
Otherwise, when you upload it, it will convert the file into the Google Sheets
native format which is fine if you don't plan on downloading it again.
If you don't plan on downloading it again, you should convert it because it'll make it
easier to work with. But, if you need it back down, so if you're just putting it
in Sheets to share it with other people and collaborate, so turn that off and
upload it. When when you open it in Google Sheets, you can arrive here.
What we're going to do is, we're going to step through the worksheets at the
bottom. So this is a template provided by Excel so I just grabbed it. I started
Excel and this is kind of like a beginners template to walk you through
things. So it has great examples of different things you can do in Excel.
We're going to do them in both places and see what happens. It just starts
out with adding. This is a good place to talk about if you just put a some
formula in here, it's going to work in the desktop version, it's going to work
in Google Sheets. The formulas shouldn't be a big deal.
This next spreadsheet is just to show you how autofill works. If you select
the 100, which is a formula, and you hold the left mouse key, drag it down, it's
going to autofill that formula into the subsequent cells. We can do that in Google
Sheets as well. So far, so good. Let's go to split. I've use Excel since the Ice
Age and I frankly didn't know it could do this, but I'll show you what it means.
If I type Nancy here, it knows that I'm typing that first name and then it's
smart enough to start to auto fill. So if I type Andy, it pre-populates it. You can
hit enter and go down and then it did the whole column. So I learned something
new today. If I go to the right, and I type Nancy, this is in Google Sheets, I
type Andy, nothing happens. So you'll lose some of that functionality. Some of the
built-in overlays in Excel that can make your life a little bit easier. Now
there's other ways to do this obviously, but the point is is that you are losing
some of the bells and whistles. Go to transpose. This is showing how you can
can select a row, sorry copy it, left click in the cell,
choose paste, and transpose. It'll take that data and it we'll change it from
horizontal to vertical. If I tried to do that in Google Sheets, select the data,
copy - paste special, paste transposed, now we can still do that. We're doing all
right. Let's go back to Excel. The sort and filter is going to work in a similar
fashion and actually Excel has recently added sorting and filtering with
formulas so we're pretty comparable there. Tables are operating differently.
When you use one in Excel, like go into this table and I left-click and I
say insert table, it asks if this range is the correct range. You can see it put
the dancing ants around the right data, so that is right, but the top one is a
header and that's checked. Excel has figured that out, so just press OK. So
Excel has made this a table, but when Excel makes something a table, it names
some of the data in a way that can save time and it automatically adds these
filtering capabilities. So this is what Excel considers to be a
table and you can work with it in different ways. You can specify entire
ranges if you want. If you come over to Google Sheets, and you select the data
with the tables, we'll select this right here, you don't have the option to format
it as a table. What you can do is format it with alternating colors, and that does
pick up the header, and we'll do a green. So you can see it looks kind of similar
and customize the colors if you want, but we'll just click done. It does make it
look like a table, so that in itself could be helpful, but it does not give it
the extra functionality that Excel has. It didn't automatically give it the
filters and you can't reference this and it's not automatically called Table1
like it is in Excel. We won't go into tables too much, but just know that the
functionality is much less in Google Sheets. Drop downs are going to work in a
similar way, we can skip over that. That's just data validation. In Excel, when you
do some analysis, let's left-click and drag down this column, you'll get this
extra box that comes up and when you left click on it you can do some
conditional formatting really quickly and it kind of guesses which ones that
you want. Let's just do a color scale. But in Sheets, you can also do this. I've
selected the same column, let's go to Format, Conditional formatting. It's
not quite as easy, you don't have the little shortcut that pops up. You do see
this here, but that's a picture that came over from the file, so that
doesn't have any functionality. I can click color scale, and let's just
get one that looks similar, let's do this. Click done. That's the same thing. That's
conditional formatting in a color scale and that's happening in Excel as well.
next table has some charts. Chart functionality is similar. It's not
exactly the same, so you'll probably have some compatibility issues, but if you
stick with the basics, you should be alright. But, one thing to keep in mind,
let me close this one now, is that goofy things will happen. When I look at
this file that got uploaded, the pictures that are talking about the axes
are floating over the chart. Now, the chart's still fine. I can just delete
those pictures. The data is still here. You still have the functionality of using
it. The fonts are different, the legend seems a little bit too small to
read, but they basically work. If we look at Pivot tables, both Excel and Google
Sheets have pretty good Pivot table functionality, but you'll see this file
that we have here is just a table, but below it is a Pivot table and you can
tell when I left click on it, the Analyze and the Design Pivot table options are
highlighted and ready to use. Left click on one of them, you'll see all of
the changes that you can make. But, if I come over to Google Sheets, come over to
the right, the Pivot table is gone. The data is still there, but the Pivot table
went away. You could rebuild it pretty easily. You could could go to Data, Pivot
table. We'll say, let's do it on this sheet, and the range is this data here
even though it's behind that. I think I got it. We're going to put it right below.
Let's make it kind of similar so the rows should be the product, the value
should be the amount. The colors are not exactly right, but that's okay, we'll live
with that. Let's close this, and we lost your Pivot table here from Excel but we
built a new one. When we download this in a minute, you'll see that remains
intact. Those are the major areas that are available sometimes in Excel
not always in Google Sheets. The last thing that we want to do is we're going
to download this back onto your desktop. I would think the only reason why you'd
want to upload it, keep it in Excel format, is if you're going to redownload
it, right? Which could be pretty common. Maybe you put it in Google Sheets so
that you can use the collaboration features. Maybe you want to share with
someone, when you're done you want to grab it back down. We're going to do that.
We're going to change the name a little bit.
I'm going to call it Welcome to Excel then to sheets and back again. I want to
save this. I'm going to minimize Google Sheets, and now let's
open this up as the only file to look at
and we'll take a quick buzz through it. So already you see some things got goofed
up a little bit and just that the height of the rows came back a little bit goofy.
The data that we filled using the autofill is still here. The split isn't
here because we didn't do it in Google Sheets, we just showed how Excel does it.
The transposition of the data stayed. Sort and filter we didn't do. If we
look at the table, and I left click in it, it is a table in Excel because when I
left click on Table tools come up. But, some of the same functionality isn't
there. The filtering buttons went away. If I sum one of the columns though, you
can see it is still a table. It called this entire column December, so this
table is dynamic. It's labeling that. Otherwise it would have just come
through as the range. If we take a look at the charts, we have to scroll down to find
them, but they're still there. They're dynamic. They're active. They might look
a little bit different, but the data is still intact. If we come to the Pivot
table, the one that went away when we uploaded it is still there, just as
regular data. We'll delete that poor thing. But, the Google Sheets pivot table came
through as a Pivot table in Excel. If we want, we can kind of move it back to
where we think it belongs. So check out this next video. It's going to show you
how to add a custom column of data inside of Pivot table. Thanks for
watching!