Browse by Tags

Harness the power of the suite of Text functions, such as LEFT, RIGHT, MID, FIND, LEN, TEXT, and REPLACE, and you'll be an unstoppable force. In this post, experiment with these functions and build your skills in finding, extracting, and changing text in worksheet cells. ...( read more ) Read More...

Ever wonder where the character count option is in Excel? It doesn't exist, at least not in the same sense as Word's word count feature. However, Excel does have a nifty little function, LEN, that lets you quickly count the number of characters in a cell. Check it out! ...( read more ) Read More...

Have you ever wanted to subtract one date from another? Or find out how many workdays until vacation? Or maybe you need to figure out a project end date. You can do all of this and more by using date functions in Excel formulas. As long as you type a date in a format that Excel recognizes: 8/22/2011, 22-August-2011, or August 22, 2011, for example, Excel will interpret the date as a serial number, and you can use formulas to find important dates. For example, you can then use formulas to subtract...

A few weeks back I wrote several posts on the improvements we have made to Excel’s function library. I’d now like to share with you a draft of a whitepaper we are planning to release when Excel 2010 ships. The whitepaper describes all of the improvements we are making. It also includes detailed descriptions of the new algorithms used. The whitepaper contains much of what I have included in my previous posts; however, it also contains some more detailed information, especially in regards to the function...

The second part of the function improvements work we are making for Excel 2010 is to create a more consistent function library. We have implemented a new naming scheme to allow consistent and accurate naming convention for functions. Also, we have added new functions to the function library to round out our function set. New functions will take advantage of the new and more accurate algorithms. Consistent Function Names To be explicitly clear about what our functions do, we have implemented a new...

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

Today’s author is Bob Umlas, an Excel MVP since 1994. This tip is a formula which enables you to access a list of values interspersed with zeros or blanks and 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:A14: …and you want to produce this list: The following formula entered in E1 and filled down to E6 does the trick. It’s an array formula which means that you must press Ctrl+Shift+Enter after entering the formula...

Today’s author, Monica Poinescu, a Software Developer in Test on the Excel team, discusses two different approaches to analyzing data in Excel. My earlier blog on the new Excel 2007 function SUMIFS spawned a very interesting discussion (thanks to everyone who posted comments there): when trying to analyze/aggregate data in a table, how do we decide whether to use functions versus PivotTables? This blog outlines reasons to use one option or another. To better illustrate the two alternatives I’ll consider...