Home Archives for Excel Video Training
October 18, 2009 Leave a Comment

Use the Transpose Feature to Change the Orientation of Data Imported into Excel

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.

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

Related Videos

Link to this post!
Filed Under: Excel Training, Import Data into Excel, Integrating MS Office Programs, Video Response to Viewers Tagged: , , , , , ,
October 17, 2009 Leave a Comment

Use the =COUNTIF() Function to see how many badges have been claimed

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.

Learn Excel quickly. Over 5 1/2 hours of Excel 2007 training on my DVD. Each lesson averages @ 6 minutes.

Shop for the DVDs in “The 50 Best Tips for…” series at my online bookstore.

Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Excel Functions, Excel Training, MS Excel 2007 DVD, MS Excel Training, Video Response to Viewers Tagged: , , , ,
June 7, 2009 2 Comments

My New DVD – The 50 Best Tips for MS Excel 2007 is now on sale

50 Best Tips fo MS Excel

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

Do you ever have one of those “A-ha” moments? You know – those moments when everything falls into place. When everything becomes crystal clear. When you finally “get it!”

Well, here is your opportunity to experience 50  “A-ha” moments. Here is a great way for you to learn “The 50 Best TIps, Tricks, and Techniques for Microsoft Excel 2007.”

I spent almost three months filming these short Excel training videos. I worked hard to create this DVD in order to make it easy for you to learn how to remain productive at work while learning to use the new Excel 2007 interface.

And, the Excel 2007 interface is really quite different.

So different, that many long-time Excel users just give up on it and return to the tried and true Excel 2003 program.

And that is too bad. Because, Excel 2007 allows you to perform so many business tasks faster and better. But … first, you must get over “the initial learning curve” to understand how to navigate in Excel 2007.

This DVD makes it easy for you to learn how to use Microsoft Excel 2007 – guaranteed!

In selecting the 50 Best Tips for Excel 2007, I wanted to include tricks that all Excel users could put to use – now! I include tips at the Introductory, Intermediate and Advanced levels.

Each lesson is short – five to six minutes on average – and focused on just one topic. Each lesson on the DVD is self-contained – I teach you how to solve one problem per lesson.Overall, the DVD contains 5 1/2 hours of training on Excel 2007!

This DVD is available exclusively from the SkillPath Seminars bookstore. They are offering it for sale at the introductory price of $49.95 Here is a link to the demonstration / order now page.

I am proud of this product. I know that you will gain some new insights into how to get the most out of Excel 2007. I welcome  your feedback. Please drop me a note  – danny@thecompanyrocks.com Or … add your comments below for all readers to share.

Thank you for your support!

Danny Rocks

Link to this post!
Filed Under: Excel Training, MS Excel 2007 DVD, Online Store for The Company Rocks Tagged: , , , , , , , , , , , ,
April 9, 2009 Leave a Comment

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

Link to this post!
Filed Under: Excel Basics, Excel Training Tagged: , , , , , ,
April 8, 2009 Leave a Comment

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

Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , , ,
April 6, 2009 1 Comment

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

Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , ,
April 3, 2009 Leave a Comment

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

Link to this post!
Filed Under: Everyday Calculations in Excel, Excel Date and Time Functions, Excel Functions, Excel Training Tagged: , , , , , ,
March 29, 2009 Leave a Comment

How to Create Scenarios in Excel

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:

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

Link to this post!
Filed Under: Excel Training, MS Excel Training, What-if Analysis Tagged: , , , , , , ,
March 25, 2009 Leave a Comment

12 Excel Practice Files to Download

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!

Sorry, there are no polls available at the moment.
Link to this post!
Filed Under: Excel Training, Interactive Webpages Tagged: , ,
March 22, 2009 1 Comment

My Favorite Excel Keyboard Shortcut

I use Keyboard Shortcuts in Excel everyday. However, the one that I count on the most; The one that has saved me from near disaster on multiple occasions is:

Ctrl + Z – The UNDO Command.

Ctrl + Z can Undo my last 16 actions (one at-a-time.) In Excel 2007, you can Undo up to 100 of your last actions!

Watch this short Excel Training Video as I demonstrate how to take advantage of the Ctrl + Z Shortcut.

Here are the steps to follow in this lesson:

  1. Type an entry or apply formatting to a cell or range of cells.
  2. Hold down the “Ctrl” Key while you press the “Z” key. Excel will Undo your last action.
  3. To Restore the Action that you just UNDID, use the “Ctrl + Y+ Keyboard Shortcut.
  4. You can Undo your last 16 actions in Excel 2003. Use the drop-down menu next to the UNDO Command Button on the Standard Toolbar.
  5. Follow a similar  procedure to RESTORE up to your last 16 actions in Excel 2003 with the drop-down menu next to the Restore Command button on the Standard Toolbar.
  6. In Excel 2007, you can UNDO and RESTORE up to your last 100 Actions!

Are you looking for Excel Video Training for a specific topic? Search my Index of Excel Video Topics.

Related Videos:

NEW! Now you can practice your Excel Skills – Download the Excel Workbooks that I use in my video lessons

Link to this post!
Filed Under: Excel Training, Tips and Time Savers Tagged: , , ,
« Previous Page
Next Page »