Tools|Options – The Rest

The most frequent thing I find myself doing with Tools > Options is UNsetting Page Breaks in the View tab. If XL12 has the same annoying habit of ignoring my stated preferences and displaying the @#$% page breaks every time I run Print Preview. (Is this a bug? Or has it been around so long it's become a 'feature'?) And now it'll be MUCH MORE OF A HASSLE to UNset the @#$% thing in XL12. Guess I know the first macro I'll write for XL12.

Since you have display options for the active worksheet in the Advanced tab, what's in the View tab on the ribbon?

On a tangent, if transition formula evaluation is a worksheet-level setting, why is 1904 date system a workBOOK-level setting?

The Add-Ins tab shows Analysis ToolPak. Since you've mentioned that the former ATP add-in functions have become built-in functions in XL12, what's left, the DATA Analysis ToolPak stuff, i.e., Tools > Data Analysis... ?

As for the Trust Center tab, where's the equivalents for the Digital Signatures button and Remove personal information from this file on save checkbox from XL10's Security tab? It looks like this tab contains nothing that shouldn't instead be in the help files. There is that Trust Center Settings tab, but you've made it look like a no-no.

The Resources tab also looks like a fugitive from the help files.

So where's the equivalent of XL10's Right-to-left section in the International tab, under the Language Settings... button in the Personalize tab? Where's Prompt for workbook properties in XL10's General tab? Where's the equivalent for Accept labels in formulas from XL10's Calculation tab? Where are the equivalents for the Formula Bar and Status Bar checkboxes in the Show section of the View tab in XL10? In the ribbon's View tab?

Then there's the matter of the 'advanced' calculation and threading settings under the Advanced tab rather than accessed by maybe an Advanced Calculation Settings button on the Formula tab. Why? You littered many of the other tabs with buttons to access other settings you must have believed you couldn't squeeze into the top-level tabs. Threading may not be changed often, but as long as Macs and PC have different default date systems, 1900 vs 1904 date system setting will need to be accessed all too frequently.

There doesn't seem to be much consistency of design here. Kinda looks like it was slapped together just before the release deadline. And a row of tabs at the top of the dialog in prior versions doesn't eat screen width like the tabs in the new dialog do. Very poorly conceived. I'll be generous and assume this was driven by the Word team since Word's Options dialog was attrocious. But that leads to another mystery. Word's Options dialog is narrower than Excel's, so what prevented the Word team from widening its Options dialog so that the tabs could fit on just two rows? There seems to be a lot of cooperation between all the Office apps development teams when it comes to the new eyewash 'features', but a lot of Not Invented Here syndrome when it comes to older features.

I'm glad that you guys followed MacBU's lead in getting rid of the old horizontal tabs in favor of the verticle tab pane.  Much less jarring for the user when switching tabs. :-)

First of all, this is great. Now I only need to remember one place for all the options (probably still need to fish around for a year or so before I will get used to the new place but that's worthwhile learning curve).

A couple of questions:

1. Dave you mentioned a long while ago answering one of my question about giving user ability to auto-enable macros when coming from certain trusted authors. Is that something in the "trust center"? maybe I am jumping ahead.

2. For other viewers of this blog as well: what do you think making the old naming dialog (Insert>Name) available in "Formulas" options?

3. And while on the topic of names, any advancement on names management? for example, an audit report of current names with properties like: sheet/workbook level, validity, etc; also, I've longed for a way of massive editting the names, e.g., multi-select a sheet level names already defined and transfer directly over to a new workbook/sheet without having to copy the sheet over, etc, etc. Any update on that front will be much appreciated.

David,

A few things on the new 2007 Options vs 2003 Options:

1.  I like the way you can now see and pick options relevant to either the Workbook and Worksheets.  However, for worksheet options I think it would be useful if you could see and select the workbook object and have those settings applied to each sheet in the workbook or an (All Sheets) option.

2.  Along the same vane it would be useful if there was some way of distinguishing between those settings that are Application wide and those that are Window wide.

3.  I echo Harlan's observations that the 'Right to left' section on the 2003 'International' tab is no longer available.

4.  Similarly, on the 2003 options 'Spelling' tab there is a section dealing with 'Language Specific' issues that I can find nowhere.

5.  Again, Harlan has already pointed out the apparently missing 'Prompt for workbook properties' item that used to be on the 'General' tab , but there is another similar option 'Remove personal information from file properties on save' on the 2003 'Security' tab that is no longer there.  What has happened to the properties of the file?

6.  Could you confirm that the dreaded Task pane is a thing of the past?

7.  I have not been able to find a Status bar on/off setting as used to be in the 'View' tab.  In 2007 it does disappear in Full Screen mode so it is possible.

8.  The old 'Web Options…' available on the 'General' tab used to generate a 'Web Options' dialog that had an item on the subsequent 'Files' tab that enabled 'Download Office Web Components' that no longer appears.  Is this because it is now done automatically as part of the updates?

9.  'Service Options…' on the old 'General' tab also had an 'Online Content' option available within it that I no longer see which used to provide access to templates, media etc when one was online.

10. I am not able to find the 'Active chart' options that were available on the 'Chart' tab when you had a chart selected.

11. The 'Advanced…' button that used to be available on the 'Security' tab that prompted one to pick an encryption type doesn't appear to be anywhere…

12. …Neither am I able to find the 'Digital Signatures…' options anywhere in the new 'Trust Centre Settings…' that used to be available.

13. In the new 2007 options for things like gridlines and error indications one can pick a colour.  The palette available is the awful old one and not the new themed colour palette which is so much better.  For the sake of consistency I hope this can be changed.

14. Finally, could you cover converting and backward compatibility issues sometime covering?  One that comes to mind in the new options area is under the 'Save' section is the 'Choose what colour will be seen in previous versions of Excel' item and how will this apply an old palette in the new all singing all dancing colour context?

And that's enough from me for the moment.  Thanks again for a great blog,

Nigel

Nigel, re changing worksheet-level settings for all sheets at once: did you try grouping the worksheets before changing settings for the active worksheet? Now it only it were as easy to protect multiple worksheets at once by grouping.

Tianwei, if this were a democracy (it isn't), then I'd vote the other way for any link to defined names from any setting tab, even the one for Formula settings. I think it was a mistake to use the name Formula rather than Calculation, but it's for settings. Defining names isn't changing settings any more than changing cell contents is changing settings.

I would like to see an "Options for this workbook" tab that would consolidate all the options that apply to a workbook and not the application. It would make the separation clearer, and it would also make it easier to find out what settings are configurable on a workbook level.

Thanks for the comments folks.

Tianwei - yes, that's the trust centre.  See this post for name management improvements: blogs.msdn.com/.../483661.aspx

Nigel, Harlan, I will check into the International and Spelling items, as well as the other items on the list.

Nigel, could you clarify which dreaded taskpane?  Also, yes, compatibility is something I will cover at some point.  Thanks.

why is there no browse button on the save tab for the deafault file location? there is one in word, but no other office apps. i mentioned this in one of my comments months ago, and i'll say it again,

"i sure hope you make all of the option tabs exactly the same in every program, because they surely weren't in previous versions of office."

there needs to be a browse button just like the word option tab.

I like this idea from Nigel:

2.  Along the same vane it would be useful if there was some way of distinguishing between those settings that are Application wide and those that are Window wide.

On the Resouces tab, although there isn't a whole lot there, I think its easier to read and associate the option descrptions if they were on the same line as the option title.

Instead of this:

get updates

______________________________________________

Get the latest updates available for Microsoft Office

This:

get updates - Get the latest updates available for Microsoft Office

How about including links and/or info about the MS Excel newsgroups on the Resources tab?

The ng's are a resource!

Or is that already included in Register for online services?

Harlan asked about Accept Labels in Formulas.

Natural Language Formulas are being depreciated in Excel 2007.

Dave - it might be cool to share the complete depreciation list in a post.

I echo the request to clearly distinguish settings for

- application

- session

- workbook

- worksheet

- window

One confusing point for many users is that in Excel, the setting in the Tools > Options > Calculation tab is an application setting rather than a workbook setting (as it is in OpenOffice). Once you turn on manual calculation, it applies to all subsequent work in Excel until you turn it off again.

It is set by the first workbook opened in a session which may be personal.xls

Is Extend List Formats and Formulas unchanged?

Are there any new error checking options?

I've just d/l the beta and hope to look at it soon.

Thanks,

Patrick

To Bill Jelen,

Thank you for the info on labels and natural language references. I suppose the new tables address this in part. A full deprecation list would be a VERY USEFUL THING.

So, does this mean workbooks using labels in formulas will need to be rewritten to work under XL12? If so, it's high time Microsoft consider adding a function like 123's @XINDEX which operates on a range assumed to have row labels in the left column and column labels across the top row, and entries below the top row and to the right of the left column are addressed by row and column labels.

David,

By the 'dreaded taskpane' I meant the dockable New, Help etc taskpane object that appeared when one selected View/Task Pane.  This often had some sort of memory leak that manifested itself by displaying the area it covered not refreshing and as there was no handle on it in VBA the only way to get rid of the grey block was to got to View/Task Pane and turn it on and then turn it off manually.

On another matter; is there a place where one can send observed problems to?  For instance I have come across a problem when using the Worksheet.Move method.  The line:

   Worksheets(x).Move Before:=Worksheets(z)

...moves Sheet(x) to position Sheet(z+1) instead of Sheet(z-1) if before the 'Move' attempt the existing Sheet(z-1) had its 'Visible' property set to xlSheetHidden or xlSheetVeryHidden.

Thanks,

Nigel