Articles

Clocking In

Publication:

California CPA Magazine

Date:

January 2017

Author:

Jeff Lenning

Have you ever tried to do some type of calculation with a time value in Excel, only to receive a strange result? Two examples are adding up hours on a time sheet and multiplying the number of hours worked by a pay rate. If you’ve tried something like this and encountered an unexpected result, the […]


Get & Transform

Publication:

California CPA Magazine

Date:

September 2016

Author:

Jeff Lenning

If you haven’t played with the “Get & Transform” commands in Excel 2016 for Windows, they’re  probably worth checking out. I don’t want to sound overly dramatic here, but this set of capabilities is a game changer. These tools provide new ways to approach tasks, and enable us to do things that were previously time-consuming, impractical or required macros. Formerly available as the “Power […]


Market Share

Publication:

California CPA Magazine

Date:

June 2016

Author:

Jeff Lenning

If you get stock quotes into Excel by typing or copy/pasting from your web browser, you may be able to get your work done faster if you use a built-in Excel feature. Excel’s external data feature enables you to retrieve data from a variety of sources—one of which happens to provide stock quotes. Let’s check it out. In any blank worksheet, […]


Link It

Publication:

California CPA Magazine

Date:

May 2016

Author:

Jeff Lenning

Back in the old days, when I used workpapers that were made with, you know, paper, I used a red pencil to create cross-references that made it easy to navigate to related documents. I  remember feeling very organized because every document was indexed and part of a collection. Now, things are different. I get annoyed when I need to print something. And, when […]


Tech Talk: Excel 2016

Publication:

California CPA Magazine

Date:

November 2015

Author:

Jeff Lenning

Microsoft recently released Office 2016, and with it, the next version of my favorite program—which is, of course, Excel. The user interface is about the same as 2013 and you’ll feel right at home. Microsoft focused on expanding Excel’s data visualization capabilities by introducing several new chart types and on Excel’s ability to import and transform […]


Tech Talk: Software Savvy

Publication:

California CPA Magazine

Date:

June 2015

Author:

Jeff Lenning

If you haven’t checked out Excel’s conditional formatting feature recently, you’re missing out on some nice enhancements. The feature formats a cell based on its value. Excel continuously monitors the cell value and updates the formatting as the cell value changes. Conditional formatting has traditionally been limited to basic cell formatting such as fill and […]


PivotTable Month Groups

Publication:

California CPA Magazine

Date:

May 2015

Author:

Jeff Lenning

PivotTable article by Jeff Lenning

There are often several ways in Excel to accomplish any goal. For example, when building reports, we can use formulas or PivotTable. When building a formula-based report, we typically set up the report labels as desired and then write Excel formulas to compute the amount values and subtotals. This is a terrific approach when we […]


Comparing Spreadsheet Lists with Ease

Publication:

California CPA Magazine

Date:

July 2014

Author:

Jeff Lenning

Have you ever had two lists in Excel and wondered what items on one list appear on the other? For example, you’ve exported a check register from your accounting system and downloaded activity from your bank’s website, and you simply wanted to know which checks cleared the bank. That is, which checks on the checklist […]


Put Errors in Check

Publication:

California CPA Magazine

Date:

May 2014

Author:

Jeff Lenning

How do you know your workbook is accurate? This question is important because it’s our responsibility to ensure that data flows properly through the workbook, with numbers that tie out, and with reports that are internally consistent. One way to help ensure workbook accuracy is by creating a worksheet within the workbook dedicated to this […]


The Power of Mapping

Publication:

Journal of Accountancy

Date:

April 2014

Author:

Jeff Lenning

Many financial systems do a fine job of generating standard reports, but accountants often need more. In those cases, accountants can create custom solutions in Excel, but that approach has drawbacks. For instance, a typical process may involve exporting some type of data, perhaps a trial balance, and then opening it in Excel. Once the […]


Spreadsheet Nerdiness

Publication:

California CPA Magazine

Date:

May 2013

Author:

Jeff Lenning

Excel 2013 Highlights The next version of my most favorite program is here—it’s finally here! Excel 2013 is available, and I’m excited to review some of the highlights. Microsoft clearly invested a lot of time into this product. Here are just a few of the things you’ll notice about Excel 2013.   Microsoft Kept the […]


Excel and Access: Tools of our Trade

Publication:

On Balance (WICPA)

Date:

June 2012

Author:

Jeff Lenning

What would you bring if you were stranded on a desert island? I would bring Microsoft Excel because it’s the only application I would need! It stores more than 17 billion cells in a single sheet, summarizes data with Pivot Tables, generates charts and has a built-in Web browser. Excel, in my opinion, is the […]


Excel on the Web

Publication:

California CPA Magazine

Date:

May 2012

Author:

Jeff Lenning

Did you know Excel has a built-in web browser with a special feature other browsers don’t? The Excel browser visually identifies and allows you to select web tables, and then import them into Excel in a structured way. The best thing is that once you have imported a table, you can refresh the data range […]


Pivotal Advance Boosts Excel’s Power

Publication:

Journal of Accountancy

Date:

September 2011

Author:

Jeff Lenning

In today’s accounting world, financial and operational data typically is stored in a variety of programs and formats. When accountants need to prepare a report based on data from various systems, the first step is to export the data into Excel. Typically, it is fairly easy to export the required data into Excel. But, depending […]


Virtual Computing Virtues

Publication:

California CPA Magazine

Date:

July 2011

Author:

Jeff Lenning

Many CPA firms install several years of tax preparation software on their desktop computers, in addition to perhaps a few different versions of QuickBooks. This often prevents firms from easily upgrading to new hardware because the prior year programs may not run well on new operating systems and it’s a hassle to find all of […]


SUM() No More

Publication:

Calliope (Wisconsin Institute of CPAs)

Date:

June 2011

Author:

Jeff Lenning

What is your #1 most-used Excel worksheet function? Is it the SUM function? Believe it or not, there is an Excel function that is superior to SUM and once you discover it, you’ll hardly use SUM again. I’m referring to the SUBTOTAL worksheet function and if you haven’t explored it yet, I would strongly encourage […]


Be Excel-lent

Publication:

California CPA Magazine

Date:

May 2011

Author:

Jeff Lenning

Microsoft released Excel 2010 about a year ago and, while you may have upgraded by now, you may be like me. Sometimes I’ll upgrade an application, but not take the time to learn about its new features. So I continue doing my work on the “DILLY” method—you know, Do It Like Last Year. But each […]


Excel Rules

Publication:

California CPA Magazine

Date:

May 2010

Author:

Jeff Lenning

Two enhancements introduced in Excel 2007 may have slipped past you: multiple condition sum with SUMIFS and Tables (not Pivot Tables). It’s my hunch that once you discover them, you’ll fall in love with them the way I have. Please note: this Author’s version contains additional screenshots and info. For the Published version, please visit […]


Simplify and Improve Your Office System

Publication:

Journal of Accountancy

Date:

April 2009

Author:

Jeff Lenning

Put your workstations on a diet, and let your server do the heavy lifting.Talk about pressure—and not just in tax season. Keeping office computer systems up and running in small and medium CPA firms is an enormous and unending responsibility. Without reliable computing and communication capabilities, your staff, your clients and your business are dead […]


Defining You

Publication:

California CPA Magazine

Date:

June 2008

Author:

Jeff Lenning

Don’t be Generic; Get Yourself a Domain Name As Published in: California CPA Magazine Technology & Business Resource Guide 2008   Do you have a generic e-mail address? For example, jeff@hotmail.com, jeff@yahoo.com or jeff@aol.com. If so, you may have had the desire to upgrade your e-mail to a more professional address—something like jeff@clickconsulting.com or jeff@yourfirmname.com. […]