Microsoft Excel

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: http://blogs.msdn.com/excel/archive/2005/10/21/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

Potentially missing option: user-specifiable default colors for worksheet and chart tabs.

Excel should automatically give worksheet and chart tabs *different* colors. Users could thus distinguish worksheets from charts without having to click each one. E.G. worksheet tabs could be bluish; chart tabs, reddish.

This would be easy to implement since Excel already lets users manually change tab colors. Users could, of course, manually override these colors on a tab-by-tab basis.

In re Francis's comments on worksheet tab colors, if this is a democracy (it isn't), my vote would counter his. There's no point coloring worksheet tabs differently by default unless there were a user option to PRE-SPECIFY the sequence of colors, in which case I could live with prespecifying grey for the entire sequence.

In re Francis's comments on worksheet tab colors, if this is a democracy (it isn't), my vote would counter his. There's no point coloring worksheet tabs differently by default unless there were a user option to PRE-SPECIFY the sequence of colors, in which case I could live with prespecifying grey for the entire sequence.

Nigel, here are some answers from the folks that designed the feature.

>>>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.<<<

As Harlan pointed out, you can multi-select the sheets you care about by ALT+click on the sheet tabs in the workbook.  We will then display ‘Multiple Selected Worksheets’ in the dropdown and you can apply your changes to those sheets.


>>>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.<<<

We didn’t distinguish this – it would have been too complicated to also divide settings based on Window-scope.  We chose to distinguish settings that are easier to understand the scope of – Application wide, workbook-specific, and worksheet-specific.


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

These settings are still there, but they are only visible if you have an RTL language (such as Arabic) enabled.  This matches the behavior of other Office applications such as Word.


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

This should be behaving the same way as #3, but looks broken in current builds ... we will follow up with the appropriate team about this as they own these settings.  Thanks for asking.

>>>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?<<<

Prompt for workbook properties has been deprecated.  Remove personal information from file properties on Save has been replaced with a new feature on the File menu (Prepare -> Inspect Document).

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

To a large degree - I believe it has been replaced in both File|New and Help, although it still exists in some applications in PowerPoint I believe.

>>>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.<<<

This has been deprecated.  There is still OM to turn off the status bar.

>>>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?<<<

The Office Web Components are no longer installed as part of Office.  More on that in a later post.

>>>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.<<<

This has been moved to the Trust Centre.

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

These options are deprecated with the new Chart architecture.

>>>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…<<<

The method of selecting your encryption algorithm moving forward is via group policy regkey. We received feedback from organizations that they did not want end users selecting encryption algorithms.


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

The digital signatures options are on the File|Prepare menu.  The Trust Center was designed to avoid wherever possible including options that are specific to a particular spreadsheet.

>>>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.<<<

We intentionally used the old color palette for these color pickers, because these colors of these features are not themeable.

>>>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?<<<

We have a doc we have written for the beta that I will post here in a week or two when it is finalized.

GaryK - that was something we spotted late.  I have made a note.

Biff - thanks for the feedback

Bill - we will post that list in a week or two when it is ready.

sysmod - no change to extend list formats and formulas, nor any other error checking options (what others would you like to see?)

Harlan - I will check it out

Nigel - I sent your problem to the test team to look at

Francis - thanks for the feedback

David,

|>>>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?<<<
|
|Prompt for workbook properties has been
|deprecated.  Remove personal information from
|file properties on Save has been replaced with
|a new feature on the File menu (Prepare ->
|Inspect Document).

Before I overreact to this, this is has been a workbook-level setting rather than an application-level setting. If one uses the wonderful new File > Prepare > Inspect menu command, will removing personal information if selected once remain a workbook setting no matter how many times the document is opened and saved, or will it be necessary to choose to remove user information on every save? If this, er, setting is persistent, why not treat it like a SETTING? And why anyone on the Excel development team thought ANY USER would think of finding this under File > Inspect > Document rather than on the Save settings tab is a testament to little thought given to peripheral features.

IMO, you screwed this one up, but Microsoft just doesn't get it about security/privacy.

|>>>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.<<<
|
|This has been deprecated.  There is still OM
|to turn off the status bar.

Another brilliant design decision! And how does this conform to earlier comments you've made about letting the USER control Excel?

'Write a macro!'

'Let them eat cake!'

|>>>12. …Neither am I able to find the 'Digital
|Signatures…' options anywhere in the new
|'Trust Centre Settings…' that used to be
|available.<<<
|
|The digital signatures options are on the
|File|Prepare menu.  The Trust Center was
|designed to avoid wherever possible including
|options that are specific to a particular
|spreadsheet.

So, basically, File > Prepare is really another Settings interface. So, 'prepare' for what? Printing? But there's a Page Layout tab on the ribbon. Just saving? Would seem so.


On a tangent, in one of the postings in Jensen Harris's blog he defended the ribbon by showing that it ate less vertical screen space than the standard menu bar plus two other toolbars. What he failed to consider is that some users don't use that many toolbars, and a few curmudgeons (like me) occasionally turn off the entire UI except for the row/column frame and the sheet tabs. There seems to be no equivalent for XL12 - the ribbon is always there whether it's wanted or not.

Where's the Prepare tab, button or other in

http://officeblogs.net/excel/1_05-22-2006.PNG

?

Is the Finish tab in that dialog image supposed to be the Prepare tab?

To this point in the blog, I've covered most of the new features that we've added to Excel 2007, but...

PingBack from http://softwareinformation.247blogging.info/microsoft-excel-toolsoptions-the-rest/