Home 2010 Archives for November 2010

Archives for November 2010

November 30, 2010 9 Comments

How to Name a Large Range of Non-Contiguous Cells in Excel

A client asked for my help on this problem. How to create a Named Range in Excel for Non-Contiguous Cells. The problem is that Excel puts a 255 character limit on the “Refers to” portion of a Named Range. I struggled with this until I found the answer in one of my favorite resource books, “This isn’t s Excel, it’s Magic!” written by Microsoft MVP (Most Valued Professional) Bob Umlas.

Solve the Problem

  1. Select the non-contiguous cells that you want to name.
  2. Open up the Visual Basic Environment (Alt + F11 is a Keyboard Shortcut).
  3. In the VBE, open up the “Immediate Pane” – Ctrl + G is the Keyboard Shortcut.
  4. Finally, type Selection.Name = “MyLabels” in the Immediate Pane and press Enter. (Substitute the name you want for “MyLabels”).

When you return to Excel, you will see that you can use the Drop-down list in the Name Box or in the Excel 2007 Name Manager to select this range.

 Amazing! Thanks Bob!

Additional  tips

  • Keyboard Shortcut F5 to open up the “Go To” Dialog Box. Select “Special” and choose “Constants – Text” to select the non-contiguous Labels that you want to name.
  • Right-click the worksheet tab and select “View Code” top open up the VBE (Visual Basic Environment) window.

I invite you to watch and subscribe to my Video Podcast on iTunes – follow this link to “Danny Rocks Tips and Timesavers”

Click here to watch this video in High Definition, Full Screen Mode on my YouTube Channel – Danny Rocks Excels

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

How to Name a Large Range of Non-contiguous Cells in Excel [ 4:49 ] Play Now | Play in Popup | Download (788)
Link to this post!
Filed Under: Excel Training, Names for Cells and Ranges, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: , , , , ,
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 1 Comment

Watch My Excel Videos in High Definition Full Screen Mode on YouTube

Logo for YouTube

YouTube Logo

At the request of several viewers, I have begun to produce my Excel, PowerPoint, Word, Outlook and Access Video Lessons in High Definition, Full Screen Viewing Mode. You can watch them, download them, share them and comment on them on YouTube. My YouTube Channel is DannyRocksExcels.

Here is the link to the YouTube”Playlist” that I have created for the HD Videos:

http://www.youtube.com/view_play_list?p=9D34C8C834E21A51

Please “bookmark” this link. As I add more HD Videos on YouTube, the Playlist will be updated.

My iTunes Podcast

Danny on iTunes

I will continue to post my videos on www.thecompanyrocks.com in M4V video format so that they can be sent automatically to the iTunes store. I invite you to subscribe to my video podcast – Danny Rocks Tips and Timesavers – on iTunes. It is free! Here is the link to my iTunes podcast:

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

I welcome your feedback on my videos. Let me know what you like, what you don’t like or would like to see improved. Send me your questions and I will do my best to answer them promptly. Just drop a note to say hello!

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

Link to this post!
Filed Under: MS Excel Training, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks 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: , , , , , , , ,
Next Page »