Home Archives for Excel Keyboard Shortcuts
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 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: , , , , , , , ,
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: , , , , , , , , ,
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 24, 2010 1 Comment

View My Excel Training Videos on YouTube

DannyRocksExcels on YouTube

YouTube

Two years ago, in August 2010, I started uploading my Excel video training lessons to my YouTube Channel – DannyRocksExcels. Now there are 93 Excel videos available. I was originally reluctant to post my video content on YouTube because I selfishly wanted to restrict my videos to my own website. After a conversation with a colleague, I changed my mind and began to upload all of my Excel videos to YouTube.
Why?
  • YouTube attracts a much more diverse audience than I could ever hope to attract on this website.
  • YouTube makes it easy to download and share my videos with others.
  • YouTube has a great analytics program – Insight – that allows me to gain valuable information about my audience.
  • YouTube is now showing several of my most popular videos as ads automatically (via Google AdSense)
  • I have good interaction with my viewers on YouTube – comments, requests for new videos, etc.

When I look at the numbers on YouTube I am amazed!

Here is a summary:

  • My Excel Videos have been viewed almost 200,000 times over the past two years.
  • One Video (Introduction to Pivot Tables) has been viewed over 20,000 times!
  • My DannyRocksExcels Channel on YouTube now has over 500 subscribers.

I invite you to view, download or subscribe to my YouTube Channel for Excel Training videos by following this link.

Thank you!

Danny Rocks

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

Link to this post!
Filed Under: Add Video to Your Website, Excel Training, MS Excel Tips, MS Excel Training, Video Response to Viewers Tagged: , , , , , , ,
August 20, 2010 8 Comments

Excel Training Videos by Category

Here is a listing, by category – with hyperlinks – to the free Excel Training Videos that I offer on The Company Rocks website. Simply click on the hyperlink for each Category to go directly to the Archive of Excel videos in that category. 

Note: Some of my Excel Videos are listed in multiple categories.

Excel Tip List

Excel Tips

The companion page to this one is, “Index of Excel Video Training Lessons.” In the Index you will see the title of each Excel Video Tutorial listed beneath the category.

If you like the free videos that I offer, I guarantee that you will enjoy my DVD – “The 50 Best Tips for Excel 2007” – now on sale here on my website.

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

Learn how to quickly “Create Pivot Tables and Charts.”

Excel Training Video Lessons – by Category:

“The 50 Best Tips for Excel 2007” DVD is now on sale!

Link to this post!
Filed Under: Excel Training, Video Training Courses 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: , , , , , , , , , ,
February 11, 2010 2 Comments

Video Tour My DVD – The 50 Best Tips for Excel 2007

I invite you to join me on a four minute, seven second video tour of my new instructional DVD, “The 50 Best Tips, Tricks & Techniques for Excel 2007.”

This DVD is different. If you want to learn Excel and you are pressed for time (and who isn’t these days?) this is the DVD for you. I give you over 5 1/2 hours of focused video instruction. The average length of each video lesson is 6 1/2 minutes! So you can find the topic that you want, learn it, and put it into practice at home or at work in under 10 minutes!

As a bonus, I include the 50 Excel worksheets that I used to create and film the video lessons. Now you can practice the tips to master your new Excel skills!

Follow this link if  you want to see the complete list of the 50 video lessons on my DVD.

Are you ready to purchase “The 50 Best Tips for Excel 2007?” Follow this link to enter the online bookstore for The Company Rocks. This is a secure shopping site. And, I guarantee that you will be satisfied with your purchase. If you are not satisfied with your purchase, I will refund you money – no questions asked!

Sincerely,

Danny Rocks

Related Videos

Link to this post!
Filed Under: Excel Training, MS Excel 2007 DVD, MS Excel Tips, Online Store for The Company Rocks, Tips and Time Savers, Video Training Courses Tagged: , , , , , , , , , , ,
December 30, 2009 Leave a Comment

Announcing the Online Store for The Company Rocks

Danny's DVDs "The 50 Best Tips" Series Available in Online StoreToday, I launched my new online bookstore for The Company Rocks. I am offering my Instructional DVDs for sale individually or as a set at attractive prices. Follow this link to enter the store to learn more about the DVDs in “The 50 Best Tips for …” Series.

“The 50 Best Tips for Excel 2007” contains over 5 1/2 hours of training. Each video lesson lasts @ 6 – 7 minutes. Each lesson focuses on one productivity tip. So you can get an answer quickly when you need it. I follow a step-by-step approach to teach you how to be more productive when you use Excel 2007. You can see how to enter and edit the formulas because I use an innovative “pan-in / pan-out” camera technique to focus your attention.

And, as a bonus: The actual spreadsheets that I use for each lesson are included on the DVD!

“The 50 Best Tips for PowerPoint 2007” packs over seven hours of training onto two DVDs! Unlike many instructional packages the teach you PowerPoint from a technical angle, I teach you how to incorporate PowerPoint into your total presentation. I am a professional speaker and trainer. I am not a “techie.” You will learn the technical side of PowerPoint 2007 – but only as it helps you to be more effective in presenting your ideas or products. I think that you will enjoy this difference!

The focus of each DVD is on the Intermediate to Advanced user of Excel or PowerPoint. The focus is on helping you to save time and take advantage of the great presentation elements in both programs. There are 10 videos each at the Beginning Level and at the Advanced Level. There are 30 videos for the Intermediate Level user.

Please visit my new online bookstore to learn more about each DVD. I welcome your feedback!

Thank you for your kind words of support as I have built my business these past three years. I wish you a very happy new year in 2010!

Danny Rocks

danny@thecompanyrocks.com

Link to this post!
Filed Under: Business Communications, Online Store for The Company Rocks, PowerPoint Video Training, Video Training Courses Tagged: , , , , , , , , ,
« Previous Page
Next Page »