Excel 2010's conditional formatting feature is a super quick and easy way to find duplicate data in your spreadsheets.
Full article: Use conditional formatting to find duplicate values
Spreadsheet templates hold such promise. A good template can save you hours of time you would otherwise spend creating your own spreadsheet, and gives you the benefit of someone else's expertise. The sad fact though is that spreadsheet templates are fundamentally 'broken', and the concept of a good template is completely illusory.
Full article: A sad tale about spreadsheet templates
It's frustrating when Excel acts as if the active area of a worksheet is significantly larger than the actual area where you have data. Suddenly your scroll bars move you into uncharted areas, such as column
TX or row
In programming parlance, this is known as the "used range" of a worksheet. Fortunately, there's a quick and simple way to reset the used range when necessary.
Full article: Resetting the Last Cell in an Excel worksheet
FREQUENCY function was first created to calculate frequency distribution tables, which are needed for charting histograms. But the
COUNTIFS function offers more power, and it's easier to use.
Excel 2007 and Excel 2010 offers some new functions that go beyond the simple
COUNTIF as they allow you to test up to 127 conditions. The new functions are
AVERAGEIFS. (Excel 2003 does not offer
AVERAGEIF but Excel 2007 does). They are called the plurals due to the S at the end of the function.
Full article: SUMIFS
SUBSTITUTE function is an easy way to replace characters in an entry with something else. Here is an example of a cell with data entered...
Full article: Not available
As an Excel programmer I get called in to a lot of scenarios where spreadsheets have failed, crashed or have just become too unreliable to use.
In a recent study by the University of Hawaii, it was discovered that over 80% of the world's spreadsheets contain errors. 70% of those errors affected results that were used in making business decisions.
After speaking to several other Excel programmers, I have compiled a list of the top reasons why spreadsheets fail to live up to a business's expectations.
Full article: 5 reasons your spreadsheets fail
I've set up a table with some team names that I want to use in a Data Validation list. The reason I formatted my list in an Excel table is because I want the range to dynamically update when I add or remove teams from the list.
Now, if you've ever tried to reference an Excel Table as your Data Validation lists... then you probably got the error: "The formula you typed contains and error". Here are two ways to fix this...
Full article: Excel Tables as source for data validation lists
Advanced Filters have a couple of advantages over regular filters:
- The filter criteria is contained on your worksheet so you can easily see what filters are applied. Whereas with AutoFilter you have to click on the down arrow to see what filters have been applied.
- Advanced Filters allow you to extract your filtered data to another place in your worksheet/workbook or extract a unique list of records.
Their downside is it's not as obvious that your data is filtered since they don't display drop-down lists for the column headers like regular filters.
Full article: Excel advanced filters