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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

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.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

How to Create Custom PowerPoint Slide Shows

Do you carry around multiple versions of a PowerPoint file – one for each of your five customers? Have you ever carried the wrong version? Have you ever forgotten to update each version with the latest information?

If so, you need to learn how to create and run a PowerPoint Custom Slide Show. You can even hyperlink to custom shows within your main presentation! And, no more File Save As – you only use one file!

You can view this short PowerPoint Video here on my website, download it, or subscribe to my – free – podcast at the iTunes store.

This PowerPoint Training Video Lesson is typical of the tips that I offer on my DVD, “The 50 Best Tips for PowerPoint 2007.” Click here to buy it now.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Perform Break Even Analysis with Excel’s Goal Seek Tool

As a small business owner or investor, you ask many“what-if?” questions. In preparing to present your business plan to an investor you want to know your “break even” point. Fortunately, Excel has a great tool – Goal Seek – that can save you time by creating multiple scenarios to help you determine your goal – to break even!

You can watch this Excel Video Lesson here – online – or you can download it to watch later, at your convenience. I invite you to subscribe to my podcast, Danny Rocks Tips and Timesavers” at the iTunes store by clicking this link – this is a free subscription.

Click here to see a listing of “The 50 Best Tips for Excel 2007.”

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Using Social Media to Engage Customers

Social Media can help you to grow your business. In order to use social media successfully, it is helpful that you follow a proven process. The AIDA process has successfully guided direct mail marketers for years. It is a perfect match for helping you to get customers to click deeper into your website: to turn their curiosity into an action; to make a sale; to make an appointment.

To see how to apply AIDA (Attention, Interest, Desire, Action) to your internet marketing plan, click to watch – or download –  this short 5 minute video.

You can read and download this article – Published on EZineArticles

Follow this link to subscribe to my podcast – “Danny Rocks Tips and Timesavers” – at the iTunes store.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

How to Animate SmartArt Diagrams in PowerPoint

SmartArt Diagrams were introduced in Office 2007. Using SmartArt on a PowerPoint slide is a great way to present a business concept. They are much more interesting for both the audience and the presenter  – compared to “boring bullet points.”

However, when you want to engage your audience, it helps to apply Custom Animation to the Smart Art so that each part of the diagram appears in the order – and with the timing – that you want.

You can quickly download this five minute video by clicking just below this image. Or, you can get this – and my other Podcast tips – from the iTunes Store by clicking this link.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Use Excel’s Goal Seek to Find Formula Error

A viewer wrote me seeking help with a formula. He was calculating a monthly payment for a loan using Excel’s PMT() Function and he was surprised at the result of the formula. I reviewed his formula and discovered that it was not a “formula problem.” Rather, it was a “results problem!” Watch this short video to see how I solved this problem for my viewer by using Excel’s “Goal Seek” tool. So now, instead of a monthly payment of $10,666.67, the result is $501.38 – a very different result!

You can download this video – for free – here on my website. Just click the button below the video image.

You can also subscribe to my Podcast, “Danny Rocks Tips and Timesavers” at the iTunes Store.

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Show Percentage of Calculations in Pivot Tables

Most people are content to let a Pivot Table Sum, Average, or Count their Values Fields. But what if you want to see individual Products as a percentage of all products sold in a Region – this is a standard request for Excel. And … it is easy to perform this calculation in a Pivot Table. Let me show you how to do this.

Follow this link to subscribe to the Danny Rocks “Tips and Timesavers” Podcast at the iTunes Store.

Learn how to “Quickly Create Pivot Tables and Charts in Excel.”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn