The fill handle is a convenient Excel tool that is used to fill data with a specific pattern. You can extend a series of numbers, dates and text combinations to desired number of cells.

To use fill handle, bring the mouse cursor at right bottom corner of the cell it will change into a + sign. Now left click on it and drag it downward to the desired range of cells.

If you select one cell the value in the cell will be copied to other cells and if you choose multiple cells with specific pattern like difference between cells then the same pattern will be copied to other cells. See the image shown below.

Excel Fill Handle with Text

Fill handle works in a similar way with texts. It helps repeat the text to desired range of cells. See the images shown below:

The text is selected and then copied to other cells using fill handle.

Excel Fill Handle with Text and Numbers

Fill handle also works with cells containing both text and number. In this case, the text does not change but the number increases by one for every cell you select. See the images:

Excel Fill Handle with Dates

Fill handle can be used to fill dates in multiple cells. If you need to enter a complete month in worksheet, you don’t need to type each date separately. Just choose a cell, type the date and using fill handle carry it over other cells, Excel will enter the subsequent dates in the selected cells. See the images:

Excel If Function

The IF function or IF statement in Excel is composed of three parts separated by commas.

• A condition
• What to display if the condition is fulfilled
• What to display if the condition is not fulfilled
See how the “IF function” is created step by step to find the safe and unsafe travelling speeds in the following example.

• Select the cell in which you want to create the “IF function”
• Type the code in the cell: =if(
• Type the condition with comma: B4>70,
• Type what you want to show if condition is fulfilled. If you want to display text then write it within quotation marks: “Unsafe”
• Type a comma: ,
• Type what you want to show if the condition is not fulfilled within quotation marks: “Safe”
• Then close the bracket and press the Enter key.

The IF function created above will look like this: =if(B4>70,”Unsafe”,”Safe”)

So the IF function says if the speed or value in the cell B4 is more than 70 then display Unsafe and if it is less than 70 then display Safe.

After pressing the Enter key you will get the result for cell B4. Drag the fill handle downward till cell C9 to get status for other speeds of column B.

Excel If Function with Calculations

“IF function” can be used in complex calculations. See the example:

If a sales executive sells more than 5 items, the company will pay incentive 40 rupees per item sold and if the sales executive sells less than 5 items, the company will pay 20 rupees per item sold.

See how the “IF function” is used with the calculation:

• Select the cell in which you want to create the “IF function”
• Type the code in the cell: =if(
• Type the condition with comma: C4>5,
• Type what you want to show if condition is fulfilled: 40*C4
• Type a comma: ,
• Type what you want to show if the condition is not fulfilled: 20*C4
• Then close the bracket and press the Enter key.
The IF function will look like this: =if(C4>5,40*C4,20*C4)
So the IF function says if value in cell C4 is greater than 5 then multiply it with 40. If it is less than 5 then multiply it with 20.

The IF function can be modified to perform different calculations:

Suppose in the above example the company wants to pay rupees 50 along with incentive to those employees who have completed probation period of 5 months or their job duration is more than 5 months.

In this case, we can insert one more column in the worksheet for job months and modify the IF function accordingly to get the results.

The modified IF function is: =if(C4>5,50+E4,E4)

It says if the value in cell C4 is greater than 5 then add 50 to E4 which is incentive of Peter. And if it is less than 5 then keep the incentive, the value of cell E4 same. See the image shown below:

Few more examples of IF Function:
=if(B2>5,B2*2,B2*4)

The IF function says if value in Cell B2 is greater than 5 then multiply this value with 2 else multiply this value with 4. See the image given below:
The IF function can also be composed in this way: =IF(B2>5,2*B2,4*B2)

=IF(B2<=50,B2*120%,B2)

The IF function says if the value of cell B2 is less than 50 or equal to 50 then increase it by 20% else display the same value without any change. See the image given below:

=IF(B2=60,””,C4*D3+5)

The IF function says if the value of cell B2 equals to 60 then leave the cell blank else multiply the cell C4 with D3 and add 5 to it. See the image given below:

So, this brings us to the end of blog. This Tecklearn ‘Excel Fill Handle and Excel If Function’ blog helps you with commonly asked questions if you are looking out for a job in Microsoft Excel. If you wish to learn Excel and build a career in Business Intelligence domain, then check out our interactive, Microsoft Power BI Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Microsoft Power BI Training

Microsoft Power BI Training

About the Course

Microsoft Power BI Training at Tecklearn will help you achieve expertise in business analytics Our best online training course teaches you all important concepts like Power BI Desktop, Mobile, Power Query & Power Pivot, Data modelling, visualization, creating dashboards and reports, DAX, etc. As part of this program, you will work on real-world projects. Also, our Power BI course curriculum will equip you with all the key skills that are required to clear the Microsoft Power BI Certification exam (70-778).

Why Should you take Microsoft Power BI Training?

• The average annual pay for a Power BI Professional is $114,000 . -PayScale.com.
• Cognizant, Dell, KPMG, Hitachi, Wipro, Avanade, Annik Inc, Brillio and 45,000 MNCs across 185 countries use Power BI and it has a market share of around 7% globally.
• Microsoft Power BI has been ranked at No.1 position in the Gartner Magic Quadrant for Analytics and Business Intelligence Platforms for 13 consecutive years.

What you will Learn in this Course?

Introduction to Microsoft Power BI

• Microsoft Power BI Introduction
• Power BI Products
• Power BI Architecture
• Installing Power BI
• Connecting to Data Sources

Power BI Workflow

• Key features of Power BI workflow
• Power BI Vs MSBI
• History of Power BI
• Power BI Products
• Data modelling and relationships

Power Query for Data Transformation

• Power Query for Data Transformation
• Learning about Power Query for self-service ETL functionalities
• Working with Excel data
• Introduction to Query Editor
• Data transformation
• Pivot and UnPivot
• Merge Join, relational operators, date, time calculations, working with M functions
• Summary Tables
• Writing custom functions and error handling
• M advanced data transformations

Filters and Drill Down Report

• Visualization Filter
• Page Level Filter
• Report LEVEL Filter
• DRILL Through Filter or Report
• BookMark Report

Power Pivot for Data Modelling

• What is SSAS
• Data Modelling
• Star Schema
• Snowflake Schema
• Introduction to MDDB and Tabular
• Data Access Modes
• Tabular with DAX
• Power BI with DAX
• DAX Functions

Data Analysis Expression – DAX Queries

• TABULAR with DAX
• Power BI with DAX
• Calculated Members, Row, Filter & Evaluation Context
• Cumulative Charts
• Calculated Tables, ranking and rank over groups
• DAX advanced features

Power BI Desktop & Administration

• Reports and Visualization
• Learning about data modelling and data relationships
• Deploying data gateways
• Reports and Dashboards
• On Premises Data Gateway
• Gateway Clusters

Introduction to Power Q & A

• Power Service
• Power Q & A best practices
• Integrating with SaaS applications
• Gateway

716