Category Archives: Misc Functions

Adding VLookup as a Control for Your Month-End Workpapers

VLOOKUP As a CONTROL

When closing for the month, many use an Excel workbook to reconcile balances to the general ledger.  As the general ledger changes, do you have a process in place to easily determine that balances previously reconciled still tie to the general ledger?  If not, using Vlookup on each reconciliation worksheet provides a quick and easy way to accomplish this control.

Usually, the month-end balance from a reconciliation worksheet is at the bottom of the worksheet.  Below this balance, you can enter the associated general ledger account number to be used in a Vlookup formula.  After the account number and Vlookup formula have been entered in each workbook, all you need to do is cut and paste the most recent general ledger balances into a separate worksheet.  The Vlookup will display the most recent balance and you can use conditional formatting to highlight whether the two balances are within a tolerable error limit.

The example below assumes that the AR Trial Balance has been created and the total needs to be compared to the general ledger.  There is a worksheet labeled “Accounts Receivable” and a separate worksheet labeled “General ledger”.

The general ledger account for accounts receivable has been entered at the bottom of the Accounts Receivable worksheet and a Vlookup formula is used to compare this total to the amount in the General Ledger worksheet.

vlookup as control 1

 

 

 

 

 

 

 

The Vlookup function is searching for a worksheet titled “General ledger” in the same workbook.  The key to making this work is that you never delete this worksheet.  When the general ledger changes, simply paste the new general ledger data over the old data in the same worksheet.

For Vlookup to work, the lookup value, in this case the general ledger account number, must be in the leftmost column of the General Ledger worksheet (In this example, column A).  The general ledger data is in columns A and B and the account balance is in column 2 (which is the B column).  The FALSE range lookup value is used to indicate an exact match.  The example general ledger is shown below.

 

 

 

 

 

 

 

 

Why use the entire columns of A and B?  That way, if general ledger accounts are added, we’re not constrained by a limited range.  This is important as we want to paste over this data again and again without using new Vlookup formulas each time.

To calculate any differences, subtract the general ledger balance from the reconciliation balance.  Use the ABS function in the formula since we just want to know the absolute amount of a difference and do not need to know whether it is positive or negative (ABS provides the absolute value of a number).

 

 

 

 

 

 

 

In this case, the difference is 0.  However, we want a way to highlight a difference if it exceeds a tolerable error limit.  Assume that we want to highlight any change greater than $100 and, if the difference is greater than $100, highlight the cell in yellow.

Select cell B13 and then select the Conditional Formatting button on the Excel Home ribbon.  From the Conditional Formatting options, select “Highlight Cell Rules”, then “Greater Than”.

conditional formatting vlookup

 

 

 

 

 

 

 

 

 

 

 

Once “Greater Than” is selected, you have the opportunity to input both an amount and highlight colors.  In this example, our amount is greater than 100 and we’ll use yellow fill with a yellow text to highlight the difference.  Note that we used an absolute value since the difference could be either greater than 100 or less than 100, depending on whether the general ledger amount was more or less than then the reconciliation amount.  Having only one value makes the Conditional Formatting comparison much easier.

Vlookup and conditional formatting

 

 

 

 

 

 

 

 

Press OK.

Since the difference is not currently greater than 100, there should be no yellow highlight.  If the general ledger balance is changed to 31,500 (a difference that is greater than 100), the difference is now highlighted.

ABS

With this Vlookup control in place for each month-end reconciliation worksheet, you can copy and paste the new general ledger data into the General Ledger worksheet and the new balances will update.  Any differences that exceed a tolerable error amount will be highlighted and you can simply scan each worksheet to determine if a difference exists.

This process is much easier to implement if all month-end account reconciliations are in one workbook.  That way, the general ledger data only needs to be pasted once in the General Ledger worksheet each time the general ledger is updated.

CPASelfStudy has several in-depth courses on Vlookup and Conditional Formatting.  If you want more information or other productivity ideas, please check out our Excel course offerings.

 

 

 

SUBTOTAL() and AGGREGATE() -TimeSavers

subtotal and aggregate - time saversMany people are only aware of the Subtotal icon and have never looked at the Subtotal function itself. After all, the icon, lets you add, average and count a list so who needs more? Well if you are one of the people who subtotal data a lot then you may want to look at the Subtotal function or the AGGREGATE() function.

Let’s start with SUBTOTAL().
There are 3 advantages of using the SUBTOTAL function rather than the icon.

  • First, the subtotal function allows you to display the subtotal wherever you want it to display on the worksheet rather than in the list itself.
  • Second, you can specify if you want Excel to subtotal all of the data in the list, even the hidden values, or to only subtotal the visible data in the list. That is powerful.

The syntax for SUBTOTAL is =SUBTOTAL(function_num, ref1,[ref2]…). The function_num indicates the mathematical operation you want Excel to do. For example, =Subtotal(1,B2:B50) tells Excel to Average the data in cells B2 through B50. Apparently the programmers sequenced the function_nums alphabetically rather than on frequency as you would normally expect that 1 would be to add – or at least I did. Anyway, I digress. 1 means to average, 2 to count and 9 means to sum.  By using the single digit function_number, Excel automatically includes all hidden rows and columns in the list.  If you use the corresponding 3 digit function_number, Excel subtotals only the visible list in the data. So, the function_num 1 includes the hidden rows, while 101 excludes them.  Below is a list of the most common function_num:

subtotal()

  • The third advantage of the Subtotal function is that it excludes other subtotals. So, for example, if you had a monthly report that has a lot of report sections such as Sales, Cost of Goods Sold, Operating Expenses, Non-Operating Gains(Losses) , typically you add up each grouping and then create a grand total. If you used =SUBTOTAL() on each of these groupings than all you need to do is use =SUBTOTAL() at the end of the report to get a grand total because SUBTOTAL() does not include subtotals when it totals!  How cool is that?  This is also a time saver if you need to create a new section for something new within the report as the grand total will automatically update if =SUBTOTAL() was used.

AGGREGATE()

If you are excited about =SUBTOTAL() then check out =AGGREGATE() if you have Excel 2010 or higher. AGGREGATE() does everything that SUBTOTAL() does and more. While =SUBTOTAL() offers 11 functions, AGGREGATE() offers 19 functions. Some of the more useful new functions include SMALL() and LARGE(). In addition to offering you the option of ignoring hidden rows of data, it also allows you the option of ignoring  error values and/or subtotals.  This is key – how often have you tried to sum a column of data that has a #Div/0! Or a #N/A? Well, if you use AGGREGATE() you will actually get an answer instead of an error message in the total line.

The syntax of AGGREGATE() is similar to SUBTOTAL() but gives you more control over what you are totaling so it is more powerful.. It is AGGREGATE(function_num, options, ref1,[ref2],..]. A function_num of 1 still means Average while a 9 means Sum. The difference here is the options section. The option number tells Excel which values to ignore in the data list you are testing.

AGGREGATE()

 

 

 

 

 

 

SUMMARY

So, why didn’t I lead with AGGREGATE()? I started with =SUBTOTAL() because a lot of people are familiar with the Subtotal Icon and it is a bit simpler. However, if you are a manager of lots of data, I suggest you jump over SUBTOTAL() and use AGGREGATE().
Below is a simple example comparing SUM(), SUBTOTAL(), AGGREGATE().

Subtotal() aggregate()

 

 

 

Using TRUNC()

This is an excerpt from our Excel CPE course – Must Know Excel tips Tricks and Tools for CPAs.

TRUNC()
No Decimals
TRUN - no pennies

If you don’t want the pennies in your calculation you can use =TRUNC(), short for TRUNCATE, or =INT(), short for INTEGER,  to turn your data into integers.

 
The two functions work differently but return the same value when working with positive numbers. You will get different answers from these two functions with negative numbers. TRUNC() removes the fractional part of the number whereas INT() rounds down to the nearest integer based on the value of the fractional part.

In the screen shot below, -6.45 returns a different value using INT as it -7 is the lower value.

TRUNC

 

GETPIVOTDATA Function

I am in the process of updating my Pivot Table course and decided that I take another look at  the GetPivotData function.
GET PIVOT DATA is an Excel function that pulls data from your pivot table. I personally don’t use it  because if you hide a field you referenced in GETPIVOTDATA, the function stops working. Sometimes, just rearranging the fields in the table can impact the function as well. However, I did want you to be aware of it.
If you try to multiply a total in a pivot table, you will immediately see that Excel automatically references the GETPIVOTDATA function.  This is a problem because if you copy the formula,  you will get the same answer for the entire column or row.
In the example below, I opened Pivot_Rearrange.xlsx and removed the Product Name field from the Pivot Table.  Next I  clicked on cell C5, as I wanted to multiply B5 by 1.10 to estimate next year’s order amount by carrier.

Instead of showing =B5 it shows =GETPIVOTDATA(“Order Amount”,$A$3,”Ship Via”, “FedEx”)*1.10

Pivot Table Function GETPIVOT

 

 

Notice that A3 is treated as an absolute cell reference. If you copy the formula down,  you will get the same answer for all of the Ship Via carriers.  As a work around, I know many people who copy and paste the pivot table into another workbook if they want to do any math on the pivot table.  However, I found a great tip on the MrExcel.com website on how to avoid this problem.

  1. Go up the Options icon and click the drop-down arrow to the right of it.
  2. Uncheck Generate GetPivotData.

Pivot Table- GETPIVOT 2

 

 

 

Now, if you go back to cell C5 and recreate the formula, you will see  the formula bar now shows =B5*1.10. It will now copy down correctly. (Excel will not correct the GETPIVOTDATA formula so you need to delete it and then type in =B5*1.1.)

GETPIVOT

 

 

 

The best part is that once you uncheck it, Excel remembers so you do not have to keep unchecking the Generate GetPivotData icon every time you want to use a formula that references the pivot table.
This is an excerpt from my new Pivot Table Course that will be out the first week of June 2016.

AGGREGATE Function

The AGGREGATE Function

The AGGREGATE function is a very powerful function for summarizing an array of data.
It combines 19 different ways to summarize data into one function!
While it is a powerful function, my favorite reason for using AGGREGATE is its ability to SUM data with errors. 

The syntax for the AGGREGATE function is =AGGREGATE(function #, options, array).
The function #’s are listed below:

aggregate function

 

 

 

 

 

 

 

 

 

 

 

 

 

AGGREGATEOPTIONS

 

 

 

 

As you can see, your options permit you to ignore nested subtotals, hidden rows as well as error values.
One of my biggest pet peeves is trying to sum data that has error values.  The AGGREGATE function makes this easy.
Assume yousum column with errors have data in cell A1:A6, and that you want to sum it.
However, because there is an error value in A5, the SUM function will return an error as shown in the screenshot on the left.
You can easily work around this by using the AGGREGATE function.

 

 

In this example, I clicked in A7 and  typed =AGGREGATE(
AGGREGATE FUNCTIONS

  • A list of the various function #’s appears.  Note that if you elect to use the Insert Function dialog box, this handy list of functions does not appear.  We want to SUM the data, so function # 9 should be used, followed by a comma.

 

 

  • After the comma, a list of options is presented.
  • AGGREGATE DIALOG BOX OF OPTIONSIn this case, use option number 6, Ignore error values and place a comma after this.

 

 

 

 

  • Lastly, identify the array to be summed.  This will be accomplished by using the cursor to highlight A1:A6.
  • Close the parenthesis.
  • Press Enter.

AGGREGATE SYNTAX

 

 

 

 

 

 

The “Must Know” component of the AGGREGATE function is how to SUM data with errors.  It is a very powerful function, though.  You may find other uses for it in your professional assignments.

 

This is an excerpt from “Must Know Functions for CPAs”.

Ways to Sum Data with Errors

Excel Techniques to Sum Data with Errors

oops- signThere are times that you need to know the sum of numeric data even though there may be errors in it.  The most common of these error values are #DIV/0!, #NUM!, #N/A and #VALUE!.  #DIV/0! refers to a situation where Excel is trying to divide by zero or by a cell that is blank.  This is mathematically impossible, hence the error value.  The #NUM! error value refers to a situation where there is a problem with the underlying formula and the #VALUE! error value most often occurs when Excel is trying to perform a mathematical operation with text.
There are numerous functions and techniques in Excel to sum data with errors.  Some have been recently developed by Microsoft and are much easier to use than the older techniques.  We’re going to start with the oldest technique first, then progress to the newer, easier techniques so that we cover all the versions of Excel.
Oldest Technique – ISERROR
The ISERROR function in Excel evaluates a cell and returns TRUE if there is an error and FALSE if there is not an error.  Because you only have a TRUE value or a FALSE value to work with, this function must generally be paired with an IF function to be useful.  The IF function in Excel is structured so that there is a value if TRUE and a value if FALSE.
Here’s an example:  More…..

The N() function. Comments Anyone?

Most people are familiar with Insert>Comment. Just right-click on a cell and select Insert>Comment. Very easy. Right-click on the comment to display it, edit it or delete it. You know there is a comment in a cell because of the red triangle in the upper right corner of the cell.
Comments

Using comments can be a pain as it is additional step and if you display the comment sometimes it can obscure other cells. A more interesting way to document formulas, in particular, is to use the N() function.
The N(item) function returns a zero when the item is a string so if I typed =Count(A1.A5)+N(“I am only counting numbers”) then the answer would be 2.=Count(A1.A5)=2+0 =2
N function This can be a nice quick way to provide information if you are creating a complex formula and want to remember what you are doing.

In the example below, I identified that I was looking up Unit Price based upon the Product ID   Now, if I had looked up Product Name instead of Unit Price, I would have received an error message if I had included the N() function. Can you guess why?  In that case instead of the resulting formula being 17.95+0=17.95 the formula would have been = “Bing Cherry”+0 =#VALUE!   You can’t add text and numbers together.  So, this may be useful, to some of you, if you use complex formulas and are always scratching your head wondering what the heck it says.N function with Vlookup

  • You guys do a great job!  As a person in industry it can be really expensive to stay current and you have great options that are actually useful as well as cost effective!

    - Sue

  • Great course! Price was right and the material was very thorough.

    - Alex

  • “..may I say it is rarer that I ever have gotten a personal reply from a CPE Site! Glad I have found your site.”

    - Tom