Being able to remove duplicate rows of information from a table of data is a request we hear fairly often from our customers (and one of the top questions in the comments in this blog). Some users know that this capability exists in Excel today; unfortunately it is buried under advanced filter settings and it’s not terribly easy to use. So we set out in Excel 12 to build a better interface specifically for this task so that any user could easily remove unnecessary data from their spreadsheet. Remove...

One of the things I mentioned in my overview of Excel 12 post in September was that we had done some work to speed up calculation on modern hardware (multi-processor or dual-core chips). I thought I would take a brief break from tables to summarize that work and to see if any readers are interested in beta testing this feature. Multi-Threaded Calculation To a large number of customers, Excel’s calculation speed is extremely important – perhaps the most important “feature” we ship. When planning Excel...

I mentioned in my previous post that the three-condition limit on sorting (Data|Sort) has been removed in Excel 12. As someone correctly pointed out in a comment many posts ago, this means the current sort dialog has changed in Excel 12. Here’s what the new dialog will look like (as always, all the details aren’t finalized, but the key features are clear) if you were in the middle of trying to sort a table by 5 columns: (Click to enlarge) The dialog is similar in behaviour to the Conditional Formatting...

Sorting and filtering are two of the most important types of basic analysis that you can do with data. In Excel 12, we have improved sort and filter functionality to better expose common tasks, to make key tasks simple, and to enable scenarios that were not possible in earlier versions. We have done work in AutoFilter, our sort functionality (Data|Sort), and in PivotTables. I will cover the first two (AutoFilter and Sort) in posts this week and PivotTables in a few weeks when I review all our PivotTable...

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