Author and award winning instructor Jeff Lenning CPA has written several books and 40+ articles featured in various publications. We've compiled them all in a single place for easy reference.

Get Current and Historical Stock Prices into Excel

By Jeff Lenning | July 15, 2019 | Comments Off on Get Current and Historical Stock Prices into Excel

Microsoft recently updated the built-in tools for retrieving stock quotes into Excel. If you’re typing closing prices into Excel manually, this article may be able to help you get it done faster. Specifically, we’ll talk about how the Stock data type retrieves current quotes and related information, and how Power Query can retrieve historical stock…

Read More

Power and Pivot

By Jeff Lenning | January 7, 2019 | Comments Off on Power and Pivot

You’ve heard the terms “Power BI,” “Power Query” and “Power Pivot,” but maybe aren’t sure what they are. Good news! They are free tools from Microsoft and this column will talk you through them. And, while we’re at it, we’ll also talk about Pivot Tables and Pivot Charts. Let’s zoom out for a moment for…

Read More

Data Model Walkthrough

By Jeff Lenning | July 10, 2018 | Comments Off on Data Model Walkthrough

Have you heard people talking about Power Pivot and the data model, but you aren’t exactly sure what they are or how to use them? Well, in this article, we’ll use them to build a basic PivotTable. Before we jump into Excel, here’s some super-fast background. Although Excel is used for many different things, workbooks…

Read More

Pivot to the Next Level

By Jeff Lenning | May 16, 2018 | Comments Off on Pivot to the Next Level

I’m assuming you love PivotTables. I mean, who doesn’t, right? Well, traditional PivotTables are amazing, but they’re not without limits. Instead of using a traditional table as the PivotTable source, you can take your PivotTables to the next level when you use Power Pivot instead. Check it out. Before we get too far, let’s clear…

Read More

Beyond Numbers

By Jeff Lenning | October 4, 2017 | Comments Off on Beyond Numbers

Often when we use Excel, we think of its ability to operate on numbers. But, Excel is also good at operating on other data types, such as dates and text strings. When we limit our use of formulas to numbers only, we miss out on many opportunities for efficiency. In this article, I’ll talk about…

Read More

Skipping Excel

By Jeff Lenning | July 5, 2017 | Comments Off on Skipping Excel

How to Take a Process from 30 to 0 Minutes Excel is used for all sorts of things, including budgeting, reporting, reconciling bank statements and more. Sometimes, it’s used as a bridge between two applications … that is, to export, clean and import data between two different systems. As an example, maybe you export transactions…

Read More

Mind. Blown.

By Jeff Lenning | June 1, 2017 | Comments Off on Mind. Blown.

Stop Doing Manual Tasks Excel can do Faster Imagine this scenario: There are 120 CSV files in a folder on your network. Each CSV export contains the transactions for a single month, and there are 10 years’ worth of files. Your mission, should you choose to accept it, is to combine all of the transactions…

Read More

Clocking In

By Jeff Lenning | January 4, 2017 | Comments Off on Clocking In

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…

Read More

Get & Transform

By Jeff Lenning | September 6, 2016 | Comments Off on Get & Transform

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…

Read More

Market Share

By Jeff Lenning | June 2, 2016 | Comments Off on Market Share

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,…

Read More