Excel Importing Training

Microsoft Excel is a powerful tool for creating first rate reports. The step by step instructions below walk you through importing a file into MS Excel and then reformatting the output.

Goals

Upon completion of this 1-on-1 training, you will be able to:

Training

Use the Text Import Wizard to load the file

The Text Import Wizard is very helpful when loading a text file (usually with extension *.txt) into Excel. The wizard will walk you through the process in a step by step fashion.

  1. Start Excel.
  2. Select Office Button > Open and find the text file to be loaded. Note you may want to change the “Files of type” to show “All Files (.)” so that you see more than just Excel files. Click Open.
  3. Text Import Wizard – Step 1 of 3.
    • Indicate whether the file is delimited or fixed width and at what row to begin the import.
    • Click Next.
  4. Text Import Wizard – Step 2 of 3.
    • If the file is fixed width, indicate where one column of data ends and the next begins by using the break lines. Do this for all columns by using the horizontal scroll bar at the bottom of the window. Click Next.
    • If the file is delimited, specify the delimiter(s) and whether text strings have quotes around the data. Click Next.
  5. Text Import Wizard – Step 3 of 3.
    • Adjust the column data format as appropriate. The “General” format works fine in most cases, however columns containing date values should be altered to type “Date”.
    • Click Finish.

Save the file to a New Name

By saving the file now, you will keep all of your hard work importing the file. In addition, you will be saving it as an Excel spreadsheet which will also keep the formatting that has been achieved.

  • Select Office Button > Save As > Excel Workbook.
  • Choose the location for the file.
  • Choose a name for the file and enter this in the “File name” box.

Format the Spreadsheet

The Text Import Wizard will do what it can to format the report for you. Inevitably, however, there will be some work left for you to complete.

  1. Adjust the column widths to view all the data.
    • Click in the top left cell to select the entire spreadsheet.
    • Move the cursor until it is a double bar (between columns A and B).
    • Double click to expand all columns to display their data.
    • You may want to shrink the widths of one or more columns so they fit on the visible screen. Do this by moving the cursor to the vertical bar to the right of the column that is too wide until it is a double bar, click and hold, and drag the bar to the left.
  2. Change the page size.
    1. Select the Page Layout ribbon.
    2. Click the Orientation button, then choose Portrait or Landscape
      • Resize individual columns until they all fit on the page.
  3. Adjust the top of the page.
    • Option 1: Change the alignment.
      1. Right justify the text on one side. Left justify the text on the other side.
      2. Fix remaining text.
      3. Copy and paste the header to the other sections of the report.
    • Option 2: Merge the cells and retype the information.
      1. Select the cells to merge.
      2. Go to the Home ribbon.
      3. In the “Alignment” section, select the dropdown menu that currently reads “Merge & Center”, and choose one of the merging options.
      4. Retype any missing information.
      5. Copy and paste the header to the other sections of the report.
    • Option 3: Create a page header.
      1. Go to the Insert ribbon.
      2. In the “Text” section, click the Header & Footer button.
      3. Type your desired header in the box.
      4. Click anywhere on the worksheet to exit the header.

Correct the alignment on the negative numbers

Numbers with the negative sign after the number, by default come into Excel as text strings rather than as actual numbers. This can be changed by following the steps below.

  1. Locate a negative number.
  2. Change the “-“ to appear before the number.

Add paging

There are times when separating a report into specific pages is helpful. To do this,

  1. Go to the View ribbon.
  2. In the “Workbook Views” section, click Page Break Preview
  3. Right click the row that you want at the top of a new page, and select Insert Page Break.
  4. Repeat as needed.

Confirm the report looks wonderful

It is possible to review your report without sending it to the printer and looking at the paper. Instead, you can view the report within MS Excel as it will look when printed. Use the following steps for this purpose.

  1. Select Office Button > Print > Print Preview.
  2. Click Next and Previous or use the scroll bar to move through the pages.
  3. Click Close Print Preview when done viewing.

Print the report and impress your friends

Now that you have imported your text file, reformatted the output, and reviewed your report within Excel, you are ready to print your report. Remember to save one last time before you leave Excel.

Additional Formatting Options

  • Split one sheet into multiple sheets
  • Create a summary sheet
  • Change the totals to be the summation of the values
  • Change the alignment of the column headings / values

Additional Resources

Review the Help within Excel

  1. Press F1, or click the question mark in the upper right corner of the screen.
  2. Enter a keyword and click Search
  3. Review the displayed information

Consult Lynda.com

Lynda.com is a provider of web-based software training videos and resources. If you are interested in access to Lynda.com please contact the Technology Help Desk at [email protected] or 563-387-1000.

Review the Technology Help Desk web site

Contact the Technology Help Desk