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.

"Return Data" means we give the data back to Excel
"View SQL" displays the SQL commands of the query.
With "Add Tables" you can do just that. Note that the tables have to be related.

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

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".

advanced