Department of Technology

TECH 394 - Tech Computer Applications

E-mail Me

Syllabus

Schedule

Handouts

Assignments

All My Webs

Dept Home

• Text importing
• Sorting - arranging the data to group related items
• Filtering - having Excel show only a selected set of data
• Database functions - applying functions to filtered subsets of data
• Subtotals - grouping data and displaying or hiding the detail for that group
• Pivot tables

Each of these items describes a tool or set of tools built into Excel that allows you to control and fromat data to meet your needs.

Text Importing

You may find yourself trying to import large chunks of information from external databases.  Many firms maintain large operational databases that format the data in ways that Excel or Access can't handle.  Using products like SAP or Great Plains Dynamics, the IS department may generate expense, sales, or inventory reports for your department. You'll be expected to use that data to manage your department's operations.

There are ways to link Access and Excel directly to these databases, but most IS departments hesitate to do this - it offers too much chance for the original data to be corrupted.  The best you'll get in some firms is a printed report.  Then someone's job is to type the data from the report into Excel.  This is time consuming and offers the opportunity for new errors.  The better choice would be to import that data into Excel.

You can usually ask for any printed material to be delivered as a "text-only" file instead.  That means that the database program generates a file containing only standard text characters, which your spreadsheet can easily read.  Since the data comes from a database, its typically organized into fields and records, and the output needs to be structured so that Excel can put one field from one record in a given cell.  This is done in one of two ways:

• Fixed-width files display all the data for one record in a given line. Each field for that record takes up a designated number of characters.  For example, if the first two fields held a customer's first and last names, and both fields were formatted to be 15 characters wide, any text between character 16 and character 30 would automatically be dumped into the second cell in that row, regardless of what it contained.
• Delimited files use common characters to tell the importing program where each field ends.  A common format is the Comma Delimited format - the data for each field is separated by commas, with all the fields for one record in one line.  Because text may include commas, any text in a field is usually enclosed in quotes.  Typical data might look like:
        "Joe","Doe","126 N 3rd Street, Apt 3",125.74,"Yes","apples"
  Note that the comma embedded in the address will be ignored because it falls between the quotes.  Also note that the number - 125.74 - is not enclosed in quotes.  Numbers don't contain commas, and the importing program often needs to know which strings to treat as text, and which are number to be used for calculations. When you use delimited files, you need to know which character serves as the delimiter (the comma) and which serves as define text (the quote marks). 

Excel will import those files using a wizard to help you define the fields, and eliminate any unneeded information.  If the text file always uses the same format, you can record a macro as you import the file, and use that macro for all other imports form the same source.

A sample file called SampleImportData.txt should be saved to your "T" drive folder. Start Excel and use the File->Open menu to begin importing the file.

<Back to Top of Page>

Sorting

Excel users often want to rearrange data to display it in an ordered list - grades from high to low, production data grouped by work area, supply deliveries by date, etc. The Sort tool provides the ability to do this with several levels of sub-sort.

The Sort tool expects you to define the range to be sorted before you invoke it. You can do this by selecting the range containing the data to be sorted. If the first row in the range contains text, the Sort tool will assume that row to be labels, and will not include it in the sort, but will display those labels as the name of the columns to be sorted. Select a range, and then select Data->Sort to bring up this form:

Note that the option button selected says "My list has a header row". Again, Excel assumes the first row to be a header, and will not sort that data with the rest. If the first row is also data to be sorted, select the "No header row" option.

Note the order in which items will be sorted. In the cse shown, the header row contains lables for "Region" and "Quarter Ending", among others. As shown, the data will be sorted by region. Any rows that contain the same entry under "Region", will then be sorted by "Quarter Ending". Both indicate that the data will be sorted in ascending order - 1 before 2, A before B and so on.

Be certain the range you select includes all columns of data. It is easy to scramble your data by sorting only the first 4 out of six columns of data. The remaining columns are unchanged, but the itemin any row appears with data from another entry!

<Top of Page>

Filtering

Excel provides two tools to allow you to display only a selected subset of data. The Autofilter displays a combo-box dropdown window for each column's header. When selected, that window allows you to choose which records to display. The Autofilter tool is invoked by pressing Data->Filter->Autofilter. A sample screen is shown:

The "Region" drop-down has been selected. The user can choose to see records only for one of the 4 regions named, see the first 10 records only, or create a custom filter to show the records of his/her choice.

The autofilter selection is a toggle. To turn the filters off and display all records again, simply select Data->Filter->Autofilter a second time.

Another option in the Data->Filter selection allows you to create your own filter based on specified criteria. This tool requires you to create a second range, with the same header row as the data to be filtered. In this second range, you enter criteria that Excel uses to select data for display. A sample form is as shown:

Note that the data to be filetered is in the first 17 rows of columns A-G. The selection criteria is also in columns A-G, but appears below it. Entries in the criteria range are evaluated as follows:

• Items in seperate columns in the same row are connnected with the word AND. If the word "Portland" appeared in the "City" column, and in the same row the word "ME" appeared in the "State" column, the filter would show only those rows with "City" = "Portland" AND "State" = "ME". Entries for Portland, Oregon, or Bangor, Maine would not show.
• Items in seperate rows are connected with he word "OR". IF the entry in the state column appears in the row below the entry in the "City" column, the same filter would show rows containing "City" = "Portland" OR "State" = "ME", so recoreds for Portland, Maine; Portland, Oregon; and Bangor, Maine would all appear, but a record for Portsmouth, Maryland would not.

The reults of an advanced filter are shown below:

Note that rows 6-9 and 14-17 are not displayed. Advanced filters are active filters - if you change the entries in the criteria range, the filter changes to display the new selection.

<Top of Page>

Database Functions

Having created the data and criteria ranges for an advanced filter, it is often desirable to use a function to determine the total or average of values in the filtered range. The screen below shows the previous data filtered to display only records of the quarter ending in June:

Note that the totals in Row 18 still reflect the sum of all records, not just the 4 filtered rows. The function in cell D23 is a database function - DSUM. It requires 3 arguments

• the database range ( I named the range from A1:G17 "Data")
• the column within that range to be totaled, averaged, etc.(I wanted the total for Hardware, so I entered "A20", the label cell for that column)
• the criteria range (here - A20:G21)

The function interprets the database and criteria range exactly as the advanced filter, then performs its function for the column specified. For this example, the four filtered records total $117,781.

<Top of Page>

Subtotals

Many applications would work better if you could calculate subtotals for groups of records, display the subtotals, and then show or hide the data for a subgroup on command. This is now built in to Excel. The image below shows the previous data grouped to total data by region:

Note that the detail for Des Moine, Fargo, and Omaha are hidden - clicking on the plus sign on the left will display them. Clicking on the minus sign to the left of "Sioux Falls Total" will hide that data. These totals are created by selecting Data->Subtotals. The form that pops up is shown below:

The range to be subtotaled needs to be selected, including the labels in the top row. Those labels appear in the first box - here the subtotals are created for each region. The second box allows the user to add, average, count, or perform other calculations at each level. Checking the "Summary below data" box adds the overall total at the bottom.

<Top of Page>

Pivot Tables

Finally, users often want to show related data in a combining table - "How did sales compare by store for a given quarter?" Pivot tables allow you to display that data without creating new tables that rearrange the data to suit. The table below shows the sales for hardware in that way:

Note that it appears on its own sheet. This table was created by selecting Data->Pivot Table. A wizard appears that in most cases can be closed by pressing "Finish". The framework that appears shows where to place the data, while a small toolbar lists all the available columns. You decide which items are to appear in the rows and columns, and which are to appear at the intersections. You can also choose to have several pages available with the same data for each - one page for each region with sales by quarter in the table.

<Top of Page>