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.

Conditional Formatting

Conditional Formatting With Data Validation

By Jeff Lenning | March 12, 2024 |

In today’s Excel University blog post, we’re going to explore the incredible combination of the Conditional Formatting and Data Validation features in Excel. I was recently asked the following question: How do I highlight matching customers on one worksheet based on the customer selected in a data validation drop-down list on another? And I’ll answer…

Read More

Excel How to Conditionally Format Variances

By Jeff Lenning | March 5, 2024 |

Today, we’re diving into conditional formatting rules. Why? Well, to answer a question I received recently, which is basically: “I want a cell to be conditionally formatted based on the value in another cell where the criteria is: if cell C7 is up to 10% less than D7 then have a yellow color and if…

Read More

Highlight Values Found Multiple Times

By Jeff Lenning | February 27, 2024 |

Today, we’ll highlight recurring values within a dataset in order to answer a recent question. I was asked the following “I’m trying to get it to recognize if a specific letter, like X, is in a column at least three times. What could the formula look like?” And I’ll answer that question in this post.…

Read More

Highlight Correct Answer with Conditional Formatting

By Jeff Lenning | February 6, 2024 |

Time to walk through another exciting Excel adventure 🙂 Today, we’re zooming in on an interesting question I recently received: “How can I highlight correct answers on an Excel worksheet for my granddaughter’s multiplication tables?” Sounds like a marvelously practical application of Excel, doesn’t it? Well, let’s dive in and find out how we can…

Read More

Dynamic Pivot-style Report with GROUPBY

By Jeff Lenning | January 9, 2024 |

In this tutorial, we will learn how to use the GROUPBY function to create dynamic pivot-style reports with a single formula. Let’s back up a sec. For years, we’ve turned to PivotTables to build summary reports. Now we have another option: the GROUPBY function. While it doesn’t create a true PivotTable, it does create a…

Read More

How to Find Invalid Characters

By Jeff Lenning | January 2, 2024 |

Welcome to this tutorial on how to find invalid characters in Microsoft Excel. In this tutorial, we will explore different methods including formulas and conditional formatting to detect and highlight characters you define as “invalid” in your Excel worksheets. Since you can easily customize the list of “invalid” characters, this approach is extremely flexible and…

Read More

Checkboxes

By Jeff Lenning | December 26, 2023 |

The goal of this tutorial is to learn how to insert checkboxes in Microsoft Excel using two different methods: checkbox cell controls and legacy form controls. We will also explore how to use checkboxes in formulas, conditional formatting, and how to activate the developer tab for legacy form controls. Video Step-by-step Exercise 1: Inserting Checkboxes…

Read More

Count and Sum Negative Numbers

By Jeff Lenning | November 28, 2023 |

Welcome to our tutorial on how to count and sum negative numbers in Excel! We will cover three exercises where we will learn how to utilize COUNTIF, SUMIF, and conditional formatting to count, sum, and identify negative transactions. By the end, you will have multiple techniques to operate on negative numbers in Excel. Let’s jump…

Read More

Find Cells That Contain Specific Text

By Jeff Lenning | November 14, 2023 |

The objective of this tutorial is to learn how to write formulas in Excel that can identify specific text strings within cells. We will start by using the SEARCH function to find cells that contain a specific word, then convert the results into TRUE / FALSE values, and finally apply conditional formatting to highlight these…

Read More

Identify the Smallest Values in an Excel List

By Jeff Lenning | October 31, 2023 |

The goal of this tutorial is to provide step-by-step instructions on how to use various methods to extract or identify the smallest values in a list. We will cover three exercises that include the SMALL function, the SEQUENCE function, and conditional formatting. Video Walkthrough Let’s take each of these methods one at a time. We’ll…

Read More