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.
Features
This is the second post in the Impossible PivotTables series, where we are exploring Power Pivot. In the first post, we examined the limitation of calculated field references in a traditional PivotTable. In this post, we’ll address the limitation of using the Show Values As option for subsequent calculations. If we use the Show Values…
This is the first post in a series called Impossible PivotTables. The purpose of this series is to explore Power Pivot. I thought a fun way to do that would be to demonstrate how using the data model enables us to build PivotTables that are either impossible with traditional PivotTables or that require workarounds. I…
Sometimes in Excel, we want to use formulas to compute row values, but, the number of rows is dynamic and changes periodically. For example, let’s say we want to create an amortization schedule and use it for a variety of loans. Some loans are paid in 36 months, some in 120 months, and some in…
Power Query is an amazing tool, and I love learning about it. In the Power Query Editor, as you click the command icons, Excel is actually writing M code behind the scenes. M code has many more functions than are available in the ribbon. So, the thing to keep in mind is that if you…
This is the second of two related posts that demonstrate how to use Power Query to deal with rows and delimited lists. In the first post, we combined rows into a delimited list. In this post, we’ll do the opposite and convert a delimited list into rows. Well, what are we waiting for … let’s…
This is the first of two related posts that demonstrate how to use Power Query to deal with rows and delimited lists. In this first post, we’ll combine rows into a delimited list. In the second post, we’ll do the opposite and convert a delimited list into rows. Well, what are we waiting for ……
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. In this California CPA Magazine article, I talk about a…
If you use PivotTables often, you’ve probably wished there was a way to set default PivotTable layout options. For example, if you prefer Tabular instead of the default Compact, it sure would be nice if all new PivotTables used the Tabular layout. Well, depending on the version of Excel you have, you may in fact be…
In this post, I answer a question from Emmanuel who asked how to compute the age when given a list of birth dates. We want the solution to be dynamic, so that it is easy to update the age calculation going forward. Since this is Excel, there are many ways to accomplish this. In this…
You want to perform a lookup with VLOOKUP, but, there are multiple lookup columns. So, what are you supposed to do? Combine them into a single lookup column? That is certainly one option, but, as with just about anything in Excel, there are multiple ways. In a previous post, I showed one way to do…