Excel crashes or freezes when highlighting cells in big datasets: reasons and solutions

Why Excel hangs or crashes when selecting cells in big files?

No one, not even Microsoft, can tell you the exact reason why Excel freezes and stops working when selecting large amounts of data at once. However, here's a typical scenario that many Excel users have run into:

You begin by searching for specific values in your Excel workbook using Excel's Find tool. The search itself is usually fast and efficient, even in massive files. After performing the search, you get a list of records that meet your search criteria. The goal seems to be almost reached. The problem arises when you attempt to select the found cells. At this point, Excel at best just freezes, and at worst, after hanging for some time, crashes unexpectedly.

An incredibly frustrating outcome, especially if you haven't saved your most recent changes in the workbook before starting the search :( Fortunately, there are solutions to prevent Excel from hanging in such situations.

How to avoid Excel freezing and crashing when highlighting cells

The key to avoiding Excel crashing when selecting cells in big datasets is to use conditional formatting instead of Excel Find to highlight cells based on certain criteria. Here's a step-by-step guide:

Step 1: Highlight cells. Begin by creating a new conditional formatting rule to highlight cells that match your criteria. For example, let's highlight all cells containing an asterisk (*).

Step 2: Group found cells. This needs to be done to prevent Excel from crashing when selecting the found data in the next step. You can group cells by sorting or filtering by color.

Here's how to sort data by color:

1. On the Home tab, click Sort & Filter and choose the Custom Sort… option. The Sort dialog window will appear.
2. In the Column drop-down list, select the target column.
3. In the Sort On drop-down list, choose Cell Color.
4. In the Order drop-down lists, set the color and sort order. We recommend selecting On bottom as it makes the selection process easier in the next step.

If sorting by color is not suitable for your data for any reason, you can achieve a similar result using the filter by color option:

1. Add an auto-filter to your dataset.
2. Click the filter icon in the target column.
3. In the menu that opens, click Filter by Color and pick the color you used for conditional formatting.

Step 3: Make your selection. With your data neatly sorted or filtered by color and the desired values grouped, you can now effortlessly select them or perform various actions like copying, formatting, or transferring them to another worksheet without the worry of Excel crashing.

For instance, to select colored cells sorted at the bottom of the column, just click on the first colored cell and press the Ctrl + Shift + End key combination to extend your selection to the last used cell.

If your values are filtered by color, the process is even simpler – as only colored cells are visible, you can select them using your mouse as you normally do.

Crash-free way to select cells in big worksheets with different colors

The Conditional Formatting method works nicely when your dataset contains only one color for highlighting. But what if your worksheet is already a colorful canvas with various shades? How can you find the specific values you need within this mosaic of colors? Fear not, Ablebits Ultimate Suite offers a few smart tools to tackle this challenge. Below, you’ll find the detailed guidance on utilizing each tool.

Find target data using Substring Tools

The Ultimate Suite for Excel offers a set of useful features for manipulating strings and substrings. The Extract Substrings option is particularly useful when you need to find and extract all values containing a specific character or text (substring). Here's how to do it:

1. Enter your search criteria in an empty cell(s) on the current sheet or a new one.
2. On the Ablebits Data tab, in the Text group, pick the Extract Substrings option from the Substring Tools menu.
3. In the Source column box, specify the range where you want to perform the search.
4. In the Substring range box, specify the cell or range containing your search criteria.
5. If necessary, add a delimiter in the Split with box.
6. For case-sensitive search, check the Case-sensitive checkbox.
7. Click the Extract button.

All the found values (asterisks in our case) are now in a separate column. To further analyze your original data, simply sort or filter your dataset based on that helper column.

Find data at certain positions in strings using Regex Tools

For those comfortable with regular expressions, our Regex Tools are a boon. And for newcomers, we've created an Excel Regex cheat sheet to ease the learning curve.

The regular expression language is exceptionally versatile, allowing you to make patterns not just for finding all asterisks, but also for matching only those asterisks positioned at particular locations within a string.

To find data matching a certain pattern, follow these steps:

1. Navigate to the Ablebits tab and, in the Text group, click on Regex Tools.
2. Select a column to search for values. The selected range will be displayed in the topmost box on the Regex Tools pane.
3. In the Regex box, enter your regular expression. For example, to match asterisks located in various positions in a string, you can use these patterns:
   • ^[*] - matches an asterisk at the beginning of the string.
   • .+[*].+ - matches an asterisk in the middle of the string.
   • [*]$ - matches an asterisk at the end of the string.
   • [*] - matches an asterisk in any position.
4. Choose either the Match or Extract option. The former will mark the records that match / do not match the pattern with the TRUE / FALSE labels, while the latter will extract the matching values into a separate column.
5. Click the Extract button.

That's it! With the extracted asterisks in a separate column, you can easily sort or filter your data based on this new column, simplifying further processing of the original data.

For more examples of using this tool, see How to extract strings in Excel with regexes.

Available downloads

Ultimate Suite 14-day fully-functional version (.exe file)