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

Volume 4 Available!

By Jeff Lenning | December 18, 2016 |

Alright…Excel University Volume 4 is available! Well, this volume has taken two years to create, but, I believe the time spent was well worth it. It is, without a doubt, my favorite volume in the Excel University series. We mainly focus on internal-use workpapers, and how to get them done the fast way! I think…

Read More
Excel time zones by Jeff Lenning

Add and Subtract Hours

By Jeff Lenning | December 1, 2016 |

I was recently working on a workbook, and had to convert an event time from one time zone to another. Basically, I wanted to enter the time of day into a cell, and then I needed a formula to compute the event time in a different time zone. This post walks through the formula to…

Read More

Data Validation with ALL CAPS

By Jeff Lenning | November 10, 2016 |

In recent back-to-back webinars, two different attendees asked how to check to see if a user entered a text string with all caps. Specifically, they asked how to set up a data validation rule to determine if all letters are capitalized. So, I figured I would write a quick post about the technique. Objective Let’s just take a moment…

Read More

Daily Bank Balance with a PivotTable

By Jeff Lenning | October 20, 2016 |

When you download banking activity, you typically get the transactions, but not the daily bank balance. To compute the daily bank balance, you need to summarize the transactions by day and display a running total for all days, even those without any activity. This post demonstrates how to build such a report using a PivotTable. Overview…

Read More

Three Excel University Announcements

By Jeff Lenning | October 13, 2016 |

I have three exciting Excel University announcements! They are about QBConnect, Top 10 CPE Courses, and Volume 4. First up, QBConnect! QBConnect I’ll be presenting an Excel session at the QBConnect conference in San Jose, and if you are going, I’d love to see you there! The title of the session is Excel: The Power…

Read More

Get & Transform Article

By Jeff Lenning | September 8, 2016 |

If you haven’t played with the Get & Transform commands in Excel 2016 for Windows, they’re probably worth checking out. I don’t want to sound overly dramatic here, but this set of capabilities is a game changer. These tools provide new ways to approach tasks, and enable us to do things that were previously time-consuming, impractical or required macros. If you’d like to…

Read More
Combine binaries by Jeff Lenning

Get & Transform: An Alternative to Copy Paste Append

By Jeff Lenning | September 1, 2016 |

Let’s say you have several data tables, and you need to combine them into a single table. One option would be to copy and paste to append them. But, depending on how many data tables there are, this type of manual process can be tedious. In this post, we’ll use a Get & Transform query…

Read More

Get & Transform: An Alternative to VLOOKUP List Comparisons

By Jeff Lenning | August 18, 2016 |

I recently received an Excel question about how to perform a specific list comparison, and I thought I’d demonstrate how to use a Get & Transform query as an alternative to the formula-based list comparisons typically performed with functions such as VLOOKUP and COUNTIFS. The original question is: “I am trying to use a list…

Read More

Get & Transform: An Alternative to Finding the Last Delimiter with Formulas

By Jeff Lenning | July 28, 2016 |

Sometimes, our data has multiple delimiters. Finding the first delimiter with formulas has been pretty easy over the years. Finding the final delimiter hasn’t been as easy…until now. In this post, we’ll use a Get & Transform query to grab the characters after the last delimiter. Objective Before we jump into the mechanics, let’s be clear about…

Read More

2016 Scholarship Winners

By Jeff Lenning | July 18, 2016 |

It is with great pleasure that I’m able to announce this year’s Excel University scholarship winners! The scholarship is supported by Excel University enrollments, so, thanks to all of you who have helped to make this possible. Both of these winners are wonderful examples of the type of accounting student that the Excel University scholarship was…

Read More