Home Archives for Tips and Time Savers
September 21, 2010 Leave a Comment

How to Use Excel Lookup Functions to Create a Customer Invoice

Excel has many powerful Lookup Functions. You can use them, for example,  to lookup an Item Number when you know the Product Description. Or to automatically look up the List Price for an Item Number on your customer invoice. In this lesson I show you how to use three Excel Lookup Functions:

  1. The INDEX() Function
  2. The MATCH() Function
  3. The VLOOKUP() Function

I hope that you enjoy learning – and applying – these techniques. I would like to hear from you, so you can either add a comment below or send me an email.

You can receive my new Tips and Timesavers Videos automatically by subscribing to my RSS Feed or to my free podcast on iTunes. Click here to view, download or subscribe to my podcast.

You can purchase any of my 5 DVDs in “The 50 Best Series …” for Excel 2007, PowerPoint 2007, Word 2007, Outlook 2007 and Access 2007 – Click here to open your shopping cart!

Learn how to “Master Excel in Minutes – Not Months!”

How to Use Excel Lookup Functions to Create a Customer Invoice [ 5:24 ] Play Now | Play in Popup | Download (789)
Link to this post!
Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers, Uncategorized Tagged: , , , , , , , ,
September 12, 2010 Leave a Comment

Prevent Duplicate Cell Entries with Excel Data Validation

Unfortunately, it is much too easy for a data entry clerk to assign duplicate account numbers. And this is a disaster waiting to happen. To prevent this from happening, use Excel’s Data Validation tool. In this short video lesson, I show you how to use the =COUNTIF() function in the Data Validation dialog box to ensure that only unique account numbers can be assigned to your customers.

I also show you how to extend the range of cells that contain this data validation rule: You use the Paste Special – Validation command.

This is one of “The 50 Best Tips for Excel 2007.” Click here to purchase the DVD using my secure shopping cart.

Learn how to “Master Excel in Minutes – Not Months!”

Use Data Validation to Prevent Duplicate Entries [ 5:38 ] Play Now | Play in Popup | Download (761)
Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
September 6, 2010 Leave a Comment

Watch My Podcast on iTunes

View in iTunes

Tips and Timesavers Podcast

I have just uploaded my 25th video podcast to the iTunes Store. The “Danny Rocks Tips and Timesavers” podcast can be viewed or downloaded for free. Click here to go to my podcast. Or, if you wish, you can copy and paste this link to do this later:

http://itunes.apple.com/podcast/danny-rocks-excel-tips/id374923275

I am pleased to report that I am presently getting over 100 downloads per day for my videos. Thank you!

On average, I upload two new videos per week. The majority of my videos demonstrate tips for getting the most out of MS Office programs (Excel, PowerPoint, etc.) I also include video tips for running productive meetings, setting goals, and other topics related to Business Communications.

Besides viewing my videos on iTunes, you can automatically receive my latest vides by subscribing to my RSS Feed. Simply click one of the RSS Feed buttons here on this post or at the top right corner of my web pages. RSS (Really Simple Syndication) is free and you do not need to provide any personal information except, of course, your e-mail address. If you have never used RSS, I encourage you to give it a try. Let me know what you think of it.

I always welcome your comments, thoughts and suggestions. And, if you enjoy my videos, please forward this post to a friend or colleague.

Sincerely,

Danny Rocks

P.S. You can Learn How to “Master Excel in Minutes – Not Months!” Click this link to get more  information.

Link to this post!
Filed Under: Business Communications, Communications, Excel Training, How to Lead Effective Meetings, MS PowerPoint 2007, Podcast Tips & Timesavers, Powerpoint Techniques, PowerPoint Video Training, Tips and Time Savers, Video Training Courses Tagged: , , ,
September 5, 2010 1 Comment

Quickly Create a Series of Dates in Excel

In this lesson, I demonstrate two approaches to creating a series of dates in Excel:

  1. Use Excel’s AutoFill Options to drag your mouse to fill in the series. Be sure to use the “AutoFill Options” button when you release your mouse.
  2. Use an Excel Formula to increment the series of dates (by day, week, month, year, or even weekday.) This approach gives you the most flexibility if you will use a different starting date on copies of your worksheet.

I devote several lessons to Excel date formulas and date formatting on my DVD, “The 50 Best Tips for Excel 2007.” Click here to add it to your shopping cart.

Lear how you can “Master Excel in Minutes – Not Months!”

Quickly Create a Series of Dates in Excel [ 5:41 ] Play Now | Play in Popup | Download (2096)
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training, MS Excel Tips, Podcast Tips & Timesavers, Video Training Courses Tagged: , , , , ,
August 28, 2010 Leave a Comment

Quickly Fill In all Blank Cells in Excel Report

Frequently clients will show me a report that they have downloaded from a Main Frame Computer or a Database Report. The report contains multiple blank cells. In order for my client to insert a Pivot Table or to perform Subtotals, they must fill in the blank cells by copying the value for the cell above. I have watched, in amazement, as clients copy and paste – or even type in – the missing values. There is an efficient way to perform this task. Here are the Excel Concepts that I cover in this short – 3 minute – Excel video training lesson:

  • Go To Special – Current Range
  • Go To Special – Blank Cells
  • Use Ctrl + Enter to register the formula into each cell in the selected range
  • Copy, Paste Special Values to convert formulas to values

This tip will save you lots and lots of time and prevent careless data entries. If you like this tip, I have 50 similar tips to offer you on my DVD, “The 50 Best Tips for Excel 2007.” Click here to add my DVD to your Shopping Cart.

Learn how you can “Master Excel in Minutes – Not Months!”

Quickly Fill in Values for Blank Cells in an Excel Report [ 3:05 ] Play Now | Play in Popup | Download (709)
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Formulas, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
August 26, 2010 1 Comment

Consolidate Data from Multiple Excel Workhseets Part 2

In Part One, I showed you how to Consolidate Data By Position –  where the range of cells was consistent in each of the Excel worksheets –  Click on this link to go to this lesson.

Consolidate Data By Category

In this lesson, I show you how to Consolidate Data By Category / By Label. You use this technique when the number of rows or columns containing values varies from worksheet to worksheet. For example, the number of sales representatives reporting may change each month. Or, some of the worksheets that you receive contain an extra column that you want to include in your consolidation. Consolidating Data by Category is very flexible as you will see in this lesson.

Learn how to “Master Excel in Minutes – Not Months!”

Consolidate Data from Multiple Excel Worksheets By Category [ 5:35 ] Play Now | Play in Popup | Download (1323)
Link to this post!
Filed Under: Consolidate Excel Data, Excel Training, Podcast Tips & Timesavers Tagged: , , , ,
July 28, 2010 3 Comments

Perform Break Even Analysis with Excel’s Goal Seek Tool

As a small business owner or investor, you ask many“what-if?” questions. In preparing to present your business plan to an investor you want to know your “break even” point. Fortunately, Excel has a great tool – Goal Seek – that can save you time by creating multiple scenarios to help you determine your goal – to break even!

You can watch this Excel Video Lesson here – online – or you can download it to watch later, at your convenience. I invite you to subscribe to my podcast, “Danny Rocks Tips and Timesavers” at the iTunes store by clicking this link – this is a free subscription.

Click here to see a listing of “The 50 Best Tips for Excel 2007.”

Learn how you can “Master Excel in Minutes – Not Months!”

Perform Break Even Analysis with Excel's Goal Seek Took [ 4:00 ] Play Now | Play in Popup | Download (1352)
Link to this post!
Filed Under: Excel Financial Tools, Excel Formulas, Excel Training, Goal Seek in Excel, Podcast Tips & Timesavers, Small Business, Tips and Time Savers, What-if Analysis Tagged: , , , , ,
July 17, 2010 3 Comments

Using Social Media to Engage Customers

Social Media can help you to grow your business. In order to use social media successfully, it is helpful that you follow a proven process. The AIDA process has successfully guided direct mail marketers for years. It is a perfect match for helping you to get customers to click deeper into your website: to turn their curiosity into an action; to make a sale; to make an appointment.

To see how to apply AIDA (Attention, Interest, Desire, Action) to your internet marketing plan, click to watch – or download –  this short 5 minute video.

You can read and download this article – Published on EZineArticles

Follow this link to subscribe to my podcast – “Danny Rocks Tips and Timesavers” – at the iTunes store.

Using Social Media to Engage Customers Play Now | Play in Popup | Download (547)
Link to this post!
Filed Under: Business Communications, Marketing, Podcast Tips & Timesavers, PowerPoint Tagged: , , , , , ,
July 9, 2010 Leave a Comment

Use Excel’s Goal Seek to Find Formula Error

A viewer wrote me seeking help with a formula. He was calculating a monthly payment for a loan using Excel’s PMT() Function and he was surprised at the result of the formula. I reviewed his formula and discovered that it was not a “formula problem.” Rather, it was a “results problem!” Watch this short video to see how I solved this problem for my viewer by using Excel’s “Goal Seek” tool. So now, instead of a monthly payment of $10,666.67, the result is $501.38 – a very different result!

You can download this video – for free – here on my website. Just click the button below the video image.

You can also subscribe to my Podcast, “Danny Rocks Tips and Timesavers” at the iTunes Store.

You can “Master Excel in Minutes – Not Months!”

Use Goal Seek to Find Formula Error Play Now | Play in Popup | Download (755)
Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Goal Seek in Excel, Podcast Tips & Timesavers, What-if Analysis Tagged: , , , , , , ,
July 8, 2010 1 Comment

Two Ways to Produce an Executive Summary of an Excel List

On one of the technology blogs that I follow, there is a debate over the best way to produce an Executive Summary of the information in an Excel Data List. Some people like the idea of using Excel’s =SUMIF() Function for this while others recommend creating a Pivot Table as the summary.

You decide – after watching this short video demonstration using both approaches. And, add a comment or drop me an email to let me know your thoughts.

This Excel Video Lesson is available for you to download as a Podcast on iTunes.

Follow this link to go to the “Danny Rocks Tips and Timesavers” podcast.

Learn how to “Master Excel in Minutes – Not Months!”

Summarize Excel Data with SUMIF Function or Create a Pivot Table [ 5:10 ] Play Now | Play in Popup | Download (1088)
Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Excel Training, Pivot Tables, Podcast Tips & Timesavers, Video Training Courses Tagged: , , , , , , , , , ,
« Previous Page
Next Page »