Excel Soft Skills

VLOOKUP Function in Excel with Example

2020-07-26T22:38:00.018+05:00

In this Lesson, we will learn how to use VLOOKUP in Excel:

In Microsoft Excel, VLOOKUP is one of the most useful and well known excel function that allow us to search and retrieve data from one column to another location. Before start using this function, we would like to discuss, what is VLOOKUP? and what is the formula of VLOOKUP in Excel?

What is VLOOKUP?

VLOOKUP function is a very useful function in Excel, which help us to find and extract cell value from one column to another column in the same row.

According to Microsoft, The VLOOKUP function say to Excel:

=VLOOKUP(What you would like to look up - look for this piece of data/information like apple, where you want to look for it - in this data set/range, and tell me some corresponding data value about it - like the selling price of apple).

VLOOKUP Formula:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

According to Microsoft, VLOOKUP function use the following arguments as mentioned below:

Lookup_value: The value that you want to lookup.
Table_array: table range in which you are looking for the value.
Col_index: specifying the column number of table range from which you want to fetch the matching value.
Range_lookup: specify whether you want an exact match (False - if an exact match not found, then it will return an error) or an appropriate match (True - if an exact match is not found, use the closest match below the lookup_value).

How to use VLOOKUP in Excel?

To provide you better understanding, we would like to share with you an examples as mentioned below:

How to Find Closing Stock of Electronic Items Using VLOOKUP Function and How to Create Drop-Down List as Lookup value.

Example - Find Closing Stock of Electronic Items Using VLOOKUP Function:

Suppose that you are working as a assistant inventory manager and your manager need to know that what is the closing stock of some electronic items on urgent basis for re-order of electronic items.

By following below mentioned steps you can easily find out your Lookup value:

Step 1: First Step is to organize the data by creating the inventory management sheet as mentioned below:

Step 2: Now the next step is to create "Item Description" and "Closing Stock" column in Cell "C18" and "D18" as mentioned in below snapshot:

Step 3: Now the next step is to type your Item Description in Cell "C19" for which you want closing Stock quantity as mentioned below:

Step 4: Now the final step is to insert the below mentioned formula in Cell "D19", to find out the closing stock quantity of "Table Lamp":

=VLOOKUP(C19, B6:H15, 7, 0)

Now you can see the VLOOKUP function working properly, you can also change the item description to find closing stock quantity of another electronic item.

How to Create Drop-Down List as Lookup Value?

By following below mentioned steps, you can easily create drop down list for above mentioned example:

Step 1: Select Cell "C19" and Remove the Item Description as mentioned below:

Step 2: Now next step is to select Cell "C19" and click on Data menu tab:

Step 3: Now in Data Tools group, click on Data validation drop down menu and select Data validation to open Data Validation Window as mentioned in below snapshot:

Step 4: Now in Data Validation window, click on drop down menu button of validation criteria and select the "List" from drop down menu as mentioned in below mentioned snapshot:

Step 5: Now you can see that new Source field has been shown on Data Validation window, now click on Source field button as mentioned below:

Step 6: After Clicking on Source field button, select the range of Cell from "B6" to "B15" for creating the drop down list as mentioned below:

Step 7: Now again click on Source field button and click OK to close the Data validation window as attached below:

Now you can see that drop down menu has been created, you can select your desired item description using drop down list to find out the closing stock quantity.

Now you can see that your VLOOKUP function and drop down list both functions are working properly. snapshot attached below:

We hope that you will like this lesson.

Example: How to Create Unit Converter in Excel by Using "VLOOKUP Function" and "Convert Function".

---Thank You---

How to Create Equated Monthly Installment (EMI) Calculator Using VBA in Excel

2020-07-22T23:36:00.002+05:00

In this Lesson, we are going to learn how to create EMI calculator using VBA (Virtual Basic for Application) in Excel:

Before creating EMI calculator, we are going to discuss about what is EMI calculator? and what is VBA?.

EMI Calculator:

EMI calculator is the equated monthly installment calculator which help us to find out how much equal amount you need to pay every month towards repayment of your loan.

What is VBA in short:

VBA stand for virtual basic for application, is a high class language developed by Microsoft Corporation. You can use this language to develop a program to control Excel spreadsheet.

What is EMI?

EMI stand for equated monthly installment, a fixed payment amount owed each month on a land / property mortgage or any other loan.

Formula to calculate EMI is mentioned below:

Where:

P = Principle loan amount

r = Annual interest rate / 12

n = Number of monthly installments

above mentioned formula is very complex. But you can use Excel PMT function to calculate the same.

PMT function:

=PMT(rate, nper, pv, [fv], [type])

"rate" = Monthly rate (Interest rate / Number of payments per year).

"nper" = Total number of payments (Loan term x Number of payment per year).

"pv" = Present Value (Actual loan amount).

Steps to create EMI calculator:

By following below mentioned steps you can easily create EMI calculator using VBA in excel:

Step 1: First step is to create the format of EMI calculator in Excel as mentioned below:

Step 2: Now next step is to create a command button by following below mentioned steps:

1. First you need to enable macros in excel by following below steps:

Click on File tab.
Click Option.
Now click on Trust Center, then click on Trust Center Settings.
In Trust Center, Click on Macro Settings.
In Macro Settings, tick on Enable all Macros.
Click OK to save the settings.

2. Now next step to save your workbook as macro enabled workbook as mentioned below:

Now you can see that your file is showing macro sign as mentioned in below snapshot:

3. Now click on Developer tab, click on Insert and select the button as mentioned in below snapshot:

4. Now Draw a button between row 9 and 10 to get the Assign Macro window as mentioned below:

5. Now change the macro name btnCalculateEMI_Click as mentioned in below snapshot:

6. Now Click on New button to get following code window as mentioned below:

7. Now insert the following code as mentioned below:

Dim monthly_rate As Single, loan_amount As Double, number_of_periods As Single, emi As Double

monthly_rate = Range("D8").Value / Range("D7").Value

loan_amount = Range("D5").Value

number_of_periods = Range("D6").Value * Range("D7").Value

emi = WorksheetFunction.Pmt(monthly_rate, number_of_periods, -loan_amount)

Range("D11").Value = emi

8. Now final step is to click on Save button and close this VBA code window as mentioned in below snapshot:

Now you can see that button has been created as mentioned in below snapshot:

Step 3: Now the Next step is to Edit the Button Name by pressing right click on button.

Now you can see that EMI calculator has been ready as mentioned below:

Step 4: Now the final step is to test your EMI calculator by putting values into the calculator and press the button to calculate EMI as mentioned below:

We hope that you will like this lesson.

---Thank You---

Install Barcode Font and Generate Barcode In Excel

2020-07-21T15:03:00.004+05:00

In this Lesson, we will learn how to install barcode font and how to generate barcode in Excel:

If you are working as a Store Manager and you want to track inventory stock, sales and purchase using Excel, you need to generate barcode in excel to track your inventory. But in Excel there is no any default option to generate a barcode.

According to Microsoft, there is no plan to add barcode generating option in Excel yet.

But you can generate a barcode in Excel by installing a barcode font in Excel.

In this lesson, i would like to share with you a method to generate a barcode in Excel by using code 128 barcodes.

Code 128 Barcode?

To learn more about code 128 barcode, please click here to read the related article on wikipedia.

Steps to Add Barcode Font in Excel:

By following below mentioned steps, you can easily add Barcode font in Excel:

Step 1: First step is to download this free barcode font by clicking on below download button:

Step 2: After download this free Code 128 Barcode font, Now you need to install this font into your system by clicking on install button as mentioned below:

Note: You can use this free font for education purpose only.

Now you can see that free barcode font has successfully been install in your system.

Generate a Barcode in Excel:

Once you install barcode font in your system, you can easily create barcodes in Excel by following below mentioned steps:

Step 1: First Step is to create a spread sheet which contains item code, item description and barcode column as mentioned below:

Step 2: Now next step is to change the Format of Item codes into "Text" as mentioned below:

Step 3: Now Insert the below mentioned formula into cell "D3".

="*"&B3&"*"

Step 4: Now next step is to drag down this formula till cell "D11" as mentioned below:

Step 5: Now next step is to select the rang of Barcode cells and change the font theme into Code 128 as mentioned below:

Now you can see that, barcode has successfully been generated in Excel.

We hope that you will like this lesson.

---Thank You---

How to use IF Function, Nested IF Function in Excel

2020-03-23T13:52:00.000+05:00

In this lesson, we will learn how to use IF function in Microsoft Excel:

IF Function:

IF function is the most popular and most useful function in Microsoft Excel. By using this function, you can easily create logical comparison or evaluation between a value and expectation.

You can test two condition, by using IF function;

If the condition is met, it is considered to be True.
If the condition is not met, it is considered as False.

Before discussing IF function, we would like to discuss most important logical operators that will help you in comparison between a value and your expectation.

Logical Operators mentioned below:

Formula of IF Function is mentioned below:

=IF(logical_test, [value_if_true], [value_if_false])

To provide you better understanding, we would like to share an example of IF function as described below:

Example: Suppose that, you are working as a Inventory Manager of "Bee Mart" (departmental store), and you are responsible to maintain the minimum stock of 10 quantity of every items. To maintain the stock level, you need to create "Inventory Status Sheet".

By Following below mentioned steps, you can easily create inventory status sheet using IF function:

Step 1: First step is to create the inventory status sheet, which includes items description and on hand quantity etc. as mentioned below:

Step 2: Second step is to insert below mentioned IF formula in Cell "K5", to identify the items quantity which are less than 10. See below snapshot:

=IF(I5<10, "Re-Order", "Available")

Now you can see that, the on hand quantity is greater than 10 therefore the status is showing "Available" in Cell "K5" according to IF function.

Step 3: Now next step is to Select Cell "K5" and drag down this formula till Cell "K11" as mentioned in below snapshot:

Now you can see that, IF function working properly because wherever the on hand quantity is less than 10 so then the status showing "Re-Order" as mentioned in below snapshot:

Nested IF Function:

"Also called IF Function within another IF Function"

According to Microsoft, Nested IF Function meaning one function inside of another, this function allow you to test multiple criteria and increase the number of possible outcomes.

Example: As a teacher, you have conducted the exam and you need to create a "Grade Sheet" for result publication, as per the marks obtained by students.

By following below mentioned steps, you can easily create the grade sheet as mentioned below:

Step 1: First Step is to Create the List of Student with Obtained Marks as mentioned below:

Step 2: Now next step is to create grade condition on the basis of obtained marks as mentioned below:

Step 3: Now insert the below mentioned Nested IF formula to get the Students Grade on the basis of marks obtained by students:

=IF(D5>=1050,"A+",IF(D5>=950,"A",IF(D5>=850,"B",IF(D5>=450,"C",IF(D5>=0,"F")))))

Step 4: Now Next Step is to Select Cell "E5" and drag down this formula till Cell "E11" as mentioned in below snapshot:

Now you can see that, Nested IF function working properly as mentioned below:

Nested IF Formula Example:
⇛How to Create Tax Calculator

We hope that you like this lesson.

---Thank You---

How to Import Data from Excel into Power BI

2020-03-04T15:32:00.000+05:00

In this Lesson, we will learn how to load data from Excel into Power BI Desktop:

If you are using Power BI Desktop then you can easily create a dynamic report by importing Excel workbook data into Power BI. After importing data from Excel into Power BI, you can easily improve or refine reports with Power BI desktop application.

By following below mentioned steps, you can easily import Excel data into Power BI Desktop:

Step 1. To import data from Excel into Power BI, first Go to home and Click on Get Data Drop Down menu button in External data group.

Step 2. To import data from Excel workbook, Click on Excel in drop down menu as mentioned in below snapshot:

Step 3. After Clicking on Excel, a new window will pop up. Now Select your desired Excel file as mentioned below:

Step 4. Now Click on Open button to load data into Power BI desktop as mentioned below:

Step 5. Now in Navigator Screen, Select Sheet1 and then click on Load. This step will load your desired data from Excel Sheet into Power BI desktop as mentioned in below snapshot:

Now you can see that, your data has been loaded into the Power BI Desktop with all data columns as mentioned below:

Step 6. Now select your desired columns in Field tool bar and click on Table in Visualization bar as mentioned below:

Now you can see that, Sales person wise Sales Report has been created in Power BI as mentioned below:

You can also create a chart or refine your report by selecting different visualization from visualization bar as mentioned below:

We hope that you will like this lesson.

---Thank You---

How to Create Tax Calculator in Excel

2020-02-27T16:55:00.000+05:00

In this lesson, we will learn how to create "Tax Calculator" in Microsoft Excel:

In Microsoft Excel, you can easily create tax calculator to compute your monthly or yearly tax liability for salaried individuals by using incremental taxation system (tax rate increases with the increase in amount of income) or periodic tax slab and Excel IF functions.

Below is the new tax slab for salaried individuals that we will use in this example:

To create Tax calculator, you need to take below mentioned steps:

Step 1. First step is to prepare the format of tax calculator as mentioned below:

Step 2. Now insert below mentioned formula in cell "D21" to calculate taxable income.

=IF(D19<B6,0,IF(D19<B7,(D19-C5)*0.05,IF(D19<B8,(D19-C6)*0.1+30000,IF(D19<B9,(D19-C7)*0.15+90000,IF(D19<B10,(D19-C8)*0.175+195000,IF(D19<B11,(D19-C9)*0.2+370000,IF(D19<B12,(D19-C10)*0.225+670000,IF(D19<B13,(D19-C11)*0.25+1345000,IF(D19<B14,(D19-C12)*0.275+2345000,IF(D19<B15,(D19-C13)*0.3+7295000,IF(D19<B16,(D19-C14)*0.325+13295000,(D19-C15)*0.35+21420000)))))))))))

Step 3. To calculate Monthly Tax Liability, Please insert below mentioned formula in cell "D20" to divide Yearly Tax Liability into 12:

=+D21/12

Step 4. To calculate Yearly Taxable Income, Please insert below mentioned formula in cell "D19" to multiply Monthly Taxable Income by 12:

=+D18*12

Step 5. You can also insert comment for your assistance, as mentioned in below snapshot:

Now you can see that, Tax Calculator has been created as mentioned below:

Now insert your Monthly Taxable Income in Cell "D18" to calculate your Monthly and Yearly Tax Liability as mentioned below:

If your Yearly Income is non-taxable, then your calculated tax liability will be always equal to zero as mentioned in below snapshot:

We hope that you would like this lesson.

---Thank You---

How to Use CONVERT Function in Microsoft Excel

2020-02-13T12:15:00.001+05:00

In this lesson, you will learn how to use Microsoft Excel "Convert" function:

In Microsoft Excel, you can easily convert number in one unit of measure to another unit of measure by using Excel "Convert" function.

Example: Convert feet to meters, kilograms to pounds or lbs, gallons to liters etc.

Formula of Convert Function:

=CONVERT(number, from_unit, to_unit)

To provide you a understandable guidelines, we would like to create excel based unit converter as an example.

Before creating unit converter, we would like to share the list of all possible abbreviations which you can utilized to create measuring system in excel as mentioned below:

By following below mentioned steps, you can easily create unit converter in excel:

Step 1. First Create a Unit converter model in excel as mentioned in below snapshot:

Step 2. Now Next step is to create data validation list of all unit of measures in Cells "C9" and "E9" for unit converter by using below mentioned steps:

1. First create list of all unit of measures (already provided you above) as mentioned below:

2. Now Select Cells C9 and E9 simultaneously.

2. Now in Data Tools group, click on Data Validation.

3. Allow List in Validation criteria.

4. Now Select all unit of measures (in source of validation criteria as mentioned below:

5. Now Press OK.

Now you can see that data validation list has been created as mentioned below:

Step 3. Now next step is to Apply Vlookup function in cells C8 and E8 to find out abbreviation from list of all unit of measures.

To apply vlookup function, First Select Cell C8 and E8 and apply below mentioned vlookup formula:

=VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])

After applying Vlookup formula in both cells C8 and E8, below you can see that abbreviation of unit of measures has been filtered out from all unit of measures list.

Step 4. Now Insert Excel Convert formula in cell E7 as mentioned below:

Step 5. Now hide column 8.

You can see that, unit converter has been created and working properly.

hope you like this lesson.

---Thank You---

Introduction to Microsoft Power BI and How to Download and Install Power BI Desktop

2020-02-12T10:12:00.000+05:00

Introduction:

Microsoft Power BI is a great Business Intelligence and Data Visualization tools that converts raw data into interactive dashboards and Power BI reports. Power BI suite provides multiple software, connector, and services like, Power BI desktop, Power BI service based on Saas, and mobile Power BI apps available for different platforms.

Microsoft Power BI work area or desktop application is utilized to make BI reports, while Power BI administrations (Software as a Service - SaaS) is utilized to publish report, and Power BI portable mobile app is utilized to see the reports and dashboards.

Microsoft Power BI desktop is available in both 32-bit and 64-bit versions

Power BI Installation Minimum Requirements:

Following are the minimum requirement to run Power BI desktop:

Windows 7 / Windows Server 2008 R2, or later.
.Net 4.5.
Internet Explorer 10 or later.
RAM: At least 1 GB available, 1.5 GB or more recommended.
Display: At least 1440x900 or 1600x900 recommended. Lowest resolution e.g. 1024x768 or 1280x800 are not recommended.
Windows display setting: If your display settings are set to change the size of text, apps and other items to more than 100%, you may not be able to see certain dialogues that must be closed or responded to in order to proceed using Power BI.
Desktop: If you encounter this issue, check your display settings by going to Setting>System>Display in windows, and use the slider to return display settings to 100%.
CPU: 1 gigahertz (GHz) or faster x86- or x64-bit processor recommended.

Source: (Microsoft Power BI)

How to Download and Install Power BI:

You may download the file manually, if you run windows 7, 8 or 10.

To download the file manually, please follow below mentioned steps:

Step 1. First check the system type of your desktop e.g. 32-bit or 64-bit as mentioned in below snapshot:

Step 2. Now search for Download Power BI in your Internet Browser.

Step 3. Now click on download Microsoft Power BI.

Step 4. On the next screen, click on advance download options.

Step 5. Now click on Download again.

Step 6. At this step, you must know, if you have 32-bit or 64-bit operating system. we have 64-bit operating system so we are going to select 64-bit to download this file as mentioned in below snapshot:

Step 7. After downloading the setup file, Now double click on the downloaded file.

Step 8. Click on Next.

Step 9. Now again click on next button.

Step 10. Now tick on "I accept the terms in the license agreement" and click on Next button.

Step 11. Again click on Next button as mentioned in below snapshot:

Step 12. Now Click on Install button as mentioned below:

Now you can see that Power BI installation is in process.

Step 13. Now click on Finish button to complete Power BI desktop installation.

Now you can see that Microsoft Power BI has been installed in your desktop.

We hope that you like this lesson.

---Thank You---

How to Use COUNT, COUNTA, COUNTBLANK, COUNTIF Functions in Excel

2020-01-27T16:45:00.000+05:00

In this lesson; we are going to learn, how to use COUNT, COUNTA, COUNTBLANK and COUNTIF Functions in Microsoft Excel:

In Microsoft Excel, we have four different Count functions which we use to count the number of cells in certain ranges or range. These functions are totally different from Excel Sum function because Count functions are not able to add data. Count functions use to count only numeric data, and ignore text data or information.

Function # 1: Count:

By using this function, you can count the number of numeric entries from data by ignoring text, blanks and errors.

Formula of COUNT Function:

=COUNT(value1, [value2], ...)

To test the accurate use of Count function, we would like to share with you an example data sheet as mentioned below:

In the above mentioned data, we have text, error, blanks, dates with numeric entries. By using Count function you can easily count numeric entries by eliminating all text, errors, blanks & date formats etc by following below mentioned steps:

Step 1: First Add a "Count" column on data sheet.

Step 2: Now next step is to Apply Count Formula on Cell "E3" by horizontally selection of Rough data as mentioned below:

Step 3: Now drag down this formula to all cells as mentioned below:

Now you can see that Count Function is working properly because it is counting only numeric data as mentioned in below snapshot:

Function # 2: COUNTA:

By using this function, you can count the number of numeric entries or text entries from data by ignoring blank cells.

Formula of COUNTA function:

=COUNTA(value1, [value2], ...)

To test this function, we again quote this example as mentioned below:

To apply COUNTA functions, you will follow all Steps as mentioned on COUNT functions just by replacing COUNT formula to COUNTA formula as mentioned below:

In below mentioned snapshot, you can see that COUNTA function is not able to count blank cell:

Function # 3: COUNTBLANK:

By using this function, you can easily count the quantity of blank cells in bulk data range.

Formula of COUNTBLANK function:

=COUNTBLANK(range)

To test this function, we would like to share with you an example as mentioned below:

Example: Suppose that you are the owner of ESS company, and you want to conduct the balloting activity to all department employees regarding implementation of "stop smoking" policy.

Below mentioned snapshot is the result of balloting activity with Yes or No department votes:

By using COUNTBLANK function, you can easily count blank cells to identify that how many votes still not yet registered for "Stop smoking" policy implementation. as mentioned below:

Step 1: First Add "Vote not yet Registered" column as mentioned below:

Step 2: Now next step is to Insert the COUNTBLANK formula in Cell "C14" as mentioned below:

After applying this formula to all cell, you can see the result as mentioned below:

Function # 4: COUNTIF:

By using this function, you can easily count the number of items in selected range according to user defined criteria.

Formula of COUNTIF function:

=COUNTIF(range, criteria)

We would like to share with you an example as mentioned below:

Example: Suppose you are working as a HR manager and you wanted to find out some values or result with the help of certain criteria or condition.

Data mentioned below:

Now Apply COUNTIF functions to find out results;

Result # 1: To find out No of Manager, please apply below mentioned formula:

Result # 2: To find out Salaries Over $ 20,000, please see below snapshot:

Result # 3: To find out No of Employees in Marketing Department, please see below screenshot:

In below mentioned screenshot, you can see that COUNTIF function is working properly:

We hope that you like this lesson.

---Thank You---

How to Calculate Depreciation Using SLN, SYD and DB Functions in Excel

2020-01-22T11:56:00.000+05:00

In this lesson, we will learn how to calculate depreciation in Microsoft Excel:

In Microsoft Excel, we have numerous functions which will help you for calculating depreciation. Functions with methods as mentioned below:

SLN (Used for Straight Line Method).
SYD (Used for Sum of Year Digit Method).
DB (Used for Declining Balance Method).

For Example: ESS company purchases a machine for $200,000 with an estimated salvage value of $20,000 and a useful life of 5 years. Calculate depreciation using all methods.

Function # 1: SLN (Used for Straight Line Method).

In Microsoft Excel, you can easily calculate depreciation of asset by using straight line method.

According straight line method; how much the amount or value of an asset reduced during a specific period of time.

Formula of SLN Function:

=SLN(cost, salvage, life)

By following below mentioned steps, you can easily calculate depreciation by using SLN function:

Step 1: First Create a Depreciation computation sheet as mentioned below:

Step 2: Now Insert the SLN formula in Column "G3" as highlighted in below snapshot:

Step 3: Now next step is to Subtract the year-1 depreciation value from cost of machine to obtain current Asset value as mentioned below:

Step 4: Now apply both formulas into all empty cells as mentioned below:

Note: to calculate 2nd year Asset value, you will use this formula (Asset value - Depreciation value).

Now you can see that straight line method working properly by using this SLN function as mentioned below:

Function # 2: SYD (Sum of Year Digit Method).

In Microsoft Excel, you can easily calculate depreciation of asset by using sum of year digit method.

Sum of year digit method, adds useful life of each year together. e.g. A life of 5 years has a sum of 1+2+3+4+5 = 15.

year 5 is 33.33% of 15, year 4 is 26.66% of 15 etc.

on the basis of these percentages, you can allocate the total depreciation of an asset.

Formula of SYD Function:

=SYD(cost, salvage, life, period)

By following below mentioned steps, you can easily calculate depreciation by using SYD function:

Step 1: First Create the Depreciation computation sheet as mentioned below:

Step 2: Now Insert the SYD formula in Column "G3" as highlighted in below snapshot:

Step 3: Now next step is to subtract the Year-1 depreciation value from cost of machine to get the current Asset value as mentioned below:

Step 4: Now Apply both formulas to all empty cells as mentioned below:

Note: To calculate second year asset value, you will use this formula (Asset value - Depreciation value)

Now you can see that sum of year digit method working properly by using this SYD function as mentioned in below snapshot:

Function # 3: DB (Used for Declining Balance Method).

In Microsoft Excel, you can easily calculate depreciation by using declining balance method.

Declining balance method in Excel refer to as DB function which calculates depreciation on fixed percentage basis.

The most interesting feature of this function is the ability to calculate depreciation from when the item was originally purchased.

If the item was purchased in middle of the financial year, then this function calculates depreciation on pro-rata basis or the first year depreciation will be based on the remaining part of the year.

Formula of DB Function:

=DB(cost, salvage, life, period, first year month)

Note: First year month is an optional value, you will not use this, so the function will automatically assume 12 as the value.

By following below mentioned steps, you can easily calculate depreciation by using DB function:

Step 1: First Create the Depreciation computation sheet as mentioned below:

Step 2: Now Insert the DB formula in column "G3" as highlighted in below snapshot:

Step 3: Now next step is to subtract the Year-1 depreciation value from cost of machine to obtain the current Asset value as mentioned below:

Step 4: Now apply both formulas to all all empty cells as mentioned below:

Note: To calculate second year value asset value, you will use this formula (Asset value - Depreciation value).

Now you can see that, declining balance method working properly by using Excel DB function as mentioned below:

We hope that you like this lesson.

---Thank You---

How to Import Data from Picture into Excel by Using Microsoft Artificial Intelligence

2020-01-16T11:18:00.000+05:00

In this lesson, we will learn how to convert your pictorial data table into excel spreadsheet data by using latest Microsoft feature "Insert Data From Picture":

In Microsoft Excel, you are now able to convert your carbon copy data table into excel based soft copy because Microsoft has added a new feature in Excel App (Android & iOS), where users can easily convert raw data into useful information.

In this feature, Microsoft uses Artificial Intelligence (AI) to analyse and read the data from paper using mobile camera to stop need of manual data entry.

As an Example, We would like to share with you a printed copy of Employees information as mentioned below:

By Using below mentioned steps, you can easily import above mentioned printed data table into Excel Sheet using Android Mobile phone camera:

Step 1: First Open your Excel Mobile Application (Android or iOS).

Step 2: Now click on "Insert data from picture" button at the bottom of the screen as mentioned below:

Step 3: Now fix or focus your data into red square / Rectangular borders as mentioned below:

Step 4: Now tap on capture button.

Step 5: Now Press "Done" button after automatically cropping unnecessary picture as mentioned below:

Now you can see that, Excel App is converting Picture into Data:

Step 6: Now you can use editing tool to correct mistakes before importing the data. Or press "Ignore" button to import the data as mentioned below:

Step 7: After previewing Data, Click on "Insert" button.

Now below, you can see that your Data has successfully imported in Excel.

Now send this file to your desktop through email and make changes as you want easily. Desktop File mentioned below:

We hope that you like this lesson.

---Thank You---

How to Merge Two or More Than Two Cells in Excel By Using Concatenate Function

2020-01-15T11:31:00.000+05:00

In this lesson, we will learn how to merge or combine multiple cells in Excel using CONCATENATE function.

In Microsoft excel, if you want to combine your data from two or more cells into single cell. then you can use "CONCATENATE" function to combine text or number easily.

Note: up to 30 piece of of text can be joined by using below mentioned formula.

Formula of CONCATENATE:

=CONCATENATE(text1, [text2], [text3], [text4],....text30)

we would like to share an example, which will help you to understand that how to use this function in Excel. Example mentioned below:

Example: Suppose that, your customers are providing you mailing addresses, which split in different cells e.g. street addresses is in one cell, city or country names is in another cell or postal codes is in another cell etc.

Data mentioned below:

By using below mentioned steps, you can easily use "CONCATENATE" function to combine above mentioned data in one column:

Step 1: First step is to Add Full Mailing Address Column in Data Sheet as mentioned below:

Step 2: Now Insert the below mentioned CONCATENATE formula in Cell "G4":

=CONCATENATE(C4,", ", D4,", ", E4,", ", F4)

Step 3: Now Press Enter to get the result as mentioned below:

Step 4: Now Drage down this formula to last cell "G8" as mentioned below:

Now you can see that mailing addresses has been combined in one cell.

We hope that you like this lesson.

---Thank You---

How to use CHOOSE Function and RANK Function in Excel

2020-01-06T16:40:00.000+05:00

In this Lesson, you will learn two different functions that will help you to generate Rank sheet and Score card in proficient way as mention below:

CHOOSE
RANK

Function # 1: CHOOSE.

In Microsoft Excel, CHOOSE function will help you to pick a value from list or index provided by the user.

We would highlight an example to learn that how to use CHOOSE function in Excel as mentioned below.

Example: Create a chart to pick a Winner with the help of Microsoft Excel CHOOSE function.

Formula "CHOOSE" mentioned below:

=CHOOSE(UserValue, Item1, Item2, Item3 through to Itemnn)

By following below mentioned Steps, you can easily use CHOOSE function to create a chart to pick a Winner:

Step 1: First Create a Chart to pick a Winner as mentioned below:

Note: As per random selection by CEO, Serial # 6 is a Dubai Tour Winner.

Step 2: Now Insert the CHOOSE formula in Column G7 to identify the name of lucky winner as mentioned below.

Now you can see the result as mentioned below:

Function # 2: RANK.

In Microsoft Excel, we use RANK function to calculate the Position of a value or to compare numbers to other mentioned numbers in the same list of data.

This ranking can be calculated in two different orders as mentioned below:

Ascending Order (Low to High)
Descending Order (High to Low)

We would highlight an example to learn that how to use RANK function in Excel as mentioned below:

Example: As a teacher, identify the Ranks of students on the basis of Secured marks.

Formula "RANK" mentioned below:

=RANK(number, reference, [order])

By following below mentioned steps, you can easily use RANK function to identify the Individuals Students Ranks on the basis of Secured marks:

Step 1: First Create Student Rank Sheet as mentioned below:

Step 2: Now Insert the RANK formula in Column E4 to identify the Secured Position of Student. (We will use Descending Order in this situation).

Step 3: Now Press Enter to Get the Result as mentioned below:

---Thank You---

How to Use AND Function and CEILING Function in Excel

2020-01-06T13:05:00.000+05:00

In this Lesson, you will learn 2 different formulas that will help you to perform job tasks efficiently and effectively.

Formulas mentioned below:

AND
CEILING

Formula # 1: AND.

In Microsoft Excel, you can easily perform two to more condition testings at the same time with the help of this AND formula. AND outcome will always comes either True or False.

Example: As a teacher, you have taken the exam from students, which comprises into two part (1.1 and 1.2) and you wanted to announce the result of students with the help of AND Function.

Exam answers mentioned below:

Formula of "AND" function is:

=AND(Test1, Test2)

Note: there can be up to 30 possible tests.

By using below mentioned steps, you can get the correct result of students with the help of AND formula:

Step 1: First you need to Add the Column "Result" in this sheet as mentioned below:

Step 2: Now Insert the formula in column F6 as mentioned below:

=AND(D6=100, E6=115)

Step 3: Now select this formula and drag it down to all empty cell.

Now you can see the result as mentioned below:

Formula # 2: CEILING.

In Microsoft Excel, you can easily round up your desired number up to the nearest specified multiple with the help of CEILING function.

Example: Suppose that you are working as a packing manager at petro chemical company and you need to generate requisition against total number of bags required for packing of plastic chips.

you have calculated the total number of bags. but you need to rounding up your answer to remove decimal number from total number of bags as mentioned below:

CEILING formula mentioned below:

=CEILING(value, multiple)

By using below mentioned steps can easily calculated total number of bags required for plastic chips packing:

Step 1: First add a new column to calculate actual bags required.

Step 2: Now insert CEILING formula in column F3 as mentioned below.

=CELING(E3, 1)

Step 3: Now drag down this formula to cell F5 as mentioned below:

Now you can see the result as mentioned below:

---Thank You---

Time Calculation in Excel (Time Difference, Time Addition and Subtraction)

2019-12-21T11:27:00.000+05:00

In this Lesson, you will learn how to calculate times in Excel in different techniques as mentioned below:

How to Type Times in Excel.
How to Calculate Total Hours Worked or Difference Between Two Time (Time-In & Time-Out) in Excel.
Adding and Subtracting Time in Excel.

Technique # 1: How to Type Times in Excel.

In Microsoft Excel, If you want to enter time in worksheet then you must keep in mind that, you will enter Colon (:) between hours and minutes instead of full-stop (.)

In Excel, you have two option, one is 24 hour system and another is am/pm system.

♥ To use the am/pm system, always require to Enter am or pm after the time and don't forget to leave space between the number and the text as mentioned below:

♥ If you would like to change your time into 24 hour system. Then please follow below mentioned steps:

Step 1: First Select your time as mentioned below.

Step 2: Now in Number group, Click on Number format button to open the format Cells window.

Step 3: In Format Cells window, First Click on Time and Select the 13:30 format and Press OK to save the setting as mentioned below:

Now you can see the result as mentioned below:

Technique # 2: Calculate Difference Between Two Time (Time-In & Time-Out) in Excel.

In Microsoft Excel, you can easily calculate the difference between two time or elapsed time.

For example: Find Actual Hours worked with the help of Time-In and Time-Out.

If you are working as Payroll Manager and you wanted to analyse the Actual Hours Worked of your employees on the basis of Time-In and Time-Out data, which you have collected from attendance machine, then you can use this technique.

By using below mentioned steps, you can easily calculate difference between two time:

Step 1: First create Employees Detailed Time Sheet which contains Time-In and Time-out as mentioned below.

Step 2: Now Select the Cell F6 and Type below mentioned formula.

=INT(E6-D6) & " days, " & HOUR(E6-D6) & " hours, " & MINUTE(E6-D6) & " minutes and " & SECOND(E6-D6) & " seconds"

Now you can see the result as mentioned below:

Step 3: Now Copy this Cell F6 and paste this copied cell to all empty cell as mentioned below:

You can also use this alternate way to calculate difference with the help of subtraction (E6-D6) and below mentioned custom format:

d "days," h "hours," m "minutes and" s "seconds"

To implement this above mentioned format, please follow below mentioned steps:

Step 1: First Select F6 Cell and type =(E6-D6) Formula for subtraction as mentioned below.

Step 2: Now Click on Number Format Button to Open Format Cells Window.

Step 3: Now in Format Cell window, Select category Custom, Paste the above Format in Type section and Click OK to save the setting.

Now you can see the result as mentioned below:

Technique # 3: Adding and Subtracting Time in Excel.

In Microsoft Excel, you can easily add or subtract hour in so many ways some of these are mentioned below:

Add or Subtract Time Under 24 hours.
Add or Subtract Time Under or Over 24 hours.
Add or Subtract Time Under 60 minutes.
Add or Subtract Time over 60 minutes.

1: Add or Subtract Time Under 24 hours.

Example # 1: If your employee has gone to his home after work done of two hours and you don't know the Time-Out of your employee, because your employee had forgot to Card-Out at the time of leaving his office.

For Calculation of Time-Out under 24 hours, you can use below mentioned formula:

=First Time (Time-In) + TIME(N hours, 0, 0)

Now you can use this formula as mentioned below:

Example # 2: If you don't know the Time-In of your employee, but you have the Time-Out of your employee. So you need to subtract Two hours to get Time-In as per the above mentioned example.

For Subtraction, you can use this below mentioned formula:

=End Time (Time Out) - TIME(N hours, 0, 0)

Now you can use this formula as mentioned below:

2: Add or Subtract Time Under or Over 24 hours.

In Microsoft Excel, if you want to Add or Subtract more than 24 hours, then you can use these formulas as mentioned below:

Formula For Addition:

=Time-In (Start Time)+(N hours / 24)

Formula For Subtraction:

=Time-Out (End Time)-(N hours / 24)

For instance, we will Add or Subtract 30 hours with the help of above mentioned formula. Result mentioned below:

Addition:

Subtraction:

3: Add or Subtract Time Under 60 minutes.

In Microsoft Excel, If you want to Add or Subtract time under 60 minutes, then you can use these formulas as mentioned below:

Formula for Addition:

=Time-In (Start Time)+TIME(0, N minutes, 0)

Formula for Subtraction:

=Time-Out (End Time)-TIME(0, N minutes, 0)

For Instance, we will Add or Subtract 30 minutes with the help of above mentioned formula. Result mentioned below:

Addition:

Subtraction:

4: Add or Subtract Time over 60 minutes.

In Microsoft Excel, If you want to Add or Subtract time over 60 minutes, then you can use these formulas as mentioned below:

Formula for Addition:

=Time-In (Start Time)+(N minutes / 1440)

Formula for Subtraction:

=Time-Out (End Time)-(N minutes / 1440)

For Instance, we will Add or Subtract 120 minutes (2 hours) with the help of above mentioned formula. Result mentioned below:

Addition:

Subtraction:

---Thank You---

How to Calculate Percentages in Microsoft Excel

2019-12-10T14:47:00.001+05:00

In this Lesson, we will discuss how to calculate Percentages in Excel

Before starting this lesson, first we would like to define what is percentage?

What is Percentage?

The term "percent" is derived from the Latin word "percentum", which means "by the hundred". as we have learned in mathematics subject, a percentage is a fraction of 100 that is calculated by dividing the numerator by the denominator and multiplying the result by 100. Example mentioned below:

(Obtained Marks / Total Marks) X 100 = Total Percentage

How to Calculate Percentages in Excel?

In Microsoft Excel, we have no any built-in functions that will help you calculate percentages, therefore we will use Mathematical skills to calculate percentages in Excel.

In this lesson, we will discuss Two examples as mentioned below:

How to find a Percentage Value?
How to find Original Amount from Total Amount?

Example # 1: How to Find a Percentage Value?

If an employer / owner has announced employees salary increment according to the grade and designation. Then the Payroll department is responsible to increase staff salary according to the employees grades and designations. Therefore payroll department need to calculate the increment on the basis of old salary and increasing percentage according to the grade & designations as mentioned below:

By following below mentioned steps, you can easily calculate the amount increase as per the increment percentage:

Step 1: To Calculate Increment Amount, First Create a Table as mentioned below:

Step 2: Now Insert the below mentioned Formula to Calculate Increment amount in Column F13:

=E13*LOOKUP(D13,$C$4:$C$9,$D$4:$D$9)

Now you can see the result as mentioned below:

Step 3: Now Copy Pasting this formula to all Empty columns as mentioned below:

Example # 2: How to find Original Amount from Total Amount?

If you are creating an expense voucher, so you must required to show sales tax portion separately. Unluckily, your receipts showing only Total Amount including 5% Value Added Tax (VAT). Snapshot attached below:

By following below mentioned steps, you can easily split this Total Amount into Original Amount or Sales Tax:

Step 1: First Create a Sheet which contains, Original Amount, VAT Amount and Total Amount as mentioned below:

Step 2: Now Insert the below mentioned formula in Column F5, to calculate original amount as mentioned below:

=H5/(100%+$E$2)

Step 3: For calculation of Value Added Tax Amount, Please use below mentioned formula or calculate the 5% of original amount as mentioned below:

=+F5*$E$2

Step 4: Now Copy Pasting these two formulas to all Empty Columns as mentioned below:

---Thank You---

Age Calculation, Auto Sum, File Name, Instant Charts, Show All Formula

2019-12-05T13:13:00.001+05:00

In this Lesson, we will discuss five most common and most simple formulas that will help you to;

To calculate person age
To calculate total amount automatically
To find out file name easily
To Create chart on one click
To Get formula easily

Formula # 1: How to Calculate Person Age

In Microsoft Excel, you can easily calculate the age of a person by using their date of birth and today date.

For example: If your date of birth is 18th April, 1999 and you would like to know your current age, then you can use this formula to calculate your age easily.

By following below mentioned steps, can easily calculate person age:

Step 1: First steps to Create Age calculator format as mentioned below.

Step 2: Now Type the Date of Birth as mentioned in example.

Step 3: Now Select Total Year Lived Cell and Insert the DATEDIF() Formula as mentioned below:

=DATEDIF(C4,TODAY(),"y")

For Total Month Lived; =DATEDIF(C4,TODAY(),"ym")

For Total Month Lived; =DATEDIF(C4,TODAY(),"md")

Now you can see the result as mentioned below:

If you want your age like words format e.g. "My Age is 20 Years, 7 Months and 22 Days" then you will use below mentioned formula:

="My Age is "&DATEDIF(C4,TODAY(),"y")&" Years, "&DATEDIF(C4,TODAY(), "ym")&" Months and "&DATEDIF(C4,TODAY(), "md")&" Days"

Now copy and paste above mentioned formula to get the result as mentioned below:

Formula # 2: How to Calculate Total Amount Automatically

In Microsoft Excel, we have great option to calculate total amount automatically instead of using AutoSum button from the toolbar.

For example: If you have sales summary report and you want to calculated the Gross Sales or Total Sales then you can use this shortcut, instead of using AutoSum button from the toolbar.

Sample sales summary report mentioned below:

By using below mentioned step you can use this shortkey to calculated Total Sales and Gross Total easily:

Step 1: Select all Gross Total or City Wise Total Sales, Blank Cells as mentioned below:

Step 2: Now Press (Alt + =) to get the result as mentioned below:

Formula # 3: How to Find Out File Name easily

In Microsoft Excel, You can easily find out the File Name or Path by using below mentioned Formula:

=CELL("Filename")

By using below mentioned steps can easily find out the File Name or Path of your Workbook:

Step 1: First Select the Cell, where you want your File name or Path.

Step 2: Now Insert the cell formula =CELL("Filename") as mentioned above.

Now you can see the result as mentioned below:

Formula # 4: How to Histogram Chart on One Click

In Microsoft Excel, you can easily create a Histogram Chart by using one Function Key instead of using Charts button on toolbar as mentioned below:

By using below mentioned steps, you can easily create the chart through one Function Key:

Step 1: First Select your desired data for histogram chart as mentioned below.

Step 2: Now Press function key "F11", for creating Histogram Chart as mentioned below:

Formula # 5: How to Get Formula Easily

In Microsoft Excel, if you wanted to view the formula on the spreadsheet, so you can use small Shortkey to see the formula.

For example: If you have want to see the formulas of City Wise Total Sales or Grand Total formula then you can use this Shortkey (Ctrl + `).

By Using below mentioned steps, you can easily see the formula:

Step 1: First Open your Worksheet.

Step 2: Now Press (Ctrl + `) to see Gross Total and City Wise Total Sales Formulas as mentioned below:

---Thank You---

Five Most Important Tips to Optimize Page Print in Excel

2019-11-29T10:51:00.000+05:00

In Microsoft Excel, we have not found any default printing setting or tool to print huge data in presentable formats. Therefore we have found some tips to better optimize page print in Excel which mentioned below:

Tips:

01: How to Print Gridlines.
02: How to Set a Print Area.
03: How to Center a Page Horizontally and Vertically.
04: How to Scale a Page.
05: How to Repeat Top Rows on Every Print Page.

Tip # 1: How to Print Gridlines in Excel

In Microsoft Excel, when we go to the Print Preview window by pressing Ctrl+P, you see that it is very difficult to tell that which cell belong to which row or column etc. Because in Microsoft Excel we do not have any default Girdline settings. as mentioned in below Preview:

In Microsoft Excel, we have a good command to organize data separately through configuration of Gridline.

By using below mentioned steps, you can easily add Gridlines in Excel:

Step 1: Open your Excel Sheet and Press Ctrl + P.

Step 2: Now Click on Page Setup.

Step 3: In Page Setup window, Click on Sheet tab.

Step 4: In Print section, Tick on Gridlines and Press OK to save the configuration.

Now you can see the result as mentioned in below snapshot.

Tip # 2: How to Set a Print Area

If you are using Microsoft Excel spread sheet for maintaining your data bank. But you don't want to print all your data, so in Microsoft Excel you can easily Set the Print limit that you want to print.

By following below mentioned steps, you can easily Set a Print Area:

Step 1: Select your Desired print area on Excel Spread sheet.

Step 2: Now click on Page layout tab.

Step 3: In Page Setup group, Click on Print Area and Select Set Print Area.

Now you can see that Print Area has been set as mentioned in below snapshot:

Tip # 3: How to Center a Page Horizontally and Vertically

In Microsoft Excel, you can set easily center a page horizontally and vertically by using the Page Setup option.

By following below mentioned set can easily center a page:

Step 1: Open your Microsoft Excel spreadsheet and Click on Page Layout.

Step 2: In Page Setup group, Click on Margin.

Step 3: In Center on page section, Tick on Horizontally and Vertically and Press OK to Close Page Setup window.

for verification Press Ctrl + P to see the result as mentioned below snapshot:

Tip # 4: How to Scale a Page

If you have a so many columns in Excel spread sheet and you want to print all your columns in one page so don't worry. You can easily reduce the size of your spreadsheet and fit your data into one page thru Scale to Fit options.

By Following below mentioned steps you can easily apply Scale to Fit Option:

Step 1: Open your Work Sheet and Press Ctrl + P to Open Print Preview window and Click on Page Setup to open Page Setup window.

Step 2: In Page Setup window, Select Fit to in Scaling section and Click OK to save the setting.

Now you can see the result as mentioned in below snapshot.

Tip # 5: How to Repeat Top Rows on Every Print Page

If you are working with Excel spread sheet and your spreadsheet comprises into more than one page then you could face problem to matched data to specific column title.

Don't worry, In Microsoft Excel we have an option that will repeat this tile automatically.

By following below mentioned steps can easily repeat tile on every print page:

Step 1: Open your Excel Sheet and Press Ctrl + P.

Now we see that on Page # 2, we don't have any page tile as mentioned in below snapshot:

Step 2: Now Click on Page Setup.

Step 3: In Page Setup window. Click on Sheet tab.

Step 4: In Print titles section, Click inside the Rows to repeat at top and select the range of rows that you want to repeat on every printed page and Click OK to save the settings.

Now you can see the result as mentioned in below snapshot of Page # 2:

---Thank You---

Consolidate Data From Multiple Worksheet to Single Worksheet in Excel

2019-11-23T12:16:00.002+05:00

In Microsoft Excel, we can easily consolidate multiple worksheet by using Excel Consolidate tool. lets discuss an example to learn how to use consolidate tool in excel.

For example, you have month wise sales reports of your company and your company owner required combine sales report. Therefore we required Sales summary report as mentioned below:

By using below mentioned steps, you can easily consolidate the data into single worksheet:

Step 1: First Arrange the data into same format and Create Sales Summary Report without amount as mentioned below:

Step 2: Now Select all Empty Cells in Sales Summary report as mentioned below:

Step 3: Now Click on Data. In Data Tool group, Click on Consolidate to open Consolidate Window.

Step 4: In Consolidate window, select following data as mentioned below:

1: In Function drop down menu, select your desired summary functions. In this example, we select Sum as mentioned below:

2: Now Click on Reference box and Select the range of your first month sales report and Press Add to add range in All reference as mentioned below:

3: Now Repeat this step for all ranges that you want to consolidate in Sales Summary Report and Select Create links to source data and Press OK to Consolidate Sales Reports.

Now you can see the result as mentioned below:

To check Joseph month wise sales, Click on (+) sign to expand joseph sales data as mentioned below:

To check data source, Click on separate cell as mentioned below:

---Thank You---

How to Maintain Data Log Sheet Using Microsoft Excel Form

2019-11-19T17:37:00.001+05:00

In Microsoft Excel, you can easily maintain your Data log sheet by using Microsoft Excel Form. This is very helpful to maintain any type of log sheet very easily.

For Example; if you are maintaining Customer's Post Dated Cheques (PDC's) Log in Excel , so you can use this form to manage your log sheet. sample log sheet mentioned below:

Before giving instruction for form creation, i would like to define What is Customer's Post Dated Cheques? and why we maintain PDC's Log?

"Post Dated Cheques are issued by customer's against credit sales to company and company's treasury department is responsible to bank these PDC's on it's due date. Therefore we generate PDC's Log to track the record of Customer's PDC's".

By using below mentioned steps, you can easily create Microsoft Excel Form:

Step 1: First Step to Add data entry form into Quick Access Toolbar by following below steps:

1) Right Click on any where on Quick Access Toolbar and Select Customize Quick Access Toolbar as mentioned below:

2) In Excel Options window dialog box, Select All Commands on Choose command from drop down list as mentioned below:

3) Now Scroll down the list of All Commands and Select Form in List. Click on Add Button to Add Form in Default buttons.

4) Now Click OK to Add Form Button in Quick Access Toolbar.

Now you can see the result as mentioned below:

Step 2: To Enter new record in PDC's Management log sheet thru Excel Form, First Select Columns Descriptions as mentioned below:

Step 3: Now Click on Form in Quick Access Toolbar.

Step 4: In Excel Form, Click on New.

Step 5: Now Enter customer PDC data in the form and Press Enter to enter the record into log sheet as mentioned below:

Now you see that your data has successfully entered in Customer's PDC's Management Log Sheet.

---Thank You---

How to Create Error Message and Attention Message Alert in Excel By Using Data Validation

2019-11-19T01:17:00.001+05:00

In Microsoft Excel, you can easily create Error Message and Attention Message Alert with the help of Data validation tool. when you would enter data to those cells, where data validation rules has been applied, then Excel will show Attention Alert before entering data into cell and also show Error Message if you will enter incorrect data.

This Attention and Error Alert Message will help you to prevent incorrect data recording. With the help of this great Data Validation rule you can give helpful instruction to your subordinates employees regarding data requirements in Excel sheet.

Suppose that you are the HR Manager of a company and as per the instruction of Owner and Chief Executive, you need to circulate an excel based sheet to all department regarding collection of information about expected salary increment from all department.

But Owner have set the designation wise Increment Limit as mentioned below:

Now as per instruction, you need to circulate the Expected salary Increment Analysis sheet after applying rules of Increment Limit in Desired Increment cells.

By following below mentioned steps can easily create a customized excel based sheet:

Step 1: First Create Excel based Expected Increment Analysis Sheet as mentioned below:

Step 2: Now Select your Desired Increment Cells in which you want Attention Message Alert when you Click on that cell. In this case we select all Manager Designation in Desired Increment cells as mentioned below:

Step 3: Now Click on Data tab. In Data Tools group, Click on Data Validation.

Step 4: Now on the Setting tab in Data Validation window;

Select "Whole Number" in Allow drop down list.
Select "between" in Data drop down list.
Enter 10000 in Minimum and 20000 in Maximum as per Increment Limit of Managers.

Step 5: To Create Attention Message;

Click on Input Message tab.
Tick on "Show input message when cell is selected".
Now type "Attention Message" in Title box.
Write Input Message as "Please Enter Increment Value Between 10,000 to 20,000 for Managers".

Step 6: To Create Error Message Alert;

Click on Error Alert tab.
Tick on "Show error alert after invalid data is entered.
Select "Stop" in Style drop down list to prevent incorrect data entry.
Now type "Error Alert" in Title box.
Write Error message as "I think you cannot read the message so please try again later with correct value".
Press OK to save the Data Validation rules.

Now you can see the Attention Message, if you select any Manger Cell in Desired Increment Column as mentioned below:

To check Error Alert Message, Enter more than 20,000 or less than 10,000 value in Manager Cell. For example we Enter 20,001 in Manager Cell and the result "Error Alert" as mentioned in below snapshot:

---Thank You---

Quick Salary Calculation By Using Goal Seek in Microsoft Excel

2019-11-17T19:07:00.000+05:00

If you are a Payroll Manager and you want to pay $50,000 Net Salary to your employee and you just have only Percentages of House Rent Allowance, Dearness Allowance, Medical Allowance, Vehicle Allowance, Provident Fund Contribution rate, Withholding Tax rate deduction etc but you don't have Basic Pay and Gross Pay. So Don't worry this is not a rocket science if you are using Microsoft Excel.

Because in Microsoft Excel, we can quickly calculate your desired net salary by using Goal Seek Analysis Tool.

What is Goal Seek?

In Microsoft Excel, Goal Seek is a great analysis tool that will help to find out the value for a selected cell with the help of What-If Analysis by using another cells formula.

By using following steps, you can easily find Basic Salary and Gross Salary with the help of Net Salary:

Step 1: First Create a Sample Salary Sheet in Excel as mentioned below:

Step 2: To automatically calculate employee Basic Pay and Gross Pay with the help of Net Pay, First Remove your sample Basic Pay from Employee Salary Slip as mentioned below:

As we know that Net Pay and Gross Pay always depends on Basic Pay so when we will remove Basic Pay then all values will become zero. For assistance please see below snapshot:

Step 3: Now Click on the Net Pay cell in which you want $50,000 Net Pay Amount.

Step 4: Now Click on Data Tab, In Data Tools group, Click on What-If Analysis and Select Goal Seek in drop down menu.

Step 5: Now in Goal Seek dialog box, Enter you desired Net Pay $50,000 in to value text box.

Step 6: Now Select Basic Pay Amount cell in By changing Cell and Press OK. Because Net Pay and Gross Pay always depend on Basic Pay. So we will inform to Excel, the basis of calculations as mentioned below:

Step 7: Now again Press OK to See your desired Net Pay with auto calculated Basic Pay and Gross Pay as mentioned below:

---Thank You---

How to Mail Merge By Using Microsoft Excel

2019-11-15T11:37:00.000+05:00

In Microsoft Excel, you can easily save your time and efforts for sending large amount of mails to your employees, family member or others by using a Mail Merge, which will help you to create custom letter easily in Microsoft Word just by merging your Excel information data bank.

By following below mentioned steps, you can easily use the Mail Merge in Excel:

Step 1: First Create or compose a Letter on Microsoft Word, that you want to sent to your all Employees as mentioned below:

You can see on above letter that, Your required field is in brackets just for your information. Before using this letter please remove brackets with value as mentioned in below snapshot.

Step 2: Now Next Steps is to Create Excel based data bank which contains information that you want to include in customized letter such as Name, Mailing Addresses, Designation etc as mentioned below:

Step 3: Now Open your Letter in Microsoft Word. Click on Mailings tab. In Start Mail Merge group, Click on Letter.

Step 4: Now Click on Select Recipients on Start Mail Merge group and then Select Use an Existing List to open Select Data Source window as mentioned below.

Step 5: In Select Data Source window, Select your Excel based data sheet and click Open.

Step 6: Now in Select Table window, Select your Excel based data sheet and Tick on First row of data contains column headers and Press OK.

To check your created Recipient List, Click on Edit Recipient List in Start Mail Merge group as mentioned below:

Step 7: To insert the Recipients data in Invitation letter, First Click on your desired place and then Click on Insert Merge Field in Write and Insert Fields group and Select your desired field in drop down menu as mentioned below:

Now you can see that, Merge fields has been inserted in Invitation letter as mentioned below:

Step 8: To make sure that your required Recipients data are correctly insert in this letter. Click on Preview Results button on Preview Result group.

Now you can see that your merge recipient list working properly by clicking left and right arrow.

Step 9: Now Click on Finished & Merge on Finish group. In drop down menu, Select Print Documents to Open Merge to Print window.

Step 10: In Merge to Print window, Select All and press OK.

Step 11: Now close your Print window if you don't want to print and Ctrl+S to save your Mail Merge file.

---Thank You---

How to Analyze Data in Excel By Using Sparklines

2019-11-11T10:52:00.000+05:00

In Microsoft Excel, we can easily analyze data by using Sparklines. When you select the data, Excel popup a Quick Analysis button on right side of lower corner of data range.

When you will click on the Quick Analysis Button or Press Ctrl+Q, you can see the options like Formatting, Charts, Totals, Tables, and Sparklines etc as mentioned below:

When you select the range of data, then these options will be available as per your data type. E.g. If your data contains only text value, then Sparklines option will freeze or not available as mentioned below:

Before Analyzing the data using Sparklines, we would like to give you a short introduction about Sparkline.

"Sparklines are a small graphical chart inside the single cell of worksheet which is useful to quickly analyze your data"

By Using Following Steps, you can easily create single cell graphical chart by using Sparklines:

Step 1: Select you desired blank cells that you want to show small graphical Sparklines.

Step 2: Now Click on the Insert tab, in the Sparklines group, Click on your desired chart ( Line, Column or a Win/Loss). In this example, we select line graph as mentioned below:

Now you can see that Create Sparklines dialog box will be open as mentioned below:

Step 3: Click on Data Range and Select your desired data range that you want to show in the Sparkline. In this example we have selected Sales Revenue range (D5:G9) as mentioned below:

Step 4: Now Click OK to Create Sparklines. Now you can see the result as mentioned below:

Step 5: To Highlight individual values;

Select all your Created Sparklines,
On the Design tab, in the Show group,
Tick every Points to highlight individual values in Sparklines.

Now you can see the result as mentioned below:

---Thank You---

How to Insert Watermark in Excel

2019-11-09T18:02:00.002+05:00

In Microsoft Excel, we don't have any buit-in featured to insert Watermark in sheet. Therefore we can create a mimic of watermark by inserting a picture with the help of Header and Footer Elements group.

A Watermark is a background image that appears on a print page. like your company logo or word, e.g. Draft, Confidential, Copyright, Sample, Original copy etc.

By using below mentioned steps, you can easily insert Watermarks in Microsoft Excel:

Step 1: Save your picture into your hard drive that you want to use as a water mark.

Step 2: Now Click on View. In Workbook Views group, Click on Page Layout to enter Page Layout view.

Now you can see the Page layout view as mentioned below:

Step 3: Click on Center section of the header as mentioned below.

Step 4: Now Click on Picture in Header Footer Element Group to open Insert Picture dialog box.

Step 5: In Insert Picture dialog box, Click on Browse.

Step 6: Now Select your desired Image that you want to insert as a Watermark in Excel.

Step 7: Now Click outside the header to see your selected image on spreadsheet as a Watermark.

Step 8: Now click on the center section of header again and press Enter few times as and when required before the image &[Picture] code, as mentioned below:

Now you can see the result as mentioned below:

Step 9: To adjust the brightness of Watermark, click on the Center section of header again. In Header and Footer Elements group, click on Format Picture to open the Format Picture dialog box.

Step 10: In Format Picture dialog box, Click on Picture and in Color drop down menu, Select Washout. Press OK to close the Format Picture dialog box.

Now your desired Watermark has been created as mentioned below:

---Thank You---