BUS/CPT 357
Franklin College
Summer 2003
Erich Prisner,

advanced

Excel

see here for the basic Excel (CPT 105)

Getting External Data

Web Queries

Click "Data --- Get External Data --- New Web Query", specify the URL of the web page and table(s) of the web page will be transformed into Excel tables. If there are several tables, you can either address the table you want by its name (look into the source code) or by its number.

Try it with this table.

You should save your query. There is some external data tollbar, where you can edit. Clicking the "Refresh Data" button on that toolbar downloads the (meanwhile maybe changed) data again.

MS Query

MS Query is an add-in for Excel. It allows to work with all ODBC-databases (open database connectivity).

Click "Data --- Get External data --- New Database Query" and choose the data source (use a Visual FoxPro database). In the query wizard, choose the fileds you want to include. Next give a filter, and sort order. At the end of the wizard, you are asked whether to give the results to Excel or to Query, or whether to create an OLAP cube. For the moment, gove it to MS Query. 

Look at the buttons in MS Query.

You may have to install MS Query.

Using Excel or Query?

Excel Query
Filter Excel data Filter data that is too large for an Excel sheet
Filter data from an external data source
Queries from multiple tables of a database

 

Pivot Tables

Download this table and try it.

Sharing and Protecting Workbooks

Goal Seek and Solver

See this worksheet

More

Formulas

Functions

Naming Cells and Ranges

You may want to name cells to make your formulas more readable. A formula like "=Test1/4 + Test2/4 + Final/2" is obviously more readable for humans than "=C4/4 + D4/4 + E4/4".

Lookup and Reference Functions

see this Excel sheet.

Forms in Excel

Printing Tables



Up to main page Erich Prisner, June 2003