Number Excel Comments for Printing
If you add comments to an Excel worksheet, you might want to include those comments when printing. There are a couple of built in options for printing comments, but neither is ideal. We’ll look at those, then a numbering system, that’s similar to numbered footnotes.
The Built In Options
In the Page Setup dialog box, on the Sheet tab, there are 3 options for printing the comments:
- At end of sheet
- As displayed on sheet
If you select At end of sheet, a separate page of comments prints, listing the cell address, commenter name and comment text.
If you select As displayed on sheet, the comment that are currently visible on the worksheet will print, exactly as they appear on screen. That might work if there are a couple of comments that you want to show, and can arrange them over an empty space. Otherwise, you’ll end up with a jumbled mess of comments, covering your data.
Add Numbers to Cells With Comments
Instead of using either of the built in options to print comments, you could use a bit of programming to add a tiny number at the top right of each cell that has a comment. Here’s a close up view of the numbered cells.
List the Numbered Comments
With another bit of programming, you can create a numbered list of the comments, with other details, such as range name, cell value, cell address and comment text.
This list is on a separate worksheet, that you can print when you print the sheet with comments.
Download the Sample File
To download the sample file for Excel 2003 or Excel 2007/2010, go to the Number and List Comments section on the Comments programming page. There’s sample code to add numbers, remove numbers and list the comments, and a zipped sample file that you can download.
The Excel 2003 numbering code didn’t work well in Excel 2007. The numbers didn’t appear in some boxes, and the boxes didn’t line up correctly in the cells. So if you’re using Excel 2007, be sure to download that version’s sample file
Both files contain macros, so you may get a warning when you open them. Enable the macros if you want to run the code.