Excel Hacks & Tricks Part 2

Quick reference

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