You can calculate a percent variance by subtracting the original number from the new number, then dividing that result by the original. For example, if the baseline number is 100, and the new number is 110: =(110-100)/100 This formula can be used to calculate things like variance between this year and last year, variance between a budgeted and actual values,…
Tag: Formulas and Functions
How to calculate decrease by percentage in Excel
If you need to decrease a number by a certain percentage, you can use a simple formula that multiplies the number times the percent – 1. Formula =number*(1-percent) Explanation In the example, the active cell contains this formula: =C6*(1-D6) In this case, Excel first calculates the result of 1 – the value in D6 (.2) to get 0.8 which it…
How to get amount with percentage in Excel
If you have a total and a percentage that presents some amount of the total, you can calculate the amount represented by the percentage with a simple formula that multiples the total by the percentage. Formula = total * percent Explanation In the example, the active cell contains this formula: =C6*D6 To calculate the amount, Excel simply multiples the value…
How to get original number from percent change in Excel
To calculate the original number based on current value and known percentage change, you can use a simple formula that divides the current value by the percent plus 1. Formula =current/(percent+1) Explanation In the example, the active cell contains this formula: =C6/(B6+1) In this case, Excel first calculates the result of B6 + 1, then divides C6 by the result: =C6/(B6+1) =200/1.15 =173.913 Rounded result…
How to get original price from percentage discount in Excel
If you have a discounted price and know the discount percentage, you can calculate the original price with a simple formula that divides the discounted price by the result of 1 minus the discount percentage. See example below: Formula =price/(1-discount) Explanation How the formula works In the example, the active cell contains this formula: =C6/(1-D6) In this case, Excel first…
How to calculate percent change in Excel
If you need to calculate the percentage change between two values in Excel, you can use a formula that divides the change itself by the “old” value. A classic example would be to calculate the percentage between sales last month and sales this month. Formula =(new_value-old_value)/old_value Explanation In the example, the active cell contains this formula: =(D6-C6)/C6 In this case,…
How to calculate percentage discount in Excel
If you have a discounted price and an original price, and you want to know the discount as a percentage, you can calculate the percentage discount using a formula that divides the discounted price by the original price and then subtracts the result from one. Formula = 1-(discount_price/original_price) Explanation In the example, the active cell contains this formula: =1-(D5/C5) In…
How to calculate percentage of total in Excel
To calculate percent of a total (i.e. calculate a percent distribution), you can use a formula that simply divides an amount by the total. Formula =amount/total Explanation In the example shown, the formula in D6 is: =C6/$C$11 Note: the result is formatted with Percentage number format to show 25%, 10%, etc. How the formula works In the example, D6 contains the formula…
How to calculate profit margin percentage in Excel
If you need to calculate a profit margin, you can easily do so with a simple formula that uses the sale price and the cost. Formula =(price-cost)/price Explanation In the example shown, the first formula looks like this: =(B4-C4)/B4 Make sure you use parentheses to control the order of operations. Note that the result will be a decimal like 0.40,…
How to calculate total from percentage in Excel
If you have an amount and the percentage that the amount represents of a total, you can calculate the total with a formula that simply divides the amount by the percentage. Formula = amount / percent Explanation In the example, the active cell contains this formula: =C6/D6 Excel simply divides the value in cell C6 by the percentage value in…
How to increase by percentage in Excel
If you need to increase a number by a certain percentage, you can use a simple formula that multiplies the number times the percent + 1. Formula =number*(1+percent) Explanation How this formula works In the example, the active cell contains this formula: =C6*(1+D6) In this case, Excel first calculates the result of 1 + the value in D6 (.2) to…
How to calculate percent of goal in Excel
If you need to calculate percent of goal, you can do so with a simple formula that divides the actual by the goal amount, with the result formatted using the percentage number format. This same formula can be used to calculate things like percent of target, percent of budget, percent of forecast, and so on. Formula =actual/goal Explanation In the…
How to calculate percent of students absent in Excel
To calculate percent of students attending a class, given a total class size and the number of students attending, you can use a simple formula that divides students absent (calculated by subtracting attending from total) by the total. The result must be formatted using the percentage number format. Formula =(total-attended)/total Note: when working manually, percentage results are calculated by dividing one…
How to calculate percent sold in Excel
To calculate percentage sold, you can use a simple formula that divides sold amount by the total amount. Formula =sold/total Note: when working manually, percentage results are calculated by dividing one number by another, then multiplying by 100 to express as a percentage. However, because Excel can display decimal or fractional values automatically as a percentage by applying the Percentage number format, there…
How to calculate project complete percentage in Excel
To calculate the percentage complete for a project with a list of tasks, you can use a simple formula based on the COUNTA function. Formula =COUNTA(range1)/COUNTA(range2) Explanation In the example shown, the formula in F6 is: =COUNTA(C5:C11)/COUNTA(B5:B11) How this formula works At the core, this formula simply divides tasks complete by the total task count: =complete/total which is then formatted…
How to Count Number of Characters in a Cell or Range of Cells in Excel
Learn how to count the number of characters in a cell or range of cells and how to count how many times a specific character occurs in a cell or range of cells. 1. The LEN function in Excel counts the number of characters in a cell. Explanation: the LEN function counts 2 numbers, 1 space and 6 letters. 2. You can combine the…
Count Cells with Text in Excel
This page illustrates multiple ways to count cells with text in Excel. COUNTIF function to count cells that contain specific text. And COUNTIFS function counts cells based on two or more criteria. 1. Use the COUNTIF function and the asterisk symbol (*) to count cells with text. 2. You can also create an array formula to count cells with text. Note: visit our page about array…
How to Copy, Paste and Retain Exact Copied Formula in another Cell in Excel
When you copy a formula, Excel automatically adjusts the cell references for each new cell the formula is copied to. For example, cell A3 below contains a formula which adds the value of cell A2 to the value of cell A1. When you copy this formula to cell B3 (select cell A3, press CTRL + c, select cell B3, press CTRL +…
How to get number of days, weeks, months or years between two dates in Excel
To get the number of days, weeks or years between two dates in Excel, use the DATEDIF function. The DATEDIF function has three arguments. 1. Fill in “d” for the third argument to get the number of days between two dates. Note: =A2-A1 produces the exact same result! 2. Fill in “m” for the third argument to get the number of months…
How to enter Today’s Date or Static Date and Time in Excel
To enter today’s date in Excel, use the TODAY function. To enter the current date and time, use the NOW function. To enter the current date and time as a static value, use keyboard shortcuts. Today and Now 1. To enter today’s date in Excel, use the TODAY function. Note: the TODAY function takes no arguments. This date will update automatically when you open…
How to Calculate Age in Excel
To calculate the age of a person in Excel, use the DATEDIF function and the TODAY function. The DATEDIF function has three arguments. 1. Enter the date of birth into cell A2. 2. Enter the TODAY function into cell B2 to return today’s date. 3. The DATEDIF function below calculates the age of a person. Note: fill in “y” for the third…
How to get Weekdays, Working days between Two Dates in Excel
Learn how to get the day of the week of a date in Excel and how to get the number of weekdays/working days between two dates. Weekday Function 1. The WEEKDAY function in Excel returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week of a date. Apparently, 12/18/2017 falls on a Monday. 2. You can also use the TEXT function…
How to calculate number of Days until Birthday
To calculate the number of days until your birthday in Excel, execute the following steps. 1. Enter the date of birth into cell A2. 2. Enter the TODAY function into cell B2 to return today’s date. 3. The most difficult part in order to get the number of days until your birthday is to find your next birthday. The formula below does the trick. Explanation:…
How to create simple Time Sheet in Excel
This example teaches you how to create a simple timesheet calculator in Excel. Cells that contain formulas are colored light yellow. 1. To automatically calculate the next 4 days and dates when you enter a start date, use the formulas below. 2. Select the cells containing the times. 3. Right click, click Format Cells, and select the right Time format. Use the circled…
Find Last Day of the Month in Excel
To get the date of the last day of the month in Excel, use the EOMONTH (End of Month) function. 1. For example, get the date of the last day of the current month. Note: the EOMONTH function returns the serial number of the date. Apply a Date format to display the date. 2. For example, get the date of the last day of the…
How to get Holiday Date from Year in Excel
This example teaches you how to get the date of a holiday for any year (2019, 2020, etc). Before you start: the CHOOSE function returns a value from a list of values, based on a position number. For example, =CHOOSE(3,”Car”,”Train”,”Boat”,”Plane”) returns Boat. The WEEKDAY function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week of a date. 1. This is…
How to calculate Quarter of Date in Excel
An easy formula that returns the quarter for a given date. There’s no built-in function in Excel that can do this. 1. Enter the formula shown below. Explanation: ROUNDUP(x,0) always rounds x up to the nearest integer. The MONTH function returns the month number of a date. In this example, the formula reduces to =ROUNDUP(5/3,0), =ROUNDUP(1.666667,0), 2. May is in Quarter 2. 2. Let’s…
How to Separate Text Strings in Excel
This example teaches you how to separate strings in Excel. Asides from using ‘Convert Text to Columns Wizard’ in data tab, you can as well follow the steps below to Separate Text Strings in Excel . The problem we are dealing with is that we need to tell Excel where we want to separate the string. In case of Smith, Mike the comma is…
How to calculate Number of Instances in Excel
This example describes how to count the number of instances of text (or a number) in a cell. 1. Use the LEN function to get the length of the string (25 characters, including spaces). 2. The SUBSTITUTE function replaces existing text with new text in a string. LEN(SUBSTITUTE(A1,B1,””)) equals 13 (the length of the string without the words dog). If we…
Excel Functions by Categories
This page provides a complete Excel Functions list, grouped by category. Each of the function links will take you to a dedicated page, where you will find a description of the function, with examples of use and details of common errors. Text Functions Logical Functions Information Functions Date and Time Functions Lookup and Reference Functions Math and Trig Functions Statistical…
Excel Two-Way Lookup Example
This example teaches you how to lookup a value in a two-dimensional range. We use the INDEX and the MATCH function in Excel. Below you can find the sales of different ice cream flavors in each month. 1. To find the position of Feb in the range A2:A13, use the MATCH function. The result is 2. 2. To find the position of Chocolate in…
Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
Formula auditing in Excel allows you find and correct errors in formulas. Also, displays the relationship between formulas and cells using Trace Precedents, Remove Arrows, Trace Dependents, Show Formulas, Error Checking and Evaluate Formula. The example below helps you master Formula Auditing quickly and easily. Trace Precedents You have to pay $96.00. To show arrows that indicate which cells are used to calculate this value, execute…
Floating Point Errors in Excel
Floating point numbers are numbers that follow after a decimal point. Excel stores and calculates floating point numbers. On the other hand, excel cannot store numbers with very large floating points, so for the function to work correctly, the floating point numbers will need to be rounded to 5 decimal places. i.e The result of a formula is a very close…
Sum Largest Numbers using LARGE function in Excel
LARGE function example shows you how to create an array formula that sums the largest numbers in a range. Syntax: LARGE(array, k) Returns the k-th largest value in a data set. You can use LARGE to return the highest, runner-up, or third-place score. For example, 1. Find the second to largest number, use the following function. 2. To sum the 4 largest numbers (don’t…
Examples of Sum with Or Criteria in Excel
How to sum data based upon multiple criteria? Summing with Or criteria in Excel can be tricky. This article shows several easy to follow examples. 1. We start simple. For example, we want to sum the cells that meet the following criteria: Google or Facebook (one criteria range). 2a. However, if we want to sum the cells that meet the following criteria: Google or Stanford (two criteria ranges), we…
Find Most Frequently Occurring Word in Excel Worksheet
Example of how to find the most frequently occurring word in an Excel Worksheet. You can use the MODE function to find the most frequently occurring number. However, the MODE function only works with numbers. You can use the COUNTIF function to count the number of occurrences of each word. However, we are looking for a single formula that returns the most frequently occurring word (circle in…
Subtotal function in Excel
The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use. The SUBTOTAL function is designed for columns of data, or vertical ranges. Use the SUBTOTAL function in Excel instead of SUM, COUNT, MAX, etc. to ignore rows hidden by a filter or manually hidden rows. Rows Hidden by a Filter 1.…
Superscript and Subscript in Excel
Superscript or Subscript generally is a number, figure, symbol, or indicator that is smaller than the normal line of type and is set slightly below it (subscript) or above it (superscript). It’s easy to format a character as superscript or subscript in Excel. 1. For example, double click cell A1. 2. Select the value 2. 3. Right click, and then click Format Cells (or press Ctrl +…
Paste Special Operations in Excel
Use the ‘Paste Special Operations’ to quickly perform operations on a range of cells in Excel. 1. Select cell D3. 2. Right click, and then click Copy. 3. Select the range A1:B8. 4. Right click, and then click Paste Special. 5. Click Multiply. Note: you can also Divide, Add or Subtract a value. 6. Click OK. Note: all values are increased…
Calculate Compound Interest in Excel
What’s compound interest and what’s the formula for compound interest in Excel? How to Calculate Compound Interest in Excel This example gives you the answers to these questions. 1. Assume you put $100 into a bank. How much will your investment be worth after one year at an annual interest rate of 8%? The answer is $108. 2. Now this interest ($8) will…