Home Archives for Excel Video Training
May 13, 2011 5 Comments

How to Apply Conditional Formatting to a Pivot Table in Excel

A Pivot Table is a great way for you to summarize and present the information contained in your data set. When you apply conditional formatting to your Pivot Table, you can “visually” spot important trends or anomolies in your data.

Improvements to Conditional Formatting

Conditional Formatting in Pivot Table

Condition Formatting for Above Average

Beginning with Excel 2007, Conditional Formatting has been dramatically improved. In addtion to pre-built formula rules, you can also apply Data Visualizations (Icon Sets, Data Bars and Color Scales).

Conditional Formatting for Above Average

In this lesson, I apply the pre-built “Above Average” rule. I want Excel to dynamically format the summary values that answer, “TRUE” to this question: “Is this value “Above the Average” in the range of cells selected?

This is the key to understanding how Conditional Formatting works. Special Formatting is applied only when the answer to your CONDITION (a rule or a formula) is TRUE.

Dynamically Filter Conditional Formatting

In this lesson, I use the Report Filter zone in my Pivot Table to see how conditions change when I change the time period in my report. Values that meet the Condition, “Above the Average,” receive the formatting that I established in my rule. Try this on your Pivot Table. It is a wonderful tool to use in an Excel Dashboard.

View This Video Tutorial in High Definition

Follow this link to watch this lesson in High Definition on my YouTube Channel, DannyRocksExcels.

My Extended Length Pivot Table Video Tutorials

I offer “free of charge,” a 24 minute in-depth video tutorial for creating your first Pivot Table in Excel. Follow this link to learn how to view it online or to download it to your computer.

Vist My New Online Shopping Site

My new, secure online shopping website – http://shop.thecompanyrocks.com/ – is now open. I invite you to visit and learn more about the new products that I have added.

How to Apply Conditional Formatting to a Pivot Table [ 6:01 ] Play Now | Play in Popup | Download (1642)
Link to this post!
Filed Under: Conditional Formatting, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , ,
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: , ,
    « Previous Page
    Next Page »