Not sure this applies to your particular case, but might help someone who lands here searching:

When I need to annotate/flag a number to be visible in a print version (or just to be more obvous and easier to use than comments, such as using auto-filters), but still need to perform calculations based on the cell's value, I have three different solutions.


First, I use cell color or some other formatting. For example, if I need one or two different flags, I'll set the backgrounds to variations of grey scale (make sure pairs are of easily distinguishable colors when printing to a BW printer). Plus, this option allows for conditional formatting so I don't have to go look for the values myself. Some other formatting options include adding a border to the cell or increasing font weight, changing to italics, or using line-through. Conditional formatting is too complex to explain here, but is certainly worth the time to investigate.


The second option is to add a narrow column just after the column that contains the values I want to annotate and enter the annotationing characters there. Insert an entire column, not just the cells near those that require annotation to avoid messing up any other formulas. This, too, can be a formula that follows rules to annotate based on values to be annotated, the row number of the cells, other cells, named ranges containing formulas, or any combination of these options.


Both options will show up when printed, allow the values to be used calculations without fancy de-texting operations, reduce workload by avoiding modification of individual formulae, reduce the potential for errors if the same sheet is used later (left over annotations), and make you look really SMRT, smart. Oh, and they work well together. You can use line-through on values that should be ignored or skipped and then use a formula to annotate the reasons in the next column.


Finally, if the annotation rules are simple, use numerical formatting. In the format cells dialog box under the number tab, use the custom format category to add characters based on values. How to use this option goes beyond this forum entry but it is worth the time to learn how this works. I once used this to add measurement abbreviations to a set of values, but could still calculate based on the values. Plus, I once used this to assign three different alignment options to one cell (first value aligned left and second value to the right). No matter how much I opened the column, each value aligned to the edges of the cell. Plus, the center had a pipe to separate the values, which was centered.

Back in the bad old days of Excel 2003, using this option combined with conditional formatting was the only way, besides macros, to get more than three conditional formats.


Really brave? Try all three options at once! Or just learn VBA.