Percentage formula present in Excel

In Mathematics calculation, every concept is required to calculate the accurate result. This tutorial explains the various methods and formulas to calculate Percentages in Excel.

What is the Percentage?

The Percentage is expressed as fractions with the denominator as 100. The percentage symbol (%) is placed after the number. To display the number in percent. For example, if a mark scored by the student in a particular subject is 50/100, the Percentage he scored is 50%.

In words, describe it as "Per cent-um," which means "by the hundred." It is the process of indicating a part per hundred of any quantity. One percent denotes a hundred parts, which means a hundred percent indicates the whole part of the given data.

Percentage in Excel

In Excel, the concept of Percentage is used in various topics, such as

Profit and Loss
Discounts
Tax calculation
Discount
Rate of interest
Statistics
Quantities and discounts

It is used in many concepts to evaluate the required result.

Formula to calculate Percentage

The Percentage is calculated by dividing the numerator by the denominator, and obtained result is multiplied by 100. In Excel, the Percentage formula used is,

Percentage= (Part/Total)*100.

Each time the user needs not to multiply a resulting fraction by 100, as Excel percentage formatting automatically does this job when applied to the cell.

Method 1

Example 1: Calculate the Percentage for the given data.

A company's Product name, manufacturing product, and defective product details are given. Calculate the Percentage for the given data.

To calculate the Percentage in Excel, the steps to be followed are:

Step 1: Enter the data such as Product Name, Manufactured, and Defect Product in the worksheet in the cell range A1:D4.

Step 2: To calculate the Percentage of working items, enter the formula in the cell D1 as =B2/C2.

Step 3: Press Enter. The result is displayed in cell D2. Use the fill handle to display the result for the remaining cells.

Step 4: Select the cell range D2:D4 to apply the Excel percentage formula. Choose Home>Numbers Group>Percentage from the drop-down list.

Step 5: It displays the Percentage result for the selected values.

The Increase Decimal or Decrease Decimal point is used to adjust the decimal point, which converts the decimal value to the actual value.

Calculate the Percentage using Absolute Reference.

Sometimes in data, the whole part is given at the end of the table. The whole part is entered as an absolute reference in the formula. To find the percentage value

In this example, the data contains the Name, Total, and the whole part mentioned at the end of the column, as shown in the tabular column.

To calculate the Percentage, the value present in cell B2 needs to divide by the whole part in B5. Hence enter the formula in the cell C2 as =B2/$B$5. Press enter to display the result. The fill handle is used to display the result for the remaining cell.

The absolute reference is used because the individual value must be divided by the absolute value present in the respective column every time. Hence the $ symbol is used to keep the value static, called an absolute reference.

Select the cell range C2:C4 to apply the Excel percentage formula. Choose Home>Numbers Group>Percentage from the drop-down list.

The Increase Decimal or Decrease Decimal point is used to adjust the decimal point, which converts the decimal value to the actual value.

Calculating the Percentage Value using Sum if Function

Suppose the given data contains multiple rows of data. The Sum of a function is used to calculate the result using several criteria. The Sum if function adds the required data and divides it with the specified number to generate the result in Percentage.

Example 1: Enter the data such as Name of the Product, Branch, Salesperson, and Sales in $ in the cell range, namely A1:D9

In the above example, to calculate the sales done in North Side, the Sumif function is used to calculate the data.

Enter the formula in the cell G4 as =SUMIF (B2:B9, G3, D2:D9)/$D$D10. Press Enter. The result will be displayed in cell G4. The formula consists of three arguments: range, criteria, and sum_range. B2:B9 is called cell range, condition or criteria are "North" branch and calculating the sum range present in North called sum_range.

To find the sales percentage in the South, the formula entered is as follows:

=SUMIF (B2:B9, G6, D2:D9)/$D$D10.

The Percentage of sales in North and South is calculated.

Increase and Decrease In Percentage

To compare the two values, the percentage value will increase and decrease. For example, the 'x' value changes based on time; hence the difference between the old and new values should be calculated. The formula to calculate the Increase in Percentage is as follows:

Percentage Increase =(new_value-old_value)/old_value

By using the formula, the percentage Increase value is calculated. If the result is positive, the new value is greater than the old value. If the obtained result is negative, the new value is lesser than the old value.

Percentage Increase or Decrease in Two Columns

The percentage Increase or Decrease in Two Columns is calculated in this formula. The percentage difference between the values in the two columns is calculated using the required formula.

Example: The expense for specific items for two months is given. Calculate the Percentage Increase or Decrease in Two Data.

The steps to calculate the percentage difference between two numbers are as follows:

Step 1: Enter the data such as Name, Previous Month, and Next Month in the worksheet A1:D5

Step 2: To calculate the percentage difference between two numbers, enter the formula in the cell D2 as =(C2-B2)/B2.

Step 3: Press Enter. The percentage difference between the two numbers will be displayed in cell D2. Use the Fill handle to display the result for the remaining cells.

Step 4: The formula returns the result in decimal points. To convert the decimal value to Percentage, choose Home->Styles Group->Percent and drag the formula to cell D5 to convert the decimal values to Percentage.

Percentage Difference Between Two Numbers

Using the required formula, the user can find the difference between two numbers in the same column and calculate the percentage difference.

Example: A growth of the plant is recorded week by week. Calculate the percentage difference between two weeks using the formula.

Step 1: Enter the data in the worksheet, namely A1:C5

Step 2: The initial height of the plant is given in cell B2. Enter the formula in the cell C3 =(B3-$B$2)/$B$2.

In the formula, the value in cell B2 is made an absolute value.

Step 3: Press Enter. The result will be displayed in cell C3. Similarly, calculate the result for the remaining cells.

Here the significant growth of the plant in the first week is not calculated since the difference is not calculated.

To convert the values in percentage format present in cell range C2:C5, choose

Home->Styles Group->Percent and drag the formula to cell D5 to convert the decimal values to Percentage.

Keyboard Shortcut

The Keyboard Shortcut is used to convert the given value into Percentage. It is one of the easiest methods to calculate the percentage value, and the shortcut key is easier to remember.

The steps to use the Keyboard Shortcut are as follows:

Step 1: Enter the data such as Name, Science, English, Maths, Total, and Percentage in the worksheet, namely A1:F4

Step 2: To calculate the total value, enter the formula as =SUM(B2:D2). Press Enter. The total value will be displayed in cell E2. Use the fill handle to get the result for the remaining cell.

Step 3: To calculate the percentage value, the total marks are divided by 3. The value is multiplied by 100 to convert it into a percentage, so 300. Enter the formula in the cell F2 as =(E2/300).

The result will be displayed in cell E2:E4. Use the fill handle to display the result for the remaining cell.

Step 4: To convert the value into a percentage, select the cell range F2:F4 and press "Ctrl+Shift+%." The value containing the selected date range is converted into a Percentage.

An alternative method is to select Percentage in the Number group of the Home tab.

Summary

The concept of Percentage is used in various fields to calculate the required Percentage. It compares the samples with the different numbers of observations. The multiple formulas and methods to calculate the Percentage in Excel are described in this tutorial. The user can choose the easiest method to calculate the Percentage for the given data.

Next TopicConcatenation in Excel

← prev next →