Formatting with different versions of excel

Topic

New Reply

I am hoping for some help in using formatting in Excel – my problem is that I have to use a spreadsheet on two different pc that run different versions of Excel.

On my my pc I am running Office Prof 2007 – the other one has only 2003.
I have a rather large spreadsheet with 2000 rows and perhaps 100 columns. The data relates to inventory and sales figures and is constantly updated. Not all stock items are active, so to make it visually easier to work with the spreadsheet I have used highlighting (fill in colours) to distinguish between active items, new items and inactive items. Otherwise I am using some generic formatting like (centred) or set some colums as numbers. For two columns I am using conditional formatting. Overall I don’t think that my spreadsheet is very complicated or demanding.

This all worked fine until recently when all of a sudden all formatting was gone. It appears that I have gone beyond the limit of what excel can handle with regards to formatting. I tried to use styles instead and recreated my spreadsheet with highlighting via styles but after I closed it, it again lost all formatting.
As my secondary workstation is running Office 2003 I have to save my worksheet as .xls instead of xlsx.

How should I handle this issue? I did not have any problems with this prior to upgrading to 2007. How can I use my worksheet with visual pointers and ensure that

[*]formatting is not lost
[*]the worksheet can be opened and ideally modified on both systems I am using

Reply | Quote

Replies

Have you checked how many styles are in your workbook?
They have a tendency to multiple when using xl2007.
I have seen reports of workbooks with thousands of styles that magically appeared.

Reply | Quote

Jim, not sure how to check that exactly.

The issue first appeared when I did not use styles but just chose the formatting details (colour, highlighting etc) by choosing the format cell functions – most of it were just simple colour highlights.

When i lost all formatting the first time, I switched to styles thinking this would solve the issue. I have created myself perhaps 8 new styles – the rest are all the default styles. Not sure why MS actually set these up as I find it awkward to scroll through this long list of styles I am not using. It’s easy enough to create a style when I need it, so why offer 200 odd default styles?

E.g. why do I have 23 buttons for Neutral named 2 to 21, highlighted in yellow and all apparently the same?

Reply | Quote

I just counted on a plain new workbook and there are no more than 42 predefined styles. ONE of them is Neutral. So your workbook is definitely “dirty” with styles.
You can right click entries in the styles dropdown to delete them.

Reply | Quote

Beethoven,

Just a couple of thoughts.

1. How are you saving the file? I would suggest always saving the file as an .xls file rather than converting it back and forth between .xls & .xlsx (2003 – 2007)

2. Is there something in the data that indicates the status, i.e. new, inactive, etc. If so you could simply apply a conditional format to the entire worksheet to do the row highlighting.

Hope this helps some.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

My free Excel add-in “Formats & Styles” will list/remove unused Styles in a workbook.
It can also handle custom number formats and conditional formats.
Some have been quite pleased with the results.
Download from… http://excelusergroup.org/media/p/4861.aspx

Reply | Quote

Jim,

I downloaded and installed your add-in but am a bit unsure how to use it. It does indeed show me more than 4000 unused custom styles but clicking the delete function, no unused custom files can be found.
What am I doing wrong?

Reply | Quote

Maybe…
The list of unused styles is displayed in a new workbook.
You have to switch back to your original workbook before running the program again.

If that is not the problem, I would very much like to get a copy of your workbook to see what is going on.
‘–
Jim Cone
( 30 + ways to sort )

Reply | Quote

Jim,
thanks again – I did not realise that the result is shown in a fresh independant spreadsheet. Now, doing it correctly, 4317 unused custom styles were deleted and only 54 styles were left.

This is much clearer for me and easier to choose when working with my spreadsheets. I am not sure why MS decided to include to so many options given that it is easy enough to create your own on demand.

Reply | Quote

I am not sure why MS decided to include to so many options given that it is easy enough to create your own on demand.

MS did not include all of the styles you have deleted, they were added to your workbook during copy/paste actions pasting in information from other workbooks.

Copying a worksheet from workbook A to workbook B can have a similar effect.

The fact that your workbook had so many styles proves that it has a very long editing history.

Reply | Quote

Copying a worksheet from workbook A to workbook B can have a similar effect.

The fact that your workbook had so many styles proves that it has a very long editing history.

Pieter, you are quite right – this particular worksheet has indeed a long history and I guess for some amateur like me it’s sometimes not obvious what effect highlighting one cell here or using a bold there may have for the overall sheet. I am glad that with Jim’s add-in I was able to cut through that.

Reply | Quote

You are welcome. Glad it helped.
For others interested, the link again is: Formats and Styles
(no registration required)
‘–
Jim Cone

Reply | Quote