Archives for July 2008

I have been busy adding content to my new blog

I have not been on vacation. I have been very busy launching my new blog – Danny Rocks Excels! This blog offers “Tips and Time-Savers” when using MS Excel for Windows.

When you have a few minutes, I invite you to visit. There are now 15 short video lessons posted on the site.  My goal is to create a 3 to 4 minute video for each concept. So far so good!

I have asked several colleagues to review the site and give me feedback. They write to tell me that they enjoy viewing a “short and sweet” lesson on a single concept.

After you visit the site, let me know what you think. I welcome your feedback.

 

 

Use Paste Special, Add to combine data from 2 columns

Here are the steps to follow for this lesson:

  1. Copy the data in the new column.
  2. Select the top cell in the original column and click Paste Special, Add
  3. To preserve any special formatting in the original column take this extra step:
  4. Click Paste Special, Values, Add

Find the video lesson that you want – Index to 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.

How to identify and convert text values to numbers

Here are the steps to follow in this lesson:

  1. Very that all values are actually numbers e.g. use the =COUNT() and =ISTEXT() functions.
  2. To convert a series a text values to numbers, first select a blank cell and copy it to the clipboard.
  3. Highlight your text values and choose Edit, Paste Special. In the Paste Special Dialog Box choose ADD.
  4. All text values are now converted into numbers that can be summed, etc.

Find the video lesson that you want – Index to 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.

Name a cell and use it in a formula

Here are the steps to follow in this lesson:

  1. A “named cell” creates an Absolute cell reference.
  2. One way to name a cell is to select the cell and type the name you want in the “Name Box” (Do not use any spaces) and hit Enter
  3. To apply the Named Cell in an existing formula, highlight the part of the formula and select Insert, Name, Paste (the named cell) and click OK.
  4. Consider creating a “Named Constant” for numbers you use frequently in a formula e.g. Sales Tax or Inflation Rate.
  5. To create a Named Constant choose Insert, Name, Define and in the “Refers to” box type (for example) = 0.0715
  6. Follow the same procedure to use the Constant in your formula: Insert, Name, Paste (named Constant) and click OK.
  7. Edit the Named Constant if e.g. the Sales Tax Rate changes. All formulas that use the Named Constant (in this workbook) will update automatically!

Click Here to Find the Excel Training Video in My Index

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.

Use absolute cell reference in % of total formula

Here are the steps to follow in this lesson:

  1. Make the right side of your formula – the reference to the “Total” – an Absolute Cell Reference.
  2. Change =C2/G1 to =C2/$G$1 by using the F4 Shortcut key.
  3. To reveal all Formulas in a worksheet, use CTRL + ~ (This is a toggle.)

Find the video lesson that you want – Index to 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.

Write a formula to total cumulative sales

Here are the steps to follow for this lesson:

  1. CHALLENGE: Write 1 formula in Column “C” to allow the range of values in Column “B” to expand by 1 cell each time the formula is copied down in Column “C.”
  2. In cell C2, the formula is =SUM(B$2:B2)
  3. Notice the $ in the cell reference on the left side of the range = SUM(B$2…
  4. The $ creates a “Mixed Cell” reference in the formula. This allows the range of cells in our formula to expand when we copy the formula down in Column “C.”
  5. Learn how to clear the “Green Triangle” Smart Tags in a range of cells.

Find the video lesson that you want – Index to 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.

Learn the powerful SUMIF Function

Here are the steps to follow in this lesson:

  1. Type =sumif( and then click the FUNCTION WIZARD to enter the ARGUMENTS.
  2. Enter the range of data that you wish to SUM as the 1st Argument.
  3. Identify the Criteria to evaluate e.g. “>500″ – (enclose the Argument inside ” “) as the 2nd Argument.
  4. The 3rd Argument is OPTIONAL. You can leave this empty if you want to SUM the same range of data from your 1st Argument.

NEW! Download the Practice File from this Lesson:

Find the video lesson that you want – Index to 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.

Related Videos

Produce Subtotals for your data

Here are the steps to follow in this lesson:

  1. Sort your data according to the field (column) by which you wish to group your records.
  2. From the DATA menu select SUBTOTALS.
  3. In the Subtotals dialog box confirm the correct field and select the FUNCTION you wish to use. There are 11 possible functions (including SUM, AVERAGE, MIN, MAX)
  4. Check to FIELDS that you want to be subtotaled and then click OK.
  5. Excel automatically OUTLINES your data. Click the Expand and Collapse buttons on the outline to see more or less detail. Click the Outline Levels to see different views of your subtotals.
  6. Explore the other options in the Subtotal dialog box (First choose Data – Subtotals.)
  7. You can remove ALL Subtotals in the Subtotal dialog box.

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

Find the video lesson that you want – Index to 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.

Related Video Lessons

Words per minute – How fast do you speak?

Our normal rate of speech – the number of words per minute (wpm) that we use – is between 120 and 150 wpm. When we are nervous or excited, we speak faster and our wpm count increases. The converse applies when we are tired or when we speak more deliberately – we use fewer wpm.

My normal wpm count is between 135 and 160. What is yours? If you don’t know, how can you find out? And why does it matter? Here’s why:

  1. So that your audience will understand what you are saying
  2. So that you can gauge how long or how short your talk will be – before you actually give your talk!

Speaking -live – is much more difficult than writing, in my opinion. In order to be an effective speaker you must be aware of how fast or how slow you speak. It is your responsibility to make sure that your audience understands what you are saying. Your audience must grasp your ideas as you present them – in real time. The first time!

When you write, you can go back and edit your thoughts. Rewrite your sentences before you publish your article. When you speak, you do not have that luxury. You can not re-do your spoken remarks when they are delivered live.

And when you speak, your voice must come alive. You voice must grab and keep your audiences attention. You need good vocal energy. You need to project authority and authenticity if you want your audience to stay tuned-in to your presentation. And this usually increases your wpm count. So you must guard against racing through your presentation.

Recording these video tutorials has been very instructive – to me! I get a chance to hear myself speak. I get a chance to see how long it takes me to present an idea. And… I have asked a few colleagues to critique these early recordings. As a result, I have received some excellent feedback. And I am making some changes.

Ideally, I want to keep the length of these video lessons short. Three minutes is ideal. Five minutes is too long. I want my audience to stay tuned-in. I don’t want them to tune-out before I make my point. When speaking, I must get to the point quickly without speaking too quickly. I must be aware of how many words per minute I use. And not waste time – nitehr my time nor my audience’s.

In preparing for today’s video lesson on my MS Excel blog, I wrote out my script. I read the script aloud several times and revised it. I timed myself as I spoke the script. I did a word count. And at 702 words divided by my 135 wpm speaking rate, this talk was coming in at 5 minutes and 20 seconds.

Too long! Too much content! What could I trim? What should I eliminate? I need to cut out at least 1 minute of talking in order to keep my audience tuned-in.

After several revisions I was ready to “go live” and record this lesson. I did not want to read my script. And I did not have too. I had rehearsed it sufficiently. I felt comfortable and confident.

The result – a 4 minute and 10 second video. I was happy. And I think that my audience will find this video informative and engaging.

Now mind you, I know this topic – in Excel – very well. I have taught it many times in individual and classroom settings. But this was the first time I was recording the lesson.

The lesson for me – I can always improve. So can you. But it requires practice – before you speak!

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

Related Articles:

Learn to AutoFilter a data list

In this lesson you will learn how to use Excel’s AutoFilter to reveal select data records that match your filter criteria.

Here are the steps to follow in this lesson:

1) Organize your list into Columns / Fields that contain a single type of data e.g. Territory

2) Apply a different format to identify the Top Row of your list as the Column Headers

3) Select one cell in the list then choose DATA – FILTER – AUTOFILTER

4) Use the “drop-down” arrow for a field to select your filter

5) The Top 10 Filter is a generic term. e.g. You can filter to Bottom 15items / percent

6) Custom filters allow you to see records that are <, <=, etc.

Find the video lesson that you want – Index to 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.

Related Videos

Related Video Lessons