August 2009 - The Excel Team Blog

Today’s author is Bob Umlas, Excel MVP and author of This isn’t Excel, it’s Magic, a book that is filled with tips and tricks for getting the most out of Excel. This post shows you how to quickly remove zero values and blank cells from a list that contains values. Here’s a formula which enables you to access a list of values interspersed with zeros or blanks and it will pick up only the non-zero values in the same sequence they’re listed. It’s better to illustrate. Suppose you have this list in A1...

Thanks to Chad Rothschiller for helping me put this post together. We’ve spent the last few weeks talking about improvements to the various visualization features in Excel. For the next few posts, I am going to cover some “under the hood” type improvements, things like performance, internal limits, and the like. For this post, I will focus on the work we did to make a 64-bit version of Excel. 64-bit Excel, simply put, is for building really really (really) big workbooks. 64-bit Excel is for those...

When you open workbooks downloaded from the Internet in Excel 2010, you’ll enter into the new Protected View . While in this mode, Excel runs in a “sandbox,” which means that you are now better isolated from any malicious code that could be contained within the file. In the past, you had to decide if you trusted the author/origin of the document before viewing the contents. Now, with Protected View, you can make a more informed decision before exposing your computer to potential vulnerabilities....

Thanks to Ben Rampson for putting this post together. In today’s article I will outline some of the significant new chart improvements that impact performance. In Office 2007 the graphics engine used by Office Charts was replaced with one that could support more complex rendering. A downside of this change was slower performance in certain scenarios relative to Office 2003. For example, supporting anti-aliasing in Office 2007 allows the chart to render smooth lines; however, the computation for smoothing...

Over the next three posts this blog will cover some of the charting changes that have been made in Office 2010, many of which are the direct result of customer feedback. As part of the Office 2007 release we overhauled charting throughout Office; however an OM for Word and PowerPoint was not provided. In response to your feedback, in Office 2007 SP2 and Office 2010 we’ve exposed a unified/consistent charting OM within Word and PowerPoint, so that you can write solutions against any of the applications...

New features in conditional formatting extend to the object model as well. Excel has traditionally allowed users to access the formatting of their cells through the object model. For example, Users can use the interior class which lives off the range object to access the formatting of the cell. Selection.Interior.Color returns the background color of the cell. However, Selection.Interior.Color does not take into account the conditional formatting applied to the cell. In Excel 2010, we’ve introduced...

Data Bars are a popular type of conditional formatting that was introduced in Excel 2007. The basic idea of data bars is that Excel draws a bar in each cell whose length corresponds to the value of the cell relative to the other cells in the selected range. Data bars are a great way of seeing trends in your data. In our example, we have the historical Sales and Profit amounts for a fictional bookstore. The last column has the percentage of increase in profits when compared to the previous year. We...