Filtering blanks made easy

In Excel the “Blanks” option is usually at the bottom of the list. This slows down selecting it.

If you have a lot of entries you need to scroll all the way down to bottom of the list to choose it – see image below.

But the word “Blanks” is searchable, so if you type b in the Search box – your work is done – no scrolling required – see image below.

If your column contains text you might need to type in bla.

Copying a Date Down a Long List in Excel

Two techniques make it quick and easy

There are a number of mouse and keyboard shortcuts for copying. But there is one type of copy that can be frustrating. Copying dates can be challenging because, in general, Excel wants to increment them, not copy them. There is a simple technique to instruct Excel to copy a date.

Columns in a Text Box

You learn something new every day.

Today I discovered that you can have columns within a text box.

If you right click the text box and choose Format Shape the Task Pane below should open on the right.

Click the third icon (Size and Properties) at the top and then open up the Text Box options.

 

The Columns button allows you to specify how many columns plus the gap between them.

Have fun.

Copy Across Shortcut

In the structure below let’s assume you want to copy the SUM formula from cell B5 to cell C5.

Obviously you could use copy and paste, but that would require a few keyboard presses or mouse clicks.

Given that we already have cell C5 selected we can use

Ctrl + Shift + >

This copies whatever is in the cell on the left to the current cell.

 

 

Inserting a Blank Row Between Entries in Excel

A sorting solution

Over the years I have had many requests to help people insert blank rows between entries is a list. Apparently there is an import routine that requires it. My normal solution is a macro because it automates the whole process but there is a manual technique that is quick and easy.

Filtering Tip

Make your headings bold.

This tip applies to tables and to the structures you use for charts.

Excel looks for the bold format when it reviews tables and layouts to figure out if your table has a headings row.

You can use Ctrl + Shift + L to add or remove the filter icons to a data table. There is also an icon on Data ribbon tab.

This will work more reliably if the headings are bold.

I use the following keyboard combination on the top left corner of the table.

Ctrl + Shift + right arrow (this selects all the headings)

Ctrl + b (this applies bold to the headings)

Ctrl + Shift + L (to turn on filters)

This combination can be done very quickly.

You can just use Ctrl + Shift + L within the table, but sometimes this applies the filter to the wrong row.

You Can Undo After You Save

I am amazed how few people know this.

Way back in Office 2007 Microsoft changed the Undo List so that it is NOT cleared whenever you save a file.

You can use Ctrl + z or the Undo icon to undo things you did before you saved the file.

If you close the file that obviously clears the Undo List.

Please let people know this as I find so many people in my training sessions do not know things have changed since Office 2003.

This applies to all MS Office apps.