Working with Autofilters in Excel VBA

Use the below VBA code, to show all the records in the sheet, without removing the already applied autofilter.

Sub ViewAllData()
  If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
  End If
End Sub

This code can be used, where we have applied multiple filters and to remove them at once, without removing the autofilter.


Use below code to view all the records in the protected sheet.

If the worksheet is protected, with no password, we can use below code to unprotect it, show all data and then turn the protection back on.

Sub ViewAllProtected()
With ActiveSheet
    .Unprotect
    .ShowAllData
    .Protect _
        Contents:=True, _
        AllowFiltering:=True, _
        UserInterfaceOnly:=True
End With
End Sub


Similarly we can use the below code to view all records in the password protected sheet

Sub ViewAllProtectedPwd()
Dim p As String
p = "password"
With ActiveSheet
    .Unprotect Password:=p
    .ShowAllData
    .Protect _
        Contents:=True, _
        AllowFiltering:=True, _
        UserInterfaceOnly:=True, _
        Password:=p
End With
End Sub


Check and apply the Auto Filter

Below code will check if the auto filter is already applied in the sheet. If already applied then the code will exit, else it will apply the filter to the data.

Sub CheckAutoFilter()
  If Not ActiveSheet.AutoFilterMode Then
    ActiveSheet.Range("A1").AutoFilter
  End If
End Sub


Removing the Autofilter

We can use the below code to remove excel AutoFilter using VBA code, if the autofilter already exists in the sheet.

Sub RemoveAutofilter()
  Worksheets("Sheet1").AutoFilterMode = False
End Sub

If there is no Autofilter already applied in the sheet, then the code will exit without executing anything.


Below code will help you to copy filtered rows

This code will copy the filtered rows to the sheet2 of the worksheet.

Sub CopyfilteredRows()

Dim r1 As Range, r2 As Range

With ActiveSheet.AutoFilter.Range
 On Error Resume Next
   Set r2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
       .SpecialCells(xlCellTypeVisible)
 On Error GoTo 0
End With

If r2 Is Nothing Then
   MsgBox "No data available to copy"
Else
   Worksheets("Sheet2").Cells.Clear
   Set r1 = ActiveSheet.AutoFilter.Range
   r1.Offset(1, 0).Resize(r1.Rows.Count - 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
End If

   ActiveSheet.ShowAllData

End Sub

Counting visible rows in a filter

The below code will help us to get the number of total rows and out of which how many are visible.

Sub CountVisibleRows()

Dim r As Range

Set r = ActiveSheet.AutoFilter.Range

MsgBox r.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 & " of " & r.Rows.Count - 1 & " Records"

End Sub

This code can be used where for example, we are counting the number of students in Group A. With the group as filtered using VBA code and the above message box code will provide us with the total and filtered row numbers.

10 tips when starting to code in Excel VBA

1. Make Use of the Macro Recorder
Microsoft Excel has a built in Macro recording feature, which give us a great way to learn about new objects and how to use them. I use the macro recorder whenever required to get the objects code in the part of my codes. The macro recorder does not always create the complete code which is required to get the best output. But is gives you the idea on how to write the code to perform specific set of actions. You have an option to record the code and then edit to get it work for the best output.

2. Make use of the immediate window
Excel VBA editor has a feature of the immediate window, which can be of great help while coding. You can write any VBA statements and get the resluts immediately on the immediate window. For example, say cell A1 = How ? and Cell A2 = by pdaphal.blogspot.com way

Select cell A1 and type activecell.value and press enter in the immediate window it will return you the value in cell A1. see the sample code and output below:

?activecell.Value
How ?
?activecell.Offset(1).Value
by pdphal.blogspot.com way

3. Make use of Debug.Print
Debug.print is the best way to find out the bugs (errors) in your code. Our code sometimes does not work the way we want to, due to variable situations we receive in the data processing. By using the debug.print code, you can see the output in the immediate window, as your code / program is running.

See example below for making use of the debug.Print code.
Say cells A1:A10 have the below list of name :
Kiran
Suresh
Arun
Vijay
Vikram

use code to test debug.print in immediate window:

Sub testdebug()
Dim name As Range, namelist As Range
Set namelist = Range("A1:A5")

For Each name In namelist
Debug.Print name.Value
Next name
End Sub

4. Think from all possible angles before starting the coding
Before starting the coding, planning or execution of any complex task, thinking thouroughly is the best way to begin. Start with wrtiting the logic on the paper, about how your code is going to work and execute the different steps. Check and understand every aspects of the problem, working this way will help to understand the problem and while understanding the problem, the solution find its way to you.

5. Make use of inbuilt Methods in excel VBA
It happens to everyone of us, we write the similar codes which we have used in past again and again for executing the different tasks. for e.g., finding the text, converting the text, splitting text. Before staring to write a code, please check the already defined functions / Methods in the Excel VBA editor and make the best use of them.

Few exmamples of methods are - Sendkeys, Second, Strreverse and more. To view methods Got to VBA editor window. Click list Properties / Methods. or you can use the shortcut Ctrl + J.

6. Program your code in Blocks
While wrtiting a bigger program, find the smallest working step which can be built easily. Build this first set of code and then start coding the rest bigger steps around it. By using this we you can test your code step by step, while for a bigger tasks you have something in hand everyday which is getting tested and completed day by day. This improves your turn around time and avoids the complexities in the code.

7. Always keep the references of the important codes / formulas you dont want to search again
If you are a regular VBA user, you should keep the VBA books references handy. Good reference books provide lots of information and tips for daily VBA usage. Also, whenever you have free time, you can always refer these books to learn something new. While, learning you can also create your personal reference bank which you require the most.

8. Break your code / project into smaller pieces
The project / problem you get might be very complex, but it always is a combined problem of different small problems, We need to identify these small problems and start finding the solution for them. So, break the problem into several small pieces. This coding method is called as modularization. Below are few advantages of using the modularization method:
You can resuse the code: once we break the big program in smalled parts, we can resue this smaller part in several places or even in different projects.
Easy to test: the smaller codes are easy to test and debug. this helps us to avoid to go through the bigger program for just testing a one line code.
Easy to maintain: as the program is broken into smaller parts, it is easy to upgrade them as required.

9. Make use of VBA only when you need it
Please make the use of VBA only when you actually use it. Once you start learning VBA code, you will feel excited about using VBA for everything, but there are tasks which can be acheived by using the excel formulas. Avoid using the VBA codes to make your situation more complex. Make the best use of the excel built in features which are readily available. e.g. data validation, conditional formatting, pivot tables, formulas and more.

10. Take Challenges to learn more
If you are into everyday VBA coding user, then taking challenges is the best way to keep your VBA techniques updated and sharpened. You need to keep sharpening your VBA saw, like the same way of the woodcutter story of sharpening the saw. Try to automate the report, that your team prepares manually, try to simplify the formlas, try enhancing the already written VBA code to improve performance. This will keep your VBA skills refreshed, updated and improve your confidence of problem solving capability

12 must know Date functions in Excel ?

Below are few of the important functions related to date, which are available in excel. These functions bring great help in our day to day calculation activities.

1. YEAR
Formula:
=YEAR(Date)

The YEAR formula is useful, when we need to extract only the year from the date.

Example:
We have around 10000 rows with dates of different years with sales data. While we need to create a chart that shows the yearly trend of total sales. For this we need to first find out the Year using the formula:

A1 = 26-Nov-14

The formula =YEAR(A1) will give us the output as 2014. We can copy paste this formula in all cells to get the Year output. Once we have the Year, we can then create a pivot table and create a chart to show the yearly trend for Sales.


2. MONTH
Formula:
=MONTH(Date)

The MONTH formula is useful, when we need to extract only the month from the date.

Example:
The same example in the YEAR formula above, can be used here to show the monthly trend for sales.
The formula =MONTH(A1) will give us the output as 11, as the month in the date is November. Once we have the month we can create a pvot table and create a chart to show the monthly trend for sales.


3. DAY
Formula:
=DAY(Date)

The DAY formula is used to extract only the day for the month from the selected date.

Example:
We will use the same date in the above examples in cell A1. The formula =DAY(A1) will give us the output as 26. As it is the 26th Day of November. This can be useful when you need to do some analysis based on only days.


4. DATE
Formula:
=DATE(year, month, day)

The date formula can be used to seperate the date time format date with simple date. Or in case we need to convert the date into serial number.

Example:
A1 = 11/26/2014  12:39:00 PM

We need to convert the date time in cell A1 to simple date format, for that we can use the DATE formula with the combination of above three formulas i.e., YEAR, MONTH and DAY.
The formula =DATE(YEAR(A1),MONTH(A1),DAY(A1)) will give the output as 11/26/2014.

This formula can also be used with combination of other formulas to convert the text into Date formats.
eample: A2 = 20141126 and we need to convert this in simple date format. We can use the DATE formula with the combinations of the LEFT, MID and RIGHT function:
=DATE(LEFT(A2,4),MID(A2,5,2), RIGHT(A2,2))

This will give us the output as 11/26/2014.


5. DAYS360
Formula:
=DAYS360(Start Date, End Date, Method)

The DAYS360 feature returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. We can use this function to help compute payments if your accounting system is based on twelve 30-day months.

The method in the above formula is optional, you can use it only if you need some specific methods mentioned below for computing.

FALSE or omitted:
U.S. (NASD) method. If the starting date is the last day of a month, it becomes equal to the 30th day of the same month. If the ending date is the last day of a month and the starting date is earlier than the 30th day of a month, the ending date becomes equal to the 1st day of the next month; otherwise the ending date becomes equal to the 30th day of the same month.

TRUE:
European method. Starting dates and ending dates that occur on the 31st day of a month become equal to the 30th day of the same month.
Note:
Excel stores the dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2014 is serial number 41,640 because it is 41,639 days after January 1, 1900.


6. EDATE
Formula:
=EDATE(Start Date, Months)

This is an very useful date function which helps us to get the future or past dates after or before specific date.
The EDATE formula can be used to calculate the warranty expiring dates from the date of purchase, maturity dates or due dates that fall on the same day of the month as the date of issue.
Example:
Say we need to find out the date after 18 months and the date in cell A1 = 31-Dec-14, then the formula =EDATE(A1,18) will give us the output as 30-Jun-16. Another example of finding past dates for 19 days, the the formula =EDATE(A1,-19) will provide us the output as 31-May-13.


7. EOMONTH
Formula:
=EOMONTH(Start Date, Months)

The EOMONTH formula is somewhere similar to the  EDATE function, the only difference here is that this formula returns the last date of the output month. This formula can be used to caluclate the due dates or maturity dates that always fall on the last day of the month.

Example:
Say we need to find out the date after 12 months and the date in cell A1 = 26-Nov-14, then the formula =EOMONTH(A1,12) will give us the output date as 30-Nov-15. Similarly to find out the past 12 month date, we can use the formula =EOMONTH(A1,-12), which will give us the output as 30-Nov-13.


8. NETWORKDAYS
Formula:
=NETWORKDAYS(Start Date, End Date, # of holidays)

The NETWORKDAYS formula give us the number of only working days between Start Date and End Date. Working days exclude weekends and any dates identified in holidays. We can use the NETWORKDAYS formula to calculate the employee benefits that accrue based on the number of days worked during a specific term.

The # of holidays in the above formula, is an optional range of one or more dates to exclude from the working calendar, such as the government holidays. The list can be a range of cells that contains the dates.
Example:
A1 = 1-Nov-14 and A2 = 26-Nov-14

Then the formula =NETWORKDAYS(A1,B1) will give us the output as 18 days. This formula excludes the weekends from the days difference count. In one more example where we have holidays, say we have the dates in cell C1 = 4-Nov-14 and C2 = 5-Nov-14, then the formula =NETWORKDAYS(A1,B1,C1:C2) will give us the output as 16, now in this formula the weekends + holidays listed in C1 and C2 are excluded in the days.


9. NOW
Formula:
=NOW()

The NOW formula give us the current date and time. If the cell format was General before the function was entered, Excel changes the cell format to the same date and time format that is specified in the regional date and time settings in Control Panel.

The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.

Note:
If the NOW function does not update cell values when you expect it to, you might need to change settings that control when the workbook or worksheet recalculates. In the Formulas category under Calculation options, make sure that Automatic is selected to calculate the formulas automatically.
Example:
The formula =NOW() will give us the output as 11/26/2014 14:36, this is the current date and time when the function was entered in excel. This date and time will keep on changing, whenever the sheet reclculates or whenever the sheet is re-opened. This function will always return the current date and time in the system.


10. TODAY
Formula: =TODAY()

The formula =TODAY() will return the current date. If the cell format was General before the function was entered, Excel changes the cell format to Date. The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals.

Example:
You can calulate the age of the person. Say if your birth year is 1984 which is the value in cell A1 and =today() in cell A2, then the below formula can be use to find that person's age as of this year's birthday:
=YEAR(A2)-A1 this formula will give us the output as 30.

in above formula we have used the YEAR formula to get the YEAR from TODAY() and then subtracted it to the birth year i.e., 2014-1984 = 30 yrs.


11. WEEKNUM
Formula:
=WEEKNUM(Date,Day)

The WEEKNUM formula provides us with the week number of a specific date.
For example, the week containing January 1 is the first week of the year, and is numbered week 1.

There are two systems used for this function:
System 1:  The week containing January 1 is the first week of the year, and is numbered week 1.
System 2:  The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1.
Example:
A1 = 1-Nov-14
The formula =WEEKNUM(A1) will give us the output as week 45. Say we use the formula,
=WEEKNUM(A1,2), this formula will give us the output as 44. Here the 2 represents the week starting on Monday or if we use 1 then week starting on Sunday. In case of 1 in the same formula the output will be 45. If nothing is entereted in the Day part in above formulam, then the WEEKNUM will by default consider 1(Sunday) to calculate the week number.
The formula


12. WEEKDAY
Formula:
=WEEKDAY(Date,Type)

The WEEKDAY formula gives us the the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.

Example:
A1 = 1-Nov-14
The formula =WEEKDAY(A1) will give us the output as 7 which is Saturday i.e., 7th day of the week. If you further use the formula =WEEKDAY(A1,2) will give you the output as 6. Here the WEEKDAY is starting from 2 (Monday) hence, from monday Staturday is the 6th Day, hence the formula returns 6th Day of the week.

Note:
By default the WEEKDAY formula considers the weekstart from day 1(Sunday). To change this further you can use 1,2 3....

What are the Logical and Reference functions in Excel ?

1. AND
Formula:
=AND(Condition 1, Condition 2, ...)

Returns TRUE if all the conditions are met, ie.e., evaluated to TRUE; returns FALSE if one or more conditions are not met, i.e., evaluated to FALSE.

One common use for the AND function is to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the AND function as the logical_test argument of the IF function, you can test many different conditions instead of just one.

Note:
The data must evaluate to logical values, such as TRUE or FALSE, or the conditions must be arrays or references that contain logical values.
If an array or reference data contains text or empty cells, those values are ignored.
If the specified range contains no logical values, the AND function returns the #VALUE! error value.
Example:
A1 = 90

The formula =AND( 9<A1, A1<99) gives us the result as TRUE
The formula =AND( 9>A1, A1<99) will give us the result as FALSE, as the first condition is not met i.e., 9 is not greated than 90.


2. IF
Formula:
=IF(logical test, value if logical test true, value if if logical test false)

The IF function returns one value if a condition you specify is met i.e., evaluated to TRUE, and another value if that condition evaluates to FALSE.

Example:
A1 = 21
The formula =IF(A1>20,"Over 20","20 or less") returns "Over 20" if A1 is greater than 20, and "20 or less" if A1 is less than or equal to 20.

Note:
Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out. Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF or the COUNTIFS worksheet functions. To calculate a sum based on a string of text or a number within a range, use the SUMIF or the SUMIFS worksheet functions.


3. IFERROR
Formula:
=IFERROR(value, value if error)

This function returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR function to trap and handle errors in a formula.

Note:
The value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.If value or value_if_error is an empty cell, IFERROR treats it as an empty string value ("").If value is an array formula, IFERROR returns an array of results for each cell in the range specified in value.

Example:
A1 = 10, B1 = 5
A2 = 10, B2 = 0

The formula =IFERROR(A1/B1,"Check the numbers in calculation") will return output as 2, as there is no error in the calculation.
The formula =IFERROR(A2/B2,"Check the numbers in calculation") will return output as "Check the numbers in calculation", as 10/0 will retrun us with #DIV/0! error.

4. OR
Formula:
=OR(Conditon 1, Conditon 2, ...)

This formula returns TRUE if any of the conditions are TRUE; returns FALSE if all the conditions are FALSE.

The conditions must evaluate to logical values such as TRUE or FALSE, or in arrays or references that contain logical values.
If an array or reference argument contains text or empty cells, those values are ignored.
If the specified range contains no logical values, OR returns the #VALUE! error value.
You can use an OR array formula to see if a value occurs in an array. To enter an array formula, press CTRL+SHIFT+ENTER.
Example:
The formula =OR(1+1=2, 2+2=4) will give us output as TRUE
The formula =OR(1+1=2, 2+2=2) will give us output as TRUE
The formula =OR(1+1=1, 2+2=2) will give us output as FALSE

in first example both the conditions are true, hence the output is TRUE.
in second exmaple first condition is true, hence the output is TRUE.
in third example both the conditions are false, hence the output is FALSE.


5. CHOOSE
Formula:
=CHOOSE(index number, value1, value2, ...)

This function uses the index number to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index number.

Example:
If value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as Index Number.

A1 = 21-Nov-2014, A2 = Weekday(A1) output in A2 = 6

=CHOOSE(A2,"Sun","Mon","Tue","Wed","Thu","Fri","Sat") will return as output as Fri, as Fri is the the sixth value in the CHOOSE function.

we can further merge the weekday formula to work with CHOOSE:
=CHOOSE(Weekday(A1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

Note:
If index_num is an array, every value is evaluated when CHOOSE is evaluated.
The value arguments to CHOOSE can be range references as well as single values.


6. COLUMN
Formula:
=COLUMN(reference)

This formula returns the column number of the given cell reference. For example, the formula =COLUMN(E9) returns 5, because column E is the fifth column.

Note:
If the reference data is a range of cells, and if the COLUMN function is not entered as a horizontal array formula, the COLUMN function returns the number of the leftmost column.
If the reference data is omitted, it is assumed to be the reference of the cell in which the COLUMN function appears.
The reference data cannot refer to multiple areas.


7. HLOOKUP
Formula:
=HLOOKUP(lookup value, data range, row index number, range lookup)

The Hlookup function searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array.

We can Use HLOOKUP when our comparison values are located in a row across the top of a table of data, and we want to look down a specified number of rows. The H in HLOOKUP stands for "Horizontal."

Note:
If HLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.
If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value.
If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
For example of HLOOKUP click here.


8. INDEX
Formula:
=INDEX(data range, row number, column number)

The INDEX formula returns a value or the reference to a value from within a table or range. The INDEX function returns the value of an element in a table or an array , selected by the row and column number indexes.

Note:
If both the row number and column number values are used, the INDEX returns the value in the cell at the intersection of row number and column number.
If you set row number or column number to 0 (zero), the INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press CTRL+SHIFT+ENTER.
Row number and column number must point to a cell within array; otherwise, INDEX will return the #REF! error value.
Example:
A1 = Orange, B1 = Grapes, A2 = Watermelon and B2 = Pears

The formula =INDEX(A1:B2,2,2) will give us the output as Pears, as the pears is located in the 2nd row and 2nd column of the array.

Similarly the formula =INDEX(A1:B2,2,1) will return the output as Watermelon.


9. INDIRECT
Formula:
=INDIRECT(reference text, range)

This function returns the reference specified by a text string. References are immediately evaluated to display their contents. We can use INDIRECT when we need to change the reference to a cell within a formula without changing the formula itself.

Note:
If the referenc text is not a valid cell reference, INDIRECT returns the #REF! error value.
If the reference text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error.
This behavior is different from Excel versions earlier than Microsoft Office Excel 2007, which ignore the exceeded limit and return a value.

Example:
A1 = Sanjay, B1 = 100,
A2 = Kiran, B2 = 200,
A3 = Vijay, B3 = 150

The formula =INDIRECT($A$2) will give us output as 200, as this is the value of our reference Kiran in cell A2.
Similarly the formula =INDIRECT($A$3) will give us output as 150


10. MATCH
Formula:
=MATCH(value we need to lookup, lookup data range, match type)

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 9, 99, and 39, then the formula
=MATCH(99,A1:A3,0) will give us the output as the number 2, because 99 is the second item in the range.

MATCH is a very useful function to locate the position of the data. MATCH can be merged with the INDEX function to find the row and column index number.

Note:
MATCH returns the position of the matched value within lookup array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, which is the relative position of "b" within the array {"a","b","c"}.
MATCH does not distinguish between uppercase and lowercase letters when matching text values.
If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
If match type is 0 and lookup value is a text string, you can use the wildcard
characters — the question mark (?) and asterisk (*) — in the lookup value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Example:
A1 = Oranges,        B1 = 9,
A2 = Grapes,          B2 = 19,
A3 = Pears,            B3 = 29,
A4 = Watermelon,   B4 = 39

The formula =MATCH(39, B1:B4,0) will give us the output as 4, as it is the fourth positon of 39 in our array. (0 is for exact match)

Similarly the formula =MATCH(30, B1:B4,1) will give us the output as 3, as the lowest number close to 30 is 29 and the position of 29 is the third positon in our array. (1 is for approximate match)

Let's learn some Text functions in excel

1. CONCATENATE
Formula:
=CONCATENATE(A1," ",B1," ",C1)

Concatenate formula is used to combine two or more words in single cell.

Example:
Cell A1 = Vinod, B1 = Shyam, C1 = Verma
with the above formula, we will get the result as "Vinod Shyam Verma" in single cell. The " " is used for creating space between two words.

This can also be acheived by directly writing formulas as:
=A1 & " " & B1 & " " & C1
here & is used in place of comma's to directly concatenate the words.


2. FIND
Formula:
=FIND(find_text, within_text, [start_num])

The FIND function is used to locate the position of one text within a second text , and return the number of the starting position of the first text from the first character of the second text.

Example:
The formula =FIND("t","complete") will give output as 7, as t is located on 7th number in the word.
The formula =FIND("a","complete") will return error as #VALUE!, as the text to find is not present in the word.

FIND is case sensitive and does not allow wildcard characters.
If find_text is "" (empty text), FIND matches the first character in the search string (that is, the character numbered start_num or 1).
If start_num is not greater than zero, FIND returns the #VALUE! error value.
Use start_num to skip a specified number of characters. Using FIND as an example, suppose you are working with the text string "FYA2014.YouthSignsRoute". To find the number of the first "Y" in the descriptive part of the text string, set start_num equal to 8 so that the serial-number portion of the text is not searched. FIND begins with character 8, finds find_text at the next character, and returns the number 9. FIND always returns the number of characters from the start of within_text, counting the characters you skip if start_num is greater than 1.


3. MID
Formula:
=MID(text, start number, number of characters)

These formulas return the number of characters from a text string. MID gives you the specified number of characters from the middle of the word.

You can specify in the MID formula where to start with the start_number and then it counts the specified number of characters to the right of the start_number.

A1 = "How are you"
Use the MID formula to get the middle word.
=MID(A1,5,3) formula gives us the word "are" from "How are you".


4. LEFT
Formula:
=LEFT(text, number of characters)

These formulas return the number of characters from a text string. LEFT gives you the number of characters from the left.
In the below example we have used the LEFT formula to get the first word.
A1 = "How are you"
=LEFT(A1,1) formula gives  us the word "H" from "how are you".



5. RIGHT
Formula:
= RIGHT(text, number of characters)

These formulas return the number of characters from a text string. RIGHT gives you the number of characters from the right of the text string.

A1 = "How are you"
Use the RIGHT formula to get the last word.
=RIGHT(A1,3) formula gives us the word "you" from "How are you"


6. LEN
Formula:
=LEN(text)

LEN returns the number of characters in a text string, including spaces.

Example:
consider, cell A1 and A2 has below text respectively
A1 =How are you
A2 =Howareyou

=LEN(A1) will give you result as 11, as it also counts spaces between words
=LEN(A2) will give you result as 9, as there are no spaces


7. LOWER
Formula:
=LOWER(text)

Converts all uppercase letters in a text string to lowercase. LOWER does not change characters in text that are not letters.
Example:
The formula =LOWER(Earn Your Way) will give output as "earn your way"
The formula =LOWER(E3. B23 Way) will give output as "e3. b23 way"


8. UPPER
Formula:
=UPPER(text)

Converts all lowercase letters in a text string to uppercase. UPPER does not change characters in text that are not letters.

Example:
The formula =UPPER(your way) will give output as "YOUR WAY"
The formula =LOWER(neat) will give output as "NEAT"


9. PROPER
Formula:
=PROPER(text)
The PROPER formula Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

Example:
A1 = this is a HEADING
A2 = 99ReaDing
The formula =PROPER(A1) will give output as "This Is A Heading"
The formula =PROPER(A2) will give output as "99Reading"


10. REPT
Formula:
=REPT(text, number_times)

This function Repeats the text a given number of times. We can use the REPT to fill a cell with a number of instances of a text string.
Note:
If number_times is 0 (zero), REPT returns "" (empty text).
If number_times is not an integer, it is truncated.
The result of the REPT function cannot be longer than 32,767 characters, or REPT returns #VALUE!.
Example:
The formula =REPT("%",5) will give output as %%%%%
The formula =REPT("Why",2) will give output as WhyWhy


11. SUBSTITUTE
Formula:
=SUBSTITUTE(the source text, old text to replace, new text to replace old text, instance number)

Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.

Here instance number is optional, it is used only when the text or number you want to replace is repeating more than once.

Example:
A1 = Exam Dashboard
A2 = Q3, 1933

The formula =SUBSTITUTE(A1,"Exam","Result") will give output as "Result Dashboard)
The formula =SUBSTITUTE(A2,"3","8",2) will give output as "Q3, 1983", here in this example the 2nd instance of 3 is replaced with 8.


12.TRIM
Formula:
=TRIM(A1)

You can use this functon to remove any space in a cell, except for single spaces between words. This formula is extremely useful because very often we run into situations where you pull data from a database and for some reason extra spaces are put in behind or in front of the data. This can create big mess if you are trying to compare using IF statements or VLOOKUP’s.
Example:
The formula =Trim( Why) will give output as "Why", the space before Why is removed.
The formula =Trim(When  ) will give output as "When", the spaces at the end of When are removed.


13. VALUE
Formula:
=VALUE(text)

This formula converts a text that represents a number to a number.

Text can be in any of the constant number, date, or time formats recognized by Microsoft Excel. If text is not in one of these formats, VALUE returns the #VALUE! error value.

You do not generally need to use the VALUE function in a formula because Excel automatically converts text to numbers as necessary. This function is provided for compatibility with other spreadsheet programs.
Example:
The formula =VALUE("2.999") will give output as 2.999.
The formula =VALUE("why") will give output as error, as there is no integer value to convert.


14. EXACT
Formula:
=EXACT(text1, text2)

Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document.

Example:
A1 = Love and B1 = love
A2 = flower and B2 = flower

The formula =EXACT(A1,B1) will give output as FALSE.
The formula =EXACT(A2,B2) will give output as TRUE.

Good to know mathematical functions in Excel ?

1. AVERAGE
Formula:
=AVERAGE(A1:A9)

Average formula returns the average (arithmetic mean) of the selected data. For example, if the data range A1:A9 contains numbers, the formula =AVERAGE(A1:A9) returns the average of those numbers.

Description:
AVERAGE(number1, [number2], ...)

Number1  is required. The first number, cell reference, or range for which you want the average.
Number2, is  optional. Additional numbers, cell references or ranges for which you want the average, up to a maximum of 255.
Average is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers.

Example:
The average of 2, 7, 4, 8, 6, and 9 is 36 divided by 6, which is 6.

2. INT
Formula:
=INT(number)

Rounds a number down to the nearest integer. i.e. this function converts the decimal number to the integer lower than it.

Example :
The formula =INT(9.234) will give output as 9

Note:
INT will not roundoff the number but converts the decimal number to lowest round number. e.g. =INT(9.564) will also give output as 9


3. MOD
Formula:
=MOD(Number, Divisor)

This function returns the remainder after number is divided by divisor. The result of this function has the same sign as divisor.

Note:
If divisor is 0, then the MOD function returns the #DIV/0! error value.

Example :
=MOD(6,9) will give output as 6
=MOD(-6,-9) will give output as -6, as mentioned above, the output sign remains same as divisor sign


4. RAND
Formula:
=RAND()

The RAND function returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.

If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press F9 to change the formula to a random number. You will get a one random number for once and the formula will be replaced by the number.

Example:
The formula =RAND()*90 will give output with a random number greater than or equal to 0 but less than 90. You can change the 90 in this formula, to the number you want.


5. ROUND
Formula:
=ROUND(A1, 2)

The ROUND function rounds a number to a specified number of digits.

Example:
If cell A1 contains 99.5682, and you want to round that value to two decimal places, you can use the above formula. The output of this function will be 99.57

Further if you use the formula =ROUND(A1, 0), the output of this function will be 100.

Description:
ROUND(number, num_digits)
number  is required. Enter the number that you want to round.
num_digits  is required. The number of digits to which you want to round the number.
If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.
If num_digits is 0, the number is rounded to the nearest integer.
If num_digits is less than 0, the number is rounded to the left of the decimal point.


6. EVEN
Formula:
=Even(number)

The EVEN function returns the number rounded up to the nearest even integer. You can use this function for processing items that come in twos. For example, a packing box accepts rows of only one or two items. The crate is full when the number of items, rounded up to the nearest two, matches the box capacity.

If number is nonnumeric, EVEN returns the #VALUE! error value.
Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an even integer, no rounding occurs.

Example:
The formula =EVEN(3) will give output as 4, as it is the nearest even integer value.
The formula =EVEN(-1) will give output as -1, as it is the nearest even integer value on the negative side.


7. MROUND
Formula:
=MROUND(number, multiple)

The MROUND function returns the number rounded to the desired multiple value.

MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple.

Example:
The formula =MROUND(9,4) will give the output as 8, which is the nearest multiple of 4.
The formula =MROUND(9.9,3.2) will give the output as 9.6, which is the nearest multiple of 3.2.


8. ODD
Formula:
=ODD(number)

The ODD function, returns the number rounded up to the nearest odd integer.

If number is nonnumeric, ODD returns the #VALUE! error value.
Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an odd integer, no rounding occurs.
Example:
The formula =ODD(4) will give the output as 5, which is the nearest odd number.
The formula =ODD(9.9) will give the output as 11, which is the nearest odd number.


9. PRODUCT
Formula:
=PRODUCT(number1, [number2], ...)

The PRODUCT function multiplies all the numbers in a data range selected in the formula and returns as the product.

Example :
If cells A1 and A2 contains numbers, you can use the formula =PRODUCT(A1, A2) to multiply those two numbers together. You can also perform the same operation by using the multiply (*) mathematical operator; for example, =A1 * A2.

But the PRODUCT function becomes very useful when, you need to multiply many cells together. For example, the formula =PRODUCT(A1:A3, C1:C3) is equivalent to =A1 * A2 * A3 * C1 * C2 * C3.

Note:
If the data range is an array or reference, only numbers in the array or reference are multiplied. Empty cells, logical values, and text in the array or reference are ignored.


10. SUMPRODUCT
Formula:
=SUMPRODUCT(array1, [array2], [array3], ...)

The SUMPRODUCT function, multiplies the values in the given arrays, and returns the sum of those products.

Example:
The formula =SUMPRODUCT(A2:B5, C2:D5)  will work as =(A2*C2 + B2*D2 + A3*C3 + B3*D3 + A4*C4 + B4*D4 + A5*C5 + B5*D5)

This example can also be explained by sum formula =SUM(A2:B5 * C2:D5).

Must know excel formulas ?

1. COUNT
Formula: =COUNT(A1:A90)

The COUNT formula counts the number of cells that contain numbers, and counts numbers within the list of data. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.
For example, you can enter the following formula to count the numbers in the range A1:A90.

In this example, if nine of the cells in the range contain numbers, the result is 9.


2. COUNTA
Formula: =COUNTA(A1:A90)

The COUNTA function counts the number of cells that are not empty in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.).

The COUNTA function counts cells containing any type of information, including error values and empty text (""). For example, if the range contains a formula that returns an empty string, the COUNTA function counts that value. The COUNTA function does not count empty cells.

If you do not need to count logical values, text, or error values (in other words, if you want to count only cells that contain numbers), use the COUNT function.

3. COUNTIF

Formula: =COUNTIF(B2:B29,"Criteria")

The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify. For example, you can count all the cells that start with a certain letter, or you can count all the cells that contain a number that is larger or smaller than a number you specify. For example, suppose you have a worksheet that contains a list of tasks in column A, and the first name of the person assigned to each task in column B. You can use the COUNTIF function to count how many times a person's name appears in column B and, in that way, determine how many tasks are assigned to that person.

For example:
=COUNTIF(B2:B29,"Ravi")


=COUNTIF(Range,Criteria)

range  required - One or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
criteria  required. A number, expression, cell reference, or text string that defines which cells will be counted. For example, criteria can be expressed as 23, ">23", C9, "oranges", or "23".

You can use the wildcard characters — the question mark (?) and the asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters.

If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Criteria are case insensitive; for example, the string "oranges" and the string "ORANGES" will match the same cells.

4. COUNTIFS

Formula: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Applies criteria to cells across multiple ranges and counts the number of times all criteria are met.

The COUNTIFS function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

criteria_range1  required. The first range in which to evaluate the associated criteria.
criteria1  required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted.
For example, criteria can be expressed as 23, ">23", C9, "oranges", or "23".
criteria_range2, criteria2, ...  Optional.

Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other.
Each range's criteria is applied one cell at a time. If all of the first cells meet their associated criteria, the count increases by 1. If all of the second cells meet their associated criteria, the count increases by 1 again, and so on until all of the cells are evaluated.
If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.
You can use the wildcard characters— the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.


5. Sum

Formula : =SUM(number1,[number2],...])

The SUM function adds all the numbers that you specify within a range.
Each data range can be a range, a cell reference, an array, a constant, a formula, or the result from another function.

For example, SUM(A1:A9) adds all the numbers that are contained in cells A1 through A9. For another example, SUM(A1, A5, A9) adds the numbers that are contained in cells A1, A5, and A9.

If the data in the range is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.

If the data in the range has error values, or has text that cannot be translated into numbers, Excel displays an error.


6. Sumif

Formula: =SUMIF(B2:B99,">9")

You can use the SUMIF function to sum the values in a range, that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 9. You can use the above formula.

In this example, the criteria is applied the same values that are being summed. If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B9, "orange", C2:C9) sums only the values in the range C2:C9, where the corresponding cells in the range B2:B9 equal "orange".


=SUMIF(range, criteria, [sum_range])

Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is numeric, double quotation marks are not required.
sum_range  Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).
It almost works same as COUNTIF function, but instead of count it gives sum as ooutput.


7. Sumifs

Formula: =SUMIFS(A1:A99, B1:B99, ">6", C1:C99, "<19")

Adds the cells in a range, that meet multiple criteria. For example, if you want to sum the numbers in the range A1:A99 only when the numbers in B1:B99 are greater than six (6) and the numbers in C1:C99 are less than 19, you can use the above formula.

The order of the data can differ between the SUMIFS and SUMIF functions. In particular, the sum_range comes first in SUMIFS, but it is the third in SUMIF. If you are copying and editing these similar functions, make sure you put the formulas in the correct order.

8. LEN

Formula: =LEN(text)

LEN returns the number of characters in a text string, including spaces.

Example:
consider, cell A1 and A2 has below text respectively
A1 =How are you
A2 =Howareyou

=LEN(A1) will give you result as 11, as it also counts spaces between words
=LEN(A2) will give you result as 9, as there are no spaces


9. TRIM

Formula: =TRIM(A1)

You can use this functon to remove any space in a cell, except for single spaces between words. This formula is extremely useful because very often we run into situations where you pull data from a database and for some reason extra spaces are put in behind or in front of the data. This can create big mess if you are trying to compare using IF statements or VLOOKUP’s.

10. MID, LEFT, RIGHT

Formulas:
=MID(text, start number, number of characters)
=LEFT(text, number of characters)
= RIGHT(text, number of characters)

These formulas return the number of characters from a text string. MID gives you the specified number of characters from the middle of the word, LEFT gives you the number of characters from the left, and RIGHT gives you the number of characters from the right of the text string.

You can specify in the MID formula where to start with the start_number and then it counts the specified number of characters to the right of the start_number.

In the below example we have used the LEFT formula to get the first word.
A1 = "How are you"
=LEFT(A1,1) formula gives  us the word "H" from "how are you".

Use the MID formula to get the middle word.
=MID(A1,5,3) formula gives us the word "are" from "How are you".

Use the RIGHT formula to get the last word.
=RIGHT(A1,3) formula gives us the word "you" from "How are you".


11. Concatenate

Formula: =Concatenate(A1," ",B1," ",C1)

Concatenate formula is used to combine two or more words in single cell.

Example: Cell A1 = Vinod, B1 = Shyam, C1 = Verma
with the above formula, we will get the result as "Vinod Shyam Verma" in single cell. The " " is used for creating space between two words.

This can also be acheived by directly writing formulas as:
=A1 & " " & B1 & " " & C1
here & is used in place of comma's to directly concatenate the words.

12. AVERAGEIF

Formula: =AVERAGEIF(range, criteria, [average_range])

Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.
In the AVERAGEIF function, the cells in range that contain TRUE or FALSE are ignored.
If a cell in average_range is an empty cell, AVERAGEIF ignores it. If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.
If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.


13. AVERAGEIFS

Formula: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Returns the average (arithmetic mean) of all cells that meet multiple criteria.This is similar function to that of countifs and sumifs.

If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value. If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value. Cells in range that contain TRUE evaluate as 1; cells in range that contain FALSE evaluate as 0 (zero).

Each cell in average_range is used in the average calculation only if all of the corresponding criteria specified are true for that cell. Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same size and shape as sum_range. If cells in average_range cannot be translated into numbers, AVERAGEIFS returns the #DIV0! error value. If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.

Must know tips in Excel ?

Tips for Navigation

1. Press [Ctrl] + [Home] and it will takes you to cell A1

2. Press [Ctrl] + [End] and it will take you to the last data cell in your worksheet

3. Press [Ctrl] + [PgDn] to move to next sheet

4. Press [Ctrl] + [PgUp] to move to previous sheet

5. The control buttons on the down left corner of the sheet tabs lets you

navigate through sheets. You can right-click on these control buttons and from the resulting menu you can select the sheet you want to view.

6. Press [Ctrl] + [Tab] to move to the next window (i.e. to next open workbook)

7. The Status Bar, at the very bottom of the screen usually says Ready in the lower lefthand corner. It provides us with useful informations. One of the useful feature is that when a block of cells is selected the SUM of the cells will appear in the Status Bar. Right-click the SUM in the Status Bar and you can choose another available functions to apply to the selected cells. You can't do anything with this result, however, except view it in the Status Bar.

  

8. View à Split or View à Freeze Panes will divide the window above and to the left of the current cell pointer position. This will allow column and /or row headers to remain displayed in one section of the window while you can scroll and move through data in another section of the window.
You can also split the window by dragging the little gray bar above the up arrow in the vertical scroll bar and /or the little gray bar to the right of the right arrow in the horizontal scroll bar.

9. With View à Arrange All option you can arrange the open workbooks with the below options :

Tiled | Horizontal | Vertical | Cascade

10. With View à Switch Window Option you will be able to see all open workbooks in the dropdown. From here you can directly select the workbook you need to view.

Note - all tips are tested for Microsoft Excel 2010


Tips for Selecting Cells

1. Hold [Ctrl] while using mouse to select the cells it will help you to select a random / non-contiguous block of cells

2. Click the mouse once in the upper lefthand corner cell in the block of data you want to select, then hold the [Shift] key down when you click the mouse on the cell at the the lower righthand corner of your block. This will select all the data in the range of cells.

3. Press [Ctrl] + [Shift] + [down arrow] to select the range of cells in a column in downward directed from the selected cell.

4. Press [Ctrl] + [Shift] + [up arrow] to select the range of cells in a column in upward direction from the selected cell.

5. Press [Ctrl] + [Shift] + [right arrow] to select the range of cells in a row in right direction from the selected cell.

6. Press [Ctrl] + [Shift] + [left arrow] to select the range of cells in a row in left direction from the selected cell.

7. Press [Ctrl] + [*] or [Ctrl] + [A]  to select cuurent block of data. This selected block of data can be moved with the help of mouse to some other location. Also, if you press [Ctrl] while moving the data, this will copy the data to other location.

Note - all tips are tested for Microsoft Excel 2010


Formatting the Cells

1. You can use the Format | Conditional Formatting to check the different logical conditions and highlight the cells based on that. e.g. error checking (invalid values can show in the formmatting we have selected)
2. In conditional formatting, If you have specified multiple conditions, then the conditions will be evaluated from the top of the list. Once the cell satisfies a condition it applies that formatting and doesn't continue down through the rest of the possible conditions.

3. To find cells that are formatted with Conditional Formatting use Edit | Go To... | Special and choose the Conditional formats radio button.

4. To find cells with identical conditional formats to the selected cell, click Same below Data validation.

5. To find cells with any conditional formats, click All below Data validation.

6. Tools | Options | Calculation | Precision as Displayed  checkbox can prevent you from falling into a potentially embarrassing "rounding error" situation 
7. You can transpose your columns to rows or rows to columns? Copy the data you want to transpose, go to Home | Paste | Paste Special dialog box | click on Transpose check box and then OK.

8. Home | Clear will provide you with different options to clear the selected cells. i.e, Clear All | Clear formats | Clear contents | Clear comments | Clear Hyperlinks

9. There may be certain portions of the worksheet that you'd like to protect from any possible changes. By default all the cells in the worksheet are locked but the locks are ignored. Review | Protect Sheet activates recognition of the locks. Before using Protect Sheet you would unlock all the cells you want to be able to edit when the rest of sheet is protected. Select Cells, Format | Cells | Protection tab and uncheck the default lock. Then use Review | Protect Sheet.

10. If most of your cells are going to be unprotected with just a few protected
Use [Ctrl] + [A] to select all the cells in the sheet Format | Cells | Protection tab and uncheck the default lock. Select the cells you DO want to protect Format | Cells | Protection tab and CHECK the lock back on
Review | Protect Sheet

Note - all tips are tested for Microsoft Excel 2010