Trying to make sense of large quantities of information can be challenging. Luckily, there is a way to make unmanageable amounts of data manageable: Pivot Tables.
By using pivot tables in Excel, you will be able to identify the key characteristics of and the trends underlying a particular dataset. They are also fundamental in helping you uncover any incorrect, incomplete or inconsistent information included in your database.
Pivot tables, for example, come in handy when you need to analyze and segment a loan portfolio, as well as to perform data quality checks on the same.
Let's have a look at how you can use pivot tables to analyze the composition of a database and to spot errors in the data.
How to Perform the Analysis of a Database using Pivot Tables
0. Save Your Excel File in the .xlsb Extension
Depending on the amount of data you will be handling, it may be a good idea to save your Excel file in binary format (.xlsb extension) to significantly reduce its size.
1. Database Analysis
Before creating pivot tables, add new fields containing any additional information you may need for your analysis, to the right of the last used column. That way, you won't change the order of the original fields. Also, the file may be too heavy to even be able to add new columns between the original field columns.
In particular, for certain types of fields, pivot tables are not useful unless you 'bucket' the data first. Just think of a database made up of 1k+ clients, each with a different birth date, or of 2k+ loans with different maturity dates or gross book values. Buckets are value ranges in which to categorize numerical data (i.e. '$10-20k' and '$20-30k', or '<18 years' and '18-30 years'), so as to have a limited number of rows in your pivot table and make your analysis more meaningful. Refer to the worksheet 'Mapping' in our template to see some examples of buckets, and to the worksheet 'Loantape_Analysis' to see how to use the INDEX MATCH function to categorize each row as per the buckets.
Differentiate the additional field headers from the original ones by using a different fill color, as shown in sheet 'Loantape_Analysis' in our Excel template.
2. Create Your First Pivot Table
Go to Insert > 'Pivot Table', and select your whole database, from the first field header down to the last row of the rightmost column.
NB: If your database has either an entirely blank row or column, or a blank field header, the pivot table will not work. If for some reason you need to leave a blank column in the database, simply write 'x' as the field's name.
In the 'Rows' area, drag and drop the field name(s), the composition of which you wish to analyze: all of the cell values or text populating that specific field will appear on the left-hand-side of your pivot table.
In the 'Values' area, drag and drop the field name(s) required to perform the desired analysis on the 'Rows' field(s): by default, the 'Values' field(s) will appear as columns at the top of your pivot table. You can decide which statistical operation to run on the 'Values' fields: at the 'Value Field Setting', you can choose to summarize the values by summing, counting, or computing the average, among others.
If you wish to filter out certain values included in the 'Rows' area, drag and drop the relevant field name(s) in the 'Filters' area, and deselect the checkboxes next to the items that you do not want to display in the pivot table.
3. Replicate the First Pivot Table for All Other Relevant Fields
Simply copy and paste your pivot table on new rows below. Leaving the 'Values' field name(s) unchanged, replace the 'Rows' field name(s) with the relevant field(s) you wish to analyze.
Refer to the sheet 'Pivot' in our Excel template for examples.
4. Format the Tables and Insert the Graphs Needed for Reporting Purposes
As presented in the sheet 'Graphs' in our Excel template, we have created tables (linked to the pivot tables) and graphs as per formatting standards, and ready to be copied and pasted to your report.
NB: If you change anything in the original data source, don't forget to hit 'Refresh All': all the pivot tables in your file will update automatically to reflect any changes made to the original database. Your linked tables and graphs will also update automatically, but pay attention to any variation in the pivot tables' rows.
How to Check for Errors in the Database
By looking at the rows of your pivot tables, you will be able to identify any incorrect input present within a particular field (e.g. a date in the 'Currency' field, or an amount in a 'Yes/No Flag' field). Also, check whether #N/A errors come up in either the rows or values.
When you spot any of the above, be sure to trace and solve the error in the original data source, then refresh the pivot tables to see whether the issue has been resolved.
We hope you found this article useful. Make sure to download our Excel template at the 'Members Area' of our website.
For questions or clarifications, feel free to reach out. Thanks for reading, and good luck!
Comments