Excel Tip: External Data | Click Consulting

Ever need to use Excel to process or analyze data that comes from another database? You typically export the data and then import it into Excel right? There is a better, more efficient, more accurate method: External Data Ranges. Read full text for more detail.

External Data

External Data ranges are ranges within Excel that contain a live link to another data source. This External Data range can be refreshed with a single click of the mouse. Excel can pull data in from a wide variety of other sources including various databases, the internet and more. While we introduce External Data ranges in this chapter we will use a Microsoft Access database as our data source.

Setting up an External Data range essentially “pulls” the data into Excel. It is the best method to use when you will frequently be importing data from the same table into the same workbook. It is also the most flexible method since you can use criteria, and only import records if they match the criteria. Conceptually, it is similar to running a query and brining the results of the query into Excel. In addition to using criteria, you may perform math, sum data, count data, and sort the data. In fact, this procedure uses a special program from Microsoft called “Query” or “Microsoft Query”.

By default, Query may not be installed, but the first time you use it Excel will automatically install it for you. We will use a full exercise for setting up external data.

The scenario is that we are the accounting manager in a private company, and our company uses Microsoft Access to retain all of the sales detail. We would like to generate an Excel workbook that will pull sales by item so that we can generate a nice graph for the monthly financial meeting. We decide to use external data to bring the information into Excel from Access.

The first step is to open Excel, and select Data -> Import External Data -> New Database Query. The first step of the wizard appears, as shown in Figure 13.

Figure 13

It is important to note at this point that you may get external data from many other sources besides Access. In our example, we select MS Access Database as the data source type, and click OK.

At this point, we should browse to the Access database that contains the table we are interested in, as shown in Figure 14.

Figure 14

Click OK to proceed, as shown in Figure 15.

Figure 15

The dialog box shown in Figure 15 allows you to specify which table or query you would like to import data from. In addition, it allows you to specify which fields from the table/query you would like to import. In our case, we would like all fields from the “tbl sales data” table, so we will click the add all fields button to move all fields from the left side of the dialog box to the right side of the dialog box. (You can change your mind and move fields back and forth using the arrow buttons.)

Since the fields we want to include are shown on the right side of the dialog box, we will click Next to continue, as shown in Figure 16.

Figure 16

This step of the wizard allows you to filter for data. For example, we are only interested in pulling in sales made in January 2002. Therefore, we need to filter the sale_date field for records where the data is greater than or equal to 1/1/02 and less than or equal to 1/31/02, as shown in Figure 16. You can filter as many of the fields as necessary.

We will click Next to continue to the next step in the wizard, as shown in Figure 17.

Figure 17

This step of the wizard allows you to specify a sort order. In our case, we would like to see the records sorted by invoice number. So, we select the invoice_num field. You may specify an additional 2 fields to sort by. We click Next to move to the next step in the wizard, as shown in Figure 18.

Figure 18

At this point, it is important to note that you can either return the data to Excel, or modify the query using Microsoft Query. We will look at modifying queries using Microsoft Query in the next example. So, for this example, simply click Finish to proceed to the final step, as shown in Figure 19.

Figure 19

At this point, you can elect to place the data in an Existing worksheet or into a New worksheet. We will place the data into the Existing worksheet by clicking OK. The results of the external data are displayed in Figure 20.

Figure 20

Now that we have set up the external data range, we can refresh the excel spreadsheet at any time. When you click the refresh button , all of the data from the Access table that meets the criteria will be pulled into the Excel workbook. We can base charts on the external data range, and update them by simply clicking the refresh button. So, as people use the Access database, and update it with new records, we can simply refresh our external data range to pull the most current data.