Excel University Blog
Read on for in-depth articles, tutorials, and videos. Search or browse for specific topics. Be sure to subscribe if you'd like to be notified when we write something new.
Jeff Lenning
I love PivotTables, and use them all the time. But, when our needs are simple, we can easily summarize data with a Get & Transform query instead. Why? To simplify our workbooks and improve our efficiency. Let me demonstrate. Objective Before we dig into the mechanics, let’s just be clear about our goal. Let’s say…
This post will demonstrate how to use Excel formulas to determine sample selections based on dollar units. The basic idea is that each dollar is a sampling unit, and as such, this method is more likely to select higher dollar items for testing. This method goes by several names, including monetary unit sampling, dollar unit…
I am super excited to announce the next Excel University course! It is an Excel certification course, so upon completion, you’ll be a Certified Excel University Graduate. I want to cover some of the high points in this post, and then provide a link with additional information. Plus, I’d like to share my next Excel…
Are you aware of the Escape Room trend, or the similar breakout logic puzzles that teachers use in classrooms? Here is the basic idea of the escape room. You and some friends or colleagues go to a company that has created an escape room. Like, in the real world, physically, you go to a place.…
I am thrilled to announce this year’s Excel University scholarship winners! These scholarships are supported by Excel University enrollments, so thanks to all of you who have helped make these awards possible! Both of our winners this year are great examples of the type of accounting student that the scholarship program was designed to assist.…
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. This type of process is so common it even has its own acronym: ETL (Extract, Transform and…
Do you love FlashFill? Do you love Get & Transform queries? If so, you’ll love the most recent Excel update pushed out by Microsoft: New Column by Example. It is essentially FlashFill inside PowerQuery. It is way cool. Check it out. Objective Let’s say we have some data. It doesn’t matter where it is, it…
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 in these 120 files into a single Excel…
There are numerous lookup functions in Excel, and often, their capabilities overlap. And, that is true with many things in Excel…there are often multiple ways to accomplish any given task. Performing lookups is no exception. So, how are we supposed to know which lookup function to use? Often, Excel users try to decide between VLOOKUP…
I’m happy to announce the immediate availability of Excel University’s newest guest lecture: VBA Pro by Jon Acampora. Earn 16 CPE hours while learning how to have macros do your work for you…what could be better? Macros offer the biggest opportunity for automation in Excel, and can help you get your work done faster. And…