Excel Formula Examples - Free Excel Tutorial

How to Average Only Positive or Negative Numbers of a Range

Suppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative numbers ignored. In this article, we will help you to construct a formula with AVERAGE… read more »

Sort Positive Numbers and Negative Numbers by Absolute Values

If both positive numbers and negative numbers exist in the same column, when sorting them by absolute values, we can sort them with the help of ABS function and helper column. In this article, we will show you the way to sort numbers by absolute values. EXAMPLE We want to sort numbers in “Numbers” column… read more »

Get Employee Information by VLOOKUP

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today, in this article, we will show you the way to apply VLOOKUP to retrieve employee information. I hope this article will help you in your daily work. EXAMPLE The left table contains employee information like HRID, name, age, and… read more »

VLOOKUP with Two Lookup Tables

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today we will show you the application of VLOOKUP function when there are two lookup tables. EXAMPLE Table1 and table2 record the rates of Y2020 and Y2021 separately. Rates are increased in Y2021. In “Statistic” table, we want to fill… read more »

VLOOKUP with Multiple Lookup Values

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match by setting lookup matching modes. Today, we will show you the way to use VLOOKUP… read more »

VLOOKUP Data by Date

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match by setting lookup matching modes. Today, we will show you the way to apply VLOOKUP… read more »

VLOOKUP – Retrieve Data from Another Workbook

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match by setting lookup matching modes. Today, we will show you the way to apply VLOOKUP… read more »

VLOOKUP – Retrieve Data from Another Worksheet

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match by setting lookup matching modes. Today, we will show you the way to apply VLOOKUP… read more »

Case Sensitive Lookup with SUMPRODUCT and EXACT

Today, we will show you how to use SUMPRODUCT and EXACT to perform a case sensitive exact match. In this article, we provide a simple example to calculate bonus for employees whose names are case-sensitive. If you meet similar scenarios in your daily work, you can directly use this formula to deal with your problem…. read more »

Basic Usage of INDEX & MATCH – Case Sensitive Lookup

In Excel, INDEX function and MATCH function are often used together for retrieving data from a particular position. MATCH function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting different match types. They are not case-sensitive functions. If we need to perform a case-sensitive lookup, we… read more »

Basic Rates Calculation by VLOOKUP Based on Weight Band

Microsoft Excel provides many functions that can execute logical test, search data, return current date and something else. They are very useful in daily work. And Excel VLOOKUP function is one of Excel most frequently used functions. It belongs to lookup & reference functions and it can perform approximate match or exact match by setting… read more »

Basic Grade Calculation by VLOOKUP Function – Approximate Match

In Excel, except combination INDEX+MATCH, we can also apply other functions to search data, for example VLOOKUP function. Like MATCH function, VLOOKUP function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting lookup matching modes. Today, we will show you the way to calculate “Grade” by… read more »

Basic Discount Calculation with VLOOKUP Function

In Excel, except combination INDEX+MATCH, we can also apply other functions to search data, for example VLOOKUP function. Like MATCH function, VLOOKUP function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting lookup matching modes. Today, we will show you the way to calculate discount by… read more »

Basic Usage of INDEX & MATCH – Exact Match

In Excel, INDEX function and MATCH function are often used together for returning value or cell reference or range reference from specified position. And MATCH function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting different match type values. Today, we will show you how to… read more »

Basic Usage of INDEX & MATCH – Approximate Match

In Excel, INDEX function and MATCH function are often used together for returning value or cell reference or range reference from specified position. And MATCH function is one of Excel lookup & reference functions that can perform approximate match by setting match type. Today, we will show you the basic usage of INDEX and MATCH… read more »

Average of Working Hours by Formula in Excel

Suppose you are going to count the average working hours of employees in the company based on different months. If a worksheet including working hours on different dates and months is prepared for you, do you know how can we calculate the average of working hours based on months? In this article, through explaining the… read more »

Average per Week by Formula in Excel

We usually apply AVERAGE function or relevant functions to return average directly in Excel worksheet. But in some situations, only applying average relevant functions cannot figure out our problem. Sometimes we can create a formula with functions and mathematical operation inside to calculate average depends on different situations. In this article, through explaining the example… read more »

Average with Multiple Criteria in Excel

AVERAGEIFS function is one of the most popular functions in Excel. Apply AVERAGEIFS function, we can calculate average simply if there are multiple conditions. In this article, we will introduce you to apply AVERAGEIFS function to calculate average of a set of numbers which are filtered by multiple criteria. You can get the basic knowledge… read more »

Average of Top N Values in Excel

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average properly based on different situations. In this article, we will introduce you the way to calculate average of the top N numbers from a range. To approach our goal, except the main AVERAGE… read more »

Sidebar