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.

Functions

Replace 25+ Nested IFs with a Single Simple Lookup

By Jeff Lenning | June 11, 2024 |

I was recently asked the following question: “I have a drop-down with 25+ categories that determines the fee percentage. Can I use an IF function to compute the fee based on the selected category?” I answer this question in this post. Video Objective Let’s begin by understanding the objective and the question. There is an…

Read More

Get Last Data Row from PivotTable

By Jeff Lenning | June 4, 2024 |

The post was written to answer a question I recently received: How can we retrieve the last row from a PivotTable? Buckle up as we solve this challenge by nesting two Excel functions into a single formula. Video Step-by-step Let’s visualize the essenence of the question. We have a PivotTable in our worksheet, perhaps something…

Read More

Convert Text to Number

By Jeff Lenning | May 28, 2024 |

Have you ever been baffled by Excel’s handling of numbers? Are you tired of grappling with non-cooperative imported data that insists on being treated as text rather than numbers? We can relate to that, and we’re here to walk through this process with you. Video Step-by-step Let’s unravel the mystery of converting text to numbers…

Read More

Update Subsequent (but not previous) Worksheets

By Jeff Lenning | May 21, 2024 |

Whether you’re managing a budget for various departments or tracking volunteers throughout the year, using multiple worksheets in a workbook is very common. I was recently asked a question about how to get a change made in one sheet to flow to subsequent sheets, but not to previous sheets. In this post, I provide a…

Read More

Dynamic Conditional Formatting for Top 3 Values

By Jeff Lenning | May 14, 2024 |

I recently received a question: “Can I format a cell based on another cell value, and have the formatting update automatically whenever the cell value changes?” Then answer is “yes” and this blog post demonstrates the steps. To illustrate the steps, we’ll dynamically highlight the top three scores in a list … and as the…

Read More

Excel Tips from 19 Experts

By Jeff Lenning | April 23, 2024 |

My friend John Michaloudis does an annual survey of Excel MVPs and experts and asks for their best Excel 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…

Read More

Creating a 4-4-5 Financial Calendar

By Jeff Lenning | April 16, 2024 |

We all love Excel, don’t we? But it’s just not that simple when it comes to creating a 4-4-5 Financial Calendar! Got questions about how it’s done? You’re not alone! Today, we will walk you through the creation of a 4-4-5 (4 weeks, 4 weeks, and 5 weeks) calendar in Excel. By the end of…

Read More

How to Launder Money with Excel (Benford’s Law)

By Jeff Lenning | April 9, 2024 |

Hello and welcome to another informative blog post from Excel University. Today, we’re diving into a somewhat unusual topic – using Excel to launder money. If you aren’t familiar with that term, it simply means taking cash received from an illegal enterprise and running it through a legitimate business. That way, the money appears as…

Read More

Create Hyperlinks to Files in a Folder

By Jeff Lenning | March 26, 2024 |

Today, we’re diving into an exciting way to enhance your data organization skills using Excel. I was recently asked the following question: Is there a way to create a list of files so that the files in the list could be hyperlinked back to the source in the folder? I’m going to answer that question…

Read More

Conditional Summing Tricks

By Jeff Lenning | March 19, 2024 |

Learn how to handle conditional summing in Excel with these amazing but easy-to-understand tricks. These effective strategies will level-up your understanding and management of Excel functionalities.

Read More