Tag: Tutorial

0026 Excel Shortcuts: How to Copy and Paste Formatting

If you are still re-formatting your spreadsheets from scratch every time, you are throwing away hours of your life needlessly.

Instead, you can copy and paste formatting only, using the shortcut in this video.

Download FREE Hands-On Exercises

Full Video Transcript:

Copying and pasting formatting is one of the most time-saving shortcuts you will ever learn, because it lets you take your previous work and apply it anywhere you want in a snap.

You may not know this, but when you’ve been copying and pasting for all these years, you’re pasting everything that came with it. So, the formulas, the formatting, the other properties behind the scenes that you may not have even seen. The beauty of this shortcut is that you learn how to copy and paste the formatting only, and the key to this is with Paste Special.

So, the only way this works is, first, you’re going to actually take the cell that has the formatting that you want, select it, copy it with CTRL + C on the PC and COMMAND + C on the Mac, and then choose the cell that has…essentially where you want to apply it to, and then you’re going to do a CTRL + ALT + V on the PC, right? You’re doing a regular paste with CTRL + V, but a Paste Special by adding the ALT key in there, which brings you to this Paste Special window.

You can move up and down with the arrows, or better yet, you can actually jump to the specific option you want by going to whatever’s underlined. So, ALT + T for formats, and notice that you can just hit ENTER and apply this formatting to this spot right here, hit ESC to get out of it.

So, the key is to copy first, then choose where you want to apply it to, CTRL + ALT + V for Paste Special, and ALT + T to jump to that spot on the window.

On the Mac, it’s very much the same thing, the shortcut is a tiny bit different. And so what you need to do here is copy with COMMAND + C, choose the cell you want and CTRL + COMMAND + V to get the Paste Special, and this time you’ll hit the fn + T for format.

Basically, you can’t use F because that’s for formulas. So, the T over here is underlined, on the PC at least, and it gives you an indication. So, here you’re just kind of flying blind, but it’s okay, just hit ENTER and it’ll apply it for you, and hit ESC to be out of it. And that’s basically how that works.

Let’s now take this in action and go to some exercise where you can actually drill this in and remember these shortcuts by practicing it over and over again, take the left side over here and make it look like the right.

And in this case, you’re going to take this format over here, you can copy a bunch at once like so and Paste Special the format only. And then you’ll have exactly the right look.

Notice there’s some border issues going on, so you can’t do it all at once, you may have to do it in sections. But I’ll let you figure that one out because that’s even more fun that way.

Don’t forget to visit 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 I believe it was Tony Robbins who said, “The path to enlightenment is through sharing the Excel love.” I’m a big fan.

0025 Excel Shortcuts: How to Add a New Line Inside a Cell

In Microsoft Word pressing ENTER will give you a new line. However, in Microsoft Excel, you need to use a specific shortcut, which is the topic of this video.

Download FREE Hands-On Exercises

Full Video Transcript:

Ever try to add a new line inside of a cell only to hit ENTER and have it move down one cell? Super annoying.

But in this video, I’ll teach to how to bypass that habit that Excel has and add that new line inside the cell.

Adding a new line inside of a cell is great when you want to cut off specific words inside of like a header and you want to force it to look a certain way. And every time you’ve tried to do it with hitting ENTER and it not working, it’s because you’re not using this shortcut properly.

So instead, you’re going to be writing out your cell. I’m going to actually double-click on the cell to edit it, or I can use this additional peripheral helpful shortcut of F2 to actually edit the cell contents and have the cursor go at the end.

And now, I’m going to hit ALT + ENTER to force that new line and say, “awesome” over here, and hit ENTER to save or complete that cell entry, and now, it’s going to force that new line because I did the ALT + ENTER, instead of just ENTER by itself.

If you’re on a Mac, on the other hand, you’ll be using OPT + RETURN to create that line break inside the cell. F2 is the same, or you can even use CTRL + U, not COMMAND, but CTRL + U to edit the cell contents, and RETURN to complete the cell entry.

Couple things to note, CTRL + OPT + RETURN might work instead of OPT + RETURN. And if F2 doesn’t work, you might want to try fn + F2. A whole bunch of little quirky nuances here, just read through this and try it out. You can also change the system preferences and make it more standard and more easy to use.

So that’s a little bit more quirky for the Mac. PC is pretty simple, just ALT + ENTER to create that new line inside of the cell.

A great way to practice this specific shortcut is to go to this exercise here and create that new line inside of these cells, and actually from there, ALT + ENTER and type the word “line,” hit ENTER, and do that for all of these, and make it look exactly the same from left to the right. Have it match and you’ll be great.

Don’t forget to visit 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 they say, “The devil’s in the details,” unless you share the Excel love, then you’re all set.

0024 Excel Shortcuts: How to Change Font, Font Size, and Font Color (PC Only)

When it comes to Font Type, Font Size, and Font Color, you only really notice it when it’s bad.

Fortunately, once you know these Excel Shortcuts your spreadsheets will always stay clean and consistent.

(Only valid for PC, sorry Mac users)

Download FREE Hands-On Exercises

Full Video Transcript:

I’ve gotten countless spreadsheets where the font type, the font size, the font color were inconsistent and all over the place, and I know it’s because the person who made it didn’t know how to fix it quickly. In this video, I’m going to teach you how.

When you’re talking font type, font size and font color, it’s one of those things that you only really notice it when it’s bad. So, the way to make it look good is to use these shortcuts to actually get the result that you want and make everything clean and consistent.

With a PC, the trick to all of this is to hit the ALT + H + F, so Home, Font. And then it’s either going to be F for Font, S for Font Size, or C for Font Color. All right. So, let me start that process again.

I’m going to choose, first, the cell or cells that I want. Let’s choose this one. ALT + H + F + F. Right now, it’s Calibri. That’s pretty much the default. Let’s go a little wild. Let’s go Arial, right? And now it changes the font type completely.

Or I can change the font size with ALT + H + F + S, and make this a size 14 and it’s even bigger. I can also do ALT + H + F + C and choose the color that I want this way, as well. So that is how to get that moving.

Unfortunately, there is no option to customize your keyboard shortcut on the Mac. And so, really, what you have to do is use the Home tab on the ribbon, and go ahead and change the font type here, the font size and the font color with the little A symbol, because that’s referring to a letter on the screen. Pretty groundbreaking, I know.

Some final Shir words of wisdom for you is to actually think about it this way. Right. If you have a bunch of cells that contain formulas that are actually giving you an answer, that are calculated, I recommend that you use black font. And if you’re using an input where the user has to put something in themselves, use a blue font.

I didn’t make this up. This is a convention that exists for years and years. I think it’s awesome and it makes it a lot clearer where you input the cells that are blue and you don’t touch the cells that are black.

Speaking of not touching cells, let’s touch all of these cells in the next series of exercises. You want to go through and actually match the format on the left with the format on the right.

I’ve even given you some hints on what font types to use. It’s amazing. Go through each of these and change the font type. For example, over here, ALT + H + F + F, and Arial it and enter. You don’t get that fancy drop-down if you do it that way, but it’s okay because you don’t need to see it. You’re just actually going to be doing it yourself. It’s great.

You’re going to go to Font Size, change the font size on these to match the size indicated at the bottom. Font Color, if you can even read this. I can’t. I might need new glasses. That’s fine, too. And finally, if you’re feeling really adventurous, play a game of Pac-Man, and then fill out the font challenge here to make it look exactly the correct way with the font, font size and font color all wrapped into one. And then celebrate with a little champagne.

Don’t forget to visit 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 share the Excel love. It’s the gift that keeps on giving.

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 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 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.