Word: Compare and Combine

In the last Office Byte, we talked about the ability to compare two Excel documents via a special Add In. In the case of Word, we can use a feature already present on the Review tab, and in many ways it is even cooler. We cover this Compare feature in the Word Advanced training… if you would like to learn more about this, I would love to see you at a future session! Let’s take a look.

Compare dropdown, Compare circled

Exercises

If you would like to follow along, here are a couple of exercises. The first is a fictional draft of a document, and the second is a final version.

Much like with the Excel example, our goal is to ascertain the difference between the two documents.

Also, big thank you to Hannah in HR for letting us use her Final document as an exercise (the draft is a fictional version of her final).

Compare

In Word, the Compare feature lives on the Review tab. This will make sense when you see what happens after we load up both documents.

1. In a blank Word Document, go to the Review tab, Compare group, and click on the Compare dropdown.

Compare dropdown in the ribbon

2. Select Compare.

Compare dropdown, Compare circled

3. A popup will appear. Click on the folder to the right of the Original Document and browse to select the draft document. Then click on the folder to the right of Revised document, and select the final version.

Compare documents screen, folder icon circled

4. Notice you have the ability to label the changes. You could list the name of the author of the revised document here. Click OK.

Label changes with: James T. Kirk as author

One more note… there is a button that says More at the lower left of this screen. For now, I am going to skip that, but it is worth taking a look as you use this Compare feature more.

Navigating the Compare Screen

Hey look, a mission control screen! What are we looking at here?

Compare screen mission control

Revisions

On the left side you will see Revisions. you can click on any of these to be taken directly to the appropriate area of the document.

Revisions Screen

Original and Revised Documents

On the right side, you have two panes: the Original and the Revised document.

Original and revised screens

Combined/Compared Document

In the center are both documents together. As you scroll down in this document, you will see the left and right panes scroll with you.

Compared document

Already, this “mission control” view (not the official name) is making it much easier to compare the differences between these two documents, and see exactly what my coworker changed. But there are some other cool things about this feature.

Changes: Accepting or Rejecting

I mentioned there is a reason this feature lives on the Review tab. The center document effectively took our original, overlaid the revised version, and is retroactively treating it like comment and markup.

What does this mean? For one, you can accept or reject each of the revisions, just as if your coworker had used markup to change your document. What a cool tool! Especially if your office doesn’t regularly use Track Changes features, and you want to utilize their functionality…

Accept dropdown

Additional Notes

A couple more things I want to make sure to share:

New Document

When you chose to Compare these two documents, take a look at the top of your Word screen. This created a new document titled Compare Result.

Word document title: Compare Result

You can save this as its own document. It will not have the mission control experience when you reopen, however it will retain the changes as if it were a document with track changes enabled, and you will be able to come back later and decide to Accept or Reject changes.

Combine

So that was compare… what is this Combine option in the same dropdown? If you selected Combine instead of Compare and ran through the same exercise, you actually wouldn’t see a huge difference.

Compare dropdown, Combine circled.

So a common question is… what is the difference between the two features? I have heard it said that the difference is Compare is only for two documents, and Combine is for multiple documents… this confused me, since I don’t see a way to add more than two in the Combine screen. What I came to learn is that Combine allows for track changes to be turned on on the documents that are being compared… it is basically Compare for two documents with tracked changes already enabled… allowing for more collaborators.

Long story short (too late)… the features behave remarkably similarly, with the one exception that if Track Changes is turned on in either of the documents to be opened, Combine seems to be the method of choice.

Annnd… if you want to learn more about Track Changes, check out Word Essentials training! [/shameless plug]

Thoughts?

So what do you think, do you think you will use Compare with your Word files?

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

  • Tierney Mount

Excel: Spreadsheet Compare

Has this happened to you? You create an Excel spreadsheet that you share with your coworker. She edits the document, sends it back to you, and you are left scanning through a long list of entries to try to determine what changes she made…

In our Word Advanced sessions, we talk about the nifty Compare feature in Word. This feature is absent in Excel, but there is a similar option for Excel users via a Microsoft Add In called Spreadsheet Compare. This Add In comes with PC versions of Microsoft Office 2013, 2016, and 365… in other words, you probably already have it…

Compare Files button in ribbon

Before we jump in, thank you so much to Hannah and Jamie for inspiring this Byte!

Exercise Files

If you would like to follow along, below are a couple of Exercise Files. Save these to a place where you can easily find them for the next step (e.g. Desktop).

This is a fictional class list of students. We are going to assume that the FictionalClass download is the original and the FictionalClassEdited is what your coworker altered and sent back to you.

Spreadsheet Compare Add In

To access the Spreadsheet Compare Add In, click on the Windows icon in the lower left of your task bar, and search for Spreadsheet Compare.

Windows button searching for Spreadsheet Compare

You will be taken to a sort of mission control for comparing spreadsheets. You haven’t loaded anything in here yet, so there is not much to see.

Spreadsheet Compare program with no files loaded

Compare Files

1. In the upper left, click on the first button called Compare Files.

Compare files button in the ribbon

2. A pop up screen will appear. Notice it is asking you to select an “older” file and a “newer” file.

3. Click on the folder to the right of each of these fields, and browse to find the FictionalClass (older) and the FictionalClassEdited (newer) that you downloaded from the exercise files above.

Compare files pop up with folder icons circled

4. The two workbooks will load into the left (older) and right (newer) panes. Notice that changed cells are highlighted in green. Click on one of these cells, and it will select the corresponding cell in the other sheet.

two spreadsheets next to each other with changed cells highlighted.

5. Look at the bottom of the screen for an outline of the various changes that were made. This list notes all sorts of changes that were made, including the contents of cells and the formatting of cells. You can also click on any of these to be taken to the change in the worksheet on the panes above.

Changes lined out underneath

6. Try double clicking on one of the changes highlighted in the bottom pane. You can also access this feature by clicking on Show Details in the ribbon.

Details of the changes that were made to a cell.

It is a little disorienting. This popup screen is showing you what was in the original document, and the change that was made in the second sheet, in kind of a strange format. Simply click on the X in the upper right to go back to the main Spreadsheet Compare screen.

Export Results

It is possible to export all the results from this comparison. in the Export group is a button called Export Results.

Export Results button circled in the ribbon.

This will create an Excel file of all the differences between the two workbooks.

Differences exported in a new Excel document.

Drawbacks

While this is a nifty way to quickly see changes between two Excel files, it lacks some of the handiness of the Word Compare feature…

  • You can’t accept or reject the alterations/differences, you can only see them displayed and highlighted. Any desired changes will still have to be made by hand in the source document.
  • There is not a way to save a combined document containing both the original and changed document.

Other than that, a pretty cool tool!

Thoughts?

Do you think you will find a use for Spreadsheet Compare? Do tell!

100th Power User Certificate Awarded!

This is a most exciting week in Power User history! We have a three way tie for 100th WSU Microsoft Office Power User. Big congratulations to:

  • Tiffany Morgan
  • Matt Stiles
  • Lauren Wilson
Lauren Wilson, Tiffany Morgan, and Matt Stiles on a poster titled "Congratulations, Power Users!"

For more information about the WSU Microsoft Office Power User Program, visit wichita.edu/poweruser

Mail Merge With Tricky Number Formats

Have you ever attempted to mail merge with fields that contain formatted numbers, only to find that the formatting does not carry over onto your merged Word document? This is a common source of frustration with mail mergers, and something that we can quickly remedy on the Excel end. Let’s take a look… but first, big thank you to Jamie for having this excellent question at Wednesday’s Excel Essentials session and inspiring this Byte.

Insert Merge Field dropdown in Word

Exercise Files

If you would like to follow along, download today’s exercise files below to your desktop.

For this Byte, I am assuming you know the basics of working with Mail Merge in Word. If you are not comfortable with Mail Merge, please attend a Word Essentials training with me! I would love to show you how it works.

Excel File

Let’s take a look at the Excel file first. This is a list of employees… here are all out new professors who we are going to send letters to, welcoming them to Starfleet.

There are columns with Last Name, First Name, Salary, FTE, and Phone Number. The number fields are all formatted: Salary as currency, FTE as rounded to two decimals, and Phone Number as the Special Phone Number format.

Excel file with names, Salaries, FTE, Phone Number, and TEXT columns

Heads up: Notice that, highlighted in yellow, there are also some “helper columns” with TEXT titles. Let’s ignore those for the present, because in real life, perhaps you haven’t created those, and instead incorporated number formatting, as most of us do in our Excel Files

Word File

Opening up the Word File, you will find a letter welcoming professors to Starfleet Academy. Our plan is to enter merge fields into the bold area circled below.

Letter with merge field entry circled

Mail Merge with Formatted Number Columns

Let’s start our merge. We are going to build this using our number formatted columns. Again, I am assuming you have merged before for this exercise. If you are not comfortable with Mail Merge, please come attend one of my Word Essentials session. I would love to show you!

1. In the Mailings tab, go to Start Mail Merge, and select Letters.

Start Mail Merge dropdown

2. For Select Recipients, select Use an Existing List. Browse for the Excel file, wherever you chose to save it.

Select Recipients dropdown

3. Take a look at the Insert Merge Field dropdown, and make sure that you see all the columns you saw in your Excel file. Remember, we are focusing on the first five merge fields for now (not the TEXT ones).

Insert Merge Field dropdown

4. Insert each merge field in the indicated location. It should look like the example below.

Merge fields inserted into Word document

5. Let’s toggle the Preview Results button to see how this will look.

Preview Results button

This is usually the point where people realize that certain types of number formatting do not come through in Mail Merge. The salary, phone number, and FTE are just general number formatting. The phone number has no dashes, the salary has no commas, and the FTE is missing a “0.”

Merge fields previewed with unformatted numbers.

6. Let’s remove the Phone, Salary and FTE merge fields, and try this again. This time, insert the TEXT options that we saw in our “helper” columns in the Excel file.

Insert Merge Field dropdown with TEXT fields circled.

So Salary should be replaced with TEXTSal, FTE with TEXTfte and Phone with TEXTPh.

Merge fields inserted as described

7. When you Preview results, it will look much more like what we had in mind.

Preview with formatted numbers

What difference! Let’s see what makes these columns different in our Excel file.

Text Formulas

Open the Excel file again, and let’s do some investigating. We are going to check out our “helper” columns in columns F, G, and H that contain TEXT in the header.

1. Let’s see how the formula for TEXTSal is set up. Click into cell F2, and take a look in the formula bar. The formula used for Sal was:

=TEXT(C2,”##,##0″)

Excel, F2 selected, formula bar circled

2. Do the same for TEXTfte in G2. The formula used was:

=TEXT(D2,”#0.00″)

Excel, G2 Selected, formula bar circled

3. … and finally, TEXTPh in H2. The formula used was:

=TEXT(E2,”[<=9999999]###-####;(###)###-####”)

Excel, H2 selected, formula bar circled

So our investigating reveals that TEXT formulas have been used in these columns, which came over in a much better format for mail merge purposes.

If you would like to learn more about the TEXT function, take a look at this handy guide from Microsoft that will walk you through creating all of these used above, and more!

Thoughts?

Has this ever happened to you when you were mail merging with numbers? Do you think you will incorporate TEXT functions with your future merges?

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

Haley Underhill