Blog

Create an Income Statement with a PivotTable

Generally, I prefer to prepare financial statements with formulas and functions such as SUMIFS because there are no layout or structure limitations imposed. This means I can design the report to my exacting specifications. However, it is interesting to note that PivotTables support basic formulas, and using a PivotTable formula to create a calculated item […]

VLOOKUP and Wildcards

In a previous post, we explored how to use the TRIM function to remove trailing spaces from lookup values used in a VLOOKUP function. In this post, we’ll tackle the reverse issue, where the values in the lookup column contain trailing spaces, by using wildcards in the VLOOKUP function. Objective Before we get started, let’s […]

List Comparisons CalCPA Magazine Article

Hi guys! Just wanted to draw your attention to a recent California CPA Magazine article I wrote about comparing lists using Excel. Have you ever wanted to ask Excel to identify which items from one list appear on another? For example, which check numbers on the check register appear on the bank download, as illustrated […]

Create Keyboard Shortcuts for your Favorite Excel Commands

Generally, you can improve your speed by keeping your hands on your keyboard. But, what do you do if there is no built-in keyboard shortcut to execute your favorite command? Well, one approach is to customize the QAT. This post discusses the Quick Access Toolbar and the related keyboard shortcuts it creates. QAT Shortcuts Microsoft […]

Create Fiscal Year Periods with VLOOKUP

I’m a huge fan of the VLOOKUP function, and am surprised by its day-to-day utility for accountants. In this post, we use the VLOOKUP function to convert or translate calendar year transaction dates into fiscal year periods, such as a fiscal quarter. To accomplish this, we’ll first need to investigate in detail the function’s fourth […]

Table Total Row

The Table feature of Excel remains one of my favorites. This post explores one very specific aspect of the feature, the total row. Overview One of Microsoft’s greatest gifts of Excel 2007 was the introduction of the table feature. It allows us to convert an ordinary range into a table (Insert > Table, or, Ctrl+T). […]

Format Locked or Unlocked Cells

This post explores options for formatting cells that are locked, or unlocked, in an Excel worksheet. Scenario Let’s pretend we have a worksheet that helps a user compare three different loans. The user is required to enter information, such as interest rate and number of years, into designated input cells, but shouldn’t be allowed to […]

Recent CalCPA Article

How do you ensure your workbook is accurate? One approach is to set up a structured error check sheet. My recent California CPA Magazine TBRG (May 2014) article discusses the ingredients needed to build a reliable error check sheet. Here are the relevant links: Article: Put Errors in Check Sample workbook: Download Excel File

Recent JofA Article

Hi guys! Just wanted to draw your attention to my April 2014 Journal of Accountancy article that discusses the power of mapping tables. If you’ve not experimented with this technique, it is worth checking out. In summary, a mapping table sits between the data and report sheets and enables you to translate labels and aggregate […]

Conditional Averages with AVERAGEIFS

The AVERAGEIFS function can compute averages for transactions that meet a set of criteria. In this post, we’ll use it to create a report by customer that ignores zero value transactions. Overview Although SUMIFS was probably the most popular multiple condition function introduced in Excel 2007, it wasn’t the only one. Microsoft released AVERAGEIFS which […]