In a collaborative environment tracking changes in an Excel worksheet like change in budgets or forecast sales or expenditure can be quite useful. You can track changes using named ranges and conditional formatting or you can use the ‘track changes’ features in the ‘review’ tab. The latter is quite easy to implement but the former gives you more information.
Let’s see how this is done.
Select the data. We have in our example data of quarterly sales of 4 teams.
Paste the data in another worksheet and ensure that it occupies the same range.
Now select the pasted data in Sheet2 and give it an appropriate range name. Here take care to remove the absolute cell references, i. e. delete the $ signs before the column and row references.
Now go back to your sheet1, select a data cell and apply conditional formatting. We have applied two different formattings: one where the cell b2 value is greater than the value in the corresponding value in sheet2 and another formatting where the value of cell b2 in sheet1 is less than the value of cell b2 in sheet2. The idea is to know the movements of the values quickly in both positive and negative directions so that the team members can take appropriate action.
Next we copy the applied conditional formatting to rest of the values in sheet1.
Now when somebody changes the values in the original sheet1 the changes can be tracked through color changes.
We have also shown how to use the ‘track changes’ feature in the Excel worksheet.
If you now change the values in the cells in sheet1 an appropriate message is displayed. Did you notice the triangle on the top left corner of the Excel worksheet cell where changes have been made?
Also since the conditional formatting along with the named range in sheet2 were also active you could experience the color change
The Excel training video describes the details vividly.
Business Uses of Excel