Deletion tools

How do you delete unnecessary data in Excel tables in bulk? To answer this, first figure out what the data you want to delete consists of and what it has in common.

What data can you delete in Excel

The data to delete can be defined by:

• the Excel object model: workbooks, sheets, columns, rows, cells, formulas, cell text, cell formatting;
• the data type: numbers, text, dates, times, percentages;
• the language model: characters, words, phrases, sentences, paragraphs;
• the attributes of the objects themselves: content (empty or not), uniqueness (unique or repeated), formatting (font, style, color, fill).

And these are probably not all the ways to define data before deleting it.

For many of these cases, Excel has built-in tools. First of all, I recommend reading how Find and Replace lets you find the extra data in bulk before you select and delete it. Replacing with nothing is also a way of deleting.

Deleting data with !SEMTools

Excel doesn’t always have simple and convenient tools for bulk deletion: many tasks require complex formulas, macros or manual work. For such cases I made my own procedures and organized them in a clear hierarchy in the DELETE group of my !SEMTools add-in. Select the range, choose what to delete, and the data changes right in place, without formulas or helper columns.

Before you apply them, I recommend using the similar options in the FIND and EXTRACT groups to see with your own eyes which elements will be deleted.

Delete characters

The Characters menu removes certain characters from a range, for example:

• remove numbers from text
• remove punctuation
• remove spaces at the beginning or end of cells
• remove a number of characters from the beginning
• remove N characters at the end of a cell
• remove text up to a certain character
• remove text after a certain character

Delete words

Single words or a whole list: Excel doesn’t know what words are, there is no such entity in its syntax. !SEMTools does, so you can delete words from text in Excel without damaging the rest of the data. For example, remove the first word, the last word, repeated words or a list of words. This is especially useful for keywords, tags and other text datasets.

Delete cells by condition

!SEMTools offers more than a dozen ready-made conditions to delete cells by condition, for example cells with digits. Instead of building formulas, choose the rule and clear out the irrelevant data.

Delete ranges

Beyond cells, the Ranges menu has procedures that delete rows by condition, empty rows (including on several sheets at once) and empty columns. It also has procedures for deleting sheets of open workbooks by condition.

Delete duplicates

This section covers different ways to remove duplicate cells in Excel. Excel has a built-in Remove Duplicates tool, but it treats only completely identical values as duplicates: one extra space, and the cells are no longer considered repeated. Such cells are called implicit duplicates, and !SEMTools has a set of procedures for them. And when you need to remove duplicates without shifting rows, you’d have to use many tricks, and here the add-in helps too.

Delete formulas

Normally, to remove formulas you copy the cells and paste them back as values. With many sheets and complex formulas, this takes a lot of time. See how to remove formulas and keep the values in a range, on a whole sheet or in a whole workbook in a couple of clicks.

Summary: deleting data in Excel

I hope you found the section you need on this page. Before deleting, check what exactly will be removed with the Find or Extract tools, and if you want to keep the source data, use the “output result to the right” checkbox on the !SEMTools panel.