It is no exaggeration that knowing excel formulas can give you a career boost. From someone starting at the long list of numbers, you can become a data god who can lookup, manipulate and analyze any spreadsheet by learning few excel formulas.
So when our little excel blog hit the 5000 RSS Subscriber milestone, I celebrated the occasion by asking you to share an excel formula through twitter or comments with rest of us. And boy, what an excellent list of formula tips you have shared with us all.
Here is the complete list of entries for the twitter formula contest.
Follow the links next to contributor’s name to see the original twitter post or comment
To return the full Path+Filename of your (saved) workbook
by Dmurphy on PHD comments
To return the full Path+Filename of your (saved) workbook (and dropping the [] characters) to get, for example, C:\Data\ExcelFiles\MyWorkbook.xls: =SUBSTITUTE(SUBSTITUTE(LEFT(CELL(”filename”,$A$1), FIND(”]”,CELL(”filename”,$A$1))),”[“,””),”]“,””)
Create a Dynamic Range that Grows and Shrinks with Data
by ps62 on twitter [@ps62]
IF($A6=””,””, SUM(OFFSET(Data,$A6-1,StartDateIndex-1,1,NumCols))) – makes stuff dynamic
Find the last cell in a row
by govi on twitter [@govi]
Return last filled cell in a row: =LOOKUP(9,999E+307;A1:IV1)
Cleaning your data (Example, changing the values in a column)
by artjohnson on twitter [@artjohnson]
Excel. Cust name header in C9 and text datalist below. Formula in B9 moves name from C9 to B9. Copy down. =if(isblank(C8),c9,b8)
Extract the month from a date
by Alan on PHD comments
Probably a easier way of doing this , extracting the month from a date as text. A1 is date =TEXT(DATE(0,MONTH(a1),1),”mmmm”)
Clean your text before you lookup
by rushikul on twitter [@rushikul]
=VLOOKUP(CLEAN(TRIM(E20)),F5:G18,2,0). To make sure you are using clear text, as text is most used in vookup_value
Find if two ranges are statistically different
by nandoaires on twitter [@nandoaires]
=IF((1-(1-NORMSDIST(ABS(A1-A2)/SQRT((2*AVERAGE(A1:A2)*(1-AVERAGE(A1:A2)))/(A3))))*2)>0,95;”Different”;”Equals”)
Lookup 3 criteria and return the match
by Alan_xls on twitter [@Alan_xls]
=Index(return,Match(1,(1stRange=criteria1)*(2ndRange=criteria2)*(3rdRange=criteria3),0)) Return result where 3 values match,Array Form
Offset with Match, get data from somewhere else
by Arnab Bose on PHD comments
This formula looks up data from another sheet considering three parameters keeping into account the column A and column B with sub-components (both on another sheet) and matching them up with the heading on both sheets. =OFFSET(’Data Sheet’!$C$1,MATCH(D$2,’Data Sheet’!$A$2:$A$140,0)+MATCH($B5,’Data Sheet’!$B$2:$B$20,0)-1,MATCH(D$3,’Data Sheet’!$C$1:$J$1,0)-1)
Using SUM with multiple conditions
by ps62 on twitter [@ps62]
{=SUM(IF(shoes=”nike”,Units,0))} – array formula – two conditions
VLOOKUP but get values from the left
by bsamson on twitter [@bsamson]
VLookup to return values to the left of the lookup range: =INDEX(SearchRange,MATCH(LookupValue,LookupRange,FALSE))
Getting data from a dynamic range
by Arnab Bose on PHD comments
This formula extracts data from a dynamic data range and returns a zero value if there is an #N/A error. =IF(ISNA(HLOOKUP($A14,Data!$AB$2:$AW$9,MATCH(”P”,Data!$AB$2:$AB$2,0),0)), 0,HLOOKUP($A14,Data!$AB$2:$AW$9,MATCH(”P”,Data!$AB$2:$AB$2,0),0))
Find the difference between maximums of two ranges
by PreetAulakh on twitter [@PreetAulakh]
{=MAX(K5:M5-K4:M4)}, one step formula to determine the max of difference of two ranges! No curly brackets in excel, Cltr+Shift+Enter
Find the top 3 values of a range
by JassiAulakh on twitter [@JassiAulakh]
Large(A1:A100,{1,2,3}). Gives you 3 highest values of a range. Select three cells and enter this formulas. Then Cltr+Shift+Enter
SUMPRODUCT with multiple conditions
by Martin on PHD comments
Here’s my little contribution (previously posted 😉 Named Ranges (should be dynamic, but….) Ship $A$2:$A$8 Captain $B$2:$B$8 flights $C$2:$C$8 in F:F Summary_ship $F$2:$I$2 this 3:3 Summary_Captain $E$3:$E$6 data is in range A1:C8, and summary is in E1:I6. =SUMPRODUCT((Ship=in Summary_ship)*(Captain=this Summary_Captain)*(flights))
Get the name of the workbook
by Dmurphy on PHD comments
To return the name of the workbook only, e.g. MyWorkbook.xls: =MID(CELL(”filename”,$A$1),FIND(”[“,CELL(“filename”,$A$1))+1,FIND(“]“,CELL(”filename”,$A$1))-FIND(”[”,CELL(”filename”,$A$1))-1)
Excel Formula Fun – Should we fight… ?
by chrismelck on twitter [@chrismelck]
=IF(ISERROR(VLOOKUP(WOMD,Iraq,1,FALSE)),”Declare war”,”Declare war anyway”)
More ways to use IF and Then formula
by Olu D. on PHD comments
This formula determines the Active (=”T”) status or otherwise of Employees in an Excel spreadsheet: =IF(AC2=””,”X”,IF(AND(AC2=500000,AD2=””),”T”,IF(AND(AC2500000,AD2?”),”F”,”Pls Enter Leaving Reason!!”)))
Using INDIRECT along with VLOOKUP to make dynamic lookups
by squash86 on twitter [@squash86]
=VLOOKUP(B3, INDIRECT(B36), COLUMN()-1,FALSE) The INDIRECT returns the name of a named range that holds the data table.
Calculate the p-value of a t-statistic [Don’t ask me what it is 😛 ]
by David on PHD comments
=NORMDIST(-1*ABS((Z27-AE27)/AG27),0,1,TRUE). Calculate p-value for t-statistic based on means in Z27 and AE27 and the std err of mean in AG27.
What is on the right side of that string
by aniVy on twitter [@aniVy]
=RIGHT(A1,LEN(A1)-FIND(“-“,A1,1)) – Extracts right side string after a hyphen.
Find frequency distribution of a range of values
by Cody on PHD comments
=FREQUENCY(DY5:DY118,EU4:EU14) for creating frequency distributions. I can’t believe I went so long before discovering that there’s an easy built-in array function that does this. Constructing the distribution by hand was always a pain.
In-cell bar graph
by JohnCorp on twitter [@JohnCorp]
=REPT(“|”,A1/MAX($A$1:$A$5)*30) creates a bar graph from the data in the range a1:a5, change the font to change the look of the graph
Get the name of the current worksheet
by Dmurphy on PHD comments
To return the name fo the current worksheet, e.g. “Sheet1?: =MID(CELL(”filename”,$A$1),FIND(”]”,CELL(”filename”,$A$1))+1, LEN(CELL(”filename”,$A$1))-FIND(”]”,CELL(”filename”,$A$1)))
Excel formula fun – Usetheforce()
by _mikii on twitter [@_mikii]
=usetheforce(choke,”Moff Jerjerrod”)
UDF to calculate to royalty, I am not getting any
by chrislbs on twitter [@chrislbs]
=TieredRoyalty($R$16:$T$19,I5) @r1c1 Uses a UDF to calculate royalty on I5 based on a TierTable in R16:T19, saving nested vlookups
Find the Next Friday the 13th
by S3bast1an on twitter [@S3bast1an]
ARRAYformula – Next Friday 13th is =MIN(IF(((WEEKDAY(TODAY()+ROW(1:1000);2)=5)*(DAY(TODAY()+ROW(1:1000))=13))=1;TODAY()+ROW(1:1000)))
Split first name and last name
by Mahmut on PHD comments
=LEFT(A1,FIND(” “,A1)-1) =RIGHT(A1,LEN(A1)-FIND(” “,A1)) Split first names and last names.
IF with a VLOOKUP
by m4th1337 on twitter [@m4th1337]
=IF(VLOOKUP(C1,’Historical Data’!$A$2:$S$332,4,FALSE)>F1,”-“,IF(VLOOKUP(C1,’Historical Data’!$A$2:$S$332,4,FALSE)
And now for the winners
I wish I had more prizes to give. All the tips are truly marvelous. I have learned several cool uses of excel formulas. But alas, we have only 2 prizes in this contest.
Dashboard bundle from Bonavista Systems goes to Govi
The excel formulas 2007 book by John Walkenbach goes to DMurphy
Both the winners are randomly selected. I have already sent them an e-mail with the further instructions to claim the prizes.
Big thank you to Bonavista Systems, the contest sponsor
I would like to thank Andreas from Bonavista systems for sponsoring the dashboard bundle. Bonavista systems makes some really cool tools for excel dashboards, spark-lines and helps you make cleaner and better looking charts. Checkout their products and know more about them from their site.
Further Resources if you want to learn Excel Formulas
22 Responses to “Master Excel 2007 Ribbon with this Free Learning Guide”
Thank you, kind sir. Well done with the baby making.
I cannot get signed up for your newsletter. I tied both this email address and churchill2001@hotmail.com. never a response.
I cannot get signed up for your newsletter. I tied both this email address and churchill2001_at_hotmail_dot_com. never a response for either attempt.
@Doug, it shows that your email address is pending verification. Can you check your inbox (and may be spam folder too) for an email from me? The subject will be "Activate Subscription to Get your Free Excel Tips E-book"
[...] PPS: If you are struggling with ribbon, you should check out ribbon learning guide. [...]
Very Useful Info..Keep it up..
@Ajay.. you are welcome 🙂
how do u download microsoft excel for free?
http://www.microsoft.com/en-us/default.aspx
Select Office
Free Trial
[...] Excel 2010 UI looks considerably better and less stressful than 2007. The colors are dull and subtle. The icons don’t call for attention unless you want to do something. The menus / ribbons feel smoother and slicker. [Learn to use Excel Ribbon with this Free e-Book] [...]
I can't open this pdf. I get the error message:
You do not have the required license to open this file.
Please request a license from the creator of the file, and add it using the license manager and they try opening it again.
What gives??
I downloaded the file again and it worked this time. Strange. (First file was 116 KB, second was 1644 KB... ???)
[...] More ribbon goodness | Free e-book to learn Excel Ribbon [...]
Hi Chandoo,
thanks for sharing your Excel 2007 learning experience with us; unfortunately the link to the pdf of the free Excel 2007 learning guide seems broken: my Acrobate Readers flags: "Unkown file type or corrupte data".
Have a nice day
Michael
well done this is great
Can somebody just provide a link the classic TAB exportedUI files for MS Office 2003 for us to use in office 2007/2010?. searching online, everybody just wnats to make a buck online with silly Classic Tab installers which do nothing more than inport exportedUI files for you.
Don't give me a ribbon how to guide, just give me free exportedUI files. I should not have to pay anyone for this, it is free XML, MS should have included this to begin with.
thanks
Dear.
There are a set of debit values and a set ot credit values in a column. I want a vba code by whcich the debit value plus a single / multiple credit value is zero that needs to be marked .
finally i will come to know out of the avaibale debits which cannot be used the with avilable credits either single or multiple values.
If multiple matching sets are available let it take the 1st or the 2nd one its not an issue.
Column A Ref
-1000 A
-5000 B
-8000 C
800 A
100 A
100 A
2000 B
3000 B
13000
15000
hi...
how to make this add-ins and display in ribbon... check this sample : http://www.cprsoft.com/GCDemo01.htm
thank you sir...
Please tell me format painter short cut key In excel ?
Thanks In Advance
thankfully.likeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
I am very much happy for such a great opportunity given to excel learners to advance their skills for the betterment of the future. I am a great user of this site and feel proud to have come across this web site.
I appreciate this, because I didn't do much works in my project management studies using gantt chart. As of now are have now learned some advancement.