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.
Techniques
Here’s the situation. We have created a PivotTable and related PivotChart, and, since we are nice, we have also provided a Slicer so that the user can easily make selections. But, we’d like the report titles to dynamically update based on the selections made. As with anything in Excel, there are multiple ways to accomplish…
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…
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…
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…
In this post, we’ll use a Get & Transform query to help with our reconciliation. The idea for this post came from a question from Laura (thanks Laura!) The basic idea is that we have two worksheets. One contains the invoice totals and the other contains line item details, where there are many line items per…
In recent back-to-back webinars, two different attendees asked how to checkĀ to see if a user entered a text string with all caps. Specifically, they asked how to set up a data validation rule to determineĀ if all lettersĀ are capitalized.Ā So, I figured I would write a quick post about the technique. Objective Let’s just take a moment…
In this post, weāll create a drop-down that contains a unique list of choices derived from a column that contains duplicate values. This may sound familiar as we previously accomplished this with a PivotTable. However, the Power Query feature that’s built-in to Excel 2016 makes this process easier. Objective We have a data table that contains RepID,…
When you need to perform a lookup, your instinct tells you to use VLOOKUP. But, when your lookup uses multiple conditions and columns, you may be inclined to use SUMIFS. However, when the value you need to return is a text string, rather than a numeric value, you are precluded from using SUMIFS since it only…
The terms Moving, Rolling, and Trailing are commonly used to describe the same calculation idea…that we want to operate on the previous say 3, 6, or 12 data rows. In this post, we’ll allow the user to define the number of rows to include and use the OFFSET function to dynamically define the desired range. Objective…
Beginning with Excel 2013 for Windows, we can use Slicers to filter table data. In this post, we’ll explore how to use Slicers as a relatively easy alternative to conditional drop-downs. Objective When we want to allow a user to select a choice from a list of items, we often consider using the Data Validation feature to…