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.

Techniques

Convert Foreign Currencies to USD

By Penny Li | January 4, 2022 |

My name is Penny Li, a cross-border tax accountant, and I often come across instances in my work where I need to convert foreign currencies to US dollar. I have decided to build a foreign currency conversion tool in Excel that is capable of generating single-day as well as average foreign exchange rates for multiple…

Read More

Excel for Budgeting

By Moss Levenson | November 23, 2021 |

Overview During an Excel University webinar, Jeff gave a demonstration of Power Query that related to a company’s annual budget process. Initially, I was not familiar with Power Query but decided to learn it. Ultimately, I was able to incorporate Power Query into our annual budget process, eliminating the need for the software we were…

Read More

PivotTable Text Values Alternative

By Jeff Lenning | September 28, 2021 |

This post show how to use Power Query instead of a PivotTable to display text values in a report.

Read More

Mapping Table without a Helper Column

By Jeff Lenning | September 7, 2021 |

A mapping table is a handy way to automatically translate labels between systems and reports. As with just about anything in Excel, there are many ways to implement a mapping table. For example, we could create a helper column to store the amounts with the SUMIFS function as I discussed in this Journal of Accountancy…

Read More

Speed Loop in Action

By Larry Peck | August 17, 2021 |

As a motorcycle safety instructor, I tell new riders that learning to ride a motorcycle is like showering. One shower does not keep you fresh and clean the rest of your life, just like taking one motorcycle class does not give you everything you need to stay safe on a motorcycle. You need to practice…

Read More

Pivot Text Values with Power Query

By Jeff Lenning | March 23, 2021 |

In this post, we’ll talk about how to Pivot text values with Power Query. We would like to do this even if there are a variable number of fields per record. This can be REALLY hard to visualize based on a short text description, so I think it is best to show screenshots of the…

Read More

Automatically Format Rows

By Jeff Lenning | January 26, 2021 |

I was recently asked how to set up conditional formatting to format a row based on the value in a column. In this post, I’ll demonstrate how to create a conditional formatting rule that automatically formats a row based on a single column value for all rows in the worksheet. Thanks Karla for your question!…

Read More

Tips from 31 Excel Experts

By Jeff Lenning | December 28, 2020 |

My friend John has put together his annual podcast featuring tips from 31 Excel experts. It is an astounding collection and I hope you get a chance to check it out! https://www.myexcelonline.com/podcast/028-the-best-microsoft-excel-tips-tricks-in-2020/

Read More

Power Query from Google Drive

By Jeff Lenning | December 2, 2020 |

These days, it is common to use online services from many providers. I’m sure that I use services from a dozen different providers every day. One of the services I use is Google Drive, and I have a variety of documents stored there. One day, I was in Excel and thinking to myself, “I wonder…

Read More

Excel Calendar Table

By Jeff Lenning | November 23, 2020 |

When you need to summarize values by date groups, a calendar table can be helpful. While Power Pivot has a button that will automatically create a new date table (shown in this post), Excel doesn’t have a similar command. So, in a recent Q&A session, Michael asked how to get that Power Pivot calendar table…

Read More