Using Data Validation to Add an Input Message

In Excel, you have the ability to add comments to a cell. After it’s created, you can mouse over the cell to see the comment, or set it to show. You can even choose to have it print.

This is an alternate method to comment. Once created, you will only see it if you select the cell. It’s normally used to offer the user guidance on input to a cell, but can also be used for comments. To me, it’s cleaner looking than the normal comment method.

To create, select the cell you want the message in, then click the Data Validation button under the Data Tools ribbon.

Under the Input Message tab, create the Title, and the Input Message.

Once created, select the cell to test…

You’re done!

Benefits of Format as Table

In this post, I’ll go over some of the benefits of formatting a block of data as as a table. In Excel, it’s as easy as clicking the [Format as Table] button.

Recently, I’ve re-discovered this very useful feature of Excel.

Here is our raw data:

Raw Data

First thing we will do is select the data, and click the [Format as Table] button.

Our data transformed…

The first thing we notice is the look. You can choose from many different styles that can make readability a lot easier. You can choose from several themed colors, and alternating row colors.

The next feature is filtering. If you’re needing that, it comes pre-loaded with tables.

Another cool feature, when you scroll down, it changes the columns from A,B, C, etc. to the name of the column. See example:

Check the column names…

Another valuable feature is adding formulas to the table. All you have to do is add the formula along side the data and it is automatically copied down the entire table. This can be a big help when deal with data that is 1,000’s of rows.

Yes, I know I need make the G column wider to show my end balances.

In my example, I’ve added a “Net” column and “End Balance” column. I added formulas to the first row of data and it copied down. It gives you the lightning bolt icon to let you know it did it’s thing. You can also us the drop down on the icon to undo this feature.

Finally, if you plan to use your data for formulas, or pivot tables, the new table comes with a name that can be edited/changed in Name Manager. Usually, Excel just assigns “Table1”, “Table2”, etc.

Name Manager

My Favorite Excel Shortcuts

Here are some of my favorite keyboard shortcuts in Excel.

ALT + EnterWhen you are in a cell, this will add a new line
ALT + =SUM() formula
CTRL + FFind text on worksheet
CTRL + HFind text, and replace with something
CTRL + BFormat bold
CTRL + IFormat italics
CTRL + UFormat underline
CTRL + `This shows formulas. Also to remove.
CTRL + Shift + $Format cell in currency format
CTRL + Shift + &Add borders to selected cells
CTRL + SpacebarSelects the whole column
CTRL + ;Inserts the current date in cell
CTRL + TABSwitch between open workbooks
F2Probably my most used shortcut. This is to edit a cell

What I like to call “Lokey Labs”

Since about 1995, I’ve been creating random websites. Mostly for my own enjoyment. Some are still useful today, and some I just keep around for nostalgic purposes, and to clutter up the internet.  They’ve been on,, and Now, their home is OK. I like my last name

One of my more useful sites is a loan calculator. It shows the principal+interest, and total paid.  At one time, I was into browser bookmarks. Some include getting the URL, and title of a web page.  And a cool one can create a QR code of the URL so you can transfer it to a smart phone.  Most recently, I’ve been playing  around with Google Sites. I have a test page, and a page I created for a property I’m selling locally.  Also created a stand alone page for it as well.

Check out the full list here.

Delete or hide blank rows with VB macros

These macros can be helpful in data clean-up.  An example might be exporting large amounts of general ledger data with tons of extra lines in between the good data.

The key is to select the area that has the data.  This might be column that has GL account amounts.  The lines you want to delete do not have anything in this area.

In this screenshot, we want to delete the rows that are red in column N.

Here is the macro name “BlanksRows_Delete”- used for deleting:

Sub BlanksRows_Delete()
End Sub

In the above example, select the N column, then run the macro.

If you only want to hide the rows, you can use this macro “BlanksRows_Hide”

Sub BlanksRows_Hide()
 Selection.EntireRow.Hidden = True 
End Sub