Here are the top 10 features in Excel according to you:
- Excel formulas.
- VBA, macros & automation.
- Pivot tables.
- Lookup formulas.
- Excel charts.
- Sorting & filtering data.
- Conditional formatting.
- Drop down validation & form controls.
- Excel tables & structural references.
- PowerPivot, data explorer & data analysis features.
Full article: Learn top 10 Excel features
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 5000.
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
Excel's 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.
Full article: Use COUNTIFS, not FREQUENCY, to calculate frequency distribution tables for charting histograms
Excel 2007 and Excel 2010 offers some new functions that go beyond the simple SUMIF and COUNTIF as they allow you to test up to 127 conditions. The new functions are SUMIFS, COUNTIFS and 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
The 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
