"Deadline-Beating Blueprints Trusted by Intermediate and Advanced Excel Users"

Excel 2007 review

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

New features and capabilities

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

Picture, Table, Pivot Table ribbon

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

Apply format quickly in Excel 2007

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

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


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

Excel 2007 Compatibility – Avoid Hair Pulling Frustration When Opening or Saving 2003-2007 Files

Excel 2007 compatibility issues occur due to a significant change in the underlying design of file formats and new features. Excel version transitions are problematic; Excel 95 to 2003, Excel 2003 to 2007 and counting.

It is particularly challenging  for developers and for users when there is a paradigm shift of this nature.

Excel Ribbon is that shift for the ‘Menu paradigm’ and also the Office Open XML file format in comparison to the Binary Interchange File Format (BIFF).

Here we will find practical solutions to the most common Excel 2007 compatibility issues…

Excel 2007 Compatibility Issue # 1 – I cannot open an Excel 2007 file in older versions

If your end users with whom you exchange files are still running an older Excel version and you want to send them files; you can do one of these things…

  • Save your Excel files as a previous version. Do it this way: Office Button>Save As>Other formats>Save as type>Excel 97-2003 Workbook
  • Set Excel 2007 to run on compatibility mode all the time. Do it this way: Office Button>Excel Options>Save>Save workbooks>Excel 97-2003 Workbook

If you are running Excel 2003 or an older version and you want to be able to receive files created on Excel 2007;  ask your file providers to use the above steps.

Another case is when you want to be able to open files in the new XML format. To be able to do it, you must install the Office 2007 Compatibility Pack. Go here (for instance, so you can see the formatting features that are unique to Office 2007).

By applying the above strategies, you will stop having unrecognized Excel files. You won’t experience the frustrating event when nobody in your surroundings has Excel 2007 to make the conversion easily. Additionally, you avoid causing struggle in your end users and customers. When people notice you care about them, you gain influence and recognition.

Now, let’s discuss the solutions when you have the…

Excel 2007 Compatibility issue # 2 – I can open the Excel 2007 file but I lost functionality

It is a natural process of new software versions that the ‘old version users’ community doesn’t disappear immediately (romantic users may remain with a version for the rest of their life);  so again, in the transition , struggle will exist.

The good news is that Excel 2007 alerts you about incompatible formatting or features that won’t load or load degraded in older versions…

Excel 2007 compatibility checker states… “The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in an earlier file format.”

Here is a very important difference worth talking about. You have two types of  loses…

Significant loss of functionality

These losses occur when one or more functions in your workbook are not available in earlier versions of Excel.

Examples: non existing function, unsupported nesting levels in a formula, etc.

Minor loss of fidelity

These losses occur when some cells or styles in your workbook contain formatting that is not supported by the selected file format.

For example: cell color, table styles, etc. These formats will be converted to the closest format available.

Here are some strategies to use so you make your files ready to be opened in Excel 2003 and older versions

1) Don’t use 2007 Functions

Excel 2003 cannot recognize Excel 2007  functions the same way Excel 2007 is not able to recognize the functions to come in future versions.

So it will be this formula =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) for Excel 2003. When recalculated in earlier versions, these functions will return a #NAME? error instead of their current results.

Here are some new formulas not supported: SUMIFS, COUNTIFS, AVERAGEIFS, etc.

2) Don’t use additional rows/columns

Data beyond 256 (IV) columns by 65,536 rows will not be saved.

And even worse…

Formula references to data in this region will return a #REF! error.

3) Don’t use unsupported format

Yes, the colors will be converted to the closest ones, but what looks good in Excel 2007 may not be reflected in your Excel 2003 end users’ screens. Be cautious when you use Colors, Styles, etc

Take a quick look at the color palettes supported by both versions:

4) Don’t use unsupported tools

Some features will not appear in the old version. This is a serious problem if you gain advantage from such features, your 2003 end users will have that advantage missing.

This is the case with Conditional formatting new features such as data bars, color scales, or icon sets.

5) Don’t use unsupported capabilities

I recently wrote a long IF formula in Excel 2007. The formula was developed for an Excel 2003 user; guess what? I saved it as 2003 format. I lost the formula I spent an entire morning working on. I got a #VALUE!

Excel 2003 converted this formula…


Into this one… #VALUE!

The fact is that some formulas have more levels of nesting than are supported by the selected file format. Formulas with more than seven levels of nesting will not be saved and will be converted to #VALUE! errors.

Here are some things you must be aware of…

  • 7 vs 64 nesting levels
  • 1,024 characters vs 8,192 character length of formula contents
  • 30 vs 255 arguments in a function

6) Run the file under compatibility mode and attach it to the recommendations or run the compatibility checker

When you are working in 2007  mode you may not be aware that you have inserted a lot of non supported features.  To avoid this, you should run the file in compatibility mode.

Do it this way: Office Button>Excel Options>Save>Save workbooks>Excel 97-2003 Workbook

If your file is run in 2007  format you must perform a check with the compatibility checker before you send the file to 2003 users.

Excel 2007 Compatibility conclusions

If your file doesn’t have extraneous features for Excel 2003 and if you save it in 2003 format, there is nothing to worry about, your end users will open the file easily as normal. Your file will be fully functional

Since you can install both versions, I recommend you do it; so you always check compatibility issues before sending an email or before losing hours of work like happened to me with unsupported nesting levels.