| A lot is being said about the Excel inefficiency to | | | | - Playing with data |
| store and query data, I have noticed that these | | | | - Reconciliation of data |
| comments almost always come from database | | | | - Matching data The purpose of these Excel |
| developers, BI industry guys, or from people who | | | | functions and formulas is to return values from |
| push Excel to the boundaries of dealing with large | | | | Excel databases (flat files) to cells in Excel reports |
| amount of data. | | | | and analyzes. |
| Excel can help you with your reports... | | | | Excel disposes of more than 300 functions. You |
| It is true that Excel is great as a reporting | | | | need only 10 of them to create reports. |
| front-end for its extensive statistical/scientific | | | | |
| function library but it is not a great tool for bulk | | | | 1. VLOOKUP and VLOOKUP + Helper Column |
| data manipulation or finding patterns in your data. | | | | 2. INDEX and MATCH |
| But we are talking here about flat files that | | | | 3. Array Formulas |
| resides in your workbook, tables that usually | | | | 4. SUMPRODUCT |
| come from a complex query made by a robust | | | | 5. SUMIF |
| database software. | | | | 6. SUMIFS |
| You can use Excel to find patterns in data and | | | | 7. COUNTIF |
| then present the information as usable and | | | | 8. COUNTIFS |
| perceptive reports. | | | | 9. AVERAGEIF |
| "Both Excel and Access are good tools for finding | | | | 10. AVERAGEIFS Of course you need other |
| patterns in data. But they work with different | | | | formulas, but these ones are the building blocks, |
| types of data and help users find different types | | | | for example you may need to compute the |
| of patterns using different analytical and | | | | standard deviation of the returned-value or sum |
| presentation methods." | | | | various returned-values. |
| - Charley Kyd - Here are some kind of operations | | | | Conclusion |
| you can do using Excel pure formulas: | | | | Grasp these formulas and you will get a boost in |
| - Filling tables with data of other tables | | | | your reporting skills, you soon will get a boost in |
| - Gathering/merging information of different | | | | your Data presentation abilities. |
| tables | | | | "...you can become a data god who cans lookup, |
| - Comparing tables | | | | manipulate and analyze any spreadsheet by |
| - Finding patterns | | | | learning few Excel Formulas. Pointy Haired Dilbert" |
| - Finding exceptions | | | | - Chandoo.org |
| - Comparing trends | | | | I find the use of lookup formulas inside another |
| - Monitoring progress | | | | formulas the most valuable aspect of its practical |
| - Consolidating measures | | | | use. |