Excel 2008 can use its ODBC connectivity to retrieve data from any number of databases, so long as the database provider (or a third-party) provides an ODBC driver that works with the Macintosh's operating system. Fortunately, drivers for all of the most popular databases are available, and in many cases you have more than one option.

While drivers can come from a variety of vendors and third-party providers, after they are installed, you can create Data Source Name (DSN) entries to control access to a particular database in the ODBC Administrator utility, which can be found in your Applications/Utilities folder. Although a thorough explanation of this utility is outside the scope of this book, any driver that you purchase or download should have instructions for installing and configuring connections and likely makes use of this or a similar ODBC administration tool. Figure 17.1 shows the ODBC Administrator at work.

The two main types of DSNs that you can use with Excel are System and User. A User DSN can be used only by the user who sets it, which in most cases will be you. A System DSN, on the other hand, can be used by any user account that is set up on your computer. If you are the only user on your computer, the distinction may not seem important, but if others use your system and also need to be able to access the database, then you are better off creating a System DSN unless there is a specific reason to deny other users access. The following steps are typical in defining a database connection, in this case using the OpenLink driver to talk to a MySQL database:

Figure 17.1. You can use the ODBC Administrator to configure Data Source Names that point to your database.

Figure 17.3. Most DSN configurations involve entering information in a setup wizard.

Excel is capable of importing data from a variety of sources, including database and various file formats. In Chapter 13, you learned about the basics of importing some types of external data, including from text files, CSV files, and FileMaker Pro databases. Now you learn how to import directly from an ODBC connected database into Excel. After you have a database driver installed and a DSN configured, you're ready to begin.

To retrieve data from a relational database, you have to first be able to define the tables and query parameters to retrieve it. Relational databases consist of one or more tables, each of which may have data that relates to another table. For example, you could have a table containing Orders, with fields (called columns in database parlance) that contain an order date, product number, quantity, and other relevant information, including a customer ID. The customer ID would relate to another table called Customers, which could contain information such as addresses and phone numbers. The Structured Query Language (SQL) was designed to be able to work with relational data, and it has numerous options for building queries.

Explaining how to make queries using SQL syntax is beyond the scope of this book; in fact, entire books have been written just on this subject. Fortunately, you don't need to learn SQL in order to import data into Excel, because Microsoft thoughtfully included a tool called Microsoft Query, which lets you easily create queries and test them visually. (If you happen to know SQL, you are, of course, free to use it.)

Opening the connection

Learning how to work with Microsoft Query is the key to mastering Excel's data importing power. But first, you need to tell it which data source you want to use. Follow these steps to get started:

1. In Excel, choose Data

   
   Get External Data
   
   New Database Query.

   This launches the iODBC Data Source Chooser dialog box, which is a bit like a stripped-down version of the ODBC Administrator tool you used to create the data source. In fact, if you somehow skipped that step, you can do it from here, as well. (Add, Remove, Configure, and Test buttons are available here that you can use to manage your DSNs.) Figure 17.4 shows the Data Source Chooser dialog box.

2. Click the tab of the DSN type you created: User, System, or File.

3. Click the data source name.

4. Click OK.

   At this point, you likely will be asked for authentication information in the form of a username and password to access your database. Figure 17.5 shows an example, but yours may look different depending on the ODBC driver you are using. See the documentation that came with your ODBC driver if you have questions about what to enter here.

5. Enter your username and password, and click OK (or Connect, depending on your driver).

If all went well, you should now be in Microsoft Query with a floating palette of tables, similar to that shown in Figure 17.6. (Of course, the tables in your database will be different!)

Figure 17.4. The first step in creating a query is to choose your data source.

Figure 17.5. In most cases, you are prompted to provide authentication information.

At the top of the Microsoft Query menu, you see four buttons, which are the main controls for working with queries:

• Query View: Use this view when you want to construct a query visually, using boxes and lines to show tables and references.

• SQL View: Use this button to toggle the view to SQL view, where you can see the SQL statement that your query is using. You can edit the SQL statement directly if you prefer.

• Show Tables: This can be used to open the Tables Palette again if you happen to close it.

• Test!: Run the query as it currently exists. If successful, you see the data you import displayed in the lower portion of the screen.

Below the buttons, a portion of the screen is reserved for displaying either the selected tables or the SQL code depending on the view you select. Under that are controls for working with data that you retrieve (the Fields pane). The lower portion of the screen is reserved to show data retrieved using the Test! button (the Data pane).

Now, let's begin building a query.

Note

For this chapter, you will see data presented from the 'Sakila' sample database created for MySQL. This sample represents a video rental store customer and inventory database. More information about the Sakila database can be found at: dev.mysql.com/doc/sakila/en/sakila.html.

Figure 17.6. Use Microsoft Query to begin building a database query.

Refining queries

Although it's possible that you want to retrieve all the data in the tables that you've chosen, it is more likely that you'd like to refine the query and restrict it to only retrieving the values that you like. You can accomplish this in two ways. One is by specifying criteria for chosen fields to limit the values that can be retrieved. The other way is to create joins between tables.

Specifying criteria

In the Fields pane, you may have noticed the Criteria field. This field can be used to limit the records returned by a query by specifying criteria that a particular field must match. Every potential record retrieved is evaluated against all the criteria. If it doesn't match any of them, the record is excluded from the results.

Figure 17.8 shows a sampling of data from the Sakila database's film_list view, which is used as an example for the various criteria options.

Figure 17.8. Retrieved results can be limited by specifying criteria for key fields.

The following are some examples of criteria you can specify to restrict data from the film_list table:

• You can use the LIKE operator to restrict data to that which matches a partial string. For example, you can restrict the film_list query to only include movies that being with the letter A by using LIKE 'A%'. Criteria text must be enclosed in single quotes. The % character is a wildcard character meaning that the results can include titles that start with 'A' followed by any other text. Likewise, LIKE '%KIRSTEN PALTROW%' applied to the actors field would limit the query to movies with that actress in the cast.

• You can use mathematical operators such as equals (=), greater than (>), greater than or equal (>=), less than (<), less than or equal (<=), and does not equal (<>). These operators can be used on numeric, date, and text fields. > 120 applied to the length field would retrieve movies longer than two hours. Likewise, you can retrieve movies in the Horror category by applying = 'Horror' to that field.

• You can use the IN operator to match a field to values in a list. For example, you can limit results to PG-13 rated movies or higher using this criteria applied to the rating field: IN ('PG-13', 'R', 'NC-17').

• You can find records that are missing data. Use the IS NULL operator in the criteria box for a field to retrieve records with null values in the field.

• You can use the NOT operator in front of most of these criteria to negate the results. For example, NOT LIKE '%KIRSTEN PALTROW%' applied to the actors field returns all movies that don't include her in the cast.

Choosing a sort order

If you add individual fields rather than the * wildcard, you can specify a sort order for your fields. Sort orders are applied from left to right. Options include no sorting (in which case data is retrieved in the order that it exists in the database table), Ascending (from lowest to highest, numerically or alphabetically), or Descending.

Joining tables

If your query includes data from multiple tables, you can refine the query in another way: You can create joins between tables. Joins are key to the power of a relational database, because they are used to define the relationship between one table and another. You can create several types of joins, and the type you choose is important to ensure that your query returns the correct results. To create a join, do the following:

1. Click a field in a table in the Tables pane.

2. Drag the field name to another table.

   The Join dialog box appears, as shown in Figure 17.9.

3. If the fields you are joining have the same name, Excel automatically adds it to the Second Field box; if not, choose a field to use for the join comparison from the list.

4. Choose a join operator. In most cases, you can leave this as the default value of Equals (=), but you can use other operators to join based on unequal relationships between the fields as well.

   

   Figure 17.9. Use the Join dialog box to define a relationship between fields in different tables.

5. Choose the join type. The default choice, item 1, specifies an inner join where both tables must have matching fields. Options 2 and 3 are used to specify outer joins. Inner and outer joins are explained in more detail below.

6. Click OK. The Tables pane now shows a line between the joined fields indicating the relationship, as shown in Figure 17.10.

You can edit or delete a join by right-clicking (or Control+clicking) the join line in the query view. The two types of joins are described here:

• Inner join: The default type of join, an inner join selects records where the joined fields in both tables match. If one record doesn't have a matching record in the other table based on this field, neither record is included in the results. Figure 17.10 shows an inner join between the inventory.film_id field and the film_list.FID field. A result set using an inner join can include multiple entries for each record, because you can have many records in each table matching.

• Outer join: An outer join selects all the records from one table regardless and includes records from the second table when a match exists. When a record from the table that's contributing all its records can't be matched with a record from the other table, the record still appears in the result set. Empty cells are used in place of data when no matching record is found in the other table.

Figure 17.10. Joins are indicated in Query view by lines drawn between the fields.

When you have a query defined the way you want it, you can return it to Excel by clicking the Return Data button. Click this button to open the Return External Data to Microsoft Excel dialog box, shown in Figure 17.11. This dialog box tells Excel where to put the new data.

Choose one of the following options for returning data:

You can choose some options for the query that Excel uses for creating the layout of the result worksheet, as well as for saving the query for later use. Click the Properties button in the Return External Data to Microsoft Excel dialog box to open the External Data Range Properties dialog box, shown in Figure 17.12.

Figure 17.11. After you've chosen the data to include in your query, you can tell Excel where to put it.

Figure 17.12. You can configure how your query is saved, along with layout and data refresh options.

You can choose the following properties:

After you've defined the query and imported data into a worksheet, it's simple to keep it up to date; just refresh the data. The refresh command causes the query to be executed again without having to go back through Microsoft Query. Instead, it's all done behind the scenes. You have these two options for refreshing the data:

You may think it's silly to have to edit a saved query if you want to search for a particular name or other field value. Well, you're right, and Excel provides a way to not have to do this by allowing you to define parameters for queries. After you've defined parameters, you can edit your queries to prompt for the values you want to search for.

Remember when I said you didn't need to know SQL syntax to build queries? Well, parameters are an exception to this. Although Excel itself is fully versed in parameters, the Mac version of Microsoft Query lags behind its Windows cousin in this regard. You can define query parameters in Microsoft Query, but it's not versed in the syntax for parameters while using the Query View, so you are forced to enter parameters using the SQL View.

Defining parameters

The following steps illustrate how to add parameters to a database query:

1. Click in the data range that you want to modify.

2. 

3. Click the SQL View button to switch to that view.

4. Add to the query's WHERE clause if it has one (you can create one if it doesn't have one). Instead of a value to compare a field to, use a question mark (?). The following example shows a query from the customer table. We added a WHERE clause that uses the LIKE operator for string comparisons:

   SELECT customer.* FROM customer WHERE customer.last_name LIKE ?

   Figure 17.14 shows SQL View with the sample query.

5. Click Return Data to return to Excel. You can't switch back to Query View after making this change, because Microsoft Query doesn't recognize the parameter syntax and gives you an error. Likewise, you can't run a test because Microsoft Query doesn't know how to prompt you for parameters.

Figure 17.14. Parameter fields must be specified using SQL View.

Setting parameter options

After you have parameters defined for the query, Excel recognizes this and allows you to set parameter options such as a prompt, default value, or cell to use for the source. You should notice that the Query Parameter button on the toolbar and Parameters menu item is made active as well. Follow these steps:

1. Click in the data range to select it.

2. 
   

   Figure 17.15. Choose settings for defined parameters.

3. Choose one of the parameter options:

   • Prompt for value: You can enter text that will be used as a prompt so you don't have to remember which parameter is which.

   • Use the following value: You can predefine a value to pass to the query. Use this setting if you don't expect to change a value often, but still want the options.

   • Get the value from a cell: This option lets you use a cell reference as the input to a parameter. This can be useful if you want to use the results of a formula as the parameter value.

4. Click OK.

5. Refresh the data by pressing the Refresh Data button or by choosing Data

   
   Refresh Data.

6. You should now be prompted for a parameter. For our example, we entered J%, as shown in Figure 17.16.

Figure 17.16. When you refresh data, you have the opportunity to enter a parameter value to pass to the query.

If all goes correctly, your list is refreshed with the data that matches the query. Figure 17.17 shows the results of our sample query. Now, every time you run the query, it plugs in the value of the parameter—from the cell, from the value you entered, or from a prompt.

It's easy to build a PivotTable using the PivotTable Wizard. Perhaps the easiest way is to just get started. Suppose you want to create a table from a data range that you have imported into a worksheet. Follow these steps:

Figure 17.22. Choose formatting and data options for the table, or accept the defaults.

Figure 17.23. An empty PivotTable created by the Wizard is ready to receive data. The PivotTable toolbar has been opened to assist in adding data.

In essence, a PivotTable is a multi-dimensional report that can be used to analyze multi-column data in various ways. To do that, you need to assign fields from the source data to the row, column, and data portions of the table, and let Excel do all the work to calculate totals based on the data.

When displaying a PivotTable, you first need to know which field you are ultimately interested in. In the case of our sample data, we want to get a sum of the payment totals for each movie title, so we drag the amount field to the data section. Because we want to tabulate payments per title, we can drag the title field to the rows section. Finally, we can drag the payment_date field to the columns section. By default, Excel automatically calculates a grand total for each title by summing the values for each month. It also calculates a grand total for each payment month, shown at the bottom of each column. This is shown in Figure 17.24.

Figure 17.24. By dragging and dropping different fields to the rows and columns, you can create PivotTable reports that show many different details from the same data. Here, we are generating a grand total for rental payments on each title.

Now, suppose we want to look at it differently and see how much each customer may have spent. We can replace the field used for the PivotTable rows with the last_name field, which is the last name of the customer. Figure 17.25 shows the new PivotTable.

Figure 17.25. By changing the field used for the PivotTable rows, we can generate a completely different table using the same data values.

Just to make it clear, let's try one more example. We'll keep the title in the rows spot and add last_name2, which is the name of the store manager. As Figure 17.26 shows, we can now see at a glance how much was spent on each title for each manager.

As you can see, PivotTable reports offer lots of options when it comes to viewing data.

Figure 17.26. In this example, we can see a PivotTable report showing payments per manager per title, along with grand totals.

So far you've seen much of what PivotTables can do, but we've barely scratched the surface. If you have looked closely, you have seen another section of the PivotTable report labeled "Drop Page Fields Here." This can be used to add yet another dimension to the PivotTable Report. If you drag one of your fields to this portion, you can filter your report to show items matching only a single value of that field. Figure 17.27 shows title versus payment date, but only for one manager.

Figure 17.27. Using the Page field area, we can filter the data by a single value of that field. In this case, the table now shows sales for a single manager, Stephens.

Figure 17.28. Use the Field Settings dialog box to conFigure Dvanced settings for field display.

You can change these items:

Figure 17.29. The PivotTable Field Advanced Options dialog box can be used to set sorting and AutoShow options.

A scenario is simply the set of values that Excel can automatically plug into specified cells in your worksheets. This capability can be especially handy if you have a formula that calculates a result based on multiple input values. The following steps show how to use the Scenario Manager to create different scenarios:

Figure 17.34. Define a scenario by specifying the cells that can be changed.

Figure 17.35. Enter new values for the changing cells in the Scenario Values dialog box.

After you've created scenarios, you can test them using the Scenario Manager. Simply select the scenario you want to run, and click the Show button. The worksheet is updated with the saved values and the results recalculated. If you reach a scenario that you'd like to stick with, you can click the Close button to return to the worksheet. Alternatively, you can build a summary of all the scenarios, as explained later in this chapter.

Suppose you have workbooks with similar layouts (say, monthly or yearly reports), and you want to run the same scenarios in each of them. The Scenario Manager offers a merge capability that allows you to use scenarios created in one workbook in another. Note that this works only if the input cells are exactly the same between the two worksheets; otherwise, you must edit the imported scenarios. Follow these steps to merge scenarios from another workbook:

After you've created a few scenarios, it's nice to be able to see the effects of different data values all in one location. The Summary feature can be used to generate a new worksheet with either formatted data values or a PivotTable with the scenario values for further work. Follow these steps to create a scenario summary:

Figure 17.37. A Scenario Summary lets you see at a glance the result of all your scenarios.

Sometimes when you are working with data, you may see something that catches your attention that you want to revisit at some point. You can flag your data for follow-up and get a notification to take a look. This can be helpful if you are working with data that you expect to be refreshed regularly. For example, you can set a follow-up notification for a time that is some point after the data has been modified so you'll be reminded to run a report. Another case where this could be useful is when you notice something amiss that you don't have time to deal with right away. Simply flag for follow-up, and you won't have to worry about it. To use this feature, choose Tools

Figure 17.38. You can use the Flag for follow-up feature to notify you to follow up on this document.