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

Automate File Cleanup

By Jeff Lenning | June 18, 2024 |

I was recently asked the following question: I download an excel file every week and make changes like deleting columns, counting and adding rows and so on. Can I automate this repetitive task ? In this post, I’ll attempt to answer this question. The built-in Excel feature we’ll use to accomplish this is Power Query.…

Read More

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

How To Group Rows (comma list of values)

By Jeff Lenning | May 7, 2024 |

Hello and welcome to our post on grouping rows in Excel. We will walk through the process of combining the values in multiple rows into a single cell (comma-separated list of values). The example we’ll use to demonstrate the steps is to combine multiple email addresses for each contact into a single cell. But, this…

Read More

Insert a Hyperlink

By Jeff Lenning | April 30, 2024 |

One of the highly effective ways to boost navigation in your workbooks is by inserting hyperlinks. You can create links in your Excel workbooks that link to files of any type, including PDF files, Excel workbooks, Word documents, images, and even specific cells in a workbook. When you click on a link to a non-Excel…

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