Home Archives for New in Excel 2007
September 13, 2012 2 Comments

How to Use a Check Box to “Toggle” Conditional Formatting On or Off in Excel

Sort by Color in Excel

Sort by Color in Excel

In my previous Excel Tutorial – “Conditional Formatting to Dynamically Format Dates” – I demonstrated how to  apply Conditional  Formatting based upon the number of days have passed since last contact with a customer. Now I will extend what you just learned.

Three Techniques in this Video:

  1. How to extend  Conditional Formatting Rules to additional cell ranges.
  2. How to use the “Sort by Colors” feature introduced in Excel 2007.
  3. How to add a Check Box Form Control that will “toggle” the Conditional Formatting On or Off.

Paste Special “Live Preview”

I demonstrate a great new feature introduced in Excel 2010, “Live Preview” for Paste Special. You will use Paste Special – Formats to extend the rules for Conditional Formatting to a new range of Cells

Sort by Color

Beginning with Excel 2007, you can now sort fields based upon the color of a font or a cell background. I show how this works.

The Check Box Form Control

The “key concept” to understand when using Excel’s Form Controls is the “Cell Link.” With a Check Box Control, the value in the Cell Link is TRUE when the box is “checked” while the value is FALSE when it is Unchecked. We can use this information to create a “Conditional Format based upon a Formula.”

Conditional Formatting Rules

When you base Conditional Formatting upon a Formula, the result of that formula must return TRUE in order apply this formatting. Setting the background cell color to “White” effectively “hides” the previous background cell colors.

Shop for The 50 Best Tips for Excel 2007

Shop for The 50 Best Tips for Excel 2007

Resources Offered for Sale

I encourage you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to preview the many training resources that I offer you. Check out my latest Excel Training Videos:

Watch this Video in High Definition

Click on this link to watch this 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 a Check Box to "Toggle" Conditional Formatting On or Off in Excel [ 8:29 ] Play Now | Play in Popup | Download (3175)
Link to this post!
Filed Under: Conditional Formatting, Excel Forms, Excel Training, New in Excel 2007, New in Excel 2010, Podcast Tips & Timesavers Tagged: , , , , , , , , , ,
September 11, 2012 1 Comment

How to Dynamically Color-Code Dates with Conditional Formatting in Excel

Conditional Formatting for Dates

Conditional Formatting for Dates

One of my viewers wants to learn how to “dynamically” color-code cells that contain dates. She is monitoring the frequency of contact with her clients and she wants Excel to automatically apply:

  • One background color to cells where it has been less than 60 days since last contact
  • A different color when the time frame is between 60 and 90 days
  • A third color when it has been over 90 days since the last contact with a client

Here, I demonstrate how she can do this using Conditional Formatting Rules. However, this will only work if you are using Excel 2007 or Excel 2010.

Understanding Dynamic Date Functions

In order to make this visualization “dynamic,” I make references to cells that contain the =TODAY() Function. This is a “volatile” function that updates automatically based upon the date in your computer system’s clock. I use this to classify the last date of contact with her clients.

Improvements in Conditional Formatting

Beginning with Excel 2007, you can now apply multiple rules that use formulas. Remember that when you use a formula, it must evaluate as “TRUE” in order to apply the formatting that you specify.

Formulas Used to Apply Conditional Formatting

Conditional Formatting Rules

Rules for Conditional Formatting

To make it easier for you to practice this skill, I have posted this table that details the formulas that I used for each of the three rules in my video tutorial.Note that with the middle condition, I use the =AND() Function. All logical tests must answer TRUE for the AND() Function to return TRUE.

It will take a little bit of practice for you to become comfortable with these formulas. As a Best Practice, I recommend that you create a table of formulas so that you can see how a “tweak” in your formula can turn a FALSE answer into a TRUE answer.

Related Video

Follow this link to Part Two in this Series. I demonstrate how to “toggle” Conditional Formatting On or Off by using a Check Box Control in Excel.

Watch this Video in High Definition

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

Get the Best Tips and Training for Excel

Click on this link to learn more about my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.” This link will take you to my secure online shopping website –  http://shop.thecompanyrocks.com

Subscribe to my Video Podcast on iTunes

Here is the link to go to my free Video Podcast, “Danny Rocks Tips and Timesavers” on iTunes.

 

 

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 Conditional Formatting to Color-Code Dates in Excel [ 8:49 ] Play Now | Play in Popup | Download (3143)
Link to this post!
Filed Under: Conditional Formatting, Date Functions and Formats in Excel, Excel Date and Time Functions, Excel Formulas, Excel Training, New in Excel 2007, New in Excel 2010, Podcast Tips & Timesavers Tagged: , , , , , , ,
August 23, 2012 1 Comment

How to Highlight Different Values in a Row with Excel’s Go To Special

Go To Special Dialog Box

Go To Special – Row Differences

I have published several video tutorials on the topic of Highlighting and Deleting Duplicate Records in Excel. However, a viewer recently asked me how to highlight cells that contain different values in the same Row. This is not as “crazy” as it first seems.

Scenario for this Tutorial

I am trying to determine accurate inventory counts for my product line. I have the count that the computer shows. I also have records from three different auditors who have performed manual counts. I need to easily highlight the cells where the inventory count “is off.” That is where there is a discrepancy between what the computer shows and what a manual count shows.

Go To Special Dialog Box

The commands in the Go To Special Dialog Box are some of my favorite tools. In my experience, I have found that most Excel users have never explored this dialog box.  I think that will change after you watch this video tutorial. For the Row Differences tool, you first select the range of cells that contain your data – beginning with the left-most column. The Row Differences will use this left-most column as the “baseline” and highlight each cell in that row that contains a value that is different from this baseline.

Sorting & Filtering by Color

Beginning with Excel 2007, you can Sort by Color and you can also Filter by Color. After the cells were highlighted with the Row Differences command, I applied a background cell color to each highlighted cell.

Use one of my favorite Keyboard Shortcuts, Ctrl + Shift + L, when working with Filters in Excel 2007 or Excel 2010.

Watch this Excel Tutorial in High Definition

You can watch this tutorial in High Definition on my YouTube Channel – DannyRocksExcels.  Click on this link to watch this video now.

Video Tutorials for Highlighting Duplicate Records

I mentioned that I have published many Excel Tutorials on the Topic of Highlighting & Removing Duplicate Records. Here are links to my tutorials:

Shop for Excel Training Resources

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

Watch My Video Podcasts on iTunes

Click on this link to watch my “Danny Rocks Tips and Timesavers” video podcast on iTunes. – It is free of charge!

 Note: The original Podcast Video did not have the correct video settings. This video has now been updated.

 

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 Highlight Different Values in a Row in Excel [ 4:34 ] Play Now | Play in Popup | Download (2169)
Link to this post!
Filed Under: Excel Training, Keyboard Shortcuts, New in Excel 2007, New in Excel 2010, Podcast Tips & Timesavers Tagged: , , , , , , ,
June 27, 2012 1 Comment

How to Add Summary Information for Excel Workbook Properties

Almost everyone has experienced the frustration of being unable to locate an Excel Workbook on their computer. You can’t remember the name or the location of the Workbook. You waste valuable time searching in vain.

Tags for Document Search

Tags for Document Search

One way to minimize this frustration is to add Excel Workbook Properties that Summarize the content and purpose of the document. By adding Tags, Keywords, Client Names or Project Titles in the Properties Summary you make it easier for your computer to Index and Find your documents.

Fortunately, beginning with Microsoft Office 2007, it is a lot easier to add these properties to an Excel Workbook. In this video tutorial, I show you how to do this.

Displaying Recent Documents

Did you know that you can display up to 50 Recent Documents? Even better, you can “Pin” important documents so that they remain on the list. This is a valuable tool when you need to access important files, for example, once a month! I show you how to do this on the video.

Shop for Additional Resources

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – where you can purchase all of the training materials that I have developed.

Watch Video in High Definition

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

Watch this Video 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: Excel Training, New in Excel 2007, New in Excel 2010, Workbooks and Worksheets in Excel Tagged: , , , , , , , , ,
March 19, 2012 Leave a Comment

Use Conditional Formatting in Excel to Highlight Cell Values that Match Criteria

Conditional Formatting in Excel

Conditional Formatting in Excel

Conditional Formatting has been available for many years in Excel. However, beginning with Excel 2007, Conditional Formatting got a major boost in its ease-of use and new visualization tools.

In this tutorial, I demonstrate how to use Conditional Formatting to highlight – via formatting – the cells in a range that anser “TRUE” to a set of criteria. For example, is the cell value “Above Average” or is it Greater than the value in a Target Cell.

How Does Conditional Formatting Work?

Conditional Formatting is “dynamic.” If you update the values – or formulas that generate cell values” the cell that receive the Conditional Formatting may change – based upon the criteria that you set as “the condition.”

Versions of Excel Covered

For this lesson, I demonstrate Conditional Formatting in BOTH Excel 2010 and Excel 2003.

Learn More About Conditional Formatting

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to see all of the resources that I offer. This includes my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.”

Watch Tutorial in High Definition

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

View 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: Conditional Formatting, Excel Training, New in Excel 2007, YouTube Videos by Danny Rocks Tagged: , ,
December 4, 2011 Leave a Comment

How to SUM Values in One Field Based on Criteria from Multiple Fields in Excel

SUMIFS and DSUM Functions

SUMIFS and DSUM Functions

The new SUMIFS() Function was introduced in Excel 2007. With SUMIFS, you can sum the values in one field based up criteria that comes from multiple fields. This is a very valuable Function.

SUMIFS Function

The key to understanding SUMIFS, is that you “pair” a criteria range with the criteria for that range. As you watch my tutorial, the importance of this concept will become clear to you.

DSUM Function

If you are using – or need to create workbooks that are compatible with – older versions of Excel – e.g. Excel 2003, you can use the DSUM Function to achieve the same results. The DSUM belongs to the Database Functions set in Excel.

Use Named Cell Ranges in Formulas

I highly recommend that you learn how to create – and then use – named cell references in your Excel Formulas and Functions. In this tutorial, I show you how to do this. Once you have created a named cell reference, you can use the F3 Keyboard Shortcut to show a dialog box that lists all of the named Ranges that you can post into your formulas. This will save you time and help to ensure accuracy in your formulas – especially when you cop a formula to another location.

Bonus: Create Drop-down Menu with Data Validation

When using Multiple Criteria, I like to be able to select my criteria values from a drop-down list. In this lesson, I demonstrate how to do this using Data Validation in Excel.

Learn More Excel Tips

I invite you to visit my new, secure, online shopping website – http://shop.thecompanyrocks.com. Here, you can learn more about the tips on my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.”

 

Watch Video in High Definition

Here is the link to view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

YouTube Video

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: Data Validation, Excel Functions, Excel Training, Names for Cells and Ranges, New in Excel 2007, New in Excel 2010, SUMIF and SUMIFS Functions Tagged: , , , , , , , ,
October 27, 2011 1 Comment

Structured Formula References in Excel 2007 and Excel 2010 – Part 2

Compare Structered References Excel 2007 v 2010

Compare Structured References 2007 v 2010

This is the second and concluding part of this series where I demonstrate how to use Structured Formula References when referring to Excel Tables.

Key Points Covered

  • The difference between Structured Formula References in Excel 2007 and Excel 2010
  • How to Edit Structured Formula References
  • How to Write Structured Formulas using the new Formula AutoComplete drop-down menu

 

Watch My Video Tutorial Now

 

Watch Video in High Definition on YouTube

Click on this link to watch this Video in High Definition on my YouTube Channel – DannyRocksExcels

Go to Part 1 in this Series of Video Tutorials

Follow this link to go to Part 1 in this Series of Tutorials about using Structured Formula References in Excel Tables.

Visit My New Secure Online Store

I have just published 6 New Products and opened a new, revised, secure online shopping website. Take a look at: http://shop.thecompanyrocks.com

 

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: Excel Data Tables and Lists, Excel Formulas, Excel Training, New in Excel 2007, New in Excel 2010, YouTube Videos by Danny Rocks Tagged: , , , , , ,
September 15, 2011 1 Comment

How to Identify and Then Delete Duplicate Records in Excel

Identify & Delete Duplicates

Identify & Delete Duplicates

It is almost inevitable that, over time, your Excel data set will contain duplicate records. This can be costly – if the duplicate records are in your mailing list. And it can also result in errors when you count and calculate data.

Three Methods to Identify and Delete Duplicate Records

In this Excel Tutorial, I demonstrate three approaches to identify duplicate records in a data set:

  1. Circle Invalid Data – This tool works in coordination with Data Validation.
  2. Remove Duplicates – This powerful command was introduced in Excel 2007. It does not “preview” the records to be deleted, so my advice is to use this command with a copy of your data.
  3. Conditional Formatting – Greatly improved for Excel 2007 and Excel 2010. Now, you have a menu choice to apply conditional formatting to either Duplicate or Unique Records. If you are still using Excel 2003 or earlier, I show you how to write the formula needed to use Conditional Formatting to highlight duplicate records.

Watch The Tutorial in High Definition on YouTube

Follow this link to view this 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
Link to this post!
Filed Under: Conditional Formatting, COUNTIF and SUMIF Functions, Data Validation, Excel Training, New in Excel 2007, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
August 8, 2011 Leave a Comment

How to Customize the Quick Access Toolbar in Excel 2007

Quick Access Toolbar Office 2007

QUick Access Toolbar in Office 2007

Learning how to Customize the Quick Access Toolbar (QAT) in Excel 2007 or Excel 2010 is a great way to ease the “learning curve” when you upgrade versions.

In my experience, I have found that most people do not even realize that there is a Toolbar in Excel 2007, let alone that you can move it to a different location and to customize it!

What I Cover in this Video Tutorial

Here is what you will learn from this YouTube Video Tutorial:

  • To move the QAT to Show it Below the Ribbon (recommended!)
  • To add the most popular command buttons one at a time
  • To open the Customize Quick Access Toolbar Dialog Box
  • To find commands to add – including Command Not in the Ribbon!
  • To organize the commands on your QAT
  • To create a customized QAT for “specific workbooks”  – great for special projects!
  • To quickly add individual command buttons with a right-click of the mours
  • To add entire command button groups to the QAT with a right-click of the mouse – (recommended!)

Since this video is a little longer than usual, I am making it available exclusively on my YouTube Channel.

Watch Video in High Definition Mode

Follow this Link to view this tutorial in High Definition on my YouTube Channel – DannyRocksExcels

“The 50 Best Tips for Excel 2007” DVD-ROM

Purchase my DVD-ROM, “The 50 Best Tips for Excel 2007” – on sale now at http://shop.thecompanyrocks.com for only $29.97 USD!

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: Excel Training, New in Excel 2007, Workbooks and Worksheets in Excel, YouTube Videos by Danny Rocks Tagged: , , , , , ,
August 6, 2011 Leave a Comment

Tips to Help You Remain Productive When Upgrading to Excel 2007

Beginning Level Videos for Excel

Videos at the Beginning Level - 50 Best Tips for Excel

I enjoy participating in several discussion and sharing groups on LinkedIn. In one group, the question was raised, “The Ribbon – Love it or Leave it?” Many group members wrote to say that they just cannot figure out the new user interface – The Ribbon. Others expressed frustration that they were much less productive in their work as they tried to make the transition to either Excel 2007 or Excel 2010. Some said that they simply gave up and returned to using Excel 2003.

Certainly, I felt frustrated and lost when I first began to use Excel 2007. So, let me share my best tips for quickly adjusting to and mastering the commands and tools in Excel 2007 – and Excel 2010.

Topics Covered in this Video

  • The Office Button in Excel 2007 to open, save and print Excel workbooks and to change Excel Options
  • 7 Standard Tabs on the Ribbon
  • Groups of related Command Buttons
  • Launchers to open up Dialog Boxes
  • Command Buttons with Menus
  • Showing the Quick Access Toolbar below the Ribbon
  • Customizing the Quick Access Toolbar
  • Working with the Mini Toolbar

Watch Video in High Definition on YouTube

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

Purchase My DVD-ROM, “The 50 Best Tips for Excel 2007”

Here is the link to learn more about my best-selling DVD-ROM. You can purchase this from my online store using a secure shopping cart. On sale now for only $29.97 USD.

 

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: Excel Training, MS Excel 2007 DVD, New in Excel 2007, Tips and Time Savers, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
Next Page »