Grouping of Rows and Columns

To group columns or Rows

 

SHIFT + ALT + RIGHT ARROW KEY

 

 

 

To Ungroup

SHIFT + ALT + LEFTARROW KEY

 

 

Hide Ribbon

During the presentations and work in MS Office, you want the Top Ribbon to get hidden

 

 

Press Ctrl + F1 to get auto hidden

Here is the result

 

 

---

DISCLAIMER: The information contained and transmitted by this electronic mail (email) is proprietary to i-Vista Digital Solutions Pvt.Ltd (i-Vista) and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this email may not have been sent with the formal approval of i-Vista. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, disclosure, transmission, printing, copying, or dissemination of this information either whole or partial, in any way, or in any manner is strictly prohibited. If you have received this communication in error, please delete this email immediately & notify i-Vista at postmaster@ivistasolutions.com

Read the content without clutter

If you are using Mozilla browser, you have an option to read the content of the page, without any advertisement.

 

Try the following

 

Locate the Reading button at the right side of the address bar

 

 

You can use the same button to clear the reading view.

 

Try it on

Find Number of Characters in a Cell

There would be time where you want to find the number of occurrences of a text, you can use the following formula

 

 

Delete External links from an Excel file

There would be time where your file size is heavy or it is taking more time to load , because of some unkonow connections in the file from external source, try this to eliminate the links, the file shall work faster

 

Data >> Edit Links >> Break Link

Formulas >> Name Manager >> Delete

 

Display Hidden Data in chart

General if the cells are hidden, chart will not pull up that data

 

Use the following to make it active

1.      Click the chart in which you want to display hidden data.

2.      This displays the Chart Tools, adding the Design, Layout, and Format tabs.

3.      On the Design tab, in the Data group, click Select Data.

1.      Click Hidden and Empty Cells.

 

 

Enable/Disable Insert related functions

This macro can be used to enable / dis-able insert and other relevant controls. TRUE is for enable, false is for diable

 

    Application.EnableEvents = True

    With Application

        .Caption = ""

       

        'Cut

        .CommandBars("Worksheet Menu Bar").Controls("Cut").Controls("Rows").Enabled = True

        .CommandBars("Row").Controls("Cut").Enabled = True

       

        .CommandBars("Column").Controls("Cut").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Cut").Controls("Columns").Enabled = True

       

        .CommandBars("Standard").Controls.Item("Cut").Enabled = True

        .CommandBars("Edit").Controls.Item("Cut").Enabled = True

        .CommandBars("Cell").Controls.Item("Cut").Enabled = True

 

        'Insert

        .CommandBars("Row").Controls("Insert").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Rows").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Insert...").Controls("Rows").Enabled = True

       

        .CommandBars("Column").Controls("Insert").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Insert...").Controls("Columns").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Columns").Enabled = True

 

        .CommandBars("Cell").Controls.Item("Insert...").Enabled = True

       

        'Delete

        .CommandBars("Row").Controls("Delete").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Delete").Controls("Rows").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Delete...").Controls("Rows").Enabled = True

 

        .CommandBars("Column").Controls("Delete").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Delete").Controls("Columns").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Delete...").Controls("Columns").Enabled = True

        .CommandBars("Cell").Controls.Item("Delete...").Enabled = True

        

        'Other Properties Set

        .WindowState = xlMaximized

        .DisplayFormulaBar = True

        .Calculation = xlAutomatic

        .CellDragAndDrop = True

        .EnableEvents = True

        .MaxChange = 0.001

    End With

 

Missing Native Excel functions

At times because of some macros some native excel functions would get disabled, if you would want to reset that, you need to do the following steps

 

Locate the users setting of excel, generally here is the path for my system

C:\Users\venu\AppData\Roaming\Microsoft\Excel

 

Delete all the contents of the folder

 

When excel is opened it will restore the settings.

 

IN my system Insert function button (ALT + I + R) was not working, and now its taken care.

 

 

Alt + I + R (Or) Alt + I + C insert function not working

You can apply the following macro to activate the nativity function of insert rows columns

 

Sub activateInsertfunctions()

Call Allow_InsertColumn(True)

Call Allow_InsertRow(True)

End Sub

 

Function Allow_InsertRow(Allow As Boolean)

Dim ctl As CommandBarControl

For Each ctl In Application.CommandBars.FindControls(ID:=296)

ctl.Enabled = Allow

Next ctl

End Function

 

Function Allow_InsertColumn(Allow As Boolean) 'Allow user to or prevent user from inserting columns

Dim ctl As CommandBarControl

For Each ctl In Application.CommandBars.FindControls(ID:=297)

ctl.Enabled = Allow

Next ctl

End Function

 

Credit: Jos Dijkstra

 

Rename filename in XL VBA

Sub ChangeFilename()

Dim s1 As Worksheet

Dim strtpoint, end_point, i As Double

 

Set s1 = Worksheets("Data")

strtpoint = s1.Cells(2, 3)

end_point = s1.Cells(3, 3)

Dim FILEPATH As String

FILEPATH = s1.Cells(1, 3)

 

Dim oldfilename, newfilename As String

Dim filenum As String

 

For i = strtpoint To end_point

oldfilename = s1.Cells(i, 2)

newfilename = s1.Cells(i, 3)

 

 

    Name FILEPATH & oldfilename As FILEPATH & newfilename

  

Next i

End Sub

Refer Data from Other Sheets

One of the employee came to me with a file which has 96 sheets and basically it was all payroll data, he wanted a summary sheet with all the employee names and the month over month tax deductions.

 

I didn’t want to write a macro…..after a small thought here is the solutions

 

a)      Build Index of Sheets

=IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A1),1),"")

 

b)      Refer the values from different sheets

=indirect( " ' "&Sheet Name&" '!"&Cell Name)

 

In one go all the data employee wise month wise is right in front of me…..

Gratuity Calculation

Gratuity Calculation In India =

 

[ (Basic Pay + D.A) x 15 days x No. of years of service ] / 26

 

 

Where, D.A = Dearness Allowance.

 

Gratuity Eligibility:

1.      Any person employed on wages/salary.

2.      At the time of retirement or resignation or on superannuation, an employee should have rendered continuous service of not less than five years.

3.      Payable without completion of five years only when death and disablement.

Index + MATCH

It’s the time to say good bye to Vlookup and start using Index & Match.

One of the staff has asked me to fetch a last transaction value and the corresponding field for a given parameter. The first answer for the solution is a Vlookup, however it has a variety of limitations

 

 

1.      Your data range is limited to a table. That means the data you are looking up has to be in a standard tabular form. You cannot use VLOOKUP to find a lookup value in a different table, sheet, or offset row. This limits the ways you can display your data, as anything you want to lookup must be available in a standard table format in your spreadsheet.

2.      VLOOKUP always searches the leftmost column of the specified table to find the lookup value. Again, this limits your choices in presenting data as lookup values always have to be to the left of the return values. This sometimes means you must have multiple copies of tables in order, think far ahead when creating tables that might be used in lookup, or reorder columns after the fact simply to use VLOOKUP.

3.      You can only specify the return value column by index number. This means there is no way to include a static reference to the return value column. If someone adds a column between the lookup value column and return value column, it will break your VLOOKUP and you have to manually increase the column index number in the formulas. This is a maintenance nightmare.

4.      VLOOKUP provides a very limited approximate match feature. The only aproximate match option finds the nearest “less than” value. Unless you want that type of behavior, you’re out of luck and can’t use it.

5.      By default, VLOOKUP uses approximate match. If this is how you want it to function, then great… However, in many cases you want an exact value returned. It gives no indication it is picking a closest match result. If you do not want this behavior (which is most of the time, I have found…), you remember to explicitly set the Range_lookup argument in the formula to FALSE. Range_lookup is optional, and not a very descrive name of this feature, so it is often overlooked. This quirk is exasperated by the second danger…

6.      VLOOKUP can provide false results if the table is not sorted in ascending order! This is an issue when you use the approximate match feature, which is TRUE by default. Basically, VLOOKUP starts at the top of the table and goes down row by row until to gets to a valie less than or equal to the lookup value. If your table is not sorted in ascending order, this can give false results, as the formula stops processing rows immediately after finding a “match.”

 

The answer to these problems and limitations is the INDEX-MATCH lookup method. This methods uses two functions together to provide a more safe and flexible lookup feature. Here’s how each function works, independently:

• INDEX returns the value at the intersection of a row and column in a given range.
• Formula: =INDEX(Array, Row_num, Column_num)
• Array - The range of cells
• Row_num - The row to return data from
• Column_num – The column to return the data from [optional]
•  
• MATCH returns a position of an item in an array that matches a value.
• Formula: =MATCH(Lookup_value, Lookup_array, Match_type)
• Lookup_value – The value you want to find in the lookup value array
• Lookup_array – The range containing lookup values
• Match_type – Exact (0), Nearest Less Than (-1), or Nearest Greater Than (1) [optional]

 

 

Here is the problem statement,

Data Set

 

Required

 

 

Lets also understand CSE Formula

 

Last date            

 

 

Last project        

 

 

Any clarification reach me back venu@vnv.ca

Identify Duplicate/ common in two data sets

In case if you have two sets of list and you want to see all the common names in it, easier , simple and quicker way.

 

a)      Select two lists

b)      Go to Conditional formatting

c)      Select highlight Duplicates and see the magic

 

 

 

 

Max + IF with multiple criteria

In a given set of data you want to find maximum or minimum number you can use the MAX + IF forumla,

Note : this is a CSE Formula, so please ensure you press Ctrl + Shift + Enter after typing this formula, instead of just pressing Enter.

 

Eg : A Vendor has many purchase records, you want to know when you purchased last

 

Say the records are this way

 

 

You want to know the last purchase details

 

 

The formula is

 

 

 

 

 

 

 

 

 

 

 

 

Tally Error Rectification

When you get an error in the tally, do the following

 

 

1)      Go to Command prompt

 

 

2)      Enter the text

a.      First – Tally exe file path

b.      Second – Data folder path

c.      Third – Error Code ( code is there in the error msg )

 

3)      Tally screen will appear -  Don’t select the company

4)      Ctrl+Alt+R – Rewrite command

5)      It will delete the error

 

Every time you do this, don’t forget to thank me ;)

 

Bad eMail Habits

Are bad email habits distracting you, wasting your time, and causing miscommunications with clients, employees and others? Making a few simple changes to the way you handle email will help you improve focus, save time, and communicate more effectively.
 
Here are five bad email habits that could be holding you back—and positive alternatives to get you moving forward.
 
Bad habit #1: Sending emails late at night, early in the morning, and on weekends. This sends clients the message that you’re on call 24/7, so they treat you that way—which ultimately stresses you out. It also sends employees the message that you expect them to be on call 24/7—which stresses them out.
Instead, try: Limiting the hours during which you and your employees send work-related emails. Prohibiting email from, say, 10 p.m. to 6 a.m., will give everyone time to unplug, rest, and recharge.
 
Bad habit #2: Using email to discuss topics best suited to other means of communication. Overly complicated emails lead to confusion, while scheduling meetings by email leads to endless chains of “reply all.”
Instead, try: Finding alternate ways to communicate complex or sensitive subjects. Use calendar tools to plan meetings, IM or chat to discuss simple topics, and phone or in-person conversations to deliver bad news or hash out complex issues.
 
Bad habit #3: Setting alerts to be notified of every incoming email. Getting pinged every time you receive an email is distracting and makes you less efficient and productive.
Instead, try: Turning off alerts (unless you’re waiting for a very urgent email). Set specific times to check email, such as in the morning, before and after lunch and in the late afternoon.
 
Bad habit #4: Using vague, unclear subject lines. Generic subject lines like “Hey” or “Meeting” or “Question” require recipients to open the email to see what it’s about and makes it harder to search for relevant emails later on.
Instead, try: Using specific, detailed subject lines to speed comprehension and save time.
 
Bad habit #5: Sending overly long and complex emails. With more users checking email on their mobile phones, an email that’s too long will likely never get read—it will just get ignored.
Instead, try: Limiting email length to five brief sentences, max. When more detail is necessary, use attachments.

 

Source: http://h30458.www3.hp.com/apr/en/smb/Are-bad-email-habits-wasting-your-time%3F_1417972.html?jumpid=em_taw_IN_aug14_pps-xbu_2250650_hpgl_gb_1417972_0&DIMID=EMID_1274774286&DICID=null&OID=11060568&mrm=1-4BVUP

cleaning up your webmail

Create a new search list based on the following parameters and delete all the mails

 

To Delete old Mails : Before certain date

 

1.      In the search option i.e. "Has the words" field, type before:2010/01/23.

2.      That's just an example date; it would delete all messages received prior to January 23 of year 2010.

3.      You can use any date you want, as long as it conforms to the format YYYY/MM/DD.

 

Find the Large Attachments

 

1.      In the search folder type size: 5000000, this is size greater than 5MB

 

Paste Special short cut not working?

If you have run some unwanted code and some copy related functions not working properly, this you can enable it by just running this code….

 

Sub EnableCopyCutAndPaste()

EnableControl 21, True ' cut

EnableControl 19, True ' copy

EnableControl 22, True ' paste

EnableControl 755, True ' pastespecial

Application.OnKey "^c"

Application.OnKey "^v"

Application.OnKey "+{DEL}"

Application.OnKey "+{INSERT}"

Application.CellDragAndDrop = True

Application.OnDoubleClick = ""

CommandBars("ToolBar List").Enabled = True

End Sub

 

<source “Mr.Excel”>