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:
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.
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,
or simply right click and select Delete Row.
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.
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"!
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.
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.
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.
To do so, click the Data Cleanup Menu and select Delete Matching Rows.
Hit Confirm to make the magic happen!
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!
As you can see, Delete Matching Rows, is a good way to preview and then delete multiple rows of data.
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.
Just like above, start by creating a filter on just Swimming and hit Apply.
This leaves us with just Swimming data.
Now we will drop the rest of the data by Delete Excluded Rows which is found in the Data Cleanup menu.
We will once again Confirm that we are ready to process the deletion.
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.
Remove the group to see the raw data. We only have swimming medals from the 2010 and 2012 games.
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.
Data is never clean, and the first step of data analysis is preparation. Luckily, Gigasheet has created 3 very useful ways to delete rows:
The best news is that Gigasheet is free forever so you can use these powerful tools right now.