Here is a post on sum-product which is a very important formula used in business. Please comment if you like it.
If you spend half of your day working on excel then I strongly suggest to learn the excel shortcuts. They come very handy when you working on a deadline or just helps in making things faster.Though there are many shortcuts in excel I use these very often to work around in excel. Myself being a heavy excel user, I believe the list of shortcuts I use will be very helpful to everyone. Always when working with “Alt” key in any shortcut combination, the key should be pressed once and released and then the remaining combination should be typed.
- Ctrl+N—> to add a new workbook
- Shift+F11–>Add a new sheet.
- Alt+E+L–> Delete a sheet
- Alt+D+F+F—> Apply filter in a sheet
- Alt + Space—> To expand dropdown
- Ctrl+Tab–>Toggle in open sheets.
- Ctrl+Pageup+Pagedown—>Toggle between the sheets up and down.
- Ctrl+Home–> To move cursor to cell a1 in the sheet.
- Ctrl+Shift+3–> To convert date to a better format.
- Ctrl+Shift+4—> To convert number to currency format.
- Shift+Space–> To select row (for column Ctrl+Space)
- Ctrl+Shift++key—> To add a row or column
- Ctrl+- key—> To delete a selected row or column
- Ctrl+L—> To convert data to table (generally I do to format them properly)
- Ctrl+~ —> to check all formulas in the sheet.
- Alt+D+L—> Data validation.
- Ctrl+1—> Get the format menu.
- Alt+O+H+H–> Hide sheet.
- Alt+O+H+U—> to unhide sheet.
- Alt+O+H+R—> Rename a sheet.
- ALT+E+S+V–> paste only values
- CTRL+G–Goto Box.
Well there are numerous other shortcuts but these are the most that I use. Let me know what are the one’s which you use in Excel.
In the last post on text functions we saw trim and clean. For those whow work with lots of data analysis and specially if the data is coming from various sources he gotta use more than just trim to transform the data into meaningful information. One of the most useful function that I use is “Left”. Suppose you have “UHG212″ in a cell A1 and you need to extract the first 3 characters from the cell in A2 then you use this formula in A2:
This will give you the first 3 characters from left and you can drag this formula to apply it for the entire column. To extract text from right you use the similar one =right(a1,3). Well that was easy but consider this situation you got a list of values like the below in cells B1 and B2 respectively.
In the above 2 values the text part is a course code and the numeric is its id number so I want to extract only the text part. (Of course you can use text to columns.) so to get this with a formula I need to know what is the position of underscore in each entry. So I know of a formula which would give me that which is =find(“_”,b1,1) which would return 5 for EXTT_23. So now to extract the text part out of it, you mix the left formula and the find which would be like this:
This would give me the answer EXTT and how does that work? The find formula gives me the result 5 and I am substracting one from it to make it 4. Now using left function to get the 4 characters from left.
Just drag the formula to the remaining cells and it would give you the required course codes for each entry.
The data for the same with formulas is in this file (techflames_practice.xlsm), you can download and check it for your reference.
Before I wrap up I would also like to tell little about concatenate with an example. Suppose you’ve got info,@,techflaemes.com in 3 cells a1,b1,c1 respectively and you gotta join them to make the email id email@example.com how would you do it? 2 Ways one is using the formula in say cell d1 which is =concatenate(a1,b1,c1) which will give you the result else the direct way would be this formula =a1&b1&c1 which also gives you this result. Ok now I leave with this thought what if you have 100 emails in a1 to a100 cells and you want to make a distribution list i.e. join them using a coma in between. Are you gonna write a lengthy formula like this =a1&a2&a3&a4…till &a100. Well for such things you got vba which can help you do this quickly. Will post the code in VBA section very soon.
Cya until next post.
Recently, I was working on a data parsing projects and I kind of refreshed all the text function in Ms-excel. Without these functions, I would have spent ages to get my file into correct shape. So I thought of taking some time out and sharing the knowledge.
The first important text function that comes to my mind is Trim. Now trim is a function which lets you remove unwanted spaces in the text. Consider you have a cell and with these text in it ” Hyderabad the city of Nizams”.
Now you see in the above text there are multiple spaces few before the start of the first word and few in the middle so if this text in the cell A1, then I would write a formula in cell like this : =trim(A1)
This would remove the extra spaces from the text and gives a cleaner version. Talking about cleaning–”Clean” is also an important text function which helps you remove unwanted/invisible characters which comes when you download some stuff from the net. It works similar way : =Clean(A1).
This will clean and remove unwanted/invisible characters from the cells. I always recommend a cleaning and trimming of text specially when you are working with parsing data from net.
Will keep writing on many such important functions. Please comment if you like the content and also let me know if any feedback. Until then Bye.