Forum Discussion

robgill205's avatar
robgill205
Copper Contributor
Jul 12, 2023
Solved

Custom formating question

Hi,

I have applied custom formatting to a column that looks like this [Color10]0%"  ▲";[Red]-0%"  ▼";

This shows that if the variance in the cell is positive or negative and shows a percentage value next to the symbol. I'm wondering if it's possible to add the criteria where this symbol "─" is shown when the variance is only +-100,000. 

Would this need to be conditional formatting and not custom formatting?

Thanks

 

 

  • robgill205 

    I'd use conditional formatting.

    Select the column; the first cell in the selection should be the active cell. I'll assume it is D2 in the following.
    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Use a formula to determine which cells to format'.
    Enter the formula

     

    =ABS(D2)<100000

     

    or if you meant 100000%, use

     

    =ABS(D2)<1000

     

    Click Format...
    Activate the Number tab.
    Select Custom.

    In the Type box, enter the formula

     

    "─";"─"


    Click OK, then click OK again.

  • robgill205 

    I'd use conditional formatting.

    Select the column; the first cell in the selection should be the active cell. I'll assume it is D2 in the following.
    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Use a formula to determine which cells to format'.
    Enter the formula

     

    =ABS(D2)<100000

     

    or if you meant 100000%, use

     

    =ABS(D2)<1000

     

    Click Format...
    Activate the Number tab.
    Select Custom.

    In the Type box, enter the formula

     

    "─";"─"


    Click OK, then click OK again.

    • robgill205's avatar
      robgill205
      Copper Contributor
      Thank you, this shows the "─" symbol for all the variances +-€100,000.
      Would it be possible to only show the "─" symbol when the variance is negative?
      This way my "▲" symbol shows even when the variance is a positive and less than 100,000.

Resources