Technology

Import External Data in 10 Clicks With Excel

The vexing problem one reader had of pulling month-to-date data directly into Excel (and bypassing the sales Access database) is solved.
Bill JelenFebruary 16, 2011

Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.

Brad K. wins an autographed copy of Guerilla Data Analysis Using Microsoft Excel from CFO and MrExcel.com for his question: “Currently, we import MTD (month-to-date) sales data into Access, then copy and paste into Excel 2003.  I’d like to skip Access and import directly into Excel. I tried using Data > Import External Data > New Database Query >,  and it sort of works. After selecting the database and entering my password, I select Transaction_Date, Store_No, and Net_Sales. Then I choose dates greater than or equal to 2/1/2011. Then the spreadsheet has a row of sales data for each store-day.  Is there a way to group all the dates together so that the import shows only sales data for each store?  I can do this grouping in Access but I don’t know how to do this in Excel.”

When you are using the Import External Data feature in Excel 2003, you are making use of a decade-old program called Microsoft Query. The various dialog boxes are designed to make this arcane tool easier to use. Unfortunately, those dialogs leave out some of the powerful features of Microsoft Query. However, in about 10 mouse clicks, you can finish editing the query before the data is returned to Excel.

CFO Insights on Inflation, Workforce Challenges, and Future Plans 

CFO Insights on Inflation, Workforce Challenges, and Future Plans 

Download our 2022 survey report for a high-level view of finance team projections and strategies, directly from our CFO.com executive readers.

Brad should build the query as he is currently doing, shown in Figures 1 through 4 (see below).

Fig. 1

MrExcel 2-16-2011 Fig01

 

Fig. 2

MrExcel 2-16-2011 Fig02

 

Fig. 3.

MrExcel 2-16-2011 Fig03

 

Fig. 4

MrExcel 2-16-2011 Fig04

 

In the final step, rather than returning data to Excel, choose to edit the query in Microsoft Query (see Figure 5).

Fig. 5

MrExcel 2-16-2011 Fig05

 

Now, you see a preview of the data in Microsoft Query. You don’t really need the transaction date in the returned results. Click the heading for Transaction Date in the preview window. Press the Delete key on the keyboard, and that column is removed from the results (although it is still used for the criteria window above). (See Figure 6.)

Fig. 6

MrExcel 2-16-2011 Fig06

 

From the menu, select View, Query Properties (see Figure 7).

Fig. 7

MrExcel 2-16-2011 Fig07

 

In the Query Properties dialog, check the box for Group Records (see Figure 8).

Fig. 8

MrExcel 2-16-2011 Fig08

 

Click on the Net Sales column in the preview window at the bottom of the screen. Then click the Sigma button in the toolbar (see arrows in Figure 9, below).

Fig. 9

MrExcel 2-16-2011 Fig09

 

Your query is now returning one row per store. The fourth icon is an arrow pointing through a door (Figure 10). This is the “Return Data” icon. Click this icon to close Query and return to Excel.

Fig. 10

MrExcel 2-16-2011 Fig10

 

In Figure 11, click OK.

Fig. 11

MrExcel 2-16-2011 Fig11

 

The summarized data will now be returned to Excel, one row per store (see Figure 12).

Until you reach a new month, you should be able to use the Refresh Data command on the Excel 2003 Data menu to rerun the query. When you get to a new month, you should edit the query to specify a new start date.

                                                                                Fig. 12

MrExcel 2-16-2011 Fig12

 

It is a bit of a mystery why Microsoft let so much time go by without updating Microsoft Query. The Browse dialog box (which I did not show here) is reminiscent of Windows 95-era dialogs.

Note that there are other solutions to this problem:
• You could pull all of the data into Excel and use subtotals;
• In Figure 11, you could choose to create a pivot table;
• You could ask the people who gave you the database password if they could create a view that summarizes the data by store;
• In Excel 2010, you could use the PowerPivot tool to build a query to get MTD data. This query would never have to be edited and could simply be refreshed each day.

Bill Jelen, founder of MrExcel.com, is the author of 32 books about Excel. You can win a copy of one of his books if your question is selected for a column. Post your question to the community content block on the right. Bill’s next Webcast for CFO is Tuesday, February 22, and will cover What-If Scenarios and Monte Carlo Analysis.

Understanding Which ERP Modules Your Business Needs – And When