Home Archives for Video lessons for Excel
May 11, 2011 Leave a Comment

How to Create an Interactive Pivot Chart in Excel

While I have created many Excel Video Tutorials demonstrating the power of interactive Pivot Tables during the past four years, I have NEVER created a video lesson focused on creating interactive Pivot Charts in Excel – until now!

Interactive Pivot Table Chart

Interactive Pivot Table Chart

Pivot Charts in Excel 2007

In this lesson, I show you how to create a Pivot Chart in Excel 2007. Starting with Excel 2007, there are several major changes:

  • By default, Pivot Charts are embedded on the same worksheet as the Pivot Table Report that is linked to the chart.
  • A new, PivotTable Chart Filter Dialog box is available – this works “in tandem” with the filters available in the PivotTable Field List” Dialog box.
  • It is very easy to “Move” a Pivot Chart to its own worksheet tab – or vice versa – to move the Pivot Chart to an existing worksheet.

 Pivot Charts and Pivot Table Reports are Connected

A Pivot Chart MUST be connected to a Pivot Table Report. Any changes that you make to the chart are reflected in the report and vice versa. If you filter the Pivot Table Report, the filter is automatically applied to the Pivot Chart. This is an important concept to understand. This is the reason why it is so easy to make BOTH Pivot Table Reports and Pivot Charts “interactive!”

Explore My Index of Free Excel Video Lessons

I was surprised to discover that I had not yet created a video tutorial for Excel Pivot Charts when I looked at my list of “free” Excel Video Lessons. This became the impetus for creating this lesson. I invite you to explore all of my free Excel Video Tutorials. Click on this link to go to my Index of Excel Video Lessons. I have organized the free video lessons by topic. Simply click on the title of any lesson and you will arrive at the posting and the video for that lesson.

Download Excel File for This Lesson

View this Lesson in High Definition

I invite you to view this Excel Video Tutorial in High Definition, Full-screen Mode on my YouTube Channel – DannyRocksExcel

Watch Video Now

 

Feedback Welcome

I always welcome your feedback. Let me know what you think about this – or any other – video lesson. Add your comments below!

Extended Length Video Training Resource for Pivot Tables

I have created 90-minutes video training resources for Pivot Tables. Follow this link to learn more about my “Master Excel in Minutes” series.

How to Create an Interactive Pivot Chart in Excel [ 9:12 ] Play Now | Play in Popup | Download (1587)
Link to this post!
Filed Under: Download Files from Video Lessons, Excel Training, Most Viewed Excel Videos, Pivot Tables, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: , , , , ,
May 9, 2011 2 Comments

How to Convert a Pivot Table to a Standard List

Watch this video tutorial in High Definition on YouTube

A viewer aksed for my help. She has a boss and a few customers who:

  1. Do not know how to create or use Pivot Tables
  2. Have no desire to learn how to create and use Pivot Tables
Convert Pivot Table to Standard List

Convert Pivot Table to List

Nevertheless, in this example, she is being asked to create a summary report – by Year and by Quarter – from @ 5,000 daily transaction records. The fastest way to create this report is with a Pivot Table. Now, to meet the demands of her boss and clients, she needs to convert this Pivot Table into a standard list that can be Filtered and Subtotaled.

Summarize by Year and Quarter

To create this Pivot Table report:

  • Add the “Date” field to the grid and then select a single date.
  • Group the Date field by Month, Quarter and Year.
  • Add and arrange the Customer and Invoice fields to the Pivot Table Grid.

Convert Pivot Table to Standard List

  1. Remove both the Subtotals and Grand Totals from the Pivot Table Report.
  2. In Excel 2007 & 2010, ensure that you are using either the Outline or Tabular report layouts – NOT the new “default” Compact layout.
  3. Also in Excel 2007 & 2010, remove the Field Headers and the + / – Symbols.
  4. Now, select the entire Pivot talbe (use Ctrl + A) and copy it to the clipboard.
  5. Use Paste Special Values to paste the Pivot Table Results.

Filling the Blank Cells in the List

As you can see, whenever you have “nested rows” in a Pivot Table you get a nice, clean report. However, in order to Filter and Subtotal this new list, you will need to “fill in the blank cells.”

  1. Select all of the values and all of the blanks in Column A.
  2. Use Ctrl + G (or F5) to open the Go To Dialog Box and choose Special.
  3. From the Go To Special choices choose “Blanks.”
  4. With all of the Blank Cells selected use this formula: = ↑
  5. Do NOT press Enter. Rather, press Ctrl + Enter and all of the Blank Cells are now filled in with the Customer Names!
  6. Finally use Copy, Paste Special, Values to complete this process.

Watch This Video on My YouTube Channel – DannyRocksExcels

Since this video tutorial is a little longer than usual, I have decided to post it as a video – in High Definition – on my YouTube Channel. Click here to view it or copy and paste this link into your browser:

Link to YouTube Video:   http://youtu.be/IwReVDJ7BAI?hd=1

Download This Excel Workbook

Link to this post!
Filed Under: Excel Formulas, Excel Training, Pivot Tables, YouTube Videos by Danny Rocks Tagged: , , , , , , , , ,
May 4, 2011 Leave a Comment

How to Create Custom Headers and Footers for Printing Excel Worksheets

How many times have you wasted time and paper when printing out an Excel Worksheet? Did you:

  • Forget to include the page numbers in the footer of each printed page?
  • Forget to repeat the column titles for the second and succeeding pages?
  • End up with “orphaned” columns that are disconnected from the main worksheet?
Headers and Footers in Excel

Headers and Footers in Excel

And the list goes on and on and …

Most Excel users, in my experience, spend very little time “previewing” their Excel worksheet prior to printing it – and wasting time and money. And also leaving a poor professional image in the minds of their clients and colleagues. 

Don’t let this happen to you!

Page Layout View

Page Layout View is a “working” view that was introduced in Excel 2007. I spend most of this video tutorial showing you how to get the most out of Page Layout View, including the placeholders for Header and Footer Elements. Personally, I spend a considerable amount of time when I am creating a new worksheet working in Page Layout View. Here, unlike in earlier versions of Excel, you can see how adding an additional column or two will effect the printed page. You can also verify that you company logo and other elements are properly positioned – while you are working inside the worksheet! For me, this view is indispensable!

Watch Video in High Definition on YouTube

This Excel Video Tutorial is a little longer than usual – 10 minutes and 14 seconds – and it contains more graphic images than usual. So, rather than embed this as an MP4 Video, I have decided to provide the link to this video on my YouTube Channel – DannyRocksExcels. Here is the link:

 http://youtu.be/X_Pb0KNFYak?hd=1

I produced this in High Definition, Full Screen Video Mode. This video is best watched on YouTube in High Definition. Let me know what you think.

Master Excel in Minutes – Not Months!

Learn how you can quickly learn how to get the most out of Excel – in the least amount of time! Click here to find out how!

Link to this post!
Filed Under: Excel Training, Workbooks and Worksheets in Excel, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
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 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: , , , , , ,
    « Previous Page
    Next Page »