Solve Almost All your Excel Reporting Needs

Hi,

I will share an important Excel Reporting fact today. So, pay attention…

Creating Excel reports for managers and customers from flat files the whole day, requires your concentration and creativity. Hopefully, you are equipped with the easiest and most flexible Data analysis software available in the market, Excel.

I owned the software too, but I was not able to use the relevant tools for my reporting assignments.

That’s not all…

I spent hours trying to build the formula that will extract the data or trying to find the direct formulas to do it. At times; I felt exhausted, since Excel has more than 300 functions. After years of trial and error attempts; I know which formulas are the relevant ones to create Excel Reports.

Don’t quit reading now! I will show them to you now…

  1. VLOOKUP It allows you to search a value in a backend index column and to retrieve the corresponding value from other column. It only searches values on the left and retrieves values to the right. For example: search the Product Code and return the Product Price
  2. INDEX &MATCH It has the same VLOOKUP functionality but it is not limited to search only the left column
  3. Array Formulas It allows you to search multiple columns and complex computations. For example: search the Orders made by Susan in August and return the total
  4. SUMPRODUCT It is a kind of Array Formula but more readable
  5. SUMIF It allows you to sum various lines that match one condition. For example: sum all the Sales made by George
  6. SUMIFS It allows you to sum various lines that match multiple conditions, these conditions are evaluated in multiple columns. For example: sum all the Sales made by George in August
  7. COUNTIF It allows you to count various lines that match one condition. For example: count all the Yes Answers
  8. COUNTIFS It allows you to count various lines that match multiple conditions, these conditions are evaluated in multiple columns. For example: count all the Yes Answers of Female respondents
  9. AVERAGEIF It allows you to average various lines that match one condition. For example: average all the Sales made by George
  10. AVERAGEIFS It allows you to average various lines that match multiple conditions, these conditions are evaluated in multiple columns. For example: average all the Sales made by George in August

Once more thing – it’s important…

By using the above formulas you will be able to create and customize any advanced Excel report; no matter the questions, no matter the style of presentation. Where you have a cell, it could be an answer.

Conclusion.

The most important reason I use Lookup formulas is because I can present only the information that is expected by end-users and I can control the cell inputs and see the changes instantly. This is not a land of Pivot Tables.

All the best

John Franco

“Boost your Excel skills and you’ll boost your career”

P.S. The amount of reports you need to prepare won’t decrease; even they will increase, so be prepared to produce them more efficiently.