Search This Blog

Sunday, January 28, 2018

FIND Function

Hi Friends,

In this article I'll explain how to use FIND function in excel, although we do not use this function in basic excel function but it is very useful in Excel VBA or Excel Macro.


So let us learn it first. As the name of this function suggest it is used to find some text or string within a cell. If FIND function is used nested with IF function then it can help you a lot.

String means a  text, in programming language we use string instead of text, those who are from computer programming background will understand the logic, for now please note String means Text.

Please note that this function is case sensitive. You must type the proper case while searching a text using FIND function.

Description

The Microsoft Excel FIND function returns the location of a substring in a string. The search is case-sensitive.

The FIND function is a built-in function in Excel that is categorized as a String/Text Function The FIND function can be entered as part of a formula in a cell of a worksheet and we will use this property with IF function nested.

Syntax

The syntax for the FIND function in Microsoft Excel is very simple.

=FIND( substring, string, start_position)

Parameters or Arguments

Substring (part of text)

The substring or part of text that you want to find.

String

The string or text to search within.

start_position

Optional. It is the position in string where the search will start. The first position is 1.

Returns

The FIND function returns a numeric value, the first position in the string is 1.

If the FIND function does not find a match, it will return a #VALUE! error.

Examples :

Let's look at some Excel FIND function examples and explore how to use the FIND function as a worksheet function in Microsoft Excel:

FIND Function in Microsoft Excel
FIND Function in Microsoft Excel

Based on the Excel spreadsheet above, the following FIND examples would return:

Case 1 : Finding a particular text/string
            =FIND("Basic",B2,1) in 2nd row or =FIND("blog",B3,1) in 3rd row


FIND Function in Microsoft Excel
FIND Function in Microsoft Excel


Case 2 : Case sensitivity
            I've got an error #VALUE!  -  because I am trying to find 'Fox' in the given text where it is typed as 'fox' and therefore I got an error though the formula is perfect.
            =FIND("Fox",B4,1)


FIND Function in Microsoft Excel
FIND Function in Microsoft Excel

Case 3 : This example is little bit confusing because you can notice that I am finding for 'w' and the result is 5 i.e., 5th position is the first occurrence of 'w', apparently it is wrong. The output is correct.

Here what I am trying to find is the position of 'w' after the first three 'w's. The formula in the cell I typed is =FIND("w",B5,4) meaning that find me the position of 'w' after the fourth character of the provided string. The below image will clarify it.

FIND Function in Microsoft Excel
FIND Function in Microsoft Excel

           
Hope you have enjoyed this article.

Thank you for reading...

Sunday, January 14, 2018

Fill Data Automatically Auto Fill Option in Microsoft Excel

Hi Friends,

Today I will discuss about AUTOFILL options in Microsoft Excel. Everybody want a shortcuts in Excel and autofill is such a tool in Excel which surprisingly reduces the manual work that has to be done.

What is AUTOFILL ?

AutoFill is a very useful Excel feature. It allows you to create entire columns or rows of data which are based on the values from other cells. In other words, Excel compares the selected data and tries to guess the next values that will be inserted.

Excel is becoming very intelligent day by day and it will be proved in my next articles.

Actually Excel tries to guess about the work we are doing and tries to help us for example, if you need ten months in words in consecutive ten cells let suppose you type 'January' in first cell and then if you drag the fill handler upto 9 cells the next months like February, 'March' etc will automatically be displayed. Same for numbers also. But there are difficulties. Let us understand it by few examples with images.

 To use the Simple Excel Autofill 

          1.    Excel Cell Fill Handle and Autofill Options Box
          
          2.    Enter a value into the start cell;
          
          3.    Use the mouse to drag the 'fill handle' (the small black square at the bottom right of the start cell) across the range of cells to be filled;
    
         4.    When you drag the 'fill handle' across the range of cells to be filled, Excel will fill the selected cells, by either repeating the value in the first cell or by inserting a sequence from the first cell value (e.g. 1, 2, 3, ...);

        5.    Click on the 'Auto Fill Options' box, which will appear at the end of your selected range of cells. This will give you the following different options:

Simple Excel Autofill
Simple Excel Autofill


Copy Cells - copy the initial cell across the selected range;

Fill Series - fill the selected range with a series of values (typically increment by 1), starting with the initial cell value;

Fill Formatting Only - fill the selected range with the formatting, but not the values of the initial cell;

Fill Without Formatting - fill the selected range with values, but do not copy the formatting from the initial cell.
Select the option that you required for the filled cells.

Autofill Using More Than One Starting Cell Value


Excel Auto Fill Sequence

If you want Excel to recognize a series that is not a simple increment by 1, this can be done by typing the first two values of your series into the first and second cells of a range. Select both of these cells and again, drag the fill handle across the range to be filled. Excel will automatically recognize the pattern from the two initial cells and continue this across the selected range. Using this method, you can get Excel to fill cells by increments or decrements of any number (e.g. 2, 4, 6, 8, ...).

Autofill Using More Than One Starting Cell Value
Autofill Using More Than One Starting Cell Value


Alternatively, if you want Excel to fill cells with repeated alternating values (e.g. 1, 2, 1, 2, 1, 2, ...) you can start off the pattern in the first two (or more) cells, then, with the initial cells highlighted, drag the fill handle and then click on the 'Auto Fill Options' box. Within this box, select the option 'Copy Cells' to repeat the initial cell values across the selected range.

Autofill Dates & Times


Excel Autofill Dates

As dates and times are stored in Excel as numbers, these can also be used with the Excel Autofill.

By default, if you just type in a single date or time and drag the fill handle, dates and times will complete in a series, by adding one day (for dates), or one hour (for times). However, as with simple numbers, you have the option of clicking on the 'Auto Fill Options' box, to select a different type of Auto fill.

Times have the same four Auto fill options as are shown above, for simple numbers (i.e. Copy Cells, Fill Series,Fill Formatting Only, Fill Without Formatting). However, for dates, there are additional Auto Fill options. As well as the four options for simple numbers and times, there are also the following:

Fill Days - Look for a pattern in the day when filling the selected cells;
Fill Weekdays - Look for a pattern in the day when filling the selected cells, but do not include Saturdays or Sundays in the series;
Fill Months - Look for a pattern in the month when filling the selected cells;
Fill Years - Look for a pattern in the year when filling the selected cells.

Autofill Dates & Times
Autofill Dates & Times


Autofill Functions and Formulas 

The Excel Autofill feature also works with functions and formulas in Excel which is very useful. However, with this type of Autofill, there is no 'series fill' option. Instead, Excel observes the rules of Absolute and Relative Cell References (i.e. if a row or column reference is preceded by a $ sign, excel will keep the reference constant as the formula is copied to other cells; Otherwise, the row or column reference will be adjusted as the formula is copied to other cells.

Horizontal and Vertical Autofill

Excel Autofill Across a Row

As well as working down a column, the Autofill feature also works horizontally, across rows.

Simply drag the fill handle across the cells that you want to populate.

Autofill Multiple Rows or Columns Simultaneously


The Excel Autofill can also handle data in more than one row or column. This is shown in the example below, in which cells A1 and A2 have numeric values 1 and 2, and cells B1 and B2 both have the numeric value 3.

Highlighting cells A1 to B2, and then dragging the fill handle down columns A and B causes the Autofill to complete both columns with their own series (i.e. column A completes with 1, 2, 3, 4, ..., and column B completes with 3, 3, 3, 3, ...)

Autofill Multiple Rows or Columns Simultaneously
Autofill Multiple Rows or Columns Simultaneously


Double Click on the Fill Handle

For speed, you can Autofill a column by double-clicking on the fill handle of a highlighted cell (or range of cells). If the cells below or adjacent to the highlighted cell (or range) contain values, double clicking the fill handle causes the Autofill to fill down the current column until it reaches the end of the current data range.

In my next article I will show you how to enter customize list to get these in autofill options.

Thank you for reading ...

Thursday, December 14, 2017

ROW Function in HLOOKUP function


In my last article, I discussed about the function COLUMN & COLUMNS and the use of these functions with VLOOKUP, nested.

In this article, I will show you another function ROW which is also is a useful function in Microsoft Excel if used wisely.

ROW() works same as the COLUMN function, the only difference is it gives you the number of the row where this function is written. 


Let us first describe the function and then we will see how can we use this function in Excel.

Description

The Microsoft Excel ROW function returns the row number of a cell reference.

The ROW function is a built-in function in Excel that is categorized as a Lookup/Reference Function.

Syntax
The syntax for the ROW function in Microsoft Excel is as follow.

=ROW( [reference] )

Parameters or Arguments

Reference
Optional. It is a reference to a cell or range of cells.
Returns

The ROW function returns a numeric value.

ROW Function without Reference
ROW Function without Reference


It’s a simple function to remember and to use. The very basic question which may arise in your mind is where to use it. I use ROW function to make HLOOKUP dynamic. Why HLOOKUP ?

Because HLOOKUP is such a function which looks up for data row wise, and like VLOOKUP where it asks for COLUMN number, HLOOKUP asks for ROW number. We must enter the ROW number to get the desired data using HLOOKUP.

The trick is to use ROW function in HLOOKUP’s row_index_number argument or parameter.

Lets take one example of it.

Suppose we have a table arranged in the following way. We should get the data row wise using HLOOKUP. The syntax of the HLOOKUP function is as follows.
=HLOOKUP(lookup_value, table-array, row_index_number, [range_lookup])

Row function in Microsoft Excel
Row function in Microsoft Excel


Now instead of writing the row number in row_index_number, if we write ROW() and if we show this function the reference, it will not be necessary to add the row number manually. Because the row number now will be supplied by the ROW function itself.

Please note that while entering the function ROW within HLOOKUP function you have to give the cell's address in the ROW function.

=HLOOKUP(E15,E2:H5,ROW(E2),0)

Please refer to the below image. The working of ROW function is same as the working of COLUMN function.

ROW function in HLOOKUP function
ROW function in HLOOKUP function



Hope you have enjoyed this article.
Thanks for reading... 

Tuesday, November 28, 2017

COLUMNS Function in VLOOKUP function

Friends,

In my last article I discussed about COLUMN function and I've shown you how to use it with VLOOKUPfunction to make VLOOKUP function more dynamic.

But COLUMN function is good for single column matching, what we will do when there are multiple columns to be matched easily ?

In the function COLUMN we give the reference cell's address and we get the number from the first column (i.e., column 'A') and we use this property of COLUMN function in the VLOOKUP function in column index number to get a dynamic VLOOKUP formula.

But sometimes we face situations where we have to put the column index number in VLOOKUP function not from the first column in the Excel sheet but from column other than 'A' column or first column (e.g., from column 'C4' to 'F4'). In this situation we can use COLUMN function but a little tweak is require (which I'll show in other article). Here in this article we will use another function which will serve the same purpose.

Microsoft Excel provides a function, almost same as COLUMN function that can resolve this problem, the function is COLUMNS. Note, it is COLUMNS, an extra 's' is added at the end which suggest that it can be used to count the number of columns more than one from any starting column.

We need to pass an ARRAY into COLUMNS function which will return the number of columns that is included in the array. Please don't be anxious about ARRAY, it is very easy besides if you don't know about ARRAY you can still use this function.

It is also one of the easiest functions in Microsoft Excel. Lets' first understand this function then we will use it in VLOOKUP function to make VLOOKUP function more dynamic.

Description

The Microsoft Excel COLUMNS function returns the number of columns in a cell reference.

The COLUMNS function is a built-in function in Excel that is categorized as a Lookup/Reference Function.

Syntax

The syntax for the COLUMNS function in Microsoft Excel is:

=COLUMNS(array)

Returns

The COLUMNS function returns a numeric value.

Example of COLUMNS function


Write "=COLUMNS(D2:F2)" in G2 cell and it will return the number of columns from 'D' to 'F' i.e., 3 in this case.

How to use COLUMNS function in Excel
How to use COLUMNS function in Excel


Now we'll use this numeric value in column index number portion of VLOOKUP function.

How use COLUMNS function in VLOOKUP function


Normally VLOOKUP function looks like =VLOOKUP(J2,B1:F10,5,0). Here 5 is the column index number, we'll replace this by COLUMNS(B2:F2). Hence the formula now looks =VLOOKUP(J2,B1:F10,COLUMNS(B2:F2),0)

COLUMNS function in VLOOKUP function
COLUMNS function in VLOOKUP function


To make VLOOKUP more dynamic we have used COLUMNS function, but what about next cells, if we assume that there are more than one columns to be mapped using VLOOKUP.

Its' very simple, we'll use $ sign to restrict the incremental property of Excel formula and the we'll just drag it in a row.

The formula now becomes  : =VLOOKUP($J2,$B1:$H10,COLUMNS($B2:F2),0)

Here we have restrict the first cell so that it does not get incremented but we have left the second potion to get incremented while dragged.
= COLUMNS($B2:F2)

Please have a careful look at the below image and you will be able to understand it and this will make VLOOKUP more dynamic.

COLUMNS function in VLOOKUP function
COLUMNS function in VLOOKUP function


Hope you have enjoyed this article.
Thank you for reading...

Sunday, November 19, 2017

Column Function in VLOOKUP Function

Friends,


today I am going to discuss about  a new function in Microsoft Excel, if used with another function like VLOOKUP will prove to be a very useful function and it will make your formula very dynamic.

The function is COLUMN, as the name of this function suggest it gives us the information about column. 

In this article I'll show you how to use this function with VLOOKUP and will make our basic VLOOKUP function very dynamic and will shorten the time to write multiple VLOOKUPs.

Let us first know about this function. Its' very easy and basic function to remember and it comes under LOOKUP functions in Excel.

Description
The Microsoft Excel COLUMN function returns the column number of a cell reference.

The COLUMN function is a built-in function in Excel that is categorized as a Lookup/Reference Function.

Syntax

The syntax for the COLUMN function in Microsoft Excel is:
=COLUMN( [reference] )

Parameters or Arguments

reference
It is optional. It is a reference to a cell or range of cells. If the reference parameter is omitted, it assumes that the reference is the cell address in which the COLUMN function has been entered in.

Output or Returns

The COLUMN function returns a numeric value like 2,3,4 etc.


Example of Column Function


Write "=COLUMN()" in any cell and it will return the column number of that particular column where the function is written if there is a blank within the bracket.


Column Function without Reference
Column Function without Reference


Its' very easy... Now let me write another example ...

Write "=COLUMN()" in any cell and select or type any cell reference within the bracket e.g., "=COLUMN(D3)".
It will also return the column number of that particular column. The benefit is you can get the column number of the referred cell.


Column Function with Reference
Column Function with Reference


Now we will use this property of COLUMN function in VLOOKUP. You remember in VLOOKUP functions I wrote the syntax as VLOOKUP( value, table array, col_index, [range_lookup] ).

col_index is the column number, so if instead of writing column number I write column and then the put the reference of that column within this function then the result will be same. For eample the formula =VLOOKUP($U2,$B:$C,COLUMN(B2),0)
(Here $ sign is to fridge the cell, as you know).


VLOOKUP function in one Cell using COLUMN function
VLOOKUP function in one Cell using COLUMN function



What is the benefit we get using this function ?

or

How to use VLOOKUP function with COLUMN function ?

To understand it lets' take an example, suppose if we have four consecutive columns that we have to fill with data collected from VLOOKUP then we have to write the column numbers four times even if we have dragged the VLOOKUP formula to the consecutive cells. But we can reduce our time by dragging one VLOOKUP formula instead of writing column numbers in each and every VLOOKUP formula if we use COLUMN function within VLOOKUP function with reference cell within the column function, an example of Nested function. But do not forget to increase the range of table array otherwise it will display #REF! error, e.g.,

 from    =VLOOKUP($U2,$B:$C,COLUMN(B2),0)
to       =VLOOKUP($U2,$B:$G,COLUMN(B2),0)

The below Image will make it more clear.

VLOOKUP function in multiple Cells using COLUMN function
VLOOKUP function in multiple Cells using COLUMN function


Hope you like this article, thank you for reading...

Friday, November 10, 2017

Pivot Table Calculated Field vs Calculated Item

Friends,


Now I hope you have noticed that there is another option in Formula tab under Option or Analyze menu as Calculated Item. Today I am going to discuss with you about Calculated Item and the difference between Calculated Field and Calculated Item.


Let take an example, in the below Pivot Table I need to add Saturday and Sunday as one day and then this value should come combined with the existing data, i.e., data related to Saturday and Sunday will come as it was, additionally a new item will appear as Sat+Sun with their total value. Here we can use Calculated Item option.


Its' very simple to use, you need to know where & how to use it otherwise you will find Calculated Item as Grayed colored inactive option.

Where to Use Calculated Item ?


Calculated Item is used in situations where we need another Item in the Row Label item field, i.e., as another item in the same column. The below picture will clear it.

How to Use Calculated Item ?


Place your cursor in any cell within Pivot Table but remember the column should be in Row Labels box, i.e., select any cell in the column where you want to add the new Item.

Calculated Item
Calculated Item


Now go to Options click Formulas, from the drop down menu select or click Calculated Item.


A new window will appear as Insert Calculated Item in "***". Give a name to the new Item (in my case it is Sat+Sun). From the Fields section click on the Column Name and you will notice the Items are listed in the right side under Items head. In the formula box enter your formula (in my example it is =Sunday+Saturday). Click OK and you are done, you can now see a new Item in the Pivot Table.

Insert Calculated Item Input Box
Insert Calculated Item Input Box


That's it, the main difference between Calculated Field and Calculated Item is the first option will create an entire Field and the Calculated Item will create a Item in the Pivot Table.

Pivot Table Before entering Calculated Item
Pivot Table Before entering Calculated Item


Pivot Table After entering Calculated Item
Pivot Table After entering Calculated Item


Hope you have understand the difference between the Calculated Field and Calculated Item and now you can easily use Calculated Field and Calculated Item.


Thanks for reading...