One of our goals with tables was to create a set of features that reduce the overall maintenance required to keep a spreadsheet functioning well over time. This involves making spreadsheets less prone to error, as well as making them more understandable days, months, and years after the spreadsheet was created. Rethinking the interaction between tables and formulas proved to be an important part of meeting that goal. As many readers have presaged, Excel 12 provides some new ways to reference tables...

One of the key benefits of tables is how other features in Excel 12 behave more predictably and more like you would expect when a table is present. This is made possible by the fact that Excel knows exactly where the table starts and ends, where the header row is, which cells make up the data and which columns they belong to, where the total row is, etc. So how exactly does this benefit the user? Here are some of the different ways in Excel’s awareness of the structure of your data changes the user...

For the next few posts, I’d like to spend some time explaining the work we’ve done in Excel 12 to improve the experience of working with tabular data in Excel. One thing that we see pretty much every Excel user doing with some frequency is working with tables. Tables can mean different things to different people so let me briefly define what we think of when we use the word table. A table is a simple structure where each row corresponds to a single “thing” (e.g. a specific transaction, an individual...

Some of you may have seen earlier today that Microsoft announced an investment in Business Intelligence across the Microsoft Office System with Excel 12 at the hub. You can watch the presentation and demo here . I have talked already about some of the Excel 12 features that support this investment, and I will be talking a lot more about others over the next few months. Specifically, over the next few weeks I will talk about: New work we have done to make working with tables a much better experience...

In a few of my posts, I reference something termed “usability studies” as information that we considered when making a decision. Jensen Harris has recently put a post on usability studies up on his Office UI blog on this subject, so you may want to take a look. We do a lot of studies on the Excel team too. One recent example was something we called a “PivotTable Longitudinal Study”. This basically means that we found around a dozen Excel “power users” internally at Microsoft (non-development-team...

Defined names are a very useful tool for authoring formulas. Defined names allow users to name cell ranges, formulas, and values and refer to those names in their formulas. Used in formulas, defined names make formulas easier to read and more robust. Additionally, when writing formulas, names are less likely to get mis-typed than cell references, and they are easier to remember than cell references (“Tax_Rate” as opposed to “G36”). In this article, I would like to discuss some of the work we’ve done...

In addition to improving the formula editing UI in Excel 12, the team has spent some time adding to Excel’s function library. Over the years, customers have found new ways to combine and leverage the functions in Excel to build all sorts of things, but there remain many areas where our customers would like to see need new capability. This release, we have targeted three areas in which to improve our function library – the Analysis ToolPak, SQL Server Analysis Services, and the most common requests...

Last post I covered improvements we made to a long-time fixture of the Excel UI - the formula bar. Today I’d like to introduce a feature that is brand new for Excel 12. The feature is called Formula AutoComplete, and it is designed to make users more efficient. Specifically, when we designed Formula AutoComplete, we had three goals: Help users build formulas faster Help users build formulas without needing to rely on external help Accomplish goals 1 and 2 in a way that is not intrusive or annoying...

I’d like to shift gears a bit and talk about the work we’ve done to improve the experience around building and editing formulas. For most customers, this is a core activity in their daily use of the product. In planning for this version of Excel, we took a hard look at the features in this area, and we have made what we think are some significant improvements. Over the next week, I am going to cover the work we have done in this area. To start, let's take a look at some changes to the formula bar...

Since I have had some comments and emails asking about how the new conditional formatting features could be accessed using VBA, I wanted to provide a few brief examples. One of the Excel team’s principles is that when we add new features, we make sure that they are available programmatically as well as in the user interface. The Excel 12 object model, accordingly, supports all conditional formatting functionality that is supported in the UI. This includes creating, editing, or deleting rules, or...