Home Archives for Excel Video Training
November 29, 2010 1 Comment

My Most Viewed and Downloaded Videos for Excel and PowerPoint

My iTunes Podcast

Danny on iTunes

I began to post my videos as podcasts at the iTunes store this past summer. Since then, my video lessons for Excel and PowerPoint have been viewed and downloaded many times. I am thirlled. And, I thank you for your support and encouragement.

Here are the links – on my website – to the most popular video episodes. I have organized them by category in the order of their popularity. You can click on any link to view or download that video from my website. Or, you can go to my video Podcast by clicking on this link – “Danny Rocks Tips and Timesavers” Podcast on iTunes.

Merging and Consolidating Excel Worksheets

 PowerPoint Presentations

Data Visualizations

What-If Analysis in Excel

Importing Data From Other Programs

Pivot Tables

Excel Tables

Social Media

Formula Errors

Please add your comments below – or on my iTunes Channel. I welcome your feedback.

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

Sincerely,

Danny Rocks

Link to this post!
Filed Under: Consolidate Excel Data, Excel Data Tables and Lists, Excel Functions, Excel Training, Goal Seek in Excel, Import Data into Excel, Integrating MS Office Programs, Most Viewed Excel Videos, MS Excel Training, New in Excel 2007, Pivot Tables, Podcast Tips & Timesavers, Powerpoint Techniques, Presentations, Public Speaking, What-if Analysis Tagged: , , , , , , , , , , , ,
November 28, 2010 Leave a Comment

How to Annotate Excel Worksheets with Comments and Images

Do you use “post-it” sticky notes to remind you how to do something? Do you ever lose your reminder notes? I know that I do! So that is why I like to annotate my Excel Worksheets with Comments – this way I have the notes properly located (in the worksheet) when I need them (to remember how to write a particular formula, etc.) the most!

In this lesson, I also show you how to add “pictures” inside your comments. This is a great tip for adding in a picture for a catalog or order form.

A question that I am frequently asked: “How do I print out my comments?” Watch this video to find out how to do this – there are a couple of “got’cha” steps involved.

Logo for The 50 Best Tips

The 50 Best Tips

Here are three ways to enter a new comment that is attached to a single cell:

  • Use the Keyboard Shortcut Shift + F2
  • Right-Mouse Click and choose “Insert Comment”
  • On the Review Tab of the Ribbon (Excel 2007) select the “Insert New Comment” command.

You can edit your comments, re size the shape of your comments, hide your comments (only a “red triangle” shows in the cell until you hover near the cell), show your comments (individually or collectively), delete, clear and paste your comments. I cover each of these techniques in this video lesson. And more!

Watch this video in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

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

How to Annotate Excel Worksheets with Comments and Images [ 8:20 ] Play Now | Play in Popup | Download (627)
Link to this post!
Filed Under: Excel Training, Podcast Tips & Timesavers, Workbooks and Worksheets in Excel Tagged: , , , , , , , ,
November 27, 2010 2 Comments

How to Use the Indirect Function to Create Excel Dashboards

You can use the Indirect Function to create an Excel Dashboard. A Dashboard gives you an overview of your data – in this case, an interactive view of the information hidden in our data set. I will create Drop-Down lists using Data Validation in the Dashboard. The Indirect Function takes a little bit of time to understand. Here are some key points:

  • The Indirect Function works best with Named Cell References
  • The syntax is =INDIRECT(reference text). In our example the “reference text” will be the label for a Named Range of cells.
  • You can “nest” the Indirect Function inside another function – e.g. =SUM(INDIRECT())

Later in the lesson, I show you how you can quickly  look up any cell at the “intersection” of a Named Row and a Named Column – e.g. April East. You use the SPACE BAR as the “Intersection Operator” to point to the cell where “April” meets “East.”

50 Best Tips for Excel 2007

Excel Tips

Some of the Keyboard Shortcuts that I use in this lesson are:

  • Ctrl + A to select all of the cells in a Data Set
  • F3 to bring up a list of all the Named Ranges that have been created in the current workbook
  • Ctrl + Shift + F3 to create Named Ranges for the cells in the selection – using as Names, the Labels in the Top Row / Left Column

Here is a link to the 1st lesson in this series on the INDIRECT Function in Excel.

Follow this link to watch this lesson in High Definition, Full Screen Mode on YouTube.

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

How to Use the Indirect Function to Create Excel Dashboards [ 7:20 ] Play Now | Play in Popup | Download (1168)
Link to this post!
Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , ,
November 25, 2010 3 Comments

How to Trigger a Unique List of Values Based Upon Another Drop-Down List in Excel

You can use the INDIRECT() Function in Excel to create a drop-down list of unique values that is“triggered” by the label that you select in another drop-down list. In the scenario that I use in this lesson:

  1. Create your 1st Drop-down list using Data Validation in Excel – Allow “List.” Here the named range is “Departments.”
  2. Create a 2nd Drop-down list using Data Validation. This time the “list” uses the INDIRECT Function to reference the selected label from the 1st drop-down list.

Key Concept

 The labels in my 1st drop-down list serve as “named range” references. That is why the INDIRECT Function is able to pull up the correct list of “Job Titles” in the 2nd Drop-down list. For example, a reference to the text label “Sales” in the 1st drop-down list brings up a list of the values (Job Titles) from the “Named Range” (Sales) in the 2nd Drop-down list.

If you need to learn about – or review – Data Validation in Excel, follow this link to my free Excel video lesson on this topic.

Create Named Ranges in Excel

I have found that creating and using “Named Cell Range” references makes lookup functions and formulas so much easier in Excel. Watch this video to see how I use a variety of methods to Create Named Ranges in Excel. One tip is to use the Keyboard Shortcut “Ctrl + Shift + F3” – after you first select the Label and the Values for your Named Range.

In this lesson, I also demonstrate how to visually “flag” all of the cells that do not meet the criteria for your Data Validation. I find that most people are unaware that you can do this! I think that you will find many uses for this auditing tool as you work in Excel.

Additional Resources

Follow this link to watch this video on YouTube in High Definition, Full Screen Mode.

Here is the link to view the 2nd video in this series about the INDIRECT Function in Excel.

Learn how to “Master Excel in Minutes – Not Months!” – visit my new, secure shopping site – http://shop.thecompanyrocks.com

How to Trigger a Unique List of Values Based on Your 1st Drop-Down List [ 6:23 ] Play Now | Play in Popup | Download (1046)
Link to this post!
Filed Under: Data Validation, Excel Training, Lookup Functions in Excel, Names for Cells and Ranges, Podcast Tips & Timesavers Tagged: , , , , , , , , ,
November 23, 2010 4 Comments

How to Use the Undocumented DATEDIF Function in Excel

Want to get in on a secret? The DATEDIF() Function is a well-kept secret in Excel. You will not find it in the list of Excel Functions. And, you will not find any documented Help for it from Microsoft! Amazing, because it is a great function to use. Be sure to bookmark this video, because this may be the only help that you get for the DATEDIF Function!

DATEDIF() has three required arguments: Start_Date, End_Date, Date Code.

Six Date Codes

  1. “Y” – Gives you the difference in “years” between the starting and ending dates.
  2. “M” – Gives you the difference in “months” between the starting and ending dates.
  3. “D” – Gives you the difference in “days” between the starting and ending dates.
  4. “YM” – Ignores the years and days to give you the difference in “months” between the starting and ending Months.
  5. “YD” – Ignores the years to give you the difference in “days” between the starting and ending Days and Months.
  6. “MD” – Ignores both the years and the months to give you the difference in “days” between the “day” of the Start_date and the “day” of the End_date.

You can add “clarifications” to the results by using the “&” (Ampersand) and a “text label.”

I use DATEDIF frequently – for example to find out a person’s exact age or to document the number of years an employee has been worked for you.

Follow this link to visit my Archive of  Video Lessons for Time and Date Functions in Excel

Click here to watch this Excel Video Lesson in High Definition, Full Screen Mode on YouTube.

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

How to Use the Undocumented DATEDIF Function in Excel [ 7:43 ] Play Now | Play in Popup | Download (839)
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
November 22, 2010 1 Comment

How to Efficiently Select Data in MS Office Programs

Before you can copy data in Excel, you must first select the cells with the data you wish to “copy and paste.” Before you can apply formatting in MS Word, you must first select the word, the sentence, the pararagraph, or the document that you wish to format. So let me demonstrate my tips for efficiently selecting data in any MS Office Program.

Selecting Data in Excel

  • To select data that is contiguous (adjacent cells) in Excel, hold down the SHIFT key and use the directional arrow keys to “draw” your selection.
  • To select data that is non-contiguous (non-adjacent cells), make your 1st selection and then hold down the CTRL key as you make additional selections.
  • You can use CTRL + SHIFT + Directional Arrow to quickly select all of the cells that contain data in a contiguous direction.
  • Use the keyboard shortcut CTRL + A to select all of the cells with data in a Data Set.
  • Watch this video for additional Excel tips for selecting data.

Selecting Data in Word

  • Double-click a word to select it.
  • Hold down the CTRL Key as you click to select the entire sentence.
  • Triple-click to select the entire paragraph,
  • Use the keyboard combination CTRL+ SHIFT + Directional Arrow to select entire words moving in that direction.
  • Use the F8 key to enter “Selectiion Mode.” As you continue to press the F* key, you will cycle to select a Word, a Sentence, a paragraph, the complete document.

I guarantee that you will save time and become more efficient when you use these tips to quickly select data in any MS Office Program.

Follow this link to watch this video in High Definition Full Screen Mode on YouTube.

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

How to Effieciently Select Data in MS Office Programs [ 7:36 ] Play Now | Play in Popup | Download (660)
Link to this post!
Filed Under: Excel Training, Integrating MS Office Programs, Podcast Tips & Timesavers, Word Tips Tagged: , , , , , ,
November 17, 2010 1 Comment

Use the Choose Function in Excel to Quickly Lookup a Value

The CHOOSE Function in Excel is an incredibly useful – albeit, relatively unknown – tool to use when you need to look up a Value in a list. In Excel 2007, you can now use CHOOSE to return up to 254 different Values in a list! (In Excel 2003, the limit is 29 values.)

Here is the Syntax: =CHOOSE(Index_Num, Value1, Value2…) where the Index_Num is a positive serial number between 1 and 254 (In Excel 2007)

In this lesson, I first demonstrate how to look up a “label” (January, February, etc.) for a cell that contains a “Number” for the month (1,2,3, etc).

Rather than struggle with “nested IF()statements,” use the CHOOSE Function when you need to return a value for any number between 1 and 254 – it is so much easier!

Click here to watch this Excel Lesson on YouTube in High Definition Full Screen Mode.

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

Use the Choose Function in Excel [ 5:56 ] Play Now | Play in Popup | Download (865)
Link to this post!
Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , ,
November 15, 2010 Leave a Comment

Use a Combo Box to Dynamically Change Your Excel Chart Data

You can “drive” your Excel Charts – dynamically change the data behind the chart. Focus your audience’s attention on the information that you are discussing. Let your audience see a chart that illustrates the scenario that they select.

To do this – add a Combo Box that lists the choices for each chart display that you offer!

(Click here to view my free Excel Training lesson on Combo Box Controls for more information.)

Key Steps to Take:

  • Use the INDEX() Function to look-up the values for your chart data.
  • For the 2nd argument in the INDEX() Function select the cell that is the “cell link” for your Combo Box. Use the F4 Keyboard Shortcut to make this part of the formula “Absolute.”
  • Insert the Chart Type that best represents your data. Position your chart adjacent to the Combo Box.

Click here to watch this video on my YouTube Channel – DannyRocksExcels –  in High Definition and Full Screen Mode.

I have several lessons that cover Charting in Excel 2007 on my DVD, “The 50 Best Tips for Excel 2007.” Buy it now!

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

Use a Combo Box to Dynamically Change Your Excel Chart Data [ 6:53 ] Play Now | Play in Popup | Download (1407)
Link to this post!
Filed Under: Excel Charts, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , , , , ,
November 12, 2010 1 Comment

How to Create a Thermometer Chart in Excel

When you want to create a chart that tracks your progress towards reaching a goal – e.g. Monthly Sales Goal – use a Thermometer Chart in Excel. This is a fairly easy chart to create. However, there are a few “got’cha” steps along the way to avoid. Here are the key points to consider when creating a thermometer chart in Excel:

  • Base the chart on one cell. This cell contains the formula =”month to date number” / goal – formatted as a percentage.
  • Be sure to clearly separate this one cell from the other cells – e.g. insert a blank row about the formula cell.
  • Begin with a Column Chart Type.
  • Format your Vertical Axis on the chart – the percentage values. Set the Minimum Value to be “fixed” at 0.0 and the Maximum Value to be “fixed” at 1.0
  • Add a Data Label to your chart – there are several options for placing this on the chart.
  • Continue to format your chart as desired.

Follow this link if you want to watch this video in High Definition, Full Screen Output.

You can learn more about creating and formatting charts on my DVD, “The 50 Best Tips for Excel 2007.” Click here to add this to your secure shopping cart.

How to Create a Thermometer Chart in Excel [ 7:12 ] Play Now | Play in Popup | Download (874)
Link to this post!
Filed Under: Excel Charts, Excel Training, Podcast Tips & Timesavers Tagged: , , , ,
October 30, 2010 1 Comment

How to Add a Data Validation Drop Down List to an Excel Form

Several viewers wrote to me after I posted my previous video – “How to Add a Combo Box Control to an Excel Invoice Form” to suggest that there is another way to create a “drop down list” for a form. Yes, you can use Data Validation in Excel and use “Allow from List” as your setting. Use the “Stop Style” to prevent a user from typing in a value that is not in the list.

In this Excel Training Video I compare both approaches: Using a Combo Box Control and Adding a Data Validation Drop Down List. Both work well on Invoice Forms. However, you will use a different function to “lookup” other values – e.g. Unit Price – depending upon your choice:

  • With a Combo Box Control, use the =INDEX() function to find the “Unit Price” for the product selected in the Combo Box
  • With a Data Validation Drop Down List, use the =VLOOKUP() function to find the matching “Unit Price.”

Let me know which approach that you prefer. Try both – expand your Excel Skill Set. Add your comments below or send me an email with your thoughts and suggestions: danny@thecompanyrocks.com

You can start to shop for my DVD, “The 50 Best Tips for Excel 2007” by clicking this link.

Learn how you can “Master Excel in Minutes”

How to Add a Data Valication Drop Down List to an Excel Form [ 6:55 ] Play Now | Play in Popup | Download (3114)
Link to this post!
Filed Under: Data Validation, Excel Forms, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , , , , , ,
« Previous Page
Next Page »