Welcome to MSDN Blogs Sign in | Join | Help

Excel Performance – New Whitepaper Available

In a recent post, I mentioned there were more Excel 2007 whitepapers coming.  Today a whitepaper was released on Improving Performance in Excel 2007.  Here is the summary from the MSDN site:

Summary: Learn about the increased worksheet capacity in Microsoft Office Excel 2007 and techniques that you can use as you design and create worksheets to improve calculation performance. (40 printed pages)

The article was written by Charles Williams of Decision Models Limited.  Charles is an expert in this area, so there is a lot of good information.  Also, while the article is written about Excel 2007, some of the information applies to current versions of Excel as well.  Enjoy.

Back Already - Updated XLL Paper on MSDN

On September 19 I announced Steve Dalton's article on Developing XLLs in Excel 2007, and posted a draft. This article has now been edited and finalized, and resides in its new home on MSDN - take a look here.

Posted by David Gainer | 6 Comments
Filed under:

PWRDWNSYS

Just over a year ago, I started this blog with the goal of documenting the new features in Excel 2007 and Excel Services. My plan was to write this content until around the general availability of Office 2007. After 163 posts, ~1,900 comments, and ~1,800 emails, this goal has pretty much been met – while I am sure there are a few small items that have slipped by, I am pretty sure that myself or another team member has written about every large- or medium-sized feature or change in Excel 2007. Along the way, I have met a huge number of users in the Excel community (several of whom I have already visited here in Redmond or when out on the road), seen numerous examples of interesting spreadsheets, received a ton of feature requests, heard a lot of useful feedback, and received a lot of help with calculation performance tuning. I have also had a lot of fun – I was not sure how things were going to turn out when I started this exercise last year, but the whole thing has surpassed my hopes in pretty much every area. Thanks to everyone that read, commented, and sent me email, and thanks to everyone that helped out with files and product issues. I really do appreciate everyone’s help.

Given that the blog’s original mandate – thoroughly documenting Excel 2007 for the Excel community - has been met, I am going to slow down my activity for the time being. I still have a few more forward-looking topics suggested by readers I hope to get to, there are more whitepapers coming to talk about, the team will probably want to post some more articles, and I am going to try and figure out a way to involve the community in the next version of the product (to the extent I can come up with a feasible model for doing that). That said, starting today, I will not be churning out posts at the rate I have been, so I thought I would let everyone know - perhaps a few a month opposed to a few a week.  We will see. 

Again, a huge thanks to everyone that has read and participated so far … Dave.

Your Turn One More Time – Limits

When we started Excel 2007, we made a decision that as part of increasing the grid size, we were going to address a lot of important other “limits” in the product.  The entire list can be seen here, in the second post I ever made to this blog.  (Note, this is probably a good place to note that we had to back out the change to “The number of characters that can be stored and displayed in a cell formatted as Text” recently – we found a problem with the implementation that couldn’t be fixed at this late date, so we are going to address it next time.)

When we developed the list of limits to address, we approached the research a few different ways.  We tried to identify anything that would prevent users from using the “big grid” to its fullest.  We tried to address top customer requests, whether they came from our support team, the newsgroups, web searches, and the like.  We talked to our large enterprise customers and developers.  We went through old support records.  Etc.  Having done that, and made a lot of changes in Excel 2007, we have already started to identify candidates for the next version – for example, the number of discontinuous ranges that can be selected, the number of categories shown in the function dialog, etc.

What else would you like to see us work on in this area?  Are there any limits that you run into frequently that you think we should try and increase or eliminate in the next version of Excel?

Excel 2007 Viewer

Over the past few months, I have had a number of questions about the Excel 2007 viewer – mostly, will there be an Excel 2007 viewer, and when will it be ready.  (For those that are not familiar with the term “viewer”, viewers are applications that Microsoft provides for free that enable people who do not have Office programs to open and look at Office files.  There are viewers available for Word, PowerPoint, and Excel, and they work with files created in any version of Office between Office 97 and Office 2003.  You can get a copy of the Excel viewer here.)

The answer is that there will be an Excel 2007 viewer, and it should be available shortly after we ship Office 2007.  It will not be available right after we ship, since we are pretty busy working on the application itself, but it should be shortly thereafter.

Posted by David Gainer | 5 Comments

Your Turn Again – Data Validation

The other night at home, I was doing some work at home, I ran across a situation where I wished that our “Data Validation” functionality and “Pick From List” were integrated – specifically, it would have been really nice if, after I set up Data Validation on a range, Excel recognized when I was typing an entry from the list of valid possible entries.

Here’s an example.  Say I was categorizing some expenses in this Table, and I have set up Data Validation on the last column to make sure I do not mis-categorize any expenses.


If I start typing an entry that is already present above or below the cell I am currently editing (I think Excel looks 25 rows above and below or something like that), like “B” in this case, Excel fills in the rest of the word with the part I didn’t type highlighted in black.  If that’s the word I want, I just have to press Enter, and I am off to the races.  Fewer keystrokes, faster data entry.


However, if I start typing something that is defined in Data Validation as a valid entry, but is not already typed (“Dogs” in this case), I don’t get the highlighting, and I have to type in the entire value before pressing enter.


This could be a bit smoother.  Many of the Data Validation Lists I define have a lot of items (30-50) that can be multiple words, so it would be really handy to get Pick-From-List auto complete – it would make things faster (less typing) and easier (since I wouldn’t have to remember every word of all 50, just the first words).

That led to my next thought about Data Validation – after you type an entry, if the entry is invalid, it erases everything you typed.  For example, if a valid entry was “Home Improvements” and you typed “Home Improvement” and pressed Enter, Excel would alert you that the value was not valid and then delete everything in the cell, leaving you to have to re-type those characters.  It would be nice to be able to just press Cancel and type an “s” instead.

So, while I am at it, here are a number of other things we have heard about Data Validation over the years:

  • It would be nice to have controll over whether things like Copy/Paste, CTRL+Enter, and Fill Down over-wrote data validation.
  • It should be possible to specify data types for the range having Data Validation applied.
  • It should be possible to specify unique values only in a range (rank everything 1-20 …).
  • Two columns can be linked in hierarchical lists (if I choose WA in column 1, only cities in WA should appear in the Data Validation list in column 2).
  • Have an option to sort entries in the drop down.
  • Allow me to reference cells on another sheet without resorting to hacks.
  • Better user control over the error message shown.

So now it is your turn.  What would you like to see added/changed/done to Data Validation?

Excel 2007 & Internet Explorer

Today we have a guest post from Andy Tischaefer.  Andy works on the Office Programmability team.  Andy is going to talk a bit about a change in behaviour using Office 2007 and how you can turn the clock back if you want to.

In prior versions of Office (since Office2000 at least), Office documents (ppt, xls, doc and others) navigated to in Internet Explorer would open with the application hosted inside of IE, like this:


Note – for the purposes of discussion in this blog, I am using Excel in my examples, but everything I am going to cover is true for all Office applications.

Many developers used this behavior to create a more seamless interaction between their web applications and the data produced by said application, for example by creating a spreadsheet on the fly and then telling IE to navigate to that spreadsheet.  However, this also caused confusion for users who wanted to actually work with documents from the web (internet or intranet) since the full Excel UI isn’t available and it’s hard to tell if you’re in IE or in Excel.  In Office 2007, we have changed the default behavior to not open inside IE, that is to say when navigating to the Office document, the appropriate Office application will open and load the document.  This should provide for a more consistent user experience but may lead to some behavior changes in your corporation’s applications.  Your choices are to change your application’s expected behavior to account for Office documents loading in the Office apps rather than IE, or to revert your users to the earlier hosted behavior.

You can revert this behavior in one of two ways.  One, you can change things through the Windows UI.  In WindowsXP, open an explorer window and open Tools / Folder Options.  Switch to the File Types tab and navigate to the document type you wish to open in IE.  In this case, I’ve picked .XLS:


From here, click Advanced, and then check the “Browse in Same Window” checkbox (highlighted in red in the picture below).


Checking this box will revert you to the behavior you saw in previous versions.  Note that you must do this for each document type you want to browse to in IE.

Another way to revert behavior is to change your “BrowserFlags” registry key for the appropriate file format to 0.  In the example below, I’m updating the key for .XLS:

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Excel.Sheet.8]

"BrowserFlags"=dword:00000000

For those of you with applications that rely on the old hosting behavior, setting these keys during your application’s setup is probably the easiest way to ensure a smooth transition when upgrading to Office2007.

Posted by David Gainer | 4 Comments
Filed under:

Using Parameters In Dashboards

Today we have a guest post from Dan Parish, who is a program manager on the Excel Services team.  Dan is going to explain a bit more on how to use Excel Services to set up dashboards that are driven by parameters.

Back in the original posts regarding Excel Services, Dave mentioned that Excel Services is targeted at consumers and explorers of workbooks.  As such, Excel Services doesn't allow you to simply type into any cell or create new workbooks on the fly.  Dave also mentioned that we do understand that in some scenarios some cells need to be edited, and this post discussed the idea of 'parameters', those being specifically marked cells that can be edited on the server.  Let’s take a closer look.

Why use parameters?

In the firts public beta, we only allowed you to mark single cell named ranges that were either blank, or contained a value (i.e. no formulas) as parameters.  With the most recent beta build, however, we also added in the ability to mark PivotTable Report Filters as parameters.  Since PivotTable and OLAP Formulas are the only way to refresh external data with Excel Services this release, this allows you to be able to drive your external data queries using web parts other than the Excel Services web part (called Excel Web Access or EWA for short).

What are some examples of where this would be useful?  Well, let's say that you are creating a dashboard and want to display several different charts of data related to a stock chosen by the user.  You can create these charts in Excel and display them using Excel Services, but you probably wouldn't want the user to have to select the stock in each web part separately. Instead you'd want the user to be able to pick the stock once, and have all of the other web parts update.

Using SharePoint's Choice or SQL Server Filter, and Excel Services, you can do just that.  Another example might be that you have a report that you want to filter based on the region a particular SharePoint user is in.  Using SharePoint's Current User filter and Excel Services, you can do that too.  The possibilities really are varied and numerous.

Creating a dashboard using Excel Services with Parameters and SharePoint Filters

So now I want to provide a bit of a walkthrough as to how you can create one of these dashboards yourself.  In this case I have a simple workbook that contains a PivotTable of all my store's sales data, and an associated PivotChart.  I want to put both of these in a dashboard and have them be filtered by one central control that contains a list of the items I sell, so that if I select one or more items from the list, both the PivotTable and PivotChart will update to show me sales information related only to the selected items.

Creating the workbook and using a Data Connection Library

The first step is obviously to create the workbook. I have all of my data stored in an OLAP database, and I've exported my ODC file to a new document library in SharePoint this release called a Data Connection Library (DCL).  This blog has talked about DCL's before, but at a high level a DCL is a document library specifically made to store Excel and InfoPath external data connection files.  By storing my ODC file here and referencing it directly from my workbook, I can have Excel Services also use it and by simply changing the ODC file in this one location, I can update the connection information automatically for every other workbook that uses it as well.

Defining a PivotTable Report Filter Parameter

Once I've got my workbook created, I need to define a parameter.  In this case, I want to filter my PivotTable Report Filter that contains the list of items. Parameters have to be named ranges, so I first give a name to the PivotTable Report Filter (the actual cell that contains the filter).  Now, to publish the workbook to the server and define a parameter at the same time, I choose Office Button > Publish > Excel Services.  On the dialog that appears I can select where I want to save my workbook, and then I can click the Excel Services Options button.  Here there is a Parameters tab where I can add in the named range that I just created as a parameter.  Only named ranges that are valid to become parameters will show up as possible choices here.


(Click to enlarge)

Laying out the dashboard page

Once I've created my parameter and saved the workbook to the server, all I need to do is setup my dashboard the way that I want it to look.  I can do all of this in my web browser without writing a line of code: it’s all point and click.  In this case, I've added two Excel Web Access web parts, and one SharePoint SQL Server Filter control.


(Click to enlarge)

Configuring the Excel Web Access web parts

First I'm going to setup the two EWA web parts.  When I click the link within the leftmost EWA web part to open the tool pane, I can then either enter the path to the workbook that I just saved directly, or I can click on the "..." to the right of the text box to launch a file picker and select the file that way.  I can also specify the name of the object that I want to display.  I simply do this for both EWA web parts specifying 'PivotTable1' to display for the first, and 'Chart 1' to display for the second.


(Click to enlarge)

Configuring the SQL Server filter

The next thing I need to do is to configure the SQL Server filter.  To do that, I click the link to open its configuration tool pane, and then browse to the same ODC file that my Excel workbook is using.  Automatically, the filter displays all of the dimensions in the cube.  If I select the same dimension my Report Filter is using, I'll see all of the hierarchies in that dimension.  After I do that, I can then select the same level that the Report Filter is using, give the web part a display name, and I'm good to go.


(Click to enlarge)

Connecting the filter control to the EWA web parts

The final thing that I need to do is to connect the SQL Server filter to each of my EWA web parts.  To do that, I select the “edit” dropdown at the top right of the filter control, and then select Connections > Send Filter Values To > (name of my EWA web part).  A dialog will pop up, and if I select “Get Filter Values From” and then click Configure, I'll see a list of all of the parameters in the workbook that the EWA is currently displaying. I then select the parameter I want to send the filter value into, and click OK.  That's it.  I do the same for the second EWA and I'm done. Note that you can also configure connections going the other way, by selecting Connections > Get Filter Values From on the EWA web parts.

Now, if I select a different item in the filter control, both of my EWA web parts will update.


(Click to enlarge)

This is just a simple example.  SharePoint ships with many different filter controls including ones that can pass the current user or any known properties about them, one that can take parameters from the query string and pass them in, one that lets you enter a hard coded list of items filter (which is a great way to do data validation with Excel Services), and many more.  This example was just one way that you can incorporate the business logic that you have in Excel into a dashboard and really integrate it into the whole experience.

New Word Blog

The folks on the Word team have asked me to let you all know they have set up a new team blog which you can read here: http://blogs.msdn.com/microsoft_office_word/default.aspx.  Check it out and follow along if you are interested.
Posted by David Gainer | 0 Comments

Another Whitepaper - Developing UDFs for Excel 2007 and Excel Services

Danny Khen is back (Danny wrote a number of posts on programmability topics over the past year) with a white paper on developing UDFs for Excel client and server.

In a few past posts, we discussed investments in user-defined functions (UDFs) in Excel 2007, and we showed (here, here and here) how UDFs can be used in an Excel solution that runs on both Excel 2007 and the new Excel Services. I now gathered all this information and elaborated some more, resulting in an article and an extensive set of code samples that we intend to publish on MSDN (I don’t have the precise date yet). I’d like to share a draft of this document and the code samples with you today. It essentially contains information about all the major paths you may take when developing an Excel client/server solution with UDFs: utilizing existing UDF code; having a core native function library; and basing your solution on managed functions. I hope you find this useful. I will appreciate any feedback of course – here in comments or through the email link.

Here is a link to the whitepaper.  And here is a link to the samples.

Just like with the draft XLL article that we posted on September 19th, please note that this one too is a draft, and the content may change for the final publication on MSDN.

Posted by David Gainer | 8 Comments
Filed under:

Your Turn - Drawing Tools

The team that builds the drawing tools in Office (sometimes referred to as OfficeArt) is interested in understanding all the different ways people are using the OfficeArt drawing tools, images, and audio/video with Excel.  While a lot of the usage is well-understood (on charts as callouts, as controls on the grid, as backgrouds in an application, as navigational items, etc.), we run into people doing all sorts of interesting things, so we thought it would be interesting to hear from blog readers on all the ways they use the drawing tools, images, etc.  We are also interested in how people are using VBA with drawing shapes.  For those of you that are game, please give us a brief description of what you’re doing, and let us know if it’s ok to contact you with follow-up questions sometime in the future.  Files are welcome too – you can send them to xlfiles@microsoft.com.

 

One other thing that is also interesting is anything that you cannot do today that you would like to be able to do (i.e. extending support for formulas, more object model, etc.).

Conditional Formatting Trick 3 – The percentmin Property

Folks that have been using data bars (see here for more information) in Excel 2007 sometimes bump into a situation where the size of the bar painted by Excel on the smallest value in the dataset seems too big.  An example will probably help.  Take a look at this fake data and accompanying data bars:


The size of the data bar for the last value – 170 – is too big given the relative size of 170 to the other numbers in the range (hundreds of thousands).  Why would that be – the data bar should technically be 0 pixels wide?  The answer is that when we were doing usability testing of this area in Excel, we found that users preferred not to see blank data bars, so Excel’s default was set to a 10% minimum width.  While there is no UI to tweak that setting, there is an OM property – percentmin – that you can use to set the minimum width to whatever you like.  Accordingly, if I select the range, open VBE and type “activecell.formatconditions(1).percentmin = 1” in the immediate window, I see this:


There are probably other interesting uses for this property … for example, I have seen someone write a bit of VBA to always make sure there was at least one pixel showing in this sort of situation, but the threshold was dynamic, not set to a fixed value like 10%.

Developing XLLs in Excel 2007

Back in January I talked about updates we have made to XLLs.  We will soon be publishing an extensive article on MSDN which will cover Excel 2007 features that affect XLL add-ins and enable new XLL functionality, as well as changes to the XLL C API itself.  The article was written by Steve Dalton (author of Excel Add-in Development in C/C++: Applications in Finance).

Today I’d like to give you a preview of this very useful document - you can find the document on MSDN here.  Also, if you want to experiment with XLLs in the most recent beta, it will be useful to have xlcall.h and xlcall32.lib … send me an email using the “email” link on the top left of the page if you would like me to send you a copy of these files.

Posted by David Gainer | 11 Comments
Filed under:

Question For The UDF Authors Out There

Specifically, we’re interested in add-ins that create custom Function Groups (Function Groups show up in the Function Wizard, next to the built-in groups like All, Financial, Date & Time, Statistical, etc.), and add-ins that are delay-loaded.  For anyone that reads this in the next few days that creates add-ins, we would love to know:

  • Do your addin's create function groups?
  • Are your addin's delay-loaded? (we think this is pretty uncommon, but getting confirmation/contradiction of that would be great)
  • Do you have add-ins that both create function groups and are delay-loaded?

Thanks for your help.

Ribbon Shots

OK, for those interested, here are shots of the ribbon as they shipped in the beta that was released yesterday.  These are more or less finalized – there may be a few tweaks between now and when we are done, but not many. 


There are both XP and Vista versions (thanks Dan!), and I have grouped them by area.  The XP versions are at a resolution of 1024x768, while the Vista versions are at 1600x1200 – this should give you a good example of how the ribbon resizes for different resolutions.

 

Default Tabs

Home – Windows XPWindows Vista

Insert – Windows XPWindows Vista

Page Layout – Windows XPWindows Vista

Formulas – Windows XPWindows Vista

Data – Windows XPWindows Vista

Review – Windows XPWindows Vista

View – Windows XPWindows Vista

Developer (can be toggled on and off) – Windows XPWindows Vista

 

Chart Tabs (Contextual)

Design – Windows XPWindows Vista

Layout – Windows XPWindows Vista

Format – Windows XPWindows Vista

 

PivotTable  Tabs (Contextual)

Options – Windows XPWindows Vista

Design – Windows XPWindows Vista

 

Other Tabs (Contextual)

Table Tools – Windows XP Windows Vista

Drawing Tools – Windows XPWindows Vista

Picture Tools – Windows XPWindows Vista

Header Footer – Windows XPWindows Vista

 

SmartArt Tabs (Contextual)

Design – Windows XPWindows Vista

Format – Windows XPWindows Vista

Posted by David Gainer | 3 Comments
Filed under:
More Posts Next page »