cell formatting won't update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi this is a follow up to my last questions. My problem is the data and
getting the formula's to read it. I copied and pasted the data from a pivot
table and the columns are set to accounting two digits. But thats not what
shows in the cell. Each cell shows many digits. The formulas are working
now if I put the curser in the cell and select return. The values then
update to the accounting format it is supposed to be already and everything
works fine. ????

Any ideas what is going on, how to avoid it and a faster way than selecting
every cell individually.

Thanks so much!

Todd
 
Not sure what could be happening there, but you could try this ..

Select an empty cell, enter a zero. Copy the cell, then select the range/col
of numbers, right-click > paste special > add > ok

Check also the book's calc mode, ensure it's set to auto
[via: Tools > Options > Calculation tab]
 
Thank you Max! For both the answers (three really). Everything is working
and I learned some very helpful info.


Todd


Max said:
Not sure what could be happening there, but you could try this ..

Select an empty cell, enter a zero. Copy the cell, then select the range/col
of numbers, right-click > paste special > add > ok

Check also the book's calc mode, ensure it's set to auto
[via: Tools > Options > Calculation tab]

---
Todd said:
Hi this is a follow up to my last questions. My problem is the data and
getting the formula's to read it. I copied and pasted the data from a pivot
table and the columns are set to accounting two digits. But thats not what
shows in the cell. Each cell shows many digits. The formulas are working
now if I put the curser in the cell and select return. The values then
update to the accounting format it is supposed to be already and everything
works fine. ????

Any ideas what is going on, how to avoid it and a faster way than selecting
every cell individually.

Thanks so much!

Todd
Click to expand...
Click to expand...
 
Max

To save a few keystrokes, it is not necessary to enter the 0 in a cell.

Just copy a blank/empty cell and Paste Special>Add


Gord Dibben MS Excel MVP
 
Gord Dibben said:
Just copy a blank/empty cell and Paste Special>Add
To save a few keystrokes, it is not necessary to enter the 0 in a cell.
Click to expand...

Agreed, Gord. It was just a precaution to make doubly certain that the
"empty" cell selected for copy didn't contain any "invisible" white spaces.
i.e. it's really empty. One never knows which "empty" cell might get selected
over there <g>.

---
 
Good point Max.

I have been lucky so far and not encountered the "space in the blank cell".

I'll keep it in mind.


Gord Dibben MS Excel MVP
 
Yeah, but...

If you have empty cells in the "pasted" range, then you'll end up with 0's in
those cells (if you start with a cell with 0).

The blank (really empty) cell doesn't have this trouble.
 
Dave Peterson said:
Yeah, but...
If you have empty cells in the "pasted" range, then you'll end up with 0's in
those cells (if you start with a cell with 0).
The blank (really empty) cell doesn't have this trouble.
Click to expand...

Good point, Dave. Perhaps the precaution (if at all necessary) should be
just to clear the selected "empty" cell instead (press Delete key) before
copying it.

---
 
I sometimes just go outside the used range (ctrl-end, then down one
row--especially in code).

But mostly, I'm careful to select an empty cell <vbg>.
 
Dave Peterson said:
I sometimes just go outside the used range (ctrl-end, then down one
row--especially in code).
But mostly, I'm careful to select an empty cell <vbg>.
Click to expand...

... think I'll stick with the single stroke of the Delete key <vbg>

---
 
Well, here's hoping that sacrificial cell wasn't used in any subsequent
formulas/code <gd&r>.
 
Thanks so much for posting this. I had the exact same problem Todd was
having with formatting. The Paste/Add thing worked perfectly to get the
cells to update to the formmating I wanted. Thanks a bunch!

Max said:
Not sure what could be happening there, but you could try this ..

Select an empty cell, enter a zero. Copy the cell, then select the range/col
of numbers, right-click > paste special > add > ok

Check also the book's calc mode, ensure it's set to auto
[via: Tools > Options > Calculation tab]

---
Todd said:
Hi this is a follow up to my last questions. My problem is the data and
getting the formula's to read it. I copied and pasted the data from a pivot
table and the columns are set to accounting two digits. But thats not what
shows in the cell. Each cell shows many digits. The formulas are working
now if I put the curser in the cell and select return. The values then
update to the accounting format it is supposed to be already and everything
works fine. ????

Any ideas what is going on, how to avoid it and a faster way than selecting
every cell individually.

Thanks so much!

Todd
Click to expand...
Click to expand...
 
Back
Top