If you’re a copyeditor like me, you probably rely on the ability to track your changes, not only so others can see precisely what you’ve changed, but so you can keep track of where you’ve been.
If you edit in Microsoft Word, it’s all about Track Changes. Set the display options to your liking, and additions and deletions will be handled on the fly marked up as you go in a way that can’t be missed. (I prefer strikethrough and underscore for deletions and insertions, respectively, with comments and queries in the margins.)
Word will also step in when you’ve forgotten to track your changes, or when you need to pinpoint the differences between two versions of the same document. Every editor knows what it’s like to get (surprise!) a new version of chapter 8 after you’ve already finished editing it. Word’s Compare feature can save you hours of work.
But what about Excel?
Some of us use spreadsheets to track content across a bunch of related projects, or to record style preferences. They’re favored by everyone from publishers to programmers to keep track of complex data sets. Copyeditors may be asked to spruce these up from time to time.
What I don’t like is that there’s no useful equivalent of Track Changes for Excel, Microsoft’s spreadsheet app. There is a Track Changes feature if you look for it, but it’s now deprecated (as in no longer actively supported by Microsoft). And all it does anyway is generate a bunch of practically invisible comments.*
Besides, Excel is for data entry. It’s not a word processor. So don’t expect things like spelling on the fly or in-line deletion and insertion tracking. In Excel, those things don’t compute.
Enter Inquire, a Microsoft add-in that includes a Compare Files feature.
First, an important caveat. The feature I’m about to describe currently requires either Microsoft 365 “enterprise” edition or Office Professional Plus. If you don’t have these, you may want to read on anyway. You never know what the future may bring (or what Microsoft may decide to offer).
If your version of Excel does support it, here’s how to add the Inquire tab to your ribbon:
- Go to File > Options > Add-ins.
- Next to Manage, select COM Add-ins and click Go.
- Check the box next to Inquire and click OK.
Using Inquire to Compare
The Inquire tab includes a number of potentially useful features, but as an editor I care mainly about the one in the middle, Compare Files:
To use this feature, do this:
- Open the two Excel files you want to compare.
- Click Compare Files.
- In the Select Files To Compare dialog box, make sure the old file (or workbook, in Excel lingo) is listed first; if not, swap them.
- Click Compare.
This will give you a fancy report with lots of ways to click around and analyze the differences between the two files. But if all you need to know is what’s been changed, Export Results is your best option. This will give you an Excel file listing all the changes, cell by cell, with before and after columns.
For the sake of illustration, let’s say someone has used Excel to edit the table of abbreviations for chronological eras in CMOS 10.38—and I want to know where and what the changes are. Here’s a snippet of the results:
The cell where each change has occurred is listed in the Range column (A6 is column A, row 6; B7 is column B, row 7; etc.); the rest is self-explanatory.
A simple example like the one above doesn’t really need such a targeted analysis. Inquire becomes truly useful for larger spreadsheets with hundreds or thousands of cells. Having the power to find a random change in just a few clicks—maybe a closing parenthesis in row 3,422, column J, that was deleted by mistake—can literally save you.
But the differences reported by Inquire aren’t highlighted in any way, so they aren’t always easy to spot. (Did you notice the extra space before “the present” in the old value for B8 in the example above?)
This is where Word comes in. Simply copy the Old Value and New Value columns from Excel into separate Word files. Then run Word’s Compare tool (located under the Review tab) on those two files, et voilà: each and every change, down to the last space and mark of punctuation, is now tracked in a way that’s familiar to editors everywhere.
* * *
Again, Inquire isn’t available to everyone. And there are other tools out there that will do something like it. Programmers, for example, use command-line data-comparison (or “diff”) tools on comma-separated values (raw spreadsheet data) all the time. But if you’re like me, you may need a more editor-friendly solution, at least once in a while. And when you do? Let’s just say that inquiring editors always want to know what’s changed.
* If you want to give it a try in spite of its deprecated status, go to File > Options > Customize Ribbon. Then set “Choose commands from” to All Commands. Then scroll down to Track Changes and add it to your Review tab.
Photo of lightbulb by Mikael Kristenson on Unsplash.
Editor’s Corner posts at Shop Talk reflect the opinions of its authors and not necessarily those of The Chicago Manual of Style or the University of Chicago Press.
~ ~ ~
Russell Harper (@cpyeditor) is editor of The Chicago Manual of Style Online Q&A and was the principal reviser of the last two editions of The Chicago Manual of Style. He also contributed to the revisions of the last two editions of Kate L. Turabian’s A Manual for Writers of Research Papers, Theses, and Dissertations.
The Chicago Manual of Style, 17th edition
Order the book
Please see our commenting policy.
4 thoughts on “Is There a “Compare” for Excel? Inquiring Editors Want to Know”
This is interesting, but I can’t make it work, even though I have Office 365. “Inquire” is not listed when I click on “COM Add-Ins.”
I thought Office 365 was the professional suite. Maybe I was wrong.
According to Microsoft: “Important: This feature isn’t available in Office on a Windows RT PC. Inquire is only available in the Office Professional Plus and Microsoft 365 Apps for enterprise editions.” See “Turn on the Inquire add-in.” That page also includes a link to a page that can help you determine which version of Office you’re using. Hope that helps!
My copy of Excel (Office 365) has Track Changes under “Tools.”
Interesting. I think my Tools menu item disappeared with the introduction of the “ribbon” (ca. 2007). But I’ve read that it can be restored (via a third-party app).
Comments are closed.