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).
In the final step, rather than returning data to Excel, choose to edit the query in Microsoft Query (see Figure 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.)