Daily Excel Tip (@DailyExcelTip) profile picture

Tips from 3 designated accountants that, when not changing diapers, watching football or playing hockey, leverage the power of spreadsheets to Excel

Halifax, Nova Scotia
Joined November 2010
Photo by Daily Excel Tip (@DailyExcelTip)Photo by Daily Excel Tip (@DailyExcelTip)Photo by Daily Excel Tip (@DailyExcelTip)Photo by Daily Excel Tip (@DailyExcelTip)Photo by Daily Excel Tip (@DailyExcelTip)Photo by Daily Excel Tip (@DailyExcelTip)Photo by Daily Excel Tip (@DailyExcelTip)Photo by Daily Excel Tip (@DailyExcelTip)Photo by Daily Excel Tip (@DailyExcelTip)Photo by Daily Excel Tip (@DailyExcelTip)
Use the formula below to sum the same cell across multiple adjacent sheets
1
2
Replace old text with new text using the SUBSTITUTE function. See example below
1
2
Use the UPPER(A1) and LOWER(A1) formulas to convert cells containing text to either Upper or Lower case
7
21
Excel Shortcuts Shift + Space -> Selects entire row Ctrl + Space -> Selects entire column
Use MATCH function to find the position of a value in a range of cells. Note Hockey is positioned 4th in range A2:A8
7
13
Always set final parameter in VLOOKUP formula to FALSE to find Exact match. If not "close" match will be returned.
3
9
Replace old text with new text using the SUBSTITUTE function. See example below
4
13
The following formula will pull all characters left of the @ symbol from an email address =LEFT(A2,FIND("@",A2,1)-1)
8
11
Use the LEN() function if you want to find the number of characters in a text string.
3
8
Set a comment to be visible at all times by right clicking on the cell that has comment and click Show/Hide Comments
3
1
The =TODAY() function returns today's date, and changes each time you open file to reflect current date
6
9
Use this formula to find the file path of your Excel file: =LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)
9
11
Use the RANK function in Excel to rank the value of a number compared to other numbers in a list.
4
5
REPT function will repeat text specified # of times. This formula will repeat cell A1 contents 5 times: REPT(A1,5)
3
5
Copy data, select Paste Special, check the Transpose box - this will paste your data with a different orientation
5
4
The =COUNTA() function counts cells in a range that are not empty
2
4
Use formula below to sum same cell across multiple adjacent sheets. In this example cell C2 is added across sheets
7
9
A formula to see if your date cell is a weekday or weekend =IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),"Weekend","Weekday")
2
4
7
Use the AVERAGEIF formula to take the average of cells that meet specific criteria. See sample below.
4
4