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,
- Calculating sum of top 3 values in a filtered list.
- Transposing a table of formulas quickly using copy-paste.
- Shading above or below lines in Excel charts
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.
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!
98 Responses to “What is the coolest Excel trick you have learned this year?”
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.
Building summary formulas with SUMPRODUCT and structured references to tables. So much easier to understand what they're doing!
=SUM(--(MMULT(10^MID(B2:B1000,{1,2,3},1),{1;1;1})=SUM(10^MID(E1,{1;2;3},1))))
What does it do Bill?
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.
Very impressive indeed.. 🙂
Doesn't work, do I miss one step?
you ommited the '-' (minus or substract) in front of 'SUM'
otherwise, FANTASTIC formula!
In macro's:
Selection.Offset(1,0).Select
Cool cool and another time cool!
Manuel
You will find the Column in the offset if optional
Hence Selection.Offset(1).Select
gives the same result
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.
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.
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
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/
The transpose trick is neat but why not just use the built in Microsoft feature for Paste Special: Transpose?
doesn't work with cell references as excel tries to change the reference range... paste special transpose only works for regular cell values
SUMIFS and CTRL+SHIFT+8 are saving me loads of time.
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!
OMG, I will be using this all the time! Thanks!
write in B2 "=a2" and in b3 "=B2&","&A3" and fill down from b3 value to end you will get list in last cell
That is a good one. Thanks!
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
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
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.
Grant - I just stumbled upon this tip and wanted to say thank you!
index+match formula
PowerPivet tables
Data tabels
Dynamic charts
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!
This is great. I've been looking for this solution forever. Thanks Grant!
Sure thing. Glad I could help...just a little outside the box thinking that a co-worker showed me a little over a year ago. It's way better than going through and selecting cells one by one with the concatenate function.
This is a great idea Grant !
I have used UDFs to get this sort of concatenation done in Excel.
One such UDF can be found at Bill Mr.Excel Jelen's post here :
http://learnmrexcel.wordpress.com/2011/04/04/learn-excel-a-better-concatenate-podcast-1375/
Thanks for that! I need to get all of my commonly used macros into a personal xl file to use universally but I've been lazy about that...That method would work just as well. I've just gotten used to doing the transpose + F9 method because I'm doing it daily on different spreadsheets, ad-hoc needs and whatnot. Thanks again!
Very cool tip.
I always use a simpler version of this: in you example
in cell B2, =A2.
In cell B3, =B2&","&A3
this will give a value 4751,4752.
double click the lower right-hand corner of the cell to fill down
copy and paste value.
This is what I do! 🙂
Hi Grant ,
Is this the same as what is described here ?
http://www.get-digital-help.com/2011/02/09/concatenate-a-cell-range-without-vba-in-excel/
Narayan
Yes it is. Thanks for posting the link. didn't think of adding the delimiting character in the original transpose equation. Thanks for helping to improve this
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.
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 !
I will definitely have to play more with tables, tough not so much fan of it so far.
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.
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).
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!
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!
The CUBE functions in conjunction with a PowerPivot data model. The possibilities are endless with these two tools.
I have learnt how to send emails directly from excel without actually logging into my gmail or yahoo account. and it was awesome.......
I missed this one, can you send link again.
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.
wow... did not know that. Thanks for sharing 🙂
I found this one by accident the other day when suffering the active cell being obscured
Calculated matrix with complex numbers.
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.
@Anil
You will find the Column in the offset if optional
Hence Selection.Offset(1).Select
gives the same result
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/
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.
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.
[…] you learn any cool Excel tricks this year? Chandoo shares his favourite new trick, and read the comments to see what other people […]
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 🙂 )
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...
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...
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"
=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)))
@Fahad
Why not simply: =LEFT(A2,FIND("/",A2,FIND(".org/",A2,1)+LEN(".org/")))
This is not working...
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.
Beautiful...!! Thanks..!!
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!
Thanks for this one. Such a time saver for cleaning up data.
Shift Enter doesn't work for me. (Excel 2007)
Control Enter does.
... what worked for me from Blanks on was OK, =, up arrow, Ctrl enter (Excel 2013). Thanks.
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:
http://www.youtube.com/watch?v=_cyIN4oxibw
The coolest and best thing, by far, I learned this year was finding out about Chandoo-the vlookup book is awesome too. Thanks Chandoo!
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.
Chndoo...Biggest fan of yours.....
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.
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!
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.
Shortcut for paste special - right click key + v (from chandoo), saves a lot of time
To remove unnecessary spaces, use =trim(text)
[Chandoo.org]
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.
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
Can you please explain how this works?
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.
Finding out about the CAMERA function - why did I not know about this before? I use it to create dynamic dashboards for projects...
use of the camera tool in dynamic charts as a way to protect your original chart setup.
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 !
thank you!
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!
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. 🙂
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. 🙂
Quickly combining text using concatenate and transpose formula is interesting and time saving. thanks for the tip.
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! 🙂
@Ruthie
Select the data area including headings
Copy
Go to a blank area
Select a cell
Paste, Paste Special, Paste Special, Transpose
[…] to Stan Brown for sharing this tip with […]
guess you ran out of days so please ignore all tips from jan 1 2014 forward
1. Index/Match with multiple criteria (array formula)
=INDEX($G$104:$J$113,MATCH(1,--($C$104:$C$113=$M105)*($E$104:$E$113=$N105),0),MATCH(1,--($G$101:$J$101=P$101)*($G$102:$J$102=P$102),0))
2.1 Formula looking for a reference in an other tab (using indirect formula) and returning the row of this reference
=ROW(INDIRECT("'"&$J5&"'!"&"a"&ROW(INDEX(INDIRECT("'"&$J5&"'!"&"$a:$a"),MATCH($K5,INDEX(INDIRECT("'"&$J5&"'!"&"$a:$a"),0),)))))
2.2 Formula looking for a reference in an other tab (using indirect formula) and returning the column of this reference in the other tab
=CHAR(COLUMN(INDIRECT("'"&$J5&"'!"&CHAR(COLUMN(INDEX(INDIRECT("'"&$J5&"'!"&"$1:$1"),,MATCH(D$3,INDEX(INDIRECT("'"&$J5&"'!"&"$1:$1"),0),)))+64)&1))+64)
2.3 Use an basic INDIRECT formula with what the two formulas above returned...
THANKS!!! This was a huge time saver!!! I have been looking for a VBA free solution for a long time!!! THANKS AGAIN!!!
I learned how to make my computer play funky town through VBA:
Private Declare Function Beep Lib "kernel32" _
(ByVal dwFreq As Long, ByVal dwDuration As Long) As Long
Sub FunkyTown()
Beep 392, 100
Beep 38, 100
Beep 392, 100
Beep 38, 100
Beep 349, 100
Beep 38, 100
Beep 392, 300
Beep 38, 100
Beep 294, 300
Beep 38, 100
Beep 294, 100
Beep 38, 100
Beep 392, 100
Beep 38, 100
Beep 523, 100
Beep 38, 100
Beep 494, 100
Beep 38, 100
Beep 392, 300
End Sub
Thanks a ton Grant. I am flattered.
It was mind boggling.
I used it combines more than 400 different cells into a single one with seconds.
Hi Grant:
Excellent tip, many thanks.
All the best.
John