I use Excel 2007 on Windows 7 Pro (64 bit), SP1. I have a spreadsheet with about 100 rows and 30 columns; i.e., 3000 data cells. Data is manually entered into the data cells, using 'inches' as the measurement system. There are also about 10 columns of summation results. This spreadsheet uses the Table format. There are several pages of linked Charts that are created based on this data.
I have a friend who wants this information in centimeters. All the methods I knew of (using Convert, etc.) required having additional cells, which is a major task.
JE McGimpsey suggested one way in a 2005 thread, "Convert my entire spreadsheet from inches to centimeters". But that thread appears to be closed.
He said: "Enter 2.54 in an empty cell. Copy the cell. Select your values to
convert. Choose Edit/Paste Special, selecting the Values and Multiply
radio buttons."
So I cloned my original spreadsheet onto a new tab, and used his procedure to quickly populate the new spreadsheet with centimeter dimensions. That worked great.
But there was a problem. I have many empty cells. Sometimes no data was taken on a day, or data was missed at a specific time. My original spreadsheet, and the cloned spreadsheet display those cells as being empty. But in the cloned spreadsheet, his method actually has a 'hidden' 0(zero) in each of the supposedly empty cells. You can only see that 0 if you click on the specific cell.
For some tasks that might not be a problem. But I have multiple charts that use the data on the cloned spreadsheet. So initially those charts displayed all supposedly empty cells as a 0, rather than ignoring them. To fix the problem, I had to click on every cell which appeared to be empty, and if it contained a 0, delete it.
Does anyone know why his method creates this 0, and if there is some way I can modify his procedure to make sure that cells which display as empty are really empty?
Harry
Bookmarks