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.
Posts
Hello Excel enthusiasts! Today, I’m answering a question I was recently asked: how to sort a pivot-style formula-based report in descending order by amount. The whole process is easier than it sounds, and I’ll demonstrate the steps through a series of exercises. Don’t worry, if you aren’t sure what a “pivot-style formula-based” report is, I’ll…
Read More
Time to walk through another exciting Excel adventure 🙂 Today, we’re zooming in on an interesting question I recently received: “How can I highlight correct answers on an Excel worksheet for my granddaughter’s multiplication tables?” Sounds like a marvelously practical application of Excel, doesn’t it? Well, let’s dive in and find out how we can…
Read More
In this tutorial, we will learn how to create a dynamic list of months in Excel using various functions including TAKE, EOMONTH, and SEQUENCE. This list can be used for reporting or pulling key performance indicators (KPIs) with lookup functions such as XLOOKUP. If you create the same basic report each month, and would like…
Read More
In this tutorial, we’ll create a fully dynamic amortization schedule based on the number of months entered. For example, if you enter 12 months, the amortization schedule will span 12 rows. If you enter 360 months, the amortization schedule will span 360 rows. The technique demonstrated in this tutorial uses the following functions: SCAN, SEQUENCE,…
Read More
Excel’s PIVOTBY function allows you to create dynamic summary reports that look very much like PivotTables. However, they are totally dynamic … no need to click Refresh. They are created with a single formula, but do not have the rich features and capabilities of PivotTables. Depending on your workbook, the PIVOTBY function may be a…
Read More
In this tutorial, we will learn how to use the GROUPBY function to create dynamic pivot-style reports with a single formula. Let’s back up a sec. For years, we’ve turned to PivotTables to build summary reports. Now we have another option: the GROUPBY function. While it doesn’t create a true PivotTable, it does create a…
Read More
Welcome to this tutorial on how to find invalid characters in Microsoft Excel. In this tutorial, we will explore different methods including formulas and conditional formatting to detect and highlight characters you define as “invalid” in your Excel worksheets. Since you can easily customize the list of “invalid” characters, this approach is extremely flexible and…
Read More
The goal of this tutorial is to learn how to insert checkboxes in Microsoft Excel using two different methods: checkbox cell controls and legacy form controls. We will also explore how to use checkboxes in formulas, conditional formatting, and how to activate the developer tab for legacy form controls. Video Step-by-step Exercise 1: Inserting Checkboxes…
Read More
Welcome to our blog post on rounding to the nearest multiple in Excel. In this tutorial, we will explore three different functions that can assist you in rounding numbers up or down to the nearest multiple: FLOOR, CEILING, and MROUND. Whether you need to calculate the number of product bundles or adjust sales targets, these…
Read More
Welcome to this tutorial on splitting text at a specific character using Microsoft Excel. In this tutorial, we will explore four different methods to split text: Text to Columns, Flash Fill, Formulas, and Power Query. Each method has its own unique advantages, and we will cover step-by-step instructions for each exercise. So, let’s dive right…
Read More