What is the coolest Excel trick you have learned this year?

Posted on December 13th, 2013 in Excel Howtos - 90 comments

We are almost at the end of 2013. Time to review how much more awesome we became this year. Today let me ask you a very simple question.

What is the coolest Excel trick you have learned this year?

Go ahead and post your answers using comments.

I will go first:

As my life revolves around Excel, not a week goes by when I don’t learn something new. That said, 3 of the most impressive tricks I have learned this year are,

For me the coolest trick has to be the transposing one. This, shared by Joey (a commenter on Chandoo.org) shows how to solve a tricky problem with smart thinking.

Transposing a table using find / replate, copy & paste

What about you?

Go ahead and tell me what is the coolest Excel trick you have learned all this year.

We have 18 more days in this year and I am really hoping to learn few more awesome tricks. I am all ears to hear what you have to say.

Go!

Your email address is safe with us. Our policies

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

90 Responses to “What is the coolest Excel trick you have learned this year?”

  1. Mike Hansford says:

    Copying the formatting from one chart into another – Saves so much time setting up a new chart using the corporate font, colours etc – To do this I use Alt, then E, then S and select formats.

  2. Rob T says:

    Building summary formulas with SUMPRODUCT and structured references to tables. So much easier to understand what they’re doing!

  3. Bill Jelen says:

    =SUM(–(MMULT(10^MID(B2:B1000,{1,2,3},1),{1;1;1})=SUM(10^MID(E1,{1;2;3},1))))

    • Chandoo says:

      What does it do Bill?

      • Bill Jelen says:

        It is a formula from Krisztina Szabó. Given a 3 digit number in E1, it will count how many 3 digit numbers in B2:B1000 contain the exact same digits in any order. I chose this as my new favorite trick, because my 5002-formula solution in Learn Excel Podcast 1834 was replaced by this one brilliant formula, which does not even require to be array-entered. I had to watch it twice in Evaluate Formula – the MMULT trick to convert a 999-row by 3-column array into 999-row by 1 column array is definitely a new tool for me.

    • david says:

      Doesn’t work, do I miss one step?

  4. Manuel says:

    In macro’s:

    Selection.Offset(1,0).Select

    Cool cool and another time cool!

  5. Khushnood Viccaji says:

    Using the in-built ‘tables’ feature in Excel 2010.

    The ease of using tables and the variety of possibilities that it has thrown open for me, makes this truly the coolest thing I’ve learnt in Excel this year.

  6. Khushnood Viccaji says:

    In macros:

    In a filtered list, I needed to copy the text of the *visible* cell above into the ActiveCell.
    This macro uses the Ctrl+’ combination to bring in the exact text of the visible cell above, into the ActiveCell, and presses ENTER.

    SendKeys “^’”
    SendKeys “{ENTER}”

    ps:
    This may sound like a job for Ctrl+D, but that doesn’t work all the time, because the cell above the ActiveCell may not be visible (because of the filter), and in such a case Ctrl+D will copy the contents of the hidden cell immediately above the ActiveCell.

    • Neil Holder says:

      SendKeys is really unreliable as it requires the focus to be set to Excel when the code runs.

      Another way to do this would be the follow:

      Dim r As Range

      Set r = ActiveCell

      ‘move up one cell at a time until we have found a visible cell.
      Do
      Set r = r.Offset(-1)
      Loop Until Not r.EntireRow.Hidden

      Debug.Print r.Value

  7. Brian Veenema says:

    Creating a clustered and stacked column chart. Jon Peltier has a great tutorial on his site. http://peltiertech.com/WordPress/clustered-stacked-column-bar-charts/

  8. Nancy Turner says:

    The transpose trick is neat but why not just use the built in Microsoft feature for Paste Special: Transpose?

    • Cal says:

      doesn’t work with cell references as excel tries to change the reference range… paste special transpose only works for regular cell values

  9. Sherry says:

    SUMIFS and CTRL+SHIFT+8 are saving me loads of time.

  10. Grant Fitzgerald says:

    Learned this in 2012, but thought I’d share…ever have a long list of values in a column you want to concatenate with commas into one list for use in SQL? Try this…assume we have data in cells A1:A100 that we want in one string
    1) Select cell B1. If my data in A1 is “1234″ I will type A1&”,” in B1, double click the lower right-hand corner of the cell to fill down.
    2) In cell C1, start typing in the “TRANSPOSE” function, tab over when it comes up and select the B1:B100 range (our data started in A1:A100, added commas in B).
    3) Before pressing enter and ending the function, hit the F9 key and it’ll show you all of your selected range’s values.
    4) Highlight the transpose function and replace it with “CONCATENATE”, replace the last comma and outer curly bracket with “) and voila…list of values.
    Trust me…do this a couple times and you’ll never make a manual list again!

    • Tonie Salzano says:

      OMG, I will be using this all the time! Thanks!

    • Sudhir Gawade says:

      write in B2 “=a2″ and in b3 “=B2&”,”&A3″ and fill down from b3 value to end you will get list in last cell

    • Pete says:

      That is a good one. Thanks!

    • Nirmalan says:

      Hi Grant,
      I am lost at step 4 “4) Highlight the transpose function and replace it with “CONCATENATE”, replace the last comma and outer curly bracket with “) and voila…list of values.” Can you upload a sample file?

      Thanks and kind regards,
      Nirmal

      • Nirmalan says:

        Hi Grant,
        I managed to figure out on how to get the results. It’s neat and a cool trick.

        Thanks for sharing and Merry Christmas .
        best regards,
        Nirmal

    • Steven Hager says:

      Been using my addin for ten pus years that contains many features allowing you to quickly create and more importantly execute SQL statements from within Excel. The steps you describe above are carried out with two clicks. My coolest has been and remains being able to execute SQL from any cells within Excel. Using DSN-Less connection strings makes my code slightly more portable. Combining the ability to return data via your SQL with VBA for processing the data negates the need to “save as Excel” from the ERP.

  11. Raed Abdullah Al-Muzail says:

    index+match formula
    PowerPivet tables
    Data tabels
    Dynamic charts

  12. Grant Fitzgerald says:

    Learned this in 2012, but thought I’d share…ever have a long list of values in a column you want to concatenate with commas into one list for use in SQL? Try this…assume we have data in cells A1:A100 that we want in one string
    1) Select cell B1. If my data in A1 is “1234″ I will type A1&”,” in B1, double click the lower right-hand corner of the cell to fill down.
    2) In cell C1, start typing in the “TRANSPOSE” function, tab over when it comes up and select the B1:B100 range (our data started in A1:A100, added commas in B).
    3) Before pressing enter and ending the function, hit the F9 key and it’ll show you all of your selected range’s values.
    4) Highlight the transpose function and replace it with “CONCATENATE”, replace the last comma and outer curly bracket with “) and voila…list of values.
    Trust me…do this a couple times and you’ll never make a manual list again!

  13. Jose Vasquez says:

    I learned how to do a dynamic, ever expanding list to use as a drop down. You know when you use drop down lists (any one of them that you choose to use), if you add new elements to this list, you need to adjust the drop down. If you use this formula, you don’t ever need to worry about that list again:

    =OFFSET(Worksheet1!$H$6,0,0,Worksheet1(Control!$H:$H),1)

    (Just adjust the worksheet and the column that you are using)

    If you define a dynamic name with this formula, you just need to assign it to whatever you are using for the drop down, or list in general.

    Very cool way to use dynamic lists without using VBA.

    • Khushnood Viccaji says:

      Jose, this trick has been around for a while now. And I have used it very often over the years.
      But after I started using Tables in Excel 2010, this formula seems redundant.

      As you add more information to the right, or at the bottom of the table, Excel expands the table (and its range-name) automatically !

    • Grant Fitzgerald says:

      This is good, but it’s just a start to infinite possibilities…I had some fun this year using offset-match to make dynamic dependent lists that work off data I pull from a SQL server. I’ve found ways online to do it with named ranges and whatnot, but I’d have around 400 lists if I did it that way. That 400 count went to under 30, but it’s far too long to post how I did it here.

  14. wookiee says:

    I’ve learned a lot of things about Excel this year (not the least of which was the 2 days of Advanced Excel Master Class here in Columbus, Ohio this summer). There’s too much to list from that class here, but one trick that just amazed me was a “hidden” feature of Excel: the ability to sort columns left to right. In Excel 2007, the direct method is reached from the Data tab of the ribbon: Sort | Options | Sort Left to RIght. An example of VBA which uses this method is below (apologies if the graphic is ilegible; I’m not familiar with Disqus posting conventions, so I couldn’t figure out if code tags worked, thus the .png image).

  15. Maxime Manuel says:

    I have learned almost everything I evolved in Excel this year from Chandoo.org. I have learned from Dynamic Range, Index, Match, Sumproduct, etc. to the basics as formatting and so on. This is one of the best Excel learning website. Oh! I forgot the tutorials on Youtube that I enjoyed a lot (specially the one with his daughter crying in the background. Laughs)
    Thank you Chandoo!

  16. Maxime Manuel says:

    I have learned almost everything I evolved in Excel this year from Chandoo.org. I have learned from Dynamic Range, Index, Match, Sumproduct, etc. to the basics as formatting and so on. This is one of the best Excel learning website. Oh! I forgot the tutorials on Youtube that I enjoyed a lot (specially the one with his daughter crying in the background. Laughs)
    Thank you Chandoo!

  17. Jon Acampora says:

    The CUBE functions in conjunction with a PowerPivot data model. The possibilities are endless with these two tools.

  18. Sandeep Sharma says:

    I have learnt how to send emails directly from excel without actually logging into my gmail or yahoo account. and it was awesome…….

  19. Stan Bown says:

    Very, very simple but I learned that the help box that comes up with formulas (the one showing the syntax) can be moved where you want it to be. Handy, because it always seems to pop up in a way that obscures the active cell.

  20. Pavel Lasák says:

    Calculated matrix with complex numbers.

  21. Anil Goel says:

    One of the most used and least profound macro I use in my Personal.xls is likely my one liner:
    Selection.offset(0,1).Select

    which is attached to a convenient button.
    Ok, with an On Error, it is a two liner. Sue me.

    When dealing with financial data, especially with account numbers, I frequently select a number of cells in the same column, do something (i.e. format, add, etc), and then select the same cells in the next column. For adjacent cells, no big deal, but if they are a little more sporadic (as is usually the case), I’ve found it quite a time saver..

    If I select data in January, clicking the button a dozen times allows the selection of the annual numbers for those selected accounts. Yes, I also have one for left, up and down.

  22. sumitbansal23 says:

    I tried creating a Google-type search suggestion box for one of the dashboards

    http://trumpexcel.com/2013/10/excel-tip-20-create-a-drop-down-list-with-search-suggestions/

  23. Hans Knudsen says:

    The coolest Excel trick I have learned this year:

    =TRIM(MID(SUBSTITUTE($A1,”,REPT(” “,999)),COLUMN(A1)*999-998,999))

    entered into B1 and copied to B1:E1 with (for example) the following content in A1:

    abc,def,ghi,123

    Finally I change the formula in (here) column E to: =–(Above formula)

    And yes, I know the Text to Columns tool.

  24. Paul Maddock says:

    I learnt this year that for the last 6 years I didn’t know what sumproduct did. Saved me a lot of time this year on dashboards. Also this year I learnt that you can use ~ to indicate to Excel that you want to find ? or * rather than use them as wild cards. i work with URLs a lot so this was of great help to me.

  25. […] you learn any cool Excel tricks this year? Chandoo shares his favourite new trick, and read the comments to see what other people […]

  26. Michel Kant says:

    Sometimes something usefull can be very very simple. This simple loop has helped me and my colleagues convert columns into one merged cell with added characters like a thousand times this year:

    Sub forLoop()

    ‘ forLoop Macro

    Dim i As Long
    Dim value As String
    For i = 4 To 32
    value = value & “^” & Range(“B” & i).value & “$|”
    Next i
    Range(“C3″).value = value


    End Sub

    ( This particular example adds regex for Google Analytics :) )

  27. Flavio says:

    The coolest thing I have learned this year has to do with Machine Learning techniques in excel. I developed a simple function to do multivariate linear regression in excel (excel already has a great solution on Data>refression) – bu this one is a Formula.
    I also learned a “Learning algorithm” called QLearning. Now I am into SVMs…

  28. Flavio says:

    I also developed an excel addin called “Excel-SQL” where you can make SQL syntax based programs and run it within excel. I does not use any SQL server. It treats each spreadsheet (inside the active workbook or any other outside) as a dataset. I can make programs to analyse data using the SQL language (just like queries in any SQL based system). It works perfectly. Now I am developing a “Query builder” for it, in order to help other people make queries by point and click. If chandoo permits, I can share it thru his webiste…

  29. Fahad says:

    Shortening the length of a string and getting the required values… I had wanted the value after “.com/” and didn’t need the rest of the URL… For eg: The URL for this post… http://chandoo.org/wp/2013/12/13/what-is-the-coolest-excel-trick-you-have-learned-this-year/

    This should give the output: http://chandoo.org/wp/
    Here is the formula:
    LEFT(A2,LEN(A2)+SEARCH(“org”,A2,1)-LEN(A2)+3)&LEFT(RIGHT(A2,LEN(A2)-SEARCH(“org”,A2,1)-3),LEN(RIGHT(A2,LEN(A2)-SEARCH(“org”,A2,1)-3))+SEARCH(“/”,RIGHT(A2,LEN(A2)-SEARCH(“org”,A2,1)-3),1)-LEN(RIGHT(A2,LEN(A2)-SEARCH(“org”,A2,1)-3)))

    Replace “org” with “com” for URLs with “.com”

    • Fahad says:

      =LEFT(A2,LEN(A2)+SEARCH(“org”,A2,1)-LEN(A2)+3)&LEFT(RIGHT(A2,LEN(A2)-SEARCH(“org”,A2,1)-3),LEN(RIGHT(A2,LEN(A2)-SEARCH(“org”,A2,1)-3))+SEARCH(“/”,RIGHT(A2,LEN(A2)-SEARCH(“org”,A2,1)-3),1)-LEN(RIGHT(A2,LEN(A2)-SEARCH(“org”,A2,1)-3)))

    • Hui... says:

      @Fahad
      Why not simply: =LEFT(A2,FIND(“/”,A2,FIND(“.org/”,A2,1)+LEN(“.org/”)))

      • Fahad says:

        This is not working…

        • Nate says:

          Excel isn’t recognizing the quotation marks as such. Presumably it has to do with the conversion of copied text done by the website. If you fix the quotation marks (and change any relevant cell references) Hui’s formula works great.

          If you find yourself frequently having to switch between site extensions, you could use:
          =LEFT(A2,FIND(“/”,A2,SEARCH(“.???/”,A2,1)+LEN(“.???/”)))

          That assumes that all of the extensions are 3 digits. If you also had some that were 2 digits, you could try:
          =IFERROR(LEFT,A2,FIND(“/”,A2,SEARCH(“.???/”,A2,1)+LEN(“.???/”))),LEFT(A2,FIND(“/”,A2,SEARCH(“.??/”,A2,1)+LEN(“.??/”))))

          For simplicity, you could also replace the LEN() function with its numerical result. Also note that using “.*/” instead of “.???/” will result in incorrect results if the web address includes a “www.” or similar at the start.

  30. Hayley says:

    When I have a list of products but the account number only shows up in the first row and I need it to show up for all the rows, someone taught me to highlight the column with the account number, hit F5, Special, Blanks, up arrow =, shift enter. It’ll copy all the account numbers to all the blank rows below it. Saves me a zillion hours a week!

    • Andrew says:

      Thanks for this one. Such a time saver for cleaning up data.
      Shift Enter doesn’t work for me. (Excel 2007)
      Control Enter does.

  31. Arjen Naafs says:

    The most interesting thing for me this year has been how to do conditional formatting with charts. Though it is a bit of hassle setting it up, but at least it works in all Excel versions and when you have dashboards with regular changing data it is awesome. Highlighting lowest or highest value, using cut-off criteria etc. See here for an example:

  32. Terri says:

    The coolest and best thing, by far, I learned this year was finding out about Chandoo-the vlookup book is awesome too. Thanks Chandoo!

  33. Robert says:

    1. Dynamic Dropdown lists, where you select a Category in one dropdown, and the subcategory dropdown list is dependant on the first selection.
    I ended up using multiple pivot tables to create the source lists, as they needed to be dynamic with my automatically updating data.
    2. Using Offset to make my pivot table sources Dynamic (pretty basic, I know… but super useful when used effectively).

    With my raw data automatically updating in my excel files, Both of these tricks have made my excel files much more user friendly for those not that familiar with excel.

  34. Chndoo…Biggest fan of yours…..

  35. s k srivastava says:

    This year I have learnt lot of tricks from both Chandoo.org and Debra’s blog. But the most wonderful trick I learnt is the Pivot drill down showing the original data after filtering. It is available at
    http://blog.contextures.com/archives/2009/01/20/filter-pivot-table-source-data-in-excel/

    I have not found any alternative code doing the same thing. I have still not been able to decipher the entire code but it works well.

    Interestingly every time I learn a new trick I wonder how I was living without it.

  36. Michelle Kosmicki says:

    My favorite things were:

    Click in a cell highlights column & row.

    Changing the background in a barchart then making the barchart the background image.

    Love Chandoo!

  37. Nate says:

    I don’t know if its really a trick, but I learned you can insert row returns into long calculations to visually break them into pieces. They take up slightly more real estate, but it makes it a lot easier to process.

  38. arvi says:

    Shortcut for paste special – right click key + v (from chandoo), saves a lot of time

  39. Rasheed says:

    To remove unnecessary spaces, use =trim(text)
    [Chandoo.org]

  40. Kushal K Shah says:

    one thing which i discovered and may be most of the stalwarts might know is you can’t enter the forward slash key(/) in a cell at one go.(all other symbols and letters can be keyed at one go)

    I was stunned when a colleague showed me this.

  41. TodoExcel says:

    Awesome formula to find the last used row, even if there is blank intermediate cells. This is very useful to build dinamic ranges.

    Last used row:
    =SUMPRODUCT(MAX((A:A””)*ROW(A:A)))

    Next empty row:
    =SUMPRODUCT(MAX((A:A””)*ROW(A:A)))+1

    * You can change column A to do the check on another column

  42. Jorge says:

    1) This is a trick from last year but I learned it this year. If you have to standardize row height, a neat trick is to do it on a “normal” or desired cell, and without even looking at the number, use the F4 command on the cells you want to fix.

    Use the following shortcuts: ALT O R E (old shortcut) or ALT H O H, press Enter and then F4. For column width, ALT H O W, Enter, F4. The video is here: http://youtu.be/_8ieaQYaH4M

    2) The second one is a way to make 2-way data tables completely dynamic. The trick is to create “clone cells” of the two variables and point the main formula to them. Then the master cells can be linked towards the data tables header and column respectively.

    It’s very important that when feeding the data table dialog box, the cells to use must be the “clone” ones, since they are the cells that the calculations use now.

    3) The last one is how to move charts using the arrow keys: simply hold down CTRL and left-click the inside the chart. You’ll get 4 small circles in the chart corners indicating that the chart object is selected and can now be moved by pressing the arrow keys.

  43. PhilDC says:

    Finding out about the CAMERA function – why did I not know about this before? I use it to create dynamic dashboards for projects…

  44. F Cocquyt says:

    use of the camera tool in dynamic charts as a way to protect your original chart setup.

  45. Stephen says:

    Hi chandoo, may I suggest another trick for the transpose thing ?
    You can just copy the table and paste special > Link cells
    KABOom
    Same result !
    Correct me if i’m wrong !
    Happy new year !

  46. charlotte says:

    thank you!

  47. Kannan says:

    This is really a cool tips. This is very much required for me in my work here. I do copy paste into Lotus Notes and get it sometimes. This is fantastic and simple. Thank you so much!

  48. ilana says:

    I had to sort a list of part numbers. Some of them started with zero, but Excel insisted on treating them as numbers. The trick that I have found is =A1&” “, copying it all list long and using *this* field for sorting. I know that it is not much, but given that I have to do this kind of sorting several times a week it *really* helpes me. :-)

  49. ilana says:

    This one from 2010, still use it and love it. I have a coloumn of strings like paragraph numbes: 1.1, 1.1.1, 1.1.2, 1.1.3, 1.2 etc. I need to change them into 6.1, 6.1.1, 6.1.2, 6.1.3, 6.2. Replace would not work here, but: add 6A to each of them – lets say they are in coloumn B – the formula that I copy is =”6A”&B1, then change all into value and getting 6A1.1, 6A1.1.1, 6A1.1.2, 6A1.1.3, 6A1.2. *now* I can replace A1 with nothing. :-)

  50. Yatin says:

    Quickly combining text using concatenate and transpose formula is interesting and time saving. thanks for the tip.

  51. Ruthie says:

    What I really want to know how to do is take data with a bunch of column headers across the top, and easily transpose those so that they are now rows along the vertical side. Tips Needed! :)

  52. me says:

    guess you ran out of days so please ignore all tips from jan 1 2014 forward

Leave a Reply