Home Archives for Video lessons for Excel
November 21, 2012 Leave a Comment

How to Use Multiple Sorting Options in Excel

Sort Using Cell Icons

Sort based on Cell icons in Excel

Beginning with Excel 2007, the Sorting Commands gained several new features and became much more powerful. For example, you are no longer limited to three levels of sorting. You can sort using as many levels as you need. In addition, you can now sort by Cell or Font Colors and also sort using the new Cell Icons.

While I was training one of my clients last week, several questions about sorting came up. So, in this video I will demonstrate several of the new features as well as showing you how to take advantage of many sorting options that have been around for some time. For example, using a Horizontal Sort to quickly rearrange the order of your columns!

Sorting Options on Video Tutorial

Here are the sorting options that I demonstrate on my video:

  • Sort on Values using a Custom List
    • First, I use a built-in custom list (Months of the Year).
    • Next, I demonstrate how to create a custom list that you can use with AutoFill and also with custom sorting.
  •  Sort data based upon Cell Icon
    • Cell Icons were introduced in Excel 2007 as part of the major upgrade to Conditional Formatting.
  • Sort Horizontally to quickly rearrange the order of your columns (fields) in your data set.
    • This feature has been available for many years. However, in my experience, very few people are aware of its existence.
    • Once you  learn this technique, you can stop the tedious task process of Insert Column, Cut Column, Paste Column, Delete Column, etc.

Shop for Excel Training Resources

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com –   where I offer a range of video training resources for all of the Microsoft Office Programs.

Watch My Video in High Definition

Follow this link to watch my video in High Definition on my YouTube Channel – DannyRocksExcels

How to Use Multiple Sorting Options in Excel [ 7:14 ] Play Now | Play in Popup | Download (7603)
Link to this post!
Filed Under: Conditional Formatting, Essential Excel Skills, Excel Training Tagged: , , , , , , , , , , ,
November 4, 2012 Leave a Comment

How to Use a Data Validation Formula When Creating a Budget

Use Formulas in Data Validation

Use a Formula in Data Validation

Recently, one of my viewers wanted to know which formula he could use to prevent an end-user from imputing a value that would exceed his budget. I created this video tutorial to demonstrate my response.

Use a Formula in Data Validation

I have identified Data Validation as one of the “Nine Essential Skills in Excel.” Here is one example – Using a Formula in Data Validation to prevent an input entry from exceeding a set budget amount

A Formula that Evaluates to TRUE

The “key” to understanding how use Formulas in Data Validation – When the formula answer is TRUE, the entry is accepted; When the answer to the formula is FALSE, the Error Message that you create prevents an invalid entry.

Remember, that in Data Validation, only the STOP style will prevent an invalid entry.!

Video Training Resources

9 Essential Skills for Excel 2010

9 Essential Skills for Excel 2010 Video Tutorial

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to preview the many video training resources that I offer you.

Watch This Tutorial in High Definition

Follow this link to watch my Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Learn How to Use Data Validation When Creating a Budget [ 10:43 ] Play Now | Play in Popup | Download (6520)
Link to this post!
Filed Under: Data Validation, Essential Excel Skills, Excel Formulas, Excel Training Tagged: , , , ,
October 27, 2012 1 Comment

Learn How Excel Stores and Formats Dates

Formatting Options for Dates in Excel

Multiple Formatting Options for Dates in Excel

In my experience, a high percentage of Excel users run into problems when working with Dates – and Times! In fact, I estimate that, during the past two weeks, I received 25 questions from viewers – they were asking for my help when working with Dates in their Excel workbooks.

Here, in this tutorial, I gathered together the most common problems that my viewers asked me help them to solve.. Gaining a firm understanding of how Excel Stores and Formats Dates is an Essential Skill that you must master!

Dates Stored as Serial Number

Dates are stored as a serial number in Excel. For example, if you type the number 1 into a blank cell and then change the formatting to a Short Date, you will see 1/1/1900 displayed. This is the date on which Excel began to keep track of time.  Dates that are entered correctly align to the right side of the cell.

Concepts Covered in this Tutorial

  • Use the =TODAY() Function – this date will automatically update.
  • Use the Keyboard Shortcut Ctrl + ; (semi-colon) to “Date Stamp” a cell – this is a static date.
  • Change the Formatting of a Date to e”General” – this reveals the actual serial number that Excel stores for the Date.
  • Apply Custom Date Codes to format cells that contain Dates.
  • Learn why it is essential that you always enter years using four digit codes – to avoid “the Century Break.”
  • Learn how to clear all formatting from cells before pasting or entering data. in them
  • Use the Text-to-Columns Wizard to convert “unfriendly” Date Text Labels to “usable” Numeric Date Values .
  • Tips for copying and pasting a range of cells formatted as USA Short Dates to a customized “European” Date Format.

Visit My Secure Online Shopping Website

I invite you to stop by my secure online shopping website – http://shop.thecompanyrocks.com – to take a look at all of th many training resources that I offer to you.

Watch Video in High Definition

Follow this link to watch my Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Podcast Video [ 11:03 ] Play Now | Play in Popup | Download (4288)
Link to this post!
Filed Under: Essential Excel Skills, Excel Basics, Excel Training Tagged: , , , , , ,
October 21, 2012 1 Comment

Essential Excel Skill #3: Creating and Using Named Cell Ranges in Excel

Essential Skill #3 - Using Named Cell Ranges

Essential Excel Skill 33 – Using Named Cell Ranges

I believe that there are two main reasons to create and use Named Cell Ranges in Excel”

  • They make great Navigational Bookmarks – to quickly move to a specific location in your workbook
  • Using Named Cell Ranges in Excel Formulas make the formula:
    • Easier to Write
    • Easier to Explain – to others
    • Easier to Understand – when you need to edit it or copy it.

9 Essential Excel Skills

My latest Excel Video Training Resource: “9 Essential Excel Skills,” contains 4 hours of focused video training. There are a total of 25 individual video tutorials in the resource.

Here, in this lesson, I demonstrate several of the tips and techniques that I present in Essential Skill # 3. I show you how easy it is to create and use Named Cell Ranges in Excel Formulas and Functions.

Secure Online Shopping at The Company Rocks

I invite you to visit my secure, online shopping website – http://shop.thecompanyrocks.com – to explore all of the training resources that I offer you.

Watch My Tutorial in High Definition

Follow this link to watch my Excel tutorial in High Definition on my YouTube Channel – DannyRocksExcels

How to Create and Use Nammed Cell Ranges in Excel [ 8:13 ] Play Now | Play in Popup | Download (4615)
Link to this post!
Filed Under: Essential Excel Skills, Excel Formulas, Excel Functions, Excel Training, Names for Cells and Ranges Tagged: , , , , , , , , , ,
October 18, 2012 Leave a Comment

Essential Excel Skill #2: Selecting Cells Efficiently

Essential Excel Skill #2

Essential Excel Skill #2

Learning how to select cells – and ranges of cells – efficiently,  is an Essential Excel Skill. Here, I demonstrate two tips from my extended length video training resource, “Nine Essential Excel Skills.”

On my DVD-ROM, I devote 13:30 minutes and two separate video tutorials to this skill.  The entire DVD-ROM is nearly four hours in length and it contains 25 individual video tutorials.

Selecting Cells Efficiently in Excel

For a contiguous range of cells, you can use these methods:

  • Ctrl + A to select all of the cells in the current range.
  • Ctrl + Shift + Directional Arrow to select all of the cells that contain data moving in that direction.
  • Hold down the Shift Key while you tap the directional arrow keys.
  • Press the F8 Key to enter “Extend Mode” and tap the directional arrow keys.

Selecting Cells by Type of Content

One of the most valuable – if underused – tools in Excel is the Go To Special Dialog Box. In this lesson, I demonstrate how to use the Go To Special commands to, first, select all of the cells that contain Numeric Formulas and then all of the cells that contain numeric constants.

As a Best Practice, I always use the Go To Special Dialog Box whenever a  client sends me an Excel Workbook. In my opinion, this is the easiest way to “inspect” an Excel worksheet. I most frequently use Go To Special to find cells that contain:

  • Types of Formulas (Numbers, Text, Logical, Etc.)
  • Types of Constants (Numbers, Text, etc.)
  • Data Validation
  • Conditional Formatting
  • Blanks

Secure Online Shopping Website

I invite you to visit my secure shopping site – http://shop.thecompanyrocks.com/ – to learn more about the many training resources that I offer you.

Watch My Video in High Definition

Follow this link to watch my Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Selecting Cell Rages Efficiently in Excel - an Essential Skill [ 6:15 ] Play Now | Play in Popup | Download (7208)
Link to this post!
Filed Under: Essential Excel Skills, Excel Basics, Excel Training, Keyboard Shortcuts, Training Tagged: , , , , ,
October 12, 2012 1 Comment

Essential Excel Skill #1: Entering Data Efficiently

My latest Excel Video Training Resource is “Nine Essential Excel Skills.” I have published versions for both Excel 2010 and Excel 2007. You can purchase them as either a DVD-ROM that I ship to you; or in a version that you can Download immediately.

Why Nine Essential Excel Skills?

As I created this Excel Video Training Resource, I had two distinct customers in mind:

  1. Everyday Excel users who know that they could get more out of Excel if only they had a structured approach to training that used “real life” business examples.”
  2. Experienced Excel users who want to prepare to take the Microsoft Office Specialist (MOS) 2010 or 2007 Certification Examinations for Excel.

 

Essential Skill #1 Video Lessons

Essential Skill #1 Video Lessons

Essential Excel Skill #1

I have identified Essential Excel Skill # 1 as “Entering Data Efficiently.” In the world of business there is a common acronym – GIGO – which means, “Garbage In delivers Garbage Out.” In order to produce accurate and meaningful reports in Excel, you MUST begin with data that is entered correctly.

Entering Time and Date Values

In my experience, many Excel users incorrectly enter Time values and Date values. Times and Dates are “Numeric Values.” When Time and Date Values are entered correctly, they align to the right-side of the cell. Once they are entered correctly, you can change their formatting to suit your purpose.

Keyboard Shortcuts for Current Date & Time

  • Ctrl + ; (semi-colon) enters the Current Date into the cell. This creates a “Date Stamp.”
  • Ctrl + Shift + : (colon) enters the Current Time into the cell. This creates a “Time Stamp.”
AutoFill Weekdays Only

AutoFill Options – Weekdays Only

Speed Up Data Entry with AutoFill Options

Use AutoFill to quickly – and accurately – fill in the Months of the Year and the Days of the week. I demonstrate – in detail – how to take advantage of the many AutoFill Options that are available. For example, to AutoFill Weekdays Only! I am confident that you will gain many productivity tips from the video tutorials in this Skills chapter.

Fill Series Dialog Box

Many Excel users have never used the “little gem” that is the Fill Series Dialog Box. As you will learn in the video tutorial, you can quickly fill in a long series of numeric values by using the “Step Value” and “Stop Value” fields in this dialog box.

Download the Complete Step-by-Step Instruction Guide – for Free!

I am so confident that you will want to purchase my video training resource, that I am offering you the opportunity to download – for Free! – the 29 page Instruction Guide that is included in “Nine Essential Excel Skills.” Click on the link to begin to download either version as a PDF:

Learn More About My Video Training Resources

My “Master Excel in Minutes – Not Months” series of video training resources is growing quickly! Follow this link to see the resources that are available now!

Watch My Excel Tutorial in High Definition

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

Watch Video Tutorial Now

 

 

 

Link to this post!
Filed Under: Essential Excel Skills, Excel Basics, Excel Training, Keyboard Shortcuts, YouTube Videos by Danny Rocks Tagged: , , , , , ,
October 8, 2012 Leave a Comment

Two Glowing Reviews for my Excel Video Tutorials

Today, two of my customers posted glowing reviews for my Excel Video Tutorials. Thank you! Thank you! Thank you!

9 Essential Skills for Excel 2010

9 Essential Skills for Excel 2010 Video Tutorial

“Nine Essential Skills for Excel 2010”

“In my opinion the 9 Essential Skills for Excel 2010 , is an outstanding training video. It covers a lot of the required intermediate requirements. One thing that really stands out for me is the method of presentation. Danny always:

  • a) explains what he is going to do in simple language 
  • b)demonstrates what he has just explained
  • c)shows you where to find the icons on the ribbon
  • d)shows you the keyboard shortcuts to use(they actually appear on the video as they are used.

The video also had a good introduction to the value of using tables. Like all of Danny’s videos this should be a fantastic success.

– John J, Canada

Pivot Tables for Excel 2003

“Excel 2003 Pivot Tables to Summarize, Analyze, and Present Information”

“A mere few hours with your Excel 2003 course and I was able to
perform pivot table operations which had eluded me for over a year
using multiple other publications totaling nearly 1,500 pages: while those sources are terrific, each of us learns differently, your courses certainly fill that learning gap for me.

I deal with mostly with cumulative metrics, that is, incremental, week to week.

After two evenings of your course, I was able to build tables presenting the year, month, quarter & weekly totals of the multiple tools deployed, and, amazingly, display the incremental changes down to a week-to-week basis.

You had mentioned that you hoped I enjoyed the course, of course, I enjoyed the course – indeed, but after a year-plus struggle with several other sources, the *learning* from your course was the true reward!”

– Paul B., Colorado

Learn More About My Video Training Resources

I am confident that you will learn many valuable tips and techniques from my extended length Excel Video Training Resources.My Motto is:

“I work hard to make it easy for you to get the most out of Microsoft Excel”

Follow these links to learn more about my video training resources:

Link to this post!
Filed Under: Excel 2003 Video Lessons, Excel Basics, Excel Formulas, Excel Functions, Excel Training, MS Excel Training, Online Store for The Company Rocks, Pivot Tables, Video Training Courses Tagged: , , ,
October 2, 2012 Leave a Comment

The 10 Most Watched Excel YouTube Videos for September 2012

My YouTube Channel – DannyRocksExcels – had 141,586 individual views during the month of September 2012:

Here is a list – with Hyperlinks – to the 10 Most Watched Excel Videos

Top 10 YouTube Excel Videos

  1. Compare Two Excel Lists to Spot the Differences– 5,304 views in September
    1. Go to this Blog Post on my website
  2. How to Merge Multiple Excel Workbooks to a Master Budget – 4,915 views in September
    1. Go to this Blog Post on my website
  3. How to Use Advanced Filters in Excel– 4,660 views in September
    1. Go to this Blog Post on my website
  4. How to Add a Check Box Control to an Excel Form– 4,301 views in September
    1. Go to this Blog Post on my website
  5. Use the Built-in Data Form in Excel to View and Filter Records– 3,948 views in September
    1. Go to this Blog Post on my website
  6. How to Create a Summary Report from an Excel Table-3,932 views in September
    1. Go to this Blog Post on my website
  7. How to Create an Interactive Excel Pivot Chart– 3,696 views in September
    1. Go to this Blog Post on my website
  8. Consolidating Data from Multiple Excel Worksheets by Position– 3,618 views in September
    1. Go to this Blog Post on my website
  9. How to Use the Solver Tool in Excel– 3,532 views in September
    1. Go to this Blog Post on my website
  10. How to Use an Excel Data Table for “What-if” Analysis– 3,513 views in September
    1. Go to this Blog Post on my website

Thanks to my 4,500 Subscriber on YouTube!

I extend my heartfelt thanks to my many loyal viewers and subscribers to my Excel video tutorials. Your feedback and requests for solutions have formed the foundation for my Excel Training Videos.

9 Essential Excel 2010 Skills

9 Essential Excel 2010 Skills

Learn More About my Extended Length Excel Video Training Resources

I have created a series of Extended Length Video Training Resources in the category of “Master Excel in Minutes.” I invite you to follow this link to learn more about my new series of videos.

Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Excel Data Tables and Lists, Excel Forms, Excel Formulas, Excel Functions, Excel Training, Lookup Functions in Excel, Most Viewed Excel Videos, Pivot Tables, SUMIF and SUMIFS Functions, Workbooks and Worksheets in Excel, YouTube Videos by Danny Rocks Tagged: , , , , ,
September 30, 2012 1 Comment

How to Clear Cell Formatting In Excel

Numbers Stored as Text

Numbers Stored as Text

In my previous tutorial, I demonstrated how to clear all text formatting from all slides in a PowerPoint Presentation. Several viewers contacted me about publishing a tutorial that demonstrates how to clear formatting from ranges of cells in Excel.

Use the Clear Formatting Command

From my experience, I had found that a great many Excel users expect that formatting as well as contents are cleared when you press the Delete Key. This is not the case. The Delete Key only clears the contents of the cells in the selected range.

Use the Clear Formatting Command in the Editing Group on the Home Tab of the Ribbon. If you are using Excel 2003 or earlier, you will find this on the Edit Menu.

Numbers Stored as Text

As a Best Practice, I strongly recommend that you verify that the numeric values that you will be using in formulas are truly numbers and not numbers stored as text. This, unfortunately is a common case whenever you are working with data that has been imported from a main frame computer or other external sources. In this lesson, I demonstrate how to use Paste Special Operators to quickly convert numbers stored as text to true numeric values.

9 Essential Skills for Excel 2010

9 Essential Skills for Excel 2010 Video Tutorial

Nine Essential Skills for Excel

I cover, in-depth,  Entering and formatting data as well as using Paste Special Options on my new video resource, “Nine Essential Skills for Excel.”

  • Four hours of video training
  • 25 individual video tutorials
  • Step-by-Step Instructional Guide
  • Excel Practice Files included

Download It or Order My DVD-ROM

Watch My Tutorial in High Definition

Follow this link to watch my Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

How to Clear Formatting from Cells in Excel [ 6:03 ] Play Now | Play in Popup | Download (2881)
Link to this post!
Filed Under: Excel Basics, Excel Training, Format Cells in Excel, MS Excel Training, Podcast Tips & Timesavers Tagged: , , , , ,
September 24, 2012 1 Comment

How to Use Lookup Functions in Excel – Take a Free Lesson from My Latest Video Training Resource

Video Lesson - Lookup Functions in Excel 2007

Video Lesson – Lookup Functions in Excel 2007

I have just published the Excel 2007 version of my latest video training resource, “Nine Essential Excel Skills.” And, I want to offer you the opportunity to watch a complete episode. This is one of the 25 video tutorials included on my video training resource. The complete package contains almost four-hours of focused Excel 2007 Training.

Lookup Functions in Excel 2007

Watch this complete 11 minutes and 30 second lesson, as I demonstrate how to use both the VLOOKUP() and HLOOKUP() Functions in Excel. I demonstrate how to return an “exact” match as well as how to return an “approximate” match. I use “plain language” to describe how to use Lookup Functions.

Learn More About My Video Training Resources

Here are the links to the specific product information pages for my latest video training resources:

Download My Step-by-Step Instructional Guide – for Free!

You can download a PDF of my Step-by-Step Instructional Guide for the “9 Essential Excel 2007 Skills” video training resource. I am offering this for free so that you can see the scope and detail of the training that I offer on my 4-hour video. Click on this link to begin the downloading process for my free Instructional Guide.

Watch this Lesson in High Definition

Follow this link to watch my Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Watch Tutorial Now

 

Link to this post!
Filed Under: Excel Basics, Excel Functions, Excel Training, Lookup Functions in Excel, MS Excel 2007 DVD, Names for Cells and Ranges, Training Tagged: , , , , , , , , , ,
« Previous Page
Next Page »