Principles of Financial Modelling

CHAPTER 26Filters, Database Functions and PivotTables

INTRODUCTION

This chapter discusses several Excel functions and functionalities that can assist in the analysis of data sets, including:

• Filter and Advanced Filters, which present or extract a filtered data set whose elements meet specific criteria.
• Database functions, which return calculations of the values in a database that meet certain criteria (without explicitly extracting the relevant data points), and whose results are live-linked to the data set. Although equivalent calculations can generally be conducted with regular Excel functions (such as SUMIFS or MAXIFS), the Database functions allow the identity of the criteria to be more rapidly changed. As functions, they generally require that errors or other specific values within the data set are corrected or explicitly dealt with through new criteria, to eliminate them from the calculation process.
• PivotTables, which create summary reports by category and cross-tabulations. Although not live-linked to the data set (so need refreshing if the data changes), PivotTables allow for very rapid reports to be created, for the identity of the criteria to be changed, and for rapid “drill-downs” or more detailed analysis of the data to be conducted very quickly. They also allow for errors or other specific values in the underlying data to be ignored (or filtered out) when applying the criteria or filters.

The chapter does not deal with the linking of Excel to external data ...