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

Excel University Volume 2 Now Available!

By Jeff Lenning | October 7, 2013 |

I am pleased to announce the immediate availability of Excel University Volume 2. The next installment in the series covers a task common to accounting and finance professionals: reporting. Since there are many different types of reports in Excel, let’s dig into the details a bit. This book focuses on how to build hands-free formula-based…

Read More

Excel Formula to Allocate an Amount into Monthly Columns

By Jeff Lenning | September 26, 2013 |

If you have ever needed to allocate an amount over time, and split the amount into monthly columns based on the number of days, you’ll quickly realize that this simple idea is tricky to actually implement. As with anything in Excel, there are many ways to accomplish this task, and this post walks through one…

Read More

How to Return a Value Left of VLOOKUP’s Lookup Column

By Jeff Lenning | August 29, 2013 |

If you have ever tried to return a value that physically lies to the left of the lookup column, you quickly realize that this task is difficult to accomplish with the VLOOKUP function.  This is a perfect time to move beyond the VLOOKUP function and explore the INDEX and MATCH functions. Note: depending on your…

Read More

FlashFill is an Alternative to CONCATENATE and Text-to-Columns

By Jeff Lenning | August 15, 2013 |

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…

Read More

Pick the Math with Excel’s CHOOSE Function

By Jeff Lenning | August 8, 2013 |

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…

Read More

Excel University Interactive – Now Available!

By Jeff Lenning | July 26, 2013 |

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…

Read More

How to Flatten, Repeat, and Fill Labels Down in Excel

By Jeff Lenning | July 25, 2013 |

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…

Read More

Custom Conditional Formatting Rules

By Jeff Lenning | July 11, 2013 |

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…

Read More

Excel University – Now Available on iTunes

By Jeff Lenning | July 5, 2013 |

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…

Read More

Outline Groups

By Jeff Lenning | June 27, 2013 |

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…

Read More