Home 2011 Archives for January 2011

Archives for January 2011

January 30, 2011 3 Comments

How to Use Conditional Formatting to Compare Two Excel Lists

Clients and viewers frequently ask me to help them to compare two differeny lists in Excel. They want to find – or highlight – the values that are different in each list. For example, which customers appear in the 1st list but NOT in the 2nd list. So, I created this video lesson to demonstrate how to do this with Conditional Formatting.

In an earlier Excel Video Lesson, I demonstrated how to compare two Excel lists using either the MATCH() Function or the VLOOKUP() Function.

Conditional Formatting Rule

Conditional Formatting Rule

Use Conditional Formatting

I demonstrate how to use Conditional Formatting to Highlight the Cell Values that are different when you compare two Excel Lists. I will use a “New Rule with a Formula” that must return the answer TRUE, to trigger the special formatting.

In Conditional Formatting, you first establish a “condition” that can be answered as either TRUE or FALSE. Then, for those cells where the answer to the condition is TRUE. the special “cell formatting” that you chose will apply.

In this lesson we will be using this Formula: =COUNTIF(List 2, 1st cell in List 1) = 0.

Steps to follow:

  1. Select the cells that you want the Conditional Formatting to apply to- in our example List 1.
  2. On the Home Tab of the Ribbon, click the Conditional Formatting arrow and select New Rule.
  3. Select New Rule – “Use a Formula to determine which cells to format.”
  4. Enter the formula – e.g. =COUNTIF(Range, Criteria) = 0 where the “Range” is the list of values in List 2 (Absolute Reference) and the “Criteria” is the 1st cell reference in List 1 (Relative Reference).
  5. Choose the Format for the cells when the condition is met – the result is TRUE. In this example, I choose to “FILL” the cells with a Blue background color.

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

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

How to Use Conditional Formatting to Compare Two Excel Lists [ 8:33 ] Play Now | Play in Popup | Download (1495)
Link to this post!
Filed Under: Conditional Formatting, COUNTIF and SUMIF Functions, Excel Formulas, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , , , , , ,
January 28, 2011 Leave a Comment

Sessions by Danny Rocks at the 2011 NAMM Show in Anaheim, CA

Here are some links and comments about the many sessions that I presented and moderated at the recent NAMM Show in Anaheim, CA. NAMM is the Intermational Music Products Association. I am proud to consult for NAMM in the area of Professional Development.

NAMM Uiniversity

NAMM University

During the January 2011 show, I modederated the Friday Morning Breakfast Session, “Lessons Learned: How Doing Business Now Has Changed for the Better.”

Follow this link to get a synopsis of the session from Music Inc. Magazine.

Here is a link to Press Coverage of my session.

Also, I presented a session in The Idea Center at NAMM University on ” How to Establish a Successful Web Presence for Your Store.”

Here is a link to the description of this session.

Follow this link to see a picture of me presenting this topic in The Idea Center at NAMM University

Link to a Listing of the NAMM University Sessions at the Anaheim 2011 Show

Link to this post!
Filed Under: Business Communications, Change Management, Lessons Learned, NAMM Show, NAMM University, The Idea Center at the NAMM Show Tagged: , ,
January 26, 2011 2 Comments

How to Calculate Overtime and Standard Hours Worked on a Time Card in Excel

One of my viewers asked me to show him how to calculate the number of hours worked each day – separated by standard hours and by overtime hours. He, like many Excel users, is frustrated when performing calculations involving time. They create a standard formula, but the results are not what they were expecting. In most cases, this is because of the need to FORMAT the cells that contain calculations to accommodate Excel’s logic.

Excel Time Card

Excel Time Card

Time Functions and Formats

In this lesson, I demonstrate the following functions and formats:

  • The =TIME() Function requires three arguments (Hours, Minutes, Seconds). In this example, I use =TIME(8,0,0) in a cell to represent the Standard Hours Worked each day – i.e. 8 hours.
  • The CUSTOM Format h:mm to format the cells that contain the results of time calculations (Total Hours Worked each day, Regular and Overtime Hours Worked each day).
  • The CUSTOM Format [h]:mm to format the cells that contain the results of SUM() for the Total Hours, Regular and Overtime Hours Worked each WEEK – i.e. Hours that exceed 24 hours
  • The Formula =SUM(Overtime Hours Worked this week) * 24 to gives me the NUMBER equivalent – Remember to format this cell as a NUMBER – not as [h]:mm,  so that you can multiply it by the cell that contains the Hourly Rate that you pay for either Regular Hours or Overtime Hours.

How Time is Stored vs. How Time is Displayed

Remember to distinguish between what Excel Stores as a calculation and how Excel Displays the result of a calculation. This is especially important with TIME and DATE Calculations in Excel.

You can view this lesson in High Defintion, Full Screen Mode on my YouTube Channel – DannyRocksExcels – by clicking this link.

I invite you to subscribe to my free Video Podcast – “Danny Rocks Tips and Timesavers”- at the iTune Store by clicking here.

Vist my Online Shopping Site

My DVD-ROM, “The 50 Best Tips for Excel 2007” is on sale at my Online Bookstore – http://shop.thecompanyrocks.com –  Click here to open a secure shopping cart.

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

How to Calculate Overtime and Standard Hours Worked on a Time Card in Excel [ 10:09 ] Play Now | Play in Popup | Download (1102)
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
January 3, 2011 1 Comment

Get My New Popular Keyboard Shortcuts for Excel Chart – It’s Free When You Register

Click the link below to download my chart of Excel Keyboard Shortcuts as a PDF:
CR – Updated Chart of Popular Excel Keyboard Shortcuts
Excel Keyboard Shortcuts

Preview Excel Keyboard Shortcuts

 Hello and Welcome – to my new “Members Only” section of The Company Rocks website! 

I just published a new five-page Chart – “Popular Keyboard Shortcuts for Excel.” It is free for you to download as a PDF. 

This, along with additional content, will soon be inlcuded in my new “Exclusive Membership Content” area on my website. 

My Exclusive Membership Site is not quite ready to launch. So, I am offering this chart for you to download as a PDF from this post. 

To get this chart, click on this link –  

CR – Updated Chart of Popular Excel Keyboard Shortcuts 

New! Download My Free 50 Minute Video Recording Demonstrating Excel Keyboard Shortcuts

Click this link to learn how you can download my new “extended length” video recording. It is “free of charge!”

I am proud of the way my Excel Keyboard Shortcuts Chart turned out – and I think that you will get a lot of value out of this free resource. I organized the Keyboard Shortcuts by the following Tasks:

Excel Keyboard Shortcuts

  • Basic File Operations
  • Undo, Restore or Repeat Actions
  • Insert, Delete, and Copy Cells
  • Edit Data
  • Show / Hide Columns and Rows
  • Select Contiguous Cells
  • Navigate Within a Worksheet
  • Navigate Between Workbooks
  • Find, Replace, and Go To
  • Enter Data
  • Work with Cell Comments
  • Work with Names for Cells and Ranges
  • Work with Formulas and Functions
  • Work with Formula Precedent and Dependent Cells
  • Print Worksheet
  • Formatting Cells and Text
  • Work with Subtotals and Outlines
  • Work with Tables, Lists, and Filters
  • Work with Charts
  • Work with Excel Windows and Open Applications

So, as you can see, this is a comprehensive – but not exhaustive – list of the best Keyboard Shortcuts for Excel. I took care to ensure that these Keyboard Shortcuts will work for all versions of Excel from 2003 through version 2010. 

The 50 Best Tips

The 50 Best Tips Series

I have several video lessons that cover keyboard shortcuts on my DVD-ROM, “The 50 Best Tips for Excel 2007.” It is a best-seller and it is now on sale! Use Coupon SAVE-20-PCT at checkout and save 20% on any puchase at my online store. Click here to open a secure shopping cart. 

And let me know your thoughts on my Excel Keyboard Shortcut Chart. Do you have a favorite Keyboard Shortcut that you want to share with our readers? Add your comments below.

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

Link to this post!
Filed Under: Excel Training, Keyboard Shortcuts, Membership Content, Tips and Time Savers Tagged: , , , ,
January 2, 2011 1 Comment

How to Import Excel Data Into Access

This is the first in a series of lessons where I demonstrate how to integrate Excel data with an Access database. In this lesson you will learn how to:

  • Access Working with External Data

    Work with External Data

  • Import an Excel worksheet as a new Table in Access.
  • Append an Excel worksheet to the Table that we created with the Imported data.
  • Create a link between the Source Excel worksheet and an Access Table.
  • Refresh the link between Excel and Access.

First, on the Excel side of this process, make sure that you have clearly identified the Field Headers in the Top Row of your data set. If you have and cells that contain Formulas, convert the formulas to Cell Values.

Begin the Import Process

You initiate the Importing from inside Access. Go to the External Data tab on the Ribbon (in Access 2007) and choose Excel. Follow the steps in the Import Wizard. I recommend that you allow Access to create a Primary Key for the new table. This will allow you to set up “table relationships”  inside Access.

Append Excel Data to an Existing Access Table

Once you have at least one Access Table set up, you have another option when you Import Excel data – you can “Append” Excel data to the Access Table. Be sure that your “field headers” match each other. Once again, follow the steps in the Import Excel data Wizard.

Notice that with each of these Import Wizards you are given the option to save your Import definition. If you will be importing data from the same Excel source on a regular basis, this “Saved Definition” will save you a lot of time.

Link to Source Data

You can decide to establish a “link” to the source data – in this case an Excel worksheet. If you do, you will see a special “icon” next to your Access table that indicates that the table is linked to an Excel source. In this case, you can not make any changes to the tabel from inside Access. You make all changes – adding, editing and deleting records – from inside Excel.

With a link to the source data, remember to “refresh” the link to see the updated information.

Watch this Video Lesson – Embedded from YouTube

As an experiment, I am embedding this video as a YouTube Video. Click on the video and you will be watching it in High Definition, Full Screen mode on my YouTube Channel – DannyRocksExcels.

Link to this post!
Filed Under: Access Tips and Timesavers, Excel Training, Integrating MS Office Programs Tagged: , , , , , , , ,
January 1, 2011 Leave a Comment

The 10 Most Watched Videos at The Company Rocks During 2010

Reminder Note

Reminder Note

I like to look back to review the results of the past year. Which posts, which videos were the most popular, which videos were downloaded most often? As I look at this list of 10 videos, the clear winner – by category – is merging data from multiple worksheets. Three of the top 10 videos that were watch the most fall into this category!

Here is a listing of the 10 Video Lessons that were watched most frequently on my website – www.thecompanyrocks.com – during 2010:

1- Summarize Multiple Excel Worksheets – Consolidate by Position

2- Build an Accounts Receivable Aging Report

3- Use Pivot Tables to Summarize by Year, Quarter and Month

4- Simplify Data Lookups in Excel

5- Perform Break-Even Analysis with Excel’s Goal Seek Tool

6- How to Display Numbers During a PowerPoint Presentation

7- Make Excel Data Come Alive with Visualization Tools

8-  Excel 2003 Basics – Data Entry

9- How to Merge Multiple Excel Workbooks into a Master Budget

10- Consolidate Data from Multiple Excel Worksheets – Part 2 – by Category

If you enjoy these videos, you will enjoy my DVD-ROMs, “The 50 Best Tips for Excel 2007” and “The 50 Best Tips for PowerPoint 2007.” You can use my secure shopping cart to purchase them now.

I have reduced the purchase price of my Individual DVD-ROMs to $29.97 USD. You do not need a coupon to receive this special price. Simply, go to my online store – http://shop.thecompanyrocks.com

You can also watch my videos on iTunes. Click here to go to my Video Podcast, “Danny Rocks Tips and Timesavers” at the iTunes Store.

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

My most recent videos can be viewed in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

Link to this post!
Filed Under: Consolidate Excel Data, Excel Basics, Excel Financial Tools, Excel Training, Goal Seek in Excel, Lookup Functions in Excel, Most Viewed Excel Videos, Pivot Tables, Powerpoint Techniques Tagged: , , , , , , , ,