MrExcel.com - Your One Stop for Excel Tips & Solutions

Preview of what Microsoft is Revealing about Excel 12

December 24, 2005

The next version of Excel is due out in September, 2006. Microsoft has been incredibly forthcoming about the new features in Office 12. I've been monitoring several blogs by employees of Microsoft.

  • All of the screenshots shown in today's episode are from Dave Gainer's Blog. Dave is the lead project manager for Excel.
  • Jensen Harris has a great blog to talk about the new user interface in all of Office

More Rows

The first bit of big news in Dave's blog is that the Excel grid is getting significantly larger. The number of rows is going from 65,536 to 1,048,576 (that is 2^20). The number of columns is going from 256 to 16,384 (2^14). This is an amazing increase in the size of the grid. Currently, an Excel worksheet has about 16 million cells.

In Excel 12, an Excel worksheet will have 17 billion cells. That is a staggering increase. To visualize this, if the green box is the size of Excel 12 grid, the yellow box is the size of the current Excel 2003 grid.

Improved Charting

In Excel 2003, say that you wanted to make a chart from data like this.

You might be used to the rather boring looking chart produced by Excel. It has looked like this since Excel 95.

Here, Dave shows the same chart in Excel 12:

You will have options to control glow, bezel, metallic, theme colors and more.

Here is another comparison of a current chart (top) and Dave's preview of the new charting engine.

New Data Bar Functionality

I love this new feature called the Data Bar. Say that you have a column of values. Select the column and apply a Data Bar to the range. Excel will draw in tiny in-cell bar charts showing the relative size of each value. This makes it very easy to spot the higher and lower values.

Quick Sum

There are many more major features, but there are minor improvements as well. Most people don't know about the quicksum feature in Excel. This is where you highlight a range of numeric cells in the worksheet as shown here.

The quicksum appears in the status bar at the bottom of the worksheet. You can right-click to make it show a Sum, Min, Max, Count, etc.

In Excel 12, this functionality has been greatly improved, showing several statistics all at once.

In all, the improvements in Excel 12 are massive. If you read Dave's blog, you will learn about amazing improvements in Conditional Formatting, Pivot Tables, an Excel Server product. However, there is one "Gotcha" that is making people nervous. The familiar menu bar of File - Edit - View - Insert - Format is … gone.

The Problems with the Current User Interface

The current Excel User Interface is comprised of 9 main menu items, plus 42 icons on the Standard and Formatting toolbar. There are also right-click menus that can appear and 17 additional toolbars. In short, the current user interface is a confusing mess.

With somewhere around 300 current menu items, new features end up getting buried deep in the menu system. For example, the Evaluate Formula command is one of the most powerful new features in Excel 2003, but how many people reading this article ever found it buried deep on the Tools menu?

The legend says that Microsoft has meetings with current Office users and asks them what new features they would like to see. The most common response always ends up being something that has already been in Excel since 1997, but no one could find the feature.

Consider the toolbar icons. Do you really know what all 47 of them do? I'll be honest - I am MrExcel and even I don't know what some of them do. If I was really bored, I could hover the mouse over all 47 of them, but if you are using Excel, odds are that you are never that bored, since we have work to get done. Hey - look - you don't have to use Ctrl+K to insert a hyperlink, you could use this icon.

And - who knew that PasteSpecial was on the toolbar, but hidden behind this dropdown?

So - the bottom line - there is a lot of stuff in the current Excel and no one has the time to find it.

The New User Interface

In the new user interface, the menubar, standard toolbar and formatting toolbar is replaced by "The Ribbon".

The ribbon contains six tabs; Sheet, Insert, Page Layout, Formulas, Data, Review. New tabs may appear depending on what you are doing in Excel. Each tab contains commands that are specific to a group of tasks. The beautiful thing about the commands is that they appear as icons and words. You don't have to hover in order to guess what the icon does.

Since I love Pivot Tables, the example that I pulled from Dave's blog is what happens when you have a pivot table on the worksheet. Two new tabs appear that are specific to pivot tables. On the first tab, you have a lot of commands specific to pivot tables. Most of these features have been in Excel, but you had to be a guru to find them.

On the second tab, there are many options for formatting the pivot table. Some of these are new, but it will be much easier to find any of them without having to use the old toolbar, then a dropdown to find the options dialog.

Of course, if you liked the old Pivot Table Options dialog, you can always display the legacy version of the dialog. Heck - even the way to access the old Options dialog is easier than the two-step process required in Excel 97 through 2003.

The new user interface also has a Quick Access Toolbar (with a cool fade-in/fade-out effect like Outlook 2003's new message notifier). The buttons on the toolbar feature a "live preview" functionality - hover over a style and you will see it temporarily applied to your spreadsheet.

My Take

Excel 12 will be massively better than Excel 2003. Does everyone need 1.1 million rows? Well, no - but a heck of a lot of people need 72,000 rows. Just the other day, Quickbooks gave me this ridiculous CSV file with 500 columns of data. I only needed 100 of those columns, but it sure was nice being able to open the file in a beta of Excel 12, delete the unwanted columns, then save it back for use in Excel 2003.

The changes in the user interface will be a minor speed bump for 400 million existing Excellers. I have to think about where things are in Excel 12.

There will initially be some frustration as we get used to the ribbon. (If you are an Excel pro and know that Alt+EIJ stands for Edit - Fill - Justify, then Microsoft has a compatibility mode for you.)

If you are a keyboard person, you will appreciate that every command on the ribbon can be accessed from the keyboard, not just some of them.

The Bottom Line:

This is the best new version of Excel released since Excel 97. I've often complained that the level of innovation in Excel dwindled after Lotus 1-2-3 was no longer a threat. Microsoft had done an amazing job with the new version. There will be some grumbling as we try to learn the new user interface. I expect a couple of days of confusion, but I will quickly get used to where things are. I've always said that white collar productivity would skyrocket if people really knew how to use more than 5% of Excel. This new version will expose many of Excel's powerful features. Personally, I can not wait for this version to show up.