Locked lesson.
About this lesson
Useful shortcuts and tips for working with Excel sheets
Quick reference
Excel Hacks & Tricks
Useful shortcuts and tips for working with Excel sheets
When to use
Quick ways of doing common tasks in Excel
Instructions
- Select entire worksheet: Click the square located in the upper left corner between Column A and Row 1
- Select an entire range of data: Ctrl + * (or Ctrl + Shift + 8)
- Select to the end of dataset in a particular direction: Ctrl + Shift + arrow key
- Insert X number of rows or columns all at once: Highlight X number of rows, right click, insert
- Jump to the end of a dataset in a particular direction: Ctrl + arrow key
- Transpose text from a cell to uppercase: =UPPER(cell reference)
- Transpose text from a cell to lowercase: =LOWER(cell reference)
- Transpose text from a cell to title case: =PROPER(cell 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.