We are familiar with diverse types of conditional formatting based on the criteria of various Excel users. However, in this article, we will discuss changing alignment using conditional formatting. Follow the steps and apply the necessary commands to apply alignment using Excel conditional formatting.

## Apply Alignment with Conditional Formatting in Excel: 2 Examples

To demonstrate alignment using conditional formatting, we have a sample data table like the image below. In this data table, we have columns for Item, Color, and Price.

### 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. This change in alignment will happen in the reverse order i.e.- if cells are on left they will align right.

**Number Format for Right Aligned Text:**

** #,##0* ;;;* @**

**📌**** Steps:**

- First of all, we will select the
**Color**column and then go through the following procedures:**Home >> Conditional Formatting >> New Rule.**

- After clicking the
**New Rule**, the**New Formatting Rule**pop-up will appear like the image below.

- In the
**New Formatting Rule**pop-up, we will 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"`

- After that, we will select
**Format.**

- After selecting
**Format, Format Cells**pop-up will appear**.**

- In the
**Format Cells**pop-up, we will go to**Custom**under the Number tab. Then we will enter the following formula:

`#,##0* ;;;* @`

- Now we will click
**OK**.

- Now again in the
**New Formatting Rule,**we will hit the**OK.**

- In the following image, we are observing that Blue has changed its alignment from left to right using conditional formatting.

**Read More:Â **How to Copy Conditional Formatting to Another Workbook in Excel

### 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. This shift in alignment will occur in the opposite direction, so if cells are aligned to the right, they will align to the left.

**For Center Alignment:**

` #,##0_)Â Â Â Â Â ;(#,##0)Â Â Â Â Â Â Â Â Â Â `

**For Left Alignment:**

` #,##0* ;;;* @`

**📌**** Steps:**

- Now we will choose the Price column and proceed as follows:
**Home >> Conditional Formatting >> New Rule.**

- We will select
**Use a formula to determine which cells to format**in**New Formatting Rule >>**enter the following formula in the box of**Format****values where this formula is true:**

`=$D5>15`

- And then, we will press
**Format.**

- The
**Format Cells**window will emerge once you pick**Format**like the following image.

- We will select
**Custom**from the**Number**menu in the**Format Cells**pop-up. Then we will plug in the following formula shown in the image below:

`#,##0_)Â Â Â Â Â ;(#,##0)Â Â Â Â Â Â Â Â Â Â `

- And we will press
**OK**.

- Once again, we will select
**OK**to complete the procedures.

- Now the result is in the following image that the cells containing values more than 15 have aligned in the middle.

Now, let us see how to align numbers to the left side. Do the following for that.

- We will 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`

- Following that, we will choose
**Format.**

- The
**Format Cells**pop-up will appear once you select**Format**.

- We will select Custom from the Number menu in the Format Cells pop-up. Then we will plug in the following formula:

`#,##0* ;;;* @`

- We will now press the
**OK**button.

- For this time, we will now press the
**OK**button in the**New Formatting Rule**.

- Finally, in the accompanying figure, we can see that a number bigger than 15 has 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.

**Read More:Â **How to Copy Conditional Formatting with Relative Cell References in Excel

**Download Practice Workbook**

You can download the practice workbook from the following download button.

## Conclusion

Follow these steps and stages to excel in conditional formatting alignment. You are welcome to download the workbook and use it for your practice. If you have any questions, concerns, or suggestions, please leave them in the comments section.

**Related Articles**

- How to Make Yes Green and No Red in Excel
- How to Create a Rating Scale in Excel
- How to Use Conditional Formatting on Text Box in Excel
- How to Apply Borders in Excel with Conditional Formatting

- How to Copy Conditional Formatting to Another Cell in Excel
- How to Copy Conditional Formatting Color to Another Cell in Excel
- How to Copy Conditional Formatting to Another Sheet
- How to Copy Conditional Formatting But Change Reference Cell in Excel

**<< Go Back to Conditional Formatting | Learn Excel**

Curious how this is accomplished if you want your number values to have 2 or more decimal places

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

what is use to do a conditional alignment for a text?

Seems like the #,##0_) ;(#,##0) only works for number alignment

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:Hopefully, the solution will fulfil your goal. Download the attached solution workbook for a better understanding.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

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 cellsdialog box then fromAlignmentselect any select anyAlignmentsof your choice.Regards

ExcelDemyLook 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…

Dear, Thanks for pointing out the fact! You are right. The

Alignmenttab in theFormat Cellsdialog 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:B7is changed. If the cell value isRed, it will applyleft alignment. ForBlue, it will becenter alignment; for the other values, it will applyleft alignment.Excel VBA Event Procedure:Hopefully, the solution will fulfil your goal. I have attached the solution workbook as well. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemy