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.
Brad should build the query as he is currently doing, shown in Figures 1 through 4 (see below).
Fig. 1
Fig. 2
Fig. 3.
Fig. 4
In the final step, rather than returning data to Excel, choose to edit the query in Microsoft Query (see Figure 5).
Fig. 5
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
From the menu, select View, Query Properties (see Figure 7).
Fig. 7
In the Query Properties dialog, check the box for Group Records (see Figure 8).
Fig. 8
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
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
In Figure 11, click OK.
Fig. 11
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
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.