Home Archives for Danny
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.

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.

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

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.

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.

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged: , , , , , ,
September 22, 2008 Leave a Comment

5 Ways to Import Excel Data into PowerPoint

MS Excel is the best application for organizing & calculating data. It is also the best choice for creating graphs of your data.

MS PowerPoint is the best application for communicating “the story” that you want your data to communicate.

Use  each application to do what it does best. And don’t waste time re-typing your Excel data into PowerPoint.

In this video lesson, I demonstrate 5 ways to import your Excel data into a PowerPoint presentation. 1) Paste it as Text only. 2) Paste it as a Picture. 3) Paste it as a Table. 4) Embed the entire Excel Workbook (OLE). 5) Paste Special to establish a “Link” to the Excel Source Data.

Here are the steps to follow in this video lesson:

  1. Select and copy the Excel Data to the clipboard.
  2. In PowerPoint select the slide for the data and choose “Paste.”
  3. Look at the bottom right corner of the “pasted data” and locate the “Paste Options” tag.
  4. Option #1 – Paste as Text Only. The data is pasted into a Text box that can me sized and moved.
  5. Option #2 – Paste a Picture of the Table – Can be sized and moved. The Picture Toolbar makes it easy to modify the background and borders of the table.
  6. Option #3 – Paste the data as a Table (Default option) – each entry fits into its own “cell” in the table. Easy to format the table to fit your needs.
  7. Option #4 – Paste the Entire Excel Workbook. This option actually “embeds” the Excel application – and increased the size of you file. You can double-click on the table and the MS Excel application is activated (OLE) so that you can e.g. modify functions and add fields. etc.
  8. Option #5 – This time you choose Edit – Paste Special – Link. Now, whenever you change the “source data” in Excel your PowerPoint slide will update to reflect the current information. Be careful to keep the Excel file in the same directory as your PowerPoint presentation file so that the “link” does not get lost when you move files to a USB drive or other location.

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

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

Link to this post!
Filed Under: Excel Charts, Integrating MS Office Programs, PowerPoint, Powerpoint Techniques, Presentations Tagged: , , , , , , ,
September 21, 2008 Leave a Comment

Watch My PowerPoint Video Lessons on YouTube

I have uploaded many of my PowerPoint Training Videos to YouTube. Here is the link to my YouTube Channel:

YouTube Logo

YouTube Logo

http://www.youtube.com/user/DannyRocksPowerPoint

 

YouTube reaches a very broad audience. It offers a very different experience. YouTube makes it very easy to share the videos that you like with your friends and colleagues. Check it out for yourself!

I must admit that I was wary about uploading my training videos to YouTube. However, I have had a very positive response to my MS Excel Videos on YouTube. So I have created a separate PowerPoint Channel and have begun to upload content from this website to YouTube.

If you want to share any of my Excel or PowerPoint Videos, YouTube make it easy to do so. You can even choose to embed the videos on your own website – go right ahead! I have included my business card at the beginning and the end of each video so that viewers know how to reach me.

Let me know what you think about YouTube. How is it different from this blog? Have you ever shared a video that you found on YouTube with your friends. Have you ever embedded a YouTube video on your own website?

I invite you to add your comments below.

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

Link to this post!
Filed Under: PowerPoint, Powerpoint Techniques 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

Link to this post!
Filed Under: Excel Training, Powerpoint Techniques, Tips and Time Savers Tagged: , , , , , ,
September 20, 2008 Leave a Comment

Work in Outline View in PowerPoint

MS PowerPoint’s Normal View has two Tabs – Slide View and Outline View.  In this video lesson, I will share tips for getting the most out of Outline View.

For example:

  • Collapse or Expand the text for individual Slides or for all Slides in the presentation.
  • Create a “Summary Slide” containing the titles for each slide in your presentation.
  • Insert Hyperlinks to text on your “Summary Slide” to make it easy to navigate to an individual slide.

Here are the steps to follow in this video lesson:

  • In Normal View, choose the Outline Tab.
  • Be sure that the Outlining Toolbar is active. (View – Toolbars – Outlining) You can either “dock it” or make it a “floating toolbar.”
  • Experiment with Expand and Collapse – Individual slides (Double-click or Expand on the toolbar) or All slides in the presentation.
  • Create a Duplicate Slide – The keyboard shortcut is Ctrl+D or choose Insert-Duplicate Slide.
  • To create a “Summary Slide” first select the slide icons for the slides you want. Then click the Summary Slide Icon on the Outlining Toolbar.
  • Create a “Hyperlink” to quickly navigate to individual slides. First select the text on the “Summary Slide” and then use the keyboard shortcut – Ctrl+K and choose “Place in this Document” and choose the slide.
  • You must be in Slide Show View in order to test your Hyperlink.

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

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

Link to this post!
Filed Under: PowerPoint, Powerpoint Techniques 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.

Link to this post!
Filed Under: Excel Training, Most Viewed Excel Videos, YouTube Videos by Danny Rocks Tagged: , , , ,
« Previous Page
Next Page »