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
My friend John Michaloudis does an annual survey of Excel MVPs and experts and asks for their best tip. He compiles the tips into an audio podcast along with show notes and related links. This is a great resource and I love sharing it every year. This year, he rounded up tips from: You’ll definitely…
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…
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…
This post show how to use Power Query instead of a PivotTable to display text values in a report.
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…
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…
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…
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!…
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/
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…