### Excellence with Excel

Master Excel navigation, formulas, formatting, graphs, data analysis, and VBA for workflow automation via case studies of Walmart and a customer due diligence file.

Learn moreIn this tutorial, you’ll learn how to complete an Excel practice test by writing a flexible formula that lets you summarize quarterly or monthly data in an annual format using the INDIRECT, MATCH, and SUMIFS functions. This is a common task given in Excel tests and case studies, especially in industries such as real estate.

When it comes to **Excel practice tests** in interviews, the key is to have a solid knowledge of key Excel functions, such as SUMIFS, INDEX/MATCH, INDIRECT, and HLOOKUP/VLOOKUP, and then combine that knowledge with a lot of practice.

If you want to get an Excel practice test with simpler multiple choice questions, please see our BIWS Certificates page and look at the PDFs under “Excel & VBA” there.

But if you want a much more challenging Excel practice test that requires you to write real formulas in Excel, keep reading.

Often, interviewers will ask you to write a single formula that accomplishes a task elegantly rather than having to modify a formula slightly each time you use it.

For example, In in many cases, you could write simple SUM formulas to sum up cells manually, but it’s far more robust to use the SUMIFS function so that you can check the dates and include only the matching quarterly or monthly data for the year you’re in.

But to make the function truly flexible so that you can copy and paste it down and around and use it to sum up data for different rows, you must use the MATCH and INDIRECT functions.

Master Excel navigation, formulas, formatting, graphs, data analysis, and VBA for workflow automation via case studies of Walmart and a customer due diligence file.

Learn moreHere’s a real-life example submitted by one of our students:

**Excel Practice Test Prompt:** “Review the quarterly real estate data for this apartment complex with 4 units, and write a SINGLE formula to retrieve everything on an annual basis.”

Here’s a partial screenshot of the data from the blank Excel file above:

And here’s the annual summary page:

The quarterly sheet is called “Quarters” and the annual sheet is called “Summary”.

So, how would you write a *single formula* to accomplish this?

Instead of trying to write one formula all at once, **break it into smaller pieces** and build up to something more flexible by the end.

At each stage of the process, you want to have a *functional formula* in Excel – that way, even if you don’t finish it perfectly, **you’ll still get credit for the work you did.**

In this case, let’s start with a SUMIFS formula so that we can capture the quarterly data by date:

=SUMIFS(Quarters!$E$9:$T$9,Quarters!$E$2:$T$2,”<=”&Summary!E$2,Quarters!$E$2:$T$2,”>”&Summary!D$2)

Here’s what it looks like in Excel for the first row:

This is a good start, but the problem is that since Quarters!$E$9:$T$9 refers to the **summation range**, we’ll have to update that and make row 9 into row 16, row 23, and so on, as we move down:

To make this part flexible, we need to *match the text on the left to the same text in the quarterly data* and then move down that number of rows.

For example, in this first row, we could write this formula to do it:

=MATCH($B4,Quarters!$B$1:$B$51,0)

But it’s **not** as simple as just inserting this MATCH function into the SUMIFS formula above.

**The problem is that Excel only allows us to reference fixed ranges of rows and columns in spreadsheets… such as B10:E19 or E9:T9.**

So, we can’t just “substitute in” one or several MATCH functions for the Quarters!$E$9:$T$9 range and be done with this Excel practice test.

But there is one exception: **we can use the INDIRECT function to create a variable reference to another spreadsheet.**

The syntax here gets a bit tricky because we’ll need to join together several parts to create a new version of the formula above, but here’s the basic approach:

First note, that double quotes (” “) indicate *text* within functions such as MATCH *inside* the INDIRECT function, and the ampersand symbol (“&”) *joins* text with functions.

So, in the original formula above:

=SUMIFS(Quarters!$E$9:$T$9,Quarters!$E$2:$T$2,”<=”&Summary!E$2,Quarters!$E$2:$T$2,”>”&Summary!D$2)

We’ll replace the Quarters!$E$9:$T$9 part with the following:

INDIRECT(“Quarters!$E$”&MATCH($B4,Quarters!$B$1:$B$51,0)&”:$T$”&MATCH($B4,Quarters!$B$1:$B$51,0))

What does this do in the context of this Excel practice test?

The **first part** starts building the text for Quarters!$E$9:$T$9:

“Quarters!$E$”&MATCH($B4,Quarters!$B$1:$B$51,0) produces Quarters!$E$9.

This is because the first text here, “Quarters!$E$”, simply prints exactly as-is within the Excel function.

And then the MATCH($B4,Quarters!$B$1:$B$51,0) function finds the row position of the text we’re searching for in the “Quarters” spreadsheet, which is 9 in this case.

So, Excel sticks together these two parts: “Quarters!$E$” and 9 to form: Quarters!$E$9.

The next part here:

&”:$T$”&MATCH($B4,Quarters!$B$1:$B$51,0))

Simply finishes the Quarters!$E$9:$T$9 text. We use the & character to join the first part to this part, and then we print the “:$T$” text in Excel.

Then, to find the appropriate row number, we use the same match function and join it with the & operator:

MATCH($B4,Quarters!$B$1:$B$51,0)

As a result, this entire term:

&”:$T$”&MATCH($B4,Quarters!$B$1:$B$51,0)) turns into :$T$9 when Excel evaluates it.

Putting it altogether, here’s what happens with this entire formula:

INDIRECT(“Quarters!$E$”&MATCH($B4,Quarters!$B$1:$B$51,0)&”:$T$”&MATCH($B4,Quarters!$B$1:$B$51,0))

becomes:

Quarters!$E$9:$T$9

**And then as we copy this down, there’s no need to modify the formula manually because the MATCH functions keep finding the row number of the matching text on the Quarters spreadsheet:**

Here’s the entire annual summary for this Excel practice test:

It is tricky to get the syntax with the double quotes and ampersands exactly correct in these types of INDIRECT functions, so you should always check your work, time permitting.

The best way to do this is to sum up **manually** each quarter’s figures for each line item and see if they match the annual totals – if not, you have a problem.

Here’s an example for Gross Potential Rent:

In the Excel practice test – an actual exercise in Excel, not just a multiple choice test – the following topics are extremely common:

-Lookup functions and INDEX/MATCH

-Summation and summary functions such as SUMIFS

-INDIRECT, which lets you create custom references and variable cell ranges that you can pass into other functions and formulas

You don’t necessarily need to get the formula above 100% correct to “pass” the test.

**The most important point is that you should make sure you have a working formula in each step of the process.**

That way, even if you mix up some of the syntax in the INDIRECT function, or your formula doesn’t work perfectly in all cases, you can show them some of your work and get credit for it.

Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street. In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.