Archives for April 2009

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

Be Careful with Calculated Items in Pivot Tables

Let me warn you – Calculated Items in Pivot Tables can produce incorrect results! Calculated Items can be useful – in special circumstances. However, don’t use a Calculated Item to create a Quarterly Summary. It is too easy to accidentally “double” your sales! And, this will not earn you any special bonus. In fact, it may harm your reputation as an Excel expert. So be very careful before you create a Calculated Item.

Review my Excel Training Video on Grouping Data to see the preferred way to produce a Quarterly Summary in a Pivot Table.

Here are the steps to follow in this lesson:

  1. You use a Formula to Calculate an Item from the values on one of the Fields in your Pivot Table.
  2. Select one value in the Field where you want to create the Calculated Field.
  3. In this lesson, I selected a Month and then from the Drop-Down Menu on the Toolbar I chose Formula – Calculated Item,
  4. I named the Calculated Item Q1 (1st Quarter) and for the Formula I clicked = January+February+March. Click Add and then click OK.
  5. As you can see, the new Q1 Item actually “doubled” the existing sales for January – March. That is not what you were looking for!
  6. To remove the Calculated Item, select it and then from the drop-down select Formulas – Calculated Item. Highlight Q1 and click Delete – OK.
  7. Review my video to see how to Group Data to produce the Quarterly Summary in the Pivot Table.

Search My Index of Excel Training Videos to find the topic you want to view

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

Group Data in Pivot Table for Quarterly Report

During a recent Excel training class, one student asked me if was possible to create a Quarterly Summary Report in a Pivot Table. Yes, it is quite easy to do this – as you will see in the video.

In the Data Set that we are using for this Pivot Table we have four territories and 12 months of sales for each territory over a one year period. The Months (Jan, Feb, etc.) are not actual dates (e.g.1/1/08,) they are Labels.

In an earlier video, I showed you how easy it is to create new fields for Months, Quarters & Years from a Date field. Because our months are Labels, we will GROUP the Months to create the 1st, 2nd, 3rd, and 4th Quarters in our Pivot Table.

Here are the steps to follow in this lesson:

  1. Create a Pivot Table using the default settings in the Pivot Table Wizard. Place the Months in the Row area, the territory in the Column area, and the Sales in the Data area.
  2. Select the Labels January, February, March. On the Pivot Table drop-down menu select Group & Show Detail – Group.
  3. Rename the generic label (Group1) to 1st Qtr.
  4. Follow the same sequence of steps to create Groups for the 2nd, 3rd, and 4th Quarters.
  5. Rename the new, generic field (Month2) Quarter.
  6. Use the Toolbar Commands to Hide & Show Detail as desired.

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

Search the Index of Excel Lessons to find my training video listed by topic

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

Related Videos

I Added a New PowerPoint Video Lesson

I am just getting back to adding more PowerPoint Video Lessons on my blog. Follow this link to view the video – “Tools To Control Text On Slides”

The key learning points in the lesson:

  • Enforce a style to limit the number of bullet points per slide.
  • Establish a style to maintain consistent ending punctuation (on or off) for bullet points.
  • Establish minimum font sizes for Slide Titles and bullet points.

Reduce “Visual Clutter” on your PowerPoint slides. Achieve the professional look that sets you apart from other presenters. Use the tools that PowerPoint provides to help improve Visual Clarity.

Tools To Control Text On Slides

I want you to reduce the “Visual Clutter” on your PowerPoint Slides. You are not trying to write a “ransom note,” you are attempting to create a professional image.

PowerPoint has a great set of tools that you can use to enforce the Visual Style options that you set. In this video lesson, I demonstrate how to set up the style guides and then how to use PowerPoint’s tools to make the changes that conform to your rules.

Here are the steps to follow for this lesson:

  1. Go to the Tools Men and choose Options and then click the Spelling & Style Tab.
  2. On the Spelling & Style Tab choose Style Options.
  3. You can make your stylistic choices on the “Case & End Punctuation” tab and on the “Visual Clarity” tab. When you have made your choices, click OK.
  4. Back in Normal View, when you click in a Text Place holder, look for the Yellow Light Bulb. Click on it to see your options to conform to the style settings that you set.
  5. Also, look for the AutoFit Options Smart Tag. Choose the best option to change your style.

Search my Index of PowerPoint Video Lessons for the topic of your choice.

News! My DVD, “The 50 Best Tips for PowerPoint 2007” is availabe for purchase. Visit my online store for details.

Related Videos

Three Frustrations When Calculating TIme in Excel

When I am training a class to use MS Excel, I always get at least one question about performing time calculations. I sense that the person asking the question is frustrated. When I ask, “How many of you are frustrated when you try to perform a calculation to total time periods in Excel?” nearly every hand is raised! And they are right; Excel’s time calculations can cause frustration.

In this video lesson, I will show you how to overcome three common frustrations that you may have when performing time calculations in Excel.

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

  1. Format the cell that contains the formula =End_Time – Start_Time) to the Custom Number Format h:mm
  2. I encourage you to use this formula instead of a simple formula: =IF(End_Time < Start_Time, End_Time + 1, End_Time)-Start_Time.
  3. This will eliminate any possible “Negative times” which result in a cell filled with ######### A Negative Time is frequently the result of “Time Periods that Span Midnight. e.g. Start @ 11:00 PM and End @ 3:00 AM
  4. TIP: Use this Formula =MOD(End_Time – Start_Time, 1) This is the MODULUS Function.
  5. When you calculate the total number of hours and the result exceeds 24 hours, use this Format [h]:mm

Search My Index of Excel Video Lessons

¬†“The 50 Best Tips for …” is now available at the online store for The Company Rocks

Related Videos