Home Archives for Paste Special
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: , , , , , ,
September 30, 2012 1 Comment

How to Clear Cell Formatting In Excel

Numbers Stored as Text

Numbers Stored as Text

In my previous tutorial, I demonstrated how to clear all text formatting from all slides in a PowerPoint Presentation. Several viewers contacted me about publishing a tutorial that demonstrates how to clear formatting from ranges of cells in Excel.

Use the Clear Formatting Command

From my experience, I had found that a great many Excel users expect that formatting as well as contents are cleared when you press the Delete Key. This is not the case. The Delete Key only clears the contents of the cells in the selected range.

Use the Clear Formatting Command in the Editing Group on the Home Tab of the Ribbon. If you are using Excel 2003 or earlier, you will find this on the Edit Menu.

Numbers Stored as Text

As a Best Practice, I strongly recommend that you verify that the numeric values that you will be using in formulas are truly numbers and not numbers stored as text. This, unfortunately is a common case whenever you are working with data that has been imported from a main frame computer or other external sources. In this lesson, I demonstrate how to use Paste Special Operators to quickly convert numbers stored as text to true numeric values.

9 Essential Skills for Excel 2010

9 Essential Skills for Excel 2010 Video Tutorial

Nine Essential Skills for Excel

I cover, in-depth,  Entering and formatting data as well as using Paste Special Options on my new video resource, “Nine Essential Skills for Excel.”

  • Four hours of video training
  • 25 individual video tutorials
  • Step-by-Step Instructional Guide
  • Excel Practice Files included

Download It or Order My DVD-ROM

Watch My Tutorial in High Definition

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

How to Clear Formatting from Cells in Excel [ 6:03 ] Play Now | Play in Popup | Download (2881)
Link to this post!
Filed Under: Excel Basics, Excel Training, Format Cells in Excel, MS Excel Training, Podcast Tips & Timesavers 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 28, 2011 Leave a Comment

3 Work-Arounds for Shortcomings in Calculated Fields in Excel Pivot Tables

Excel Work-arounds

3 Work-Arounds for Calculated Fields

Don’t get me wrong. Excel Pivot Tables are a great way to summarize, analyze and present the information in your data. And, creating Calculated Fields in Pivot Tables is a terrific way to extend your analysis.

However, there are a few “quirks” that you should be aware of when using a Calculated Field”

A Calculated Field is always performed against the SUM of your data!

Well, what if you want to calculate against the MIN or the MAX of your data? Or perform some other calculation?

3 Work-Arounds for Calculated Field Shortcomings

I encourage you to look at a Pivot Table as “a means to an end.” Let your Pivot Table take you to the 90% completion point – and a Pivot Table does this very well! Then, use one of these 3 strategies to help you to complete your analysis.

  1. Sometimes, you simply must return to your source data and add an additional field so that your Pivot Table has more information to work with.
  2. You can make a copy of your Pivot Table and then Paste Special the Values to another location. From there, you can complete your analysis with more flexibility.
  3. You can create formulas outside of the Pivot Table – as I demonstrate in this video, deselect the “pesky” GetPivotData Function first. There are some limitations here:
  1. Remember that all cell references in your formula must appear in your Pivot Table
  2. Your Pivot Table must essentially remain “static” – e.g. no more fields added or subtracted, etc.

Watch Video Tutorial in High Definition on YouTube

This video lesson is longer in length than normal. To watch it in High Definition, click on this link to go to my YouTube Channel – DannyRocksExcels

Download Extended Length Video Tutorials for Pivot Tables

I have created extended length video tutorial recordings for Pivot Tables in Excel 2003, Excel 2007, and Excel 2010. You can download them for only $9.95 USD.

Click on this link to get more information about my Excel Extended Length Video Recordings.

 

Link to this post!
Filed Under: Excel Training, Pivot Tables, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
May 30, 2011 5 Comments

New Keyboard Shortcuts for Paste Special in Excel 2010

Content Key on Keyboard

Content Key on Keyboard

In Excel 2010, you can now use special keyboard shortcuts to control your Paste Special Options – e.g. Paste Values Only, Transpose, Paste Link, Paste Formatting, etc.).  There is a new technique to learn, however, before you can become proficient with these keyboard shortcuts in Excel 2010. Let me demonstrate.

Live Preview for Paste Special in Excel 2010

In my previous video tutorial, I demonstrated how to take advantage of this great new feature – “Live Preview for Pasting” – in Excel 2010. Click here to watch that video.

Two Ways to Use Keyboard Shortcuts to Paste Special in Excel 2010

  1. Press and Release the Ctrl Key, then press the “letter key” for the shortcut.
  2. Press the “Context Key” on your keyboard (to the right of the Space-bar; between the Alt & Ctrl keys) and then press the “letter key” for the shortcut.

Each of these techniques is easier to explain through a video demonstration than to write out the instructions. So, I encourage you to watch my video if you want to learn these new techniques for Excel 2010!

Learn More Paste Special Options

Main Menu Essential Skills for Excel

Main Menu for “9 Essential Excel Skills”

On my latest DVD-ROM, “Nine Essential Skills for Excel 2010,” I go into greater detail about the many ways that you can use Paste Special. This IS one of the 9 Essential Skills that I have identified. Follow this link to learn more about the 4 hour training video. The DVD-ROM includes 25 individual video tutorials, a 29-page instructional manual,  and the Excel 2010 Practice Files that I used while filming the videos.

Learn about all of the training resources that I offer at my secure online shopping website – http://shop.thecompanyrocks.com

Watch Video in High Definition on YouTube

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

New Keyboard Shortcuts for Paste Special in Excel 2010 [ 8:39 ] Play Now | Play in Popup | Download (2790)
Link to this post!
Filed Under: Excel Training, Keyboard Shortcuts, New in Excel 2010, Podcast Tips & Timesavers Tagged: , , , , , , ,
May 28, 2011 1 Comment

Introducing Live Preview for Paste Special in Excel 2010

Paste Special Gallery

Gallery of Paste Special Options

I have always loved the many “Paste Special Options” available in Excel. Now, in Excel 2010, there is a great new tool available – “Live Preview of a Gallery of Paste Special Options” – to help you to discover or to choose the best way to paste items copied to the clipboard.

From either the Paste Menu on the Home Tab of the Excel 2010 Ribbon or from the right-click shortcut menu, you can hover your mouse over one of the Gallery Icons to see a “Live Preview” of your Paste Special Option Selection.

When I demonstrate this feature  to my clients, they are amazed to discover the many options that are available! Of course, some of these same clients are also “overwhelmed” by all of the choices on the menu!

That is why I created this video – to help you to discover and to select the best Paste Special Option for your needs.

Paste Between MS Office Programs

Later in this video tutorial, I also show you how to use the Microsoft Office Clipboard and the Gallery of Paste Special Options to copy a data set from Excel and Paste it as a Picture in a Word document. With the Office Clipboard, you can cut, copy and paste up to 24 items between Excel, PowerPoint and Word. However, you must first open up the Office Clipboard for this tool to be available to use.

Additional Paste Special Features in Excel 2010

I have decided to produce an addition video tutorial to demonstrate how to use some new Keyboard Shortcuts in Excel 2010 to take advantage of the Paste Special Options.

Watch Video in High Definition Mode

Click here to watch this video in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

Introducing Live Preview for Paste Special in Excel 2010 [ 7:01 ] Play Now | Play in Popup | Download (2181)
Link to this post!
Filed Under: Excel Training, New in Excel 2010, Podcast Tips & Timesavers Tagged: , , , , , , , ,
April 6, 2011 2 Comments

How to Use Text Functions in Excel

Text Functions in Excel

Text Functions

In my experience, I find that many of my clients do not realize that Excel has many powerful – and easy to use – Text Functions.

Even more amazing, I observe clients spending hundreds of hours and thousands of dollars “re-keying” or “manually editing” text entries that have been downloaded from a Main Frame Computer. If you – or your colleagues – have been doing this, you will want to watch this Excel Video Tutorial.

Categories of Text Functions in Excel

In this Excel video lesson, I demonstrate how to use three categories of Text Functions:

  1. Transform Text – Use the UPPER, lower and Proper functions to change the “Case” of a text string.
  2. Join Text – Use either the CONCATENATE() Function or the & (Ampersand) Operator to join two or more text strings into a single text string.
  3. Clean-up Text – Use the LEN(), TRIM() and CLEAN() Text Functions to “trim the fat” from text strings – especially those that you download from a Main Frame Computer.

Once again, in my experience, I have too many clients contacting me in a panic because they get a REF# error when using Text Functions. I demonstrate how to recover from a “seeming disaster” and also how to use COPY – PASTE SPECIAL – VALUES to transform Formulas into Constant Values.

I welcome your feedback. Please send me an email: danny@thecompanyrocks.com – to let me know if this tip helped you to save time and money. I also welcome your suggestions for future Excel Video Lessons to create and post here, on my website.

Watch This Excel Video Tutorial in High Definition Mode

Follow this link to view this Excel Video Lesson in High Defintion Mode on my YouTube Channel – DannyRocksExcels

Download the Excel Workbook

Follow this link to download the Excel Workbook I use in this lesson.

Learn How to Master Excel in Minutes – Not Months!

If you like this Excel Video Tutorial, I invite you to learn more about the resources that I offer to help you to improve your Excel skill set.

How to Use Text Functions in Excel [ 8:28 ] Play Now | Play in Popup | Download (1181)
Link to this post!
Filed Under: Download Files from Video Lessons, Excel Training, Podcast Tips & Timesavers, Text Functions Tagged: , , , , , , , , , , , , , ,
February 17, 2011 2 Comments

How to Find and Change Numbers Stored as Text in Excel

Whenever a client or a colleague sends me an Excel workbook I like to inspect it, to be sure that the “numbers are really numbers.” I will not get accurate results when I SUM them if there are some cells that appear to contain numbers, but really display a “number stored as text.”

Prevent GIGO

Prevent GIGO

This is an example of GIGO – “Garbage in delivers Garbage Out!” I take steps to prevent an outbreak of GIGO in my Excel worksheets.

Looking for Numbers Stored as Text

As a best practice, I use both the COUNT() Function and the COUNTA() function to see if there is a difference between the number of cells with numbers (the COUNT Function) and the number of non-blank cells (the COUNTA Function). In this example, I was expecting to see 31 cells with numbers in the range of sales. There were on 27. So, I need to find those 4 cells that contain “numbers stored as text.” 

If you have a keen eye – and some time to spare – you can look for cells with a little Green triangle in the upper left corner. Click on the message next to these “marked” cells and read the error message. Excel will offer to correct the error – one cell at a time. That is not an efficient way to proceed!

Use the ISTEXT() or ISNUMBER() Functions

An alternative way to find the cells that contain text stored as numbers is to use one of the “logical functions” ISTEXT or ISNUMBER. Each returns either TRUE or FALSE when evaluating a cell. You can then sort the data to group the TRUE results together and then change them – again, one at a time. This is also not the best way to make the changes.

Use Conditional Formatting to Highlight Cells with Number Stored as Text

I like to use the ISTEXT() Function as a “Rule” to apply special formatting to the cells that evaluate as TRUE with Conditional Formatting. Be sure that you select the cells that you want to evaluate before applying the Conditional Formatting.

Use Paste Special – Add

The quickest way to change numbers stored as text into real numbers is to use Paste Special – Add. First, select and copy a blank cell. Next, select your data range – in this case the cells with “Sales.” Then, right click the selection and choose Paste Special. In the dialog box select “Add.” Now – instantly – all of the cells with numbers stored as text have been changed into real numbers. You have stamped out GIGO!

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

Follow this link to watch this video in High Definition, Full-screen mode on my YouTube Channel – DannyRocksExcels

How to Find and Change Numbers Stored as Text in Excel [ 7:19 ] Play Now | Play in Popup | Download (1308)
Link to this post!
Filed Under: Conditional Formatting, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , , , , , ,
November 25, 2010 3 Comments

How to Trigger a Unique List of Values Based Upon Another Drop-Down List in Excel

You can use the INDIRECT() Function in Excel to create a drop-down list of unique values that is“triggered” by the label that you select in another drop-down list. In the scenario that I use in this lesson:

  1. Create your 1st Drop-down list using Data Validation in Excel – Allow “List.” Here the named range is “Departments.”
  2. Create a 2nd Drop-down list using Data Validation. This time the “list” uses the INDIRECT Function to reference the selected label from the 1st drop-down list.

Key Concept

 The labels in my 1st drop-down list serve as “named range” references. That is why the INDIRECT Function is able to pull up the correct list of “Job Titles” in the 2nd Drop-down list. For example, a reference to the text label “Sales” in the 1st drop-down list brings up a list of the values (Job Titles) from the “Named Range” (Sales) in the 2nd Drop-down list.

If you need to learn about – or review – Data Validation in Excel, follow this link to my free Excel video lesson on this topic.

Create Named Ranges in Excel

I have found that creating and using “Named Cell Range” references makes lookup functions and formulas so much easier in Excel. Watch this video to see how I use a variety of methods to Create Named Ranges in Excel. One tip is to use the Keyboard Shortcut “Ctrl + Shift + F3” – after you first select the Label and the Values for your Named Range.

In this lesson, I also demonstrate how to visually “flag” all of the cells that do not meet the criteria for your Data Validation. I find that most people are unaware that you can do this! I think that you will find many uses for this auditing tool as you work in Excel.

Additional Resources

Follow this link to watch this video on YouTube in High Definition, Full Screen Mode.

Here is the link to view the 2nd video in this series about the INDIRECT Function in Excel.

Learn how to “Master Excel in Minutes – Not Months!” – visit my new, secure shopping site – http://shop.thecompanyrocks.com

How to Trigger a Unique List of Values Based on Your 1st Drop-Down List [ 6:23 ] Play Now | Play in Popup | Download (1046)
Link to this post!
Filed Under: Data Validation, Excel Training, Lookup Functions in Excel, Names for Cells and Ranges, Podcast Tips & Timesavers Tagged: , , , , , , , , ,
Next Page »