Friday Formulas Microsoft Excel - I understand that it's Friday and the only thing on your mind is the coming weekend, but I want you to bear with me and read my tips on some excel functions and formulas.
Trust me, you won’t have to bang your head on a wall to understand them. These tips are easy to understand and can come in very handy at different tasks you may be working on.
'SUM’ing Continuous Sheet
You have 12 sheets/tabs that has every months sales data and you want to create a Full Year sheet by SUM’ming every month sheets. We know you can do so by =Jan!A1+Feb!A1+Mar!A1+..+Dec!A1.
However, there is a trick you can use to make your task easier. It involves only specifying the first and last sheet and asking to SUM the specified cell in all the sheets in between.
This is how it is done: =SUM('Jan:Dec'!A1).
Remove Annoying Spaces
At times you receive data with annoying blank spaces at the beginning or end of text or numbers. I am sure you want to get rid of them without having to manually delete them one by one.
I recommend using the TRIM function. If your data that needs clearing is in Column A. Enter =TRIM(A1) into another cell. For this exercise we will use cell B1 and drag it to the bottom of your data. If you wish, you can then copy the returned numbers and paste as values on column A.
TRIM removes all blank spaces not only from the beginning and end of text and numbers, but it also double spaces in the middle of text!
Largest Number in Your Data
You have a data in a table and would like to find the largest number. What you would normally do is highlight your range data and sort it by A->Z.
What if you don’t want to sort? What if your data is dynamic? What if you want to find out not only the highest, but second highest or third highest number?
Don’t look further, your solution is to use LARGE() function.
=LARGE(A1:A50,1)+LARGE(A1:A50,2)+LARGE(A1:A50,3)
The above excel formula shows how you can add up the three highest 3 numbers from your data within A1:A50 range. You can amend it to fit your needs.
Convert to Upper or Lower case text
DON’T YOU HATE WHEN SOMEONE WRITES WITH CAPS LOCK ON!
Use LOWER() to ‘shut them up’.
=LOWER(A1) function will convert text in cell A1 into lower case. =UPPER(A1) will do opposite of it by converting to upper case.
This is it for friday! I hope you will find it useful and wish you great weekend!