Home
March 20, 2013 2 Comments

2.9 Million Views for My YouTube Channel – DannyRocksExcels

2.9 million viewsThis morning, I passed the 2.9 Million views milestone for my DannyRocksExcel’s YouTube Channel!

Thank you to all who have supported my Excel tutorials since I began posting them in August, 2008.

Top 10 Excel YouTube Videos Year-to-Date in 2013

Here are the Top 10 Videos that have been viewed so far (January 1 – March 21) in 2013. Click on the hyperlink to watch the video(s) on YouTube:

  1. How to Create an Interactive Pivot Table Chart - 65,095 Lifetime views
  2. How to Use Advanced Filters in Excel - 84,557 Lifetime views
  3. How to Add a Check Box Control to an Excel Form - 87,327 Lifetime views
  4. Compare 2 Excel Lists to Spot the Differences – 84,325 Lifetime Views
  5. Use Excel’s Built-in Data Form to Edit & Filter Records – 65,325 Lifetime views
  6. Use the Solver Tool in Excel – 46,035 Lifetime views
  7. How to Use an Excel Data Table for “What-if” Analysis – 72,694 Lifetime views
  8. How to Merge Multiple Excel Workbooks to a Master Budget – 104,376 Lifetime views
  9. How to Create a Summary Report from an Excel Table – 38,502 Lifetime views
  10. Import Excel Data into Microsoft Access – 54,601 Lifetime views

Quickly Improve Your Excel Skills

I have created many Video Tutorial Packages that I offer for sale at my secure online shopping website – http://shop.thecompanyrocks.com I invite you to visit and preview my training resources for MS Excel and the other programs in the Microsoft Office Suite.

The “Special Bundles” of videos are very attractively priced!

 

 

 

Link to this post!
Filed Under: Access Tips and Timesavers, Conditional Formatting, Excel Data Tables and Lists, Excel Forms, Excel Training, Most Viewed Excel Videos, Pivot Tables, What-if Analysis, Work with Multiple Worksheets, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
February 12, 2013 Leave a Comment

How to Edit an Excel Macro that You Recorded

Edit VBA Code for MacroThis is Part 3 in my series, “How to Record, Run, and Edit Macros in Excel.” In this episode, I demonstrate how to open up the VBA (Visual Basic for Applications) Code window and then:

  • Step Into each line of the code – using the F8 Keyboard Shortcut – to examine how the Macro Behaves
  • Use the ‘ (apostrophe) to “remark out” one line of the code. This will quickly change the macro to use Relative Cell Referencing rather than Absolute Cell Referencing!
  • Edit one line of the VBA Code to change the “text” that you want your macro to enter.

I created this series of Excel Tutorials to assist my viewers to “get started on the right path” when they first start to experiment with the power of Macros in Excel. I welcome your feedback on this – or any – Excel tutorial that I have published.

Step Into a Macro

When you run a Macro, it is impossible to “trouble shoot” or examine the individual actions that occur. In this lesson, I demonstrate how to use “Step Into” mode – with the keyboard Shortcut F8 – to run each line of the VBA Code step-by-step. Using Step Mode with any macro is a great way to learn how top efficiently write, record, or edit a macro.

Secure Online Shopping for Excel Training Resources

I welcome you to visit my Secure Online Shopping Site – http://shop.thecompanyrocks.com – where you can learn about the range of video training resources that I offer.

Watch Excel Tutorial in High Definition

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

View Excel Video Tutorial Now

Link to this post!
Filed Under: Excel Training, Excel VBA Macros Tagged: , , , , , ,
February 6, 2013 Leave a Comment

Excel Macros: When to Use Relative Cell Referencing When Recording

This is Part 2 of my new series of Excel Tutorials: “Recording, Running, and Editing Excel Macros.” In this episode, I demonstrate how and when to use either Relative Cell Referencing or Absolute Cell Referencing.

Best Practice: Chose Relative Cell Referencing

Chose Type of Cell Referencing for Macro

Default Setting for Recording Macros – Absolute Cell Referencing

In my experience, the first major mistake Excel Users make when recording a Macro – they neglect to turn on Relative Cell Referencing. In the majority of Macros, you are better off using Relative Cell Referencing. There are a few – specialized – reasons to use Absolute Cell Referencing. However, they are in the distinct minority.

Select Starting Cell Before Recording a Macro

Once again, in my experience, I find that too many Excel users fail to select their “Starting Cell” before they click on the Record Macro Button. My Best Practice: “Minimize Cell Movements When Recording a Macro!”

Whenever Possible Use Ctrl + Enter When Recording a Macro

My Best Practice: “Minimize Cell Movements When Recording a Macro.” I recommend that your use Ctrl + Enter when entering or editing a value or formula in a cell. This procedure will keep the focus on the active cell. This will also minimize extraneous cell movements while recording your Macro. When you take steps to avoid extraneous cell movements, your Macro will be easier to edit or update.

Demonstration of Absolute vs. Relative Cell Referencing in a Macro

Watch my video tutorial to see the very different results that you get depending upon the type of cell referencing that you use when recording a Macro.

Secure Online Shopping at The Company Rocks

I invite you to visit my secure online shopping site – http://shop.thecompanyrocks.com  Take a few minutes to look at  - and , hopefully, purchase – one of the many video training resources that I offer. I guarantee that you will be 100% satisfied with my training materials. If not, I will refund your purchase with no questions asked!

Watch My Video in High Definition

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

Play My Video Now

Link to this post!
Filed Under: Excel Training, Excel VBA Macros Tagged: , , , , , , ,
February 4, 2013 Leave a Comment

The 4 Steps to Take Before Recording an Excel Macro

Prepare to Record a Macro

The 4 Steps to Take Prior to Recording a Macro

With this video tutorial, I begin a new series – “How to Record, Run, and Edit a Macro in Microsoft Excel. I’ve designed this series to introduce Intermediate Level Excel Users to the “ins and out” of working with Macros.

Here are the “4 Steps to Take Before Recording an Excel Macro:”

  1. Add the Developer Tab to the Ribbon. I show you how to do this in both Excel 2010 and Excel 2007.
  2. Set the Proper Level for Macro Security. You do this from the Trust Center. My recommendation is to choose “Disable Macros with Notification.” – While this is a somewhat cryptic description, it is similar to the “Medium Level” Macro Security Settings in Excel 2003.
  3. Establish Trusted Locations. Here, you inform Excel that the Workbooks in this Folder (and Subfolders) contain Macros that are “safe” to open with the Macros Enabled.
  4. Unlike earlier versions of Excel, if a workbook contains a Macro, you MUST save it with the file extension .XLMS (Macro-enabled Workbook.) This applies to Excel Version 2007 and higher.

By taking these 4 steps, you will ensure that the Macros that you create will open safely and that they will run smoother.

Shop at The Company Rocks Online Website

At my secure online shopping website – http://shop.thecompanyrocks.com – I offer a wide range of video training resources. I invite you to click on this link and take a look at my products.

Watch Video in High Definition

Follow this link to view my Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Watch Video Now

Link to this post!
Filed Under: Excel Training, Excel VBA Macros Tagged: , , , , , ,
January 4, 2013 Leave a Comment

How to Work with Numeric Values in an Excel Pivot Table

Value Field Settings for Pivot Table

Value Field Settings for Pivot Table

One of my clients contacted me with a question about Excel Pivot Tables. Specifically, about how to control a field – Employee ID – that contains numeric values.

Beginning with Excel 2007, when you click – to add – a field that contains numeric values, it is automatically placed in the Values Zone of the Pivot Table and the Sum Function is used to Subtotal it. That is what was troubling my client.

It is really easy to change the location for any field in the Pivot Table. In this case, I moved the Employee ID to the Row Labels area and everything worked out nicely.

Value Field Settings for Pivot Table

In my experience, I use the commands in the Value Field Setting Dialog Box more than any other commands when working with Pivot Tables. Learning how to use the Value Field Settings Commands is an Essential Skill when working with Pivot Tables. In this video tutorial, I demonstrate how to:

  • Change the function used to subtotal a field
  • Change the name of the subtotaled field
  • Change the numeric formatting for the subtotaled field
  • Move fields to different drop zones in a Pivot Table
  • Expand and collapse entire Pivot Table fields
  • Remove fields from the Pivot Table

Video Training Resources for Sale

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to see the many video training resources that I make available.

Watch Video Tutorial in High Definition

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

Podcast Video [ 5:51 ] Play Now | Play in Popup | Download (1492)
Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , , , ,
December 13, 2012 Leave a Comment

Excel Pivot Tables Tutorials: My YouTube Playlist

I have been organizing the video tutorials on my YouTube Channel – DannyRocksExcels – into Playlists.

I quickly realized that I have more videos (70 +) that focus on Excel Pivot Tables than on any other topic. So, having a playlist for all 70 (and growing) Pivot Table Tutorials is a great help when you need to learn about Pivot Tables. Or, simply to refresh your memory about how to use a specific Pivot Table Tool.

Scope of My Pivot Table Tutorials

I posted my first Pivot Table Tutorial on YouTube back on August 1, 2008. So far, that 7 minute Introduction to Pivot Tables in Excel 2003 has received over 90,000 views! Since then, I have created Pivot Table videos for Excel 2007 and Excel 2010.

Front Page of Online Shopping SiteIn-Depth Focused Training for Excel Pivot Tables

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to learn about the many training resources that I offer you.

 

Link to My Excel Pivot Tables Playlist

Follow this link to go to my Excel Pivot Tables playlist on YouTube. If you like this Playlist, I encourage you to share it with your friends and colleagues.

Excel Pivot Tables Playlist on YouTube

Link to this post!
Filed Under: Excel Training, Pivot Tables, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
December 11, 2012 Leave a Comment

How to Prevent Empty Cells from Displaying in a Pivot Table

IPivot Table Optionsn response to one of my viewers, I demonstrate how to prevent “empty cells” from displaying in a Pivot Table Report. In my experience, I have found that clients and colleagues who are unfamiliar with Pivot Tables tend to “doubt the numbers” when they see these “empty cells.”

What You Will Learn

In my video, I demonstrate:

  • How to use Pivot Table Options to show a Zero instead of “blanks.”
  • How to “drill down” to reveal the details for any Subtotal.
  • How to use a great Keyboard Shortcut to apply and clear Filters.

Learn More About Pivot Tables

Pivot Tables Video Tutorial

Pivot Tables Tutorial

I have published a 90 minute video tutorial focused exclusively on Pivot Tables and Pivot Charts. The package includes:

  • 9 Video Tutorials – total of 90 minutes of video instruction.
  • Step-by-Step Instructional Manual as a PDF for you to use as you watch the videos.
  • The Excel Worksheets that I use in each video lesson so that you can practice your new skills.

I make this video training resource available both on DVD-ROM and as a Downloadable Product. Versions are published for Excel 2010, Excel 2007, and Excel 2003.

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to learn more about all of my training resources.

Watch Video in High Definition

Follow this link to watch my video in High Definition on my YouTube channel - DannyRocksExcels.

Watch Video Now

 

 

 

Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , , , ,
November 21, 2012 Leave a Comment

How to Use Multiple Sorting Options in Excel

Sort Using Cell Icons

Sort based on Cell icons in Excel

Beginning with Excel 2007, the Sorting Commands gained several new features and became much more powerful. For example, you are no longer limited to three levels of sorting. You can sort using as many levels as you need. In addition, you can now sort by Cell or Font Colors and also sort using the new Cell Icons.

While I was training one of my clients last week, several questions about sorting came up. So, in this video I will demonstrate several of the new features as well as showing you how to take advantage of many sorting options that have been around for some time. For example, using a Horizontal Sort to quickly rearrange the order of your columns!

Sorting Options on Video Tutorial

Here are the sorting options that I demonstrate on my video:

  • Sort on Values using a Custom List
    • First, I use a built-in custom list (Months of the Year).
    • Next, I demonstrate how to create a custom list that you can use with AutoFill and also with custom sorting.
  •  Sort data based upon Cell Icon
    • Cell Icons were introduced in Excel 2007 as part of the major upgrade to Conditional Formatting.
  • Sort Horizontally to quickly rearrange the order of your columns (fields) in your data set.
    • This feature has been available for many years. However, in my experience, very few people are aware of its existence.
    • Once you  learn this technique, you can stop the tedious task process of Insert Column, Cut Column, Paste Column, Delete Column, etc.

Shop for Excel Training Resources

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com -   where I offer a range of video training resources for all of the Microsoft Office Programs.

Watch My Video in High Definition

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

How to Use Multiple Sorting Options in Excel [ 7:14 ] Play Now | Play in Popup | Download (1464)
Link to this post!
Filed Under: Conditional Formatting, Essential Excel Skills, Excel Training Tagged: , , , , , , , , , , ,
November 4, 2012 Leave a Comment

How to Use a Data Validation Formula When Creating a Budget

Use Formulas in Data Validation

Use a Formula in Data Validation

Recently, one of my viewers wanted to know which formula he could use to prevent an end-user from imputing a value that would exceed his budget. I created this video tutorial to demonstrate my response.

Use a Formula in Data Validation

I have identified Data Validation as one of the “Nine Essential Skills in Excel.” Here is one example – Using a Formula in Data Validation to prevent an input entry from exceeding a set budget amount

A Formula that Evaluates to TRUE

The “key” to understanding how use Formulas in Data Validation – When the formula answer is TRUE, the entry is accepted; When the answer to the formula is FALSE, the Error Message that you create prevents an invalid entry.

Remember, that in Data Validation, only the STOP style will prevent an invalid entry.!

Video Training Resources

9 Essential Skills for Excel 2010

9 Essential Skills for Excel 2010 Video Tutorial

I invite you to visit my secure online shopping website - http://shop.thecompanyrocks.com – to preview the many video training resources that I offer you.

Watch This Tutorial in High Definition

Follow this link to watch my Excel Tutorial in High Definition on my YouTube Channel - DannyRocksExcels

Learn How to Use Data Validation When Creating a Budget [ 10:43 ] Play Now | Play in Popup | Download (2494)
Link to this post!
Filed Under: Data Validation, Essential Excel Skills, Excel Formulas, Excel Training Tagged: , , , ,
October 27, 2012 1 Comment

Learn How Excel Stores and Formats Dates

Formatting Options for Dates in Excel

Multiple Formatting Options for Dates in Excel

In my experience, a high percentage of Excel users run into problems when working with Dates – and Times! In fact, I estimate that, during the past two weeks, I received 25 questions from viewers – they were asking for my help when working with Dates in their Excel workbooks.

Here, in this tutorial, I gathered together the most common problems that my viewers asked me help them to solve.. Gaining a firm understanding of how Excel Stores and Formats Dates is an Essential Skill that you must master!

Dates Stored as Serial Number

Dates are stored as a serial number in Excel. For example, if you type the number 1 into a blank cell and then change the formatting to a Short Date, you will see 1/1/1900 displayed. This is the date on which Excel began to keep track of time.  Dates that are entered correctly align to the right side of the cell.

Concepts Covered in this Tutorial

  • Use the =TODAY() Function – this date will automatically update.
  • Use the Keyboard Shortcut Ctrl + ; (semi-colon) to “Date Stamp” a cell – this is a static date.
  • Change the Formatting of a Date to e”General” – this reveals the actual serial number that Excel stores for the Date.
  • Apply Custom Date Codes to format cells that contain Dates.
  • Learn why it is essential that you always enter years using four digit codes – to avoid “the Century Break.”
  • Learn how to clear all formatting from cells before pasting or entering data. in them
  • Use the Text-to-Columns Wizard to convert “unfriendly” Date Text Labels to “usable” Numeric Date Values .
  • Tips for copying and pasting a range of cells formatted as USA Short Dates to a customized “European” Date Format.

Visit My Secure Online Shopping Website

I invite you to stop by my secure online shopping website – http://shop.thecompanyrocks.com - to take a look at all of th many training resources that I offer to you.

Watch Video in High Definition

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

Podcast Video [ 11:03 ] Play Now | Play in Popup | Download (1792)
Link to this post!
Filed Under: Essential Excel Skills, Excel Basics, Excel Training Tagged: , , , , , ,
Next Page»
Powered by WishList Member - Membership Site Software