Automatically Insert Current Time and Date in a Worksheet

If you want to display the current time and date in excel automatically, then just use this formula:

=Now()

This will return the current date and time in the format that you set in your windows preferences. If, on the other hand, you only want to see the date, then just use this formula:

=Today()


Here is a sample output of each formula:

Now()   : 5/21/2011 12:41
Today() : 5/21/2011



Happy Computing!

Tweet

How to use Excel VLOOKUP

One of my most used formula in excel is the VLOOKUP formula. Basically what this formula does is that it returns a certain value from a table given a specific value.

A very simple example would be like this.
Assuming you have this table:

If you want to choose from the table given the number, then we can use VLOOKUP this way.

=Vlookup("1",A1:B4,2,False), this will result in "Apples"

In layman's terms, it means

=Vlookup("what to lookup for?", "what table?", "what column to get the results", "don't expect sorted data")

you can use a formula at the first argument to reference to another cell.

Now try it!

Happy Computing!

Tweet

Quick Auto Sum in Excel

Want to use a keyboard shortcut to quickly sum up a column or row of data? Here's how:

• Go to the end of the row or column (first cell with no values)
• Press Alt-= (Alt and equal sign)
• Auto sum is immediately inserted.
• Done!

Quick. Effective. Simple.

Happy Computing!

Tweet

Quick Navigation in Excel (Control Key)

If you want to go to the last data in a row or column, simply use this combination:

Ctrl-Arrow key.

Example, if you are at the top of the column and you want to go to the last data of that column (before any break), then use the Ctrl-Arrow Down. If you were at the bottom, then use Ctrl-Arrow up.


Same rules for Ctrl-Left Arrow and Ctrl-Right Arrow.

Now it's quicker to navigate highly populated sheets!

Happy Computing!

Tweet

Highlight All Cells Referenced by a Formula

Here's a quick shortcut to highlight all cells referenced by a formula. Go to the cell with the formula then hit

Ctrl-[ (control and open square bracket).

You will see all referenced cells highlighted. To move to the next selection (highlighted cell), simply press enter.

Very useful for auditing worksheets!

TIP: If you want to determine the OPPOSITE of it- that is formulas that reference the current cell  then simply use:

Ctrl-] (control and close square bracket)

Very useful for auditing sheets!

Happy computing!

Tweet

Add Bullets in Excel

Ever wondered how to add bullets in excel? It's so easy to do it in microsoft word. But how about excel?
Here's how.

On the selected cell, press Alt-0149. It will insert a bullet. Continue doing so for other cells or just copy paste the bullet and then add the text.

• Alt-0149
• Copy paste
• Or just repeat the key combination.

Works on all excel versions.

Happy computing!

Tweet

Display all formulas in a sheet with a single key combination!

Want to show all formulas within a sheet with just a sing key combination? Yes! With this combination, you can toggle this to display the results or the formula.

Here it is Ctrl - ~ (it's ctrl key and the tilde, key before "1").

That's it, you can now conveniently see and hide the formulas in your sheet.

Works with any version of excel.

Happy Computing!

Tweet