Home Archives for Named Cells and Ranges
April 9, 2009 Leave a Comment

Save Time and Maintain Consistency When Entering Data

This is every company’s nightmare: You have a list of Customer Names that are entered in multiple worksheets by multiple employees. The (nightmare) result – you have multiple misspellings of the Customer Names! This is GIGO (“Garbage In, Garbage Out”). This is a mess!

This is preventable!

In this Excel Training Video I demonstrate three tips to save you time and help you to maintain consistency when entering long text labels into your worksheets. The Tips:

  1. Use Data Validation – Choose from a List
  2. Use a Named Constant  – you type =”Shortcut” and get the Customer Name spelled out correctly
  3. Use MS Office’s AutoCorrect Option – Create your “Replace and Replace With” in Excel and you can use it in Word and PowerPoint

Here are the steps to follow in this lesson:

  1. Enter the list of Customer Names in a worksheet. Highlight the list and type a NAME for this range into the Name Box. Remember to begin your Name with a letter and you can not use a Space in the Name.
  2. Tip # 1 – Use Data Validation – Allow – List. You can watch my lesson on Data Validation.
  3. Tip # 2 – Create a Named Constant. You can watch my lesson on Named Cells, Ranges & Constants.
  4. Tip # 3 – Use MS Office’s AutoCorrect Option – Go to Tools – AutoCorrect Options in any MS Office Program. For the “Replace” entry, type in your “Shortcut.” I like to make my 1st character an”_” so that it is easy to find, edit or replace. For the “Replace with” type in the full Customer Name. Remember to ht the SPACEBAR when you use this in Word & PowerPoint!

It is easy to find the Excel Training Video you want to view – Search my Index of Excel Topics

New! Danny’s DVD Training Series, “The 50 Best Tips for …” is now available at the online store for The Company Rocks

Link to this post!
Filed Under: Excel Basics, Excel Training Tagged: , , , , , ,
February 28, 2009 4 Comments

Simplify Data Lookups in Excel

In this Excel Video Lesson I demonstrate a great tip that dramatically simplifies data lookups!

Yes, learning how to use the Lookup Functions (VLOOKUP, HLOOKUP, MATCH, INDEX) is important. But, there is an easier way to answer some simple questions about you data.

In this video, I show you how to use the combination of “Named Ranges” and the “Intersection Operator” – the SPACE – to return the value at the intersection of a specific column and specific row. This “tip” is guaranteed to save you time and simplify your data lookups!

Here are the steps to follow in this lesson:

  1. Create “Named Ranges” for your data set.
  2. Use the “Intersection Operator” – the SPACE – in your formula. e.g. =Region_2 Week_16
  3. It’s that simple!

NEW! Download the Excel file that I used in this video lesson:

simplify-data-lookups

Looking for the answer to your Excel Questions? Use my Index of Excel Topics to find the right video

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Related Video Lessons

Link to this post!
Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel Tagged: , , , ,
December 30, 2008 3 Comments

Use =INDIRECT() Function to Connect 2 Pick-From Lists in Excel

This video is in response to a viewer who wants to join two columns containing Data Validation “Pick-from” lists. She knew that there was a Formula that could perform this, but she didn’t know how to accomplish this.

I promised to create this Excel video lesson to show her how – using the =INDIRECT() Function.

Here are the steps to follow in this lesson:

  1. Create lists for each group you want to be able to select from. Division and then in Departments (Sales, Marketing, etc.)
  2. Name each of these lists. Use either the NAME BOX or “Insert – Name – Create” (Select the Label and the Values).
  3. For the 1st Column – Division – use “Data – Validation” and in the “Allow” drop-down, choose “List.”
  4. Use the F3 shortcut to bring up the Named Range Dialog Box. Select “Division.” Type in a message for the Screen Tip and click OK.
  5. For the 2nd Column – the one whose “Pick-from” list will be based on the Values from the 1st Column (Division), we start with “Data – Validation” and in “Allow” choose “List.”
  6. Here is where we use the =INDIRECT() Function in the “Refers To” box. Click to select the cell in the Same Row in the 1st Column. Note: Be sure to make the Cell Reference Relative so that the formula can be copied down the column.
  7. I created the Validations in the Top Cell in each Column. Use Copy, Paste Special, Validation to apply the “Pick-from” validation to the remaining cells.

This is not a “Perfect” solution. As you can see, when you change the value in the first drop-down list, the value in the second list remains in place. What does change, however, is the list of values now available in the second drop-down list – waiting for you to make your selection!

Looking for a specific Excel Video Lesson? – Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, Lookup Functions in Excel, Video Response to Viewers Tagged: , , , , ,
December 19, 2008 Leave a Comment

Navigate to Excel Worksheets with Hyperlinks

I encourage my clients to organize their work in Excel workbooks by creating additional worksheets. Each worksheet should contain discrete information relating to your Excel project – e.g. Monthly or Quarterly budgets; Regional sales, or information about specific product lines.

A best practice is to rename each worksheet to reflect the information it contains. I also encourage clients to add a “Tab Color” to each worksheet. This makes it easy to identify the “Active” worksheet.

Many of my clients use 20 or more worksheets in their workbooks and they ask my help in navigating to a specific worksheet or cell range.

One strategy that I share with them is to create Hyperlinks to go to a specific worksheet. Another strategy involves naming specific cells or ranges. Once you have “named” a cell or a range, simply click the drop down arrow next to the Name box; select the named range and Excel takes you directly to that “Name.”

These are great time-savers and they boost productivity. Watch this video to see these techniques in action:

Here are the steps to follow in this Excel Video Lesson:

  1. Click a worksheet and then use Ctrl+Page Down to activate the next worksheet to the Right. Ctrl+Page Up activates the next worksheet to the Left.
  2. Create a new worksheet and give it a name like “Dashboard.” Type in text like “Link to XYZ Worksheet.”
  3. Ctrl+K opens the Create Hyperlink Dialog Box. Fist choose “Place in this Document.” Then select the name of the worksheet and click OK.
  4. The easiest way to “Name” a cell or a region is to highlight it and then go to the “Name Box” to type in a name. Names must begin with a letter and not use spaces. Click ENTER to register the name.
  5. Use the drop=down arrow next to the Name Box to find and go to a named cell or range. You can also create a hyperlink to a named cell or range.

Looking for a specific Excel Video? Click here for the Index to all Excel Video Lessons

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged: , , , , ,
« Previous Page