Category Archives: Excel Training

How to Delete Blank Rows in Excel

Quickly and easily delete blank rows and columns in Excel

It’s not uncommon to end up with blank cells or rows in an Excel worksheet. If you need to delete these to maintain accuracy, especially when calculating the average value, the quick way is to filter out all blank cells / rows and delete them with one click.

Choose the column you want to filter, go to Data->Filter, after the downward button shows, undo Select All and then pick up the last option, Blanks. All blank cells will show immediately. Now, the blank cells / rows can be deleted easily.

delete_cells_rows

Delete blank rows in Excel

Another way to delete blank rows is to first select blank cells inside the data using the Go To Special feature. It works as follows:

GTS

This tool is opened by pressing F5 or by clicking Home, Find & Select, Go to Special. In the Go to Special box, select the Blanks option, then click OK. You will notice that all blank cells in and around the data are now selected.

SelectedCells

At this point, to delete the empty rows, go to the Home ribbon and click the drop-down of the Delete button in the Cells group. In the drop-down list, click Delete Sheet Rows.

DSR

The data will now have no blank rows inside.

NBR

Use this method only for simple tables with a couple of dozens of rows that fit within one screen. The main reason is that if a row with important data contains just one blank cell, the entire row will be deleted.

Delete blank rows in Excel using COUNTBLANK

Use this method if you have a table with numerous empty cells scattered across different columns, and you need to delete only those rows that do not have a single cell with data in any column.

1

In this case we do not have a key column that could help us to determine if the row is empty or not. So we add the helper column to the table:

Step 1. Add the “Blanks” column to the end of the table and insert the following formula in first cell of the column: =COUNTBLANK(A2:C2).
This formula, as its name suggests, counts blank cells in the specified range, А2 and C2 are the first and last cell of the current row, respectively.

2

Step 2. Copy the formula throughout the entire column.

3

Step 3. Now we have the key column in our table. Apply a filter to the “Blanks” column to show only rows with the max value (3). Number 3 means that all the cells in a certain row are empty.

4

Step 4. Then select all the filtered rows and remove whole rows .
As a result, the empty row (row 5) is deleted, all the other rows (with and without blank cells) remain in place.

5

Step 5. Now you can remove the helper column. Or you can apply a new filter to the column to show only those rows that have one or more blank cells.
To do this, uncheck the “0” checkbox and click OK.

6

7

 Additional Resources

Deleting rows of Data

Delete cells, rows, or columns

Using the Advanced Filter in Excel

Refine your filter options with the Advanced Filter in Excel

For most filtering of data Excel AutoFilter is perfectly adequate. Here we’ll look at some benefits for moving beyond the AutoFilter by using the advanced filter in Excel.

A Recap of AutoFilter Benefits

For ease of use, it’s hard to beat the Excel AutoFilter feature. Just click the Filter command on the Excel Ribbon’s Data tab, and the filter is ready to go.

Filter

You can click the drop down arrows in the column headings, and use the check boxes to select the items you want in the filter. Or, use the Text, Date or Number Filters commands, for variations on the filters.

Autofilter

Another quick way to filter is to right-click on a cell in the list, then click Filter, and click Filter by Selected Cell’s Value.

Right-click

Benefits of using Advanced Filter in Excel

The Advanced Filter in Excel isn’t quite as easy to use but it does have some benefits that make it worth the effort.

With the Advanced Filter, you can apply multiple filter criteria to the whole database whereas with the other types of filters, you have to filter in a step-wise way, which means that when you have applied one type of filter to the data, there is less data available to run a second and third filter.

After you click the Advanced command on the Excel Ribbon’s Data tab, the Advanced Filter dialogue box opens. Fill in the details, and then click the OK button to filter the data.

advanced_filter_in_excel

Filter Data to Another Sheet

Another reason to use an Advanced Filter, especially when working in Excel, is that it’s a quick and efficient way to copy specific data to a different worksheet.

In the screen shot above, the option is selected to copy the filtered data to another location. You can use that option to quickly create a report for each department, or salesperson, and email the results.

If you select “Filter the list, in-place”, the results will be shown in the database.

Tip: If you wish to remove the filters, you need to click the Clear button in the Data ribbon because the dropdown filter arrows disappear when you apply advanced filters.

Create a List of Unique Items

Excel 2007 introduced the Remove Duplicates feature, but you can still use an Advanced Filter to create lists of unique items. The Remove Duplicates feature strips the duplicates out of the list that is selected, so you have to remember to work on a copy of the list, if you want to keep the original list intact.

With an Advanced Filter, no items are removed from the list, so you don’t risk losing any of your original data.

Complex Filters

With an Advanced Filter, you can create a criteria range on the worksheet, and see at a glance what the filter settings are. You can also create complex filters with AND/OR settings that go beyond what an AutoFilter can do.

For example, with an AutoFilter, you can select two specific customers, and two products for those selected customers.

AuFRes

With an Advanced Filter, you can create OR conditions between columns, such as Customer A OR Product B — you can’t do that in an AutoFilter!

AdFRes

How to create a criteria table for the Advanced Filter

1. Paste a copy of your data table’s header row into an empty section of your spreadsheet or into another blank sheet.

2. Then type in your search criteria below the appropriate headers. If you type criteria next to each other, as in the example below, it performs an AND filter, meaning it will show results meeting ALL requirements.

CritTab1

The criteria above will show people whose surnames start with C AND who work in Development. It will also show people whose surnames start with F AND who work in Production.

If you type criteria on different rows, as in the example below, the criteria will perform an OR filter, meaning it will show results which meet ANY requirement.

CritTab2

Wildcard searches

? (Question mark): Any single character.
For example, “Bl?ck” finds either “Black” or “Block”

* (asterisk): Any number of characters.
For example North* finds “North”, “Northeast”,
and “Northwest”.

*west finds “West”, “Northwest”, Southwest”, etc.

*a* finds the “a” anywhere in the text

Additional resources

The difference between Autofilter and Sort in Excel

Using filters in Excel

Using VBA to filter data

Filter by using advanced criteria

 

 

How to change case text in Excel

Quick and easy ways to change case text in Excel.

You may have a list which you wish to change the case test in Excel. Rather than manually re-entering the data in Excel, there are some useful functions you can call on to save you this work. Let’s look at a few of them.

In this example we have a list of planets starting in lowercase and we wish to have them starting in uppercase. Here we will use the Proper function.

Step 1 Select the adjacent blank cell and type =Proper(A1)

Step 2. Autofill through to B8. You should now have a list of Planets, not planets!

Excel_proper_function

 

Step 3. To replace the original data copy the cells B1:B8 and paste as values back to A1:A8.

Step 4. Finally clear the range B1:B8.

To turn text to upper case repeat the steps above using  the Upper function instead of Proper. And, yes you guessed it, use the Lower function to change test all to lowercase.

Writing a Macro to run the Proper case function

If you need to change the case of text in Excel on a routine basis, you can save even more time by automating the process with a quick macro.

Using a blank Workbook press Alt+F11 to go to the VBA editor.

insert_module_macro
Insert a  new module and type the following 2 macros.

Sub Proper()
Dim cell As Range   ‘cell is a name for the range variable
For Each cell In Selection
cell = WorksheetFunction.Proper(cell)
Next cell
End Sub

Sub Upper()
Dim cell As Range
For Each cell In Selection
cell = UCase(cell)
Next cell
End Sub

Now you can close the “Microsoft Visual Basic for Applications” window (don’t worry your macros are now saved), and return to your spreadsheet.

Save  your spreadsheet as a Macro-Enabled workbook called MyMacros.

You could use the workbook to store other macros for use in any workbook and assign them to buttons on the Quick Access Toolbar.

Finally hide the workbook by selecting View, Hide and exit Excel saving again as MyMacros.

When you return to Excel open MyMacros and add the Proper and Upper macros to the Quick Access Toolbar as follows.

1. Click the arrow on the right of the Quick Access Toolbar.

more_commands_quick_access_toolbar

2. Select More Commands.
3. Choose Macros in the commands from option.
4. Click Proper and click Add, click Upper then Add.
5. Select Modify to change the button images.
6. Change the Display name.
6. Reposition the buttons to suit.

Now exit Excel again and test your macros work by selecting some text.

The hidden MyMacros workbook automatically opens when the Proper Case button is clicked and changes the text accordingly. A press of the button and the work is done, whether for a single cell or for a large range of selected cells in any workbook, saving you time and the risk of errors from manual data entry.

Propermac2