Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formatting Problem
Hi
I run Excel 2k I have a column of numbers going from 001 through to 700 The numbers have been formatted as custom 000 so as to accomodate the numbers 001 through to 099. They all look as they should on the worksheet. I use this column of data in a pivit table. I found that the numbers 001 & 002 show up in the pivot table as 1 & 2. I looked in the worksheet and noticed that when I select the cell (A1) with the number 001, the edit bar shows it as 1. This also applies for the cell A2 where the number on the worksheet is 002 but in the edit bar it shows as 2. All the other numbers show the same in both the worksheet and the edit bar ( 003, 004, 005, etc etc) I have tried reformatting these 2 cells but it still shows in the edit bar as 1 and 2. I have tried copying over these 2 cells with numbers that work ok (ie; 003, 004 etc) and then editing these number but still they show as 1 and 2. Can anyone tell me how to get these 2 cells to hold the formatting of custom 000 Thanks John |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formatting Problem
Hi John
In the Pivot Tabledouble click the Field nameNumberFormat and set as 000, the same as your source data. Right click on the PTTable Optionsuncheck AutoformatcheckPreserve Formatting. -- Regards Roger Govier "John Calder" wrote in message ... Hi I run Excel 2k I have a column of numbers going from 001 through to 700 The numbers have been formatted as custom 000 so as to accomodate the numbers 001 through to 099. They all look as they should on the worksheet. I use this column of data in a pivit table. I found that the numbers 001 & 002 show up in the pivot table as 1 & 2. I looked in the worksheet and noticed that when I select the cell (A1) with the number 001, the edit bar shows it as 1. This also applies for the cell A2 where the number on the worksheet is 002 but in the edit bar it shows as 2. All the other numbers show the same in both the worksheet and the edit bar ( 003, 004, 005, etc etc) I have tried reformatting these 2 cells but it still shows in the edit bar as 1 and 2. I have tried copying over these 2 cells with numbers that work ok (ie; 003, 004 etc) and then editing these number but still they show as 1 and 2. Can anyone tell me how to get these 2 cells to hold the formatting of custom 000 Thanks John |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formatting Problem
An inverted comma before the 0's will force the value to be displayed as
text. The inverted comma will not be visible in cells. You will see 001 not '001. Excel can still perform mathematical calculations with numbers formatted as text in this manner. The resulting calculation results in a number format Example: 3 * '002 will return 6 (not '006). The text-formatted numbers will increment in sequence using the fill handle (e.g. enter '001 in A1, '002 in A2 and use the fill handle to drag the values down). -- Steve "John Calder" wrote in message ... Hi I run Excel 2k I have a column of numbers going from 001 through to 700 The numbers have been formatted as custom 000 so as to accomodate the numbers 001 through to 099. They all look as they should on the worksheet. I use this column of data in a pivit table. I found that the numbers 001 & 002 show up in the pivot table as 1 & 2. I looked in the worksheet and noticed that when I select the cell (A1) with the number 001, the edit bar shows it as 1. This also applies for the cell A2 where the number on the worksheet is 002 but in the edit bar it shows as 2. All the other numbers show the same in both the worksheet and the edit bar ( 003, 004, 005, etc etc) I have tried reformatting these 2 cells but it still shows in the edit bar as 1 and 2. I have tried copying over these 2 cells with numbers that work ok (ie; 003, 004 etc) and then editing these number but still they show as 1 and 2. Can anyone tell me how to get these 2 cells to hold the formatting of custom 000 Thanks John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formatting problem | Excel Discussion (Misc queries) | |||
formatting problem | Excel Discussion (Misc queries) | |||
Formatting problem? | Excel Discussion (Misc queries) | |||
csv to xls formatting problem | Excel Discussion (Misc queries) | |||
Formatting problem | Setting up and Configuration of Excel |