I have an excel file that has been exported from a SQL Server Reporting Services report. The cells in the first column are a list of store numbers and should all be center aligned but for some reason a few of them are left aligned. When I go to correct the alignment by setting it to center nothing happens. When I go and change the column type from General to Number to Text still nothing happens. However, when the column is set to Text and then I go edit (F2 then 'enter') the cell it magically aligns back to the middle. This would be great except for the fact that I don't want to have to do this for each individual cell.
Has anyone ran into this issue before. Is there any way to correct the alignment of all the cells in the column without going to each one individually?
111 Answers
I bounced around blogs, forums, etc. and found out that this had something to do with the values being saved as Text vs Number. Eventually I strolled upon a Microsoft article with an alternative solution.
- Select the entire column
- Select the "Data" tab
- Press the "Text to Columns" button under "Data Tools"
- For "Step 1" press "Next"
- For "Step 2" press "Next"
- For "Step 3" select "Text" as the "Column data format" and then press "Finish"
- When you go to check your columns they should all be aligned correctly
In Excel 2016 I found the issue was being caused by an incorrect Style being applied to the cell - none of the other Answers was able to correct this.
By clicking on the cell and selecting a different Style (Home > Styles > 'Normal' for example) the variant formatting was removed and I was able to change the cell format back to its normal state.
The easiest answer that worked for me is as below:
Locate a cell that you think is aligned center (or if you want to align left), copy that cell then select all the rows/columns/cells that you want to fix alignment and then "paste as formating".
I used the answer above but added an additional couple of steps. My version of excel is from Mac for windows 2011. After marking the entire column as above, I then highlighted the cells that had numbers in them as they were showing the little green cell flag to indicate an error to the user (numbers stored as text). I then clicked on the exclamation and chose the ignore error option which made the green cell flags disappear. Next I highlighted all the same cells with numbers in them, then on the Home ribbon bar, under the number section, I chose the drop down which was showing text and changed it to number. The centring remained and the cells were now being treated as a number again. NB - if you edit the cell again, the left alignment returns :-(
I found that this problem manifests on all attempts to format by just making this change:
Formulas -> Formula Auditing -> Show Formulas.
When you disable this, formatting function returns. You may then have to;
Data -> Text To Columns -> ...
Hope this helps.
1The columns above and below may have spaces in them that the other columns do not. So when they center align they are aligning to bigger content. Remove the spaces in the other columns and it should correct it. Ctrl+H, Find What: (Put a space here), Click: Replace All, Re-align if necessary.
I tried some of these suggestions but they didn't work for me. However, I found that the thing that did work in my specific situation, was, to remove the comma in the number format section. Uncheck the box that says "Use 1,000 separator (,). I was using several columns, on an amortization sheet and the one column that refused to "center align" was the one that had 2 decimal places - the rest just had 1.
So - Format Cells > Number (tab) > Category: Number > Uncheck box "Use 1,000 separator (,).
Hope this can help someone!
In almost all cases following should work:
- Go to 'Formulas'
- Locate 'Formula Auditing' group
- Disable 'Show Formulas'
try to create another excel tab and type a word then select any alignment symbol. Go back to the previous tab and the alignment will now work.
Disabling Wrap Text worked on my problem cells. None of the suggestions worked. I am using a shared document.
I also have the same problem, but none of those are working, Mine is incapable on aligning because some of the cells are merged. If that is the case, follow this instructions: 1. Select the column or row that has the problem 2. Right click, choose format cell 3. Choose Alignment tab, in the text control box, uncheck merge cells 4. Click Ok. 5. Try to change the alignment like usual.
Hope it works for you!
