Home Archives for Excel Training
October 5, 2008 Leave a Comment

Generate Random Numbers for Excel Spreadsheets

When I create the examples for this podcast I frequently use the =RANDBETWEEN() function to generate random numbers. Then, I usually copy the formulas and use Paste Special to convert them into values.

Recently, I watched Bill Jelen. Mr. Excel demonstrate an absolutely amazing Excel Trick – use your Right-mouse button to move a selected block of cells over the the right. And then, without releasing the mouse button, move the block of cells back into place. A menu pops up and asks if you want to “Copy here as Values.” Try it! It is amazing! What a time-saver!

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

  1. Use the =RANDBETWEEN() Function to generate random numbers in your Excel spreadsheet. The function takes two arguments – a Top Number and a Bottom Number (both of your choosing.)
  2. The RANDBETWEEN Function is part of the Analysis ToolPak Add-IN. Activate it by choosing Tools – Add-Ins- Analysis ToolPak.
  3. Select the block of cells before you write the formula and then use Ctrl+Enter to populate all of the cells in your selected range with the formula.
  4. To convert the formulas to values, use Copy, Paste Special Values.
  5. OR… try this incredible trick (courtesy of Bill Jelen, Mr. Excel: Select the block of cells. With the RIGHT-Mouse Button, move the Border of the cell range over the the right and then back into place. Then, when you release the mouse a menu pops up. Choose, “Copy as Values Here.” It is amazining!

Find the Video Lesson that you want. Index to all Excel Topics

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

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 Functions, Excel Training, Tips and Time Savers Tagged: , , , , , ,
September 30, 2008 Leave a Comment

How to Solve Four Frustrations When Calculating Time in Excel

Have you ever been frustrated when you:

  1. Entered a “Time Value” in a cell only to find that Excel did not recognize it as a “time value?”
  2. Wrote a formula to calculate the elapsed time between “start and finish” only to find that Excel returned a “date” (e.g. 1/15/2008) and not a number?
  3. Wrote a formula to calculate the hours worked for your “Graveyard Shift” (those whose work hours cross Midnight) only to get a cell full of ########?
  4. Used the AutoSum function to calculate the total hours worked in a week only have Excel return a “silly” total like 1.7915?

Well, join the club! Most of my clients have experienced these four frustrations when they try to calculate “time values” in Excel. In this short video lesson, I will demonstrate how to solve each of these four frustrations.

I’d like to receive your feedback on this video lesson. Have you ever encountered this frustation? Did my explanation help you? What additional topics would you like me to cover?

You can email me – danny@thecompanyrocks.com

Here are the steps to follow in this video lesson:

  • Excel stores Time values as decimal numbers. Midnight is 0.00; 6:00 AM is 0.25; Noon is 0.5
  • Be careful when you enter “time values” into a cell. 12:00PM is considered “text.” You need to include a SPACE before the PM e.g. 12:00 PM in order to tell Excel that this is a “time value.”
  • Use this Keyboard Shortcut Ctrl+Shift+~ (Tilde) to see how Excel stores a “time value” in a cell.
  • When calculating “elapsed time”, use a “Custom Format” for  the cell with the Formula E.g. [h]:mm
  • When you calculate “elapsed time” for cells that “cross midnight” you will get ###### as your result unless you use a formula like =MOD((End_Time-Start_Time), 1)
  • When you total “time values” that will exceed 24 hours (E.g. Hours worked in a week) format the cell containing the formula with a “Custom Format” e.g.[h]:mm

Find the Excel Video Lesson that you want – Index to all Excel Topics

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

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: Everyday Calculations in Excel, Excel Date and Time Functions, Excel Training Tagged: , , , , ,
September 27, 2008 Leave a Comment

Trust Your Sources but Verify Your Data’s Accuracy

You may be familiar with the expression, “Trust but Verify!” President Reagan used this phrase when referring to the Soviet Union’s agreement to disarm their missiles. It is also a good approach to follow when you receive Excel data from someone else:

  1. Never open up a data file unless you trust the source.
  2. Never make a decision on the data until you verify the accuracy of the formulas and the structure of the spreadsheet.

In this Excel Video Lesson, I demonstrate how to use the GoTo Special Dialog box to verify the accuracy of your data.

Here are the steps to follow in this lesson:

  1. Choose Edit – GoTo – Special – Formulas to highlight all of the cells containing formulas in the worksheet.
  2. The Keyboard Shortcuts for “GoTo” are Ctrl+G or the F5 Key.
  3. In the “Special” dialog box, you can also choose “Constants.” This may make it easier to spot the cells that are “hard-coded” with a constant value when they should contain a formula.
  4. You can narrow your selection to “Text,” “Logical,” Numeric,” etc. formulas.
  5. Use the Ctrl+~ (Tilde) Shortcut to show the actual formulas in your worksheet.

NEW! Download the Practice File Used in This Excel Training Video

Find the Excel Video Lesson that you want – Index to all Excel Topics

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

Related Videos

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 Formulas, Excel Functions, Excel Training Tagged: , , , , , , , ,
September 26, 2008 15 Comments

Excel’s QuickSum Calculator – Get Results Without Writing a Function

MS Excel 2003 has a great feature – The QuickSum Calculator – but you have to know where to find it. It is on the Status Bar – that area at the lower right of your screen. Simply select at least two data cells. They can be contiguous or non-contiguous. But remember where to look for the QuickSum Calculator  – it is on the Status Bar.

Want to change the function? It simple! Just right-click on the name of the Function. You can choose Average, Count, Count Numbers, Min or Max – Excel’s most common Functions.

This is a great way to perform an Ad-hoc Query. I recommend that you use this for spreadsheets with Subtotals. Watch this short video lesson to see how this works – and how you can save time!

Find the Excel Video Lesson that you want – Index of all Excel Topics

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

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 Functions, Excel Training, Tips and Time Savers Tagged: , , , , ,
September 23, 2008 2 Comments

The NASCAR Approach to Saving Time in MS Excel

This morning, while I was teaching a class to use MS Excel 2003, a young man in the class called out, “I get it!”

I replied, “That’s great, Ian. What did you get?”

“It’s NASCAR!”

“NASCAR?” I replied – somewhat puzzled.

He explained. “In NASCAR Racing, the pit crews save a second here and a quarter of a second there. That’s how they win the race.”

So, there you go – “It’s NASCAR!” Saving a few seconds here and a few seconds there is how you win the business race. Watch this video to see how to save time when creating multiple formulas.

Here are the steps to follow in this video:

  1. To AutoSum BOTH the Columns AND the Rows with one-click of the mouse:
  2. Select the range of cells containing the values that you want to total PLUS the blank cells immediately to the RIGHT and immediately BELOW.
  3. Click the AutoSum ICON. Amaze your friends.
  4. If you like Keyboard Shortcuts. Instead of clicking the AutoSum Icon, use Alt+=

Find the Excel Video Lesson that you want – Index to all Excel Topics

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

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, Tips and Time Savers Tagged: , , , , , ,
September 21, 2008 3 Comments

Calculate Data in Excel and Present it in PowerPoint

You can’t beat MS Excel when it comes to organizing, calculating & graphing data. However, when it comes to presenting data – especially to a large gathering of people –  then MS PowerPoint is the best choice.

The 50 Best Tips

The 50 Best Tips Series

However, too many people simply re-type their Excel data into PowerPoint. This wastes time and it is not the most effective way to present the data.

In this video lesson, I demonstrate five ways to Paste, Link, and Embed Excel data in PowerPoint:

Here are the steps to follow in this video lesson:

  1. In Excel, select and copy the data to the clipboard.
  2. In PowerPoint, select the slide to receive the data and Right Mouse Click to “Paste.”
  3. Look to the lower right-hand corner of the pasted data and click the Paste Options Menu.
  4. Paste Option – Keep Text Only. You can move and re-size the Text Box. You can edit and format any text entry.
  5. Paste Option – Picture of Table. You can move and re-size the picture. The Picture Toolbar is activated. This allows you to format the picture. You can not edit individual values – you only have a “picture” of your Excel data.
  6. Paste Option – Table. This is the Default option. Each entry is in it’s own cell. You can choose Rows and Columns to format, insert or delete.
  7. Paste Option – Excel Table (Entire Workbook) – With this option you actually “Embed” the Excel application. Double-click on the data to activate Excel. Make any changes you desire.
  8. Edit-Paste Special – Link. With this option you create a “live link” to the source data in Excel. Any changes to the Excel file are reflected on the PowerPoint slide. Be careful to keep the Excel file and the PowerPoint file in the same directory to prevent “losing the link.”

Find the Excel Video Lesson that you want – Index to all Excel Topics

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

Related Videos

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, Powerpoint Techniques, Tips and Time Savers Tagged: , , , , , ,
September 19, 2008 1 Comment

My 5-Star Excel Video Lessons on YouTube

YouTube Logo

YouTube Logo

I have posted several of my MS Excel 2003 Video Lessons on YouTube. The YouTube community votes  or rates individual videos. I’d like to share the results with you. To make it easy for you to view a video that interests you, I have established “Hyperlinks” to take you directly to that video lesson. Simply click on the title and you can view the tutorial.

Here is a listing of my 5-Star Excel Videos on You Tube:

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

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, Most Viewed Excel Videos, YouTube Videos by Danny Rocks Tagged: , , , ,
September 18, 2008 1 Comment

The versatile and valuable =IF() Function

MS Excel’s =IF() Function is a very versatile function and it is used in a variety of situations. Watch this short video lesson to learn how to use it when Grading Scores, Calculating Shipping Rates and “Trapping Errors.”

Here are the steps to follow in this lesson:

  1. The =IF() Function requires three arguments: A “Logical Test” – (which will return either a TRUE of a FALSE Response); the Value if “True,” and the Value if “False.”
  2. In my first example I have set up a simple scoring system for grades. For the Value if TRUE, I have entered “Pass.” Notice that the TEXT must be enclosed in ” “.
  3. In my second example, I have “nested” several =IF() functions inside the Value if FALSE Function Argument. You can “nest” up to 7 different IF() Functions in a formula.
  4. Notice how useful the =IF() Function is when you want to “trap an error.” Especially when you are starting to build a spreadsheet and many cells contain Zeros or NULL Values.

Find the Excel Video Lesson that you want – Index to All Excel Topics

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

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 Functions, Excel Training Tagged: , , , , , , ,
September 8, 2008 2 Comments

How to Prepare Your Zip Codes for Mailing Labels

Clients call me up in a panic. “Danny, we are sending out a direct mail piece and Excel has screwed up our Zip Codes – they only contain 4 numbers! The Post Office can’t deliver the mailing piece this way. Can you help us?”

Yes I can!

The problem is not with Excel. The problem is not choosing the correct formatting for the Columns / Fields that contain Zip Codes or Postal Codes.

At the risk of jeopardizing some lucrative consulting fees, I will demonstrate how to solve this problem in this short Excel Video Tutorial.

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

  1. When you have control over your data, pre-format your Zip Codes column using the “Formant Cells, Number, Special, Zip Code” format.
  2. If your data contains cells with both 4 and 5 digit Zip Codes, use this formula:
  3. =IF(A2<=9999, 0&A2, A2)
  4. Now, you will see some cells formatted as “Text” – aligned to the Left side of the cell and other cells formatted as “Numbers” – aligned to the Right side. Take the next step:
  5. First, Copy the data (the cells w/ the =IF() Function) and choose Paste Special, Values to return the results of the formulas.
  6. With the cells still selected choose, Data, Text-to-Columns and choose “Fixed Length.” After previewing your data, choose the “Format as Text”  button and Finish the Wizard.

Find the Excel Training Video that you want – Index to all Excel Topics

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

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, Text Functions Tagged: , , , , ,
September 6, 2008 5 Comments

2 reasons to format cells before you enter data in Excel

Stop! Think! Consider! Develop a plan!

Before you enter any data in your spreadsheets.

Ask yourself questions about your data.  Why?

  1. To save yourself time
  2. To maintain consistency in data entries
  3. To prevent GIGO  (Garbage in equals Garbage out)
50 Best Tips for Excel 2007

Excel Tips

In this video lesson I demonstrate the advantages you will obtain when you:

  1. Stop – before you start to enter data
  2. Pre-plan your data list
  3. Format your data fields / columns BEFORE you make your 1st entry

Here are the steps to follow in this video lesson:

  1. Set up and Format your Column / Field Headers for each data field.
  2. For each Column / Field Header, select the columns and format the cells (Ctrl+1) BEFORE you enter any data.
  3. On the NUMBER tab, choose “Special” to select formats for Social Security #, Phone #, and Zip Code

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.

Find the Free Excel Training Video that you want – Click here to go to my Index of Excel Video Lessons

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 Training Tagged: , , , , ,
« Previous Page
Next Page »