Download My Excel Workbooks and Step-by-Step Instructional Manuals for Free!

Finally! I have figured out a way to allow my viewers to download the Excel Workbooks, Word Documents, PowerPoint Presentations, etc. that I use in my video lessons. For the past few months, I have been able to give you a direct link to download the Adobe Acrobat files that I create. For example:

My problem has been that – Starting in Office 2007 –  the underlying file structure of each program (Excel, Word, PowerPoint, etc.) has changed and it is no longer possible to “Publish to the Web” the worksheets, documents and presentations that I offered in the past. I will spare you the boring details of why and how this has happened, because I have good news for you!

Enter, “The Cloud!”

Cloud Computing

You have probably seen or heard this advertising slogan recently. Here is how I have just discovered how “The Cloud” can work for both you and me:

Initially, I am uploading – and sharing – the workbooks, documents, PDFs, etc. that accompany the extended video recordings that I offer on my http://thecompanyrocks.webex.com site.

This is a new development for me and The Company Rocks. I offer a series of extended length ( 25 minute to 60 minutes) video tutorials.

  • Intro to Pivot Tables in Excel 2007

    Information about this Video Recording

     

  • As I write this posting, I have 4 modules – each offered “free of charge” – for you to view and / or download.
  •  
  •  

Go to http://thecompanyrocks.webex.com to see what I currently offer you!

  • As I gather feedback, activity, statistics, etc. I may extend the range of downloadable content that I create – My decision will depend upon your feedback, so please add your comments below in this posting!
  • My Next Level of Product Offerings 

    I am on a journey to evolve the range and detail of the products and services that I offer you, my loyal viewers and subscribers. I wnat to be responsive to your needs. So, that meanrs that the more feedback that I get from you – my viewers and subscribers – the better I can structure my offerings. I welcome your feedback!

    Based on some great feedback from my viewers and subscribers, I have just begun to create a series of extended length ( 25 to 45 minutes) video tutorials. The initial tutorials will be offered “free of charge.” Other video tutorials will be carry a modest – I might add, aggressive – pricing model! My goal is to give you the product and content that you need at a price that is mutually acceptable! You, and your level of supposrt will tell me what is acceptable – please give me your feedback!

    Extended Video Tutorials Now Online

    You can now view or download – after registering – at WebEx by Cisco – for these video tutorials that I have posted:

    

    I hope that you find these links and videos helpful. Please add your feeback via the comments section below this post.

    Sincerely,

    Danny Rocks

    www.thecompanyrocks.com

    Share and Enjoy:
    • Add to favorites
    • Facebook
    • Twitter
    • Technorati
    • Print
    • email
    • Digg
    • StumbleUpon
    • del.icio.us
    • Yahoo! Buzz
    • Google Bookmarks
    • Orkut
    • SphereIt
    • Sphinn

    Video Tutorials for Both Excel 2007 and Excel 2003

    Many of my clients and viewers continue to use Office 2003 Programs (Excel 2003, Word 2003, PowerPoint 2003, etc.) and they have asked me to produce more video lessons that use these programs.

    Finally, I have found the solution!

    WebEx Recorded Sessions

    WebEx Recorded Sessions

    Extended Length Video Recordings on WebEx by Cisco

    I have just started to create a series of extended length, in-depth instructional video tutorials as part of my “Master Business Skills in Minutes!” Series. I am making these recorded video lessons available on my WebEx by Cisco Systems website: http://thecompanyrocks.webex.com

    My goal is to offer a “mix” of recorded video lessons where some are offered “free of charge,” while others will carry a modest fee. Each lesson that I publish will be offered in two versions – one for Office 2007 and a parallel version for Office 2003. Eventually, I will publish versions that use Office 2010.

    Registration is Required – and Simple!

    Each of the lessons – free or paid – will require you to register at WebEx before you can have access to download the video or to view it online. I have made the registration process as simple as possible: all that you need to supply is First Name, Last Name and a valid email address. That’s it! I value your privacy – click here to read or download the Privacy Policy for The Company Rocks.

    Create a Pivot Table with Fewer than 10 Clicks of Your Mouse

    Free Video Lessons on My Website, Video Podcast and YouTube

    I will continue to post free Excel Video Lessons here on my website – www.thecompanyrocks.com and I will include links to my YouTube Channel – DannyRocksExcels  These videos will be short and focused – under 9 minutes!

    What You Get with My WebEx Video Recordings

    The videos that I publish at http://thecompanyrocks.webex.com -are designed to be longer than what I post here on my website or to YouTube. On the “extended length” videos, I will pay much more attention to the details and the “reasons why” aspects of the key steps. With WebEx, I can also offer you the opportunity to download the actual Excel Workbook that I used to create the lesson and give you a PDF of the “Step-by-step” instructions for each lesson.

    I am excited about this new development. I want to hear what you think. Kindly add your comments and feedback in the section below this post.

    Share and Enjoy:
    • Add to favorites
    • Facebook
    • Twitter
    • Technorati
    • Print
    • email
    • Digg
    • StumbleUpon
    • del.icio.us
    • Yahoo! Buzz
    • Google Bookmarks
    • Orkut
    • SphereIt
    • Sphinn

    Announcing My Online Courses – Master Business Skills in Minutes

    I am proud to announce that I have posted the first two recorded video lessons for my online course: “Master Excel in Minutes!”

    WebEx Navigation Pane

    WebEx Navigation Pane

    These courses are recorded in-depth videos that you can choose to watch online or download to your computer to watch at a later time. Some of the recordings will be offered free of charge. For others – which contain premium content – I will charge a fee. The recorded videos will be found on my website at WebEx:

    http://thecompanyrocks.webex.com

    New Page on My Website

    You can find more information about my online courses by following this link to a new page that I have created here on my website.

    Here are the direct links to my first two recorded video lessons on WebEx – be sure to click the “Recorded Sessions” on the main “Attend a Session” navigation pane at http://thecompanyrocks.webex.com:

    In this format – recorded, downloadable, video lessons – I am able to go into more detail on the topic than I do with my free video podcasts. I can also cover – in depth – topics that my viewers have been requesting. For example, I can now train you in MS Office 2003 (Excel 2003, Word2003, etc.)

    Contents of Recorded Video Lessons

    Each of the recorded video lessons includes:

    • The recorded video lesson that you can watch online
    • The recorded video lesson that you can download to your computer to view at a later time
    • The workbooks or documents that I use during the lesson – so that you can practice the concepts that I present on the video recording.
    • A PDF containing the Step-by-step instructions for each lesson – so that you can make notes as you view the recording or during your practice sessions.

    I hope that you will take a few minutes to view my first offerings in this course. After you watch the lessons, please send me your feedback, questions and suggestions for future course offerings.

    Thank you!

    Danny Rocks

    Share and Enjoy:
    • Add to favorites
    • Facebook
    • Twitter
    • Technorati
    • Print
    • email
    • Digg
    • StumbleUpon
    • del.icio.us
    • Yahoo! Buzz
    • Google Bookmarks
    • Orkut
    • SphereIt
    • Sphinn

    Create a Web Query in Excel to Gather Updated Information

    Web Query in Excel

    Web Query in Excel

    When you select, copy and paste data from a website into an Excel document, you get static images and data. When you want that data to be updated – or refreshed – with the latest information, you create a Web Query in Excel.

    Follow the process that I demonstrate in this video and you will learn how to gain access to current information without having to leave Excel!

    Create a Web Query

    • On the Data tab of the Excel 2007 Ribbon, select Get External Data – From Web.
    • In the Dialog Box – that resembles a “Web Browser” – type in the URL of the Website you want to search. Notice that the “Browse” Button is missing. My Best Practice is to make a copy of and paste in the URL of the website you want to search.
    • After you press the “GO” Button, you will see some “Yellow Arrow” markers on the website. These are the HTML Tables that you can select for your Web Query.
    • When you however your mouse over the Yellow Arrows, you will see a Blue Border around the HTML Table. Click the Yellow Arrow and a “Green Check Mark” indicates that you have selected this table. Continue to make your selections and then click “Import.”

    Import Web Query

    The final step is to decide where to place your Web Query, I usually accept the default selection of cell $A$1. There is an “Properties” Button that you can access now – or later. Press the “Add” Button and after a few seconds, you have imported the data from your Web Query.

    As you will see in the Video for this lesson, it it easy to go back and Edit your Web Query or Change the Properties of the Query – e.g. to automatically Refresh every 60 minutes. You can also choose to Refresh the Query now.

    Best Practice Suggestions

    • Use a separate session of Excel when setting Properties of your Web Query to automatically refresh. In this way, you will not slow down your work on another Excel project.
    • Gather your favorite Website URLs and paste them as Hyperlinks on a Worksheet. Select the Hyperlink prior to initiating a new Web Query. This will save you time as the Web Query Dialog Box does not have a Browse Button!

    Watch Video in High Definition

    Follow this link to watch this video in High Definition on my YouTube Channel – DannyRocksExcels

    Learn How to Master Excel in Minutes – Not Months!

    Share and Enjoy:
    • Add to favorites
    • Facebook
    • Twitter
    • Technorati
    • Print
    • email
    • Digg
    • StumbleUpon
    • del.icio.us
    • Yahoo! Buzz
    • Google Bookmarks
    • Orkut
    • SphereIt
    • Sphinn

    Functions to Analyze Loan Payments in Excel

    Loan Payment Functions

    Loan Payment Functions

    When you use the PMT Function in Excel, the result is the amount of money that you will pay each period (usually monthly) until the loan is repaid. When you want to know how much of each payment is an interest charge, you use the IPMT Function. And,to find the amount that goes towards reducing the principal on your loan, you use the PPMT Function.

    Cumulative Payments

    Two other functions are helpful in analyzing your loan payments. When you want to see the Cumulative amount of Interest (CUMIPMT Function) that you paid between two periods – e.g. Starting with payment 1  and ending with payment 12. You use the CUMPRINC Function to total the amount of your loan payments that went towards reducing the principal on your loan between any two periods.

    Absolute and Relative Cell References

    As you will see in the video, I use a combination of Absolute Cell References (e.g. $A$4) and Relative Cell References (e.g. A4) in these formulas. An Absolute Cell Reference means that cell will “remain in place” when the formula is copied down or across to other cells. In this example, I have my Loan Information (Amount to borrow, Interest Rate, etc.) at the top of the spreadsheet. Almost all references to these cells in formulas is Absolute.

    Function Arguments Dialog Box

    I almost always use the Function Arguments Dialog Box when I am creating my formulas. I use the Keyboard Shortcut, Ctrl + A at this point in the formula – “=PMT(” to activate the dialog box. The advantages of using the Function Arguments Dialog Box are:

    • Explanation of both the function and each “argument” in the function
    • Ensures arguments are answered in the proper sequence
    • Ensures that all “required” arguments (Argument Labels are in Bold)
    • Evaluates the result of each “intermediate” calculation – to the right of each argument

    This is the first of several Excel Video Lessons that I am creating to demonstrate how to perform Financial Calculations. Let me know what you think or send me your questions to answer. You can add a comment below this post.

    Watch This Video in High Definition on YouTube

    Follow this link you view this Excel Video on my YouTube Channel – DannyRocksExcels

    Download Excel Workbook

    Follow this link to download the Excel Workbook I used in this lesson.

    Subscribe to My Video Podcast

    I invite you to receive an automatic notice – via email – whenever I publish a new Excel Video Lesson. Follow this link to find out the details. The subscription is free; you do not need to provide and personal information beyond your email address; and you can cancel at any time – no questions asked!

    Share and Enjoy:
    • Add to favorites
    • Facebook
    • Twitter
    • Technorati
    • Print
    • email
    • Digg
    • StumbleUpon
    • del.icio.us
    • Yahoo! Buzz
    • Google Bookmarks
    • Orkut
    • SphereIt
    • Sphinn

    Dynamically Update a Cell with an Excel Worksheet Name

    Formula to Capture Worksheet Name

    Capture Tab Name in Cell

    I am excited to share this Excel Tip with you! Several viewers have recently asked if there was a way to create a link in a cell to the name of a worksheet tab. Yes there is! And, in this lesson, I demonstrate how to do this.

    Say, for example, that you want Cell C1 to contain the name of the current worksheet (Sheet1). With this formula, when you change the name of the worksheet to “January,” cell C1 is dynamically updated to show “January.”

    Perfect for Excel Templates

    If you use a template for your monthly, regional sales or financial reports, then this Excel formula is essential. You write the formula once – using Group edit – and each worksheet in your Excel workbook will be linked to a cell in your report. Change the name of the worksheet and your report name is automatically updated.

    Functions Used in this Formula

    • CELL() – to gather information about the worksheet – in this case, the “Filename.”
    • MID() – to extract the characters in a text string – e,g, the “worksheet name.”
    • FIND() – to locate the starting point for the MID() function to extract the characters in the text.

    As the final argument for the MID() Function, I use 31 characters because that is the maximum number of characters that you can use when naming an Excel worksheet.

    Try this for yourself. Let me know how it works for you. Add your comments below.

    Download Excel Workbook for this Lesson

    Watch Video in High Definition

    Follow this link to view this video tutorial on my YouTube Channel – DannyRocksExcels

    I invite you to watch or to subscribe to my video podcast on iTunes. Follow this link to learn more about my RSS Feed.

    Share and Enjoy:
    • Add to favorites
    • Facebook
    • Twitter
    • Technorati
    • Print
    • email
    • Digg
    • StumbleUpon
    • del.icio.us
    • Yahoo! Buzz
    • Google Bookmarks
    • Orkut
    • SphereIt
    • Sphinn

    How to Use Text Functions in Excel

    Text Functions in Excel

    Text Functions

    In my experience, I find that many of my clients do not realize that Excel has many powerful – and easy to use – Text Functions.

    Even more amazing, I observe clients spending hundreds of hours and thousands of dollars “re-keying” or “manually editing” text entries that have been downloaded from a Main Frame Computer. If you – or your colleagues – have been doing this, you will want to watch this Excel Video Tutorial.

    Categories of Text Functions in Excel

    In this Excel video lesson, I demonstrate how to use three categories of Text Functions:

    1. Transform Text – Use the UPPER, lower and Proper functions to change the “Case” of a text string.
    2. Join Text – Use either the CONCATENATE() Function or the & (Ampersand) Operator to join two or more text strings into a single text string.
    3. Clean-up Text – Use the LEN(), TRIM() and CLEAN() Text Functions to “trim the fat” from text strings – especially those that you download from a Main Frame Computer.

    Once again, in my experience, I have too many clients contacting me in a panic because they get a REF# error when using Text Functions. I demonstrate how to recover from a “seeming disaster” and also how to use COPY – PASTE SPECIAL – VALUES to transform Formulas into Constant Values.

    I welcome your feedback. Please send me an email: danny@thecompanyrocks.com – to let me know if this tip helped you to save time and money. I also welcome your suggestions for future Excel Video Lessons to create and post here, on my website.

    Watch This Excel Video Tutorial in High Definition Mode

    Follow this link to view this Excel Video Lesson in High Defintion Mode on my YouTube Channel – DannyRocksExcels

    Download the Excel Workbook

    Follow this link to download the Excel Workbook I use in this lesson.

    Learn How to Master Excel in Minutes – Not Months!

    If you like this Excel Video Tutorial, I invite you to learn more about the resources that I offer to help you to improve your Excel skill set.

    Share and Enjoy:
    • Add to favorites
    • Facebook
    • Twitter
    • Technorati
    • Print
    • email
    • Digg
    • StumbleUpon
    • del.icio.us
    • Yahoo! Buzz
    • Google Bookmarks
    • Orkut
    • SphereIt
    • Sphinn

    Learn How Excel Calculates Formulas

    Order of Calculations in Excel

    Excel Calculation Order

    Have you ever written a Formula in Excel only to receive a result that was different from the one you expected? Most Excel users have shared that experience from time to time! Excel is smart and fast. However, it can not read your mind.

    In this lesson, I show you how Excel evaluates and calculates a formula. Once you understand “Excel’s perspective” of the formula, you will know how to correctly write the formula so that Excel will produce the result that you intended. In other words, to see the formula from “your perspective!

    Order of Precedence

    Excel performs calculations in formulas moving from left to right in this order:

    1. Performs Multiplication (*) and Division (/)
    2. Goes back and performs Addition (+) and Subtraction (-)

    This is the “key” to consistently getting Excel to produce the results that you intended. No, you can’t get Excel to make you an “instant millionaire” or do anything illegal. However, understanding how to write formulas correctly – so that you control the order of calculation in Excel – is the “secret sauce!”

    Control the Order of Calculation in Excel This formula: =5+15*2 results in 35. If you were expecting the result to be 40, then write the formula as (5+15)*2. In other words, take the “result” of 5+15 or 20 and multiply this by 2 to give me 40.By using parentheses (5+15) you take control over the order of precedence that Excel uses. Help Excel to see the formula from your perspective!

    Free Chart of Excel Formula Operators

    I have published  a chart – “Using Operators in Excel Formulas” – To get your free copy as a PDF, click on the link below:

    CR – Using Operators in Excel Formulas

    Explore My Free Excel Video Lessons

    Follow this link to my Index of Free Excel Video Tutorials

    Watch This Video Tutorial In High Definition

    This link will take you to the DannyRocksExcels video on YouTube

    Share and Enjoy:
    • Add to favorites
    • Facebook
    • Twitter
    • Technorati
    • Print
    • email
    • Digg
    • StumbleUpon
    • del.icio.us
    • Yahoo! Buzz
    • Google Bookmarks
    • Orkut
    • SphereIt
    • Sphinn

    How to Search For and Use Text Strings in Excel

    A viewer asked for my help in creating a report that will group his customers’ email addresses by their “domain name.” For example, he wants to be group all customers with gmail addresses . Likewise, his  customers’ yahoo mail, aol.com accounts, etc.

    Pivot Table for Email Domains

    Pivot Table Email Domains

    I immediately decided to use two powerful Excel tools to produce this report:

    1. Text-to-Columns to create two columns (name and domain) from one text value (the email address).
    2. Pivot Table Report to group and present customer emails by domain name.

    Both tools work great and I produced the report with only a few mouse clicks!

    The response that I got from my viewer was, “That’s great Danny. But …”

    It turns out that the viewer wanted to have the Domain Names as the Field Headers and to show each customer’s email address as a row going down vertically in the corresponding field. To give my viewer the report that he wanted, I used these tools and techniques:

    • I returned to the Pivot Table to copy the unique list of domain names.
    • I used Paste Special – Transpose to convert the vertical list of domain names into a horizontal row of Field Headers.
    • I began to build my “Mega-formula” by taking “baby steps” – i.e. I created one formula and then “nested” it inside additional formulas and functions.
    • For the first “baby step” formula I used the SEARCH() Function.
    • Next, I “nested” this formula as the “logical test” inside an IF() Function.
    • In order to “hide” all of the formula error signs, I nested both of these functions inside the NEW IFERROR() Function.
    • IFERROR() was introduced in Excel 2007 and I now consider it to be “indispensable!” This function, alone, makes upgrading to either Excel 2007 or Excel 2010 a “nobrainer” decision!

    I enjoy answering questions from my viewers. Many of these questions can be answered from one of the lessons on my DVD-ROM, “The 50 Best Tips for Excel 2007.” For the rest, I try to create a video lesson that demonstrates how to get the answers that you – my viewers and customers – are looking for.

    Keep your questions and suggestions coming! I enjoy receiving feedback from you!

    I invite you to subscribe to my RSS Feed on Feedburner to be automatically notified whenever I publish a new Excel Video Tutorial.

    Watch this Excel Video in High Definition on YouTube

    Here is the link to watch this video on my YouTube Channel – DannyRocksExcels

    Share and Enjoy:
    • Add to favorites
    • Facebook
    • Twitter
    • Technorati
    • Print
    • email
    • Digg
    • StumbleUpon
    • del.icio.us
    • Yahoo! Buzz
    • Google Bookmarks
    • Orkut
    • SphereIt
    • Sphinn

    How to Take Advantage of Report Filters in Excel Pivot Tables

    Starting with Excel 2007, Pivot Tables now include a “drop field” labeled “Report Filters.” The Report Filters area replaces the area labeled the “Pages” drop area in Excel 2003 and earlier. Report Filters is a more descriptive and intuitive name. So , this is a welcome change.

    How to Organize Pivot Table

    Use Report Filters in Pivot Table

    In this lesson, I am responding to an additional request from one of my viewers who wants me to demonstrate how the “Report Filters” drop zone will provide incredible reporting power in a Pivot Table.

    Request from a Viewer

    In this lesson, my viewer is a “hydrologist” who analyzes average Water Flows by day and by month in a “Water Year.” If you use a “Fiscal Year” reporting structure, you will want to pay attention to this lesson. In this case, the “Water Year” begins on October 1 and ends on September 30. My viewer needs my help in separating out individual Pivot Table Reports organized by Water Year – placed in the Report Filters Drop Zone.The Pivot Tables show the average daily water flow by date. He told me that he is concrned that that Pivot Table Report may not be able to “account for Leap Years.”

    Not to worry!

    Watch this Excel Video Tutorial, as I demonstrate how to answer my viewer’s query and also give you some “solid tips” to get the results that you need when using Excel!

    Watch this Excel Video Tutorial in HD Mode on my YouTube Channel

    Here is the link to my YouTube video channel for this video.

    Learn how to “Quickly Create Pivot Table Reports!”

    Share and Enjoy:
    • Add to favorites
    • Facebook
    • Twitter
    • Technorati
    • Print
    • email
    • Digg
    • StumbleUpon
    • del.icio.us
    • Yahoo! Buzz
    • Google Bookmarks
    • Orkut
    • SphereIt
    • Sphinn