I would like to show a cell in basis points, so that if the numeric value is 0.01, it renders as a string "1 bps" in the cell; 1.00 would show "100 bps"

Is there a way to do this through custom formatting in Excel?

2

1 Answer

Yes! It is a bit tricky / obscure ...

1. Create a custom format like this: 0" bps"%% Each % sign multiplies the value by 100 so two of them gets you from a raw value to bps. You can leave out the text (" bps") or add decimals (0.0 vs just 0) ... your preference. This format for a value of 0.01 shows 100 bps%% ... which is not ideal with the weird %% at end.

Now here's the trick

2. Go back into the custom format ... move your cursor between the second " and the first % hold down Alt and type the numbers 010 on number pad. (that creates a line break between the " and % Hit OK

3. The cell will still look the same (with the %% at end) but then format the cell as Wrap Text and presto!!! the %% falls to the next line and is hidden Now the cell with 0.01 shows "100 bps"

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy