Home Archives for Import Data into Excel
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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
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: , , , , , , ,
April 10, 2011 Leave a Comment

Create a Web Query in Excel to Gather Updated Information

Web Query in Excel

Web Query in Excel

When you select, copy and paste data from a website into an Excel document, you get static images and data. When you want that data to be updated – or refreshed – with the latest information, you create a Web Query in Excel.

Follow the process that I demonstrate in this video and you will learn how to gain access to current information without having to leave Excel!

Create a Web Query

  • On the Data tab of the Excel 2007 Ribbon, select Get External Data – From Web.
  • In the Dialog Box – that resembles a “Web Browser” – type in the URL of the Website you want to search. Notice that the “Browse” Button is missing. My Best Practice is to make a copy of and paste in the URL of the website you want to search.
  • After you press the “GO” Button, you will see some “Yellow Arrow” markers on the website. These are the HTML Tables that you can select for your Web Query.
  • When you however your mouse over the Yellow Arrows, you will see a Blue Border around the HTML Table. Click the Yellow Arrow and a “Green Check Mark” indicates that you have selected this table. Continue to make your selections and then click “Import.”

Import Web Query

The final step is to decide where to place your Web Query, I usually accept the default selection of cell $A$1. There is an “Properties” Button that you can access now – or later. Press the “Add” Button and after a few seconds, you have imported the data from your Web Query.

As you will see in the Video for this lesson, it it easy to go back and Edit your Web Query or Change the Properties of the Query – e.g. to automatically Refresh every 60 minutes. You can also choose to Refresh the Query now.

Best Practice Suggestions

  • Use a separate session of Excel when setting Properties of your Web Query to automatically refresh. In this way, you will not slow down your work on another Excel project.
  • Gather your favorite Website URLs and paste them as Hyperlinks on a Worksheet. Select the Hyperlink prior to initiating a new Web Query. This will save you time as the Web Query Dialog Box does not have a Browse Button!

Watch Video in High Definition

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

Learn How to Master Excel in Minutes – Not Months!

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Create a Web Query in Excel [ 6:11 ] Play Now | Play in Popup | Download (1008)
Link to this post!
Filed Under: Excel Training, Import Data into Excel, Podcast Tips & Timesavers Tagged: , , , , , ,
March 9, 2011 Leave a Comment

My Best Practice Tips for Importing External Data Into Excel

I created this Excel Training Video after helping a viewer who was having difficulty getting the correct results in his Pivot Table. The problem, it turned out, was not with the Pivot Table. Rather, it was with the way his underlying data was stored and formatted.

When to Store Numbers as Text Values

Get External Data From Commands

External Data Commands

Specifically, he had combined the records from multiple workbooks into a master table. The data came from multiple main-frame computers. However, in some workbooks he had the “Invoice Number” stored as a “Numeric” value while in other workbooks it was stored – properly – as a”Text” value. Excel will treat Invoice Number 678910 stored as a number differently from the same entry (678910) stored as a text value.

Here is the “rule of thumb” to follow: Unless you will be using the SUM, AVERAGE, PRODUCT, MIN or MAX functions in a field, store numbers as “TEXT Values.” A short list includes fields for telephone number, postal codes, customer numbers and invoice numbers.

Start with the Correct Data

When you Import External Data Into Excel, start off on the right footing. Import the fields that contain Invoice Numbers, Postal Codes and Telephone numbers as “Text” values. In my experience, I have found that trying to format these fields “after the fact” does not give me the results that I was looking for. Getting the External Data fields correctly formatted at the Import Data step is the key to producing accurate and informative Excel reports.

“The 50 Best Tips for Excel 2007” DVD-ROM

Secure Shopping Cart
Secure Shopping Cart

This is one of the 50 Video Lessons that I offer on my DVD-ROM, “The 50 Best Tips for Excel 2007.” Click here to open a Secure Shopping Cart to purchase this DVD today. I guarantee your satisfaction. I will refund your purchase price if you are not 100% satisfied with my products.

Watch in High Definition mode on YouTube

Follow this link to watch this Excel video at DannyRocksExcels on YouTube.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
My Best Practice Tips for Importing External Data Into Excel [ 7:07 ] Play Now | Play in Popup | Download (952)
Link to this post!
Filed Under: Excel Training, Import Data into Excel, Podcast Tips & Timesavers Tagged: , , , , ,
November 29, 2010 1 Comment

My Most Viewed and Downloaded Videos for Excel and PowerPoint

My iTunes Podcast

Danny on iTunes

I began to post my videos as podcasts at the iTunes store this past summer. Since then, my video lessons for Excel and PowerPoint have been viewed and downloaded many times. I am thirlled. And, I thank you for your support and encouragement.

Here are the links – on my website – to the most popular video episodes. I have organized them by category in the order of their popularity. You can click on any link to view or download that video from my website. Or, you can go to my video Podcast by clicking on this link – “Danny Rocks Tips and Timesavers” Podcast on iTunes.

Merging and Consolidating Excel Worksheets

 PowerPoint Presentations

Data Visualizations

What-If Analysis in Excel

Importing Data From Other Programs

Pivot Tables

Excel Tables

Social Media

Formula Errors

Please add your comments below – or on my iTunes Channel. I welcome your feedback.

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

Sincerely,

Danny Rocks

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Consolidate Excel Data, Excel Data Tables and Lists, Excel Functions, Excel Training, Goal Seek in Excel, Import Data into Excel, Integrating MS Office Programs, Most Viewed Excel Videos, MS Excel Training, New in Excel 2007, Pivot Tables, Podcast Tips & Timesavers, Powerpoint Techniques, Presentations, Public Speaking, What-if Analysis Tagged: , , , , , , , , , , , ,
June 5, 2010 Leave a Comment

How to Import Text and Database Files in Excel 2007

When you need to import a Word document or an Access database file in Excel, resist the temptation to simply “open” the file with Office Button (File) – Open. Rather, follow the steps to “Get External Data” from the Data Tab on the Ribbon. This is how you can control the data type of each field that you import – especially important for formatting fields that contain dates. You also can control – and preview – who Excel will distribute the data into individual columns before you import the file.

Watch as I demonstrate these steps – this tip will save you a lot of time and avoid frustration.

(Note: This is a re-posting so that I can feed this video lesson to my Podcast at the iTunes store.)

Follow this link to view or subscribe to the “Danny Rocks Tips and Timesavers” podcast at the iTunes store.

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
How to Import Files from Other Programs in Excel 2007 Play Now | Play in Popup | Download (679)
Link to this post!
Filed Under: Excel Training, Import Data into Excel, Podcast Tips & Timesavers, Video Response to Viewers Tagged: , , , , ,
May 15, 2010 Leave a Comment

I Have Updated My Index of Excel Video Lessons

As part of the process of reorganizing my website, I have updated my “Index of Excel Video Lessons” page. The content of this page will be updated as I add each new Excel Video Training Lesson. So, I suggest that you click here to go to the Video Index and then save the page as one of your “Favorites” if you want to learn how to really get the most out of Excel or to find a quick answer when you are using Excel.

On the Index Page you can see an organized listing of my Excel Videos by Category – e.g. Formulas, Formatting, Pivot Tables “What-if” Analysis, etc. Each lesson is “hyperlinked” so that you can just click on the topic to go directly to the lesson that you are interested in.

I indicate in the title if the lesson is specific to Excel 2007.  All my new Excel video lessons are created using Excel 2007.

If there is a topic that you would like me to cover, send me an e-mail message – danny@thecompanyrocks.com

If you enjoy my video instruction, I encourage you to purchase my DVD, “The 50 Best Tips for Excel 2007.” The DVD contains over 5 1/2 hours of Tips, Tricks & Techniques to help you to master the program – and to save a considerable amount of time while working in Excel! Click here to enter my secure shopping site.

Thank you for your support and encouragement!

Danny Rocks

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Everyday Calculations in Excel, Excel Basics, Excel Data Tables and Lists, Excel Date and Time Functions, Excel Financial Tools, Excel Formulas, Excel Functions, Excel Training, Import Data into Excel, Lookup Functions in Excel, MS Excel 2007 DVD, MS Excel Tips, MS Excel Training, Online Store for The Company Rocks, Pivot Tables, Text Functions, Tips and Time Savers, Training, Video Training Courses, What-if Analysis Tagged: , , ,
May 11, 2010 2 Comments

How to Use Excel’s Transpose and Text to Columns Tools to Reorganize Your Data

This video lesson is in response to a viewer’s request. The information that he gets comes from a mainframe computer and the orientation of the data is horizontal (going left-to-right across columns. He wants to be able to reorganize this data to show it vertically (from top-to-bottom in rows. I use Excel’s Transpose tool to show him how to do this.

I also demonstrate an extremely useful Excel Tool – Text to Columns – to break apart an address block (City, State, Postal Code) into discrete fields – to help you to manage a mail merge or to sort and filter by State or by Postal Code.

(Note: Somehow this lesson “got lost” as I was reorganizing my website. So, I apologize in advance if you already viewed this lesson.)

If you enjoyed this lesson, I invite you to visit my online bookstore to shop for my DVD, “The 50 Best Tips for Excel 2007.” It is on sale now!

You can watch this Excel Video Lesson on YouTube – vist my channel – DannyRocksExcels

Related Videos

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Training, Import Data into Excel, Tips and Time Savers, Video Response to Viewers Tagged: , , , , ,
October 18, 2009 Leave a Comment

Use the Transpose Feature to Change the Orientation of Data Imported into Excel

I am working through the special requests that viewers have sent me. In this lesson, I show you how to:

  • Use Data – Get External Data – to import a simple delimited text file.
  • Use Copy – Paste Special – Transpose – to make a copy of the imported data that is now “vertically oriented.” i.e. The field entries run down the column rather than across the rows.
  • Use the Data – Text-to-Columns feature to break an “address block” (City, State, Zip Code) that is one cell into three distinct cells. This is a terrific time saver!

Now available for purchase: My DVD “The 50 Best Tips, Tricks, and Techniques for Excel 2007”

Over 5 1/2 hours of training. Each video lesson focuses on one tip and averages 6 minutes.

Now available from the online store at www.thecompanyrocks.com

Related Videos

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Training, Import Data into Excel, Integrating MS Office Programs, Video Response to Viewers Tagged: , , , , , ,