Three ways to save time when comparing Excel files to find differences
If you regularly invest your time and energy to compare Microsoft Excel files to be sure that important data did not get changed and will not break your calculations, then this video is for you. If other people can edit your workbook, you may wonder what they changed. You will see three methods with rising level of automation for recognizing differences between two Excel files.
00:00 Introduction
00:50 View Excel files side-by-side for comparison (method 1)
01:43 Comparing Excel files using Power Query (method 2)
02:07 Loading data into Power Query
03:48 Adding an index column as row number
04:04 Grouping the data to identify duplicates
05:47 A time-saving trick in Power Query
06:52 Merging queries to identify mismatched records
09:45 Appending the queries into one table
10:19 Changing date type to text to preserve formatting differences
11:06 Identifying columns with switched position
11:45 Make column headers the first row
12:09 Transposing the header row
13:22 Merging queries to identify changes in column position
13:58 Keeping headers of only switched columns
15:13 Adapting the data source settings if you want to use my files
15:27 Using Microsoft Spreadsheet Compare tool to quickly identify differences (method 3)
File with the queries:
https://practicalspreadsheetsolutio-m...
Old file to compare:
https://practicalspreadsheetsolutio-m...
New file to compare:
https://practicalspreadsheetsolutio-m...
#excel #exceltips #powerquery
Screenshots used with the permission from Microsoft.