How to Apply Alignment in Excel Conditional Formatting

To demonstrate alignment using conditional formatting, we have a sample data table like the image below.

Method 1 – Apply Right and Left Alignment for Text That Meets a Criteria

In Excel, the default text alignment is left alignment. In this method, we will see that any cell that meets a certain criterion will change its alignment.

Number Format for Right Aligned Text:

#,##0* ;;;* @

Steps:

Select the Color column, then go to Home and select Conditional Formatting, then select New Rule.

The New Formatting Rule pop-up will appear like the image below.

Select Use a formula to determine which cells to format.
Enter the following formula in the box of Format values where this formula is true:

=$C5="Blue"

Select Format.

The Format Cells pop-up will appear.

Go to Custom under the Number tab.
Enter the following formula:

#,##0* ;;;* @

Click OK.

In New Formatting Rule, hit the OK.

Blue has changed its alignment from left to right using conditional formatting.

Method 2 – Align Numbers to Right, Center, and Left with Excel Conditional Formatting

The default number alignment in Excel is the right alignment. In this procedure, each cell that fulfills a given set of criteria will have its alignment changed.

For Center Alignment:

#,##0_) ;(#,##0)

For Left Alignment:

#,##0* ;;;* @

Steps:

Choose the Price column and select Conditional Formatting New Rule.

Select Use a formula to determine which cells to format in New Formatting Rule.
Enter the following formula in the box for Format values where this formula is true:

=$D5>15

Press Format.

The Format Cells window will appear.

Select Custom from the Number menu in the Format Cells pop-up.
Plug in the following formula shown in the image below:

#,##0_) ;(#,##0)

Press OK.

Select OK to complete the procedure.

The cells containing values more than 15 have aligned in the middle.

Select Use a formula to determine which cells to format in the New Formatting Rule pop-up.
Enter the following formula in the box of Format values where this formula is true:

=$D5>15

Choose Format.

The Format Cells pop-up will appear.

Select Custom from the Number menu in the Format Cells pop-up.
Plug in the following formula:

#,##0* ;;;* @

Press the OK button.

Press the OK button in New Formatting Rule.

Numbers bigger than 15 have been shifted from right to left utilizing conditional formatting.

Note:

You can apply both rules and get the numbers in the left, middle, and right positions simultaneously.

Download the Practice Workbook

Conditional Formatting Alignment.xlsx

<< Go Back to Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range Dim targetRange As Range Set targetRange = Me.Range("B2:B7") If Not Intersect(Target, targetRange) Is Nothing Then For Each cell In Intersect(Target, targetRange) If cell.Value = "Red" Then cell.HorizontalAlignment = xlLeft ElseIf cell.Value = "Blue" Then cell.HorizontalAlignment = xlCenter Else cell.HorizontalAlignment = xlRight End If Next cell End If End Sub

7 Comments

Richard Jan 18, 2024 at 8:48 PM

Curious how this is accomplished if you want your number values to have 2 or more decimal places
- Reply
  Abrar-ur-Rahman Niloy Jan 21, 2024 at 12:38 PM
  
  Hello Richard!
  
  You need to replace 0s in the format type with 0.00s.
  For example, instead of #,##0_) ;(#,##0) for center alignment, use #,##0.00_) ;(#,##0.00).
  
  Regards
  Niloy
  Team Exceldemy
M_curious Feb 9, 2024 at 4:40 AM

what is use to do a conditional alignment for a text?
Seems like the #,##0_) ;(#,##0) only works for number alignment
- Reply
  Lutfor Rahman Shimanto Jun 11, 2024 at 12:23 PM
  Hello there! Thanks for sharing an exciting problem. Since using the conditional formatting option for text alignment is impossible, you can only use an Excel VBA procedure.
  
  Note: The event procedure will trigger when range B2:B7 is changed. If the cell value is Red, it will apply left alignment. For Blue, it will be center alignment; for the other values, it will apply left alignment. You can modify the code based on your needs.
  
  Excel VBA Event Procedure:
```
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range

    Dim targetRange As Range
    Set targetRange = Me.Range("B2:B7")

    If Not Intersect(Target, targetRange) Is Nothing Then
        For Each cell In Intersect(Target, targetRange)

            If cell.Value = "Red" Then
                cell.HorizontalAlignment = xlLeft
            ElseIf cell.Value = "Blue" Then
                cell.HorizontalAlignment = xlCenter
            Else
                cell.HorizontalAlignment = xlRight
            End If
        Next cell
    End If

End Sub
```
  Hopefully, the solution will fulfil your goal. Download the attached solution workbook for a better understanding.
  
  DOWNLOAD SOLUTION WORKBOOK
  
  Regards
  Lutfor Rahman Shimanto
  Excel & VBA Developer
  ExcelDemy
- Reply
  Shamima Sultana Feb 12, 2024 at 1:27 PM
  
  Hello M-curious,
  
  To do a conditional formatting follow the steps of method 1. Instead of using custom option from format cells, you can use the Alignment options.
  Go to Format cells dialog box then from Alignment select any select any Alignments of your choice.
  
  Regards
  ExcelDemy
  - Reply
    ML Jun 11, 2024 at 4:40 AM
    
    Look at the screenshots in the article/post and alignment, when applying conditional formatting, isn’t an option – if it were we wouldn’t be looking for workarounds…
    - Lutfor Rahman Shimanto Jun 11, 2024 at 12:05 PM
      
      Dear, Thanks for pointing out the fact! You are right. The Alignment tab in the Format Cells dialog box is unavailable when setting up conditional formatting rules. So, using the conditional formatting option for text alignment is impossible.
      
      Don’t worry! There is an idea of using an Excel VBA event procedure. Please check the following:
      
      The event procedure will trigger when range B2:B7 is changed. If the cell value is Red, it will apply left alignment. For Blue, it will be center alignment; for the other values, it will apply left alignment.
      
      Excel VBA Event Procedure:
      
      Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range Dim targetRange As Range Set targetRange = Me.Range("B2:B7") If Not Intersect(Target, targetRange) Is Nothing Then For Each cell In Intersect(Target, targetRange) If cell.Value = "Red" Then cell.HorizontalAlignment = xlLeft ElseIf cell.Value = "Blue" Then cell.HorizontalAlignment = xlCenter Else cell.HorizontalAlignment = xlRight End If Next cell End If End Sub
      
      Hopefully, the solution will fulfil your goal. I have attached the solution workbook as well. Good luck.
      
      DOWNLOAD SOLUTION WORKBOOK
      
      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

How to Apply Alignment in Excel Conditional Formatting

Method 1 – Apply Right and Left Alignment for Text That Meets a Criteria

Method 2 – Align Numbers to Right, Center, and Left with Excel Conditional Formatting

Related Articles

Leave a reply Cancel reply