Archives for August 2010

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!”

Review for My 50 Best PowerPoint 2007 Tips DVD

I was pleasantly surprised to see my DVD, “The 50 Best Tips for PowerPoint 2007” reviewed today by noted author and speaker, Ellen Finkelstein.

Here is a link to Ellen’s review

Notice that the review is part of Ellen’s “PowerPoint Tips Blog.” I encourage you to go to Ellen’s website – – to read and subscribe to her newsletter and to gather some great tips for improving your presentations. By registering, you can download a free white paper: “From Death by PowerPoint to Life by PowerPoint.” Check out Ellen’s site today.

Danny Rocks

The Company Rocks

Consolidate Data from Multiple Excel Workhseets Part 2

In Part One, I showed you how to Consolidate Data By Position –  where the range of cells was consistent in each of the Excel worksheets –  Click on this link to go to this lesson.

Consolidate Data By Category

In this lesson, I show you how to Consolidate Data By Category / By Label. You use this technique when the number of rows or columns containing values varies from worksheet to worksheet. For example, the number of sales representatives reporting may change each month. Or, some of the worksheets that you receive contain an extra column that you want to include in your consolidation. Consolidating Data by Category is very flexible as you will see in this lesson.

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

View My Excel Training Videos on YouTube

DannyRocksExcels on 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.
  • 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!”

Summarize Multiple Excel Worksheets – Consolidate Data By Position

There are many ways to Summarize the data that is stored in multiple Excel Worksheets or Workbooks. Pivot Tables are great for producing summaries. However, many people do not use – or do not know how to use – Pivot Tables, so let me demonstrate how to use Excel’s Consolidate Data Tool to get the job done.

Consolidate Data By Position

In this scenario, I will take the data from four identical worksheets and consolidate the sales numbers in a new worksheet. First, without using a “Link” to keep the data in the consolidated worksheet current and then I show you how to create a link to the Source Data.

But… there is a “Got’cha Step” when you link sources. It is possible to “double your sales numbers” without realizing it! This might make you feel good when you first see this. However, this is not good – when you are found out. And, trust me on this, someone will definitely find this error!

SUM Across Group of Excel Worksheets

As a bonus, I include another technique to SUM cells from multiple worksheets. Watch as I show you this “trick” – how to use the SUM() Function to total data across a contiguous group of Excel worksheets. It really is a great tip to learn!

Watch My Video on YouTube

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

Related Video Tutorials

I continue this lesson on Data Consolidation in Part Two. Click on this link to see how to Consolidate Data By Category.

Watch my Video on iTunes

You can download and view this Excel Training Video at the iTunes Store. Follow this link to subscribe to the “Danny Rocks Tips and Timesavers” podcast.

My Video Training Resources

You can learn “The 50 Best Tips, Tricks and Techniques for Excel 2007” when you purchase my DVD – ROM!

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

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!

Cast Roles for Effective Meetings

One way to ensure full and balanced participation in your meetings is to “cast the roles” that each participant plays during the meeting. Want to minimize the disruption of “agenda hijackers?” Assign specific roles for each participant. Want to maximize participation? Limit your meeting invitations to the people who have been cast to play a specific role in helping you to accomplish the purpose of your meeting.

In this video, I detail the roles and responsibilities for each of these “cast members:” Leader, Facilitator, Recorder, Timekeeper, Parking Lot Manager, Subject Matter Experts, Stakeholders, and Devil’s Advocate. In my experience I have found that when you help people to understand the role that they need to perform, you improve their performance.

Watch this 7 minute and 27 second video and let me know what you think. Do you have other roles to suggest? Have you tried this approach? If so, I invite you to add your comments below.

This is the second installment in my “Running Effective Meetings” series of podcasts. Follow this link to view the first installment. I invite you to subscribe to my – free – podcast at the iTunes store by following this link.