Need to identify changes in data from one week to the next

[creehatch]

Good Evening!

I produce a weekly report from a Seibel based database dump that shows sales results for a few hundred employees. Each sale is entered into the database with a unique Opportunity ID#. The Opportunity ID can include several different products. The database report breaks out the results by product, so the same Opportunity ID can appear on multiple rows if more than one type of product was sold on a given sale. I wrap these up in a nice pretty Pivot Table.

I need to be able to identify changes to a sale ($ amount, sales stage etc...) from one week to the next. The data is always formatted the same way. Is there a function or formula in Excel that will highlight the changes from the previous week by comparing each week's report?

I also posted a similar question in the Access forum in case this can't be done in Excel.

Thanks in advance for any help you can provide!

Crystal

 

[Joe Was]

How is one weeks data indicated to be different from new data and how is new and old data listed?

Check Excel Workbook Help, using: "About tracking changes"

 

[creehatch]

I download the data weekly

Basically I get a data dump from our forecasting tool, and it's up to me to determine what changes from one week to the next. For example, a Sales Director shows $1M in committed sales on week #1's forecast, but when we pull week #2's forecast the committed sales have dropped to $500k. I would like to find an automated way to identify what caused the change. Because the forecast consists of hundreds of individual opportunities, it's too much for me to a quick "stare and compare".

When I download the data it comes as one big table with the following fields - Opportunity ID (primary key), product code, Committed month, probability and sale value.

I will take a look at the help file, if I could find a way to automate this it would save me a ton of time! :wink:

Thanks!

 

[Joe Was]

Crystal, You may find that the "Pivot Table" utility in Excel will help you do this task.

A PivotTable report is an interactive table that quickly combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest.

A PivotTable report lets you analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. Because a PivotTable report is interactive, you can change the view of the data to see more details or calculate different summaries, such as counts or averages.

In a PivotTable report, each column or field in your source data becomes a PivotTable field that summarizes multiple rows of information. A data field, such as Sum of Sales, provides the values to be summarized.

To create a PivotTable report, you run the PivotTable and PivotChart Wizard. In the wizard, you select the source data you want from your worksheet list or external database. The wizard then provides you with a worksheet area for the report and a list of the available fields. As you drag the fields from the list window to the outlined areas, Microsoft Excel summarizes and calculates the report for you automatically.

If you're using an Office Data Connection to retrieve external data (external data: Data that is stored outside of Excel. Examples include databases created in Access, dBASE, SQL Server, or on a Web server.) for your report, you can return the data directly to a PivotTable report, without running the PivotTable and PivotChart Wizard. Office Data Connections are the recommended method of retrieving external data for your reports when you don't need to combine data from more than one table (table: A collection of data about a particular subject that is stored in records (rows) and fields (columns).) in the external database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) or filter the data to select specific records before creating the report, and for retrieving data from OLAP (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) databases.

After you create a PivotTable report, you can customize it to focus on the information you want: change the layout, change the format, or drill down to display more detailed data.