Locked lesson.
About this lesson
Useful shortcuts and tips for working with Excel sheets
Quick reference
Excel Hacks & Tricks Part 2
Useful shortcuts and tips for working with Excel sheets
When to use
Quick ways of doing common tasks in Excel
Instructions
- Display a leading zero in a number: Type a single quote mark before the zero
- Dynamically calculate today’s date: =TODAY()
- Display a date X days from today: =TODAY()+X
- 00:04 Here's something you'll notice sooner or later, on our worksheet,
- 00:08 this customer's account number starts with a zero.
- 00:11 But Excel removed the leading zero by default because it recognizes that this is
- 00:16 a number and knows that a leading zero has no value.
- 00:20 But when you're leading zero is the beginning of an employee ID number,
- 00:24 an account number or a product code, maybe you'd like it to be displayed.
- 00:28 This problem can easily be solved by adding a single quote mark ahead of
- 00:33 the first zero.
- 00:34 Let's click on that cell, hit f2 to get inside and
- 00:38 go to the beginning of that number.
- 00:40 We type our single quote mark, and a zero and Enter.
- 00:45 Now our zero shows up, but
- 00:47 notice the green triangle in the upper left corner of the cell.
- 00:52 When we click on that cell and hover over the error message, it says
- 00:57 the number in the cell is formatted as text or preceded by an apostrophe.
- 01:02 Of course, if you know that the data in this cell, column or row will always have
- 01:07 a leading zero, it makes sense to change the format to text all together.
- 01:11 But for now, we can click on the error drop down and
- 01:15 select ignore error, and we're happy.
- 01:18 Finally, here's a super useful function, the today function.
- 01:24 This function returns the current date formatted based on
- 01:27 the location settings of your computer.
- 01:30 Let's enter this function as the date of our quotation,
- 01:34 we simply type equals, and today.
- 01:37 Notice that while we're typing functions, Excel is helping us along by
- 01:42 suggesting the function or functions it thinks you're typing in.
- 01:47 If we press on the tab key, it'll complete the name of
- 01:51 the first function on the list and the open parenthesis.
- 01:56 We just need to finish up by entering the arguments and the closed parenthesis.
- 02:01 In the case of the today function, no additional arguments are necessary.
- 02:05 So, we type our close parenthesis, hit Enter, and today's data is displayed.
- 02:11 The today function is dynamic, so each time we open this document,
- 02:15 the date of the quotation will be the date we open the document.
- 02:19 Now we want our quotations to be valid for 14 days, so in this cell,
- 02:24 we can type a formula.
- 02:26 We start with equals, click on
- 02:30 cell D3 plus 14, and Enter.
- 02:35 We also want our dates to be non-ambiguous, so let's select both cells.
- 02:41 We click on D3, Ctrl D8, let's choose a date
- 02:46 format that's universally understood.
- 02:51 We press Ctrl+1 to get our number formats window and
- 02:56 we select the Date category.
- 02:59 Let's scroll down to find a date format that we like.
- 03:03 Let's choose this one, we hit OK, And
- 03:08 now we have our quotation looking just the way we want.
- 03:12 So can you believe it?
- 03:14 You've gone from Excel zero to Excel hero.
- 03:17 You can now create formulas, apply special number formats,
- 03:21 add and remove worksheets, apply conditional formatting, sort and
- 03:26 filter data and visually tell stories from your data using charts.
- 03:32 If you haven't already done so,
- 03:34 you want to challenge yourself by using our downloadable practice exercises and
- 03:39 probably even experimenting with a few scenarios of your own.
- 03:43 Visit our course catalog to check out more cool ways that Excel
- 03:48 can help you to work smarter.
Lesson notes are only available for subscribers.