Comparing sets of Data in Microsoft Excel

Jul 1, 2021 | Excel Hints and Tips

Are you needing to compare different sets of data?  Do you end up copying the data several times to do this?  This is OK but what if that data then changes?  You now have multiple versions of the same data that you need to update.  But why not use scenarios instead? 

Scenarios allow you to do what if analysis but with all the data still stored in one place but with the ability to switch from one option to another efficiently. For example, if you have the data for this year but are looking to project forward for next but there are 3 possible scenarios you want to compare to determine which is the best way to go scenarios would be a perfect solution. 

Setting up scenarios

1. From the Data tab, select What if Analysis then Scenario Manager.  Initially there will be no scenarios created. 

What if analysis

2. Click Add then give the scenario a name, decide what cells are going to be changed and add a helpful comment.  The cells to be changed are normally the same for each scenario you create even if some are not altered on each occasion. 

Edit scenario

3. Click OK

4. A list will appear for every cell that you have said will be changed and you set each of these to a specific value.

Scenario values

5. If you do not want to create any more scenarios then click OK otherwise click Add to repeat steps 2 to 4 for your next set of data.

Using the scenarios created

1. From the data tab, select What if Analysis then Scenario manager. 

2. Select the scenario you want to view then OK 

Comparing scenarios 

The scenarios created can be compared side by side which is useful to see the impact of the changes created.

1. From the data tab, select What if Analysis then Scenario manager. 

2. Click on Summary 

3. Choose Scenario Summary 

 

Scenario summary

4. By default the value of all cells that were changed when setting up the scenario will be shown but in addition results cells can be added.  These could be overall totals for example.  They are normally calculated cells using some of the cells that have been changed 

5. A new Scenario Summary sheet will be added to the spreadsheet with a separate column for each scenario produced 

Merging scenarios 

If a scenario has already been created in another file or sheet within the current workbook then it can be reused 

1. From the data tab, select What if Analysis then Scenario Manager 

Merge scenarios

2. Choose Merge.  If the scenario to be reused is in another file then this file must be open 

3. Click on OK 

Scenarios is a hugely underused feature in Microsoft Excel but can really be beneficial in comparing possible situations.  

Further Reading

If you’ve enjoyed reading about how to compare sets of Data  on Excel, there’s some other blogs below that you might find useful:

Want to learn more about other Excel features? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Courses I run.

MEMBERSHIPS

FOLLOW LARA