Say thanks with an excel tip

Posted on November 26th, 2008 in blogging , Featured , Learn Excel - 34 comments

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

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

34 Responses to “Say thanks with an excel tip”

  1. Ashish Bogawat says:

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

  2. aerogeek says:

    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

  3. Ketan says:

    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 !

  4. Stružák says:

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

  5. WebEyE says:

    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.

  6. Pankaj Verma says:

    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

  7. TJMurphy says:

    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

  8. Jon Peltier says:

    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.

  9. paresh shah says:

    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.

  10. AlexJ says:

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

  11. Nor says:

    My heart and prayers are with the people in Mumbai.

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

  12. AlexJ says:

    Sorry - should not have used square brackets in the last post - it should read "Alt key plus F11".

  13. Hui... says:

    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...

  14. Hui... says:

    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

  15. aerogeek says:

    Ctrl * is great!!!!! . Thanks

  16. Martin J says:

    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)

  17. Jonida says:

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

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

  19. Mrayo84 says:

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

  20. Mrayo84 says:

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

  21. mouse says:

    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.

  22. Chandoo says:

    @All: thanks everyone.. There are so many great tips I didn't know.

  23. Doug says:

    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)

  24. Venki Das says:

    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

  25. Ketan says:

    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

  26. Sanjay says:

    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,

  27. Jon Peltier says:

    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?

  28. Ketan says:

    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.

  29. nagendra prasad says:

    how to hide formulas without protect the sheet.

  30. Chandoo says:

    @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)

  31. S23 says:

    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.. 🙂

  32. Jon Peltier says:

    @S23 -
     
    Those should be Ctrl+Space and Shift+Space, not backspace.

  33. radiostar says:

    Custom format a cell with ;;; and, boom, the input in that cell goes invisible:)

Leave a Reply