Share via

Help creating a custom format using Format Cells/Number/Custom Formatting

Anonymous
2022-07-01T15:08:43+00:00

Hi Everyone,

Currently, my sheet has a column indicating trend using the following custom format (Format Cells/Number/Custom Formatting): [Color10]"▲";[Red]"▼";[Color16]"►". I would like to know how to alter this format so the grey ► arrow appears for values between -2 and 2.

Thanks in advance for your help.

John

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-07-04T13:28:28+00:00

    I was able to figure out the formatting - would anyone know how I can remove the negative sign in the 3rd row, last column (red colour)?

    The formula I'm using is [Color10][>2]"▲";[Red][<-2]"▼";[Color16]"►"

    73 76 -3 ▼
    82 81 1 ►
    82 83 -1 -►

    Thank you,

    John

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2022-07-04T12:24:38+00:00

    Hi Bernie,

    Thanks for your help - for this application, I can't change the 1- to 2 values to 0, it will cause issues and unfortunately, I'm not proficient in VBA.

    I tried using the helper just to see how it worked and was unable to properly merge it with the existing IFERROR formula: =IFERROR(E7-F7," "). I received this pre-programmed and want to do as little reprogramming as possible in the interest of time.

    Feel free to share any other ideas - I appreciate any help i can get with this.

    John

    Was this answer helpful?

    0 comments
  3. Anonymous
    2022-07-01T15:22:29+00:00

    I would use a set of helper formulas in the formatted cells that convert your data values from -2 to 2 into 0:

    =IF(AND(A2>=-2,A2<=2),0,A2)

    You could also use VBA and an appropriate event to apply different formats based on the cell value.

    Was this answer helpful?

    0 comments