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 need CHAR(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:

1. Select the column where the merged comments appear.
2. Click Wrap Text in the toolbar (under the Home tab).
3. 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 swap CHAR(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.

Creating Surveys using OneDrive

I really like surveymonkey. I use it quite a bit. When we want to pick the pizza toppings for Three Thing Game I put up a quick survey and then I have a cunning little Excel spreadsheet that works out how many pizzas I need to order. The only problem I have with the service is that I'm from Yorkshire in England, and this gives me a disposition which is not disposed to parting with money. And the free surveys that you get are great, but for some of the good stuff you have to pay money. 

However, I just found a way of getting free surveys from OneDrive. Better yet, it makes the surveys and delivers the results straight into an Excel spreadsheet that you can work in online. So I might be able to integrate my cunning spreadsheet (Pro tip: around 2.5 students per large pizza seems to work) into this as well. 

You make a new survey by pressing the Create button on the OneDrive website. Select Excel survey and a wizard starts up that will talk you through creating your survey

You can enter a number of different kinds of question, including Yes/No and multiple choice. Each question can have a subtitle if you really want it to.

Finally, when you've finished, you can preview the questionnaire and get a link to it, which can be shortened into a tiny one. You can find my cheese survey here.

The survey results end up in a spreadsheet in your OneDrive storage which you can use as you would any other. When you open it all the responses are there for you to look at. 

Very nice, and I'll be using it for the next Three Thing Game I reckon. 

Cleaning with Notepad

One of my many (and increasing by the day) New Year's Resolutions is to blog things that I find useful. This is a simple enough trick which I use rather a lot, but you might not know it. 

If you move data around from one program to another using the clipboard you often find things attached to the data that you don't want. Sometimes text can have HTML wrapped round it, or perhaps there is some formatting that you want to get rid of. I use Notepad to do this. Drop the text into Notepad and then copy it back out again. Notepad is only good for text, and so it will normally get rid of any unwanted data. It is even easier if you use the keyboard shortcuts:

Click in your Notepad window, CTRL+A to select all the existing text in the Notepad window, CTR+V to paste over it  the text you want to "clean", CTRL+A to select the cleaned and then CTRL+C to copy it. 

There are probably much neater ways of doing this, but it works and you can find Notepad on most any machine. 

“Keep with next” with Word

I’ve been in the lab most of today marking First Year projects. One of the things that we ask the students to do is create a user manual for the program that they write. I reckon that the manual works best if it has headings that direct the reader to particular topics. But that can cause problems….

Sometimes the heading can end up at the bottom of the page, like the rather contrived example above. If you want to stop this happening, you can right click on the heading text (in the example above “Getting a copy of the notes”) and choose “Paragraph” from the menu that appears.

Then you can select “Keep with next” for the paragraph and this means that if you have a page break in the wrong place (as above) then the heading will follow the text over the page.

If you are entering program samples, or things that you don’t want to have split over page breaks you can also use the “Keep Lines Together” setting. If you add this behaviour to styles you can get your documents to lay themselves out automatically.