Home Archives for Video lessons for Excel
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: , , , , , , , , ,
October 28, 2010 13 Comments

How to Add a Combo Box Control to an Invoice in Excel

If you are creating or modifying an Invoice Form in Excel, you will want to add a Combo Box Control to allow users to select products from a listing. The key in formatting your Combo Box Control is to choose the correct cell to contain the “Cell Link.”

Cell Link in Form Control

I think that you will benefit from “seeing how this is done” in this video lesson. I know that I always struggled with “reading about” Excel Form Controls. Once you see how important the “Cell Link” placement is, you will better understand how Combo Box Controls work.

Use INDEX Function

Once we have formatted the Combo Box, we need to be able to look up other values to place on our Invoice. In this example I demonstrate how to use the INDEX() Function to lookup the “Unit Price” for each product selected from the Combo Box list on the Invoice. As a best practice, I recommend that you use “Named Ranges” for the “array” that you Index. The INDEX() Function has three arguments:

  1. The ARRAY to Index – In this case our “named range” with three fields (Product Name, Unit Price, Cell Link)
  2. The ROW reference – In this case the cell in the ARRAY that contains our CELL LINK for the Combo Box
  3. Optionally, the COLUMN reference – in this case “2” for the 2nd Column in the ARRAY (Unit Price)

Let me know if my videos in this series have helped you to understand how to use Form Controls in Excel. It took me some time to figure out how they worked; I hope that I can save you some time and ease your frustration in apply them to your forms. Add your comments below or send me an email – danny@thecompanyrocks.com

Find Additional Videos for Form Controls in Excel

Form Controls include Option Buttons, Spinners, List Boxes and more. Here is a link to the other videos in my series on Form Controls in Excel.

Watch Tutorial on YouTube

If you prefer, follow this link so that you can watch this video on my YouTube Channel – DannyRocksExcels

Resources Available at My Online Shopping Site

I invite you to visit my new, secure online shopping site  where you can Learn how you to “Master Excel in Minutes – Not Months!

How to Add a Combo Box Control to an Invoice Form in Excel [ 8:01 ] Play Now | Play in Popup | Download (1645)
Link to this post!
Filed Under: Excel Forms, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , , , ,
October 25, 2010 Leave a Comment

How to Use the VLOOKUP Function in Excel

The VLOOKUP Function is one of the essential Excel functions that you need to understand in order to work with data tables, invoices and product listings. In response to numerous email requests, I created this video lesson to walk you through each step.

I share my best practices which include:

  • Use a “named cell range” for your “array.” The “array” is the table that contains the information that you are looking up.
  • Make sure that the “Leftmost” column in your array is sorted in ascending order and that this column contains the values that you are looking for.
  • Use “FALSE” as the optional fourth argument when you need to find an “exact” match in the Leftmost Column.

Work with Excel Workbook Used in This Lesson

Purchase DVD-ROM

You can learn more about essential Excel Functions on my DVD, “The 50 Best Tips for Excel 2007.” You can buy it now!

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

How to Use the VLOOKUP Function in Excel [ 5:36 ] Play Now | Play in Popup | Download (1006)
Link to this post!
Filed Under: Excel Functions, Excel Training, Interactive Webpages, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , , ,
October 23, 2010 Leave a Comment

How to Add Scroll Bar Controls to Excel Forms

This is the fourth in my continuing series of Excel training video lessons on Adding Form Controls to your worksheets. Here, I demonstrate how to Add Scroll Bar Controls to a Loan Scenario. Form Controls help to make your Excel worksheets “interactive,” and they are great tools to add when you distribute spreadsheets to users who may not be proficient in Excel.

Understand the Cell Link

The key concept to understand with Form Controls is the “Cell Link.” The form controls are merely “drawing shapes” until you actually link them to a cell that controls their output. The “got’cha” step for Scroll Bars – as with Spin Buttons – is that the Maximum Value is 30,000. So, if you want a control to show a loan amount of, for example, $300,000.00 you make the Maximum value 300 and then create a formula that references the “cell link” and multiplies it by 1,000.

In my research on Form Controls, I rarely find authors who mention this point – and, in my experience, this is how most Excel users get “tripped up” when they want to add Form Controls to workbooks to make them interactive. Watch as I walk you through each step in this process.

Please, let me know if this video lesson has helped you to better understand how Excel Form Controls work. Add your comment below – I promise to respond to your comments!

Danny

You can click on this link to view the other videos in my series on Form Controls in Excel.

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

How to Add Scroll Bar Controls to Excel Forms [ 5:32 ] Play Now | Play in Popup | Download (1981)
Link to this post!
Filed Under: Excel Forms, Excel Formulas, Excel Training, Podcast Tips & Timesavers, Video Training Courses Tagged: , , , , , , ,
October 21, 2010 Leave a Comment

Learn How to Protect Your Excel Worksheets

Protecting your Excel worksheet is a two-step process. Understand that, by Default, all cells in the worksheet are “locked.”

1) Your first step is to select the input cells that you want your end-user to be able to type in or edit and “unlock” them. That is the “got’cha” step.

2) Next, you go to the Review tab on the Ribbon – in Excel 2007 – and apply the Protection options that you want to be in place for the worksheet.

I like to apply protection to any Excel worksheet that I intend to distribute to end-users. I want them to be able to interact with the data, but I do not want them to be able to change the formulas and formatting that I worked hard to put in place.

Understanding the need to “unlock” input cells while “locking” formula cells is the key to successfully protecting your Excel worksheets. I walk you through two examples in this short video lesson. In addition, I show you how to protect your entire workbook – with or without a password.

This is one of the tips that I include on my DVD, “The 50 Best Tips for Excel 2007.” Click here to purchase it today!

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

Learn How to Protect Your Excel Worksheets [ 6:31 ] Play Now | Play in Popup | Download (561)
Link to this post!
Filed Under: Excel Basics, Excel Training, Podcast Tips & Timesavers, Workbooks and Worksheets in Excel Tagged: , ,
October 18, 2010 Leave a Comment

Create User Friendly Excel Forms Using Spin Button Controls

An Excel Form is great way to distribute an Excel worksheet to your customers or field representatives. Most of the time, you do not want them to type values into a cell. You want to make this Form as “user friendly” as possible! You want to add “Spin Buttons” to your form, so that the user can simply click to change cell values up or down in the increments that you want to display.

A perfect example: A Mortgage Loan Scenario where – by clicking the Spin Buttons – your client can see what their monthly payment will be. This is my second lesson covering Excel’s Form Control Tools. Watch as I demonstrate how to “work around” the Form Controls limits of Maximum Amount for a Spin Button (30,000) and also how to increment interest rates by 1/4 of a Percent.

From personal observation, I know that many people start to create an Excel Form and then just give up in frustration. I did too – in the beginning! Watch me demonstrate how to avoid the traps of the “got’cha” steps in this short Excel Training Video.

Check out the great tips that I offer on my DVD, “The 50 Best Tips for Excel 2007.” Click here to start shopping.

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

Create User Friendly Excel Forms with Spin Button Controls [ 8:54 ] Play Now | Play in Popup | Download (1298)
Link to this post!
Filed Under: Excel Financial Tools, Excel Forms, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , ,
October 16, 2010 Leave a Comment

How to Add a Check Box Control to an Excel Form

Judging by the comments that I get from many clients, adding a “Check Box Control” to and Excel Form is not as easy as it appears at first glance.

And, I agree! I, too, struggled with adding Form Controls in Excel. Most of the explanations that I read seemed to add more confusion than clarification!

Spin Buttons in Excel

Spin Button Controls

That is why I created this Excel Training Video: to  lead you through the process; to save you some time and to ease your frustration.

Key Points to Remember:

  1. In Excel 2007, be sure to display the “Developer Tab” on the Ribbon in order to to Insert any Form Control.
  2. Choose the Controls in the “Forms Controls” and NOT in the Active X Controls.
  3. After you “draw” the Control Box on your Form, be sure to select a “Cell Link” to enable the Check Box Control.
  4. Remember that a Check Box Control can only be applied when the result of the Formula or Option is a Logical Value – either a result of  “True or False.”

Don’t be scared off! I find that this is an Excel concept that is better presented “visually.” I struggled for some time when I “read” how to add form controls.

So … I welcome your thoughts – “True or False.” Did I make this easy for you to understand?

Please feel free to add your comments below!

Watch My Video on YouTube

Follow this link to watch my tutorial on my YouTube Channel – DannyRocksExcels

Watch Video Now

 

Master Excel in Minutes Training Resources

I have created a series of extended length Excel Training Videos. I invite you to visit my secure online shopping site to:

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

How to Add a Check Box Control to an Excel Form [ 10:15 ] Play Now | Play in Popup | Download (1315)
Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Excel Forms, Excel Training, Most Viewed Excel Videos, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
« Previous Page
Next Page »