Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Get nth match with INDEX / MATCH in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • How to use Excel MMULT Function
  • How to use Excel LOOKUP Function
  • How to use Excel MATCH Function

Data Analysis Examples

  • Conditional Formatting Rules in Excel
  • How to calculate current stock or inventory in Excel
  • Chart Axes in Excel
  • How to Create Gantt Chart in Excel
  • Create Scatter Chart in Excel

Data Validation Examples

  • Excel Data validation must not contain
  • Excel Data validation require unique number
  • Excel Data validation specific characters only
  • Excel Data validation only dates between
  • Excel Data validation don’t exceed total

Tag: Formulas and Functions

How to calculate percent variance in Excel

by

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 …

Continue Reading

How to calculate decrease by percentage in Excel

by

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) …

Continue Reading

How to get amount with percentage in Excel

by

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, …

Continue Reading

How to get original number from percent change in Excel

by

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 …

Continue Reading

How to get original price from percentage discount in Excel

by

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) …

Continue Reading

How to calculate percent change in Excel

by

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 …

Continue Reading

How to calculate percentage discount in Excel

by

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 …

Continue Reading

How to calculate percentage of total in Excel

by

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 …

Continue Reading

How to calculate profit margin percentage in Excel

by

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 …

Continue Reading

How to calculate total from percentage in Excel

by

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 …

Continue Reading

How to increase by percentage in Excel

by

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 …

Continue Reading

How to calculate percent of goal in Excel

by

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. …

Continue Reading

How to calculate percent of students absent in Excel

by

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 …

Continue Reading

How to calculate percent sold in Excel

by

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 …

Continue Reading

How to calculate project complete percentage in Excel

by

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: …

Continue Reading

How to Count Number of Characters in a Cell or Range of Cells in Excel

by

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. …

Continue Reading

Count Cells with Text in Excel

by

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. …

Continue Reading

How to Copy, Paste and Retain Exact Copied Formula in another Cell in Excel

by

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 …

Continue Reading

How to get number of days, weeks, months or years between two dates in Excel

by

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 …

Continue Reading

How to enter Today’s Date or Static Date and Time in Excel

by

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 …

Continue Reading

How to Calculate Age in Excel

by

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 …

Continue Reading

How to get Weekdays, Working days between Two Dates in Excel

by

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 …

Continue Reading

How to calculate number of Days until Birthday

by

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 …

Continue Reading

How to create simple Time Sheet in Excel

by

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 …

Continue Reading

Find Last Day of the Month in Excel

by

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 …

Continue Reading

How to get Holiday Date from Year in Excel

by

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 …

Continue Reading

How to calculate Quarter of Date in Excel

by

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 …

Continue Reading

How to Separate Text Strings in Excel

by

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 …

Continue Reading

How to calculate Number of Instances in Excel

by

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 …

Continue Reading

Excel Functions by Categories

by

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 …

Continue Reading

Excel Two-Way Lookup Example

by

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 …

Continue Reading

Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’

by

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 …

Continue Reading

Floating Point Errors in Excel

by

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 …

Continue Reading

Sum Largest Numbers using LARGE function in Excel

by

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 …

Continue Reading

Examples of Sum with Or Criteria in Excel

by

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: …

Continue Reading

Find Most Frequently Occurring Word in Excel Worksheet

by

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 …

Continue Reading

Subtotal function in Excel

by

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 …

Continue Reading

Superscript and Subscript in Excel

by

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 …

Continue Reading

Paste Special Operations in Excel

by

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. …

Continue Reading

Calculate Compound Interest in Excel

by

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. …

Continue Reading

Posts navigation

  • 1
  • 2
  • Next

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Convert Text to Numbers in Excel
  • How to split text with delimiter in Excel
  • How to count specific characters in a range in Excel
  • How to count total characters in a range in Excel
  • Count Number of Words in Excel
  • How to calculate quarter from date in Excel
  • How to get year from date in Excel
  • WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate next scheduled event in Excel
  • Calculate days remaining in Excel
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment for a loan in Excel
  • COUPDAYBS function: Description, Usage, Syntax, Examples and Explanation
  • ODDFYIELD function: Description, Usage, Syntax, Examples and Explanation
  • AMORLINC function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles