Podcast Tips & Timesavers | The Company Rocks

Danny October 21, 2010 Leave a Comment

Learn How to Protect Your Excel Worksheets

Protecting your Excel worksheet is a two-step process. Understand that, by Default, all cells in the worksheet are “locked.”

1) Your first step is to select the input cells that you want your end-user to be able to type in or edit and “unlock” them. That is the “got’cha” step.

2) Next, you go to the Review tab on the Ribbon – in Excel 2007 – and apply the Protection options that you want to be in place for the worksheet.

I like to apply protection to any Excel worksheet that I intend to distribute to end-users. I want them to be able to interact with the data, but I do not want them to be able to change the formulas and formatting that I worked hard to put in place.

Understanding the need to “unlock” input cells while “locking” formula cells is the key to successfully protecting your Excel worksheets. I walk you through two examples in this short video lesson. In addition, I show you how to protect your entire workbook – with or without a password.

This is one of the tips that I include on my DVD, “The 50 Best Tips for Excel 2007.” Click here to purchase it today!

Learn how you can “Master Excel in Minutes – Not Months!”

Share and Enjoy:

Learn How to Protect Your Excel Worksheets [ 6:31 ] Play Now | Play in Popup | Download (561)

Filed Under: Excel Basics, Excel Training, Podcast Tips & Timesavers, Workbooks and Worksheets in Excel Tagged: Excel Video Training, Protect Excel Worksheet, Video lessons for Excel

Danny October 20, 2010 1 Comment

Create Interactive Excel Forms Using Option Button Controls

This is the third in my series of video lessons that show you how to add Form Controls to an Excel worksheet. Option Buttons, inside a Group Box, are a great way to make your forms “interactive” – to give your end-user multiple options. For example, a choice of shipping methods for their order. Or, to see how different “down payment” options effect their mortgage payments.

The key to understanding how Excel Form Controls operate is to see how the value in the “cell link” changes when you choose an option. The cell link for an Option Button translates a “text expression” into a numeric value. For example, “Surface” translates to “1” for a shipping option.

In this Excel Training Video, I introduce the =CHOOSE() Function. It is rare that I find a client who has ever used this function. Once you see how useful it is, you will start to use it instead of struggling through multiple =IF() Functions. It is really a great “hidden gem” of an Excel Function!

Click on this link to go to my Archive of Excel Video Lessons on Forms.

You can also find additional groups of related videos by selecting a “Category” from the drop-down menu on the right side bar of any page on my website.

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:

Create Interactive Excel Forms Using Option Button Controls [ 8:33 ] Play Now | Play in Popup | Download (1641)

Filed Under: Excel Forms, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: "What-if" Analysis in Excel, Cell Link in Excel Forms, Choose Function in Excel, Excel Form Controls, Excel Functions, Excel Video Training, Lookup Functions in Excel

Danny October 18, 2010 Leave a Comment

Create User Friendly Excel Forms Using Spin Button Controls

An Excel Form is great way to distribute an Excel worksheet to your customers or field representatives. Most of the time, you do not want them to type values into a cell. You want to make this Form as “user friendly” as possible! You want to add “Spin Buttons” to your form, so that the user can simply click to change cell values up or down in the increments that you want to display.

A perfect example: A Mortgage Loan Scenario where – by clicking the Spin Buttons – your client can see what their monthly payment will be. This is my second lesson covering Excel’s Form Control Tools. Watch as I demonstrate how to “work around” the Form Controls limits of Maximum Amount for a Spin Button (30,000) and also how to increment interest rates by 1/4 of a Percent.

From personal observation, I know that many people start to create an Excel Form and then just give up in frustration. I did too – in the beginning! Watch me demonstrate how to avoid the traps of the “got’cha” steps in this short Excel Training Video.

Check out the great tips that I offer on my DVD, “The 50 Best Tips for Excel 2007.” Click here to start shopping.

Learn how you can “Master Excel in Minutes – Not Months!”

Share and Enjoy:

Create User Friendly Excel Forms with Spin Button Controls [ 8:54 ] Play Now | Play in Popup | Download (1298)

Filed Under: Excel Financial Tools, Excel Forms, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: "What-if" Analysis in Excel, Cell Link in Excel Forms, Excel Form Controls, Excel Video Training, PMT() Function, Video lessons for Excel

Danny October 16, 2010 Leave a Comment

How to Add a Check Box Control to an Excel Form

Judging by the comments that I get from many clients, adding a “Check Box Control” to and Excel Form is not as easy as it appears at first glance.

And, I agree! I, too, struggled with adding Form Controls in Excel. Most of the explanations that I read seemed to add more confusion than clarification!

Spin Button Controls

That is why I created this Excel Training Video: to lead you through the process; to save you some time and to ease your frustration.

Key Points to Remember:

In Excel 2007, be sure to display the “Developer Tab” on the Ribbon in order to to Insert any Form Control.
Choose the Controls in the “Forms Controls” and NOT in the Active X Controls.
After you “draw” the Control Box on your Form, be sure to select a “Cell Link” to enable the Check Box Control.
Remember that a Check Box Control can only be applied when the result of the Formula or Option is a Logical Value – either a result of “True or False.”

Don’t be scared off! I find that this is an Excel concept that is better presented “visually.” I struggled for some time when I “read” how to add form controls.

So … I welcome your thoughts – “True or False.” Did I make this easy for you to understand?

Please feel free to add your comments below!

Watch My Video on YouTube

Follow this link to watch my tutorial on my YouTube Channel – DannyRocksExcels

Watch Video Now

Master Excel in Minutes Training Resources

I have created a series of extended length Excel Training Videos. I invite you to visit my secure online shopping site to:

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:

How to Add a Check Box Control to an Excel Form [ 10:15 ] Play Now | Play in Popup | Download (1315)

Filed Under: COUNTIF and SUMIF Functions, Excel Forms, Excel Training, Most Viewed Excel Videos, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: Cell Link in Excel Forms, Conditional formatting in Excel, Excel Form Controls, Excel Forms, Excel Formulas, Excel Video Training, PMT() Function, Tips and Time Savers, Video lessons for Excel

Danny October 10, 2010 1 Comment

Use an Excel Data Table to See Effect on Multiple Formulas

Excel Data Tables are part of the “What-if” Analysis group of tools and commands. In this lesson, I demonstrate how to use a one-input Data Table to show how substituting a series of inputs effects three separate formulas. This is a powerful – and easy – way to get answers to a series of “What-if” questions.

2-Input Data Table

Two-Input Data Table

I know that as I learned how to construct Data Tables, it was a lot easier for me to “see” how to set them up rather than to “read about” how they are set up. You can put your series of substitute values in either the “Column running down” or the “row going across” in the data table. To include the three formulas in the Data Table, I simply included “Links” to the formulas in the original assumptions table.

Learn More About “What-if Analysis Tools in Excel

I have identified “What-if” Analysis as one of my “Nine Essential Skills for Excel.” You can learn more about my 4 hour video tutorial package by clicking on either of these links:

Share and Enjoy:

Use an Excel Data Table to See Effect on Multiple Formulas [ 4:29 ] Play Now | Play in Popup | Download (839)

Filed Under: Excel Data Tables and Lists, Excel Formulas, Excel Training, Podcast Tips & Timesavers, What-if Analysis Tagged: "What-if" Analysis in Excel, Excel Data Tables, Excel Video Training, Scenarios in Excel, Video lessons for Excel

Danny October 7, 2010 Leave a Comment

Use the Visible Cells Command in Excel to Paste Subtotal Results

Earlier this week a client asked me to help her paste the Subtotal results to a new Excel workbook. She was frustrated and nearly hysterical because her manager needed the results in an hour. If you work with Subtotal reports in Excel I am sure that you share my client’s frustration.

The key to making this work is to use the “Visible Cells Only” command before you copy your selection. You can find this command on the “Find and Select” drop-down menu. If you like keyboard shortcuts, you can use Alt + ; for this command. However, remember to make your selection first!

Watch me demonstrate how this works in this short 4 minute 58 second Excel Video Lesson. You can also view it on iTunes and on my YouTube channel. This is one of the tips that I share on my DVD, “The 50 Best Tips for Excel 2007.”

Learn how you can “Master Excel in Minutes – Not Months!”

Share and Enjoy:

Use the Visible Cells Command to Paste Subtotal Results [ 4:58 ] Play Now | Play in Popup | Download (734)

Filed Under: Excel Training, Podcast Tips & Timesavers Tagged: Excel Subtotals, Excel Video Training, Format Subtotal Title lines, Paste as a Picture, Video lessons for Excel, Visible Cells Only in Excel

Danny October 3, 2010 Leave a Comment

My Video Podcast has been downloaded 4,800 times

Danny on iTunes

OK, so let me brag a little. I launched my Video Podcast, “Danny Rocks Tips and Timesavers,” in June 2010 on iTunes. When I looked at the number of downloads and views, I was amazed to see that the number had surpassed 4,800 – I am thrilled.

Thank you for your support and encouragement. And, one viewer has reviewed my podcast:

Customer Reviews

Concise training

by ChipAv

These are very specific and concise training modules on individual aspects of Excel and Powerpoint. (Also a few on meeting management and social media.) No nonsense, down-to-business, accurate, and professional. If one of these matches the skill you want to quickly acquire, you should like these.

I invite you to view, download, subscribe or review my video podcasts – either individually or as a whole. Here is the link to my iTunes video podcast:

http://itunes.apple.com/podcast/danny-rocks-excel-tips/id374923275 – If you like what you see, then pass this link on to a friend or colleague.

You can add your comments below and feel free to use one of the Social Media Icons at the bottom of this post.

Share and Enjoy:

Filed Under: Add Video to Your Website, Business Communications, Excel Training, Podcast Tips & Timesavers, Powerpoint Techniques, Tips and Time Savers, Word Tips Tagged: Communications, Danny Rocks Podcast, Excel Video Training, MS Excel Training, Public Speaking, Tips and Time Savers, Tips and TImesavers Podcast, Video lessons for Excel

Danny October 1, 2010 3 Comments

How to Create a Pivot Table Year-to-Year Comparison Report

One of my viewers asked for my help in creating and Excel Pivot Table Report. She wants to compare the total number of units shipped during three time periods: the years 2008, 2009 and 2010. The trick to producing this report is to drag the field to be summarized – Units Shipped – to the Values Area three times. Then, you change the Value Field Settings to first Sum the Units, then to compare the change in units year-over-year and finally to express this as a percentage of change. This creates a Year-to-Year Comparison Report.

This is a tip that is best demonstrated visually. So, I invite you to watch this 6 minute and 55 second Excel Video Training Lesson.

If you do not have the time to watch the video now, you can use my RSS Feed to deliver it to your computer or you can subscribe to my Video Podcast on iTunes to watch it later.

Learn how to “Quickly Create Pivot Table Reports and Charts”

Share and Enjoy:

How to Create a Pivot Table Year Over Year Comparison Report [ 6:55 ] Play Now | Play in Popup | Download (3302)

Filed Under: Excel Financial Tools, Excel Training, Pivot Tables, Podcast Tips & Timesavers, Video Response to Viewers Tagged: Excel Pivot Tables, Excel Video Training, Group Dates in Pivot Tables, Pivot Table Drop Areas, Show Values As in Pivot Tables, Summarize Pivot Table Fields, Video lessons for Excel

Danny September 29, 2010 2 Comments

How to Display Numbers During a PowerPoint Presentation

How many presentations do you “sleep through” – where the presenter displays slide after slide packed with numbers? Probably more that you wish – unless you need to catch up on your sleep!

Numbers are powerful – they help to support your point of view; they help to make your case. But, don’t overwhelm your audience with numbers, numbers, numbers. Don’t make the assumption that “the numbers speak for themselves.” If they do, then why do we need to hire you to advance the slides? Just send the numbers to us. Or… include them in your handout.

If you are going to “present” numbers, you need to direct the discussion of those numbers so that the audience can see and understand the trends that you are pointing out. One effective way to do so is to use Custom Animation in PowerPoint to introduce one chart series at a time.

Watch this short 5 minute video as I share my best practice tips for displaying financial numbers during a PowerPoint Presentation.

Bonus: You can read – and download – the article that I published on this topic. Click here to read “Five Tips for Displaying Numbers During a PowerPoint Presentation” on www.exinearticles.com

Share and Enjoy:

How to Display Numbers During Presentations [ 5:03 ] Play Now | Play in Popup | Download (1235)

Filed Under: Communications, Excel Training, Integrating MS Office Programs, Podcast Tips & Timesavers, Powerpoint Techniques, Presentations Tagged: Charts in PowerPoint, Combine Excel and PowerPoint, Custom Animation, PowerPoint Presentations, Presenting Financial Results, Public Speaking

Danny September 24, 2010 Leave a Comment

List of My Most Popular Posts and Pages

Summer – in the Northern Hemisphere – is now behind us. I went back to survey the videos and pages on my website that had the most views during the Summer of 2010.

Here is a listing of my “Top 10 Video Posts” and my “Top 5 Pages.” Click on the Links for each title to go directly to the Video Lesson or Page.

The “Top 10 Video Posts” on The Company Rocks Website:

The “Top 5 Pages” Viewed on The Company Rocks Website:

The Danny Rocks Tips and Timesavers Podcast

During the Summer of 2010, I started my own video podcast at the iTunes Store. These podcasts are free of charge. The video resolution is optimized for viewing on an iPad, iPod or other portable media device.

Click here to go to the list of video podcasts that I have posted.
I invite you to subscribe to my podcast so that you can receive the latest installment automatically.

The DannyRocksExcels Channel on YouTube

Over the past two years I have posted 104 video lessons on my “DannyRocksExcels” Channel on YouTube.

Click here to go to my channel to view, download and share my Excel video lessons with your friends and colleagues.

The DannyRocksPowerPoint Channel on YouTube

I maintain a separate channel on YouTube – “DannyRocksPowerPoint”– to post my PowerPoint Tips and Narrated PowerPoint Presentations. I invite you to vist my channel to view, download and share these videos with your friends and colleagues. Click here to go to my YouTube Channel.

“The 50 Best Tips for …” series of DVDs

I offer five DVDs for sale on this website. Click here to start your shopping cart. I guarantee your satisfaction. If you are not 100% satisfied with my DVDs, I will refund your purchase with no questions asked!

You can learn how to “Master Excel in Minutes – Not Months!”

Here is a listing of the DVDs that I offer for sale. Click on the link for each title to go to the list of the tips that I offer on each DVD:

Share and Enjoy:

Filed Under: Excel Training, Most Viewed Excel Videos, Podcast Tips & Timesavers, Powerpoint Techniques Tagged: "What-if" Analysis in Excel, Consolidate Excel data, Excel Video Training, Group Dates in Pivot Tables, Hide Details in Pivot Tables, MS Excel Training, Tips and Time Savers, Video lessons for Excel, VLOOKUP Function

Danny Rocks “Training You to Excel”

Learn How to Protect Your Excel Worksheets

Create Interactive Excel Forms Using Option Button Controls

Create User Friendly Excel Forms Using Spin Button Controls