Excel 2007 vs. Excel 2003: Filtering and Pivot Tables

By David H. Ringstrom, CPA

I’m sure you know that Excel 2007 features a completely different user interface — the familiar, yet aged, menu structure has been abandoned. Although the new ribbon seemed jarring at first, I now prefer it to the traditional menu. A majority of Excel’s functionality is now only one or two clicks away, and previously overlooked features like pivot tables and filtering are just a click away. Further, I disagree with the pundits who claim users will need weeks to acclimate to Excel 2007. Try a small project or two in Excel 2007, and I think you’ll find the transition easier than you’d expect. To help you get started, I’ll walk you through converting an Office Accounting 2007 report into an Excel 2007 pivot table. I’ll also show you the steps in Excel 2003, so that you can compare the differences.

 

Try it now:  A free 60-day trial of Office 2007 is available for immediate download. You can instruct the installation program to leave your present version of Office intact so that you’re free to work in either version.

 

Generate the Office Accounting Report

Once you have Office 2007 installed on your computer, the next step is to send the Sales by Item Detail Report in Office Accounting to Excel. Choose Reports, Sales, and then Sales By Customer Detail. When the report appears on screen, as shown in Figure 1, you can choose from one of three ways to send it to Excel:

·         Click the Excel icon on the tool bar (it’s the fifth button from the left)

·         Choose Actions, and then Export to Excel

·         Press Alt-X

 

Figure 1: We’ll transform this report into a pivot table.

 

Timesaver: These three techniques work for every report in Office Accounting, so feel free to use the method that’s easiest for you.

 

Transform the Report into an Excel 2007 Pivot Table

If necessary, save and transfer the report spreadsheet to a computer with Office 2007. Click the Office logo button in the top left corner to access the menu from which you’ll open the spreadsheet. Follow these steps once you open the Sales by Item Detail report in Excel 2007:

 

1.      Use the worksheet frame to select rows 1 through 7.

2.      Click Delete in the Cells section of the ribbon to remove the report headings.

3.      Select column A, and then click Delete to remove the extra blank column.

4.      Notice how the customer names only appear at the start of each set of transactions. We need the names on every row, so select cells A2 through the last customer row. For my report from the Northwinds Traders sample company I selected cells A2 through A69.

5.      Click Find & Select in the Editing section of the ribbon, choose Go To Special, and then double-click Blanks.

Expert tip: In some cases you can double-click an option in a dialog box to make a selection and close the dialog box at the same time. This technique works in all versions of Excel.

6.      Enter this series of keystrokes in the formula bar:

·         equal sign

·         up arrow

·         Ctrl-Enter.

The respective customer names should now fill each cell in column A.

 

7.      Convert the formulas to values:

·         Select column A

·         Click Copy

·         Click the arrow on Paste icon

·         Choose Paste Values.

 

8.      Widen column A by double-clicking on the worksheet frame between columns A and B.

 

9.      Pivot tables require a heading for each column of data, so enter the word Customer in cell A1.

 

10.  Delete column B.

 

11.  Select all columns on the report by clicking the upper left corner of the worksheet frame.

 

12.  Unhide all columns by choosing Format, Hide & Unhide, and then Unhide Columns.

 

13.  Pivot tables work best when there are no blank rows in the source data, so we’ll delete the extraneous rows from the report, such as rows 9 and 10 in Figure 2. To do so, click Sort & Filter, and then choose Filter.

 

Figure 2: Delete customer headings and totals before you create the pivot table.

Transition: The Data menu feature known as AutoFilter in previous versions of Excel is now known simply as Filter.

 

14.  Click the arrow in column J, and then clear the checkbox for Select All. Scroll down and select Blanks from the bottom of the list, as shown in Figure 2, and then click OK.

 

15.  Select and delete any rows with blue numbers on the worksheet frame.

 

16.  Click Sort & Filter, and then Clear to remove the Filter arrows.

 

Then and now: Earlier versions required you to select Data, Filter, and then AutoFilter to clear the AutoFilter arrows. Excel 2007 eliminates clicks and makes the process more intuitive.

 

17.  Choose Insert from the menu above the ribbon.

 

18.  Click the PivotTable icon, and then click OK. A blank pivot table should appear on a new worksheet.

 

19.  Choose these fields from the Field list in this order:

 

·         Item Name

·         Customer

·         Amount.

 

20.  As shown in Figure 3, your pivot table is now complete and tallies your sales by item, by customer!

 

Figure 3: The Sales By Customer Report as converted to a pivot table.

 

Transform the Report into an Excel 2003 Pivot Table

By way of comparison, open your original Sales by Item Detail Report in Excel 2003 and see how the steps differ:

 

1.      Use the worksheet frame to select rows 1 through 7.

 

2.      Choose Edit, and then Delete Row.

 

3.      Select column A, click Edit, and then Delete Column to remove the extra blank column.

 

4.      Select cells B2 through the last customer row in column B.

 

5.       Choose Edit, Go To, and then click the Special button. Double-click Blanks.

 

6.      Enter this series of keystrokes in the formula bar:

·         equal sign

·         up arrow

·         Ctrl-Enter

 

The respective customer names should now fill each cell in column B.

 

7.      To convert the formulas to values, select column B, and click Edit, and then Copy. Next choose Edit, Paste Special, and then double-click Values.

 

8.      Widen column A by double-clicking on the worksheet frame between columns A and B.

 

9.      Enter the word Customer in cell A1.

 

10.  Delete column B.

 

11.  Select all columns on the report by clicking the upper left corner of the worksheet frame.

 

12.  Unhide all columns by choosing Format, Column, and then Unhide.

 

13.  Choose Data, Filter, and then AutoFilter.

 

14.  Click the arrow in column J, and then choose Blanks.

 

15.  Select and delete any rows with blue numbers on the worksheet frame.

 

16.  Choose Data, Filter, and then AutoFilter to remove the AutoFilter arrows.

 

17.  Click on cell A1, and then choose Data, and then Pivot Table and Pivot Chart Report.

 

18.  Click Next to accept the default values of Microsoft Office Excel list or database and PivotTable.

 

19.  Excel should automatically determine the coordinates of your data range, in my case cells A1:E21, so click Next to continue.

 

20.  Click the Layout button; place these fields on the table:

 

a.       Item Group in the Row section

b.      Customer in the Row section

c.       Amount in the Data section.

 

21.  To complete the pivot table, click OK, and then Finish.

 

You’ve now seen how approachable advanced features like pivot tables and filtering are in Excel 2007. You probably also noticed that just about every step we carried out was available from the Home section of the ribbon, and most commands only required one or two clicks. Change can be good, even in a venerated spreadsheet!

 

 

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.