Say thanks with an excel tip

Here’s one many people don’t seem to know:
Ctrl+PgUp/PgDn – Move to previous/next worksheet

F4 repeat your last action. Very handy use it all the time

one bonus

Alt+Enter to get a new line in the same cell

Indian Gurus say if you want to increase any thing, first share whatever you have !
Share problems here we get the solution and not only increase our knowledge / idea but also increae friends base globally.
Once again, i appreciate the initiative and participations of global friends to solve / shape the any query in easiest way, that without any cost !

I can imagine working without following:
Shift + F11 – insert new sheet
F2 – edit active cell

very basic but one of my favs:

strg while selecting with shift and cursor to extend the selection to the last nonblank cell in the same column or row.

strg+space – select column
shift+space – select row
strg+shift+space – select active ‘array’ for lack of a better word.

My deepest sympathies are with the people who suffered in Mumbai attack. Wish I could change the colour of Red Streets outside the Taj Hotel by just change the formating (Ctrl+1). Wish I could give F1 to them for using instant help when they needed. Wish Ctrl+F could have been provided to find the terrorist.
Wish we could share their grief by using Alt+t+b just like sharing workbook.
Wish I could have the option like ctrl+z to undone what has happened to them and ctrl + D to clear the destructive contents from the minds of those insane attackers.
My heart goes out to the people in Mumbai and all others who are the victims of terror. May god give them strength to come out of this.

Regards,

Pankaj Verma

Ctrl-9 to hide (current / selected) rows, Ctrl-0 to hide (current / selected) columns. Ctrl-Shift-9 and Ctrl-Shift-0 do the reverse (ie unhide).

Tony

This has been very disturbing news from Mumbai.
 
Two of my favorite tips have been described already:
 
Ctrl+1 opens the formatting dialog for whatever object is selected, not just the active cell. it is particularly useful in Excel 2007, which inexplicably does not open this dialog when double clicking on an object.
 
F4 repeats the last action, but unfortunately Excel 2007 has a short and faulty memory of what that last action was. It either does nothing, or it repeats something you did three or four actions ago, or it only repeats part of the last action, like formatting only the line color of a chart series, and not the whole format you just applied.
 
This is my contribution:
 
Ctrl and Up/Down Arrows in the VB Editor moves backward or forward in the active module to the previous or next procedure. I never knew this one, but learned it from a student in a class I gave a couple months ago. I hope it shows that a good teacher also learns from his students.

A really sad day for us Indians – but let us all resolve to become self reliant so that we can protect our country.

Had helped a colleague out yesterday when his formulas were not working – press F9 for manual calculation. Go to tools ->options-> calculation-> manual/automatic to change settings as required.

I find that + to get me to the VBE is useful.

My heart and prayers are with the people in Mumbai.

I always use Ctrl Shift * (8) to quickly select the current region data

Sorry – should not have used square brackets in the last post – it should read “Alt key plus F11″.

Ctrl 1, doesn’t just display formatting for a cell
It also accesses the Format dialogue for nearly any object that is selected, inlcuding Charts, Series on charts, Drawing objects, Rows, Columns, Ranges
Just try it anywhere…

My own favorite is Ctrl *
That selects the block of cells that is contiguous with your location
that is if you are in the middle of a range of cells with data all around you, Ctrl * selects the range of cells that includes all cells with data touching your location

Ctrl * is great!!!!! . Thanks

This one is very usefull, I use it all the time – this will display all formulas within a worksheet- CTRL + ` (The key just before 1)

Your blog and the comments of many posted here have already helped me on more than one occasion, or given me new ideas. Thanks!

control + shift # to apply date format, specially after getting a serial number when entering a formula

Isn’t ALT + ENTER the shortcut to PRINT PREVIEW?

The ` on the key onthe left of 1 is called the TILDE.

Chandoo, lovin’ the blog and the rest of the tips. Two of my favourites:
ctrl plus ; inserts current date (not as a formula)
ctrl plus shift plus ; inserts current time.
Cheers.

Ctrl & Shft & 1 formats all numbers with commas and 2 dec pts
Ctrl & Shft & 2 formats all numbers in time format
Ctrl & Shft & 3 formats all numbers in date format
Ctrl & Shft & 4 formats all numbers in $$ format (with 2 dec pts)

Ok, little bit different from the rest of you lot…, Hold down the middle mouse button and drag right, left, top, bottom to navigate throughout the worksheet. Absalute ZEN while navigating those huge worksheets

In any cell after writting the formula and bfore pressing the enter key, just press “F9″. This will copy the result and paste the value in the same cell. — No need to go thru copy==>past special==>value

LEN(A1)-LEN(SUBSTITUTE(A1,” “,”"))+1
Hi
I was using the above function to chk the nos of words in a line.
But if you use the above sepertely MEANS
a=LEN(a1)
b=LEN(SUBSTITUTE(A1,” “,”"))+1
Then
A-b
it was giving diff no.
Pl explain me why this happend.

Regards,

Sanjay -

First, if there are any double spaces between words, you will overstate the number of words. TRIM() removes leading and trailing spaces, and reduces multiple spaces between words to single spaces. Use:

LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),” “,”"))+1

Second, without seeing your actual values, it’s hard to see where the discrepancy lies. What is the string in A1, and what are the calculations for the four formulas?

By clicking the roller(middle switch) of the mouse, directional arrows appear. By keeping the distance in a particular direction, data will move in that direction its own. Distance will evaluate the speed i.e. more distance high speed in that direction.

This is very useful to check the each data when the database is very large.

Share your experience.

how to hide formulas without protect the sheet.