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.

Functions

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 2013 Highlights

By Jeff Lenning | May 2, 2013 |

Microsoft Excel 2013 offers many new features and functions worth exploring. This post provides a few highlights and a link to my May-2013 CalCPA Technology and Business Resource Guide article, “Spreadsheet Nerdiness” which goes into more detail. Summary Design With the exception of some minor cosmetic changes and some animations, the design is similar to…

Read More

Perform Approximate Match and Fuzzy Lookups in Excel

By Jeff Lenning | April 11, 2013 |

This post explores Excel’s lookup functions, approximate matches, fuzzy lookups, and exact matches.  The built-in Excel lookup functions, such as VLOOKUP, are amazing. When implemented in the right way for special projects or in recurring use workbooks, they are able to save a ton of time. The VLOOKUP function alone has saved countless hours in…

Read More

Multiple Condition Summing in Excel with SUMIFS

By Jeff Lenning | March 15, 2013 |

The SUMIFS function is for sure, without a doubt, one of my most favorite Excel functions of all time.  I’m sure I use it in well over half of my workbooks, and it is a must-know function for Excel users.  I’ll try to get you warmed up, but just know that this post only gets…

Read More

Compute the Last Day of the Month in Excel

By Jeff Lenning | March 7, 2013 |

As you probably know, I like converting manual input cells to formula cells in Excel. To me, the more I can delegate to Excel the better off I am.  Some workbooks can benefit by automatically computing the last day of the month, eliminating one more manual input cell. As you also know, it seems that…

Read More

Create an Index Worksheet using Excel Hyperlinks

By Jeff Lenning | February 21, 2013 |

Have you ever worked in a workbook with only a few worksheets…say three or four sheets? Navigating to the correct sheet is fast and easy, and you spend very little time getting to the spot you need to be to do your work. Have you ever worked in a workbook with more sheets? Say, 10,…

Read More

Calculate the Payment of a Loan with the PMT Function in Excel

By Jeff Lenning | February 14, 2013 |

Thanks to Excel’s PMT function, computing the monthly payment of a loan with various terms is easy. In summary, you provide the function with the basic loan information, including the loan amount, the interest rate, and the term, and the function will compute the payment. You provide this basic loan information to the function through…

Read More