Are there any limitations of pivot table in financial analysis? What are they?

Answer 1

Not sure how to answer that question. If you are building a pivot table, you typically want your data to be organized in rows and columns, with unique field headers. The pivot table will return a compact version of your data aggregated by the fields you choose in the fields section (rows, columns, filters) - that you can also plot as a chart.

In the case of financial modeling, many times the template or format of your output (P&L, BS, CFS) does not allow you to use a pivot table. In that case, you would probably want to revert back to the good old COUNTIF()/COUNTIFS() and SUMIF()/SUMIFS() functions.

If you are doing analysis on a data set, the limitations are most likely going to be your data set itself: you will probably have bad data, i.e. blanks, non-numerical characters such as " " or "-" instead of the accounting zero, as well as duplicate values. It is good practice to trim and clean your data set prior to doing any analysis.

As far as data set size, you can easily manage pivot tables with well over 1M rows of data (traditional limit on Excel sheet) using PowerPivot and queries, including connecting multiple data sets together.

Hope that helps!