Managing Tabulating Data in Excel

ng a new query for linking with a database Excel will first ask for the source of the data (that is, the format in which the source data is stored)— for an illustration of this, see Figure 182. In the figure, five possible sources are shown26. You may have more or less show up on your computer. The formats that show up are a function of the “drivers” (a program that, in this context, allows a program to read a file in the format associated with the specific “driver.” You can buy/download drivers for different database formats. 26 Note that Excel and Text Files are not, strictly speaking, database formats. Nevertheless, the same protocol can be used to read files form these sources also. In a way, an Excel file is like a database. More on this later in the chapter — see Table 96 on page 177 Linking to a Database 179 Figure 182: Step one of the Query wizard— choosing the data source Check the option “Use the Query Wizard.” The database file you will use in this example is an Access database. So, choose the database format “Microsoft Access 97.” Click on the button Browse. In The relevant dialog (shown in Figure 183), browse through the network of drives and choose the Access file that has the source data27. (The file — “Bookstor.mdb”— is included in the zipped file you downloaded.) Execute the dialog by clicking on the button OK. The query wizard is launched— it is shown in Figure 184. 27 Database files typically have certain security features. For example, you may have permission to read data from the database but not to write data to it. If that is the case, then check the box “Read Only.” In addition, the database may be on a network path that is not shown in the list of drives in the drop-box “Drives.” You will have to click on the button “Network” and choose the appropriate drive and path. Usually you will need a password to access the drive and / or the database file. Consult the “techies” in your office / school for more information. Managing & Tabulating Data in Excel 180 Figure 183: Step one of the Query wizard— choosing the path and name of the database file 12.2.B STEP 2: CHOOSING THE COLUMNS/FIELDS/VARIABLES TO IMPORT After you click on “OK” in the dialog shown in Figure 183, the “Query Wizard” will be launched. The first dialog within this wizard is shown in Figure 184. The left half of this dialog shows the names of all the tables in the database file. Notice the “plus” signs to the left of each table name. If you click on the sign, then the fields in that table can be viewed (as shown in Figure 185 for the table “Authors”). Linking to a Database 181 Figure 184: Step two of the Query wizard (choosing the data series to import)— 1 Note that each field (which corresponds to a column in Excel) must be fetched from inside a table; the table itself needs to be accessed through the database file. Figure 185: Step two of the Query wizard (choosing the data series to import)— 2 Managing & Tabulating Data in Excel 182 Click on a field name and then click on the button “Preview Now.” You will see a sample of the data in the selected field. The next figure illustrates this. Figure 186: Step two of the Query wizard (choosing the data series to import)— 3 Selecting fields for import into Excel How does one select the field/column one wants to import into Excel? For an entire table, click on the table's name on the left half and click the “right” arrow button. (This button is marked by the arrow in Figure 187.) For a specific field or column, first click on the “plus” sign (click on a specific field/column name, and then click on the right arrow button. (The right arrow button is marked by the arrow in Figure 187.) Note that each field you choose will be imported as a column onto the Linking to a Database 183 Excel worksheet. So, if you choose X number of columns/fields, you will import X columns in Excel. Figure 187: Step two of the Query wizard (choosing the data series to import)— 4 After choosing all fields/columns from the table “Authors,” I clicked on the table name “Title Author” and chose all the fields in that table except for “Au_ID” (because the field is common to the two tables I have accessed the fields from— “Authors” and “Title Author” — I only need to select it from one of the tables. (For an illustration of this, see Figure 188.) You may follow my example, or select any other fields/columns. Managing & Tabulating Data in Excel 184 Figure 188: Step two of the Query wizard (choosing the data series to import)— 5 The fields are selected. Move to step three by clicking on the button Next. 12.2.C STEP 3: (PRE–) FILTERING THE DATA TO BE IMPORTED Once you choose all the fields you need, this step of the wizard is complete. Move on to the next step/dialog by clicking on the button Next. The “Filter” dialog— shown in Figure 189— opens. This dialog allows you to “Filter” data; to “Filter in” data you desire to import and “Filter out” cases you do not want to import. “Filtering” implies the selection of a subset of the fields (that you selected in the dialog shown in the previous figure) based on criteria defined by you. You are “Querying” the database— “Go get me only the fields I selected, in the tables I selected and only for those cases/rows that match my criteria for selection.” Linking to a Database 185 Figure 189: Step three of the Query wizard (setting the criteria for selecting the observations to be imported)— 1 Assume the first criterion is the selection of only those cases/rows in which the “Au_ID” is field/column is not empty (null). Figure 190: Step three of the Query wizard (setting the criteria for selecting the observations to be imported)— 2 Managing & Tabulating Data in Excel 186 Click on the field name “Au_ID.' on the right side of the dialog, click on the downward arrow (for an illustration of this, see Figure 190), and choose the option “is Not Null” (for an illustration of this, see Figure 191). Figure 191: Step three of the Query wizard (setting the criteria for selecting the observations to be imported)— 3 The first criterion has been defined. Assume that the second criterion is “only cases/row where the field/column “Year Born” is between 1945 and 1954.” On the left half of the dialog, click on the field/column label “Year Born” (for an illustration of this, see Figure 192). Enter the criteria in the right half of the dialog. First, click on the downward arrow. Linking to a Database 187 Figure 192: Step three of the Query wizard (setting the criteria for selecting the observations to be imported)— 4 You will notice a list of possible operators. Choose the operator “greater than or equal to” (for an illustration of this, see Figure 193). On the right of the operator list, you will view another downward arrow. Click on that— you will see all the values inside the field/column “Year Born. Choose the value “1945” (or type in the value). Even though you are in Excel, you have picked up information from the database file! Excel is already “Querying” the database and has picked up information like the type of data in each field— numeric or string/text, the range of values, etc. Therefore, although you are in Excel, and remain in Excel throughout the process of querying, Excel has managed to enter into the database file and obtain the table definitions, field definitions, etc. Managing & Tabulating Data in Excel 188 Figure 193: Step three of the Query wizard (setting the criteria for selecting the observations to be imported)— 5 Figure 194 shows the first half of the criterion “is greater than or equal to 1945.” I also want only those values where “Year Born” is “less than or equal to 1954.” Because it is an additive criteria (that is, I want both criteria to be obeyed), choose the option “and” as shown in Figure 194. Click on the downward arrow in the second row for criteria and choose the criterion in a similar fashion as done for the first criteria. You may follow my example, or set your own filter. Linking to a Database 189 Figure 194: Step three of the Query wizard (setting the criteria for selecting the observations to be imported)— 6 Now only those rows/cases will be selected which fit the criteria “Au_ID is not empty (null) and the Year Born is between 1945 and 1954, end points inclusive.” (For an illustration of this, see Figure 195.) Figure 195: Step three of the Query wizard (setting the criteria for selecting the observations to be imported)— 7 Managing & Tabulating Data in Excel 190 This step of the wizard has been completed. Move to the next step/dialog by clicking on the button Next. 12.2.D STEP 4: PRE–SORTING THE DATA TO BE IMPORTED The next dialog (shown in the next figure) in the wizard allows you to Sort the data (as it is read into Excel) by the fields of your choice. I have chosen to Sort [a] first by ascending values of the field “Year Born” and [b] second by ascending values of the field “Author.” You may follow my example, or set your own sorting rules. Figure 196: Step four of the Query wizard (pre–Sorting the data to be imported) You have completed step four of the process. Move to step five by clicking on the button Next. Linking to a Database 191 12.2.E STEP 5: SAVING THE QUERY (THAT IS, SAVING STEPS 1–4) The “Query” is defined! You may want to use the query again, edit (change) it, extend it, or check its structure later. In order to make these processes possible, click on the button “Save Query” (for an illustration of this, see Figure 197) and save the query (for an illustration of this, see Figure 198). Figure 197: Step five of the Query wizard (saving the query)— 1 Figure 198: Step five of the Query wizard (saving the query)— 2 When you click on the button “Save,” you will be taken back to the query wizard’s last dialog (shown in Figure 197). Managing & Tabulating Data in Excel 192 12.2.F STEP 6: WRAPPING IT UP— GETTING THE DATA INTO EXCEL Click on the option “Return Data to Excel” and click on the button Finish. (For an illustration of this, see Figure 199.) Figure 199: Step six of the Query wizard (getting the data into Excel)— 1 Choose where you want the data to be read int. I advise choosing the option “New Worksheet.” (For an illustration of this, see Figure 200.) Figure 200: Step six of the Query wizard (getting the data into Excel)— 2 Linking to a Database 193 The data is read into Excel as shown in Figure 201. Figure 201: The imported data Save the Excel file in a folder that has access to the database file. 12.3 REFRESHING THE LINK BETWEEN THE EXCEL RANGE AND DATA IN THE DATABASE FILE The data is a “live link.” This Excel worksheet has automatically been linked to the query you created. Whenever you want to replace the data in the worksheet with the latest data in the database, open the Excel file, and click on this worksheet. Select the option DATA/REFRESH DATA! You have a live link to a database. This is extremely useful if you want to add dynamism to your Excel file and/or if your source data is of a kind that is regularly updated. Managing & Tabulating Data in Excel 194 Figure 202: Menu on a worksheet that contains imported data Note that all charts, tables, and formulas that reference the data in the linked cells will also be automatically updated when you refresh the link. When making a Pivot Report, you can link directly to the source database by choosing the option “external data source” in the first dialog of the Pivot Report wizard. This dialog is shown in Figure 122 on page 129. 12.4 EDITING AN EXISTING QUERY Choose the worksheet that has the downloaded data and follow the menu path DATA/GET EXTERNAL DATA/EDIT QUERY. You will be shown the query wizard. Use steps similar to those shown on 12.2 to edit and redefine the query. Linking to a Database 195 12.5 USING “EXTERNAL DATA SOURCE” TO CREATE A PIVOT REPORT While making a Pivot Report, one option for the “source” data is “External data source.” In the previous chapter on Pivot Reports, I skipped over that option (for an illustration of this, see Figure 122 on page 129 and footnote 17 on page 128). The use of External data sources required the knowledge you got in this chapter. Make a Pivot Report using the external data source used in this chapter. 12.6 NEW IN THE XP VERSION OF EXCEL: OLAP 12.6.A DATA FROM THE INTERNET The procedures have new features that allow data import using the latest in internet technology. Online Analytical Processing (OLAP) is a new mode for obtaining data. 12.7 ICONS RELEVANT TO EXTERNAL DATA You can customize the set of icons shown on the toolbars at the top of the screen. This can save time by enabling you to place the often–used buttons onto the toolbar. Please refer to Volume 3: Excel– Beyond The Basics to learn how and why to customize toolbars and the icons shown in Managing & Tabulating Data in Excel 196 them. The icons shown in the next figure provide easy access to refreshing linked data or to changing the properties of the link with the external data source. Figure 203: Icons for the Query or Import wizard Managing & Tabulating Data in Excel 198 CHAPTER 13 READING ASCII TEXT DATA Contents of chapter: — UNDERSTANDING ASCII TEXT DATA — WHY IS DATA STORED AND DISTRIBUTED IN THIS FORMAT? — WHAT IS SPECIAL ABOUT THIS FORMAT? — FIXED–WIDTH/COLUMN — DELIMITED/FREE-FIELD — READING DELIMITED/FREE-FIELD ASCII TEXT DATA — STEP 1: CHOOSING WHETHER THE FORMAT IS DELIMITED OR FIXED WIDTH — STEP 2: CHOOSING THE CORRECT DELIMITER: TAB, COMMA, SPACE, ETC — DETERMINING IF EXCEL/YOU HAVE CHOSEN THE WRONG DELIMITER — STEP 3: DEFINE THE DATA FORMATS (IF NOT DONE AUTOMATICALLY/CORRECTLY BY EXCEL) — CONVERTING INTO AN EXCEL FILE — READING FIXED WIDTH/COLUMN ASCII TEXT The most difficult stage of a project should not be the reading in of data. Unfortunately, the “ASCII Text” format (often called simply “ASCII” or “Text” format) can make this stage extremely frustrating and time consuming. Reading ASCII Text Data 199 Advice Though I teach how to read ASCII Text data into Excel, bear in mind the following issues: — If the suppliers of data can provide you with data in a simpler format then request them to do so! — An excellent option for quickly converting data from different file formats into Excel format is by the use of data conversion software like STATTRANSFER (web site: www.stattransfer.com) or DBMSCOPY (web site: www.dbmscopy.com). 13.1 UNDERSTANDING ASCII TEXT DATA Why is data stored and distributed in this format? Most large data sets, especially those available to the public via CD–ROM or the web, are in ASCII Text format because of (1) the relatively small disk space needed to store and distribute ASCII TEXT data and (2) the conformity of ASCII with standardization guidelines across operating systems and hardware. What is special about this format? As the name “Text” suggests, ASCII Text data is data written in the form of text, as in a Note Pad, Word, or WordPerfect file. In contrast to ASCII, data is organized into rows and columns in Excel. Managing & Tabulating Data in Excel 200 When ASCII data is being entered, data–entry involves typing the data for variables next to each other while separating different variables by a standard character28 (called a “Delimiter”) or by column positions. These concepts will become clear as I now delve deeper into understanding the two broad types of ASCII TEXT formats. Fixed–width/Column29 In this format, the data is identified by position. When you obtain such data, you will need a “code book” which tells you the position each series occupies in the file. Assume there are three variables: ID, First Name, and Last Name. The case to be entered is “ID=009812348,” “First Name = VIJAY,” and “Last Name = GUPTA.” The codebook states that the series “ID” is to be entered in the position 1 to 9, “first name” in 10 to 14 and last name in “15 to 21.” The data–entry person does this. When you read the data into Excel, you have to provide the program with information on the positions of variables. That is, reading our sample file would involve, at the minimum, the provision of the following information: “ID=1 to 9,” “First Name =10 to 14” and “Last Name =15 to 21.” This is shown in the next text-box. 28 The standard “delimiters” are: tab, comma, or space. 29 The reading of fixed-field / column ASCII text data is beyond the scope of this book. Reading ASCII Text Data 201 Location 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Actual data 0 0 9 8 1 2 34 8 V I J A Y G U P T A Delimited/Free-Field In this ASCII Text format, spaces, tabs, commas, or semi–colons separate data series. That is, after entering the data for one of the variables, the data–entry person places a Delimiter to indicate the beginning of the next series. The next text-box shows this for the three common Delimiters: space, tab, and comma. • Space delimited (.prn): 00981234821 VIJAY GUPTA • Tab delimited: (.dat): 00981234821 VIJAY GUPTA • Comma delimited (.csv): 00981234821,VIJAY,GUPTA ASCII files may have many other extensions. Some of the extensions I have seen are “.asc,” “.txt,” “.hac,” etc. In addition, a “.csv” file may not always be a comma–delimited file. 13.2 READING DELIMITED/FREE-FIELD ASCII TEXT DATA Using the mouse, select the menu path FILE/OPEN. Choose the ASCII TEXT file — use the file “Text format.txt” supplied in the zipped file— and click on “Open.” The “File Open” dialog opens — this dialog is shown in Figure 204. Managing & Tabulating Data in Excel 202 Figure 204: The “File Open” dialog Irrespective of the file extension, Excel automatically recognizes the file as an ASCII Text file, and, consequently, launches the 1st dialog of the “Text Import Wizard” (shown in Figure 205). 13.2.A STEP 1: CHOOSING WHETHER THE FORMAT IS DELIMITED OR FIXED-WIDTH The file could have either of the two broad categories of formats: delimited (also called “free–field”), or fixed–width (also called “fixed-column”). The Excel Wizard 'senses' that the ASCII Text file is of the delimited type. Reading ASCII Text Data 203 Therefore, it selects the option “Delimited” in the area “Original data type30.” Figure 205: Step one of the Text Import wizard Interestingly, Excel can also read in ASCII Text data that was stored using Macintosh, DOS, or OS/2 operating systems (for an illustration of this, see Figure 206.) 30 Excel may make a mistake sometimes; if it has selected the incorrect option (between 'Delimited' or 'Fixed width'), then you should make the correction manually by choosing the correct option. (The agency that supplied the data should have supplied you information on the specifications of the ASCII text file.) Managing & Tabulating Data in Excel 204 Figure 206: Choosing the “File Origin” Choose the options as shown in Figure 207. This step of the wizard has been completed. Move on to the next step/dialog by clicking on the button Next. 13.2.B STEP 2: CHOOSING THE CORRECT DELIMITER: TAB, COMMA, SPACE, ETC Now you are at step 2 of the Text Import Wizard (for an illustration of this, refer to Figure 207). In step 2, you will choose the precise type of Delimiter. As shown in Figure 207, Excel has 'sensed' the correct Delimiter is “Tab.” Look at the sample of the file in the area “Data preview.” The long vertical lines indicate the column/series end/beginning points. Does the sample make sense? If the answer is in the negative, you should experiment with other types of Delimiters. Reading ASCII Text Data 205 Figure 207: Step two of the Text Import wizard — 1 Determining if Excel or you have chosen the wrong Delimiter Let us try the “Space” Delimiter. The “Data preview” area now shows how the data will look if read in as space–delimited. The sample looks patently incorrect31. This is the good thing about the wizard— you can view samples to assist you in determining the best Delimiter. (Note: Usually the correct Delimiter will be provided to you by the agency that provided the data.) 31 Look at the haphazard position of the dark black vertical bands in the first row of the data (in the “Data preview” box shown in Figure 692. Managing & Tabulating Data in Excel 206 Figure 208: Step two of the Text Import wizard — 2 Therefore, select the correct Delimiter— “Tab.” Figure 209: Step two of the Text Import wizard — 3 You have finished the second dialog in the wizard. Click on the button Next. 13.2.C STEP 3: DEFINE THE DATA FORMATS (IF NOT DONE AUTOMATICALLY/CORRECTLY BY EXCEL) In this step (step 3 of the wizard), you can define the data formats for each series/column or to skip the import of a certain column. I find these two Reading ASCII Text Data 207 issues dealt better in Excel— outside of the “Text Import Wizard32.” Therefore, just click on the button Finish. Figure 210: Step three of the Text Import wizard The data is read in and is shown in Figure 211. 32 Using the menu option FORMAT / CELLS and the key DELETE. Managing & Tabulating Data in Excel 208 Figure 211: The data is imported into Excel 13.2.D CONVERTING INTO AN EXCEL FILE Choose the menu option “FILE/SAVE AS“ and save the file as an Excel workbook so that, in the future, you do not have to use the “Import Text Wizard” to read the same ASCII Text file. Has Excel read in all the cases? Please check. The ASCII Text file may be much larger than the holding capacity of an Excel worksheet (depending on your version of Excel and the size of the ASCII TEXT file). Excel may not give a message like “Failed to read in all the data.” I would suggest scrolling down to the end of the Excel worksheet. If the last row and/or column are filled then it is highly probable that some of the data was not read. You should check if the data formats are correct for the purpose of your use of the data. In the situation of errors id formats, choose the column(s) reformat the data type using the menu option FORMAT/CELL/NUMBER. Perform this procedure separately for each data type. (The F4 key — whose use is taught in Volume 1: Excel For Beginners— makes such work much easier. Do the formatting for one column. After that, choose other Reading ASCII Text Data 209 columns to which you want to apply the same formatting and then press the F4 key.) 13.3 READING FIXED-WIDTH ASCII TEXT Select the option FILE/OPEN. Choose the ASCII Text file. Irrespective of the file extension, Excel automatically recognizes the file as an ASCII TEXT file, and, consequently, launches the 1st dialog of the “Text Import Wizard” shown in the next figure. The Excel Wizard 'senses' that the ASCII Text file is of the Fixed-width type. Therefore, it selects the option “Fixed-width” in the area “Original data type33.” 33 Excel may make a mistake sometimes; if it has selected the incorrect option (between 'Delimited' or 'Fixed width'), then you should make the correction manually by choosing the correct option. (The agency that supplied the data should have supplied you information on the specifications of the ASCII text file.) Managing & Tabulating Data in Excel 210 Figure 212: Step one of the Text Import wizard for Fixed-width Text data Click on the button Next. The second dialog of the wizard opens. Excel “senses” the column widths and places demarcation lines. (The demarcating lines are called “Break Lines.”) Reading ASCII Text Data 211 Figure 213: Step two of the Text Import wizard for Fixed-width data — 1 Excel often makes errors in picking the demarcation points. Use the mouse to set the correct column widths. Managing & Tabulating Data in Excel 212 Figure 214: Step two of the Text Import wizard for Fixed-width Text data — 2 Click on the button Next. In this dialog (step 3 of the wizard), you can define the data formats for each series/column or to skip the import of a certain column. I find these two issues dealt better in Excel— outside of the “Text Import Wizard34.” So, just click on the button Finish and the data are imported into Excel. 34 Using the menu option FORMAT / CELLS and the key DELETE. Reading ASCII Text Data 213 Figure 215: Step three of the Text Import wizard for Fixed-width Text data Figure 216: The data is imported into Excel Choose the menu option “FILE/SAVE AS save the file as an Excel workbook. This ensures that, in the future, you do not have to use the “Import Text Wizard” to read the same ASCII Text file. Paste Special 215 CHAPTER 14 PASTE SPECIAL This chapter teaches the following topics: — PASTING THE RESULT OF A FORMULA, BUT NOT THE FORMULA — OTHER SELECTIVE PASTING OPTIONS — PASTING ONLY THE FORMULA (BUT NOT THE FORMATTING AND COMMENTS) — PASTING ONLY FORMATS — PASTING DATA VALIDATION SCHEMES — PASTING ALL BUT THE BORDERS — PASTING COMMENTS ONLY — PERFORMING AN ALGEBRAIC “OPERATION” WHEN PASTING ONE COLUMN/ROW/RANGE ON TO ANOTHER — MULTIPLYING/DIVIDING/SUBTRACTING/ADDING ALL CELLS IN A RANGE BY A NUMBER — MULTIPLYING/DIVIDING THE CELL VALUES IN CELLS IN SEVERAL “PASTED ON” COLUMNS WITH THE VALUES OF THE COPIED RANGE — SWITCHING ROWS TO COLUMNS This less known feature of Excel has some great options that save time and reduce annoyances in copying and pasting. Managing & Tabulating Data in Excel 216 14.1 PASTING THE RESULT OF A FORMULA, BUT NOT THE FORMULA Sometimes one wants the ability to copy a formula (for example, “=C223 + D223)”) but paste only the resulting value. (The example that follows will make this clear.) Select the range “F223:F235” on worksheet ““main.” Choose the menu option FILE/NEW and open a new file. Go to any cell in this new file and choose the menu option EDIT/PASTE SPECIAL. In the area “Paste,” choose the option “Values” as shown in Figure 217. Figure 217: The PASTE SPECIAL dialog in Excel versions prior to Excel XP Paste Special 217 In Excel XP, the “Paste Special” dialog has three additional options: • Paste Formulas and number formats (and not other cell formatting like font, background color, borders, etc) • Paste Values and number formats (and not other cell formatting like font, background color, borders, etc) • Paste only “Column widths.” Figure 218: “Paste Special” dialog In Excel XP In Excel XP, the “Paste” icon provides quick access to some types of “Paste Special.” The options are shown in the next figure. The calculated values in the “copied” cells are pasted. The formula is not pasted. Try the same experiment using EDIT/PASTE instead of EDIT/PASTE SPECIAL. The usefulness of the former will be apparent. Figure 219: The pasting options can be accessed by clicking on the arrow to the right of the “Paste” icon Managing & Tabulating Data in Excel 218 14.2 OTHER SELECTIVE PASTING OPTIONS 14.2.A PASTING ONLY THE FORMULA (BUT NOT THE FORMATTING AND COMMENTS) Choose the option “Formulas” in the area “Paste” of the dialog (user-input form) associated with the menu “EDIT/PASTE SPECIAL.” This feature makes the pasted values free from all cell references. The “pasted on” range will only contain pure numbers. The biggest advantage of this option is that it enables the collating of formula results in different ranges/sheets/workbooks onto one worksheet without the bother of maintaining all the referenced cells in the same workbook/sheet as the collated results. Figure 220: Pasting formulas only 14.2.B PASTING ONLY FORMATS Choose the option “Formats” in the area “Paste” of the dialog associated with the menu “EDIT/PASTE SPECIAL use the “Format Painter” icon. I prefer using the icon. Paste Special 219 Refer to Volume 1: Excel for Beginners for a discussion on the format painter. 14.2.C PASTING DATA VALIDATION SCHEMES Pick the option “Validation” in the area “Paste” of the dialog associated with the menu “EDIT/PASTE SPECIAL.” Data validation schemes are discussed in Volume 4: Managing & Tabulating Data in Excel. This option can be very useful in standardizing data entry standards and rules across an institution. 14.2.D PASTING ALL BUT THE BORDERS Choose the option “All except borders” in the area “Paste” of the dialog associated with the menu “EDIT/PASTE SPECIAL.” All other formatting features, formulae, and data are pasted. This option is rarely used. 14.2.E PASTING COMMENTS ONLY Pick the option “Comments” in the area “Paste” of the dialog associated with the menu “EDIT/PASTE SPECIAL.” Only the comments are pasted. The comments are pasted onto the equivalently located cell. For example, a comment on the cell that is in the third row and second column that is copied will be pasted onto the cell that is in the third row and second column of the “pasted on” range. This option is rarely used. Managing & Tabulating Data in Excel 220 14.3 PERFORMING AN ALGEBRAIC “OPERATION” WHEN PASTING ONE COLUMN/ROW/RANGE ON TO ANOTHER 14.3.A MULTIPLYING/DIVIDING/SUBTRACTING/ADDING ALL CELLS IN A RANGE BY A NUMBER Assume your data is expressed in millions. You need to change the units to billions— that is, divide all values in the range by 1000. The complex way to do this would be to create a new range with each cell in the new range containing the formula “cell in old range/1000.” A much simpler way is to use PASTE SPECIAL. On any cell in the worksheet, write the number 1000. Click on that cell and copy the number. Choose the range whose cells need a rescaling of units. Go to the menu option EDIT/PASTE SPECIAL and choose “Divide” in the area Options. The range will be replaced with a number obtained by dividing each cell by the copied cells value! The same method can be used to multiply, subtract or add a number to all cells in a range Figure 221: You can multiply (or add/subtract/divide) all cells in the “pasted on” range by (to/by/from) the value of the copied cell Paste Special 221 14.3.B MULTIPLYING/DIVIDING THE CELL VALUES IN CELLS IN SEVERAL “PASTED ON” COLUMNS WITH THE VALUES OF THE COPIED RANGE You can use the same method to add/subtract/multiply/divide one column’s (or row’s) values to the corresponding cells in one or several “pasted on” columns (or rows). Copy the cells in column E and paste special onto the cells in columns C and D choosing the option “Add” in the area “Operation” of the paste special dialog. (You can use EDIT/UNDO to restore the file to its old state.) 14.4 SWITCHING ROWS TO COLUMNS Choose any option in the “Paste” and “Operations” areas and choose the option “Transpose.” If pasting a range with many columns and rows you may prefer to paste onto one cell to avoid getting the error “Copy and Paste areas are in different shapes.” Index 223 CHAPTER 15 SAVING OR TRANSFORMING TO SPECIAL WEB AND DOCUMENT FORMATS This chapter shows how to save/convert Excel files to the following formats: — PDF — WEB (HTML, WEB ARCHIVE) — XML 15.1 CONVERTING TO A PDF (ADOBE ACROBAT) FILE The PDF format has become popular for distributing reports, especially over the Internet. The format has some advantages over an Excel/Word file format: — The PDF file has better security measures — you can create a file that cannot be printed, copied, or from which material cannot be extracted. — The file looks the same on all computers, and Managing & Tabulating Data in Excel 224 — Users are less hesitant to download PDF files (compared to Microsoft Office files) because of the lower risk of downloading a virus. Adobe offers several tools for converting to PDF format. Check out their web site adobe.com. 15.1.A CREATING A PDF (ADOBE ACROBAT) FILE FROM THE PRINTOUT I now discuss one of the tools you can use to convert to PDF. The tool is a PDF “Printer Driver.” With this tool, you print your file into a PDF file. You will see the option “Acrobat PDF Writer” as one of the printers in the area “Name” as shown at the top of the next figure. Note: The PDF Printer Driver is sold at Adobe.com and third-part vendors. Index 225 Figure 222: PDF Writer Choose the “Acrobat PDF Writer” printer option, and the options you desire in the areas “Print range” and “Print what” of the “Print” dialog which is reproduced above. Execute the dialog by clicking on the button OK. The “Save PDF File As” dialog shown in the next figure opens. Choose a location for the PDF file and write in a file name. Execute the dialog by clicking on the button OK. The Excel file will be “printed” out as a PDF file. Selecting the check-box “View PDF File” will follow the printing task with a display of the file within the Adobe Acrobat Reader. Managing & Tabulating Data in Excel 226 Figure 223: “Save PDF As” dialog 15.1.B OTHER TOOLS FOR CONVERTING ONE OR MULTIPLE FILES INTO PDF Adobe offers several tools for converting to PDF format. Check out their web site adobe.com. 15.2 SAVING AS AN HTML FILE An Excel worksheet, chart, or the entire workbook can be saved as an HTML file. This file can be placed on the web. It opens in any web browser like Internet Explorer, Netscape, Opera, or AOL. Index 227 Figure 224: Saving as a web page Access the feature through the menu path FILE/SAVE AS WEB PAGE. Choose what you want to include in the HTML file. Click on the button “Save.” 15.2.A INTERACTIVITY WHEN SAVING A WORKSHEET If you want visitors to the web site to be able to work on Excel data while inside a browser application, then click on the option “Add interactivity.” This option only works if you are choosing to save one Excel worksheet or chart as an HTML file— see the next figure. Inquire whether FP extensions or Microsoft Office extensions are needed for this. (Not all web hosts support this functionality.) This Managing & Tabulating Data in Excel 228 remarkable feature adds Excel–like functionality to data displayed on a web page! Figure 225: Adding Interactivity In the area “Viewing options,” choose what kind of interactivity you desire — “Spreadsheet” or “Pivot Report.” If the current worksheet is not a Pivot Report then you should choose “Spreadsheet Functionality” Refer to the next section to learn how to use this interactivity after opening the file in a web browser. Figure 226: The two modes of Interactivity At this stage, you will be prompted to write a web page title. Type a title and execute the dialog by clicking on the button OK. Excel will prompt you to enter information about the specific range of data you need on the HTML file. This is shown in the figure below — see the area “Choose” at the top of the dialog. Index 229 Figure 227: Setting a title for the web page Figure 228: Choose the range of cells to publish as a web page Click on the option “Publish” to complete the process of creating the HTML file. 15.2.B INTERACTIVITY WHEN SAVING A CHART Assume you are saving a chart as an HTML file. Choose the chart and choose the menu option FILE/SAVE AS WEB PAGE. Choose the option Managing & Tabulating Data in Excel 230 “Add interactivity,” type in a name for the HTML file, choose a path for saving the file, and then click on “Save.” Figure 229: Saving a Chart with Interactivity In the dialog that opens (shown in the next figure), choose the options as shown. Click on the option “Publish” to complete the process of creating the HTML file. Figure 230: The “Publish as Web Page” dialog for a chart The option “Open published web page in browser” will launch the default Index 231 Internet Browser on your computer and display the file you just saved as a web file. 15.3 NEW IN THE XP VERSION OF EXCEL: WEB ARCHIVE FORMAT AND XML The Web Archive format allows the saving of an entire file as on web file. AutoRepublish facilitates easy updating of Excel files on the web. In addition, in the XP version of Excel, you can save in XML format. 15.3.A WEB ARCHIVE Figure 231: Saving as a Web Archive Managing & Tabulating Data in Excel 232 I recommend selecting “Add interactivity.” The file may be opened in a web browser. (For optimal compatibility, use Internet Explorer.) An example is shown in the next illustration. The example is from the sample file “DateTime.mhtml.” Figure 232: The file viewed in a web browser The file has several sheets. Focus on the downward arrow at the bottom left of the previous figure. On clicking the arrow at the bottom-left, you will see all the sheets in the file. The next figure illustrates this feature. Index 233 Figure 233: Viewing the sheets in the file. Clicking on the name of a sheet opens that sheet in the web browser. At the top of the file, you will see a short toolbar that provides access to interactivity features. The toolbar is reproduced in the next figure. Figure 234: The toolbar for interactivity The toolbar icons provide access to the following features (from left to right): • Undo • Cut • Copy • Paste • AutoSum • Sort Ascending • Sort Descending • Filter • Refresh All Managing & Tabulating Data in Excel 234 • Export to Excel • Commands & Options • Help The second-from-right icon (“Commands & Options”) provides access to many features of the file. On selecting this icon, the dialog shown in the next figure opens. The options in the tab “Workbook” provide access to features of the entire workbook. The order of the sheets can be changed using the up and down arrows to the right of the box that displays the sheet names. A sheet can be deleted by selecting it in the box and then clicking on the “Delete” button. Figure 235: Workbook Commands & Options Similarly, a new sheet can be inserted before an existing sheet by clicking Index 235 on the button “Insert.” In the area “Show/Hide,” you can decide whether to view the arrow that permits the selection of a sheet. The options in the tab “Sheet” (reproduced in the next figure), provide: • Access to a “Find” utility • Options for viewing the sheet in reverse order (right to left) • Options for hiding row/column headers & Gridlines. Figure 236: Sheet Commands & Options The “Formula” tab provides access to formula editing. The area “Names” maps each cell to a “Location” on the file. The formulas may reference these names instead of ranges. See Volume 1: Excel For Beginners for more on “Named Ranges.” The next update of this book will contain more on using “Named Ranges” in formulas. Remember, that purchasing the book entitles you to one year of monthly updates to the text and the example worksheets. Managing & Tabulating Data in Excel 236 Figure 237: Formula Commands & Options The “Format” tab provides access to the Undo command, and basic text and cell formatting. The dialog is reproduced in the next figure. Macros and utilities for improved conversion of Excel sheets into web formats. The updates will be announced on our site by Winter 2002. Index 237 Figure 238: Formatting Commands & Options 15.3.B XML An Excel workbook can also be saved as an “eXtended Markup Language” (XML) file. Managing & Tabulating Data in Excel 238 Figure 239: Saving as an XML Spreadsheet This format may gain in popularity because many other software companies are seeking to make XML compatibility a central feature of their products. Detailed discussion of this topic is beyond the scope of this book. Index 239 INDEX A A1........................................................95 ACCESS .............................................23 ADD-INS .............................................16 AND23, 40, 97, 126, 127, 174, 198, 215 ASCII198, 199, 200, 201, 202, 203, 208, 209, 213 ASCII TEXT DATA .........................198 AUDITING...........................................16 AUTOCORRECT ...........................16, 57 AUTOFILL.........................................68 AUTOFILTER ...........98, 100, 102, 104 AUTOFORMAT ...................................15 AVERAGE........................................119 C CELL ................................159, 208, 215 CELLS .......14, 15, 40, 80, 207, 212, 215 CLEAR.................................................13 COLUMN ......15, 97, 127, 132, 198, 215 COLUMNS ..........................14, 174, 215 COMMA ...........................................198 COMMENT .........................................15 COMMENTS ...............................14, 215 CONDITIONAL FORMATTING ... 16, 80, 81, 84, 87, 112 CONSOLIDATION ...................... 17, 124 CONSTANT GROWTH RATE ......... 59 COPY.............................. 13, 56, 76, 101 COPYING AND PASTING ......... 40, 97 CRITERIA ............................. 40, 80, 97 CRITERION ...................................... 97 CUSTOM LIST............................ 59, 68 CUSTOMIZE ................................. 16, 77 CUT .................................................... 13 D DATA ENTRY ................................... 59 DATA VALIDATION ........................ 40 DAY .................................................... 23 DBASE ............................................... 23 DELETE SHEET .................................. 13 DELIMITED / FREE-FIELD ASCII TEXT DATA ................................ 198 DELIMITER .................................... 198 E EDIT...12, 56, 60, 61, 63, 64, 66, 68, 72, 73, 76, 101, 109, 113, 114, 115, 116, 117, 194, 216, 217, 218, 219, 220, 221 Managing & Tabulating Data in Excel 240 EDITING AN EXISTING QUERY 174 ERROR ALERT................................. 40 EXTERNAL DATA............... 17, 174, 175 F F142 FILE..... 12, 23, 24, 25, 29, 30, 174, 198, 201, 208, 209, 213, 216, 227, 229 FILL.... 13, 59, 60, 61, 63, 64, 66, 68, 72, 73, 76 FILL IN NUMBERS AND DATES .. 59 FILLING FORMATS ........................ 59 FILTER ............ 17, 97, 98, 100, 102, 104 FIND ................................................... 13 FIXED.............................................. 198 FIXED WIDTH/COLUMN ASCII TEXT ............................................. 158, 198 FORM ......................... 17, 33, 34, 38, 56 FORM BASED DATA ENTRY......... 33 FORMAT15, 80, 87, 112, 198, 207, 208, 212 FORMULA ........ 14, 119, 126, 159, 215 FORMULA BAR................................... 14 FOXPRO ............................................ 23 FREEZE PANES .................................. 17 FUNCTION ........................... 14, 15, 126 FUNCTION / FINANCIAL .................. 14 FUNCTION / INFORMATION ............ 15 FUNCTION / LOGICAL...................... 14 FUNCTION / LOOKUP ....................... 15 FUNCTION / MATH & TRIG............... 15 FUNCTION / STATISTICAL................ 14 FUNCTION / TEXT..............................14 G GET EXTERNAL DATA .........178, 194 GO TO13, 109, 110, 113, 114, 115, 116, 117, 118 GOAL SEEK ..............................16, 171 GROUP AND OUTLINE .......................17 H HEADER ............................................14 HEADER AND FOOTER ......................14 HELP...................................................18 HIDE....................................................17 HTML ......... 28, 226, 227, 228, 229, 230 HYPERLINK .........................................15 I IF 198 IMPORT ...........................................174 INSERT ...............................................14 L LINKS...................................................13 LOTUS 1– 2– 3 ..................................23 M MACROS .............................................16 Index 241 MISSING VALUES ...........................59 MODE ................................................23 MONTHS ...........................................59 MOVE OR COPY SHEET ....................13 MS SQL SERVER..............................23 MULTIPLYING/DIVIDING/SUBTRA CTING/ADDING ALL CELLS IN A RANGE BY A NUMBER ............215 N NA.......................................................15 NEW QUERY ..................................178 NOT ..........................................198, 215 O OBJECT .........................................13, 15 OFFICE ASSISTANT ...........................18 OFFICE CLIPBOARD ..........................13 ONLINE COLLABORATION ...............16 OPEN .........12, 23, 24, 25, 30, 201, 209 OPTIONS.................17, 68, 69, 126, 215 OR.......................................33, 174, 198 ORACLE ............................................23 P PAGE BREAK .............................13, 14 PAGE BREAK PREVIEW .....................13 PAGE SETUP.......................................12 PARADOX..........................................23 PASTE ..13, 56, 101, 216, 217, 218, 219, 220 PASTE SPECIAL...13, 56, 216, 217, 218, 219, 220 PASTING ALL BUT THE BORDERS ..................................................... 215 PASTING COMMENTS ................. 215 PASTING DATA VALIDATION .... 215 PASTING ONLY FORMATS.......... 215 PASTING ONLY THE FORMULA 215 PDF.....................28, 223, 224, 225, 226 PIVOT REPORT..17, 126, 127, 128, 141, 152, 174 PIVOT REPORT WIZARD ............. 126 PIVOT REPORTS ........................... 128 PRINT AREA ....................................... 12 PRINT PREVIEW................................. 12 PROPERTIES....................................... 12 PROTECTION ..................................... 16 Q QUATTROPRO ................................. 23 QUERY .................................... 174, 194 R REDO .................................................. 12 REFRESH DATA ............................ 193 REPLACE ............................................ 13 RIGHT................................................ 59 ROW ...................15, 126, 127, 132, 215 ROWS ...........................14, 97, 119, 215 Managing & Tabulating Data in Excel 242 ROWS TO COLUMNS.................... 215 S SAS .................... 23, 24, 25, 26, 28, 127 SAVE 12, 24, 25, 29, 208, 213, 227, 229 SAVE AS ......... 12, 24, 25, 208, 227, 229 SAVE AS WEB PAGE ......... 12, 227, 229 SAVE WORKSPACE ..................... 12, 29 SCENARIOS ............... 16, 160, 161, 168 SEARCH ............................................. 12 SHARE WORKBOOK .......................... 16 SHEET................................................. 15 SORT............................... 17, 90, 93, 120 SORTING BY ROWS........................ 89 SORTING RANGES THAT DO NOT COVER ENTIRE COLUMNS OR ROWS ............................................ 89 SPEECH........................................ 16, 78 SPELLING ........................................... 16 SPLIT .................................................. 17 SPSS .. 3, 5, 23, 24, 25, 26, 28, 127, 175 STATA ............................... 5, 23, 24, 28 STATUS BAR ....................................... 14 STYLE ................................................. 16 SUBTOTAL ..................................... 120 SUBTOTALS........................ 17, 119, 123 SUM ................................................. 119 T T 20, 23, 201 TAB .................................................. 198 TABLE .................................................17 TEXT ............................................23, 40 Text Files..........................................178 TIME ..................................................23 TOOLBAR ..........................................59 TOOLBARS............................13, 77, 141 TOOLS .... 16, 68, 69, 78, 160, 161, 168, 171 TYPE ..................................................33 U UNDO..........................................12, 221 V VALIDATION 17, 40, 41, 42, 46, 52, 55, 56 VALUE .......................................97, 159 VIEW ...............................13, 77, 86, 141 W WEB ....................................................16 WINDOW ............................................17 WORKSHEETS ....................................14 WORKSPACE ....................................23 Y YEAR ..................................................59 Index 243 Z ZOOM ..................................... 14, 86, 87 Managing & Tabulating Data in Excel 244 VJ Inc Corporate and Government Training We provide productivity-enhancement and capacity building for corporate, government, and other clients. The onsite training includes courses on: Office Productivity Software and Tools Data Mining, Statistics, Forecasting, Econometrics Financial Analysis, Feasibility Studies Risk Analysis, Monitoring and Management Building and using Credit Rating/Monitoring Models Specific software applications, including Microsoft Excel, VBA, Word, PowerPoint, Access, Project, SPSS, SAS, STATA, ands many other Contact our corporate training group at