0023 Excel Shortcuts: How to Highlight Cells (Change Background Color) (PC Only)

Most people either highlight their spreadsheets way too much, or far too little.

But today, you’re going to learn the fastest and most effective PC Excel Shortcut to draw attention to specific areas of your spreadsheet.

(Valid on PC only. Sorry MAC users)

Download FREE Hands-On Exercises

Full Video Transcript:

This shortcut is the quickest, most effective way to draw someone’s attention to a cell or a range of cells. And now, you can finally do it with the keyboard if you’re on a PC. Sorry, Mac users.

Highlighting cells is important not just to make your spreadsheet look pretty, but to show your data in a clean, professional, and clear way that gets your message across.

I can’t even begin to tell you how many spreadsheets I’ve received that were complete eyesores. In fact, I can’t remember the last clean-looking spreadsheet I ever got.

So, if you think about it, if you’re highlighting everything, then all of a sudden, nothing stands out, so don’t be that person. Be very clean with your highlighting cells, and today, I’m going to show you not only how to do it, but how to do it efficiently and quickly.

On the PC, you’re going to have a whole bunch of ways to access the highlighting, all starting with the ALT + H, right? ALT is going to get us to the top here. ALT + H for Home, and then we’re going to go to H for Highlight Cells, and then we have a bunch of choices. So, we can actually go ahead and use the UP and DOWN arrows to choose the color we want and hit ENTER once we find that choice that we like.

It’s a little bit more manual, but there’s no other way to jump to the color you want specifically, unless of course, you’re doing something like ALT + H + H + N. Notice the N over here for No Fill, which actually removes it. In this case, a white background, which is not the same as everything else, so I don’t want that either.

The last choice is ALT + H + H + M for More colors, which brings us to this little popup where we have a whole bunch of choices and we can get very, very specific with the color scale, or we can choose from a list of standard colors, or actually get very exact, so that’s a nice way to do it.

Unfortunately, on the Mac, there are no keyboard shortcuts and you can’t even customize it, but what you can do is use the top Home ribbon and click on the Highlight Cells and do it the way that you’ve probably been doing it until now, because that’s the only option.

There’s not a whole lot to remember, because everything corresponds, so you’re going to highlight the cells, and then you’re going to go ahead and do No Fill, and M for More colors, so it’s all very straightforward.

There’s a bunch of other bonus shortcuts that all have to do with ALT + H + H + M, all to do with this screen here when you choose More colors. So, CTRL + PGDN will actually go to the next tab or CTRL + PGUP to go to the previous tab, things like that.

If you hit TAB, it’ll actually move to the next section. You can use the arrows and hit ENTER. It gets very fancy. You don’t have to worry too much about it, but it actually navigates within this window, and this is true, also, for many other popups that come up on your computer, so it’s kind of cool.

I’m going to hit ESC to get out of this, but what we cannot escape from is reinforcing these concepts, and we’re going to do that with exercises I’ve created for just this purpose. You’re welcome.

We’re going to go to this tab over here, and we’re going to see how we highlight the cells we want in the colors that we want. So, choose the cells here and go to your ALT + H + H and find that blue, whatever corresponding color we’re going for here. I believe it’s this one. I’m little colorblind, so I don’t know. I think that looks good, right?

The point is to make the left section over here look identical to the example on the right, so go ahead and follow those to the end, to fruition. You’ll notice here if you do No Fill, you might see some lines from borders. That’s because that’s the gridline that’s been there all along in the background, but in fact, when we’re using colors on these cells, it looks like we don’t have them anymore. So, if you see them again, that’s not a bad sign. That actually means that it’s working correctly. Don’t freak out.

The last one here is to choose exact colors using the More colors, and in fact, there’s a red, green, blue color scheme here for all of it. I’m sure you’ll do great. Have fun with that.

Don’t forget to visit test.excelshir.com where you can download these exercises, along with other free resources such as keyboard shortcut cheat sheets for both PC and Mac.

Thanks for watching, see you next time, and as a diehard Star Wars fan, I would be remiss if I didn’t leave you with this parting thought. May the force be with you as you’re sharing the Excel love.

0022 Excel Shortcuts: How to Copy and Paste Column Widths

After painstakingly changing your column widths, there’s no need to do that work over again. Just use the shortcut I’m about to teach you to copy and paste the column width in a snap!

Download FREE Hands-On Exercises

Full Video Transcript:

If you’re anything like me, you probably spent the better half of your adult life not knowing that you could copy and paste column widths in Excel.

Now that you know it’s possible, today I’m going to teach you exactly how to do it.

In the previous video, I showed you how to change column widths and row heights. You can actually go ahead and click on the link in the description in case you missed it.

Now that you’ve done that work already, I’m going to teach you how to copy and paste only the widths of the columns from one section to another, using a little something I like to call “Paste Special.” Since Microsoft calls it Paste Special too, that’s what we’ll all be calling it from here on out.

Paste Special’s one of those amazing things that, if you’re using it correctly, you’re saving yourself tons of time every single time that you’re using Excel, but if you don’t use it, you’re really missing out.

The key to it is to first choose and select the cell that has the quality that you want to it. For example, if I want to take this cell right here, and actually this entire column really, I’ll go ahead and expand the selection using CTRL + SPACE. I’ll do a little CTRL + C to copy it.

Now, I’m not going to paste the actual contents, but what I will do is paste the width of the column. I can go ahead and do CTRL + SPACE again to select this column here, or I can just choose a single cell and do CTRL + ALT + V. Now notice what happens. I threw an ALT in there. Instead of doing a regular paste with a CTRL + V, I’m using CTRL + ALT + V, which brings me to Paste Special.

And of course, the magic here completely is the column width. I can jump to this even quicker by using the ALT + W on the PC to get to that spot immediately. I’ll hit OK, and it’s now going to obviously give me an error message, because why not? In fact, that thing I said before, let’s make it width, and then it’s going to work. See, it’s always better to make it like to like. If you’re doing a whole column, then paste a whole column. Otherwise it’ll yell at you, basically.

That’s how you want to do it. I can hit ESC to get rid of these little borders here. It’s just showing it’s on the clipboard. The whole point is, you’re doing a Copy and a Paste Special with just the column width.

Let’s see this in the Mac. It’s very much the same thing. The whole point on the Mac is that instead of doing it with…let’s do, for example, over here. Same concept. I’ll do CTRL + SPACE to get the whole column, COMMAND + C to copy. I’ll do CTRL + SPACE to get this new column over here, and do CTRL + COMMAND + V to bring my Paste Special window up here.

The only difference is, I’m not going to do ALT, I’m going to do a fn + W. I hit the fn + W, it will get you to this spot right here. Then you do OK, and it will apply that width to there as well. Hit ESC to get rid of that.

If you want to lock in this shortcut, then let’s go ahead and go to this exercise, where you’re actually going to take the original column width over here, you’re going to copy it, and you’re going to paste it over to these.

It’s really, really simple, but the whole point is to get it to look like it does on the right. Use that shortcut and then do it as many times as you need to until you can do it with your eyes closed. Enjoy.

Don’t forget to visit test.excelshir.com where you can download these exercises, along with other free resources such as keyboard shortcut cheat sheets for both PC and Mac.

Thanks for watching. See you next time, and remember, every journey begins with a first step. May I suggest that first step be to share the Excel love. I read it in a fortune cookie once. It was delicious.

0021 Excel Shortcuts: How to Change Column Width and Row Height

You know those annoying # signs that pop up on your spreadsheet every once in a while? It’s because your columns aren’t wide enough!

In today’s video, I’m going to teach you how to change the column width and row height with laser-like precision and speed.

Download FREE Hands-On Exercises

Full Video Transcript:

If you’re still using the mouse to adjust the column widths manually, I guarantee you that you’re leaving minutes on the table every single day.

Minutes that you could be spending getting coffee, gossiping with coworkers, or watching videos of corny Excel instructors.

No matter what industry you’re in or what your job is, or pretty much what you’re doing with your life, you will always need to change column widths and row heights. It’s just a fact of life. It’s just how it is.

There’s two main approaches. You can do the quick and dirty approach, and the precise guess and check. Quick and dirty refers to AutoFit, and the guess and check refers to changing the exact column width or row height. So, let me show you both.

If you’re on a PC, you want to go ahead and use the ALT + H + O + W to set the column width. Now, notice something about all of these is that they’re based on the current selection. So, for example, if I take all of this here and I want to expand it to fit all of them, I’m not just going to go ahead and click this way because that takes me forever.

Instead, I’m going to select all of these and do ALT + H + O, right? H for Home, O for Format, and I want to do a W for Width. If I wanted to guess exactly how much, because I’ve been doing this a long time, I’m going to guess probably, like, 14, and it’s pretty close, right?

But if I want it to be exact, I would do ALT + H + O + I to AutoFit, which is a really big time saver, and it’s based on the selection, meaning if I do ALT + H + O + I here, it’s going to fit only to this level. If I want to do the entire column, ALT + H + O + I.

If I, you know, hit ALT first, it’ll work, and it’ll give us to all of that because I have a lot of text underneath, so be aware of what you’re selecting before you actually put that shortcut into effect.

If you want to do the row, in contrast, it’s going to be ALT + H + O + H for the Row Height. Usually the default is 15. Or you can actually go ahead and do ALT + H + O + A to AutoFit the row height.

So, how do we remember all this? There’s a couple really corny Santa Claus tricks here, so just bear with me.

W is for width, which is super easy. H is for height, but for the I and the A, just think of the I as, like, suck it in, Santa, and the fact that I is, like, the most narrow vertical line anyway, so it will, you know, suck it in, basically.

A is, like, think of an attic, and you got to watch your head in the attic because they have low ceilings. You want to fit that row so it’s nice and snug, and it’s really compact. So, again, you always start with the H + O. That’s why I thought of Santa Claus. HO HO HO HO, get it? Okay.

And then there’s the Mac equivalent, which is all based on the customized shortcuts. So, go ahead and go to the description of this video and click on the link to go step-by-step on how to create your own Mac keyboard shortcuts. otherwise these will not work at all. So, again, I chose similar keys so that it’s actually very much related to the same as the PC.

Wishing you had your own exercises to follow along with? Well, fortunately, they’re right here. Just go ahead and actually follow through with each of these to the point where the left side looks exactly like the right. And you’ll notice that the numbers themselves are different on the PC than on the Mac, but again, just use the ones that correspond to the system that you’re using.

So, If I have a PC here, I’m going to keep the default at 8.43, but this one I’m going to do ALT + H + O + W and get the 9 here and hit ENTER, and it’s going to adjust it for me that way.

So, I’m going to go through each one. You can select a single cell or the entire column if you’re doing the Adjust Width with ALT + H + O + W because it’ll affect the whole column anyway. So, that’s for the Set Column Width.

Similar thing with the row height, and then you’re going to do AutoFit. So, be aware of what you’re selecting first. That’s the trick to this whole thing to make it look exactly like this. You’ll get a feel for it and see how it works, but, basically, that’s it, and then when you’re done, you want to actually go ahead and make sure that everything wraps up nicely.

The trick to this whole thing is that when you’re using Wrap Text, things can get a little bit weird because it doesn’t know how you want to, basically, AutoFit them. So, what I mean by that is…I’m going to do a little AutoFit of the row. So, ALT + H + O + A is how I get this point, but I don’t know. If I do ALT + H + O + I it’s going to cut it off here, but if I’m over here, for example, and I do ALT + H + O + I, it’s different.

It doesn’t always know what to do, so this is more of the art of Excel than the science. So, kind of get it most of the way there first, but then get more precise by doing ALT + H + O + W and maybe doing it to a four. And then you can snug it in so it’s already cut off at the right word, that kind of thing. So, again, just play with it and get the feel for it, but basically, have fun and learn.

Don’t forget to visit test.excelshir.com where you can download these exercises, along with other free resources such as keyboard shortcut cheat sheets for both PC and Mac.

Thanks for watching, see you next time, and one last exercise. Think of the person you love the most. Call them up and share the Excel love. Sharing is caring.

0020 Excel Shortcuts: How to Add and Remove Borders

FACT: Most Excel files either have way too many borders, or not enough. The reason? Formatting borders with the mouse is inconvenient and time consuming.

The solution? Watch this video and learn 6 Excel shortcuts for the most common border formats.

Download FREE Hands-On Exercises

Full Video Transcript:

Can I ask you a personal question? How much time are you spending creating borders? If it’s any more than a few seconds, you’re taking way too long.

But don’t worry, in this video, I’ll show you how to create the exact borders you want, fast.

We all know borders are important because it separates information, like headers or total rows from the rest of the table.

But the real question is how do we create the exact borders we want and how do we do it efficiently? We can actually do it very, very fast and very efficiently with the following shortcut.

So for the PC, we want to do things like CTRL + SHIFT + & to create an outside border on the current selection. Let me show you how this works, right? If I’m over here and I do CTRL + SHIFT + &, it looks like nothing happened, but as soon as I move the selection away, I’ve left that outside border there in place. It’s there, it’s waiting, it’s awesome.

You can even select a few different cells and then do that shortcut CTRL + SHIFT + & and then you move aside and you get that same effect.

Let’s go to the next shortcut. Over here is the CTRL + SHIFT + _ (Underscore) to remove the outside border from the current selection. So, if I want to take this one only, I can do CTRL + SHIFT + _ (Underscore) and it’s going to remove it, or I can take an entire selection like this and overlap it that way, if I want to save some time to do it across the board.

The memory trick here is to think of it as an ampersand, you’re adding a lot of borders, hence the outside, everything up on a perimeter of the current selection.

The underscore is like a blank, so you want to fill in the blanks, right? It’s going to remove whatever border you had on the outside.

The next series of shortcuts are going to be a little bit more involved, in the sense that we have the ALT + H + B and then a letter. So, the way that works here is…let me show you each example. ALT + H + B Home Border and we get a ton of options, but we don’t want to use all these because they’re not as useful.

In fact, the P is really helpful because it’s going to be the top. Now, T was already taken, but P, you want to think of it as popping the top off the Pringles. It’s a lot of P’s in the sentence but it basically helps you remember to put it towards the top, right?

If you want to do the bottom, it’s ALT + H + B + O for the bottom. Think of O as like, the original flavor of Pringles, which is the bottom of the barrel, because some people hate it, apparently. And you can also think of it as like a Pringles can on a table and tracing a pencil around it and it draws a little O there. So ALT + H + B + O will get you to the bottom of whatever the current selection is.

In contrast, ALT + H + B + L is for left border, ALT + H + B + R is for right, those are really straightforward. So again, you can do all the directions you want based on the current selection if you just start with the ALT + H + B and then a letter that corresponds to the border that you want.

Things are a little bit different for the Mac. So, if we want to go back to the other sheet here, we’ll see that, in fact, the same things can be achieved, but it’s done a little bit differently.

So, with the Mac, you want to use the OPT + COMMAND + 0 (Zero) to add the outside border. If you look at it, it’s kind of like an outside perimeter. Sort of. But basically, helps you draw the outside border.

If you do the OPT + COMMAND + _ (Underscore), that’s the same to remove the outside border, and then this is one of those rare cases where Mac is better than a PC, in terms of the default shortcuts, because you just use the arrows. You do OPT + COMMAND + UP up to create a top border, DOWN for a bottom border etc. etc. I think that’s awesome and I love that about the Mac shortcuts there. So, if you have a Mac, enjoy it while you can.

Let’s see these bad boys in action, right? Go ahead and go to these different tabs and actually complete these exercises. So, you want to make the left section here match with the right section in the example, and use the borders shortcuts that we just learned.

So, select the cells you want first and then put into effect the actual shortcut CTRL + SHIFT + &, in this case, and you’ll just go through and fill in everything until it looks identical and until you pretty much have this down cold. So, go ahead and practice it for the outside border, removing borders, top, bottom, left, right, and if you’re feeling fancy, little challenge, anyone want to play pong, anyone? Yeah? Okay, great.

Don’t forget to visit test.excelshir.com where you can download these exercises, along with other free resources such as keyboard shortcut cheat sheets for both PC and Mac.

Thanks for watching and I’ll see you next time. And share that Excel love. Yeah, it’s contagious.

0019 Excel Shortcuts: How to Wrap Text (PC Only)

Have you ever had text get cut off because the cell wasn’t wide enough? Wrap Text is what’s been missing in your life, and your spreadsheet.

In this video, I will teach you how to maximize the space on your spreadsheet with one simple shortcut for PC (sorry Mac users).

Download FREE Hands-On Exercises

Full Video Transcript:

Have you ever widened a column so much just so that you could fit all the text, and have a whole bunch of white space left over? Stop immediately.

In this video, I’m going to teach you how to actually wrap text quickly and correctly.

This shortcut is really important because it lets you take full advantage of the space that you have on your spreadsheet, on your screen, and pretty much in your life, right?

I’ve had clients where they had columns that were way too wide and was just taking up all the space because they didn’t know how to wrap text correctly, and so we couldn’t even see all the relevant information on the screen at the same time. We had to scroll back and forth, it was a waste of time, it was frustrating. The whole point is to be concise, get everything nice and neat on your screen.

The way to do Wrap Text with a PC is to hit ALT + H + W. It’s going to give you the same spot here on the top ribbon, and the whole thing is, it’s a “sticky key.” When you hit ALT, it is a sticky key because you’re pressing and letting go and that means it’s still waiting for your next move.

You’re going to hit H for Home and W for Wrap Text. So again, you want to keep in mind what you’re selecting first before you do this, otherwise it’ll just wrap the wrong thing, essentially.

Sadly, for the Mac, there is no keyboard shortcut for this and you can’t customize it, but what you can do is go to the top ribbon on the Home tab and hit Wrap Text on the Mac and you’ll get the same result. Or you can get into the same Format Cells with command one and go to the Alignment tab and change the Wrap Text that way.

Let’s see this in action with an exercise that’ll help us remember and actually internalize this shortcut. So, go ahead and go to this Wrap Text tab and match the left side to the example on the right. So again, select first. Choose all the cells that you want to make have this change, and then do ALT + H + W and it’s going to wrap them so that you can see everything nicely versus having to do it the old-fashioned way and spend way too much time and space to do it that way.

Instead, you’re going to choose those cells and you’re going to ALT + H + W and make it look clean, nice and concise.

Don’t forget to visit test.excelshir.com where you can download these exercises, along with other free resources such as keyboard shortcut cheat sheets for both PC and Mac.

Thanks for watching and I’ll see you next time, and in the meantime, don’t forget to share the Excel love.

0018 Excel Shortcuts: How to Align Cells

Cell Alignment isn’t just for the obsessive excel user, since it can singlehandedly make or break the overall presentation of a report. I’ve sat in on year end evaluation meetings before, trust me.

In this video, I will show you how to align cells consistently every time with a few simple excel shortcuts.

Download FREE Hands-On Exercises

Full Video Transcript:

Nothing will drive a person crazier than when cells do not line up.

I kid you not. I had a client once whose entire purpose for hiring me was to line up the cells in a report.

And it might seem trivial, but in fact, this can make or break the professionalism of a report. And today I’m going to show you how to do it.

Whether you’re aware of it or not, you are already aligning cells left, right, center, all the time, but you’re probably using the mouse. And today I’m going to show you how to use the keyboard to cut that time down significantly.

It really comes down to a very straightforward series of steps. The whole beginning is to hit ALT + H for Home and A for Align, and then it’s going to be a letter that corresponds to the action itself, so L for Left, C for Center, R for Right. I mean it doesn’t get more straightforward than this. T for Top, M for Middle, B for Bottom. Just go straight with that, and you’ll be on a very good footing.

The whole point is to get to this top ribbon piece with the PC, and then you’re golden. There’s really not too much to remember. It’s all baked into it. If you’re on the Mac, on the other hand, the two that are built in are COMMAND + L for the Left and COMMAND + E for Center. And because C was already taken, you have to use E and think of it as Equidistant from both sides.

Unfortunately, on the Mac, you cannot customize the keyboard shortcuts for the rest of these, for the right, top, middle or bottom, so you’re going to have to use the ribbon on the Mac to actually do it the other way. But otherwise, you’re going to save a considerable amount of time by using these keyboard shortcuts to align your cells.

Let’s illustrate these shortcuts with a few specific exercises. So you’ll have the align left. Again, just make this left section here match the exact format on the right and use the keyboard shortcut to make it happen.

So once you’re here, ALT + H + A + L for the Align Left. And you’ll go through and you’ll actually practice it several times until you get it down cold. We have it for the left, for the center, for the right, top, middle and bottom. And of course, a nice little challenge to get it to match up exactly.

Don’t forget to visit test.excelshir.com where you can download these exercises, along with other free resources such as keyboard shortcut cheat sheets for both PC and Mac.

Thanks for watching. See you next time. And if there’s just one thing you do today, share the Excel love. It will make you feel all warm and fuzzy on the inside.

0017 Excel Shortcuts: How to Change Number Formats

I wouldn’t show this as a party trick at your next soirĂ©e, but I would use this when creating a quick back-of-the-napkin spreadsheet.

In this video, I will teach you how to quickly format your numbers 7 different ways without touching the mouse.

Download FREE Hands-On Exercises

Full Video Transcript:

Don’t you hate it when you’re typing out a list of numbers and some appear with a decimal, some with no decimal, some with a comma, some with a currency symbol, and all you wanted is to get them all to look the same?

This video is for you because I’m going to teach you exactly how to format your numbers quickly, efficiently, and correctly.

In the previous video, I showed you how to format your spreadsheet precisely and comprehensively. There’s actually a link in the description below, in case you missed it.

In contrast, this video shows you how to get the most common formats really quickly, for those back-of-the-napkin type calculations that you just need more quickly.

So here is a list of all of the quick formatting shortcuts. The trick to all of this is CTRL + SHIFT + Something, and that something is going to be on that entire, basically, half of that row on the keyboard on the top, starting with that Grave symbol, which you may not have used ever before, all the way through 1 through 6.

So if you do CTRL + SHIFT and one of those, you’ll get the format that corresponds to it. So CTRL + SHIFT + ` (Grave) will get you general. G, grave, general. Get it? It’s all related. Okay.

Exclamation point is because you’re going to do the number, right? Think of it as like the decimal point that appears when you do it. The @ is like, “What time is the party at?” So I want to see the time. That’s how that’s working.

The hashtag here, the little date thing, I always think of, you know, being stranded on a deserted island and counting down the days with little tick marks. So that makes me think of the day format.

Currency is straightforward because it’s a dollar sign. Percentage is a percentage. This one is a little caret, or caret, depending on how you want to pronounce it, and it’s going to be raising the scientific roof because basically it’s scientific notation. So the numbers are too big or too small, actually, and you need to show it in a scientific format.

That’s essentially it, and it’s the same exact shortcut for the Mac. It’s still CTRL + SHIFT, and then that other additional number or the Grave symbol on the left of the 1.

Pull up a chair because there’s a bunch of shortcuts to work through here, right? We’ve got the general. So you want to make the entire left section over here match the format on the right. And all you have to do is turn these cells into the correct format, CTRL + SHIFT + ` (Grave), and it will go ahead and match it to that spot.

Go through each of these using the shortcut that we talked about. There might be some subliminal messaging here, which is not so subliminal anymore because I talked about it, but you get the idea. Go through each of these, and then go through the challenge one where every single one of these should match in a very precise way. And really just practice as much as you need to until you get it memorized.

Don’t forget to visit test.excelshir.com where you can download these exercises, along with other free resources such as keyboard shortcut cheat sheets for both PC and Mac.

Thanks for watching. See you next time. And tonight, before putting your kids to sleep, instead of reading them a bedtime story, share the Excel love. Trust me. Knocks them right out.

0016 Excel Shortcuts: The Best Way to Format Cells

You may think you know how to format cells, but until you master this goldmine of a shortcut, you’ll forever be limited.

In this video I will teach you the best (and most comprehensive) way to format cells.

Download FREE Hands-On Exercises

Full Video Transcript:

Some people are extremely particular about the way their spreadsheets look. Clearly, I’m one of them, and in this video, I’m going to teach you how to be very precise and comprehensive with your formatting, which is great for presenting to your boss, investors, or that guy across the hall you’re trying to impress.

Sure, you can use the “Home” tab on the ribbon for many of the most common formatting options, but if you really want to start to get things to the next level and be really precise and comprehensive with all your formatting options, the one place to go is Format Cells, and the way to get there is with CTRL + 1 on the PC or COMMAND + 1 on the Mac.

This is one of those shortcuts that will open up an entire world of different options for you. So, we’re going to explore in-depth, but really, the only main shortcut is the Format Cells for CTRL + 1. The way to remember this is, the one formatting shortcut to control them all, all right? Think of The Lord of the Rings when you think about this.

So once you have this window here, CTRL + 1, there’s a lot of different tabs up top, and you want to be able to navigate quickly between the different sections. The way to navigate is to go CTRL + PGDN to move to the tab on the right, CTRL + PGUP to go to the tab on the left. If you hit TAB, you’re going to move to the next section or area on this popup. TAB again, TAB again. If you want to go in the reverse direction, you’ll do SHIFT + TAB. And again, this is true for any popup that comes up on your screen on your computer, which is really cool.

Another way to navigate is to go ahead and hit the Spacebar when you’re on a check mark. You can check it by hitting SPACE, and you can uncheck it by hitting SPACE again. Alternatively, you can also do things like hit ENTER to hit the OK button or basically make it save and take effect, or hit ESC to cancel out and essentially close the window without saving your changes.

You can also do things like use the ALT and then the letter to actually activate that specific area. So, ALT + W, in this case, will get me to Wrap Text. ALT + W again will undo it. If I want to move to this box here, ALT + H. You can do a lot of these types of things, just pay attention. This is for the PC. The ALT key is a nice accelerator option.

The memory tricks for this are to control your pages or they won’t pay their tab. Think of pages as little rambunctious, obnoxious kids. They’re running around in the restaurant and not paying their bill, so control your pages, or they won’t pay their tab. That’s how you remember that piece. If you want space for dessert, nope. Check, please. The space, dessert, you know, check, it’s all related. In my mind, it was. Hopefully, that helps you.

Fortunately, on the Mac, it is actually very much the same kind of thing with very little, minute differences. It’s the same concept. CTRL + FN + DOWN, which is the same as PGDN. So again, it’s pretty much the same thing as we’ve done on the PC.

There are three main tabs on this window that are important, so that we’re going to cover. There’s the “Number,” “Alignment,” and “Border,” and let’s go through each one.

On the “Number” tab, you have a lot of different types of data, different categories for what the data could actually be. So, you’ll start by selecting that on the side, and then you get a whole host of options. Again, explore each of these on your own time, and really get a flavor of it, but things like General, Number, Currency, Accounting, these are all very, very important, and if you get really fancy, you can do all sorts of custom stuff, but most people don’t ever have to do that. So, don’t sweat it.

Let’s go to the “Alignment” tab and see that there’s a lot you can do here. You can orient your text. You can do things like “Wrap Text” or “Shrink to Fit.” You can even do some merging across, we’ll talk about that a little bit later. And so, that’s a great way to take full advantage of how your text, or your numbers, or whatever your data is, is aligned exactly the way you want to.

Finally, there’s the “Border” tab here, which is kind of incredible because it lets you choose, first, your style, what kind of paintbrush are you going to use, essentially. Well, I want this solid line or this thicker line, this thicker brush, for example. You can then choose the color. Most people keep it Automatic, but you can actually choose a different color here, and then, you use this area here as a canvas, and you will paint the borders onto this.

You can use these things on the side to actually be very specific and targeted about it, or you can do it with the mouse on that section itself, which is another approach. You can do None to erase it all quickly, you can do Outline, whole lot of things you can do, again, explore this, get a feel for it. But first, choose your style if it’s not the one that you want, then your color, and then you can go ahead and apply those changes to this canvas here.

Very important note is to select the cells first before you do any of these things. So, for example, I’m going to hit Cancel, I want to choose these cells over here, and then I’m going to hit CTRL + 1 and I’m going to apply a change, for example, a border.

Let’s make it crazy and do a little sideways border, which looks terrible, and then it applies it all the way to it. But the point is, if you just start doing borders all over the place without selecting first, it won’t get you the result that you want.

You better believe it. I’ve got exercises to practice all of these shortcuts and really remember them cold, so let’s go into each one. You’re going to match the format, make this left section look exactly like the right.

Hints, probably do the thing that it talks about at the bottom, right? It’s pretty cool. Select the cells you want, like the Number here, hit CTRL + 1 and go to the “Number” tab, and let’s see. Let’s move this to the side. Well, in fact, I want the number to appear like so, with only one decimal point and a thousand separator, and with the negative to be with red and a parentheses. So, it’s very, very precise and very comprehensive.

So, go through all of those steps to do things like changing the number format, go through the Alignment tab, make it look exactly the same way, and also do things like the border. It gets very specific. One little note for the alignment, do not merge these cells. It says so right here in the hint. It’s much better to, instead, CTRL + 1, if you want to basically get a hint, Alignment, and Horizontal, it is “Center Across Selection.” What that does is it actually keeps your data only on the left side, but it does the nice thing of putting it as if it’s centered across all of them, versus merging, which can lead to a whole bunch of problems later on, which are just annoying and stupid. So, don’t do that.

Don’t forget to visit test.excelshir.com where you can download these exercises, along with other free resources such as keyboard shortcut cheat sheets for both PC and Mac.

Thanks for watching, and I’ll see you next time. And I think it was Gandhi who said it best when he said, “Don’t forget to share the Excel love.” Wise words.

0015 Excel Shortcuts: How to Insert and Delete Cells, Rows, and Columns

One major advantage that Excel has over paper & pen is that you can literally shift EVERYTHING over and make space for more information without disturbing your existing work. You can also clean things up by removing unused information (no more erasing feverishly!).

In this video, I will teach you how to insert and delete cells, rows, and columns quickly and correctly.

Download FREE Hands-On Exercises

Full Video Transcript:

Ever hand in a report to your boss, only to have her respond with, “Where’s the March data?”

Don’t worry, there’s a super quick fix to this and it involves inserting cells, rows or columns, which is the topic of this video.

This is one of the most powerful shortcuts in Excel because it lets you go back and add new information to an existing project, while keeping the cell references and everything else intact.

On the flip side, when you’re deleting, make sure to do so with caution because you can only undo up to a certain point. And once your information is lost, it’s pretty much gone forever and no amount of kicking or screaming is going to bring it back.

Fortunately, the actual shortcut here is very, very straightforward. To add or insert cells, you’re going to hit CTRL + + (plus). To delete, you’re going to do CTRL + - (minus). That’s really it, and this is one of those rare times where our Mac and PC shortcuts are identical.

The only nuance to this is you’re going to select first, before you actually use this shortcut. So, let me show you what I mean. If you want to take an entire row, you’re going to select the row and then insert with CTRL + + (plus) to get that row inserted. If you want to choose an entire column, you’ll do it this way and then do it afterwards.

If you want to do an entire cell, you’ll get this pop-up window saying, “Do you want to shift the cells right or down?” You’ll never really need to use these two because you can just select the entire row or column first. That saves you some trouble. But again, think about how you want to shift stuff over to the right or down. Sometimes it varies, based on what you’re trying to do.

Here are some exercises that I’ve created specifically to reinforce these shortcuts, and practice as many times as you want. So, to insert all the cells here, you’re going to end up getting the side on the left to match exactly with the side on the right. And the way to do this, in this example, is to insert the cell, so CTRL + + (plus).

Again, notice I’ve selected the cell first. Shift it to the right because I want this number to move over to the right, and hit “Okay.” And I do the same type of thing over, make sure I’m shifting around until it matches exactly.

You’re going to go through insert the cells, you’re going to do delete all the cells. You’re going to insert the rows, delete the rows, insert columns, delete columns. Lots to do, but it’s all under the same umbrella of inserting cells with CTRL + + (plus) and deleting cells with CTRL + - (minus).

Don’t forget to visit test.excelshir.com where you can download these exercises, along with other free resources such as keyboard shortcut cheat sheets for both PC and Mac.

Thanks for watching. See you next time. And in the words of William Shakespeare, “To err is human, but sharing the Excel love, now, that’s divine.” He’s so right.

0014 Excel Shortcuts: How to Clear Cell Contents

Have you ever needed to clear PART of a cell, but not all of it? If so, this video is for you!

It will save you minutes, hours, or days, depending on how complex your spreadsheet is :).

Download FREE Hands-On Exercises

Full Video Transcript:

I don’t know if you know this, but Excel can actually hold a lot of different types of information, sometimes all within a single cell.

And I’m not just talking about numbers or text. There are things like comments and hyperlinks and other data that’s actually there behind the scenes that may not be visible.

So, what happens when you want to clear away some of it, but not all of it? Well, today I’m going to teach you the shortcut on how to clear cell contents.

Instead of being sloppy about deleting information, there’s actually a way to be very precise and efficient about what specific information you want to get rid of, especially when your spreadsheets start getting more complex.

So, here are a number of different ways to clear contents. The first one is Delete. If you actually hit the DEL key, it will remove all of the contents without touching anything else. I’m going to bring that back with a little undo action.

The other ones are all based on the ALT key as the starting point. So, ALT + H for Home, E for the Clear, and then we have a whole bunch of options.

And the memory tricks for all of these is going to be, “Hey, everyone, clear all,” or, “Hey, everyone, clear formats,” “Hey, everyone, clear messages.” All right? The messages part is referring to the comments. Or, “Hey, everyone, remove hyperlinks.”

So it’s all related to that first letter, but you have to get to it with the ALT + H + E something. So, for a clear all, ALT + H + E + A and it will remove not just that content itself, but all the data along with it. Same idea for formats, for comments, for hyperlinks. We’re going to go through each one.

A couple things to note, the DEL key itself is what you want to use when you have multiple cells selected to clear the contents. Instead of the Backspace, which will only give you this annoying first cell removed, but then the blinking cursor, not what you want. So, people usually make that mistake. Don’t make that mistake, just hit the DEL key instead of the Backspace.

For the Mac, there is the other way of doing this, which is to use the fn + Delete on the keyboard. And for the rest of these, these are all customized, so go ahead and go to the description of this video, click on the link for how to customize your Mac keyboard shortcuts, and go through all those steps to do it in detail.

Something that I cannot stress enough is the importance of selecting the cells first, and then making the change, right? “Select, then effect.” So, don’t just start deleting things, select the cell or cells that you want, and then use the shortcut to have it take effect.

Here are a few exercises I created specifically designed to help you practice and reinforce all the different shortcuts. So, go ahead and make the left section here match exactly as it appears on the right using the keyboard shortcuts that we talked about.

So, in this one, we’re going to use the DEL key, we’re going to have…go ahead and select all of it and clear it out and continue to go through until it’s all matching the same way.

And notice that each one uses its own, go through each of these, and at the end, there is a challenge to use a combination of all the different ways to make it look exactly the same.

Don’t forget to visit test.excelshir.com where you can download these exercises, along with other free resources such as keyboard shortcut cheat sheets for both PC and Mac.

Thanks for watching, see you next time. And remember, dream big, live your truth, and share the Excel love. I do.