Excel Hacks & Tricks

Quick reference

• 00:04 Since you stuck around this long, let's share some handy tips for
• 00:07 doing common tasks within Excel.
• 00:10 Let's say you want to apply a particular format to all your cells at once and
• 00:15 it would just take too long to click on the first cell and
• 00:18 drag all the way down to the last cell in your data set.
• 00:21 Well, here's a shortcut that selects every cell within your worksheet with one click.
• 00:27 Click on the corner button in the upper left corner of your worksheet, like this.
• 00:33 And every cell is selected.
• 00:36 Any action you do now is applied to all the data on your sheet.
• 00:40 Let's make all the text here red.
• 00:45 And it's done.
• 00:47 Of course, we don't like this red font.
• 00:49 So let's change it back.
• 00:51 We'll do Cntrl + Z to undo what we just did.
• 00:55 Okay, so maybe you'd already picked up on that one, how about this one?
• 01:00 To select an entire data set, go within the data set and do Cntrl + Asterisk.
• 01:09 This entire data set is selected.
• 01:13 Or let's say you're within the data set and
• 01:16 you'd like to select everything within that column all the way to the bottom.
• 01:20 Cntrl + Shift and the down arrow selects everything from your
• 01:25 current cell to the bottom of your data set.
• 01:30 What if you wanted to select everything to the right of your current cell?
• 01:34 Well, it's Ctrl + Shift and your right arrow key.
• 01:39 And if you wanted to select everything to the left, it's Cntrl + Shift and
• 01:44 the left arrow key, you get the picture.
• 01:48 Now you already know how to insert a single row or column, but
• 01:52 if you need to add several, just do it in one entry
• 01:56 by clicking on the equivalent number of columns or rows that you want to add.
• 02:01 Let's say you want to insert three rows above row 11.
• 02:05 Then you'd go to row 11, make sure you get your black arrow,
• 02:10 select three rows going downward, right-click, and insert.
• 02:16 You've got three rows inserted above row 11.
• 02:20 Let's Cntrl + Z to undo.
• 02:22 The process is the same for columns.
• 02:25 If you wanted to insert three columns to the left of column C,
• 02:30 you'd make sure you get your black arrow at column C,
• 02:34 select three columns going right, right-click and Insert.
• 02:39 Let's undo.
• 02:41 The cool thing is that this even works for non-adjacent rows and cells.
• 02:45 If you wanted to insert one row here, above row 8,
• 02:50 and one row above row 13, you would Cntrl to select row 13,
• 02:56 and one row above row 18, Cntrl + 18.
• 03:00 Now you have all three selected, right-click, Insert, and
• 03:05 you've got three rows inserted right where you want to.
• 03:09 This can save you lots of time.
• 03:12 Let's Cntrl + Z.
• 03:15 Here's a way to quickly jump to the end of your data set.
• 03:19 If you type Cntrl and
• 03:20 the down arrow, then you've jumped to the bottom of your data set.
• 03:25 If you click Cntrl and the up arrow, you're at the top.
• 03:29 And if you do Cntrl and the right arrow,
• 03:32 you're at the end of the data set to your right.
• 03:35 We also want to share this little known function to convert text
• 03:39 to the case you want, whether that's uppercase, lowercase or title case.
• 03:45 Maybe your data is coming from somewhere that it's in a case that you don't want.
• 03:49 Maybe like what we're looking at here.
• 03:51 Our item descriptions are all in uppercase,
• 03:53 but we'd much rather see them in title case.
• 03:57 Here's a really cool way to do this.
• 04:00 We go to an empty cell and we type this function =proper,
• 04:05 if we want it in title case, open parentheses,
• 04:09 the cell reference that we're trying to convert, close parentheses.
• 04:15 And now we have each word from the original cell converted to title case.
• 04:21 We'd like to do this for all the items within our price list, so
• 04:25 let's copy this formula by double clicking on the fill handle.
• 04:32 So this looks the way we want, but
• 04:34 we'd like to get this information over into column B.
• 04:38 Now we remember that we're looking at a formula here.
• 04:41 So if we copy this and put it here, well,
• 04:44 that's a problem because that's a circular reference.
• 04:47 What we can do is to copy and paste the value instead of the formula.
• 04:51 Here's how to do this.
• 04:52 We already have the cells highlighted, we can just hit Copy here.
• 04:57 And when we're about to paste, we right-click and
• 05:03 select Values from our Paste Options.
• 05:07 Click on Paste Values, and now our product descriptions
• 05:12 are converted from uppercase to title case.
• 05:16 That's pretty cool.
• 05:18 Let's delete.
• 05:20 If we wanted everything in lowercase, we would do =lower,
• 05:25 open parentheses, cell reference, and close parentheses.
• 05:31 Everything is in lowercase.
• 05:33 And if we wanted everything to be back in uppercase, =upper,
• 05:39 open parentheses, cell reference, close parentheses, and we're done.
• 05:46 This function can come in pretty handy if you do lots of importing of text.
• 05:52 So we like the way column B looks now.
• 05:55 Let's delete what's in column D.
• 05:57 If you liked those hacks, we have a few more for you in part two.

Lesson notes are only available for subscribers.