Tomorrow is thanksgiving day here. It is a harvest festival on which people express thanks for what they have. Much like Sankranthi (pongal) in India.
At this moment I am very angry and frustrated about the terror strikes in Mumbai. I am not sure how a person can do something like this to others. I do not believe in any god in particular, but now I am praying to god asking for little sanity and humanity in us.
I would also like to take a moment and tell you how much I am thankful for having discovered a passion through this blog. I have learned so much in last few months just by sharing what I know and interacting with people.
So to express the gratitude, I am sharing a simple excel tip. Please feel free to share your tip to tell how thankful you are for everything you have.
- Ctrl + 1 – opens the dialog box for formatting a cell. I use this all the time
Now your turn!
PS: expect delay in posts due to holidays
34 Responses to “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.
@All: thanks everyone.. There are so many great tips I didn't know.
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?
@Sanjay, as Jon suggested, can you provide us with some examples where this discrepancy is happening ?
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.
@Nagendra: Welcome to PHD and thanks for asking a question.
I am not aware of methods to hide formulas without protect sheet. But I am curious to know why you want such a method. You want to just hide the fact that there is a formula, but still let your users edit it? (as with out protect sheet, users will be able to go around and edit values / formulas)
Shift+backSpace to select entire row of activecell;
Ctrl+backSpace to to select entire Column of activecell;
Shift F2 to insert a comment
Too late to post.. 🙂
@S23 -
Â
Those should be Ctrl+Space and Shift+Space, not backspace.
Custom format a cell with ;;; and, boom, the input in that cell goes invisible:)