Issues with porting spreadsheets

Hi, I've just started working with Excel. I'm using Excel 2010 on Windows and Excel 2011 on Mac. I have several files that started life as dBase II/Foxpro .dbf files. They are basically sets of cells containing numbers and alphanumeric characters. They were imported into Excel 2010 and since have been swapped back and forth between 2010 and 2011. Here are some of the things I'm seeing:

1) In one file, I have a column with data like "08-23". To prevent Excel from trying to translate this, I found I had to enter a single quotation mark ' in front of the characters when I entered them. If I cut and paste a few rows of data from this file into another file, I find the characters in this column often change. Sometimes they change to pound signs ("#####"). Sometimes they change to a number such as "41136" or "41142." Can anyone explain what is going on, and how I can stop this?

2) Another column has cells containing characters such as "0226." To prevent Excel from converting this (dropping the leading zero, etc) I again enter a single quotation mark ' when entering data. After entering data, I often see a triangular green shape appear at the upper left corner of each of these cells. What causes this to happen?

3) Most spreadsheets by default seem to have light gray borders (lines) around all of the cells of the spreadsheet, whether I've entered data in them or not. In some files, however, the light gray borders are missing, or only on one side of certain cells. If I go to Borders to change this, I seem to end up with solid black borders that are much darker than the default light gray borders. Is there any way to restore the light gray borders throughout the spreadsheet?

I suspect that items 1) and 2) might be resolved by looking at the type of data that each row of cells is defined to contain, but I haven't found the commands yet to accomplish this. Thanks for any help.

1) 08-23 is a date string that Excel can recognize, August 23rd (of the current year).

When copy / paste changes those to #######, more than likely if you expand the column you may see a new format may have been applied by the user's local settings and/or column attributes, it may appear as Thursday August 23rd, 2012, which is too much text to appear in the thinner column.

When a copy / paste changes those dates to numbers in the 40,000 range, you've just discovered the trick of Excel's dates, each day is a whole number.
41136 = August 15, 2012.
41137 = August 16, 2012
etc.

Excel puts the number in the cell, then the "Date Format" is applied to cause that number to appear to the user in the date format they want.

2) Clicking on the green triangle will answer that question.

3) The "light gray" borders are actually called the "grid", they're not really borders. Go into the File > Options > Advanced > Display Options for worksheet and turn on the [x] Show gridlines

Thanks, I appreciate the help. Follow-on:

1) These numbers may look like a date, but I don't want Excel to think they're dates or try to do any manipulations on them. Is there a way to apply a format to this column of cells that says "These are just alphanumeric characters, don't do anything with them"?

2) When I click on the green triangle in the other column, I get a warning, "The number in this cell is formatted as text or preceded by an apostrophe." The only way I can get the triangle to go away is to select "Ignore Error" from a drop-down. It would be very tedious to go through the spreadsheet and do this for all cells in this column. Again, is there a format I can apply to this column that tells Excel these are just alphanumeric characters, don't try to do anything with them?

3) I found the gridlines checkbox (it took a while on the Mac, as many menu items are in different places). This restored gridlines to all of the cells of the spreadsheet. Strangely, though, these appear to be a lighter shade of light gray than the light gray grid that surrounded some of the existing cells. Is it possible that gridlines were just turned off for this spreadsheet, but a light gray border or rule was somehow defined around certain cells?

Thanks again --

Originally Posted by JBeaucaire

THere is no need to store leading zeros in data cells since numbers can be formatted to display with leading zeros, even date values. I would recommend you stop doing the whole "apostrophe" thing and format your columns as needed to display the way you need.

Date format: MM-DD

...would show August first as 08-01.

I'm looking at Excel 2010 for Windows, and can't find a menu option either to (a) make it display a leading zero on a number, or (b) display dates as MM-DD. (I do see a choice for MM/DD, with a slash rather than a hyphen.) Are these options there somewhere?

Thanks, I'll experiment with the CUSTOM number format. Once the definition is created, is it specific to the document, or to the copy of Excel on a specific PC? I'm wondering what I need to do to use the definition if I'm creating a new document on the same or a different computer.

Also, I wanted to go back to my question about gridlines and borders. Below is a link to a partial screenshot from one Excel document. The data has been deleted; the gridlines are turned on:

http://www.inkbox.net/misc/gridlines-on.jpg

and here is the same file with the gridlines turned off:

http://www.inkbox.net/misc/gridlines-off.jpg

Note that, even with gridlines turned off, there are light gray borders around some cells (not quite as light as the gridlines). Some are solid and some are dotted.

If I define these cells, right-click, select Format Cells > Border > Line > Style and pick "None," nothing changes -- these borders are still there.

Is there a way to make them go away?

Thanks for the offer, I don't mean to put you to any trouble.

Do Excel files contain any edit history (a record of data that has been deleted or changed, so that the user can roll back to it if desired)? The reason I ask is that the file that exhibits this behavior with the cell borders contains some proprietary data. I can delete the data, but I'd have to make sure that no record of it persists in a hidden state in the file.

Okay, thanks much. I just attempted to upload a file called "gridline test.xls". I'll be interested in your impressions about what is going on with the borders.

Interesting, thanks much. I didn't find the menu option by that path (in my Excel 2010 in Windows 7, ctrl-F1 toggles display of the menu bar for whatever command tab is currently selected, but I could get to it by defining the cells, right-clicking and selecting a border drop-down. I'm not how those manually applied borders could have been applied, but I'm glad to be rid of them now. Thanks again for the help.