Are you an Excel expert who has developed a romance with old Excel GUI: custom toolbars, menus, etc?

For you it may have been difficult to find things in the new Excel 2007 Ribbon; but user interface is just one area of productivity to consider. What about after the weeks you took to re-learn the new look-and-feel?

The benefits of new features are worth considering even the initial displeasure. Microsoft Excel 2007 is a major rewrite so it is justified to wait a lot from it.

Let’s review the new characteristics that make it better than Excel 2003…

1. Manage/Analyze your Data Faster with Significant Improvements that Turn Your List into a Central Command

The automatic detection of data region (for sort and filter) was a kind of mystical Excel capability; this feature has evolved to Excel Table. In consequence, forget annoying things like: inserting a column/row to exclude adjacent data, subtotal row included in the sort/filter process, etc.

Focus on your data instead. Here you have some of the benefits:

• Sort intuitively by field, you now dispose of sort dialog in each field
• Sort after filtering or vice versa. Split tables intuitively by field. Sort and Filter are now together.
• Clean duplicate data in place with new delete duplicates command. Choose the field by which you want to delete dups
• Don’t worry about calculating subtotals:
  • Hidden rows are never considered
  • Total row is independent from sort and filter
  • Cells are not excluded after adding rows
  • Don’t spend time writing functions in the total row. Now you have an integrated set of subtotals functions at hand (Count, Average, Max, Var, etc)
• Filter discrete items. Use check boxes to select or deselect items
• Spend less time configuring common filter criteria with predefined filters: for numbers(top 10, above average), for dates (yesterday, tomorrow, this week, next quarter, etc)
• Filter dates discretely and get oriented in time with hierarchies in dates filters. Dates are group by year and month
• Sort/filter by format (cell color, font color)
• Don’t lose data when opening big files. Manage bigger data sets with more rows and columns (1,048,576 rows by 16,384 columns)
• Sort deeper with up to 64 levels of sorting
• Filter by selection by using the right click contextual menu
• Focus on data analysis as you add data. Excel Table AutoExpansion capabilities automatically resize the table when you add adjacent data (columns and rows)
• Don’t exclude data beyond the 1000th row

Read: 18 List Features of Excel 2007 Worth Using Today

That’s not all; there are new small table features that make difference, see below.

2. Boost Your Productivity when Working with Tables

“If you think you are too small to be effective, you have never been in bed with a mosquito.” – Betty Reese

Take a look at these small enhancements that make a difference:

• Get oriented through a table after vertical scrolling (without freezing panes), the header fields remain on top (see graphic 1, a)
• Get oriented when you sort text, numbers or dates with contextual descriptions (seegraphic 1, b):
  • Sort oldest to newest (for dates)
  • Sort A to Z (for texts)
  • Sort smallest to largest (for numbers)
• Know instantly the filter criteria applied to each field through Filter Icon anddescription (see graphic 1, c)
• Get subtotal information quickly (count, average, max, min, sum) with an eyeball atthe Status Bar (see graphic 1, d). Now you can select more than one function
• Avoid retyping already-entered-data with Auto fill (now it works between blanks)

Read: Excel Table – You will Hate Yourself if You are not Using this Excel 2007 Command

Graphic 1

3. Stop Clustering Toolbars with Contextual Ribbons

Custom Toolbars match your workflow but they have some problems: they populate your Excel interface, they are sometimes not loaded when you open Excel and they remain floating still after you finish working.

In Excel 2007 no more clustering of custom toolbars with on demand Ribbons. You dispose of:

• Format ribbon for pictures (see graphic 2)
• Options and design ribbons for Pivot Tables
• Design ribbon for Tables

Ribbons have been criticized by power users because it hurts productivity. The lower efficiency is the result of: re-learning curve of the location of commands and toolbars and the inability to customize ribbon bars.

Read: A Belated Review of Excel 2007

Graphic 2

4. Write/Debug/Read Formulas Easier than Ever with Formula Autocomplete, Name Manager and More

The logic is yours and it will always be but now you have some enhancements that will make your life easier, see below…

• Don’t guess anymore when picking arguments (blocked cells or unknown cells because a blocked column header due to a flooding formula bar). Formula bar content is automatically wrapped
• Get oriented as you write/debug formulas, enter functions, arguments, constants and names with the new tool Formula Autocomplete (see graphic 3)
• Read/write longer formulas with the new resizable Formula Bar
• Work with more functions arguments, now you can use 255 instead of 64
• Write longer formulas because content length passed from 1,024 characters of previous version  to 8,192 characters
• Manage names more efficiently with the new Name manager (see graphic 3). Now you can:
  • Delete multiple names at once
  • Assign comments to each name
  • See relevant information of names in a pane. The pane shows the following info: name, value, refers to, scope and comments
  • Query Names based on scope, names with errors, table names

Read: Excel 2007 Formulas – What’s in It for Me?

Graphic 3

5. Take Decisions more Visually

In Excel 2007 you can format your data for both: analytical and presentation purposes. You can combine this visual flags with filter and sort commands to get new insights of your data. You can create business dashboards also.

You dispose of:

• Gradient colors
• Data bars, and
• Icon sets

6. Reduce File Size, Improve Speed and File Format Security

Excel 2007 has new capabilities:

• Memory management passed from 1 GB in Excel 2003 to 2 GB in Excel 2007.
• Dual-processors and multithreaded chipsets to perform faster calculations in large,formula-intensive

Excel 2007 introduces the new file formats known as the Office Open XML formats. They will:

• Facilitate integration with external data sources, and
• Offer reduced file sizes and improved data recovery.

7. Create a Professional Look Quicker

You dispose of several ways to format your data quickly. Excel brings predefined styles but you can create your owns.

A style is a set of colors, fonts, lines, and fills effects that can be later be applied to items. You have:

• Table styles
• Cell styles
• Shape styles
• WordArt styles
• Chart styles
• Pivot table styles

See graphic 4 below…

Additionally, you can work with page headers, footers, and margin settings right in the worksheet. Printing page Layout View and Page Layout Tab features offer you a WYSYWYG printing experience.

Graphic 4

Excel 2007 Offers You even More…

• Better Charting experience for new users

Read this article: Changes to Charting in Excel 2007

• Easier to use Pivot Tables
• A versatile color palette
• 14 new functions: IFERROR, SUMIFS, COUNTIFS, AVERAGEIF, AVERAGEIFS, CUBEKPIMEMBER, CUBEMEMBER, CUBEMEMBERPROPERTY, CUBERANKEDMEMBER, CUBESET, CUBESETCOUNT, CUBEVALUE, EOMONTH, XNPV

Important notice:

According to Excel 2007 section “Calculation specifications and limits” the “Number of available worksheet functions” is 341 (12 more than Excel 2003 set which is 329)

• Zoom buttons at the status bar

More articles: Excel 2007 Compatibility – Avoid Hair Pulling Frustrastion When Opening or Saving 2003-2007 Files, Excel 2007 – 4 Things I Love about the Hated Ribbon

Well, this article is for highlighting the pros but Excel 2007 brings problems/lacks too, here you have them…

Problems/Lacks of Excel 2007

Here are some problems/lacks:

• Sparklines
• True algebraic debugging tool for formulas
• The Excel Ribbon replaces the classic menus, not good for old users
• Custom toolbars not allowed (just the quick access toolbar and ribbons not changeable)
• Better deleting duplicates capabilities

Conclusion

It is true that Excel 2007 brings new features that will boost your productivity. It is also true that fundamentals remain the same.

The best way to get the most of the software is by learning the building blocks that doesn’t change version after version: cell referencing, formula writing and nesting, filtering data, etc