Home Archives for Excel Video Training
December 21, 2010 4 Comments

How to Filter Excel Data and Save Custom Views

With an Excel data set, you rarely want to view all of the records – hundreds or thousands of records. Rather, you want to view a subset of the data – e.g. Sales of Laptop Computers in June sold by Bob. To do this, you create and apply filters. A filter is similar to a query – you are asking a question and getting the answer – information – from your data. If this is a question that you ask frequently, you can save the filter as a Custom View.

Filter Types in Excel

Filter Types

In Excel 2007, filtering has been greatly improved with the introduction of Date Filters, Text Filters and Number Filters. Now it is easy to find the sales for “last week,” or the invoices that will be due “next month,” etc. These new filter types – also available in Access 2007 – allow you get better information quicker from your data set.

Top 10 and Above Average Filters

In this lesson, I also demonstrate how to use the “Top 10” filter. You can also use the new “Above Average” and “Below Average” number filters to quickly find your best performers.

Create Custom Views

There is one “frustration” with Custom Views: If you use Tables in Excel 2007, you cannot use a Custom View. In fact, all Custom Views are disabled if you have a Table on any worksheet in your Excel Workbook.

You can learn more about filtering in Excel – click here to watch my video on Using Advanced Filters in Excel.

Want to watch this video in High Definition, Full-screen Mode? Click here to go to my DannyRocksExcels Channel on YouTube.

View my Video Podcast on iTunes. Click here to go to my iTunes Video Podcast, “Danny Rocks Tips and Timesavers.”

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

How to Filter Excel Data and Save Custom Views [ 9:00 ] Play Now | Play in Popup | Download (1234)
Link to this post!
Filed Under: Data Filters and Queries, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
December 20, 2010 1 Comment

Learn How to Quickly Create Excel Pivot Table Reports

Excel Data Set

Data Set in Excel

Many people are curious about Excel Pivot Tables. They would like to learn how to create a Pivot Table, but they fear that that will take too long and be difficult to learn. 

Here’s the good news! Pivot Tables are one of the most powerful tool in Excel AND Pivot Tables are also one of the easiest Excel Techniques to learn! It sounds like a contradiction, doesn’t it? 

How would you like to turn this data set – with over 500 rows of data … 

into this Pivot Table Summary Report with @ six clicks of the mouse? 

Pivot Table Row & Column

Pivot Table Summary

 That’s all it takes. With a Pivot Table you turn long lists of data into summarized information that highlights the trends and patterns in your business. 

Special: Save 20% on any purchase! Use Coupon SAVE-20-PCT during Checkout! 

I can teach you how to get started on the road to mastering Excel Pivot Tables. On my DVD-ROM, “The 50 Best Tips for Excel 2007,” I have the following Pivot Table video lessons: 

  • Introducing Pivot Tables – Run time: 8:23
    • Why Use Pivot Tables?
    • How to Create a Pivot Table
    • How to Modify a Pivot Table
    • How to Update a Pivot Table
  • Grouping and Charting in Pivot Tables – Run time: 5:50
    • Grouping Pivot Table Fields by Month, Quarter and Year
    • Grouping Pivot Table Fields by Week
    • Collapsing and Expanding Pivot Table Fields
    • Creating Pivot Charts
  • Using New Pivot Table Features in Excel 2007 – Run time: 7:46
    • Base Your Pivot Table on an Excel Table
    • Adjust Pivot Table Report Layouts
    • Apply Conditional Formatting to Pivot Tables
    • Insert Blank Rows in Your Pivot Table Report
The 50 Best Tips

The 50 Best Tips Series

As you can see from the “Run times,” each lesson is focused on one Excel technique. If you don’t have a lot of time and you want to quickly learn how to create Pivot Tables, my DVD-ROM is the best place to begin. 

Here is a PDF that you can download to see the content and Run times for each of my tips: The 50 Best Tips for Excel 2007 Detailed Content Listing 

And, here is a link to my Archive of Free Pivot Table Videos  on The Company Rocks website. 

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

Thank you! 

Danny Rocks

Link to this post!
Filed Under: Excel Training, MS Excel 2007 DVD, New in Excel 2007, Online Store for The Company Rocks, Pivot Tables Tagged: , , , , ,
December 18, 2010 5 Comments

The 50 Best Tips for Excel 2007 – Download Detailed Content Listing

By request, I have created a detailed content listing of each of “The 50 Best Tips for Excel 2007” available for you to download. Not only do you get a List of the 50 Best Tips, you also get details of the learning points in each tip and the Run time for each video lesson.  

Excel Tip List

Excel DVD Tips

You can download: 

If you would like to receive this detailed content listing as an Excel File, please click on this link to contact me directly. I will email you the file as an attachment. 

There are over 5 1/2 hours of focused video instruction on my DVD-ROM.The files are Shockwave Flash Files and they play in any Internet Browser (Internet Explorer, FireFox,Safari, etc.) 

As a bonus, the DVD-ROM contains the 50 Excel Workbooks that I use in the video lessons. Use these workbooks to recreate the lessons that you learn on the video. 

I invite you to visit my online store ( http://shop.thecompanyrocks.com) to purchase the DVD-ROM for “The 50 Best Tips for Excel 2007.” Click here to start shopping using my secure shopping cart. I guarantee your complete satisfaction with my products. If you are not satisfied, I will refund your purchase price with no questions asked. 

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

Sincerely, 

Danny Rocks

Link to this post!
Filed Under: Excel Training, Online Store for The Company Rocks Tagged: , ,
December 17, 2010 2 Comments

Using the Built-in Excel Data Form to Edit, Enter and View Records

Data Form in Excel

Data Form

Excel has a built-in Data Form which is very useful for viewing all of the fields (up to 32 of them) associated with a single record. When you have lots of “Fields” in your “Data Set” and you do not want to do a lot of horizontal scrolling to view or edit the values in the cells, use this Data Form.

There is a limit of 32 fields when using Excel’s built-in Data Form.

Customize the Quick Access Toolbar

However, it does not appear as a Ribbon Command in Excel 2007. To use the Data Form add the Form Command to your Quick Access Toolbar (QAT) – I show you how to do this in the video.

Personally, I do not recommend the Excel Data Form for entering new records in your data set. I like to add Data Validation drop-down lists to ensure data integrity and this feature is not available in the Data Form. However, you can use Criteria to quickly find records for viewing and editing in the form.

Filtering Data with the Data Form

I give you a brief introduction to some of the new filtering options available in Excel 2007. I will cover this in greater detail in another lesson.

Subscribe to My Video Podcast on iTunes

I invite you to subscribe to my free video podcast, “Danny Rocks Tips and Timesavers” on iTunes. Click here to go directly to my iTunes podcast. I have had over 25,000 downloads since I began my podcast in June of 2010.

Watch this video in High Definition

Click here to go to my DannyRocksExcels YouTube Channel.

Watch Video Now

Master Excel in Minutes Video Training Resources

I have created a series of extended length Excel Video Training Resources. I invite you to visit my secure online shopping site to: Learn how to “Master Excel in Minutes – Not Months!”

Using the Built-in Excel Data Form to Edit, Enter and View Records [ 7:58 ] Play Now | Play in Popup | Download (1296)
Link to this post!
Filed Under: Excel Forms, Excel Training, New in Excel 2007, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: , , , , ,
December 10, 2010 Leave a Comment

Learn How to Use Date Functions in Excel

Excel has a group of functions that help you to calculate dates. For example, the number of days between two dates or the date on which an invoice will be payable.

This is the second in my series of lessons where I demonstrate how to enter and calculate dates and times in Excel. Here is the link to part one.

Excel Tip List

Excel DVD Tips

The date functions that I demonstrate on this video are:

  • TODAY()
  • NOW()
  • DATE()
  • DATEVALUE()
  • WORKDAY()
  • NETWORKDAYS()
  • EOMONTH()

The last three date functions require you to activate the Analysis ToolPak Add-in. You will also learn some valuable and time saving keyboard shortcuts.

You can watch this video in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

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

Learn How to Use Date Functions in Excel [ 8:54 ] Play Now | Play in Popup | Download (823)
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , , ,
December 9, 2010 2 Comments

How to Work with Dates in Excel

The majority of the questions that I receive from viewers center on how to use and format DATES & TIMES in Excel. So, I have decided to create a series of lessons to show you how to work with dates and times in Excel.  This is part one in a continuing series of free Excel Video Training Lessons.

50 Best Tips for Excel 2007

Excel Tips

Learn How to Work with Dates

  • Use a Keyboard Shortcut to enter the current date in a cell – to “time stamp” the date.
  • Use the TODAY() Function to enter a “dynamic” date function in a cell – so that your date always reflects the current date.
  • Distinguish between how Excel “stores” a date – as a serial number – and how Excel formats a date – how you choose to format a date.
  • Tell if a date was entered correctly – as a number – or incorrectly – as “text” by seeing how it is aligned in the cell (right or left).
  • See why a fraction could be recognized as a date – and how to correct this “got’cha” step!
  • How to create a series of dates in your spreadsheet.

In my experience, the majority of “Excel problems” are based around incorrect date and time entries. So, whether you are new to Excel or a veteran who gets frustrated with Time and Date calculations, this lesson is for you.

If nothing else, you will pick up some great Keyboard Shortcuts in this lesson!

Follow this link to 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 Work with Dates in Excel [ 8:55 ] Play Now | Play in Popup | Download (796)
Link to this post!
Filed Under: Excel Basics, Excel Date and Time Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
December 7, 2010 2 Comments

Use the SUMPRODUCT Function to Total Lists Using Multiple Criteria

In Excel 2007 and Excel 2010 you can use the new SUMIFS Function to total a list using multiple criteria. This is a great new feature!

But … what if you are using Excel 2003 or earlier? Or, if you collaborate with colleagues and clients who use older versions of Excel – how can you use multiple criteria to select the records that you want to Sum? You can use the SUMPRODUCT Function as I demonstrate in this video lesson.

The 50 Best Tips

The 50 Best Tips Series

Key Concepts

  • Create – and use – Named Cell References for Ranges / Arrays in your formulas
  • Use the Function Arguments Dialog Box – Use the keyboard shortcut Ctrl + A to bring this up
  • Use the F3 Keyboard Shortcut to bring up the Named Ranges List
  • In this lesson, use () to enclose each Array Argument
  • In this lesson, use * to substitute for the , to separate each Array in the formula (Edit this in the Formula Bar)

There are usually several ways to solve any problem in Excel. This is a good procedure to learn – regardless of the version of Excel that you use.

Click here to view the 1st part of this series on how to use the SUMPRODUCT Function in Excel.

You can view this Excel Video in Hig Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels by clicking here.

I cover the SUMIF and SUMIFS Functions in greater detail on my DVD, “The 50 Best Tips for Excel 2007.” Click here to start shopping using my secure shopping cart.

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

Using the SUMPRODUCT Function to Total a List Using Multiple Criteria [ 7:47 ] Play Now | Play in Popup | Download (1121)
Link to this post!
Filed Under: Excel Functions, Excel Training, Podcast Tips & Timesavers, SUMIF and SUMIFS Functions Tagged: , , , , , , , , ,
December 6, 2010 2 Comments

How to Use the SUMPRODUCT Function in Excel

In my experience, most Excel users are unfamiliar with the SUMPRODUCT Function. So let me give you two reasons to watch this short (5 minutes, 22 seconds) video lesson to learn how – any why – to use SUMPRODUCT:

  1. The SUMPRODUCT Function will speed up your calculations in Excel – get results with one step. With SUMPRODUCT() you can avoid several intermediary calculations.
  2. With SUMPRODUCT, you may be able to trim the size of your Excel worksheet. You may not need as many columns (fields) as you think to get the result that you need!

In this lesson, I show you how to calculate the Total Value of the Inventory (for a fictional company) with one formula!

50 Best Tips for Excel 2007

Excel Tips

Key Points Coverered

While the SUM Function “Adds Up” the values in the Arguments, the PRODUCT Function “Multiplies” the values in its Arguments.

  • Using “Arrays” in SUMPRODUCTS. In Excel 2007, you can use up to 255 Arrays! The key point is that all Arrays must be the same size and shape. For example, if the 1st arry in the Arguments is 15 rows in one vertical column, then all of the subsequent Arrays must be 15 rows in one vertical column.
  • I find that Creating – and Using – Names Cell Ranges for the Arrays will speed up writing the SUMPRODUCT formula.

I have decided to present the SUMPRODUCT Function in two lessons – so click on the sequel to see other uses for this function!

Click here to watch the 2nd video lesson on how to use the SUMPRODUCT Function in Excel.

You can watch this Excel Training Video Lesson in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

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

How to Use the SUMPRODUCT Function in Excel [ 5:21 ] Play Now | Play in Popup | Download (954)
Link to this post!
Filed Under: Excel Functions, Excel Training, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: , , , , ,
December 2, 2010 2 Comments

How to Speed Up Data Entry on Forms By Using Named Cell Ranges

You can speed up data entry on an Excel form by creating – and using – named cell ranges. This also improves data accuracy, because the data entry clerk only has to hit the “Enter” key after each imput; the curosor automatically moves to the next cell in the named range that you applied to your form!

There is one “got’cha” step in this process. As you will see in this video, it is important to observe the position of the “Active Cell” in your selection!

The 50 Best Tips

The 50 Best Tips Series

Key Points Covered

  • Hold down the “Ctrl” key as you click on the cells in your non-contiguous range.
  • Use labels and cell shading to identify the cells that require data input.
  • When selecting your non-contiguous cells, choose the cell that you want to be the “Active Cell” last.
  • You can enter a formula or value into all of the cells in a range with “Ctrl+Enter.”

Try this out  on a form that you use at work. Remember to create a “name” for your data input cells. “Named cell ranges” are saved when you create a copy of your workbook – so this technique can be easily duplicated by saving the file with a new name.

Click here to 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 speed up data entry on forms with named cell ranges [ 4:58 ] Play Now | Play in Popup | Download (882)
Link to this post!
Filed Under: Excel Forms, Excel Training, Names for Cells and Ranges, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: , , , ,
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: , , , , ,
« Previous Page
Next Page »