Home Archives for Tips and Time Savers
October 10, 2013 Leave a Comment

How to Copy and Paste Cell Formatting Only in Excel

For the past several months, I have been teaching MS Excel online for the Continuing Education Courses at several major colleges here in the USA. It has been a great experience!.

Splash Page for Excel with ExcelOne of my clients, VanderCook College of Music, has recently adopted Schoology for their Learning Management System (LMS). This is a robust framework for presenting course work and interacting with my students!

Now, when I assign my weekly assignments to my class, I can quickly spot the common “stumbling blocks” or questions that many student have.

While I was grading my Week #1 assignments, I found that many students were unfamiliar with Excel’s AutoFill Option for “Filling the Formatting Only” in a contiguous range of cells. So, as a start to a class discussion, I created this YouTube Excel Tutorial where I demonstrate three ways to “Paste the Formatting Only” from one location to multiple locations in an Excel Worksheet.

Before I presented my course work for Week #1, I surveyed my class and realized that the majority of students were either novice or “rusty” users of Excel. Nevertheless, I find that Excel users at all levels of experience will benefit from this @ 7 minute video tutorial.

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

Watch this Excel Video Tutorial in High Definition on my YouTube Channel – DannyRocksExcels!

Click below to watch this video now on YouTube!

Link to this post!
Filed Under: Essential Excel Skills, Excel Training, MS Excel Tips, MS Excel Training Tagged: , , , , , ,
October 14, 2012 1 Comment

Survey Results – Which Version of Excel Are You Using

Survey Results - Which Version of Excel

Which Version of Excel Are You Using?

This week, I surveyed the Excel Users in my mailing list. I wanted to find out:

  1. Which version of Excel they are currently using at work?
  2. Are they using the same verion of Excel at Home as they use at work?
  3. Do thy plan to upgrade to a later version of Excel in the Next 12 Months?

Survey Results

A solid majority of the people who opened my survey, completed the survey.

Which version of Excel are you currently using at work?

54% responded that they are using Excel 2010 at work, while 36% are using Excel 2007. !0% are using either Excel 2003 or another version.

Are you using the same version of Excel at Home as well as at work?

68% responded “Yes,” whil 32% responded “No.”

Do you plan to upgrade to a newer version of Excel during the next 12 months?

23% responded “Yes,” while 23% responded “No.” 9% were not sure.

My Observations

I am pleased that almost all of the Excel users who completed the survey are using Excel 2007 or higher. This was very important information for me to gather. I really needed to determine if there was a market for me to continue to publish my video tutorials for Excel 2003 – the answer is “No!

The results for my second question – “Are you using the same version of Excel at Work and at Home?” – were about what I expected.

As to plans to Upgrade to a later version of Excel – We will have to wait and see what Excel 2013 has to offer and how many users will be purchasing new computers with Excel 2013 already installed.

Take My Survey Online

If you wish, click on the link below to take this survey yourself. No personal information will be collected and you not be added to my mailing list.

Click on this link to take my survey – Which Version of Excel Are You Using?

Thanks to everyone who responded!

What are your opinions and observations? Add your comments below!

Link to this post!
Filed Under: Excel Training, Survey Results Tagged: , , , ,
October 8, 2012 Leave a Comment

Two Glowing Reviews for my Excel Video Tutorials

Today, two of my customers posted glowing reviews for my Excel Video Tutorials. Thank you! Thank you! Thank you!

9 Essential Skills for Excel 2010

9 Essential Skills for Excel 2010 Video Tutorial

“Nine Essential Skills for Excel 2010”

“In my opinion the 9 Essential Skills for Excel 2010 , is an outstanding training video. It covers a lot of the required intermediate requirements. One thing that really stands out for me is the method of presentation. Danny always:

  • a) explains what he is going to do in simple language 
  • b)demonstrates what he has just explained
  • c)shows you where to find the icons on the ribbon
  • d)shows you the keyboard shortcuts to use(they actually appear on the video as they are used.

The video also had a good introduction to the value of using tables. Like all of Danny’s videos this should be a fantastic success.

– John J, Canada

Pivot Tables for Excel 2003

“Excel 2003 Pivot Tables to Summarize, Analyze, and Present Information”

“A mere few hours with your Excel 2003 course and I was able to
perform pivot table operations which had eluded me for over a year
using multiple other publications totaling nearly 1,500 pages: while those sources are terrific, each of us learns differently, your courses certainly fill that learning gap for me.

I deal with mostly with cumulative metrics, that is, incremental, week to week.

After two evenings of your course, I was able to build tables presenting the year, month, quarter & weekly totals of the multiple tools deployed, and, amazingly, display the incremental changes down to a week-to-week basis.

You had mentioned that you hoped I enjoyed the course, of course, I enjoyed the course – indeed, but after a year-plus struggle with several other sources, the *learning* from your course was the true reward!”

– Paul B., Colorado

Learn More About My Video Training Resources

I am confident that you will learn many valuable tips and techniques from my extended length Excel Video Training Resources.My Motto is:

“I work hard to make it easy for you to get the most out of Microsoft Excel”

Follow these links to learn more about my video training resources:

Link to this post!
Filed Under: Excel 2003 Video Lessons, Excel Basics, Excel Formulas, Excel Functions, Excel Training, MS Excel Training, Online Store for The Company Rocks, Pivot Tables, Video Training Courses Tagged: , , ,
September 11, 2012 1 Comment

How to Dynamically Color-Code Dates with Conditional Formatting in Excel

Conditional Formatting for Dates

Conditional Formatting for Dates

One of my viewers wants to learn how to “dynamically” color-code cells that contain dates. She is monitoring the frequency of contact with her clients and she wants Excel to automatically apply:

  • One background color to cells where it has been less than 60 days since last contact
  • A different color when the time frame is between 60 and 90 days
  • A third color when it has been over 90 days since the last contact with a client

Here, I demonstrate how she can do this using Conditional Formatting Rules. However, this will only work if you are using Excel 2007 or Excel 2010.

Understanding Dynamic Date Functions

In order to make this visualization “dynamic,” I make references to cells that contain the =TODAY() Function. This is a “volatile” function that updates automatically based upon the date in your computer system’s clock. I use this to classify the last date of contact with her clients.

Improvements in Conditional Formatting

Beginning with Excel 2007, you can now apply multiple rules that use formulas. Remember that when you use a formula, it must evaluate as “TRUE” in order to apply the formatting that you specify.

Formulas Used to Apply Conditional Formatting

Conditional Formatting Rules

Rules for Conditional Formatting

To make it easier for you to practice this skill, I have posted this table that details the formulas that I used for each of the three rules in my video tutorial.Note that with the middle condition, I use the =AND() Function. All logical tests must answer TRUE for the AND() Function to return TRUE.

It will take a little bit of practice for you to become comfortable with these formulas. As a Best Practice, I recommend that you create a table of formulas so that you can see how a “tweak” in your formula can turn a FALSE answer into a TRUE answer.

Related Video

Follow this link to Part Two in this Series. I demonstrate how to “toggle” Conditional Formatting On or Off by using a Check Box Control in Excel.

Watch this Video in High Definition

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

Get the Best Tips and Training for Excel

Click on this link to learn more about my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.” This link will take you to my secure online shopping website –  http://shop.thecompanyrocks.com

Subscribe to my Video Podcast on iTunes

Here is the link to go to my free Video Podcast, “Danny Rocks Tips and Timesavers” on iTunes.

 

 

How to use Conditional Formatting to Color-Code Dates in Excel [ 8:49 ] Play Now | Play in Popup | Download (3143)
Link to this post!
Filed Under: Conditional Formatting, Date Functions and Formats in Excel, Excel Date and Time Functions, Excel Formulas, Excel Training, New in Excel 2007, New in Excel 2010, Podcast Tips & Timesavers Tagged: , , , , , , ,
September 8, 2012 Leave a Comment

Review for my Latest Publication – “9 Essential Skills for Excel 2010”

Main Menu Essential Skills for Excel

Main Menu for “9 Essential Excel Skills”

I just received a fabulous review, from one of my viewers, for my video tutorial, “9 Essential Skills for Excel 2010.”

Read this Review

Product Reviews

It’s really a master piece!!

Posted by Tamoghna on 8th Sep 2012

“I was a beta tester of “9 Essential Excel Skills- Excel 2010” by Danny Rocks. In one word this is an excellent resource for anyone who wants to master those essential skills which are required to use excel professionally. There are total 9 chapters which consist of a series of dense but brief video lessons.

The lessons have been planned in a careful way so that the viewers are introduced from simple to more complex topics.

Surely you are going to be amazed by the incredible picture and sound quality of the videos. I had a feeling as if I was watching an excel movie while putting my head phone. Danny has several qualities as an instructor. His pace of delivery and voice modulation is just fantastic and if you are not an absolute excel-newbie watching a video just one time is enough. Before starting each lesson he gives a brief introduction so that you can connect what you learned in the previous lesson. He also repeats and stresses some part which you will find really helpful.

Another great take away from this video tutorial is a bunch of great keyboard shortcuts and best excel practices which you can expect only from an excel veteran. Among so many other things I was left with surprise why I didn’t use “page layout view” and “vertical alignment formatting” before! Even if you are an advanced excel user surely you are going to learn a lot of useful tricks including some commonly encountered gotchas and how to avoid them.

I won’t be taken aback If this product goes every corner of the excel user community and becomes best seller in this field.”

Learn More About My Video Tutorial

I have produced the “9 Essential Skills for Excel 2010” in two formats:

Both versions include 4 hours of video instruction. 25 individual video tutorials. The Excel Practice files that I used while filming the video tutorials. A PDF of the Step-by-step Instructional Guide that I created for these video lessons.

Version for Excel 2007 Now Available!

DVD-ROM, "9 Essential Excel 2007 Skills"

DVD-ROM, “9 Essential Excel 2007 Skills”

I have just published “9 Essential Excel 2007 Skills” for DVD-ROM. Click to to get more information about my latest publication.

Secure Shopping at The Company Rocks

You can use a secure shopping cart to purchase my products at my online shopping website – http://shop.thecompanyrocks.com

 

 

Link to this post!
Filed Under: Excel Training, Online Store for The Company Rocks, Reviews, Video Training Courses Tagged: , , , , , , , , , , , , , , , , , , , , ,
July 16, 2012 1 Comment

How to Create a Series of 30 Minute Time Intervals in Excel

How Excel Stores TimeOne of my readers tracks data points at 30 minute (half-hour) periods throughout the day. He asked me for some help in streamlining this process.

AutoFill the Step-value for a Series

You can use AutoFill in Excel to create this series. Here are the steps to take:

  1. In two adjacent cells, enter the beginning time value and the time that is 30 minutes later than the beginning time value.
  2. Select BOTH cells and AutoFill the cells in the range that you want to “populate.”

Calculate Decimal Number that Represents 30 Minutes

You can calculate the numeric value for a half-hour (30 minute) period:

  1. Use a Formula to subtract the Start Time from the End Time.
  2. Format the result as a Number with 6 Decimal Places.

In this case, the result is: 0.020833 which you can use as a Constant Value in a Formula.

Keyboard Shortcut to Enter the Current Time

Ctrl + Shift + : (Colon) will “time stamp” a cell.

How Excel Stores Time

While you can Format Time in a variety of ways, Excel must be able to recognize and store Time entries as Numeric entries! This means that correctly entered times align to the right-side of the cell. Time values are stored as a “decimal part of one” day. For example, 12:00 PM (Noon) is stored as 0.50 – 50% of the day has elapsed

Shop at My Secure Online Website

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to view all of the resources that I offer you to improve your Excel skills.

Watch Tutorial in High Definition

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

How to Create a Series of 30 Minute Intervals in Excel [ 6:22 ] Play Now | Play in Popup | Download (2689)
Link to this post!
Filed Under: Excel Basics, Excel Training, Keyboard Shortcuts, Tips and Time Savers Tagged: , , , , , , , ,
June 27, 2012 1 Comment

How to Add Summary Information for Excel Workbook Properties

Almost everyone has experienced the frustration of being unable to locate an Excel Workbook on their computer. You can’t remember the name or the location of the Workbook. You waste valuable time searching in vain.

Tags for Document Search

Tags for Document Search

One way to minimize this frustration is to add Excel Workbook Properties that Summarize the content and purpose of the document. By adding Tags, Keywords, Client Names or Project Titles in the Properties Summary you make it easier for your computer to Index and Find your documents.

Fortunately, beginning with Microsoft Office 2007, it is a lot easier to add these properties to an Excel Workbook. In this video tutorial, I show you how to do this.

Displaying Recent Documents

Did you know that you can display up to 50 Recent Documents? Even better, you can “Pin” important documents so that they remain on the list. This is a valuable tool when you need to access important files, for example, once a month! I show you how to do this on the video.

Shop for Additional Resources

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – where you can purchase all of the training materials that I have developed.

Watch Video in High Definition

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

Watch this Video Now

 

Link to this post!
Filed Under: Excel Training, New in Excel 2007, New in Excel 2010, Workbooks and Worksheets in Excel Tagged: , , , , , , , , ,
June 24, 2012 Leave a Comment

How to Create Hyperlinks in Excel

Hyperlink Dialog Box in ExcelOne of my viewers – Ben from the UK – wrote to tell me that he needs to learn how to create Hyperlinks in Excel in preparation for a job interview this week. He asked me that, since he could not find a decent YouTube video on this topic, to create a tutorial demonstrating ow to create Hyperlinks in Excel.

Hey, in these tough economic times, I am happy to help as many people as I can. However, given my time constraints – and, my need to make a living from my video resources – I invite you to purchase one or more of my many resources on my secure online shopping website – http://shop.thecompanyrocks.com

Topics Covered in this Excel Video Tutorial

In this video tutorial, I demonstrate how to:

  • Create a simple Hyperlink to another worksheet in your Excel workbook
  • Create Hyperlinks that “Return you” to your main “Dashboard” worksheet
  • Hyperlink to other programs – e.g. PowerPoint
  • Create hyperlinks that direct a viewer to send you an E-Mail message
  • Hyperlinks to websites

I am the first to admit that the Insert / Edit Hyperlinks Dialog Box is confusing and “too busy” for my tastes. However, in this video tutorial, I demonstrate how to navigate through this Dialog Box and also, how to get te most out of it. For example, how to change the “text displayed” for the Hyperlink while working in the Dialog Box.

Watch  this Video in High Definition

Click here – on this Hyperlink – to watch this video in High Definition on my YouTube Channel – DannyRocksExcels

View this Totorial Now!

Purchase My Resources to improve your Excel Skills

If you are in a similar situation as Ben – where you need to demonstrate specific knowledge of MS Office Programs Skills – why trust your luck? Rather, improve your skills – and your odds for securing a new job or a promotion!

I invite you to purchase one or more of my down-to-earth video tutorials. Visit my secure online shopping website – http://shop.thecompanyrocks.com

Link to this post!
Filed Under: Excel Basics, Excel Training, Integrating MS Office Programs, Keyboard Shortcuts, Video Response to Viewers Tagged: , , , , , ,
March 1, 2012 Leave a Comment

How to Protect Cells that Hold Formulas in Excel

Protect Cells w Formulas

Protect Excel Formula Cells

Learning how to Protect the Excel Worksheet Cells that contain Formulas is a three-step process. This process tends to confuse and frustrate many Excel users. Some of the steps seem counter-intuitive when you first begin the process. That is why I created this video tutorial – To demonstrate each of the steps; to remove the confusion.

First, I show you how this is done in Excel 2010 / Excel 2007. Next, I show you how to protect the Formula Cells in Excel 2003.

Key Concept for Protecting Formula Cells

By default, all cells in an Excel Workbook are “Locked!”

So, if you are going to allow a user to input current values in the worksheet, you must first “select” the input cells and then, “Unlock” them.

Protecting Excel Worksheet

The final step in the process is to Protect the Worksheet. I rarely use a password for the worksheet because I tend to forget it and then I cannot even access the protected worksheet!

Take care in the Protect Sheet Dialog Box to select only the options that best suit your purpose. For example, in this video tutorial, I choose to limit users to select only the cells that I unlocked in steps 1 and 2.

Go To Special Dialog Box

The quickest way to select the cells that you wish to “unlock” is to open the Go To Special Dialog Box. There, choose “Constants.” You can limit the constants to “Numbers” as I do in this tutorial.

Danny Rocks Tips and Timesavers Podcast on iTunes

I invite you to subscribe to my “free” video podcast on iTunes. Click on this link to access all of my Podcast Videos.

Watch Tutorial in High Definition

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

 

 

How to Protect Cells with Formulas in Excel [ 7:32 ] Play Now | Play in Popup | Download (2394)
Link to this post!
Filed Under: Excel Formulas, Excel Training, Format Cells in Excel, Podcast Tips & Timesavers, Workbooks and Worksheets in Excel Tagged: , , , , , , , ,
February 2, 2012 Leave a Comment

The Basics for Creating and Copying Formulas and Functions in Excel

Relative and Absolute Cell References

Relative and Absolute Cell References

Formulas and Functions are the “core elements” of Excel. It is vital that you have a solid grounding in understanding how formulas and functions work; especially when you need to copy and paste them into other cells.

Relative and Absolute Cell References

When you use Relative Cell References – the default setting in Excel – the Row numbers and Column letters adjust automatically when you copy and paste a formula.

There are, however, situations where you need to “freeze in place” part of an Excel Formula. For example, you need to “freeze” or use an Absolute Cell Reference to the cell with “Total Sales,” when creating and copying a formula to determine Product Sales as a Percentage of Total Sales.

Copying Excel Formulas

In this tutorial, I demonstrate two methods for copying and pasting formulas and functions:

  • Standard Practice is to select the cell with the formula and use the Ctrl + C Keyboard Shortcut to place the formula cell on the Excel clipboard. Then, after selecting the destination cell(s), use the Ctrl + V Keyboard Shortcut to paste the formula in the new location(s)
  • AutoFill Tool.If you are copying the formula cell into adjacent cells, use the AutoFill tool to do this quickly and accurately!

Tips that You May Not Know

In my experience, many Excel veterans are not familiar with these tips and tricks which I demonstrate in this tutorial:

  • The Ctrl + ~ (tilde) Keyboard Shortcut to “toggle” the Show Formulas view for the active Excel Worksheet.
  • The Alt + Enter Keyboard Shortcut to automatically use the =SUM() Function – for adjacent cells.
  • The F4 Key to automatically add Absolute Cell Reference when creating or editing a formula. For example, converts A1 to $A$1.

Learn More Excel Tips and Tricks

50 Best Tips for Excel 2007

50 Best Tips DVD-ROM

If you enjoy the tips and techniques that I demonstrate in this lesson, then you will really benefit from purchasing my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.” You can learn more about the resources that I offer by visiting my secure online shopping website – http://shop.thecompanyrocks.com

Watch Tutorial in High Definition

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

Play Video Now

Link to this post!
Filed Under: Excel Basics, Excel Formulas, Excel Functions, Excel Training, YouTube Videos by Danny Rocks Tagged: , , , , , , , , , , , , , , , ,
Next Page »