Have you ever split one column in to many columns, or vice versa, combined many columns into a single column? If so, you have likely relied on the text-to-columns feature to split a single column into many and the CONCATENATE function to join many columns into a single column. Beginning with Excel 2013, Microsoft provides […]
Have you ever wanted to allow the workbook user to pick the math that a formula should use? This post explores the CHOOSE function, which, among other things, enables us to allow the user to pick the math. CHOOSE Before jumping right into the function’s syntax, let’s get a little context. Let’s say that we […]
I’m so excited to make this announcement….the online training format for Excel University Volume 1 is now open for registration! We have converted the content of Excel University Volume 1 into an interactive self-paced training format, and it is now available through our online learning management system (LMS). If you have ever looked for an […]
In this post, we’ll cover a handy technique for manually filling data labels down through an Excel range. This can also be referred to as repeating the labels, or, as flattening the data. Let’s get into it. Note: An alternative would be to use a Get & Transform query, as discussed in this post. Flattening […]
Today, we will look at using conditional formatting with formulas, data validation, and comparison formulas. Think you are up for it? Let’s do this thing. Let’s say we have a worksheet that computes the payment of a loan. We can use the PMT function to easily accomplish this task. The PMT function requires three arguments […]
Guys, I just wanted to share the good news. Excel University is now available in Apple’s iTunes iBookStore! So far, Excel University Volume 1 Featuring Excel 2010 for Windows is online. Over the next week or two, Excel University Volume 1 Featuring Excel 2013 for Windows should be available. And, beginning with Excel University Volume […]
Let me ask you a question. How do you hide a row or a column? Since this is Excel we are talking about, there are several methods to accomplish this goal. Common Approaches One common approach is to select the row or column, and then select the Hide command, either from the Format Ribbon icon […]
We are getting close to the release of Excel University Volume 2! I’m absolutely excited about this volume. We cover the mechanics for setting up hands-free reports. The manuscript is with the publisher now, going through a round of copy-editing. I expect it to be released in July or August on Amazon. I want to […]
You know I love keyboard shortcuts. I believe that keeping your hands on your keyboard will improve your speed. I recently came across two very cool shortcuts I’d like to share in this post. Precedent First, let’s get a term cleared up. A Precedent is something that comes earlier, or before. In Excel, this idea […]
This post demonstrates how to count the number of distinct (unique) values in an Excel 2013 PivotTable. Prior to Excel 2013, this capability was not built-in to the PivotTable feature. For Excel versions earlier than 2013, there are a variety of different workarounds available, some use VBA code, some use helper formulas, and some of […]