Home Archives for AutoFill Options
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 12, 2012 1 Comment

Essential Excel Skill #1: Entering Data Efficiently

My latest Excel Video Training Resource is “Nine Essential Excel Skills.” I have published versions for both Excel 2010 and Excel 2007. You can purchase them as either a DVD-ROM that I ship to you; or in a version that you can Download immediately.

Why Nine Essential Excel Skills?

As I created this Excel Video Training Resource, I had two distinct customers in mind:

  1. Everyday Excel users who know that they could get more out of Excel if only they had a structured approach to training that used “real life” business examples.”
  2. Experienced Excel users who want to prepare to take the Microsoft Office Specialist (MOS) 2010 or 2007 Certification Examinations for Excel.

 

Essential Skill #1 Video Lessons

Essential Skill #1 Video Lessons

Essential Excel Skill #1

I have identified Essential Excel Skill # 1 as “Entering Data Efficiently.” In the world of business there is a common acronym – GIGO – which means, “Garbage In delivers Garbage Out.” In order to produce accurate and meaningful reports in Excel, you MUST begin with data that is entered correctly.

Entering Time and Date Values

In my experience, many Excel users incorrectly enter Time values and Date values. Times and Dates are “Numeric Values.” When Time and Date Values are entered correctly, they align to the right-side of the cell. Once they are entered correctly, you can change their formatting to suit your purpose.

Keyboard Shortcuts for Current Date & Time

  • Ctrl + ; (semi-colon) enters the Current Date into the cell. This creates a “Date Stamp.”
  • Ctrl + Shift + : (colon) enters the Current Time into the cell. This creates a “Time Stamp.”
AutoFill Weekdays Only

AutoFill Options – Weekdays Only

Speed Up Data Entry with AutoFill Options

Use AutoFill to quickly – and accurately – fill in the Months of the Year and the Days of the week. I demonstrate – in detail – how to take advantage of the many AutoFill Options that are available. For example, to AutoFill Weekdays Only! I am confident that you will gain many productivity tips from the video tutorials in this Skills chapter.

Fill Series Dialog Box

Many Excel users have never used the “little gem” that is the Fill Series Dialog Box. As you will learn in the video tutorial, you can quickly fill in a long series of numeric values by using the “Step Value” and “Stop Value” fields in this dialog box.

Download the Complete Step-by-Step Instruction Guide – for Free!

I am so confident that you will want to purchase my video training resource, that I am offering you the opportunity to download – for Free! – the 29 page Instruction Guide that is included in “Nine Essential Excel Skills.” Click on the link to begin to download either version as a PDF:

Learn More About My Video Training Resources

My “Master Excel in Minutes – Not Months” series of video training resources is growing quickly! Follow this link to see the resources that are available now!

Watch My Excel Tutorial in High Definition

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

Watch Video Tutorial Now

 

 

 

Link to this post!
Filed Under: Essential Excel Skills, Excel Basics, Excel Training, Keyboard Shortcuts, YouTube Videos by Danny Rocks Tagged: , , , , , ,
September 20, 2012 Leave a Comment

Instructional Guide “9 Essential Excel 2010 Skills” – Download for Free

Instructional Guide "9 Essential Excel 2010 Skills"

Instructional Guide “9 Essential Excel 2010 Skills”

I want you to discover the scope of the Excel Training that I offer on my new video tutorial, “9 Essential Excel 2010 Skills.” So, I am offering the PDF of the Step-by-Step Instructional Guide that accompanies the video tutorial.

Link to The Company Rocks Free Resources Page

Click on this link to begin the download process for the Instructional Guide.

Instructions for Downloading the Free Instructional Guide

  1. Add this product to your secure shopping cart at my website.
  2. During Checkout, you can choose to either Register or Shop as a Guest
  3. You will need to provide a valid email address in order to receive the actual link to download my Instructional Guide.
  4. Even though you will be downloading this product, I had to set it up as a physical product with “Free Shipping.”
  5. Shortly after you complete the checkout process you will receive an email that contains a hyperlink to the file that you will download.
  6. You will be downloading a “zipped” file. So, once the download is completed, be sure to “unzip” this file!
  7. I created my Instructional Guide using the free Adobe Acrobat Reader.

Read the Instructional Guide and then Purchase my Video!

I am convinced that once you see the scope of the training in Excel 2010 that I offer, you will want to purchase the video tutorial. I offer my video in tow versions:

List of the “9 Essential Excel 2010 Skills”

9 Essential Skills for Excel 2010

9 Essential Skills for Excel 2010 Video Tutorial

These are the 9 Essential Skills that I have identified for my video tutorial:

  1. Entering Data Efficiently in Excel 2010
  2. Selecting Cell Ranges Efficiently in Excel 2010
  3. Creating and Using Named Cell Ranges in Excel 2010
  4. Applying Styles and Formatting to Excel 2010 Worksheets
  5. Working with Structured Data Sets in Excel 2010
  6. Working with Excel 2010 Formulas and Functions
  7. Using Paste Special Options in Excel 2010
  8. Using Data Validation in Excel 2010
  9. Using Excel 2010 “What-if” Analysis Tools

Excel 2010 Practice Files Included

When you purchase my video tutorial, you receive the same Excel 2010 Worksheets that I used while filming each of the 25 video tutorials. Using the same files as you view my videos, you will be able to practice your new skills. And, of course, you also receive the same Instructional Guide that I am offering for free!

I welcome your feedback! Please send me your comments via email – danny@thecompanyrocks.com – or by adding a comment below.

Thank you!

 

Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Data Validation, Everyday Calculations in Excel, Excel Basics, Excel Data Tables and Lists, Excel Formulas, Excel Functions, Excel Training, Format Cells in Excel, Goal Seek in Excel, MS Excel Tips, MS Excel Training, Names for Cells and Ranges, New in Excel 2010, Online Store for The Company Rocks, Subtotals and Outlines in Excel, SUMIF and SUMIFS Functions, Video Training Courses, What-if Analysis 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: , , , , , , , ,
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: , , , , , , , , , , , , , , , ,
November 28, 2011 Leave a Comment

How to AutoFill the Letters in the Alphabet in Excel

 One of my viewers would like to be able to AutoFill the Letters in the Alphabet in his Excel Workbooks – he creates Alpha-Numeric Part Numbers for his products.

Two Ways to AutoFill Letters in the Alphabet

  1. Use a Formula – =CHAR(ROW(A65)) and AutoFill Down in the Column – This is not ideal
  2. Create and Use a Custom List in Excel. – So, use the Formula to create your list of 26 Alphabetical Letters. Copy the List and use Paste Special Values to convert the formulas to values. Then create your Custom List.

Using the CHAR() Function in Excel

There are 255 ASCI Characters that you can use in Formulas. For example, =CHAR(169) will return the © Symbol. In this lesson, I share a great Keyboard Shortcut that you can use to add this Symbol into the Footer of your printed Excel worksheet – Watch the video to discover this!

Visit My Secure Online Shopping Site

I hope that you take a few minutes to visit my new, secure online shopping website. The address is: http://shop.thecompanyrocks.com

 

Watch Tutorial in High Definition

On my YouTube Channel – DannyRocksExcels – I have posted this tutorial in High Definition Mode. Click here to go directly to this video.

 

Link to this post!
Filed Under: Excel Functions, Excel Training, Tips and Time Savers, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
July 8, 2011 1 Comment

Use Excel’s AutoFill Tools and Formulas to Increment Time Intervals

In my experience, many Excel users get “frustrated” when trying to enter and calculate Time Intervals. For example, setting up a schedule for meetings that begin and end in 45 minute intervals. After you watch this video tutorial, you will learn how easy it is to set up this type of schedule!

The Problem with Time in Excel

When it comes to entering Time Values, Excel will only accept a short list of Time Formats. For example, 6:00 AM is a valid Time Entry in Excel. However, Excel will treat 6:00 A.M. as a “text entry!” 6:00 AM is stored as the decimal value 0.25 in Excel. Decimals are numbers that Excel can calculate and increment. In this video, I demonstrate a shortcut that ensures that you always enter a Time Value as a Number.

How to Increment in Two-Hour Intervals

As I demonstrate in this video, use this formula to increment a series of two-hour time intervals:

=starting_cell + 2*(1/24)

In this case the (1/24) portion of the formula indicates a “one-hour segment.” 1 hour out of a 24 hour day. The +2 part of the formula indicates that you want to increment by 2 – of what? The answer is +2*(1/24) – by 2 hours.

To indicate a 15 minute increment use this formula:

=starting_cell + 15 * (1/24/60) – That is, divide each one hour segment (1/24) by 60 minutes (1/24/60) and multiply this by the number of minutes that you wish for your schedule.

Watch Part 1 of this Series – Increment Dates in Excel

Follow this link to watch Part 1 of this two-part series – “Use Excel’s AutoFill Tolls and Excel Functions and Formulas to Increment Date Intervals.”

View this Tutorial in High Defintion on YouTube

Click on this link to watch the video lesson in High Definition on my YouTube Channel – DannyRocksExcels

Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007 for only $29.97

Use Excel's AutoFill Tolls and Formulas to Increment Time Intervals [ 9:02 ] Play Now | Play in Popup | Download (2003)
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , , , ,
July 7, 2011 1 Comment

Use Excel’s AutoFill Options and Formulas to Increment a Series of Dates

Excel AutoFill Options

Excel AutoFill Options

I begin this Excel Video tutorial by first, demonstrating how to use Excel’s AutoFill Options to increment a series of dates. Some of these AutoFill Options are:

  • Fill Weekdays Only (Monday through Friday)
  • Copy Formatting Only
  • Increment by Adding one month to the previous cell
  • Increment by Adding one year to the previous cell

Here’s a Tip: If you use your Right-mouse button to AutoFill a series, a menu of options will pop up automatically when you release the mouse. Try it!

Excel Functions and Formulas to Increment a Series of Dates

Two of my favorite Excel Date Functions are part of the “Analysis ToolPak” Add-in:

  1. The EDATE() Function – returns a “serial number” for a Month that is X number of months away from the starting date.
  2. The EOMONTH() Function – is similar to EDATE. However, it always returns the last Calendar Day of the Month that is X  number of months away from the starting date.

In Excel 2007 and Excel 2010, the Analysis ToolPak Add-in is activated by default. In this video, I demonstrate how to activate it if you are using Excel 2003 or older.

Other Functions Used in this Video Tutorial

  1. The DATE() Function. Remember that if you use this function to increment by one-year intervals that you need to “nest” the Year(), Month() and Day() functions in the arguments. This is a “tricky” function to use.
  2. The WEEKDAY() Function. In the video, I nest this function inside an IF() Function in order to get a series of dates that include Monday through Friday only!

Related Video Tutorial

My next video in this series demonstrates how to use Formulas to increment a series of times by “minutes” or by “hours.”

Watch This Video in High Definition

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

Shop for Excel Training Resources

Shop for The 50 Best Tips for Excel 2007

Shop for The 50 Best Tips for Excel 2007

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to see all of the great training resources that I offer you.

Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007” for only $39.95!

 

How to use Excel's AutoFill Options and Formulas to Increment a Series of Dates [ 11:21 ] Play Now | Play in Popup | Download (1637)
Link to this post!
Filed Under: Date Functions and Formats in Excel, Excel Date and Time Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , , , , , , ,
March 13, 2011 Leave a Comment

How to Sort Using a Custom List in Excel

What happens when you sort a list of Months in the Year in Ascending Order? You will get “April, August …, etc.” And that, I guarantee, is not what you want! So, how do you get the list to sort as “Jan, Feb, Mar, … etc.?

Sort Using a Custom List

Sort on Custom List

Sort on Custom List

The months of the year and the days of the week are Custom Lists that are built into Excel by default. You can also create your own Custom Lists – e.g. a list of department in the order that you want to see them displayed. Once you have a Custom List saved in Excel, you can choose to sort using this order.

Watch this short video lesson to see how to create a Custom List and also how to Sort Using a Custom List in Excel.

High Definition, Full Screen Mode on YouTube

Here is the link to watch this video on my YouTube Channel – DannyRocksExcels

Learn More About Custom Lists in Excel

I have identified “Creating Custom Lists to AutoFill and Sort By” as an Essential Skill in Excel. I have created a four-hour comprehensive video training package that covers the “Nine Essential Skills in Excel.”  Click on these links to learn more about my video training resources:

How to Sort Using a Custom List in Excel [ 5:12 ] Play Now | Play in Popup | Download (1200)
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , , ,
Next Page »