Home Archives for Video lessons for Excel
October 10, 2010 1 Comment

Use an Excel Data Table to See Effect on Multiple Formulas

Excel Data Tables are part of the “What-if” Analysis group of tools and commands. In this lesson, I demonstrate how to use a one-input Data Table to show how substituting a series of inputs effects three separate formulas. This is a powerful – and easy – way to get answers to a series of “What-if” questions.

Data Table

2-Input Data Table

Two-Input Data Table

I know that as I learned how to construct Data Tables, it was a lot easier for me to “see” how to set them up rather than to “read about” how they are set up. You can put your series of substitute values in either the “Column running down” or the “row going across” in the data table. To include the three formulas in the Data Table, I simply included “Links” to the formulas in the original assumptions table.

Learn More About “What-if Analysis Tools in Excel

I have identified “What-if” Analysis as one of my “Nine Essential Skills for Excel.” You can learn more about my 4 hour video tutorial package by clicking on either of these links:

 

Use an Excel Data Table to See Effect on Multiple Formulas [ 4:29 ] Play Now | Play in Popup | Download (839)
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Formulas, Excel Training, Podcast Tips & Timesavers, What-if Analysis Tagged: , , , ,
October 7, 2010 Leave a Comment

Use the Visible Cells Command in Excel to Paste Subtotal Results

Earlier this week a client asked me to help her paste the Subtotal results to a new Excel workbook. She was frustrated and nearly hysterical because her manager needed the results in an hour. If you work with Subtotal reports in Excel I am sure that you share my client’s frustration.

The key to making this work is to use the “Visible Cells Only” command before you copy your selection. You can find this command on the “Find and Select” drop-down menu. If you like keyboard shortcuts, you can use Alt + ; for this command. However, remember to make your selection first!

Watch me demonstrate how this works in this short 4 minute 58 second Excel Video Lesson. You can also view it on iTunes and on my YouTube channel. This is one of the tips that I share on my DVD, “The 50 Best Tips for Excel 2007.”

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

Use the Visible Cells Command to Paste Subtotal Results [ 4:58 ] Play Now | Play in Popup | Download (734)
Link to this post!
Filed Under: Excel Training, Podcast Tips & Timesavers Tagged: , , , , ,
October 3, 2010 Leave a Comment

My Video Podcast has been downloaded 4,800 times

My iTunes Podcast

Danny on iTunes

OK, so let me brag a little. I launched my Video Podcast, “Danny Rocks Tips and Timesavers,” in June 2010 on iTunes. When I looked at the number of downloads and views, I was amazed to see that the number had surpassed 4,800 – I am thrilled.

Thank you for your support and encouragement. And, one viewer has reviewed my podcast:

Customer Reviews

Concise training      

by ChipAv

These are very specific and concise training modules on individual aspects of Excel and Powerpoint. (Also a few on meeting management and social media.) No nonsense, down-to-business, accurate, and professional. If one of these matches the skill you want to quickly acquire, you should like these.

I invite you to view, download, subscribe or review my video podcasts – either individually or as a whole. Here is the link to my iTunes video podcast:

http://itunes.apple.com/podcast/danny-rocks-excel-tips/id374923275 – If you like what you see, then pass this link on to a friend or colleague.

You can add your comments below and feel free to use one of the Social Media Icons at the bottom of this post.

 

Link to this post!
Filed Under: Add Video to Your Website, Business Communications, Excel Training, Podcast Tips & Timesavers, Powerpoint Techniques, Tips and Time Savers, Word Tips Tagged: , , , , , , ,
October 1, 2010 3 Comments

How to Create a Pivot Table Year-to-Year Comparison Report

One of my viewers asked for my help in creating and Excel Pivot Table Report. She wants to compare the total number of units shipped during three time periods: the years 2008, 2009 and 2010. The trick to producing this report is to drag the field to be summarized – Units Shipped – to the Values Area three times. Then, you change the Value Field Settings to first Sum the Units, then to compare the change in units year-over-year and finally to express this as a percentage of change. This creates a Year-to-Year Comparison Report.

This is a tip that is best demonstrated visually. So, I invite you to watch this 6 minute and 55 second Excel Video Training Lesson.

If you do not have the time to watch the video now, you can use my RSS Feed to deliver it to your computer or you can subscribe to my Video Podcast on iTunes to watch it later.

Learn how to “Quickly Create Pivot Table Reports and Charts”

How to Create a Pivot Table Year Over Year Comparison Report [ 6:55 ] Play Now | Play in Popup | Download (3302)
Link to this post!
Filed Under: Excel Financial Tools, Excel Training, Pivot Tables, Podcast Tips & Timesavers, Video Response to Viewers Tagged: , , , , , ,
September 26, 2010 Leave a Comment

How to Create and Modify Excel Pivot Tables

How is it possible that the most powerful tool in Excel is also one of the easiest tools to use? An Excel Pivot Table is, in my opinion, the most powerful tool for analyzing data and presenting it as valuable and actionable information. And, an Excel Pivot Table is also one of the easiest tools to use.  

Summarize Data with Pivot Tables

Create a Pivot Table

Create a Pivot Table in six steps:  

  1. With one cell in the data table selected, I clicked on the Insert Tab of the Ribbon and clicked Pivot Table.
  2. I made one change to the default settings – I clicked a cell on the current worksheet to place my Pivot Table.
  3. On the Pivot Table Field List, I placed the Division Field in the Columns area and the Products Field  in the Rows area,
  4. Also on the Pivot Table Field List, I placed the Sales Field in the Values area.
  5. To format the Sales field Subtotals, I Right-clicked a single number and chose Value Field Settings – Number Format.
  6. I also changed the name from “Sum of Sales” to “Sales Revenue.”

It’s that easy! Try it yourself. You will be amazed at how easy it is to create your first Pivot Table. And… as an added bonus, with a Pivot Table you can not harm the underlying data! Pivot Tables work with a “virtual snapshot” of your actual data. Because a Pivot Table uses a “memory cache”  of your data, you can quickly swap your data fields from Rows to Columns to Filters, etc. This is done quickly and efficiently. 

Learn how to “Quickly Create Excel Pivot Table Reports and Charts.”

Here, on the website of The Company Rocks, I offer many free Excel Training Videos to help you get started to create and modify Excel Pivot Tables. Here are the links to the Introductory set of Pivot Table video lessons: 

You can also search for free Excel Pivot Table Video Lessons by following these links: 

“The 50 Best Tips for Excel 2007” DVD

Excel Tip List

Excel DVD Tips

You can also purchase my DVD, “The 50 Best Tips for Excel 2007.”It is affordable and it offers over 5 1/2 hours of focused Excel training tips, tricks and techniques. 

Follow this link to start shopping with my secure shopping cart. I guarantee that you will enjoy my Excel Video Tips. If you are not 100% satisfied, I will refund your purchase price with no questions asked!

Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , ,
September 24, 2010 Leave a Comment

List of My Most Popular Posts and Pages

Summer – in the Northern Hemisphere – is now behind us. I went back to survey the videos and pages on my website that had the most views during the Summer of 2010. 

Here is a listing of my “Top 10 Video Posts” and my “Top 5 Pages.” Click on the Links for each title to go directly to the Video Lesson or Page.

The “Top 10 Video Posts” on The Company Rocks Website:

  1. Simplify Data Lookups in Excel
  2. Make Excel Come Alive with Visualization Tools
  3. Build an Accounts Receivable Aging Report
  4. Summarize Multiple Excel Worksheets – Consolidate Data by Position
  5. How to Merge Multiple Excel Workbooks into a Master Budget
  6. Use Pivot Tables to Summarize by Year, Quarter and Month
  7. Perform Break Even Analysis with Excel’s Goal Seek Tool
  8. 2 Reasons to Format Cells Before You Enter Data in Excel
  9. Hide and Show Details in Excel Pivot Tables
  10. Use Vlookup Function to Prepare a Payroll Statement

The “Top 5 Pages” Viewed on The Company Rocks Website:

  1. The Excel Home Page
  2. Index to Excel Video Lesson Topics
  3. List of “The 50 Best Tips for Excel 2007”
  4. Excel Training Videos by Category
  5. The 50 Best Tips for PowerPoint 2007

The Danny Rocks Tips and Timesavers Podcast

During the Summer of 2010, I started my own video podcast at the iTunes Store. These podcasts are free of charge. The video resolution is optimized for viewing on an iPad, iPod or other portable media device.

The DannyRocksExcels Channel on YouTube

Over the past two years I have posted 104 video lessons on my “DannyRocksExcels” Channel on YouTube.

The DannyRocksPowerPoint Channel on YouTube

I maintain a separate channel on YouTube – “DannyRocksPowerPoint”– to post my PowerPoint Tips and Narrated PowerPoint Presentations. I invite you to vist my channel to view, download and share these videos with your friends and colleagues. Click here to go to my YouTube Channel.

“The 50 Best Tips for …” series of DVDs

I offer five DVDs for sale on this website. Click here to start your shopping cart. I guarantee your satisfaction. If you are not 100% satisfied with my DVDs, I will refund your purchase with no questions asked!

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

Here is a listing of the DVDs that I offer for sale. Click on the link for each title to go to the list of the tips that I offer on each DVD:

Link to this post!
Filed Under: Excel Training, Most Viewed Excel Videos, Podcast Tips & Timesavers, Powerpoint Techniques Tagged: , , , , , , , ,
September 21, 2010 Leave a Comment

How to Use Excel Lookup Functions to Create a Customer Invoice

Excel has many powerful Lookup Functions. You can use them, for example,  to lookup an Item Number when you know the Product Description. Or to automatically look up the List Price for an Item Number on your customer invoice. In this lesson I show you how to use three Excel Lookup Functions:

  1. The INDEX() Function
  2. The MATCH() Function
  3. The VLOOKUP() Function

I hope that you enjoy learning – and applying – these techniques. I would like to hear from you, so you can either add a comment below or send me an email.

You can receive my new Tips and Timesavers Videos automatically by subscribing to my RSS Feed or to my free podcast on iTunes. Click here to view, download or subscribe to my podcast.

You can purchase any of my 5 DVDs in “The 50 Best Series …” for Excel 2007, PowerPoint 2007, Word 2007, Outlook 2007 and Access 2007 – Click here to open your shopping cart!

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

How to Use Excel Lookup Functions to Create a Customer Invoice [ 5:24 ] Play Now | Play in Popup | Download (789)
Link to this post!
Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers, Uncategorized Tagged: , , , , , , , ,
September 17, 2010 Leave a Comment

Two Ways to Create a Frequency Distribution in Excel

What is a “Frequency Distribution?” A good example is to see how frequently daily sales are “under $75.00” or how often they range “between $75.00 and $150.00.” Once you know how frequently a result occurs, you can better focus your attention on that particular segment of your business.

In this short 5 minute and 45 second Excel Training Video Lesson, I demonstrate two approaches to creating a Frequency Distribution Report:

  1. Use the =FREQUENCY() Function – this is an ARRAY Function.
  2. In a Pivot Table, Group the Row Labels to produce a Frequency Distribution.

An ARRAY Function has two “Got’cha steps:”

  1. Select all of the cells that will contain the results before your enter your Array Formula.
  2. Use the Ctrl + Shift + Enter keyboard combination to enter your Array Formula.

You can watch this video here on my website, you can download it via an RSS Feed or you can watch it as a Podcast at the iTunes Store.

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

Two Ways to Create a Frequency Distribution Report in Excel [ 5:45 ] Play Now | Play in Popup | Download (818)
Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , ,
September 14, 2010 Leave a Comment

How to Use an Excel Data Table for “What-if” Analysis

Excel has many tools that give you answers to “What-if” questions. An Excel Data Table allows you to feed a series of “substitute values” into two arguments in a formula. For example, with a Data Table you can:

  • See a table of monthly payments on a loan by substituting both the “Interest Rate” and the “Amount Borrowed” simultaneously.
  • See a table of “Net Payments” by changing both the “Sales Price” and the “Quantity” simultaneously.
  • See the amount that you “tip” a service professional by changing both the “Tip Percentage” and the “Food Total” simultaneously.

As you will see in this Excel Video Lesson, the trick is to understand which series of values are the “Row Inputs” and which series of substitute values are the “Column Inputs.”

Trust me on this: It is easier to “see” how to set up a Data Table than it is to “write a description” of a Data Table. So, I invite you to “see for yourself” how easy it is to set up an Excel Data Table and get answers to two simultaneous “What-if” questions!

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

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

How to Set up Excel Data Tables for "What-if" Analysis [ 6:43 ] Play Now | Play in Popup | Download (617)
Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Podcast Tips & Timesavers, What-if Analysis Tagged: , , , , , , ,
September 13, 2010 Leave a Comment

When to Use Absolute, Relative & Mixed Cell References in Formulas

In my experience, I find that many Excel users get easily confused when it comes time to use an Absolute or a Mixed Cell Reference in a formula. Let me demonstrate how and when to use the proper cell references, so that you always get the correct result.

Here is what each type of cell reference looks like in a formula:

  • Relative Cell Reference – =A2*B2 when copied down one row becomes = A3*B3
  • Absolute Cell Reference – =A2*$B$2 when copied down one row becomes =A3*$B$2 (Notice the $B$2 Absolute cell reference in the formula)
  • Mixed Cell Reference – =$A2*B$3 when copied down one row becomes = $A3*B$3 and when copied one cell to the right becomes =$A*C$3 (Notice how one part of the cell reference is relative – it moves; and the other part of the reference is Absolute – it remains fixed in place)

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

How to Use Absolute, Relative & Mixed Cell References in Formulas [ 5:09 ] Play Now | Play in Popup | Download (788)
Link to this post!
Filed Under: Excel Basics, Excel Formulas, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
« Previous Page
Next Page »