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.

Excel

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

Aggregate with OR Logic

By Jeff Lenning | December 14, 2020 |

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

List of Workbook Tables and References

By Jeff Lenning | December 8, 2020 |

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

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

Dependent Drop Downs with FILTER

By Jeff Lenning | November 17, 2020 |

This post shows how to create multiple dependent drop downs using the FILTER function. These are also known as cascading or conditional drop downs, where the choices in a drop down depend on the selection made in a previous drop down. The technique presented enables you to create as many drop downs as you need,…

Read More

FILTER GOAT CalCPA Article

By Jeff Lenning | November 10, 2020 |

Question: What is the best Excel function? Typical responses are VLOOKUP, INDEX, MATCH, XLOOKUP and SUMIFS, which are among Excel’s highlights. Follow-up question: If there was a function that could do the work of these, would it be the Greatest of All Time (GOAT)? Well, the FILTER function is too new to declare it the…

Read More

Treasure Maps 3

By Jeff Lenning | October 26, 2020 |

This is the third post in the Treasure Maps series where we are talking about various ways to implement mapping tables. In this series, we are using mapping tables to help translate labels between the data and the report. In the first post, we used SUMIFS to pull the values into the mapping table. In…

Read More

Treasure Maps 2

By Jeff Lenning | October 19, 2020 |

This is the second post in the Treasure Maps series, where we are discussing various ways to implement mapping tables. In the first post, we covered SUMIFS. In this post, we’ll talk about Power Query. Overview Let’s say we have a list of transactions in a CSV file, like this: We would like to use…

Read More