Data Analysis in Excel - Index/Match and PivotTables
Two tools to advance your career
This is the second article in a series, “Data Analysis in Excel”. The first article, outlining tips and tricks, can be found here. Today we take a look at two Excel features I use on a daily basis. The steps below assume that you are using tables in your workbook, as we discussed in article one. If anything is unclear please post in the Substack chat, as other readers likely have similar questions.
In modern corporate America, you often hear buzzwords such as dashboarding, business intelligence, reporting, and analytics. I live in this world and I hear these requests. My team, the Data Strategy & Reporting team, is responsible for delivering insights to business users with a diverse set of needs. Oftentimes, these needs can only be met with complex solutions, custom tools, and the use of code. However, for the small business, sometimes Excel may suffice.
Business intelligence (BI), as defined by IBM, is “software that ingests business data and presents it in user-friendly views such as reports, dashboards, charts and graphs.“ Based on the needs of a business and the specifications of the data, Excel is, in fact, a BI tool. With the INDEX and MATCH functions to join datasets and PivotTables to add a dashboard layer on top, Excel is all you really need for relatively small datasets. Note this only works with a semi-infrequent update cadence. If real-time, or even hourly data is needed, you may need a purpose-built or code-based solution.
Using similar data to our first Data Analysis in Excel article, I will walk you through how to build a layer of dashboards on top of raw data in Microsoft Excel.
Index/Match
To illustrate appropriate usage of the INDEX and MATCH functions, I created a mock work orders dataset. In many real estate applications, work orders are the unit on which necessary work are contracted out to vendors or assigned to employees. In other industries these billable units may look different.
In a hypothetical analysis, we want to analyze work order volume and cost by type and by market. However, our raw work order data does not contain a market identifier. What are we to do? In R we would left_join(), in SQL we would LEFT JOIN, in Excel we combine two functions, INDEX and MATCH.
We know that our Properties table has a Market field. Additionally, we recognize that our Properties and work orders (WOs) table both use a Property Id. This will be the value we match on. The INDEX function works by giving us a value that aligns with the row we specify1. Take a look at our formula below:
=INDEX(Properties[Market],MATCH([@[Property Id]],Properties[Property Id],0))In plain English, we want the Market taken from the row of the Properties table where our Property Ids are the same. We tell Excel that the Property Id in our WOs table should have a corresponding value in the Properties table. Note that Excel will always return the index value associated with the first match. Let’s say, as an example, that an asset is in Arlington, TX. If our data was messy, some rows may identify this asset as in Dallas, and others as in Fort Worth. If Dallas appears first in our data, we would have no way of knowing that it is also associated with the Fort Worth market.
This is a bad example since we should have had better data integrity on a property level in the first place, but my point still stands. If a match can return multiple indexed values, it will only return the first value it comes across.
You might be asking, why is there a ,0 at the end of our formula? For most purposes, you want to specify this 0. This guarantees that Excel will look for an exact match. The other two options, 1 & -1 will also return values less than or greater than our lookup value, respectively. This is useful if the closest match is what you need for a specific analysis, but if you are unsure please be certain you specify exact match.
PivotTables
In my opinion, PivotTables are the most accessible BI solution on the market today. With no coding background, a user can build a robust reporting layer on top of any dataset. PivotTables are easy to use, and even easier for end users to drill down on.
I always point out to our business users that if you double click on a PivotTable row that you will receive the raw data behind that subset of your source table. This is a great advantage for the power users in your organization, and allows them to feel comfortable with the data you are presenting.
With no code and built in transparency, PivotTables are a great option for rapid implementation of BI.
Creating a PivotTable from an existing table is very easy. If you go to the Insert menu, it is all the way to the left, and you can specify a table name. If you’re like me and don’t want to touch your mouse, Alt+N+V+T gets you to the same place.
Once you have created your PivotTable, a menu appears on the right of your screen, PivotTable Fields. At this point, you can specify filters, columns, rows, and values. The menu is not dissimilar to common BI software solutions such as Tableau, PowerBI, and Amazon Quicksight.
At this stage, I’d encourage readers to play around with the data on my Github and build a variety of different PivotTables. Proficiency comes with repetition, and each use-case is unique. In general, I place subsets in my rows and metrics within my values. I utilize filters to remove the noise from my data. A word of caution, you do not want a PivotTable wider than your computer monitor. Do not use columns for any field with more than three options.
Finally, slicers are a great addition to give users the ability to work with dynamic filters. If you use Alt+N+SF with your cursor on the Pivot you can create a slicer tied to any field in your dataset.
I truly believe that iterative work is the best way to achieve what the business needs. Fortunately, Excel is a low-cost solution to mock up work prior to displaying data in a “productionized” BI environment. PivotTables, enabled through the INDEX and MATCH functions, allow us to show the end user draft data in advance.
As mentioned above, for a small business this may be all that is needed. For enterprise organizations and firms with institutional clients, Excel, and in particular PivotTables, are a great weapon to add to your arsenal in developing a BI suite.
Alec A. Nathan is the Director of Research and Data Analytics at Sylvan Road Capital. A graduate of the University of Georgia, Alec specializes in real estate investment and data science. Please direct any inquiries to anathan@sylvanroad.com.
Pro Tip: An =INDEX(MATCH(),MATCH()) allows us to specify both row and column!