Importing Report Data into Microsoft Excel

It is easier to work with the numbers you get in a monthly report if you import the data into Microsoft Excel. Once you have an Excel file, you can separate, arrange, and total the numbers as you see fit. There are two similar methods you can use to accomplish this.

In the first method, you need only copy the information you'd like to transfer from the e-mail by highlighting it, right-clicking and selecting Copy. Then open up a blank Excel document, right-click on the cell in the uppermost left hand corner and select Paste. This imports all of the data into the spreadsheet, but it doesn't put it into columnar format. To do that, you need to highlight the column you'd like modified, and then select Text to Columns under the Data menu. This will bring up the Text Import Wizard that is discussed in detail below.

Another, lengthier, method for importing report data into an Excel file is to save the e-mail as a text file. You must then open Excel, select the File menu and go down to Open, or click on the Folder icon near the upper left-hand corner. Near the bottom of the new window there is a drop-down box labeled: Files of Type. Scroll down to Text files in that menu. Once Text Files is selected, navigate to the file you saved from your e-mail and click on Open.

After completing either of the methods discussed above, the Text Import Wizard will pop up. It gives you the choice of selecting either a Delimited file type, or a Fixed Width file type. Either will work, but the simplest way is to choose Delimited, then click on Next.

If you choose Delimited, Excel will look at the file and assume spaces, commas, or tabs are the column separators. When importing a text file, you must select Space as the type of Delimiter you would like to use. As a result, the numbers are separated into convenient columns and rows.

After selecting Space, click on Next again, then Finish. You now have a workable Excel file with the report data nicely divided into rows and columns. One drawback to importing a text file into Microsoft Excel is that it isn't quite sure what to do with anything but numbers. Because there isn't a great deal of text, this is easily remedied by deleting the text and retyping it as you'd like.

It is important to save the file as an Excel file when you're through. If you just click on Save, Excel will assume you'd like to keep this as a text file. Go to File and select Save As. In the Save as Type drop-down list at the bottom, choose Microsoft Excel Workbook. Type in a suitable File Name and you're done.

This is what we started with:

This should be the end result: