I have been busy lately in training and automating some excel reporting stuff. Last week I was asked by one of my clients to protect a sheet dynamically. So that already entered information cannot be changed but should allow new rows to be entered. And when closed the newly added row should also be locked when re-open. Its difficult to explain the entire stuff in text here thus I recorded a small tutorial to explain it quickly as I felt it will be useful for many who might need it. Don’t mind the background noise for this one.
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
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 in Excel Category I talked about text functions and I posted a situation to create a distribution list from list of email ids in a column. There is no formula in excel which you can use to create a distribution list. But good news is that you can create one using VBA. Below is the code which would give a formula in excel to use for distribution list.
There are many ways to get this function written but below is one of the easiest way to understand the code. Paste the below code in any vba module and use it as a normal formula in excel.
Function listcat(src_range As Range, diff As String) As String
‘src_range is range variable to allow range selection and diff is variable to store ‘differentiators
Dim final As String
‘variable called final to store the distribution list
Dim c As Range
For Each c In src_range
final = final & diff & c.Value
‘finally taking the clean distribution list to listcat
listcat = Right(final, Len(final) – 1)
That is it go back to excel and use this like a formula in excel, for ex: =listcat(B2:B9,”;”) where B2:B9 is the list of email ids which needs to be added and “;” is the separator.
Attached is the file for your reference with the code and formula hope you will find it useful.
Note: These days you can directly copy the entire values in the column and paste in outlook/other mail clients which will automatically create a list. But if in situations it doesn’t this is the best way.
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.