Adding VLookup as a Control for Your Month-End Workpapers

Mar 9, 2018 | Functions, General, Lookup Functions, Misc Functions

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...

SUBTOTAL() and AGGREGATE() -TimeSavers

Sep 7, 2017 | Functions, Misc Functions, Miscellaneous Excel Tips

Many 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...

Using TRUNC()

May 12, 2017 | Excel, Functions, Misc Functions, Miscellaneous Excel Tips

This is an excerpt from our Excel CPE course – Must Know Excel tips Tricks and Tools for CPAs. TRUNC() No Decimals 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...

GETPIVOTDATA Function

May 31, 2016 | Functions, Misc Functions, Pivot Tables

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...

AGGREGATE Function

May 20, 2016 | Functions, Misc Functions

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...

Ways to Sum Data with Errors

Nov 16, 2015 | Logic Functions, Misc Functions

Excel Techniques to Sum Data with Errors There 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...