Home 2011 Archives for April 2011

Archives for April 2011

April 25, 2011 4 Comments

Free Video Recording of Excel Keyboard Shortcuts to Download

I have just published a 50 Minute Video Recording on which I demonstrate the most popular Keyboard Shortcuts for Excel. The great news – for you – is that you can download this video recording for FREE! If you prefer to view this online, you can do so – for FREE!

Download My Excel Workbooks

Download my Workbooks

Download Excel Workbooks

In addition, I have published the Excel Workbooks, Adobe PDFs and PowerPoint files that I use during this 50 minute video online. You can access them – for FREE!

I store them as “public documents” for you to download on my “Sky Drive” at Windows Live! Here is the direct link:

http://cid-644f2f857aa6afdc.office.live.com/browse.aspx/Public%20WebEx%20Documents/Excel%20Keyboard%20Shortcuts%20Part%201

In order to access these files, you will need a “free” Windows Live account (First Name, Last Name, email address). You can also use your HotMail account to login.

My Extended Length Video Recordings on WebEx

I have recently began to publish “extended length” video recordings at http://thecompanyrocks.webex.com – This 50 Minute video is my 5th recording on the site. I offer it to you “free of charge.” However, you must register (fitst name, last name, email address) with WebEx by Cisco before you can download the recording or view the recording online.

In this format, I can offer “specialized instruction” for Excel Pivot Tables, Macros, Public Speaking Courses, etc. I can also offer instruction in older (Excel 2003) and newer (Excel 2010) programs. In addition, I can create a specialized recording “just for you.” Contact me to learn about how to organize this!

Landing Page for More Information

Links for Downloads

Information about Links and Downloads

Rather than repeat what I have already written, here is the link to the special Landing Page that I have created for this 50 minute video recording. On the Landing Page, I have included all of the direct links to the download pages for the video, the Excel files, PowerPoints and Adobe Acrobat documents that I used on this recording.

I invite you to view or download these new resources that I have made available. When you have a chance send me your comments, questions and suggestions for future recordings!

Danny Rocks

The Company Rocks

Watch This Video Lesson in High Definition

Click here to go to my DannyRocksExcels YouTube Channel where you can view this tutorial in High Definition.

Free Video Recording of Keyboard Shortcuts to Download [ 8:51 ] Play Now | Play in Popup | Download (1317)
Link to this post!
Filed Under: Excel Training, Keyboard Shortcuts, Podcast Tips & Timesavers, Video Training Courses, WebEx by Cisco Tagged: , , , , , , ,
April 23, 2011 Leave a Comment

Create an Accounts Payable Summary Report in Excel

Accounts Payable Status Summary

Accounts Payable

Here is another video lesson that I created to answer a question from one of my viewers. In this case, the request is for help in creating an Accounts Payable Summary Report in Excel.

Formula to Create Label for Status

My viewer wants to know how to create one formula that will report the status of an Invoice as “Paid,” “Pay Now,” or “Delay.” To do this I “nested” one IF() formula inside another IF() formula. I used the ISBLANK() function as my first “logical test” in the formula. ISBLANK returns either TRUE or FALSE, so it is a good Function to become familiar with.

One of my “best practices” when learning how to “nest” IF Formulas, is to create each one separately – to test the results – and then copy and paste them into the combined formula.

Use SUMIF() Function to Total Status

Now, that your formula returns a status label for each invoice, you can use the SUMIF Function to search in the Named Range “Status” for the cells that match the criteria (Paid, Pay Now, Delay) and then Sum the Invoice Amounts for each category.

Watch This Video in High Definition on YouTube

Here is the link to my YouTube Channel, DannyRocksExcels, where you can watch this in High Definition.

Download this Excel Workbook

Follow this link to go to my Public SkyDrive to download this workbook to practice with.

Create an Accounts Payable Report in Excel [ 8:15 ] Play Now | Play in Popup | Download (1680)
Link to this post!
Filed Under: Excel Financial Tools, Excel Functions, Excel Training, SUMIF and SUMIFS Functions Tagged: , , , , , , , ,
April 22, 2011 Leave a Comment

How to Use SUMIF in Excel to Total Year-to-Date Sales

One of my viewers asked for my help to use the SUMIF Function in Excel to total Year-to-Date (YTD) Sales. Generally, the SUMIF Function is very easy to set up and use. However, when you need to refer to a “date” in the criteria argument, there is a “Got’cha” step.

How to Write the Formula for Year-to-Date Sales

=SUMIF(Date, “<=”&TODAY(), Sales) Notice how the <= comparison operator is enclosed within ” ” (Quotation Marks) and I use the & (ampersand) to join the TODAY() Function. Be careful when you write this fomula. In my experience, when I try to write THIS formula in the “Functions Argument” Dialog Box, I almost always get an Error Message.

Create and Use Named Cell Ranges in SUMIF Function

As a best practice, I encourage you to create and use “Named Cell Ranges” in your formulas. This is especially helpful with the SUMIF, SUMIFS, COUNTIF, COUTIFS functions because you are usually looking inside extensive ranges of data.

Another Way to Total Year-to-Date Sales

For many years, I did not realize that there was a SUMIF Function. So, I used to create an extra column and write to formulas to compute the YTD Sales. If you do want to or need to see YTD sales in each cell, I show you how to do this by writing just one formula.

Watch Lesson in High Definition

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

Download My Free Video Lessons on Pivot Tables

Click here to learn about my 24 minute video recordings “Introducing Pivot Tables.” I have two versions – One for Excel 2003 and one for Excel 2007. I am offering both of them as a “free of charge” downloadable product!

How to Use the SUMIF Function to Total Year to Date Sales [ 5:09 ] Play Now | Play in Popup | Download (1424)
Link to this post!
Filed Under: Date Functions and Formats in Excel, Excel Training, Podcast Tips & Timesavers, SUMIF and SUMIFS Functions Tagged: , , , , , ,
April 19, 2011 Leave a Comment

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

    Link to this post!
    Filed Under: Download Files from Video Lessons, Excel Training, Integrating MS Office Programs, Pivot Tables, WebEx by Cisco, WebEx Recordings & Webinars Tagged: , , , , , ,
    April 16, 2011 Leave a Comment

    Using Paragraph Styles in Word to Improve Readability

    50 Best Tips for Word

    50 Best Word Tips

    I am proud to announce the launch of a new series of Video Tutorials for Microsoft Word!

    In addition to the major changes in the user interface from Word 2003 to Word 2007 and Word 2010, there was another change that many people who use Word on a regular basis have some difficuly in understanding:

    In Word 2007 the default setting adds 10 pts of space after each paragraph. This means that you only have to hit the “Enter” key once at the end of each paragraph. By contrast – In Word 2003, the default setting is 0 pts of space when you hit the “Enter” key. That is why – for years – we have developed the habit of hitting the “Enter” key twice at the end of a paragraph.

    In addition, in Word 2007 and Word 2010, there is an automatic 1.15 line spacing within each paragraph versus the “single line spacing” in Word 2002.

    More “White Space” on the Page

    As a result, the pages in Word 2007 and 2010 appear to have more “breathing room” because more “white space” has been built in to the default paragraph settings. Learn to take advantage of this to make it easier for your readers to quickly “scan” you pages – to quickly find out “what you have to offer them!”

    I know that you will pick up many great tips during this video tutorial. One of them is to use the Keyboard Shortcut Ctrl + Shift + S to bring up the “floating” Apply Styles Dialog Box.

    Getting Names and Addresses to Fit on Labels in Word 2007

    Do you have difficulty getting your “Address Block” to print correctly when you perform a Mailing Label Merge in Word 2007? Many people do. Watch this lesson to learn how to make 2 changes to ensure that your names and addresses print correctly on your labels the first time – and everytime!

    New Extended Length Video Tutorials on My WebEx Site

     I have just posted two extended length, in-depth Word Video Tutorials at http:thecompanyrocks.webex.com

    Follow these links to view online or download:

    Watch This Video Lesson in High Definition on YouTube

    Follow this link to watch this video lesson on my YouTube Channel – DannyRocksExcels

    Using Paragraph Styles in Word to Improve Readability [ 9:41 ] Play Now | Play in Popup | Download (954)
    Link to this post!
    Filed Under: Podcast Tips & Timesavers, Word Tips Tagged: , , , , , , , ,
    April 14, 2011 2 Comments

    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.

    Link to this post!
    Filed Under: Excel Training, Podcast Tips & Timesavers, WebEx Recordings & Webinars Tagged: , , , , , ,
    April 13, 2011 Leave a Comment

    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

    Link to this post!
    Filed Under: Business Practices, Excel Training, Pivot Tables, WebEx Recordings & Webinars Tagged: , ,
    April 10, 2011 Leave a Comment

    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!

    Create a Web Query in Excel [ 6:11 ] Play Now | Play in Popup | Download (1008)
    Link to this post!
    Filed Under: Excel Training, Import Data into Excel, Podcast Tips & Timesavers Tagged: , , , , , ,
    April 9, 2011 Leave a Comment

    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!

    Functions to Analyze Loan Payments in Excel [ 8:05 ] Play Now | Play in Popup | Download (1170)
    Link to this post!
    Filed Under: Download Files from Video Lessons, Excel Financial Tools, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , , ,
    April 7, 2011 Leave a Comment

    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.

    Create a Link to an Excel Worksheet Name in a Cell [ 6:06 ] Play Now | Play in Popup | Download (1667)
    Link to this post!
    Filed Under: Download Files from Video Lessons, Excel Formulas, Excel Functions, Excel Training, New in Excel 2007, Podcast Tips & Timesavers, Workbooks and Worksheets in Excel Tagged: , , , , , , ,
    Next Page »