Home Archives for Essential Excel Skills
October 25, 2013 Leave a Comment

Working with Worksheets in Excel Workbooks – Part 1

I have been quite busy this year teaching several “continuing education” courses online for colleges here in the USA. One of the benefits – for me as the instructor – is that I get “real time” feedback from my students when they submit their assignments. I like this!

Objectives for Lesson - Working w Worksheets Part 1

Working with Excel Worksheets in Excel Workbooks

From some of my recent interactions with my students, I realize that I need to reinforce the concept of working with Excel Worksheets inside an Excel Workbook.

In this Excel Tutorial, I address these three concepts:

  1. 1) Working in “Group Mode” to place content and corrections in multiple Excel Worksheets.
  2. 2) The Advantages of using Color-Coding for each Excel Worksheet Tab.
  3. 3) Using the Keyboard Combinations of Ctrl + Page Up and Ctrl + Page Down to navigate – and “activate” individual Excel Worksheets inside an Excel Workbook.

Watch this Video in High Definition  on My YouTube Channel – DannyRocksExcels

Here is the link to watch this video on my YouTube Channel

Watch My Video Now in High Definition on YouTube

 

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Essential Excel Skills, Excel Training Tagged: , , , , ,
October 10, 2013 Leave a Comment

How to Copy and Paste Cell Formatting Only in Excel

For the past several months, I have been teaching MS Excel online for the Continuing Education Courses at several major colleges here in the USA. It has been a great experience!.

Splash Page for Excel with ExcelOne of my clients, VanderCook College of Music, has recently adopted Schoology for their Learning Management System (LMS). This is a robust framework for presenting course work and interacting with my students!

Now, when I assign my weekly assignments to my class, I can quickly spot the common “stumbling blocks” or questions that many student have.

While I was grading my Week #1 assignments, I found that many students were unfamiliar with Excel’s AutoFill Option for “Filling the Formatting Only” in a contiguous range of cells. So, as a start to a class discussion, I created this YouTube Excel Tutorial where I demonstrate three ways to “Paste the Formatting Only” from one location to multiple locations in an Excel Worksheet.

Before I presented my course work for Week #1, I surveyed my class and realized that the majority of students were either novice or “rusty” users of Excel. Nevertheless, I find that Excel users at all levels of experience will benefit from this @ 7 minute video tutorial.

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

Watch this Excel Video Tutorial in High Definition on my YouTube Channel – DannyRocksExcels!

Click below to watch this video now on YouTube!

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Essential Excel Skills, Excel Training, MS Excel Tips, MS Excel Training Tagged: , , , , , ,
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

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
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

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
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

 

 

 

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Essential Excel Skills, Excel Basics, Excel Training, Keyboard Shortcuts, YouTube Videos by Danny Rocks Tagged: , , , , , ,