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.
Named Ranges
We all love Excel, don’t we? But it’s just not that simple when it comes to creating a 4-4-5 Financial Calendar! Got questions about how it’s done? You’re not alone! Today, we will walk you through the creation of a 4-4-5 (4 weeks, 4 weeks, and 5 weeks) calendar in Excel. By the end of…
Read More
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 More
Today, 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 More
Keeping your records organized is essential whether you’re running a business or taking care of the household. These days, there are a ton of apps you can use to keep track of daily to-dos, but they just can’t quite compare to the customization abilities and advanced features of Excel. One of those features is the…
Read More
Hyperlinks are a useful way to expedite navigation. If you have ever created a hyperlink to a worksheet, you may have noticed that your hyperlink can break if you change the worksheet name. This post demonstrates how to use defined names to avoid this type of error. Objective Let’s say we want to make it…
Read More
In this post, I’ll answer a question I received from Sara about how to create a drop down with each worksheet name and reference it in a formula to retrieve values from the selected sheet. As this is Excel, there are a variety of alternatives to accomplish this. In this post, I’ll demonstrate one such…
Read More
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…
Read More
The table feature introduced in Excel 2007 is amazing and has nearly eliminated the need to build dynamic named ranges since tables auto-expand. Beyond auto-expansion, tables offer numerous other benefits, including, structured references which allow us to refer to an area within the table, such as a specific column. In this post, we’ll examine a method…
Read More
Have you ever called up the Go To dialog in Excel? There is a little button in it that enables you to go to special places in Excel. This post explores some of these special destinations. For starters, let’s call up the Go To dialog within an empty workbook. There are a few different ways…
Read More