Comparative Analysis in Excel

Analysing data can mean comparing the same data from two different sources. In this lesson we look at the functionalities that allow the user to view ON THE SAME SCREEN two or more documents  to compare the data in  them. You can use the calculator to validate some totals from the data sheet and the report sheet on the same screen.

Comparing two different workbooks

In Excel you can open more than one workbook. To see them side by side go to  the menu bar "Window/Arrange" you now have the choice of viewing all the open workbooks using  different arrangements. The most commonly used are horizontal and vertical. You can just look at the data or  copy/paste from one to the other. When you want to go back to a single workbook on the screen click on the " Maximize" icon of the workbook that you want to work in.

Comparing two sheets from the same workbook

You might also want to compare two sheets from the same workbook. To do so you must open other  instances of the same workbook. To do so go to  the menu bar "Window/New Window" . Once you have done so click again on "Window" and you will see (at the bottom of the list of items) that two instances of the same workbook are open.

You can see that two copies of " my workbook.xls" are open and that instance # 2 is on screen (the check mark). At this point anything you  change in one instance is AUTOMATICALLY changed in all the open instances. You can close the instances like you close any other workbook and it is only when you try to close the last instance that Excel will ask you if you want to save the changes.

To look at a 2 different sheet from your workbook you open two instances, you arrange them as explained in the section above and you select a different sheet in each sub-window.

Use the calculator (lesson 1) to validate your reports. For example, you look at the database sheet in one sub-window and at a report sheet in the other. You use the calculator on the database to validate the results of  the report.

Comparing two different files (Excel, Internet  and Word)

Sometimes you want to compare data from a Word document or from some website on the Internet with what you have in Excel. You might just want to look at both documents on the same screen or copy/paste from one to the other.

To do so open Excel and the other document (with Word or you browser). Go to the "Start" bar  at the bottom of the screen. Right click on the empty space (where we  have added asterisks):

and a special menu comes up:

Select "Tile Windows Vertically" and you see both documents at the same time.

Learn more on Excel
See the list of small downloads

There are  five sections in this website

Section 1: About Databases: 4 lessons
In this section we review the basic notions on databases and evaluate Excel as a database. You will then see how Excel can be used downstream from most centralized databases and data management programs. Finally we will discuss the "upgrade" from Excel to Access..
Section 2: Excel and External Data: 7 Lessons
There are many ways to get data into your Excel Workbooks and there are many ways to easily extract data from all the databases in your organization and the Internet. This section is centered on querying data
Section 3: Analyzing Data with Excel: 8 lessons
Once you have the data in the right formats within your Excel workbook it is time to use the powerful analytical tools of Excel including the database functionalities (sort, filter, subtotals), the integrated calculator, the comparative analysis tools and the mysterious and not so complicated pivot tables. 
Section 4: Reporting with Excel: 7 lessons
The report is a communication tool and you can not accept to compromise on its presentation. Excel is the best reporting tool allowing you to easily develop popular dashboards and to share these reports with multiple colleagues.
Section 5: Other Functionalities in Excel: 3 lessons
In this fifth section you will review the useful CTRL and ALT keystrokes, the conditional formatting, the drop-down lists and other interesting functionalities.