Microsoft Excel 2007

                 

Enhancements to Excel

1. Increased the total number of available columns in Excel from 256 (2^8) to 16,000 (2^14).
    

2. Increased the total number of available rows in Excel from 64,000 (2^16) to 1,000,000 (2^20).
    

3. Increased the total amount of PC memory that Excel can use from 1GB to the maximum allowed by Windows.
    

4. Increased the number of rows allowed in a PivotTable from 64k to 1,000,000 (2^20).
    

5. Increased the amount of memory that Excel can use from 1GB to the maximum allowed by Windows.
    

6. Eliminated the limit on the number of rows of a column or columns that can be referred to in an array formula.
    

7. Increased the number of conditional format conditions on a cell from 3 conditions to limited by available memory.
    

8. Increased the number of levels of sorting on a range or table from 3 to 64.
    

9. Increased the number of items shown in the Auto-Filter dropdown from 1,000 to 10,000.
    

10. Increased the total number of characters that can display in a cell by 32-fold. More specifically, this limit was increased from 1k (when the text is formatted) to 32k or unlimited (regardless of formatting).
     

11. Increased the number of characters per cell that Excel can print from 1k to 32k.
     

12. Increased the total number of unique cell styles in a workbook (combinations of all cell formatting) from 4,000 to 64,000.
     

13. Increased the maximum length of formulas (in characters) from 1,000 characters to 8,000 characters.
     

14. Increased the number of levels of nesting that Excel allows in formulas from 7 to 64.
     

15. Increased the maximum number of arguments to a function from 30 to 255.
     

16. Increased the maximum number of items found by “Find All” from 65,472 to 2 Billion.
     

17. Increased the number of rows allowed in a Pivot Table from 64,000 to 1,000,000. Increased the number of columns allowed in a Pivot Table from 255 to 16,000. Increased maximum number of unique items within a single Pivot Field from 32,000 to 1,000,000. Increased the number of fields (as seen in the field list) that a single PivotTable can have from 255 to 16,000.
     

18. Increased length of the MDX name for a Pivot Table item; also the string length for a relational Pivot Table from 255 characters to 32,000.
     

19. Increased the length at which fields’ labels are truncated when added to PivotTable; this also includes caption length limitations from 255 to 32,000.
     

20. Increased the number of cells that may depend on a single area before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations) from 8,000 to limited by available memory.
     

21. Increased the number of different areas in a sheet that may have dependencies before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations) from 64,000 to limited by available memory.
     

22. Increased the number of array formulas in a worksheet that can refer to another (given) worksheet from 65,000 to limited by available memory.
     

23. Increased the number of categories that custom functions can be bucketed into from 32 to 255.
     

24. Increased the number of characters that may be updated in a non-resident external workbook reference from 255 to 32,000.
     

25. Increased the number of rows of a column or columns that can be referred to in an array formula from 65,335 to no limitation.

  26.  Improved Tables – Better Looking, Better Behaved – The brand new table feature in Excel 2007 called “table     
        styles” makes it possible to add formatting with one click.  Table styles is a feature that provides a way to quickly
        format my entire table using a preset style definition.  It is similar in concept to the AutoFormat feature Excel had in
        the past, with a couple key differences:

·         Table styles are dynamic, not a one-time formatting operation, so the formatting associated with the style behaves intelligently through many table actions – addition or deletion of rows or columns, sort, filter, etc.

·         There will be a healthy variety available.  Excel 2007 will ship with 30-40 table styles out of the box, and users will be able to vary the colors used by the style, so users will have a couple hundred styles available with one or two clicks of the mouse.  If none of the built-in styles tickle your fancy or suit your presentation, you will be able to create your own custom styles which are sharable with others.

·         The UI for applying styles – galleries in the ribbon – is a big step forward.  This is a great example of the kinds of benefit the ribbon brings to Excel.

·         They are part of a broader set of work that includes cell styles, chart styles, PivotTable styles, and document themes. But the goal of all this work is to make it fast and easy to create professional-quality, consistent-looking documents.

Table styles are easy to apply – all you need to do is create a table, and a default table style is applied as soon as a table is created (don’t worry though, it doesn’t remove your existing formatting).  If you don’t like the table style, you can pick something different or remove it altogether.  The gallery of table styles is available directly from the table style ribbon.  By default, we show you a single row of styles (the number depends on the resolution of your screen – more resolution, more space for styles).

If you want to see other styles, you can either scroll, or click on the drop-down to see all the styles available. 

As you hover over each table style with the mouse, Excel displays a preview of what the table looks like with that style.  The preview appears directly on the table in my spreadsheet – it is not a separate image.  This is another of the user interface advancements we have made with the new ribbon feature in Office.  If you like the way the preview looks, simply click on it to apply it to the table. 

 

To summarize, one of the benefits of this feature is that professional-quality formatting is one click away.  The fun is only starting, however.  Once a table style is applied, it will grow and shrink with the table as the table changes.  Additionally, certain elements of table style formatting have other intelligent behaviors.  For example, if a table style specifies what we call “banded row” formatting, meaning that every odd row be one color and every even row be another color, then that banding rule will always apply no matter what you do to the table.  This means you can add and delete rows and your table still appears properly banded.  You can sort and filter a table and your rows will still appear properly banded.  (Note – filtering is one place where the technique of using “=MOD(ROW(), 2)" in a conditional formatting rule to generate bands doesn’t work – since filtering hides rows, the banding effect is destroyed.)  For example, if your table looks like this before filtering ...

Before Filter

... and we filter out all even-numbered rows, here is what your table will look like after we filter it:

After Filter

Table styles have several different options that can be turned on and off, and all of them have similar intelligent behaviors.  For example, a table style might define that the first column should always appear a certain way.  If the “first column” option is enabled, then Excel will always make sure the first column is formatted appropriately no matter which column is in the first position.  You can see the other table style options in the screenshot below – header and total rows, first and last columns, and banding on either rows or columns.  You can toggle these on and off for each table style.

A common question related to displaying table styles is how it relates to “direct” (user-applied) formatting and conditional formatting.  If a table style is applied, you are still free to format your table using either conditional or direct formatting.  Formatting that you directly apply to a table will always appear “above” formatting that is defined by a table style, and conditional formatting will always appear “above” direct, or user-applied formatting (bold for example).

PivotTable Enhancements - One of the nice exploration features of PivotTables is the ability to expand and collapse items in order to view values at different levels of detail.  In Excel 2007, Microsoft has added expand/collapse indicators to the PivotTable to make it easy to discover when there are more details to explore (and to make it obvious that this feature even exists!). The expand indicator is a “+” and the collapse indicator is a “-”. Let’s look at an example. In the PivotTable below, the user has added three fields to the row area and the sales amount field to the values area.  Currently only items of the first field, year, are displayed. To display the details below 2001, click the expand indicator:

 

 

Now we are looking at the sales amount for each product category in 2001:

 

 

To go to a lower level of detail, we can expand mountain bikes as well and we get the sales amount for each bike model:

 

 

Note that we are now at the lowest level of my “hierarchy”, so there are no expand or collapse indicators.  The indicators do not print by default, and they can be turned off altogether once you are done exploring the data and are getting ready to present the result.

 

Compact Axis - The PivotTables in Excel 2007 look more “compact” than a PivotTable in current Excel versions.  Probably the easiest way to explain this is with a few pictures.  Here is an Excel 2007 PivotTable with three fields on the row axis.

 

 

And here is the same PivotTable in Excel 2003.

 

 

To significantly improve the readability of PivotTables, Microsoft added a new layout option for displaying items in the row area, which is referred to as “compact”.  In the Excel 2007 screenshot above, you will notice that items from all of the three different fields in the row area are displayed in a single column.  To distinguish between items from different fields, Mountain Bikes is indented under 2001 and the individual mountain bike models are indented even further under Mountain Bikes.  One of the key benefits of this feature is that PivotTable row labels take up far less room on your screen, so that there is much more room for your numbers. This compact form is the new default layout for PivotTables in Excel 2007.  That said, we have provided three different “row area layout options” to choose from.  The layout settings can be controlled for each field individually but it is very easy to set them for all fields at once.  This is done in the Report Layout drop down on the PivotTable Styles tab.

 

 

In addition to the compact form that we have already looked at, the tabular form displays one column per field displayed and leaves space for field headers.  Here is what the tabular form looks like for the same PivotTable – much like current versions of Excel.

 

 

The outline layout is very similar to tabular except that you can have subtotals at the top of every group, since items in the next column are displayed on the rows below the current item. To illustrate the difference, the screenshot below shows outline form where Mountain Bikes is one row below 2001:

 

 

As the screenshots above illustrate, the great advantage of the new compact form is that the PivotTable utilizes space a lot better, making it much easier to read. Tabular and outline form include a lot of white space making the report wider and the result is that the values are pushed out of view in many cases.

 

PivotTable Styles - Table styles provide a way to quickly format entire tables using a preset style definition.  They are dynamic, meaning as your data change the style is re-applied smartly. The UI for applying table styles is very visual and easy so that out-of-the box people will be able to create presentation-level quality. The good news is that Microsoft has done the same for PivotTables.  In Excel 2007, Microsoft has added PivotTable styles, which are another important part of our work to make PivotTables easier to read and understand.  In the same way as table styles, the PivotTable UI offer styles in a gallery.

 

 

Clicking a style in the style gallery will immediately apply the style to the entire PivotTable. Below are two examples of PivotTable styles. The first example is a style that highlights the top part of the report while formatting everything below similarly:

 

 

The next example demonstrates that you can make each group stand out to make it easier to find subtotals in the report.  In this example, 2001 and Mountain Bikes are in bold text since they represent subtotals whereas the individual mountain bike models are in regular text since they are at the lowest level of detail.

 

 

Excel 2007 will come with a large set of predefined PivotTable styles that you can pick and choose from.  In addition, just like table styles, you can create your own styles that fit your specific needs whether that might be corporate guidelines or individual preferences.  PivotTables, however, are more complex than tables, so there are more table elements available for users to define formatting on.  For example, you can define formatting for multiple levels of subtotals, you can define striping at different levels in the PivotTable. 

 

 

Microsoft believes that users will really enjoy this feature – once a style has been applied to a PivotTable, the PivotTable continues to look good through sorts, filters, pivots, addition or removal of fields, etc.

- END -