Look Who it Was Random video

Excel - When a Long Cell Spills over and obscures cells to the right - Episode 616

520 views · Published 31 March 2009 · 3:32 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: When a Long Cell Spills over and obscures cells to the right.

Welcome back to the MrExcel netcast! In today's episode, we have a question from Ethan about spilling cells in Excel. He's wondering if there's a way to display data in Column B without obscuring the formula in Column C when the data is too long. After trying out a few solutions, it seems like the only way to achieve this is through a macro. Let's take a closer look at Ethan's problem and how we can solve it.

Ethan's data in Column B sometimes spills over into Column C, where there is a formula pulling a value. However, when the data is too long, it obscures the formula in Column C, making it difficult to see the results. Ethan is wondering if there's a way to use an IF function to display nothing in Column C when the data in Column B is too long. Unfortunately, this solution doesn't work as Excel only allows Column B to spill over when Column C is empty. While there is a complicated workaround using the camera tool, it would be much easier to use a macro to solve this problem.

So, let's dive into the macro solution. We'll need to loop through all the data in Column B and if the data is longer than a certain length, we'll clear the formula in Column C. This will allow the data in Column B to spill over without obscuring the formula. To do this, we'll use the macro editor and insert a module. Then, we'll write a simple macro that checks the length of each cell in Column B and clears the formula in Column C if it's longer than a specified length. This will allow the data to spill over and be visible in Column C.

With just a few lines of code, we've solved Ethan's problem! This is a great example of how the macro language in Excel can help us solve practical problems that would be very difficult to do manually. So, thank you Ethan for sending in your question and thank you for tuning in to another netcast from MrExcel. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Ethan sends in a question about optionally allowing long cell values to spill over into the next column. Episode 616 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Table of Contents:
(00:00) Introduction
(00:17) Ethan's Question
(00:27) Problem with Column B Data
(00:37) Solution Attempt with IF Function
(01:02) Difficulty with Empty Cells
(01:41) Using a Macro to Solve the Problem
(02:07) Step-by-Step Guide to the Macro
(02:43) Running the Macro
(03:01) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Camera tool
Clear formula in column C
Column B data
Data length in column B
Empty cell in column C
End of data in column B
Formula in column C
IF function
Loop through data in column B
Macro solution
Run macro
Spilling over into column C


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152322/

More from this channel