Learn Excel Blog

Challenging ExcelTV to the Ice Bucket Challenge

               

I will be the guest on the August 20, 2014 edition of ExcelTV.

I am challenging the three hosts – Oz, Rick, and Jordan to the ALS Ice Bucket Challenge.

Here is Zeke’s challenge video:

F2 Stops Excel RefEdit Boxes From Inserting Cell Addresses

               

Maybe you are setting up Conditional Formatting, or defining a Name. It could be any dialog where you have a RefEdit control (the boxes with the red arrow at the right). You mis-type something and instinctively press LeftArrow LeftArrow LeftArrow to correct it. But Excel starts inserting cell addresses instead of moving left.

RefEditF2

 

This is caused by something called Point Mode. If you look in the lower left corner of the worksheet before you press the Left Arrow, you were in something called “Enter” mode. When you press the left or right arrow key from “Enter” mode, you go into Point Mode and it starts inserting cell addresses.

The solution is easy, once you know it.

Press F2 to toggle out of Enter mode and into Edit mode! You can now arrow to your heart’s content.

 

Add a ToolTip to Any Cell in Excel

               

ToolTips are awesome, right? You see them all the time in the Ribbon. If you are creating a worksheet for someone else to use, why not add reminders and notes to the cells they are supposed to fill out?

AddTooltipToCell

 

The steps are super-easy.

1. Select the cell where you want the ToolTip to appear.

2. Select Data, Validation (or press Alt+D followed by L)

3. In the Data Validation dialog, go to the Input tab. Optionally type a title. Definitely type an Input Message

4. Click OK

When ever someone selects that cell, the ToolTip appears.

Rearrange Just One Row in Excel

               

You have one row out of place in Excel. Sorting seems like a hassle. Is there a fast way to sling the row to the right place?

RearrangeOneRow

1) Select the whole row with Shift+Spacebar

2) While holding down the Shift key, grab the row border and move it to the correct location

Note: The SHIFT key during the drag is crucial. Forget to shift and you will cut and paste. Do Ctrl instead of Shift and you will copy and paste. Both of those overwrite where you drop the row. Holding down Shift while dragging is the only way to have this work correctly!

 

 

 

Remove Duplicates in Excel

               

Need to remove duplicates in Excel? Excel 2007 introduced the Remove Duplicates command on the Data tab of the ribbon.

RemoveDuplicates

1. Before you start… Make a copy of your data, since the command will delete the duplicate rows.

2. Select one cell in your table

3. Data, Remove Duplicates

4. Initially, all columns are checkmarked. Click Unselect All.

5. Choose the column where you want only unique values.

6. Click OK.

Excel will report that NN records have been removed and N records remain.

Good news: if you forgot to make a copy of the data, Undo will bring all of your data back.

For this and 511 other tips about Excel, check out Learn Excel 2007-2010 from MrExcel.

 

Learn Excel from MrExcel