Other Links
This is one of the 33 lessons from the
Tutorial on Excel
Lesson 1-3: Importing External Data Into Excel
Obviously when you open a new workbook in Excel there is no data in it. So all data is external.
Expensive and complex packages to import data
Before we get to the easy and FREE tools within Excel to add or import data into your spreadsheets here are a few comments on different products offered to you on the market.
There are many importers offered to you on the market. They are all expensive and they require extensive training. The worst part is that else than being importers they all try to do better than Excel as reporting application and they fail. The worst of these importers is Crystal Report.
EssBase (Hyperion) and its Excel add-in are among the best tools on the market to bring the data to the analysts and the report developers. If you consider buying Essbase ($25,000) don't buy their reporting and analyzing applications. USE EXCEL as your reporting and analyzing interface. Its add-in is a little heavy (a lot of useless functions) because its creators wanted users to be able to import data directly into a report with a perfect layout. Don't do it. IMPORT YOUR DATA ON ONE EXCEL SHEET AND BUILD YOUR REPORT ON ANOTHER ONE, use SUMPRODUCT formulas to carry the data from the "DATA SHEET" to the "REPORT SHEET" with the perfect layout. Another positive aspect of ESSBASE from Hyperion is that you can use VBA (the programming language within Excel) to use the objects within ESSBASE so you can create and modify the queries using VBA.
You can use VBA (macros) (and it is very easy) to automate the importation of data hence developing multiple retrieves in a single spreadsheet to either assemble this information into a relational report or to create a "multiple retrieves/multiple reports" spreadsheet.
If your ERP system is SAP, the Interactive Excel add-in is also a good tool to bring data into an Excel spreadsheet. You can bring the data from the data warehouse directly into an Excel worksheet by creating a simple MATRIX. Again use the add-in as a data miner but don't try to create reports with it. The add-in is a little heavy (a lot of useless functions) because its creator wanted users to be able to import data directly into a report with a perfect layout. Don't do it. IMPORT YOUR DATA ON ONE EXCEL SHEET AND BUILD YOUR REPORT ON ANOTHER ONE, use SUMPRODUCT formulas to carry the data from the "DATA SHEET" to the "REPORT SHEET" with the perfect layout.
There are all kinds of data importers that come as add-ins to Excel (like I-Apps, Crystal Reports). They are usually expensive, complex and like most applications designed to do it all they do everything somewhat like you would want them to do and they do a lot of things that you don't really need and that make the program complex. Find a good Excel-VBA developer and he will design a customized efficient importing tool that will do exactly what you need and NOTHING MORE.
Without the use of these expensive tools there are many ways to get the data into your spreadsheets and they are presented below. And once the data is in the workbook in a database for mat you will use the powerful SUMPRODUCT formulas that are queries in a formula.
#1 Manual Entry (Level: Beginner)
The most basic way to enter data in Excel is to key it in.
Three tips to begin this section:
1- When you want to enter the same value in many cells select them, enter the value in the formula bar right above the grid and click " CTRL/ENTER"
2- To enter the date of the day in a cell use " CTRL/SEMI-COLON" to enter the time use " CTRL/SHIFT/COLON"
3- To force a line break in a cell with multiple lines use " ALT/ENTER"
You should respect 2 rules about manual entries because the process can become time consuming and very expensive due to errors.
Rule # 1: If the data has already been entered anywhere on any electronic support (database, accounting program, WWW, manufacturing program, Emails, Word, text files, another Excel workbook.....) you must find a way to import it rather then key it in.
Rule # 2: Data is never entered in a report. It is entered respecting the Excel's requirements to recognize your set of data as a database. Then you will be able to use all the powerful analyzing tools in Excel and SUMPRODUCT formulas will carry the data into the reports. If you have data for 12 months, 6 stores, 50 accounts, 30 salespersons... there is no need to have 6, 12, 30, 50 sheets in a spreadsheet. Create a database on one sheet and develop AUTOMATED reports using the SUMPRODUCT function on other sheets.
#2 Copy/Paste (Level: Beginner)
You can Edit/Copy/Paste data from another Excel file, a Word document a website, an EMail and many other types of files.
See lesson 1-7 on comparative analyses in Excel to discover how you can view two different files, two different workbooks and even two different sheets of the same workbook on the screen at the same time to make your copy/paste operations easier.
If some numbers that you have Copied/Pasted in Excel are in text format here is a trick to fix the problem. Enter a 1 in any cell outside your set of data. Select it and go "Copy" then select the column of data and go to " Edit/Paste Special/Multiply"
You don't need to copy/paste directly from Microsoft Access. In Access you select a table or a query and then you go to the menu bar and select "Tools/Office Links/Analyze it with Microsoft Office Excel". An Excel workbook is automatically created with all the data from the table or the query. You can ask your database administrator to automate this process so that when you get to the office in the morning your Excel files are ready. This automation can also be developed from most other central databases, and sales, accounting, manufacturing central programs.
Instead of using Copy/Paste to bring an entire set of data from one Excel workbook to the other you can use the very simple "Data Import" functionality described in item #5 below.
#3 Functions and Formulas from a local file or a file on the WWW (Level: Beginner and Intermediate)
On the local network
You can use a formula to bring into a spreadsheet data that is in another spreadsheet on the network.
The basic formulas looks like this if the source spreadsheet and the destination spreadsheet are in the same directory, on the same hard drive:
=[otherspreadsheetsName.xls]SheetName!$A$1
If there is a space in the source sheet's name, the formula (with apostrophes) is this:
='[otherspreadsheetsName.xls]Sheet Name'!$A$1
If the Excel source spreadsheet is not in the same directory, the formula looks like this:
='C:\TEMP\[therspreadsheetsName]sheet Name'!$A$1
Remember that as soon as the sheet's name includes a space or special character it must be surrounded by apostrophes. ex: Introduction or 'Sales of the Month'
Formulas looking at a spreadsheet on the WWW
Tired of sending the same Excel reports to tens of people each week. Here is a SIMPLE solution
='http://www.excel-examples.com/[excel-online.xls]Sheet1'!D8
Open a new Excel workbook. Copy/Paste the formula above in any cell. Save it and re-open it. The result is 1,729.75 because the value in the cell of the workbook on the WWW is 1,729.75.
Click here to download the source
Click here to download a complete report that looks at a master on the WWW
So you send a report to everybody with the WWW formula and each time they open it it is updated because you have updated the master that sits on the WWW.
#4 Opening other documents with Excel (Level: Intermediate and Advanced)
With Excel you can open directly documents with the suffixes .XLS, .CSV, .HTM or .HTML (Web). You clean out what you don't need and you can start using the data.
For other documents .TXT or .ANYTHING you will open them with "File/Open" and select "All Files (*.*)" in the "Type of Files" in the text box at the bottom of the dialog window. Then you will use the Import Wizard as explained in lesson 1-4.
If there is a screen version of the document chances are that there is an electronic file that you ca open with Excel. Use the Import Wizard. Usually the columns of data (fields) are quite static. Rows (records) on the other hand can vary a lot (number of records, order of records). SUMPRODUCT formulas allow you to summarize the data and create AUTOMATED reports. If the input document contains a lot of garbage you can develop a macro (VBA procedure) to clean the file before you use SUMPRODUCT.
Sometimes the data is not exactly as you want it (ex: name and fist name in two columns). Simple text formulas will help you reformat certain data (assembling name and surname, assembling regional code and phone number).
If you need to reopen any file regularly to copy/paste the data into another Excel workbook you will prefer importing the data as explained I #5 below.
#5 Importing Data (Level: Beginners)
Instead of opening another file (XLS, CSV, TXT, HTM, etc..) and copy/paste from them you can import its data by going to the menu bar "Data/Import External Data/Import Data" and select the file that you want to open in the dialog window.
From then on you just need to refresh the data (just go to the menu bar "Data/Refresh") instead of going through the whole process of opening the data file again. Better yet you can automate the import so that the data is refreshed each time you open the Excel file or at any time interval. This approach is used a lot by people who get financial data from the WWW. See how to set your import for this automation functionality.
You can also use the Import Wizard.
#6 Microsoft Query (Level: Intermediate and Advanced)
If you don't want to simply import complete sets of data within Excel you can use the more elaborate functionality that allows you to select what part of the data that you want and that also allows you to access any external database on the network.
Within Excel there is Microsoft Query (you might need to install it using your Office CD). It is a tool designed to extract data from databases and other programs (accounting, manufacturing, sales, etc.). You can extract the whole data from another Excel file, a text file or a table or query from a database but you can also use filters to import only part of the data.
With this approach you can also automate the import process.
#7 VBA for Excel (macros) (Level: Advanced Plus VBA Macros)
With VBA (macros) you can automate all the data entries presented above.
Even better you can use SQL within VBA for Excel* to import data directly from central databases an even ask the database people to create stored procedures that you will call with VBA and that will run on the server rather than your station making it much faster.
* Visit www.excel-vba.com
This is one of the 33 lessons from the
Tutorial on Excel
