Is there a way to set Excel up whereby trailing spaces are visible?

1: Use a conditional format with the whole table selected and a formula to set the condition, I used

=OR(RIGHT(A1,1)=" ",LEFT(A1,1)=" ")

and all cells with a leading or trailing space got a red background and are double underlined, this will let you focus on those cells that have extra invisible spaces and as @fixed1234 suggested the underlines with show you where the spaces are. Cells that Excel treats as numbers do not show up with spaces even if they were entered with spaces (unless preceded by an apostrophe thus forcing it into a text format).

2: Find or edit a Font where the space character is not blank.

Some ideas about font editing in the answer linked https://graphicdesign.stackexchange.com/questions/19554/how-to-replace-a-character-in-a-font-with-another-character

The best answer I've found (Rick Rothstein, MVP - Excel):

If I understand what you want to do, then unlike Microsoft Word (which allows you to do what you are asking for), Excel does not have a way of showing you the non-printing characters I think you are interested in. One reason may be because what you see in the cell may not be "real"... it could come from, or be a concatenation from, other cells on the sheet, or from a different sheet or even from a different workbook. On top of that, the cell may be formatted to look the way you see it, but the actual value in the cell may be completely different. I think it would be a monumental task for the programmers of Excel to figure out how to show you the "invisible characters" you seek given the various ways in how the value displayed in the cell could be formed to look that way.

Two solutions:

1. Conditional formatting

   This method doesn't directly show the invisible characters, but it identifies cell that have them. Select a cell and go to Conditional Formatting. Select the option to use a formula. Say the cell is A1. Enter a formula like =LEN(TRIM(A1))<LEN(A1) and select a fill color. Use Copy, then select the entire sheet, and Paste Special | format to copy the conditional formatting. Any cell containing trailing spaces will be highlighted.

2. Underlining

   This solution will identify the trailing spaces but is not nearly as useful as conditional formatting because with everything underlined, you need to look at each cell to see if it is revealing anything. Select the entire sheet, go to the font format window (right-click | format cell | fonts, or Fonts from the ribbon menu), and turn on underlining (turn off when you're done). The underline will extend to the end of the content, so trailing spaces will be identifiable.