Excel Training | The Company Rocks

Danny December 11, 2012 Leave a Comment

How to Prevent Empty Cells from Displaying in a Pivot Table

In response to one of my viewers, I demonstrate how to prevent “empty cells” from displaying in a Pivot Table Report. In my experience, I have found that clients and colleagues who are unfamiliar with Pivot Tables tend to “doubt the numbers” when they see these “empty cells.”

What You Will Learn

In my video, I demonstrate:

How to use Pivot Table Options to show a Zero instead of “blanks.”
How to “drill down” to reveal the details for any Subtotal.
How to use a great Keyboard Shortcut to apply and clear Filters.

Learn More About Pivot Tables

Pivot Tables Tutorial

I have published a 90 minute video tutorial focused exclusively on Pivot Tables and Pivot Charts. The package includes:

9 Video Tutorials – total of 90 minutes of video instruction.
Step-by-Step Instructional Manual as a PDF for you to use as you watch the videos.
The Excel Worksheets that I use in each video lesson so that you can practice your new skills.

I make this video training resource available both on DVD-ROM and as a Downloadable Product. Versions are published for Excel 2010, Excel 2007, and Excel 2003.

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to learn more about all of my training resources.

Watch Video in High Definition

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

Watch Video Now

Share and Enjoy:

Filed Under: Excel Training, Pivot Tables Tagged: Drill Down for Details in a Pivot Table, Excel Keyboard Shortcuts, Excel Pivot Tables, Excel Video Training, Filter Excel Data, Video lessons for Excel

Danny November 21, 2012 Leave a Comment

How to Use Multiple Sorting Options in Excel

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

Share and Enjoy:

How to Use Multiple Sorting Options in Excel [ 7:14 ] Play Now | Play in Popup | Download (7603)

Filed Under: Conditional Formatting, Essential Excel Skills, Excel Training Tagged: Conditional formatting in Excel, Custom Lists, Excel Video Training, MS Excel Training, Multiple Levels of Sorting in Excel, Sort Across Columns, Sort from Left to Right to Rearrance Column Order, Sort Horizontally, Sort on Cell icon, Sort using a custom list, Sort using an Excel Custom List, Video lessons for Excel

Danny November 4, 2012 Leave a Comment

How to Use a Data Validation Formula When Creating a Budget

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

Share and Enjoy:

Learn How to Use Data Validation When Creating a Budget [ 10:43 ] Play Now | Play in Popup | Download (6520)

Filed Under: Data Validation, Essential Excel Skills, Excel Formulas, Excel Training Tagged: Absolute cell reference, Data Validation, Excel Keyboard Shortcuts, Excel Video Training, Video lessons for Excel

Danny October 27, 2012 1 Comment

Learn How Excel Stores and Formats Dates

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

Share and Enjoy:

Podcast Video [ 11:03 ] Play Now | Play in Popup | Download (4288)

Filed Under: Essential Excel Skills, Excel Basics, Excel Training Tagged: Clear Formatting from Cells in Excel, Custom Formatting for Dates in Excel, Dates as a Serial Number in Excel, Excel Video Training, How Excel Stores Dates, Text-to-Columns Data Tool, Video lessons for Excel

Danny 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

Share and Enjoy:

How to Create and Use Nammed Cell Ranges in Excel [ 8:13 ] Play Now | Play in Popup | Download (4615)

Filed Under: Essential Excel Skills, Excel Formulas, Excel Functions, Excel Training, Names for Cells and Ranges Tagged: Create Names from Selection in Excel, Create Names in Excel, Define Names in Excel, Excel Functions, Excel Keyboard Shortcuts, Excel Video Training, Named Cell in a formula, Named Cells and Ranges, Paste Names Dialog Box, Paste Names in Formulas, Video lessons for Excel

Danny October 18, 2012 Leave a Comment

Essential Excel Skill #2: Selecting Cells Efficiently

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

Share and Enjoy:

Selecting Cell Rages Efficiently in Excel - an Essential Skill [ 6:15 ] Play Now | Play in Popup | Download (7208)

Filed Under: Essential Excel Skills, Excel Basics, Excel Training, Keyboard Shortcuts, Training Tagged: Excel Keyboard Shortcuts, Excel Video Training, Go To Special dialog box, Selecting a Cell Range in Excel, Selecting Cells in Excel, Video lessons for Excel

Danny October 14, 2012 1 Comment

Survey Results – Which Version of Excel Are You Using

Which Version of Excel Are You Using?

This week, I surveyed the Excel Users in my mailing list. I wanted to find out:

Which version of Excel they are currently using at work?
Are they using the same verion of Excel at Home as they use at work?
Do thy plan to upgrade to a later version of Excel in the Next 12 Months?

Survey Results

A solid majority of the people who opened my survey, completed the survey.

Which version of Excel are you currently using at work?

54% responded that they are using Excel 2010 at work, while 36% are using Excel 2007. !0% are using either Excel 2003 or another version.

Are you using the same version of Excel at Home as well as at work?

68% responded “Yes,” whil 32% responded “No.”

Do you plan to upgrade to a newer version of Excel during the next 12 months?

23% responded “Yes,” while 23% responded “No.” 9% were not sure.

My Observations

I am pleased that almost all of the Excel users who completed the survey are using Excel 2007 or higher. This was very important information for me to gather. I really needed to determine if there was a market for me to continue to publish my video tutorials for Excel 2003 – the answer is “No!

The results for my second question – “Are you using the same version of Excel at Work and at Home?” – were about what I expected.

As to plans to Upgrade to a later version of Excel – We will have to wait and see what Excel 2013 has to offer and how many users will be purchasing new computers with Excel 2013 already installed.

Take My Survey Online

If you wish, click on the link below to take this survey yourself. No personal information will be collected and you not be added to my mailing list.

Click on this link to take my survey – Which Version of Excel Are You Using?

Thanks to everyone who responded!

What are your opinions and observations? Add your comments below!

Share and Enjoy:

Filed Under: Excel Training, Survey Results Tagged: Excel Versions, Microsoft Excel Versions, MS Excel Training, Survey of Excel Users, Tips and Time Savers

Danny 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:

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

Share and Enjoy:

Filed Under: Essential Excel Skills, Excel Basics, Excel Training, Keyboard Shortcuts, YouTube Videos by Danny Rocks Tagged: AutoFill Options, Excel Keyboard Shortcuts, Excel Video Training, Fill Series in Excel, Keyboard Shortcuts in Excel, Learn to use AutoFill in Excel, Video lessons for Excel

Danny 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 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:

Excel Pivot Tables to Summarize, Analyze, and Present Your Data
- Available for Excel 2010, Excel 2007, and Excel 2003
- Available on DVD-ROM and also for Instant Downloading
Nine Essential Skills for Excel
- Also available for Excel 2007
- Available on DVD-ROM or for Instant Downloading

Share and Enjoy:

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: Excel Pivot Tables, Excel Video Training, Tips and Time Savers, Video lessons for Excel

Danny October 4, 2012 Leave a Comment

How to Correct #N/A Error Messages for Excel’s VLOOKUP Function

Fix the #N/A Error Message

A colleague of mine – who is an experienced Excel user – cannot figure out why he is receiving those ugly #N/A Error Messages when he is using the VLOOKUP Function in Excel.

After a quick look at his workbook, I spotted the problem!

Use the TRIM() Function to Remove Extra Spaces

It turns out, that the data source – housed on the company’s Main Frame Computer – is using “Fixed Width” Fields. So, while only 7 characters are visible, I used the LEN() Function to determine that the field was actually storing 12 characters.

The #N/A Error Message for VLOOKUP()

The #N/A Error Message appears when a Match cannot be found for the Lookup Value in the Left-most Column of the Table_Array when using VLOOKUP(). In this case, my viewer was trying to match a lookup value that contains 7 characters to a value in a field formatted to contain 12 characters. This is what produced the #N/A Error!

Paste Special Values

The final step to correct this problem is to use Copy – Paste Special – Values to replace the Formulas with the values of those formulas.

Essential Excel Skills

9 Essential Skills for Excel 2010 Video Tutorial

In this video, I cover three Essential Excel Skills:

Essential Functions – VLOOKUP()
Text Functions – LEN() and TRIM()
Paste Special Options

I cover each of these skills – and more – in greater detail on my new 4-Hour Video Training Resource, “Nine Essential Skills for Excel.”” Click on the links below to learn more about:

Watch Video in High Definition

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

Share and Enjoy:

How to Correct #N/A Error Messages for Excel's VLOOKUP Function [ 6:02 ] Play Now | Play in Popup | Download (7514)

Filed Under: Excel Basics, Excel Functions, Excel Training, Import Data into Excel, Lookup Functions in Excel, Text Functions Tagged: #N/A Error Message in Excel, Exact Match for VLOOKUP, Fixed Width Fields in Excel, Formula Errors, Len() Function, Paste Special Values, Trim() Function, VLOOKUP Function

Danny Rocks “Training You to Excel”

Follow Danny on Social Media

What You Will Learn

Learn More About Pivot Tables

Watch Video in High Definition

Watch Video Now

Sorting Options on Video Tutorial

Shop for Excel Training Resources

Watch My Video in High Definition

Use a Formula in Data Validation

A Formula that Evaluates to TRUE

Video Training Resources

Watch This Tutorial in High Definition

Dates Stored as Serial Number

Concepts Covered in this Tutorial

Visit My Secure Online Shopping Website

Watch Video in High Definition

9 Essential Excel Skills

Secure Online Shopping at The Company Rocks

Watch My Tutorial in High Definition

Selecting Cells Efficiently in Excel

Selecting Cells by Type of Content

Secure Online Shopping Website

Watch My Video in High Definition

Survey Results

Which version of Excel are you currently using at work?

Are you using the same version of Excel at Home as well as at work?

Do you plan to upgrade to a newer version of Excel during the next 12 months?

My Observations

Take My Survey Online

Why Nine Essential Excel Skills?

Essential Excel Skill #1

Entering Time and Date Values

Keyboard Shortcuts for Current Date & Time

Speed Up Data Entry with AutoFill Options

Fill Series Dialog Box

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

Learn More About My Video Training Resources

Watch My Excel Tutorial in High Definition

Watch Video Tutorial Now

“Nine Essential Skills for Excel 2010”

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

Learn More About My Video Training Resources

Use the TRIM() Function to Remove Extra Spaces

The #N/A Error Message for VLOOKUP()

Paste Special Values

Essential Excel Skills

Watch Video in High Definition

Locate My Video Tutorials by Category

Seach My Website with Keywords

Podcast Feeds

Blogroll

Blogs for Business Writing

Blogs for Excel

Blogs for Presentations

Register or Log In

Tags