Combining Spreadsheet Comments for Report Feedback in Excel
/If you’ve ever had to mark different sections of a student report using a spreadsheet, you’ll know the pain of writing feedback in multiple columns and then trying to combine it into one coherent message. You’d think Excel would be thrilled to help, but it turns out it has a habit of making things harder than they need to be. Fortunately, with a little Excel wizardry, we can make it work.
The Problem: Merging Feedback for Different Report Sections
Let’s say you’ve got a spreadsheet where you’ve left comments on different sections of a report:
Report Section | Introduction Feedback | Risk Analysis Feedback | Presentation Feedback |
---|---|---|---|
Report 1 | Clear but brief | Lacks detail | Well-structured |
What we’d like to do is take all these comments and merge them into a single, readable feedback message. But we don’t want everything mashed together in a way that makes it impossible to read. We want nice, neat line breaks between each section.
Solution: Using Excel’s TEXTJOIN
Function
The TEXTJOIN
function is a bit of an unsung hero in Excel. It lets you take multiple pieces of text, stitch them together, and even throw in a separator between them. In this case, we want to use a new line (which Excel calls CHAR(10)
) as our separator.
Here’s the formula you need:
=TEXTJOIN(CHAR(10), TRUE, B1, B2, C1, C2, D1, D2)
This produces a sequence of headings and comments. Let’s break this down:
TEXTJOIN
is our function of choice for merging text.CHAR(10)
is the special character that represents a new line in Excel (Windows users, take note! If you’re on a Mac, you might needCHAR(13)
).- The
TRUE
argument tells Excel to skip any empty cells (so you don’t end up with awkward extra blank lines). B2, C2, D2
are the columns containing our feedback comments.
One More Thing: Wrap That Text!
By default, Excel doesn’t believe in displaying new lines. It just smugly ignores your CHAR(10)
, leaving you staring at an unhelpful string of text.
To fix this:
- Select the column where the merged comments appear.
- Click Wrap Text in the toolbar (under the Home tab).
- Marvel at the newfound readability of your feedback.
Copying the Comments Elsewhere
If you want to paste the merged comments into a report, Word document, or email, the new lines should carry over—unless you’re pasting into a plain text field that doesn’t respect line breaks. If that happens:
- Try pasting into Notepad first, then copying again.
- Use
Ctrl + H
(Find and Replace) in Excel to swapCHAR(10)
for another separator (like a semicolon or bullet point) before copying.
Final Thoughts
This method saves a ton of time when providing feedback on different sections of a report. No more manually copy-pasting comments or trying to decipher your own mess of concatenated text. And as a bonus, you get to look like a spreadsheet wizard in front of your colleagues.
So, go forth and merge feedback with confidence! And don’t forget to turn on Wrap Text, or you’ll be staring at a very long, very unreadable single line of text, wondering where it all went wrong.
Happy grading!
Thanks to ChatGPT for help writing this.