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
In today’s Excel University blog post, we’re going to explore the incredible combination of the Conditional Formatting and Data Validation features in Excel. I was recently asked the following question: How do I highlight matching customers on one worksheet based on the customer selected in a data validation drop-down list on another? And I’ll answer…
Read MoreToday, we’re diving into conditional formatting rules. Why? Well, to answer a question I received recently, which is basically: “I want a cell to be conditionally formatted based on the value in another cell where the criteria is: if cell C7 is up to 10% less than D7 then have a yellow color and if…
Read MoreToday, we’ll highlight recurring values within a dataset in order to answer a recent question. I was asked the following “I’m trying to get it to recognize if a specific letter, like X, is in a column at least three times. What could the formula look like?” And I’ll answer that question in this post.…
Read MoreToday, we’re answering a question we received about how to combine multiple Excel workbooks into a single workbook, even when there are a variable number of worksheets within each workbook. This post shows how to use Power Query to accomplish it. Let’s jump right in! Video Classic Problem, User-friendly Solution In handling large datasets or…
Read MoreHello 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 MoreTime 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 MoreIn 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 MoreIn 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 MoreExcel’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 MoreIn 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