I invite you to join me on a four minute, seven second video tour of my new instructional DVD, “The 50 Best Tips, Tricks & Techniques for Excel 2007.”

This DVD is different. If you want to learn Excel and you are pressed for time (and who isn’t these days?) this is the DVD for you. I give you over 5 1/2 hours of focused video instruction. The average length of each video lesson is 6 1/2 minutes! So you can find the topic that you want, learn it, and put it into practice at home or at work in under 10 minutes!

As an added bonus, I include the 50 Excel worksheets that I used to create and film the video lessons. Now you can practice the tips to master your new Excel skills!

Follow this link if  you want to see the complete list of the 50 video lessons on my DVD.

Are you ready to purchase “The 50 Best Tips for Excel 2007?” Follow this link to enter the online bookstore for The Company Rocks. This is a secure shopping site. And, I guarantee that you will be satisfied with your purchase. If you are not satisfied with your purchase, I will refund you money – no questions asked!

Sincerely,

Danny Rocks

Related Videos

• Video Tour of My DVD – “The 50 Best Tips for PowerPoint 2007″

Here is another request for help from a viewer. How to create a report that shows “the aging” of past due invoices (1 – 30 days past due, etc.).

Here are the Functions that I use in this report:

• =IF()
• =AND()
• =WEEKDAY()

Looking for a specific Excel video? Take advantage of my Index of Excel Video Lessons to find the video that you want to watch.

Related Videos

• Use the =TODAY() Function to find past due invoices

Want to learn the best Tips for Excel? You can purchase my DVD, “The 50 Best Tips, Tricks & Techniques for Excel 2007″

Do you manage Projects? Do you create Project work schedules? Do you need to know how many days were actually worked on your Project?

If so, this Excel video lesson is for you. A viewer asked for my help. He did not want to use MS Project for his work schedules. Rather, he wants to know which Excel Functions to use for his Project work schedules.

In this lesson, I demonstrate how to use two functions included in the Excel “Analysis Tool-Pak”:

• =WORKDAY()
• =NETWORKDAYS()

Search for a specific Excel Video Lesson on this website in my Index.

Want to learn how to get the most out of Excel 2007? My DVD, “The 50 Best Tips, Tricks & Techniques for Excel 2007″ is available for purchase.

Here is another response to a viewer request. The letter asks for my help in identifying, counting and totaling the amount of “Past Due” invoices. In the viewer’s letter, she wanted me to use the =NOW() Function. This function returns the current date and time (Hour, Minute, Second) from your computer’s system clock. The =TODAY() Function is similar, but it returns only the current date. Both the =NOW() and =TODAY() Functions are “volatile.” This means that the value that they return will automatically update according to your computer’s system clock. THis makes them excellent reference points in formulas that identify “Past Due” invoices.

In addition to using the =IF() Function to identify the invoices that are “Past Due,” I also demonstrate two other functions: =COUNTIF() to total the number of “Past Due” invoices and =SUMIF() to give me the total dollar amount that is “Past Due.” I recreate these formulas, this time, using “named cell ranges” in the formulas.

Finally, I show you a great new Filtering Feature in Excel 2007 – the ability to filter by time period e.g. “Next Week!”

Related Videos

• Build an Accounts Receivable Aging Report

Check out my new DVD, “The 50 Best Tips, Tricks, and Techniques for Excel 2007.” It contains over 5 1/2 hours of training for Excel 2007. You can locate the specific tip that you want to learn – and in @ 6 minutes, you will have received all of the information that you need to become more productive in this area.

I am working through the special requests that viewers have sent me. In this lesson, I show you how to:

• Use Data – Get External Data – to import a simple delimited text file.
• Use Copy – Paste Special – Transpose – to make a copy of the imported data that is now “vertically oriented.” i.e. The field entries run down the column rather than across the rows.
• Use the Data – Text-to-Columns feature to break an “address block” (City, State, Zip Code) that is one cell into three distinct cells. This is a terrific time saver!

Now available for purchase: My DVD “The 50 Best Tips, Tricks, and Techniques for Excel 2007″

Over 5 1/2 hours of training. Each video lesson focuses on one tip and averages 6 minutes.

Available from www.ourbookstore.com and www.completebook.com

Now available from the online store at www.thecompanyrocks.com

A viewer wants to know how many badges have been claimed and how many have not yet been claimed. In Excel, use the =COUNTIF() Function to get the answer(s) to this question.

If you have ever struggled with using “Nested =IF() functions” you will welcome the =COUNTIF() Function – it is so much easier to use because the arguments for the function follow a logical progression.

My viewer also wanted to see the names of the guests who have claimed or who have not yet claimed their badges. To answer this request, I use a Data Filter – very easy to use and change.

Find the Excel Tip that you want – quickly! Over 5 1/2 hours of Excel 2007 training on my DVD. Each lesson averages @ 6 minutes.

Available from: www.ourbookstore.com and www.completebook.com

New! Now available from the online store at www.thecompanyrocks.com

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

Related Videos

• 3 Reasons to Use Names in Your Formulas
• 3 Ways to Minimize Data Entry Errors

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

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

• 3-frustrations-with-time-calculations

Find any Excel Video Training Lessons by Topic – Search My Index of Excel Lessons

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

Related Videos

• Learn How Excel Calculates Time
• How Does Excel Calculate Future Dates?

Scenarios are part of Excel’s “What-If” Analysis tool set – aka Data Analysis. Use Scenarios when you need to know what the result will be when you change one or more variables in one or more  situations (scenarios).

Frequently, I create Scenarios to define  a “Good, Better, Best” or “Worst Case, Best Case, Case Most Likely” outcome.

In this short Excel Training Video, I demonstrate how to create, edit,  and report four Scenarios for a Monthly Office Expense Budget.

These are the steps used in this Excel Video Lesson:

• Create (or open) an Excel worksheet that contains a formula. Identify the formula and the precedent  cells that feed into the formula.
• Select the “target cell” – the cell that contains the formula whose result will change with each Scenario.
• Choose TOOLS – SCENARIOS and follow the directions in the Create Scenario dialog box.
• First, click ADD. Name the Scenario. Click OK. Confirm that you have selected the “Target Cell” and then click / type in the “Changing cells.” The Changing cells should be either direct or indirect precedents to the Target cell formula. Click Add. Add additional Scenarios as desired.
• Click on the name of your Scenario and select SHOW to display the changing cell values and the new result in your target cell.
• If you wish, choose SUMMARY to create a summary report in a separate worksheet.
• I strongly recommend that you create named cells and use them in the formulas and changing cells of your Scenario.

NEW! Download the Excel Practice file used in this lesson:

• create-scenarios

Use my Index to search for the free Excel Training Video that you want to view.

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

Related Videos

• Perform “What-if” analysis with a two-input Data Table
• Use Goal Seek to optimize a monthly loan payment

I want to thank the viewers who emailed me and responded to the Poll on this blog. I have now uploaded 12 Excel Practice files to this blog. Follow this link to navigate to the Download Excel Practice files page.

• Click on the Practice file that you are interested in.
• Right-click the Icon to determine how and where to open the practice file.
• The Practice files are saved as “Interactive Web Pages.” From here, you can decide to work with the file over the Internet or to save it to disk as an Excel Workbook.

I will be adding additional Practice files – approximately three each week – so check back to locate the latest files that I have downloaded. If there is a particular file that you want to work with, email me your request – danny@thecompanyrocks.com and I will make it available as quickly as I can.

Thank you for your continuing support of The Company Rocks!

 Loading ...