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
I was recently asked how to separate multiple column headers from their values. My favorite tool for transforming data like this is Power Query. So, this post walks through the steps with Power Query. Thanks Ron for your question! Objective Before we get into the steps, let’s visualize the start and end points. Ron states…
Read More
This is the final post in the Slow to Fast series, where we are incrementally improving the efficiency of reconciliations. In the first post, we looked at conditional formatting. This was a great option when the lists were relatively short, stored on the same worksheet, and only required us to look at a single column.…
Read More
This is the second post in the Slow to Fast series, where we are taking incremental steps to improving our reconciliations. In the first post we improved a purely manual process with conditional formatting. That approach works when the lists are relatively small, on the same sheet, and when we are only concerned with a…
Read More
This is the first post in the Slow to Fast series, where we will incrementally improve the task of comparing two lists (commonly called a reconciliation). The illustration I’ll use is a bank reconciliation. We want to compare the list of checks in our accounting system with the list of checks from our bank download.…
Read More
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
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
The family of IFS functions, such as SUMIFS and COUNTIFS, use AND logic when evaluating multiple conditions. In other words, all conditions must be true to be included. And generally, this logic works well. However, there are situations when we’d like to use OR logic instead. That is, any of the conditions can be true…
Read More
John asked if there was a way to create a list of all table names in the workbook, along with the underlying table reference. In this post, I’ll demonstrate how this can be accomplished with Power Query and an Excel formula. Objective Before we get into the mechanics, let’s confirm our goal here. We have…
Read More
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
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