If you're using Google Sheets, but you find yourself needing to do the same
thing over and over. For example if you get a spreadsheet that looks like
this. Say you get it every day and you need to take it you need to format it to make
it look nice and then you need to prepare it for, let's say, your boss. You
may be able to record a macro which is a series of steps that can be automated.
We're going to walk through making this look nice and then we're going to walk
through making it work well. Let's say, when you get this, we're gonna go through
this series of steps that you would typically do to make it look nice, but
we're going to go to Tools, and we're going to go to Macros, and then we're
going to do Record a macro. Now you'll notice in the bottom you're
getting a flashing circle and it's saying a recording macro. That's going to
tell you every step that it's recording. It hasn't started yet. It's not recording
the movements of your mouse, but it's going to record it when you start changing
things. The second thing to keep in mind is that it's asking, hey, do I need
to start exactly where you're starting or can this be done wherever you happen
to be when you start the macro the next time. For these, it actually could be
either because they're going to apply to the entire spreadsheet, so we're just
gonna leave it on absolute reference. Let's say the first thing that we do is
we select the entire sheet. We're going to Text wrapping and turn it off
because we don't want the lines to be higher than one line each. That
already makes it look better. The second thing we're going to do, some of these now
are too big for one cell and they're getting overlapped by the next column.
Let's select everything. I'm just doing that by left clicking on the
rectangle in the very upper left hand corner of the grid. Take the mouse
and just double click on one of the column separators.
Okay, there we go. That resizes every column that you have
selected. Now let's make it look like a table. Go to Format, Alternating
colors, and let's pick the red because it looks nice.
We do have a header but we don't have a footer. That's all you want to
do to make it look good. Let's click Save and we're going to do it Looks good.
You can also assign shortcut keys. If you hold down ctrl-alt-shift+1,
that'll make it look good. Every time you get this spreadsheet the next couple
days, all you have to do is ctrl-alt-shift+1 to make it look good. Now
we're gonna make it work well. Go back to Record macros and, if you look, the one
that you just recorded is here. If you record a bunch of them, they'll be below.
You can only do 10. If you need to delete some, go to Manage macros. For now
we're going to do Record macro and leave it on absolute reference again. We're going to
insert a column. Watch down here. It'll record another step. I'm going to
right-click on C to insert one to the left. It recorded action 1 insert columns.
Put in a header that says Tick Mark. We're going to center that and make the font
white. The last thing that we're going to do is freeze the columns and the
rows and I'll show you what that will do. Let's do freeze one row, now freeze one
column, and what that did was this entire header isn't going to move when we scroll
down and this column of data isn't going to move when we scroll to the right.
We're going to go down to the lower right hand corner now and I'm going to
scroll to the right and you see column A isn't moving. I scrolled down, row one
isn't moving. We're going to call this Works well and macro number two and save
it. That's it. What we're gonna do now is we're going to look at a
spreadsheet where this hasn't been done. I put another worksheet in the
bottom here. We're going to be applying the macros. What we're doing is we're
acting like you just got this spreadsheet. Nothing's been done and
we'll see how now you can do it in two steps instead of lots of more clicks.
You could also use the shortcuts that we did, but those don't really show so well
when you're doing a video. We're gonna go up to tools, we're going to
macro, and we're going to do Looks good. Okay, it's all done. That probably took about
60 seconds. I they edited that part out, This is about a 10,000 line spreadsheet so
hopefully yours isn't that big. But, now that it looks good. This time, let's do
the shortcut key. I'm going to hold down ctrl-alt-shift and then
hit the 2 and it's running the script without even touching the mouse. Froze my
columns, froze my rows, we are all good. There's a few other things to keep in
mind. These macros stay with this worksheet. So, if you're getting this as a
new file every day, and you want to run those macros, just go down to the bottom,
add a new worksheet, put that data in a new worksheet, and then the macros are
gonna be in this file. You can run them on it. Hopefully that was helpful. If
you liked that video, and you want to see others like it, please subscribe in the
lower right-hand corner. Thanks!