Cell alignment is one of the most paramount ways to emphasize and categorize your data. In different situations, different cell alignment makes users comfortable with the data. But many times, due to different types of formatting issues, we can’t change the cell alignment in Excel. If you are curious to learn how you can change the cell alignment if it’s not working, then this article may come in handy for you. In this article, we discuss how you solve the issue of cell alignment not working in Excel with elaborate explanations.
Cell Alignment Is Not Working in Excel: 5 Possible Solutions
We present 5 probable issues and their solution. These three issues are the most frequent and common issues related to cell alignment. To avoid any version or compatibility issues, try to use the Excel 365 version.
Solution 1: Modify Cell Format
Cell format has a huge influence on the type of alignment of the cell. Sometimes certain cell format prevents the user from changing the cell alignment.
Steps
- We have the Product Information in the range of cells B4:C14.
- And the costs in column C are right aligned.
- Now if we try to change the alignment of the cells, such as center alignment, there is no change of alignment at all. The alignment seemed to be static at this point.
- To resolve this issue, first, copy the cells in the range of cells D5:D18, and then right-click on the mouse.
- Then from the context menu, click on Format Cells.
- A new window named Format Cells will open.
- In that window, go to the Number tab.
- Then click on the Custom option.
- In the right-side menu, you can see the Custom formatting text, which defines your cell values format.
- In the type of field, you will notice a small asterisk in between the text (marked with an arrow sign in the image below).
- Remove those asterisks one by one.
- And then click OK.
- The final formatting will look like the one below.
- After clicking OK, you will notice that the alignment is now in correction alignment.
Read More: All Types of Alignment in Excel
Solution 2: Change Custom Number Formatting
A certain character in the custom formatting can inhibit the alignment-changing process. Some small tweaking in the formatted text can resolve this issue.
Steps
- In the below dataset, we got the unit price in the product information dataset.
- And the Unit Price values are slightly right aligned, and we actually want to align it as the center.
- To do this, first, copy the values in the Unit Price in the D5:D18 range.
- Then right-click on the Unit Price values.
- After that from the context menu, click on Format Cells.
- In the Format Cells new window, click on the Number tab, then click Custom.
- Then in the Type field, notice the spaces in front of the format texts.
- Click OK after this.
- The final format text will look like the below image.
- Click OK after this.
- Now the Unit Prices column is center aligned.
Solution 3: Use Text to Columns Command
Text to Columns feature change the data type and thus solve static formatting issue.
Steps
- In the dataset shown below, the cell values are right aligned.
- The main issue here is that they are saved as text vs. number.
- From the ribbon menu, click on the Data tab, and then click on Data Tools.
- Then from the dropdown menu, click on the Text to Columns commands.
- A new window named the Convert Text to Columns Wizard will open.
- Then from the Choose the file type that best describes your data, select Delimited.
- Click Next after this.
- In the next step, check the Tab box if it is not checked.
- Then click Next again.
- In the final step, select Text and then click on Finish.
- After that, you can change the alignment of the cells in the worksheets.
- We finally changed the alignment to the center.
Note:
While changing the data format to text, the signs from the original format may be lost. To circumvent this, you need to manually input the signs inside the cell. In this example, the Unit Price. Values contain the 4 signs as part of their formatting. After the data formatting, the $ sign now vanished, and we must manually input the $ sign inside the cells.
Read More: How to Align Columns in Excel
Solution 4: Make Sure Cells Are Not Merged
Merged cell sometimes creates the illusion of aberrant alignment of cells. Unmerging and re-formatting the cells could potentially solve the issue of cell alignment not working issue.
Steps
- Sometimes some cells in the Excel sheets appear to be not in alignment properly.
- For instance, notice the below values in the sheet.
- The values of $785, $265, and $2500 now have the right alignment of the cell. And the rest of the cell values are in central alignment.
- So it could appear that the cells are not properly aligned.
- But actually, the $785 and others cell is in a merged state with the neighbor cell.
- It can be seen by hovering over the cell, as the Fill Handle is covering the D5.
- These combined merged cells are in center alignment. But they appear to be Right aligned compared to other cell values if placed side by side.
- This is the same thing for other cells.
- To resolve this, we need to unmerge the cells.
- To do this, select the cells and right-click on the mouse.
- Then in the context menu, notice the merge/unmerge icon in the corner.
- Click this icon to unmerge the cell in the sheet.
- Do the same for the other cells.
- You can fasten up the process by selecting all the cells that have these alignment issues and then clicking on the merge/unmerge icon from the context menu.
- After clicking the Unmerge icon, you can notice that the cells are now unmerged.
- You can now change the alignment of the cells.
- We can change the alignment to center, by clicking on the alignment’s options in the Alignment group in the ribbon.
- Now all of the cells in the Unit Price column have the center alignment.
Read More: Align Decimal Points in Excel
Solution 5: Use the Copy-Paste Feature to Copy an Alignment
Paste special tool enables users to paste not only values but also cell formats. By pasting the desired cell formatting, we can achieve our intended cell formatting with correct alignment in Excel.
Steps
- Here the cell format of the E5 is now going to be implemented in the range of cells C5:C18.
- First, copy cell E5.
- Then select cell C5:C18, and paste the format.
- To paste format, right-click on the range of cells C5:C18, and from the context menu, go to Paste Special > Other Paste Option > Paste Format.
- As the Sample Format cell, E5 is in the center align state, the pasted cells are also in the center align state.
Download Practice Workbook
Download this practice workbook below.
Conclusion
To sum it up, the issue of how we can solve the issue of cell alignment not working in Excel using 5 separate solutions.
For this problem, a workbook is available to download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section.
Related Articles
- How to Left Align in Excel
- How to Top Align in Excel
- How to Middle Align in Excel
- Align Two Sets of Data in Excel
- How to Bottom Align in Excel
- How to Apply Center Horizontal Alignment in Excel
<< Go Back to Alignment in Excel | Excel Cell Format |Â Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
