How to Apply Alignment in Excel Conditional Formatting

Get FREE Advanced Excel Exercises with Solutions!

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.

A sample data table: How to Apply Alignment in Excel Conditional Formatting


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.

Apply Right and Left Alignment for Text That Meets a Criteria

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

 New Formatting Rule pop-up will appear

  • 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.

Enter a formula in the box of Format values where this formula is true:

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

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.

Apply Right and Left Alignment for Text That Meets a Criteria

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

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.

Blue has changed its alignment from left to right

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.

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

  • 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.

Press on Format

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

The Format Cells window will emerge

  • 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.

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

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

we will select OK

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

the result is in the following image

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.

Choose Format

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

The Format Cells pop-up will appear

  • 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.

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

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

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.

Align Numbers to Right, Center, and Left with Excel 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

<< Go Back to Conditional Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Ikram Amit
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

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

    • Reply Abrar-ur-Rahman Niloy
      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

  2. what is use to do a conditional alignment for a text?
    Seems like the #,##0_) ;(#,##0) only works for number alignment

    • 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.

      Alignments

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo