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.
Excel
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…
Read More
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…
Read More
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…
Read More
In this post, we’ll summarize data from multiple CSV files with a PivotTable. Specifically, we’ll use a Get & Transform query (Power Query) to retrieve and prepare data from numerous CSV files. Then, we’ll send the query results into the data model (Power Pivot). Then, we’ll build our summary report using a PivotTable based on…
Read More
Traditional PivotTables are an incredible feature of Excel, but, they are not without limits. Many of the typical restrictions are removed when you use the data model rather than a single Excel table. If you’d like to learn how to build a PivotTable using the data model, and learn what the data model is, strap…
Read More
If you have ever downloaded bank activity from your bank’s website, you know there is some amount of cleaning you need to do before the data is ready to use. Perhaps the next step is to summarize the bank activity with a PivotTable, or, perhaps to perform a bank rec. Regardless of your ultimate objective,…
Read More
My Excel-friend John Michaloudis does an annual interview of Excel experts from all over the world. The format of his interview is an audio podcast. This year, you can hear directly from 20 Excel experts as they share their best Excel tips and ideas. The tips include shortcuts, functions, and features, and it is a…
Read More
Using the Data Model (or Power Pivot) helps us build some amazing PivotTables. A date table (or calendar table) is integral to most data models as they allow us to group reports by various date periods and use a wide variety of time intelligence functions. Although there are many ways to build such a date…
Read More
If you like shortcuts, features, and tools that can save you time in Excel, I’d like to call your attention to a Journal of Accountancy article that includes tips from several Excel experts. I’m honored that my tips were included in the article 🙂 The article includes these main categories of tips: Keyboard shortcuts Quick…
Read More
Comparing lists is a fairly common task in Excel, and as with anything, there are many ways to approach it. When we compare two lists, we are essentially trying to find out which names appear in both lists, or, which names appear in only one list. Tony posted a blog comment about how to perform…
Read More