Round Entire Worksheet

[dschroeder]

Is it possible in Excel 2007 to round an entire worksheet at once?

I want to round figures to the nearest 1,000 then drop the last 3 zeros.

See data below. I want to take the data shown in the 1st picture and make it look like the 2nd. I know I can do it cell by cell but can it be done to an entire worksheet at one time?


dollars

<TABLE style="WIDTH: 283pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=377><COLGROUP><COL style="WIDTH: 177pt; mso-width-source: userset; mso-width-alt: 8630" width=236><COL style="WIDTH: 106pt; mso-width-source: userset; mso-width-alt: 5156" width=141><TBODY><TR style="HEIGHT: 15.75pt; mso-outline-parent: collapsed" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #99ccff; WIDTH: 177pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl117 height=21 width=236>Cash Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 106pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl118 width=141> $ 6,086,643.95 </TD></TR><TR style="HEIGHT: 15.75pt; mso-outline-parent: collapsed" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #99ccff; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl117 height=21>A/R Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl118> $ 23,289,815.97 </TD></TR><TR style="HEIGHT: 15.75pt; mso-outline-parent: collapsed" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #99ccff; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl117 height=21>Inventory Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl118> $ 200,663,486.54 </TD></TR></TBODY></TABLE>


dollars in thousands

<TABLE style="WIDTH: 283pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=377><COLGROUP><COL style="WIDTH: 177pt; mso-width-source: userset; mso-width-alt: 8630" width=236><COL style="WIDTH: 106pt; mso-width-source: userset; mso-width-alt: 5156" width=141><TBODY><TR style="HEIGHT: 15.75pt; mso-outline-parent: collapsed" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #99ccff; WIDTH: 177pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl117 height=21 width=236>Cash Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 106pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl118 width=141> $ 6,087 </TD></TR><TR style="HEIGHT: 15.75pt; mso-outline-parent: collapsed" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #99ccff; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl117 height=21>A/R Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl118> $ 23,290 </TD></TR><TR style="HEIGHT: 15.75pt; mso-outline-parent: collapsed" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #99ccff; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl117 height=21>Inventory Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl118> $ 200,663</TD></TR></TBODY></TABLE>

Thanks for your help

David

 

[Matty]

Try this...

Type 1000 in an empty cell, select that cell, hit copy, select the cells you want to change, go to Paste Special, select Divide and click OK.

Now format those cells as numbers with no Decimal places.

Matty

 

[dschroeder]

Very cool function. It gave me exactly what I was looking for. Now, is there a way to toggle it on and off so I can show the figures each way as needed?

Thanks

David

 

[shg]

You could instead just format as $ #,##0,