Excel gracefully accepts proprietary data created in many other applications. Excel also makes it easy to import data from text files and helps you parse it into worksheet columns.
Note
Mountains of very specific, sleep-inducing technical details are available about importing and exporting files. If you need details, you should consult Microsoft Office Online (office.microsoft.com). Also, for information regarding sharing data with other Microsoft Office 2010 applications and working with external databases, see Chapter 32, and Chapter 24.
To import a file from another application or from an earlier version of Excel, click the File tab, click Open, and select the file you want to import from the list of files in the Open dialog box. To narrow the list of files and zero in on a specific file type you want to import, use the drop-down list adjacent to the File Name box at the bottom of the Open dialog box, shown in Figure 2-38. When you choose a file type, the Open dialog box displays only files of that type in its list. Keep in mind that it is not necessary to include a file name extension (such as .txt) when you import a file because Excel determines the format of the file by examining the file’s contents, not its name.
To export an Excel file to another application or to an earlier version of Excel, click the File tab, and click Save As. Then select the file type associated with the application you’re exporting to in the Save As Type drop-down list. The available options are similar to the drop-down list shown in Figure 2-38, except the list here contains somewhat different format options for saving. For example, you can easily open any type of Excel file immediately because All Excel Files, which comprises all past and current Excel file types, is selected by default in the Open dialog box. But you must be specific when you save, so you can specify any of 10 different Excel file formats in the Save As dialog box.
Excel for the Macintosh since 1998 has been using the same file format as Windows versions of Excel from Excel 97 through Excel 2003. You can share these files with Macintosh users by simply transferring files from one computer to the other.
To save an Excel 2010 (or Excel 2007) file to share with someone using the Macintosh version of Excel, click the File tab, click Save As, and click the Microsoft Excel 97-2003 Workbook option in the Save As Type drop-down list.
To import Macintosh files to your PC, you first need to transfer the file to your PC via a cable, a disk, a network, an e-mail attachment, a Web site, or a tool such as MacOpener. Exporting files from a Windows version of Excel to the Macintosh version is just as easy as importing Macintosh files. Simply transfer the file from the Windows computer to the Macintosh using your method of choice, and then use the Open command to load it into Excel.
Yes, some people don’t use Excel, and you might meet one someday. Seriously, plenty of reasons exist for making Excel-based data accessible outside the program, whether or not Excel is available at the destination. Posting data to a Web site or creating data sets for proprietary analysis software are two possible applications where you might want data that can fly free, independent of the Excel mother ship.
Office Web Apps give you a familiar Office experience when you are away from your Office applications on your computer. With your Office files uploaded to a Web storage location such as your own Web site or Windows Live SkyDrive, you don’t need your own computer or software to view and perform light editing of Word 2010, Excel 2010, PowerPoint 2010, and OneNote 2010 files. You just need a computer running a popular Web browser.
Note
For more information about Office Web Apps, see Chapter 26.
Two options in the Save As Type drop-down list in the Save As dialog box produce files that you can use as Web pages: Web Page (HTM, HTML) and Single File Web Page (MHT, MHTML). They produce essentially the same result, the important difference being that the Web Page format saves not only a main HTML file but also a folder containing supporting files that must travel with the main file. As you might expect, the Single File Web Page format manages to cram it all into a single file without using the supporting folder. Single File Web Page has the advantage of being more portable, but Web Page gives you more control over individual elements. A separate cascading style sheet is created using the Web Page format, along with individual HTML files for each worksheet in the workbook. Figure 2-39 shows the contents of the supporting folder that is created after saving a seven-sheet workbook entitled Humongous2010 using the Web Page file format.
Figure 2-39. The Web Page file format creates a folder full of supporting files to go with the main Web page.
If you are an HTML aficionado, you can open the supporting files in other programs. For example, if the original workbook contains graphics, Excel saves them as separate image files (JPEG, PNG, or GIF) that you can modify with an image-editing program. Or you can change the fonts used by editing the cascading style sheet with a text editor such as Notepad. This is not work for the timid, of course. The slightest editing error in the HTML code for any of the files has the potential to render them all unusable.
To export an Excel file as a text file, click the File tab, click Save As, and select one of the following eight text formats from the Save As Type drop-down list. In all of these formats, Excel saves only the current worksheet. Number formatting is preserved, but all other formatting is removed.
Formatted Text (Space Delimited) (*.PRN) This creates a file in which column alignment is preserved by adding space characters to the data in each column so that each column is always filled to its maximum width.
Text (Tab Delimited) (*.TXT) This separates the cells of each row with tab characters.
Unicode Text (*.TXT) This is a worldwide standard text format that stores each character as a unique number; Unicode defines a number for every character in every language and on any computer platform.
CSV (Comma Delimited) (*.CSV) This separates the cells of each row with commas. Comma-delimited text files are preferable to tab-delimited files for importing into database management programs. (Many database management programs can accept either form of text file, but some accept only .csv files.) Also, many word-processing applications can use .csv files to store the information for mail merge operations.
Text (Macintosh) (*.TXT) This saves the current worksheet as a tab-delimited text file using the Macintosh character set.
Text (MS-DOS) (*.TXT) This saves the current worksheet as a tab-delimited text file compatible with the character-based MS-DOS interface.
CSV (Macintosh) (*.CSV) This saves a comma-delimited text file using the Macintosh character set. The differences between the normal, Macintosh, and MS-DOS variants of each file type have to do only with characters that lie outside the normal 7-bit ASCII range.
CSV (MS-DOS) (*.CSV) The MS-DOS options use the IBM PC extended character set. (You might see this referred to also as OEM text.) Select one of these options if you intend to import your text file into a non-Windows-based application.
Note
Excel 2010 (and Excel 2007) uses a file format that is incompatible with previous Excel versions. And Excel 2003 shares with its predecessors (Excel 2002, Excel 2000, Excel 97) a file format that is incompatible with even older Excel versions. However, you can use the Save As command and Save As Type list to export Excel 2010 and Excel 2007 workbooks using two formats that play nicely with older versions of Excel: Excel 97-2003 Workbook and Microsoft Excel 5.0/95 Workbook.
Note
If you regularly share files with colleagues using different vintages of the program, you might want to read Ensuring File Compatibility with Previous Versions of Excel on page 52.
You can use a few other file formats, most of which you won’t even need unless you have a particular program with which you want to share data. Data Interchange Format (DIF) is a legacy format that allows the specification of data in rows and columns, saves only the active worksheet, and does not process graphic content. Symbolic Link (SYLK), a format that dates back to the days of VisiCalc and Multiplan, is a sort of “rich-text format” for spreadsheets that saves only the active worksheet and does not process graphic content. Portable Document Format (PDF) and XML Document (XPS) are formats that allow accurate visual representations of documents to be easily shared across platforms by using free and easily obtainable Web-based viewers.