🌱Excel Tips and Tricks
Published by Scott Kingery on
General Tips
- If showing formulas instead of results, check if the field is formatted as Text and change it to General
- Copying all the way down
- Another way to accomplish this is to use a combination of the fill-down shortcut and the select all in range. After you enter the data in the cell, press Ctrl+Shift+End to select from the current cell to the end of the range that would be auto-filled. Then, press Ctrl+D to "fill down" into the entire range from the top cell.
Count
This formula will count the number of times the value in cell A1 on Sheet2 appears in column C of Sheet1.
=COUNTIF(Sheet1!C:C, Sheet2!A1)
Count Distinct
This will count the distinct values C2 to C1990
=SUMPRODUCT(1/COUNTIF(C2:C1990,C2:C1990))
Get left of or right of
Get everything to the left of the first space
=LEFT(A1, FIND(" ", A1) - 1)
To extract everything to the right of the first space in cell A1, you can use this formula:
=RIGHT(A1, LEN(A1) - FIND(" ", A1))
This formula works by finding the position of the first space in A1 using the FIND function, then using the RIGHT function to extract the text from that position to the end of the string.
Getting the file name and extension from a path
Given a path in A2 like: "C:\My\SuperLongPath\to the file\facts and figures.xlsx"
In B2, get just the file name with: =MID(A2,FIND("*",SUBSTITUTE(A2,"\","*",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))))+1,LEN(A2))
In C2, get the extension from B2 with: =MID(B2,FIND("*",SUBSTITUTE(B2,".","*",LEN(B2)-LEN(SUBSTITUTE(B2,".",""))))+1,LEN(B2))
Remove spaces after commas
=SUBSTITUTE(A1, ", ", ",")
See if value in one column is in the other
- This looks through column C to find the value in A38
=IF(COUNTIF(C:C,A38)> 0,"Exists","Not Found")
- This looks up the value of A2 in the C column (starting at C2)
- If there is a match it returns Y, else N
=IF(ISERROR(MATCH(A2,$C$2:$C$201,0)),"N","Y")
Find a string in a sting
- to find the word "trend" in B2
=IF(ISNUMBER(SEARCH("trend",B2)), "Yes", "No")
Find last instance of a string
- This finds the last / and returns everything to the right
=RIGHT(R2,LEN(R2)-FIND("@",SUBSTITUTE(R2,"/","@",LEN(R2)-LEN(SUBSTITUTE(R2,"/","")))))
Look up a value in a column and return adjacent value
- This looks up V2 in the X range and returns a value from the W range
=IFERROR(INDEX(W$2:W$25000,MATCH(V2,X$2:X$25000,0)),"")
=IFERROR(INDEX(return_value_range,MATCH(value_to_lookup,range_of_lookup_values,0)),"")