One of the most advantageous aspects of Microsoft Excel a finance professional can learn is how to automate different tasks. In this post I’ll focus on two different types of spreadsheet automation in Excel; that via advanced techniques with the application, and the automation enabled by cutting edge software. Proficiency in the right Excel techniques and functions, as well as the use of new technologies can result in improved productivity and efficiency for any organization.
Automation Via Advanced Excel Techniques
1. VLOOKUP
The VLOOKUP formula is among the most commonly used functions in Microsoft Excel. Its purpose is to create analysis, summaries, models, and other key reporting elements in spreadsheets. However, while the VLOOKUP function is fairly easy to use, Excel users spend far too much time on small tasks like making sure their formulas are pointing to the right data, in the right column. Fortunately, Excel users can save themselves all that time by simply automating the VLOOKUP formula. The following illustrated examples spell this out:
With reference to the above spreadsheet: let’s say you wanted to pull March revenue for customer #1002. You would have to use the VLOOKUP formula as shown in cell D12 with col-index-num equal to 4 in order to pull it. The problem with cases like this is that a lot of time is wasted when attempting to find the revenue for other months for the same customer. This would require repeatedly manually changing the col-index-num from 4 to 5, in order to pull the revenue from May, for example. This is a time consuming and error prone manual task. The solution to avoiding this time waster is using the MATCH function in tandem with VLOOKUP.
The above illustration shows a second VLOOKUP formula in cell D13; with col-index-num 4 substituted with MATCH($B$11,$A$3:$G$3,0). The steps to create the MATCH function are as follows:
- 1. Type MATCH, followed by an open parenthesis. For example: VLOOKUP($A13,$A$3:$G$9,MATCH(
- 2. Select the lookup value, followed by a comma. In this case, the lookup value is B11, or Mar. You can choose to type the name in the formula instead of referring to a cell number; but make sure to have that value (“Mar”) in quotation marks if you do so. The syntax should be as follows: VLOOKUP($A13,$A$3:$G$9,MATCH($B$11,
- 3. Select the lookup array, or the row of the column heading. In the illustration, the lookup array is $A$3:$G$3. Make sure the range is similar to the VLOOKUP range. In this example, cells A:G are selected in the VLOOKUP; thus the MATCH formula must have the same range of A:G. Finish the MATCH formula with a comma and a closed parenthesis, as follows: VLOOKUP($A13,$A$3:$G$9,MATCH($B$11,$A$3:$G$3,0)
- 4. Finally, complete the VLOOKUP formula by putting a comma, zero and closed parenthesis: VLOOKUP($A13,$A$3:$G$9,MATCH($B$11,$A$3:$G$3,0),0)
If you change B11 to May, the VLOOKUP formula will automatically show May revenue for customer #1002. The MATCH function can always help the VLOOKUP formula find the reference column as long as it is in formula range.
2. Selection & Sum of Variable Rows in Excel:
Selecting each individual cell to sum variable rows is too time-consuming if you have thousands of rows. Setting up a function to do this is a lot more efficient.
Take a scenario where your objective is to sum every third cell where the row label is “forecast”. Building the formula by referring to each individual cell would take too long. The following Excel figures show a better way to do this, through creating a function.
It would take hours to finish the formula started in this example without a function:
Formulas like =C4+C8+C12+C16+C20+ or =SUM(C4,C8,C12,C16,C20, have been around since the dawn of the spreadsheet in 1981. What you need to solve this problem is a function introduced in 1997, called SUMIF.
As you can see in Figure 2 (above), the function usually has three arguments. First, you specify a range of row labels. The second argument is a single value that you hope some row labels will match. The third argument is a range of numbers that correspond to the row labels in argument 1. Whenever the row label in the range matches the criteria, the corresponding value from the sum_range is included in the total.
One way to build the formula is to specify the criteria in quotes is in the spreadsheet below =SUMIF(B2:B4464,”Forecast”,C2:C4464)
Or, you can enter the criteria in a cell and point to a cell. In the figure below, dollar signs are added to make the first and third arguments absolute references, and the formula is copied to build a small summary table to add up each of the three types of values in the data set.
3. Variations on SUMIF:
Back in Figure 2, Microsoft shows the [sum_range] argument in square brackets, meaning that it is an optional argument. How could this be optional? If the range and sum_range are the same set of cells, you can leave off the sum_range. This would only happen if you were looking for numeric criteria, such as summing all cells above 20000: =SUMIF(C2:C4464,”>20000”)
Invariably, once you master SUMIF, you will run into a problem where you need to sum based on two conditions. SUMIF cannot handle this, but other tools can.
FP&A Automation Enabled by High Tech Resources
A) Merge other data sources with Excel workbooks:
Most businesses store their data in a variety of formats. You might have information in rich and complex spreadsheets, GIS, web services, etc. Financial software gives you the ability to integrate your spreadsheet with data from hundreds of other formats. By building a workflow, you can automatically import all your data and merge it however you choose. The ability to query any data before generating reports is a huge added value to decision-making and analysis. Once your Excel data is in an integrated workflow, the possibilities for data transformation, QA, and analysis are virtually endless. Some of the new possibilities include:
- Applying spatial analysis such as point-on-area overlays
- Enriching your data with online services for drive-time areas, routing, geocoding
- Performing geometry validation check.
B) Generate reports and summaries in high volumes
Create useful summaries and statistics sheets by transposing or pivoting the data. For example, you can design a workflow that filters the data into different sheets based on the value of a column, then generates a summary report with an analysis of the content. That workflow can be run as many times and as often as you need.
When it comes to repetitive tasks, nothing is more satisfying than having it done automatically while you get on with more important things. That’s one of the greatest advantages of using a software system: create your workflow once, then use it over and over to do your work for you. For high volumes, you can automatically generate reports on a schedule, or even set up a self-serve download service where anyone can generate a report on demand.
C) Manage and analyze data larger than the Microsoft Excel size limits:
Huge datasets can result in poor performance or size restrictions in MS Excel. Thankfully, you can still perform all your analysis and data manipulation outside of Excel. Many FP&A software have a graphical interface feature that makes working with spreadsheet data intuitive.
You can also create subsets of the large spreadsheet by leveraging pivots and statistics. Cleaning up the data or reformatting the values can also have a big improvement on performance—for instance, by splitting up strings or reformatting date fields.
D) Load any data into spreadsheet templates:
Much like you can integrate a spreadsheet with data from a variety of disparate sources, you can also merge data to create interesting Excel reports. For instance, you could embed a map or raster image from another source directly into the spreadsheet.
With software, users are empowered with fine-tuned control over the schema, content, and style of output automated spreadsheets. Once you define the styling and formatting—perhaps some combination of predefined pivots, charts, graphs, and images—you can automatically load data into that template at any time. The software system automatically runs the workspace and updates your Excel dataset any time new information is received.
