Viewers: in countries Watching now:
In Excel 2007: Creating and Managing Invoices, Microsoft Most Valuable Professional Curt Frye shows how to set up an efficient and flexible invoice management system. As part of the instruction on how to design a custom invoice in Excel, Curt explains how to use a PivotTable to put each customer's information on its own worksheet. If Word is the preferred method for creating an invoice, Curt covers how to use Word 2007's Mail Merge function to work with an Excel sheet. He also outlines some advanced tips, including calculating interest on unpaid items and tracking invoices. Exercise files accompany the course.
In Excel 2007, you can create separate worksheets, which contain PivotTables for each of your customers. To do that, you need to use what's called a Report Filter. The Report Filter field is here in the PivotTable field list. If you want to add a field to the Report Filter area, you can drag it either from the list up here or from one of the existing area such as Column Labels or Row Labels. In this case, I want to use the Customer ID, so I will drag it from the Row Labels area to the Report Filter area. Now that it's there, it no longer affects the organization here in the body of the PivotTable.
But instead, if I wanted to, I could filter the values within the PivotTable. So if I only wanted to see the items for CustomerID #1824, I could filter and click OK. To remove the filter, just go up here click All and they come back. Now that I have a field, in this case the Customer ID field, in the Report Filter area, I can use it to create my separate PivotTables. To do that, on the Options contextual tab of the Ribbon click the Options button and then click Show Report Filter Pages. When I do, Excel displays a list of the fields that are available in the Report Filter area.
In this case, there is only one. So make sure it's selected and click OK and Excel creates my separate worksheets. In this case, I have a PivotTable that is already filtered to only display those items ordered by Customer ID #1824. It's here on this worksheet, also called 1824. The same thing occurred for CustomerID #1825, which is here with the separate PivotTable. So if I were to click this arrow, you'd see that I would be able to filter it, but it is preset to a filter for this customer. If there had already been a worksheet named 1825 or 1824, you would see a worksheet with the name 1824 followed by parenthesis and then the number 2.
So in other words, it would be 1824 and then 2 surrounded by parenthesis. Now that you've created separate worksheets for each of your customers, you can add those customers information. I'll show you how to do that in the next movie.
There are currently no FAQs about Excel 2007: Creating and Managing Invoices.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.