How to Track Changes in Excel

Here’s an old proposal writers’ joke¹:

Q: What’s the best way to write a proposal in Excel? A: Don’t!

This bit of homespun wisdom is unhelpful when faced with an RFP that states, in no uncertain terms, that “responses must be submitted in Excel format.” All of a sudden, Microsoft Word—the writer’s fundamental, if not beloved, tool of the trade—and its many advantages have gone by the wayside.

Yes, writers who need to compose in Excel find themselves in much-reduced circumstances, laboriously typing answers into individual cells or the thin formula bar, hoping they don’t accidentally press Ctrl+Z, Escape, or any of several keys that will delete their input without confirmation.

At McKinnon-Mulherin, we’ve developed innovative tools and techniques to make this process simpler, faster, and more accurate. But first, let’s discuss why Excel is a poor platform for proposal preparation, and why it is required by requestors nonetheless.

Proposals in Excel 

At the risk of diving into the metaphysical deep end, Excel is a poor tool for word processing because it is not a word processor. Rather, it is a spreadsheet program, intended for collecting and performing calculations on tabular data. It is not primarily (or secondarily, or tertiarily) intended for writing, and its functionality in this regard is severely limited by design. For instance, Excel lacks the following:

• Proofreading and copyediting. The text entry box in Excel provides only rudimentary support for basic spellchecking and typo correction.
• Writing tools. Paragraph breaks and styling are impossible, while basic word processing tasks, like inserting line breaks, are bound to unfamiliar, platform-dependent keys.
• Track changes. Support for tracked changes in Excel has actually gotten worse over time. In Excel 2013, tracking was part of the “sharing” functionality; however, Excel 2016 has replaced sharing with co-authoring support, which doesn’t provide the ability to track changes. In other words, it is no longer technically possible to track changes in Excel.

“Don’t use Excel to respond to RFPs” is an easy pronouncement to make in a blog post, but may prove somewhat more difficult to enforce with your client, much less the procurer. Although it’s inconvenient for us writers, the Excel format serves several purposes for purchasing and procurement teams:

• It makes it easy to compare answers from multiple respondents.
• It tends to keep answers shorter, aiding in speed of review.
• The lack of formatting limits the display issues that may crop up.
• Cell content is easier to copy into an online system, as is often necessary.

With these advantages, it’s unlikely that Excel proposals are going away no matter how much we grumble. So, how do you manage the task of composing, editing, proofreading, and securing approval for a complex proposal that must be delivered in Excel format?

Write in Word

The most elegant approach by far is to write, edit, and proofread the proposal responses in Word before moving final responses into Excel. The advantages of this approach mirror the disadvantages of writing in Excel:

• Track changes and document comparison functions are well-developed.
• Keyboard shortcuts and quality-of-life conveniences are all present and accounted for.
• Word’s spellchecker—while still not a substitute for human attention—is fully functional.

At the same time, writing in Word still does not completely resolve the original issue. Eventually, the time will come to copy these responses into Excel. This introduces its own set of challenges and limitations:

• Text formatting will be eliminated.
• Tracked changes may be destroyed or garbled (Figure 1).
• Paragraph breaks are treated as a new cell command (Figure 2).
• Cells have hard and fast size limits:²
  • Dimensions (255 characters wide, 409 points tall)
  • Data (no more than 32,767 characters, about 6,424 words)

Word to Excel 

Our Excel track changes tool is a set of several VBA scripts, running in Word and Excel, which make it possible to easily migrate written content from Word to Excel. This solution provides two novel and innovative features.

Track Changes

The Excel platform provides no support for tracked changes, but it does provide support for rich text formatting within text boxes. We exploit this functionality to automatically “convert” Word track changes to formatting-based visual markup in Excel. While these are not “true” track changes, they nevertheless have great use for proposal development specialists and teams whose clients request to see the tracked changes in Excel format. Advantages to this approach include the following:

• Clearly visible insertions and deletions (Figure 3)
• Ability to “accept” or “reject” all insertions or deletions using simple find-and-replace formulae

Simplified Migration

Our solution simplifies the migration process, particularly the issue of paragraph breaks being treated as new cell divisions. If you take care to format your Word document correctly, the script can convert paragraph breaks into line breaks, making it simple to copy and paste your entire Word document much more quickly than before. The two processes are compared below.

For each RFP response:

1. Copy the response in Word
2. Double-click the Excel cell
3. Paste in the copied text
4. Re-insert line breaks

For each RFP section:

1. Copy all responses in Word
2. Paste at first response cell in Excel

Conclusion

While Excel-based RFPs and proposals are inconvenient, they’re not going away any time soon, so we need to adapt and “excel” in spite of the platform’s significant limitations and challenges. With professional support, you can take advantage of creative tools and techniques that help your message get through and succeed.

1: This joke was made up, by me, for this article, and is therefore neither particularly old nor homespun.

2: A comprehensive list of Excel’s specifications and limits is available here.