Excel 2007 / 2010 – What’s New?

Learn more about the new features of Microsoft Excel 2010 and what are the things to look out for in Excel 2007/2010. Excel 2007 and Excel 2010 are two different version of Excel but they have a similar interface. What you can do with Excel 2007, can also be done in Excel 2010. In fact, you can do more with Excel 2010.

New file extensions for Excel 2007 and 2010

Excel 2007/2010 files are saved with the new xlsx Extension. Workbooks used by version 2003 and below are stored with the xls extension. The new extension also offers the benefits of smaller file size for the same amount of data stored in the older version. In the new version, files with macros are stored with the extension xlsm. The “m” I believe represent macros. In the older version of Excel, there is noo differentiation between files with or without macros.

What happens if you are using 2003 and somebody send you a file that was created in 2007/2010? Other than asking them to save the file in a lower version or spend money to upgrade to the new version, there is another option which will cost you nothing. Download this office converter from Microsoft Website

Creating pdf files using Excel 2007/2010

To create a pdf file in previous version of Excel, we need to make use of the acrobat writer which will set you back by a few hundreds dollars. Now, with Office 2007/2010, you don’t even need to pay a single cent to create a pdf document. If you cannot find it in your version of Excel 2007 or 2010, download and install this add-in from Microsoft®’s website and you are on your way to create a pdf file, simply by saving your excel file in pdf format.

Auto filters

The improvement done to Excel 2007 and Excel 2010 is the ability to group the dates in the dropdown list into years and months. That means you don’t have to create new columns to identify the years and months of each row using Excel formulas.

In addition, you can now select multiple values in the dropdown list for filtering.

Instead of scanning through row and rows of records to identify those that are coloured with a certain color, you can filter the records by colour and have auto filter present you with all the selected coloured records.

In Excel 2010, you are also given a search box where you can use to quickly find the list of description or value that matches the searched word or phrase. With the result, you can then select the values you wish to filter.

CustomiZed Ribbons / Menu for Excel 2010 only

Ribbons (or more commonly known as Tabs) were introduced in Excel 2007. It was a new interface for all Excel users and cannot be customized by a normal Excel users. Although there is another section for Quick Access Toolbar which works like older versions of Excel but in reduced scales, it is unlike the toolbars in Excel 2003.

In Excel 2010, the improvement over 2007 was the ability to customize your own Ribbon (i.e. Tab) and put your frequently use commands in one place. In this way you do not have to click from one Ribbon (or Tab) to another to find your commonly used command. In my Excel 2010, I have a new customized Ribbon called Jason that stores all the commands I use frequently. See picture below:

To find out how to set it up, click on this Ribbon and Quick Access Toolbar link.

Sorting

In the earlier vesion of Excel (2003 and below), sorting can only be done based on the values or text in the cells. The improved Excel 2007 and 2010 allows you to sort the records based on the colour formats of the cells. Not only that, you can also sort the records by the font colour.

Multiple Conditional Count

Multiple conditional count is now made easy with Excel 2007 and 2010. In the older Excel versions 2003 and below, we are able to use COUNTIF to count the number of cells based on one single condition. To work on more than one condition, we would have to form another column and combine all the conditions together. Now, all we need is to mark out all the conditions within the formula and bola! the count is computed for us.

Multiple Conditional Sum

When I was using Excel 2003, multiple conditional sum has to be done using the SUMPRODUCT formula or by combining all the conditions into one before applying the SUMIF formula to the range. With the new Excel 2007, you can now easily sum up the values based on more than one condition by using the SUMIFs formula.

New Averageif Formula in Excel 2007/2010

In the older versions of Excel, we have to calculate the average of a range of numbers based on condition using a combination of sumif divided by countif. In Excel 2007, you can calculate the average with a new single averageif formula. The inputs is the same as that for sumif “=AVERAGEIF( Range, Criteria, Average Range)“. In fact, you could calculate average based on more than one condition with the new averageifs formula.

The Averageifs is entered differently from the averageif formula. It starts with the range of value to calculate the average, following by the range for the 1st criteria, the criteria, criteria2_range, criteria2…..) i.e.

=Averageifs(Average_Range, Criteria1_range, Criteria1, Criteria2_range, Criteria2…)

Beware of Conditional Formatting in Excel 2007

Conditional formatting in 2007 is filled with bugs. I found 2 so far and have another one from one of my visitors. You can read more about it in my conditional formatting page.

Colours in Excel 2007/2010

Excel 2007/2010 has 16 millions colours, much more than the 56 colors in Excel 2003 and below.

Click here to see the difference between excel formulas and functions