Create a table as the working file for tracking updates. See diagram below.
Make a copy of the table in Sheet2. The table location must be the same as that in Sheet1. Sheet2 is the base worksheet to be used for comparison.
Conditional formatting normally works for data comparison within each worksheet only. But with the name range, it is able to compare the data between 2 worksheets. Therefore, the next step for tracking updates is to create a range name for the data in Sheet2 to be used later for conditional formatting.
1. Select the cell A2 in the Sheet2.
2. Goto to Menu -> Insert -> Name - Define
3. In the pop-up window, enter the name "old_data" (no space is allowed) into the box directly below the "Names in workbook" label.
4. Go to the box below the "refers to:" label.
5. Change the range to relative reference, i.e. without the dollar ($) sign. This will make the cell reference dynamic, changing the cell address according to position in conditional formatting cell later.
6. Click on the "Add" Button to add the range name "old_data" into the list.
7. Close the window.
Go to Sheet1 where your sales people will be revising their forecast numbers.
Select Cell A2 in Sheet1 and activate conditional formatting.
Go to Menu -> Format -> Conditional Formatting.
Change the option from "Cell Value is" to "Formula is"
In the text box on the right, enter the formula excluding the inverted commas "=A2<>old_data"
Click on the Format button to format the font, borders and the color of the cell.
Click OK again to close the conditional formatting window.
Copy the format in cell A2 to the rest of the cells in the table.
Save the file and you are done.
From now on, the cell(s) containing the forecast numbers will change to the pre-designated format when sales people updated the table with their revised forecast numbers.
If you want, you can also set the formula such that a different color appear when the revised forecast is below the orginal and another color when it is above. In Excel 2003 and below, you can enter up to 3 conditions to each cell.
Members can log in to our Microsoft® Excel Online Course site to watch the tracking updates video.
Click on this Excel Course for Business Professional website to find out the course date for the next run.
ALTernatively, tracking updates can be done using the track changes function in Excel. Here's how:
Select Tools -> Track Changes -> Highlight Changes.
Check the option "Track Changes while editing. This also share your workbook.
Check on the option "Who." This will detect who made the change.
When you click OK, Excel will prompt you to save the workbook.
Click OK to continue.
Once you have completed the above step and start making changes to the workbook, a triangle on the top left corner of the cell will appear. If you move your mouse over the cell, the details will pop up, the same way your comments will pop up. See diagram below.
Share this page:
Would you prefer to share this page with others by linking to it?
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft