Company & Product
Nov 30, 2022

3 Easy Ways to Delete Rows From Big Data Files

While columns could be deleted in Gigasheet, rows were hard to get rid of. Until now! Introducing not just one, but three easy ways to delete rows from your data.

Previously, deleting rows required a work around: unwanted rows could be filtered out and then Save As a new sheet would create a file with only the desired data. While this cumbersome process worked, it caused unnecessary steps and created clutter in users' Library. At Gigasheet, our goal is to make exploring and analyzing data easy.

To that end, we are introducing three ways to quickly and easily delete rows from your data.  In this blog post, we are going to explore the following ways to delete rows:

  1. Select data and individually Delete Row
  2. Filter data and Delete Matching Rows
  3. Filter data and Delete Excluded Rows

Select Data and Individually Delete Row

The quickest way to delete rows is to simply select bad data and delete the row that you no longer need. This is convenient for one-off data cleanup, which is nice because it seems like there is always a row here or there with bad data. The Delete Row function works one row at a time but can be repeated to delete multiple rows.

In this example file, we see rows 4, 5, and 6 literally have "Bad Data", which does not belong in this list of Olympic Medalists. By using Shift and clicking, I have highlighted the 3 values that do not belong in the data set for illustration purposes.

Selected Bad Data in our Data Set

Select the first "Bad Data" point for deletion. Once selected, there are two ways to perform the delete action. You can either choose Data Cleanup -> Delete Row,

Accessing the Delete Row Function from the Data Cleanup Menu

or simply right click and select Delete Row.

Accessing the Delete Row Function from the Right Click Menu

Whichever way you select Delete Row, the outcome will be the same. Gigasheet will first ask you if you are sure you want to delete the rows, because the delete operation cannot be undone. In this case, we are sure, so hit Confirm.

Confirm Deletion of the row in Gigasheet

The first row containing the "Bad Data" will be removed. Repeat these steps for the other two rows containing "Bad Data" and the file will be ready for analysis. Here is the cleaned-up file. No more "Bad Data"!

Clean data after using the Delete Row Function

Filter Data and Delete Matching Rows

Delete Row is convenient for cleaning up errors that you find as you explore a file. However, what if there are huge sections of data that need to be removed?

In that case, it's best to use the two Delete Rows functions that take advantage of Gigasheet's powerful Filters. Both functions allow us to delete millions of rows at a time, leaving us with a file that contains only the desired data.

Returning to the Olympic Medalists file, let's group the data by "/year" to see what exactly is in the file. Our data set contains records for the 2000 through 2012 Olympic games.

Deleting Multiple Rows using the Delete Matching Rows function

Let's say that we only want to keep data from 2010 and 2012. To do this, we will need to delete the rows from the 2000 through 2008 games. That would take too long to do individually, so let's use filters!

Create a filter to select the games from the 2000's by choosing "/year" equals and typing in each of the years. Gigasheet's Auto-Complete feature will suggest values as you type, and selections will turn blue.

Selecting the filter criteria for rows to delete

Once filtered, I now only see data from the 2000's. Delete Matching Rows, this is a good way to preview and validate what exactly is going to be deleted. Explore the current result set to confirm what will be removed. The data can remain Grouped, or you can remove the Groups to page through the individual fields.

I can quickly confirm that the result set is from the 2000's, and the desired 2010 and 2012 values are not present. Thus, I am comfortable moving forward with the deletion.

The result set from the filter

To do so, click the Data Cleanup Menu and select Delete Matching Rows.

Calling the Delete Matching Rows function from the Data Cleanup Menu

Hit Confirm to make the magic happen!

Confirming deletion using the Delete Matching Rows function

Gigasheet will delete the matching rows and return the remaining data. We are left with only data from the 2010 and 2012 Olympic games!

The filter that we created to select rows for deletion will be removed since those rows no longer exist in our file. If your data was grouped, it will remain grouped. Deletion has never been so easy!

Results after using Delete Matching Rows

As you can see, Delete Matching Rows, is a good way to preview and then delete multiple rows of data.

Filter Data and Delete Excluded Rows

Now that we have seen the power of deleting multiple rows using filters, let's explore the Delete Excluded Rows function. This performs exactly like Delete Matching Rows but deletes the inverse data set. Instead of deleting the filter result set, it deletes the rows that did not match the criteria for the filter.

Said differently, Delete Excluded Rows keeps the filtered data and drops everything else. Let's see it in action!

Continuing with our Olympic medalist data set, suppose we are only interested in Swimming. We can group by "/sport" and see that there is data from other sports that will need to be removed.

Data set for Delete Excluded Rows function

Just like above, start by creating a filter on just Swimming and hit Apply.

Creating a filter to narrow down the data

This leaves us with just Swimming data.

Filter results with only swimming data visible

Now we will drop the rest of the data by Delete Excluded Rows which is found in the Data Cleanup menu.

Calling the Delete Excluded Rows function from the Data Cleanup Menu

We will once again Confirm that we are ready to process the deletion.

Confirm deletion with the Delete Excluded Rows function

Just like that, rows are deleted, and the filter is removed since it is no longer needed. The data that was excluded by the filter has been deleted. We are left with only 124 rows of Swimming data.

Results after deletion, still grouped

Remove the group to see the raw data. We only have swimming medals from the 2010 and 2012 games.

Raw data after deletion

As we have demonstrated, the Delete Excluded Rows function is a fantastic way to keep only the current result set created by a filter. All the excluded data will be deleted.

Conclusions

Data is never clean, and the first step of data analysis is preparation. Luckily, Gigasheet has created 3 very useful ways to delete rows:

  1. Delete Row - is a convenient way to perform one-off data cleanup. Select an individual row with "Bad Data" and delete it forever.
  2. Delete Matching Rows - uses filters to delete rows in bulk, allowing you to preview the data selected for deletion.
  3. Delete Matching Rows - uses filters to delete rows in bulk, keeping only the current result set created by a filter.

The best news is that Gigasheet is free forever so you can use these powerful tools right now.

Create an account and give it a try!

The ease of a spreadsheet with the power of a database, at cloud scale.

No Code
No Database
No Training
Sign Up, Free

Similar posts

By using this website, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. View our Privacy Policy for more information.