Home Archives for Paste Special Values
October 4, 2012 Leave a Comment

How to Correct #N/A Error Messages for Excel’s VLOOKUP Function

Fix the #N/A Error Message

A colleague of mine – who is an experienced Excel user – cannot figure out why he is receiving those ugly #N/A Error Messages when he is using the VLOOKUP Function in Excel.

After a quick look at his workbook, I spotted the problem!

Use the TRIM() Function to Remove Extra Spaces

It turns out, that the data source – housed on the company’s Main Frame Computer – is using “Fixed Width” Fields. So, while only 7 characters are visible, I used the LEN() Function to determine that the field was actually storing 12 characters.

The #N/A Error Message for VLOOKUP()

The #N/A Error Message appears when a Match cannot be found for the Lookup Value in the Left-most Column of the Table_Array when using VLOOKUP(). In this case, my viewer was trying to match a lookup value that contains 7 characters to a value in a field formatted to contain 12 characters. This is what produced the #N/A Error!

Paste Special Values

The final step to correct this problem is to use Copy – Paste Special – Values to replace the Formulas with the values of those formulas.

Essential Excel Skills

9 Essential Skills for Excel 2010

9 Essential Skills for Excel 2010 Video Tutorial

In this video, I cover three Essential Excel Skills:

  1. Essential Functions – VLOOKUP()
  2. Text Functions – LEN() and TRIM()
  3. Paste Special Options

I cover each of these skills – and more – in greater detail on my new 4-Hour Video Training Resource, “Nine Essential Skills for Excel.”” Click on the links below to learn more about:

Watch Video in High Definition

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

How to Correct #N/A Error Messages for Excel's VLOOKUP Function [ 6:02 ] Play Now | Play in Popup | Download (7514)
Link to this post!
Filed Under: Excel Basics, Excel Functions, Excel Training, Import Data into Excel, Lookup Functions in Excel, Text Functions 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: , , , , , , ,
June 13, 2011 Leave a Comment

How to Use an Excel Macro to Quickly Fill in Blank Cell Labels

Options for Filling in Blank Cell Labels

Options for Filling in Blank Cell Labels

In this Excel video tutorial, I demonstrate how to write, proofreed and run a Macro that will quickly fill in values for blank cell lables in a report that you may have received from a colleague or a client.

Reports With “Holes in the Data”

Frequently, when you receive a report that was created in an Excel Pivot Table or from a relational database – e.g. MS Access – the report looks great! However, if you wish to apply filters or other analysis to this report, you will need to fill in the “missing” or blank cell labels.  

Watch This Excel Video Tutorial in High Definition

Follow this link to view this lesson in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

Related Excel Video Tutorials

If you would like to learn how to convert an Excel Pivot Table to a Standard List – and to Fill in the Blank Cell Labels – click on this link to see my video tutorial on this topic.

And, here is a link to another related Excel Video Tutorial – Quickly Fill in Blank Cell Labels

Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Formulas, Excel Macros, Excel Training, Excel VBA Macros 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 9, 2011 2 Comments

How to Convert a Pivot Table to a Standard List

Watch this video tutorial in High Definition on YouTube

A viewer aksed for my help. She has a boss and a few customers who:

  1. Do not know how to create or use Pivot Tables
  2. Have no desire to learn how to create and use Pivot Tables
Convert Pivot Table to Standard List

Convert Pivot Table to List

Nevertheless, in this example, she is being asked to create a summary report – by Year and by Quarter – from @ 5,000 daily transaction records. The fastest way to create this report is with a Pivot Table. Now, to meet the demands of her boss and clients, she needs to convert this Pivot Table into a standard list that can be Filtered and Subtotaled.

Summarize by Year and Quarter

To create this Pivot Table report:

  • Add the “Date” field to the grid and then select a single date.
  • Group the Date field by Month, Quarter and Year.
  • Add and arrange the Customer and Invoice fields to the Pivot Table Grid.

Convert Pivot Table to Standard List

  1. Remove both the Subtotals and Grand Totals from the Pivot Table Report.
  2. In Excel 2007 & 2010, ensure that you are using either the Outline or Tabular report layouts – NOT the new “default” Compact layout.
  3. Also in Excel 2007 & 2010, remove the Field Headers and the + / – Symbols.
  4. Now, select the entire Pivot talbe (use Ctrl + A) and copy it to the clipboard.
  5. Use Paste Special Values to paste the Pivot Table Results.

Filling the Blank Cells in the List

As you can see, whenever you have “nested rows” in a Pivot Table you get a nice, clean report. However, in order to Filter and Subtotal this new list, you will need to “fill in the blank cells.”

  1. Select all of the values and all of the blanks in Column A.
  2. Use Ctrl + G (or F5) to open the Go To Dialog Box and choose Special.
  3. From the Go To Special choices choose “Blanks.”
  4. With all of the Blank Cells selected use this formula: = ↑
  5. Do NOT press Enter. Rather, press Ctrl + Enter and all of the Blank Cells are now filled in with the Customer Names!
  6. Finally use Copy, Paste Special, Values to complete this process.

Watch This Video on My YouTube Channel – DannyRocksExcels

Since this video tutorial is a little longer than usual, I have decided to post it as a video – in High Definition – on my YouTube Channel. Click here to view it or copy and paste this link into your browser:

Link to YouTube Video:   http://youtu.be/IwReVDJ7BAI?hd=1

Download This Excel Workbook

Link to this post!
Filed Under: Excel Formulas, Excel Training, Pivot Tables, YouTube Videos by Danny Rocks 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: , , , , , , , , , , , , , ,
October 5, 2008 Leave a Comment

Generate Random Numbers for Excel Spreadsheets

When I create the examples for this podcast I frequently use the =RANDBETWEEN() function to generate random numbers. Then, I usually copy the formulas and use Paste Special to convert them into values.

Recently, I watched Bill Jelen. Mr. Excel demonstrate an absolutely amazing Excel Trick – use your Right-mouse button to move a selected block of cells over the the right. And then, without releasing the mouse button, move the block of cells back into place. A menu pops up and asks if you want to “Copy here as Values.” Try it! It is amazing! What a time-saver!

Here are the steps to follow in the Excel Video Lesson:

  1. Use the =RANDBETWEEN() Function to generate random numbers in your Excel spreadsheet. The function takes two arguments – a Top Number and a Bottom Number (both of your choosing.)
  2. The RANDBETWEEN Function is part of the Analysis ToolPak Add-IN. Activate it by choosing Tools – Add-Ins- Analysis ToolPak.
  3. Select the block of cells before you write the formula and then use Ctrl+Enter to populate all of the cells in your selected range with the formula.
  4. To convert the formulas to values, use Copy, Paste Special Values.
  5. OR… try this incredible trick (courtesy of Bill Jelen, Mr. Excel: Select the block of cells. With the RIGHT-Mouse Button, move the Border of the cell range over the the right and then back into place. Then, when you release the mouse a menu pops up. Choose, “Copy as Values Here.” It is amazining!

Find the Video Lesson that you want. Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Link to this post!
Filed Under: Excel Functions, Excel Training, Tips and Time Savers Tagged: , , , , , ,