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

Lookup and Reference Examples

  • How to use Excel MATCH Function
  • How to get last column number in range in Excel
  • Merge tables with VLOOKUP in Excel
  • Last row number in range
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Excel Bar Chart
  • How To Filter Data in Excel

Data Validation Examples

  • Excel Data validation allow uppercase only
  • Prevent invalid data entering in specific cells
  • Excel Data validation allow weekday only
  • Excel Data validation only dates between
  • Excel Data validation don’t exceed total

Category: Excel Functions

Left Lookup in Excel

by

The VLOOKUP function only looks to the right. To look up a value in any column and return the corresponding value to the left, simply use the INDEX and the MATCH function. 1. The MATCH function returns the position of a value in a given range. Explanation: 104 found at position 4 in the range $G$4:$G$7. 2. Use this result and the INDEX function to …

Continue Reading

Excel MAX, MATCH and ADDRESS function illustration

by

Locate Maximum Value in Excel This example teaches you how to find the cell address of the maximum value in a column. 1. First, we use the MAX function to find the maximum value in column A. 2. Second, we use the MATCH function to find the row number of the maximum value. Explanation: the MATCH function reduces to …

Continue Reading

Convert text string to valid reference in Excel using Indirect function

by

Applying Excel  INDIRECT function to Cell Reference, Range Reference, Named Range, Worksheet Reference. Use the INDIRECT function in Excel to convert a text string into a valid reference. You can use the & operator to create text strings. Cell Reference Use the INDIRECT function in Excel to convert a text string into a valid cell reference. 1. For example, take a look …

Continue Reading

Two-column Lookup in Excel

by

This example teaches you how to perform a two-column lookup in Excel. See the example below. We want to look up the salary of James Clark, not James Smith, not James Anderson. 1. To join strings, use the & operator. 2. The MATCH function returns the position of a value in a given range. Insert the MATCH function …

Continue Reading

Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions

by

To find the closest match to a target value in a data column, use the INDEX, MATCH, ABS and the MIN function in Excel. Use the VLOOKUP function in Excel to find an approximate match. 1. The ABS function in Excel returns the absolute value of a number. Explanation: C3-F2 equals -39. The ABS function removes the minus sign (-) from a …

Continue Reading

Convert Negative Numbers to Zero in Excel

by

A clever Excel trick to change negative numbers to zero but leave positive numbers unchanged. Below you can find two columns with numbers. To subtract the numbers in column B from the numbers in column A, execute the following steps. 1. Select cell C1. 2. Enter the formula =A1-B1 3. Click the lower right corner of cell C1 and …

Continue Reading

Convert Text to Numbers in Excel

by

By default, text is left-aligned and numbers are right-aligned in Excel. Atimes user enter inverted comma before inputting data values in a cell which automatically converts a number to text. This example teaches you how to convert ‘text strings that represent numbers’ to numbers. 1. Select the range A1:A4 and change the number format to General. 2. Numbers preceded by …

Continue Reading

Excel ABS function Example

by

The ABS function in Excel returns the absolute value of a number. In other words: the ABS function removes the minus sign (-) from a negative number, making it positive. 1. For example, the ABS function in cell B1 below returns the absolute value of a negative number. The ABS function has no effect on 0 (zero) or positive …

Continue Reading

Forecast vs Trend Function in Excel

by

The FORECAST and TREND function give the exact same result. When you add a trendline to an Excel chart, Excel can display the equation in a chart (see below). You can use this equation to calculate future sales. Explanation: Excel uses the method of least squares to find a line that best fits the points. The R-squared value equals 0.9295, which is a …

Continue Reading

How to Create Box and Whisker Plot in Excel

by

This example teaches you how to create a box and whisker plot in Excel. A box and whisker chart shows distribution of data into quartiles, highlighting the mean and outliers. The boxes may have lines extending vertically called “whiskers”. These lines indicate variability outside the upper and lower quartiles, and any point outside those lines or whiskers is …

Continue Reading

PERCENTILE and QUARTILE function examples in Excel

by

This example teaches you how to use the PERCENTILE and QUARTILE function in Excel. Below you can find a list of scores (green fill for illustration only). 1. Use the PERCENTILE function shown below to calculate the 30th percentile. Excel returns the value 12.7. This means that 30% (6 out of 20) of the scores are lower or …

Continue Reading

Calculate Weighted Average in Excel

by

To calculate a weighted average in Excel, simply use the SUMPRODUCT and the SUM function. 1. The AVERAGE function below calculates the normal average of three scores. Suppose your teacher says, “The test counts twice as much as the quiz and the final exam counts three times as much as the quiz”. 2. Below you can find the …

Continue Reading

Use of INT and TRUNC function in Excel

by

This example illustrates two functions to chop off decimals in Excel. The INT and the TRUNC function. Int The INT (Integer) function rounds a number down to the nearest integer. Conclusion: the INT function only chops off decimals from positive numbers. Trunc Truncate a number to a given precision. The TRUNC (Truncate) function always chops off decimals. The TRUNC function can keep …

Continue Reading

Excel MROUND, CEILING and FLOOR function examples

by

Find Nearest Multiple in Excel This example illustrates three functions to round numbers to a multiple of x in Excel. The MROUND, CEILING and the FLOOR function. Mround 1. For example, round a number to the nearest multiple of 10. 2. For example, round a number to the nearest multiple of 5. Ceiling The same as MROUND but rounds up. Floor The same as …

Continue Reading

How To Use MaxIfs and MinIfs in Excel

by

This function combines the MAX and IF function in order to get the maximum value in a data set of Excel worksheet. To look up the maximum value in a range based on specific criteria, you can use a basic array formula based on the IF function. {=MAX(IF(criteria_range=criteria,value_range))} Use the MAXIFS and MINIFS function in Excel …

Continue Reading

Even and Odd function in Excel

by

This example illustrates the EVEN and the ODD function in Excel. It also shows you how to determine if a number is even or odd. Even 1. Rounds a positive number up to the nearest even integer. 2. Rounds a negative number down to the nearest even integer. Odd 1. Rounds a positive number up to the nearest odd integer. …

Continue Reading

How To Use AGGREGATE function to sum a range with errors in Excel

by

Excel functions such as SUM, COUNT, LARGE and MAX don’t work if a range includes errors. However, you can easily use the AGGREGATE function to fix this. 1. For example, Excel returns an error if you use the SUM function to sum a range with errors. 2. Use the AGGREGATE function to sum a range with errors. Explanation: …

Continue Reading

How to return blank in place of #DIV/0! error in Excel

by

This example illustrates the IFERROR function and how to return blank in place of #DIV/0! error in Excel. 1. For example, Excel displays the #DIV/0! error when a formula tries to divide a number by 0. 2. Use the IFERROR function. If a cell contains an error, an empty string (“”) is displayed.

Continue Reading

Count Errors in Excel

by

IF function and ISERROR function are used to check for an error in Excel. This example shows you how to create an array formula that counts the number of errors in a range. 1. We use the IF function and the ISERROR function to check for an error in the record below: Explanation: the IF function returns 1, if an …

Continue Reading

Example of Count with Or Criteria in Excel

by

How to count data based upon multiple criteria? Counting 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 count the number of cells that contain Google or Facebook (one column). 2a. However, if we want to count the number of rows that contain Google or Stanford …

Continue Reading

How to Sum Every nth Row in Excel

by

This example shows you how to create an array formula that sums every nth row in Excel. We will show it for n = 3, but you can do this for any number. 1. The ROW function returns the row number of a cell.   2. The MOD function gives the remainder of a division. For …

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

How to Sum Range with Errors in Excel

by

This example shows you how to create an array formula that sums a range with errors. You can also use the AGGREGATE function to sum a range with errors. 1. We use the IFERROR function to check for an error. Explanation: the IFERROR function returns 0, if an error is found. If not, it returns the value of the cell. …

Continue Reading

Convert Numbers to Text in Excel

by

By default, numbers are right-aligned and text is left-aligned in Excel. This example teaches you how to convert numbers to ‘text strings that represent numbers’. 1. Select the range A1:A4 and change the number format  to Text. 2. Precede a number by an apostrophe and it will also be treated as text. 3a. If you add text to a number and …

Continue Reading

SUMIF function in Excel

by

The powerful SUMIF function in Excel sums cells based on one criteria. This page contains many easy to follow SUMIF examples. Numbers 1. The SUMIF function below (two arguments) sums values in the range A1:A5 that are less than or equal to 10. 2. The following SUMIF function gives the exact same result. It uses the & operator to …

Continue Reading

Join Text Strings Using Concatenate or ‘& Operator’ in Excel

by

This example illustrates how to concatenate (join) strings in Excel. When joining text and a number, use the TEXT function to format that number. 1. Simply use the & operator to join strings. Note: to insert a space, use ” ” 2. The CONCATENATE function produces the exact same result. 3a. The CONCAT function in Excel 2016 produces the exact …

Continue Reading

Not Equal To ‘<>‘ operator in Excel

by

In Excel, <> means not equal to. The <> operator in Excel checks if two values are not equal to each other. Let’s take a look at a few examples. 1. The formula in cell C1 below returns TRUE because the text value in cell A1 is not equal to the text value in cell B1. 2. …

Continue Reading

Count Unique Values in Excel

by

This example shows you how to create an array formula that counts unique values. Examples: 1.. To count the number of 5’s, use the following function. We use the COUNTIF function.  2. To count the unique values (don’t be overwhelmed), we add the SUM function, 1/, and replace 5 with A1:A6. 3. Finish by pressing CTRL + SHIFT …

Continue Reading

Excel Standard Deviation

by

What is Standard Deviation (STDEV.P, STDEV.S)? This page explains how to calculate the standard deviation based on the entire population using the STDEV.P function in Excel and how to estimate the standard deviation based on a sample using the STDEV.S function in Excel. What is Standard Deviation? Standard deviation is a number that tells you how far numbers are …

Continue Reading

Countif function in Excel

by

COUNTIF is one of the statistical functions, it is used to count the number of cells that meet a criterion This chapter covers  many easy to follow COUNTIF examples like Numbers, Text, Booleans, Errors and ‘Or Criteria’. Numbers 1. The COUNTIF function below counts the number of cells that contain the value 20. 2. The following COUNTIF …

Continue Reading

RAND vs RANDBETWEEN function examples in Excel

by

Excel has two useful functions when it comes to generating random numbers. The RAND and RANDBETWEEN function. Rand The RAND function generates a random decimal number between 0 and 1. 1. Select cell A1. 2. Type RAND() and press Enter. The RAND function has no arguments. 3. To generate a list of random numbers, select cell A1, click on …

Continue Reading

Vlookup Examples in Excel

by

The VLOOKUP function is one of the most popular functions in Excel. This chapter contains many easy to follow VLOOKUP examples for Exact Match, Approximate Match, Right Lookup, First Match, Case-insensitive, Multiple Criteria, #N/A error  and  Multiple Lookup Tables. Exact Match Most of the time you are looking for an exact match when you use the VLOOKUP function in Excel. …

Continue Reading

Excel If, Nested If, And/Or Criteria Examples

by

IF function is one of the most used functions in Excel. This page contains many easy to follow IF examples. Simple If Examples The IF function checks whether a condition is met, and returns one value if true and another value if false. 1a. For example, take a look at the IF function in cell B2 below. …

Continue Reading

Offset in Excel

by

The OFFSET function in Excel returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells. OFFSET function Syntax; OFFSET(reference, rows, cols, [height], [width]) 1. The OFFSET function below returns the cell that is 3 rows below and 2 columns to the right of cell A2. The …

Continue Reading

Round numbers in Excel

by

This chapter illustrates three Mathematical functions ROUND, ROUNDUP and ROUNDDOWN. Navigation: Formula Tab → Function Library Group → Math & Trig Category Before your start: if you round a number, you lose precision. If you don’t want this, show fewer decimal places without changing the number itself. Round Syntax: ROUND(number, num_digits) 1. Round a number to two decimal places. …

Continue Reading

9 Mathematical Computations Example using Excel Statistical Function

by

In addition to formulas, another way to conduct mathematical computations in Excel is through functions. Statistical functions apply a mathematical process to a group of cells in a worksheet. This chapter gives an overview of some very useful statistical functions like: AVERAGE function, AVERAGEIF function, MEDIAN function, SUMIF function, MIN function, MODE function, STEDV function, LARGE function, SMALL function. Average To calculate the average of a …

Continue Reading

PMT, RATE, NPER, PV and FV Financial Functions in Excel

by

Learn all about Excel’s PMT, RATE, NPER, PV and FV To illustrate Excel’s most popular financial functions, we consider a loan with monthly payments, an annual interest rate of 6%, a 20-year duration, a present value of $150,000 (amount borrowed) and a future value of 0 (that’s what you hope to achieve when you pay off a loan). We make monthly payments, …

Continue Reading

Excel VLOOKUP and HLOOKUP functions Examples

by

Learn all about Excel’s lookup & reference functions such as the VLOOKUP, HLOOKUP, CHOOSE, MATCH and INDEX  function. Navigation: Formula Tab → Function Library Group → Lookup and Reference Vlookup VLOOKUP stands for Vertical lookup.  The VLOOKUP function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column …

Continue Reading

Manipulating text strings using Left, Mid, Right, Len, Substitute in Excel

by

Excel has many functions to offer when it comes to manipulating text strings. Formula tab → Function Library Group → Text Function N/B: Every formula in excel must begin with an equal sign, function then in bracket; cell name/cell ranges/cell references. Syntax: =fx(cell name/cell ranges/cell references) Example: = Sum( A1:B4) Where Sum is the function name and A1:B4 is …

Continue Reading

Excel Date & Time Functions Example

by

To enter a date in Excel, use the “/” or “-” characters. To enter a time, use the “:” (colon). You can also enter a date and a time in one cell. Note: Dates are in US Format. Months first, Days second. This type of format depends on your windows regional settings. Learn more about Date and Time formats. Year, …

Continue Reading

Posts navigation

  • Previous
  • 1
  • …
  • 17
  • 18
  • 19
  • 20
  • 21
  • 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
  • Remove text by matching in a cell in Excel
  • Count Number of Words in Excel
  • Remove first character in a cell in Excel
  • How to extract substring in Excel
  • TRIM function: Description, Usage, Syntax, Examples and Explanation
  • List holidays between two dates in Excel
  • Two ways to sum time over 30 minutes in Excel
  • How to get number of days, weeks, months or years between two dates in Excel
  • Pad week numbers with zeros in Excel
  • Steps to create Dynamic calendar grid in Excel
  • MDURATION function: Description, Usage, Syntax, Examples and Explanation
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
  • Example of Future value of annuity in Excel
  • Calculate cumulative loan interest in Excel
  • ACCRINTM function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles