Percentages are part of our everyday life. We use them to indicate tax amounts on bills, to decide whether the return on our investments is acceptable, to see how much you’re saving on a clearance item, and to measure performance. Percentages are merely a fraction of the number expressed as a value out of one hundred. Expressed differently, percentages are the ratio of a number expressed as a fraction of 100.
If you want to calculate percentages in Excel then you need to understand the basics of how to calculate percentages. The Excel 2013 made easy course will help you create a strong foundation for using the world’s most popular business software, so why not improve your Excel skills today and register for this course.
Let’s take a look at a few simple percentage examples:
Example 1: how to calculate test results as percentages
As students, we have become accustomed to seeing our results expressed as a percentage. Percentages allow us to quickly see how we are doing and to assess whether we are improving or not. The total marks of tests often differ and if we merely compared our test scores with one another, it would be really difficult to tell if we were improving or not; but expressing the marks as percentages makes this process a lot easier.
The following table represents the test scores of the students in three different tests. You can see the test scores written as fractions, so for example, Adam scored twelve out of twenty for his first test, thirty five out of fifty for his second and twenty six out of 30 for the third test.
As you can see, it is quite difficult to determine whether Adam is improving or not. But we can express these fractions as a percentage and you will see that by expressing the test scores as a percentage, the figures become a lot easier to work with.
To express the scores as a percentage we need to express the result of the fraction as a fraction of one hundred. To work out the percentage we therefore need to take the student’s mark and divide that by the total test mark and then tell Excel we are working out a percentage. Excel will express our fraction as a percentage automatically when we click the % format. To learn to use Excel like a Pro, fast, click here.
To work out the percentages, we first enter each test score into each cell using the following formula:
Fraction = Student’s Test Score/Total Test Score
So in Adam’s case, to work out the fraction for the first test, you will type “=12/20” into cell C3 to work out the fraction.
Our worksheet will then look like this:
Now that we have the test scores as fractions, we can use the Excel percentage format to multiply the fraction by one hundred to turn the fraction into a percentage.
There are various ways you can format the cell as a percentage.
You can select the cell by clicking on it and then right click to bring up a menu that contains various formatting options as shown below.
To format the cells as a percentage click the “Format Cells” option. Then under the Number Tab, select Percentage to format the cell as a percentage and select the number of decimal points you wish to show in your results. The sample box above your selection shows you what your cell will look like once its formatted.
Alternatively you can select the range of cells and then click the formatting tools available on the home tab. Take a look at the formatting options below:
If we select the range and then choose percentage from the format tab then we can format the entire table as percentages instead of formatting one cell at a time. The table will look like this:
Now you can easily see that Adam is improving since he received 60% in his first test, then 64% in his second and 87% in his third test. You can also easily see that Sarah’s grades are slipping since she got 70% in her first test, 68% in her second test and 67% in her third test.
For more information and training on how to format your Excel worksheets and how to harness the power of excel, the Mastering Excel for Beginners course from udemy will teach you about the various cell formatting options available in Excel.
Let us take another look at a percentage example.
Example 2: How to work out what interest the bank is charging
Percentages are often used to compare loan rates from various financial institutions. In this example we will use percentages to compare four different loans to see which loan offers us the best repayment plan.
Assume you have four loans, your mortgage, your car loan, your overdraft and a credit card loan. To decide if you should consolidate your loans, and which loan to use to consolidate your loans, you need to work out the annual interest percentage on each loan to see which loan will save you the most money.
Here are our loans and their repayment amounts:
To calculate the annual interest rate, we need to first work out what we actually pay per annum. So we need to multiply our monthly repayment by twelve. The data will look as follows:
Now we can work out the repayment as a fraction of the total loan to work out the interest rate percentage we are being charged. To work out the percentage, the following formula is used:
Interest rate percentage = monthly repayment / loan amount
Once we have entered the formula, you need to format the cells as a percentage.
The following data represents the interest charged on each of our loans:
Now you can easily see that the cheapest loan in this case is the mortgage loan we have at the bank. In fact, we can use percentages to work out how much money we would save if we loaned money from the cheapest loan provider to pay off all of our other loans.
To work out what we would save we first need to calculate the difference between the lowest interest rate we have and the other interest rates we are paying. This is a simple subtraction formula in Excel and the data would look like this:
Now we have the savings we can use the interest rate difference to work out how much we would save per annum and then divide that by twelve to work out the monthly saving:
For other tutorials on how to work with formulas in Excel, why not sign up for an advanced excel course from Udemy today?