Home Archives for Podcast Tips & Timesavers
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!”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
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 19, 2010 Leave a Comment

How to Share Excel Data with Word Documents

Microsoft Excel is tightly integrated with MS Word. There are several ways to share data that you create in Excel with a Word Document. In this lesson I demonstrate how to copy part of an Excel worksheet and then paste it into a Word document. Here are the techniques that I cover in this short Excel Video Lesson:

  • Paste as a Word Table
  • Paste as a Picture
  • Paste Special as a different type of Picture
  • Paste a Link between the Excel and Word documents – to update the latest sales figures

This is the first in a series of videos where I demonstrate how easy it is to share information between the different programs in the Microsoft Office Suite.

You can see a list of “The 50 Best Tips for Word 2007” by following this link.

You can view and download this video – for free – at the iTunes Store. I invite you to subscribe to my podcast, “Danny Rocks Tips and Timesavers.”

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
How to Share Excel Data with Word Documents [ 5:28 ] Play Now | Play in Popup | Download (711)
Link to this post!
Filed Under: Excel Training, Integrating MS Office Programs, Podcast Tips & Timesavers, Word Tips Tagged: , , , , , , , , , , ,
September 17, 2010 Leave a Comment

Two Ways to Create a Frequency Distribution in Excel

What is a “Frequency Distribution?” A good example is to see how frequently daily sales are “under $75.00” or how often they range “between $75.00 and $150.00.” Once you know how frequently a result occurs, you can better focus your attention on that particular segment of your business.

In this short 5 minute and 45 second Excel Training Video Lesson, I demonstrate two approaches to creating a Frequency Distribution Report:

  1. Use the =FREQUENCY() Function – this is an ARRAY Function.
  2. In a Pivot Table, Group the Row Labels to produce a Frequency Distribution.

An ARRAY Function has two “Got’cha steps:”

  1. Select all of the cells that will contain the results before your enter your Array Formula.
  2. Use the Ctrl + Shift + Enter keyboard combination to enter your Array Formula.

You can watch this video here on my website, you can download it via an RSS Feed or you can watch it as a Podcast at the iTunes Store.

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Two Ways to Create a Frequency Distribution Report in Excel [ 5:45 ] Play Now | Play in Popup | Download (818)
Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , ,
September 14, 2010 Leave a Comment

How to Use an Excel Data Table for “What-if” Analysis

Excel has many tools that give you answers to “What-if” questions. An Excel Data Table allows you to feed a series of “substitute values” into two arguments in a formula. For example, with a Data Table you can:

  • See a table of monthly payments on a loan by substituting both the “Interest Rate” and the “Amount Borrowed” simultaneously.
  • See a table of “Net Payments” by changing both the “Sales Price” and the “Quantity” simultaneously.
  • See the amount that you “tip” a service professional by changing both the “Tip Percentage” and the “Food Total” simultaneously.

As you will see in this Excel Video Lesson, the trick is to understand which series of values are the “Row Inputs” and which series of substitute values are the “Column Inputs.”

Trust me on this: It is easier to “see” how to set up a Data Table than it is to “write a description” of a Data Table. So, I invite you to “see for yourself” how easy it is to set up an Excel Data Table and get answers to two simultaneous “What-if” questions!

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

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
How to Set up Excel Data Tables for "What-if" Analysis [ 6:43 ] Play Now | Play in Popup | Download (617)
Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Podcast Tips & Timesavers, What-if Analysis Tagged: , , , , , , ,
September 13, 2010 Leave a Comment

When to Use Absolute, Relative & Mixed Cell References in Formulas

In my experience, I find that many Excel users get easily confused when it comes time to use an Absolute or a Mixed Cell Reference in a formula. Let me demonstrate how and when to use the proper cell references, so that you always get the correct result.

Here is what each type of cell reference looks like in a formula:

  • Relative Cell Reference – =A2*B2 when copied down one row becomes = A3*B3
  • Absolute Cell Reference – =A2*$B$2 when copied down one row becomes =A3*$B$2 (Notice the $B$2 Absolute cell reference in the formula)
  • Mixed Cell Reference – =$A2*B$3 when copied down one row becomes = $A3*B$3 and when copied one cell to the right becomes =$A*C$3 (Notice how one part of the cell reference is relative – it moves; and the other part of the reference is Absolute – it remains fixed in place)

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
How to Use Absolute, Relative & Mixed Cell References in Formulas [ 5:09 ] Play Now | Play in Popup | Download (788)
Link to this post!
Filed Under: Excel Basics, Excel Formulas, Excel Training, Podcast Tips & Timesavers 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!”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
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.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
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!”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
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: , , , , ,
September 3, 2010 1 Comment

Use Excel’s Text-to-Columns Tool to Quickly Correct Date Entries

The Text-to-Columns tool in Excel is one of my favorites. I use it to quickly “parse” text elements in a cell. For example, to separate “First Name” from Last Name in a cell that contains the “Full Name” entry.

In this short – 3 minute, 23 seconds – video, I demonstrate another way to use Text-to-Columns. I show you how to separate the “Year,” the “Month” and the “Day.” A client called me in a panic – they downloaded daily transactions from their mainframe computer into Excel. However, the “dates” appeared as – e.g. 20100901 – and they could not find a way to format the dates as – e.g. 9/1/2010

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Use Excel Text-to-Column Tool to Correct Date Entries [ 3:23 ] Play Now | Play in Popup | Download (829)
Link to this post!
Filed Under: Excel Functions, Excel Training, MS Excel Tips, Podcast Tips & Timesavers, Text Functions 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!”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
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: , , , , , ,
« Previous Page
Next Page »