Excel formatting not working for some cells when trying to do center alignment
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?
microsoft-excel microsoft-excel-2010
asked Apr 18 '14 at 20:15
skeletank
831111
add a comment |
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?
microsoft-excel microsoft-excel-2010
asked Apr 18 '14 at 20:15
skeletank
831111
I had a similar problem: formatting the number of digits after the decimal point for an entire row does not work for certain cells in that row, though all of the cells are calculated by the same function macro.
– richard1941
Apr 26 '17 at 3:38
add a comment |
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?
microsoft-excel microsoft-excel-2010
asked Apr 18 '14 at 20:15
skeletank
831111
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?
microsoft-excel microsoft-excel-2010
microsoft-excel microsoft-excel-2010
asked Apr 18 '14 at 20:15
skeletank
831111
asked Apr 18 '14 at 20:15
skeletank
831111
asked Apr 18 '14 at 20:15
skeletank
831111
asked Apr 18 '14 at 20:15
skeletank
831111
asked Apr 18 '14 at 20:15
skeletank
831111
831111
I had a similar problem: formatting the number of digits after the decimal point for an entire row does not work for certain cells in that row, though all of the cells are calculated by the same function macro.
– richard1941
Apr 26 '17 at 3:38
add a comment |
I had a similar problem: formatting the number of digits after the decimal point for an entire row does not work for certain cells in that row, though all of the cells are calculated by the same function macro.
– richard1941
Apr 26 '17 at 3:38
I had a similar problem: formatting the number of digits after the decimal point for an entire row does not work for certain cells in that row, though all of the cells are calculated by the same function macro.
– richard1941
Apr 26 '17 at 3:38
I had a similar problem: formatting the number of digits after the decimal point for an entire row does not work for certain cells in that row, though all of the cells are calculated by the same function macro.
– richard1941
Apr 26 '17 at 3:38
add a comment |
6 Answers
6
active
oldest
votes
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
answered Apr 18 '14 at 20:15
skeletank
831111
add a comment |
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.
answered Sep 19 '17 at 12:44
Steve Taylor
465522
add a comment |
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 :-(
answered Aug 16 '17 at 9:39
DJDenton
11
add a comment |
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.
answered Apr 11 '18 at 19:58
Rob Fox
1
The accepted answer is already “Data → Text To Columns”. Have you tried doing just that? Is your extra step necessary?
– Scott
Apr 11 '18 at 20:59
add a comment |
The 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.
answered Oct 16 '18 at 20:07
SArce
1
add a comment |
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!
answered Dec 10 '18 at 5:19
Sasa
1
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f743499%2fexcel-formatting-not-working-for-some-cells-when-trying-to-do-center-alignment%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
6 Answers
6
active
oldest
votes
6 Answers
6
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Apr 18 '14 at 20:15
skeletank
831111
add a comment |
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
answered Apr 18 '14 at 20:15
skeletank
831111
add a comment |
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
answered Apr 18 '14 at 20:15
skeletank
831111
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
answered Apr 18 '14 at 20:15
skeletank
831111
answered Apr 18 '14 at 20:15
skeletank
831111
answered Apr 18 '14 at 20:15
skeletank
831111
answered Apr 18 '14 at 20:15
skeletank
831111
831111
add a comment |
add a comment |
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.
answered Sep 19 '17 at 12:44
Steve Taylor
465522
add a comment |
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.
answered Sep 19 '17 at 12:44
Steve Taylor
465522
add a comment |
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.
answered Sep 19 '17 at 12:44
Steve Taylor
465522
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.
answered Sep 19 '17 at 12:44
Steve Taylor
465522
answered Sep 19 '17 at 12:44
Steve Taylor
465522
answered Sep 19 '17 at 12:44
Steve Taylor
465522
answered Sep 19 '17 at 12:44
Steve Taylor
465522
465522
add a comment |
add a comment |
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 :-(
answered Aug 16 '17 at 9:39
DJDenton
11
add a comment |
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 :-(
answered Aug 16 '17 at 9:39
DJDenton
11
add a comment |
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 :-(
answered Aug 16 '17 at 9:39
DJDenton
11
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 :-(
answered Aug 16 '17 at 9:39
DJDenton
11
edited Aug 16 '17 at 13:36
answered Aug 16 '17 at 9:39
DJDenton
11
answered Aug 16 '17 at 9:39
DJDenton
11
answered Aug 16 '17 at 9:39
DJDenton
11
11
add a comment |
add a comment |
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.
answered Apr 11 '18 at 19:58
Rob Fox
1
The accepted answer is already “Data → Text To Columns”. Have you tried doing just that? Is your extra step necessary?
– Scott
Apr 11 '18 at 20:59
add a comment |
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.
answered Apr 11 '18 at 19:58
Rob Fox
1
The accepted answer is already “Data → Text To Columns”. Have you tried doing just that? Is your extra step necessary?
– Scott
Apr 11 '18 at 20:59
add a comment |
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.
answered Apr 11 '18 at 19:58
Rob Fox
1
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.
answered Apr 11 '18 at 19:58
Rob Fox
1
answered Apr 11 '18 at 19:58
Rob Fox
1
answered Apr 11 '18 at 19:58
Rob Fox
1
answered Apr 11 '18 at 19:58
Rob Fox
1
1
The accepted answer is already “Data → Text To Columns”. Have you tried doing just that? Is your extra step necessary?
– Scott
Apr 11 '18 at 20:59
add a comment |
The accepted answer is already “Data → Text To Columns”. Have you tried doing just that? Is your extra step necessary?
– Scott
Apr 11 '18 at 20:59
The accepted answer is already “Data → Text To Columns”. Have you tried doing just that? Is your extra step necessary?
– Scott
Apr 11 '18 at 20:59
The accepted answer is already “Data → Text To Columns”. Have you tried doing just that? Is your extra step necessary?
– Scott
Apr 11 '18 at 20:59
add a comment |
The 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.
answered Oct 16 '18 at 20:07
SArce
1
add a comment |
The 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.
answered Oct 16 '18 at 20:07
SArce
1
add a comment |
The 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.
answered Oct 16 '18 at 20:07
SArce
1
The 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.
answered Oct 16 '18 at 20:07
SArce
1
answered Oct 16 '18 at 20:07
SArce
1
answered Oct 16 '18 at 20:07
SArce
1
answered Oct 16 '18 at 20:07
SArce
1
1
add a comment |
add a comment |
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!
answered Dec 10 '18 at 5:19
Sasa
1
add a comment |
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!
answered Dec 10 '18 at 5:19
Sasa
1
add a comment |
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!
answered Dec 10 '18 at 5:19
Sasa
1
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!
answered Dec 10 '18 at 5:19
Sasa
1
answered Dec 10 '18 at 5:19
Sasa
1
answered Dec 10 '18 at 5:19
Sasa
1
answered Dec 10 '18 at 5:19
Sasa
1
1
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f743499%2fexcel-formatting-not-working-for-some-cells-when-trying-to-do-center-alignment%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I had a similar problem: formatting the number of digits after the decimal point for an entire row does not work for certain cells in that row, though all of the cells are calculated by the same function macro.
– richard1941
Apr 26 '17 at 3:38