Excel Examples

Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single row or a single column using an array formula. The example below shows the operation… read more »

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays. Generate Array with ROW or COLUMN Functions Array formulas often need to use “natural number” as parameters of the function, such as the… read more »

Excel Array Operation

We have all heard of arrays. Depending on the dimensionality, they are divided into one-dimensional arrays and two-dimensional arrays. Depending on the data type, they can also be divided into numeric arrays, logical arrays, and so on. The concatenation and operation of different arrays is an important part of excel formulas. Many people are still… read more »

Excel Array Matrix Operation

In Excel, we have a built-in function used to perform matrix operations. It is the MMULT function. This function accepts two arrays as parameters and returns the product of the two arrays. However, this function has a prerequisite that the number of rows and columns of the two arrays passed in are the same. MMULT… read more »

A1 Reference Style and R1C2 Reference in Excel

In Excel, there are two reference styles. A1 reference style and R1C2 reference style. These two reference styles can help us find the cell by the cell address. Some functions return these two addresses through the argument settings. A1 Reference Style In the Excel worksheet, any cell can use its column’s letter plus the row’s… read more »

Excel Structure Reference

This tutorial introduces the basics of structured referencing in Excel and shows you how to use structured referencing in a table with examples. When you use formulas that reference tables, whether the formula references a part of the table or the entire table, structured references make it easier and more intuitive for you to work… read more »

Excel/Google Sheets: Full Row Reference

Excel or Google sheets supports both full row references and full column references. This article will talk about the usage of full row references. Full Row reference is another cell reference in Excel or Google Sheets, which is used to reference the entire Row, for example, if you want to reference the entire Row 1,… read more »

Excel/Google Sheets: Full Column Reference

Full column reference is another cell reference in Excel or Google Sheets, which is used to reference the entire column, for example, if you want to reference the entire column A, you can use the following reference format A: A. Full column references Full column reference is composed of two column numbers plus a colon,… read more »

Excel/Google Sheets: Expanding Reference

An expanding reference is a reference type in Excel and Google worksheets that extends the range of a cell when a formula is copied to the cell below or to the right. In this article, we will explain how to use extended references in Microsoft Excel or Google worksheets with specific examples. Expanding reference Extended… read more »

Excel/Google Sheets: Mixed Reference

In Microsoft Excel Spreadsheet or Google sheets, there is another cell reference, mixed references, where part of the reference is absolute, part of the relative. This article will describe how to use mixed references through specific examples. Mixed Reference When the formula is copied to other cells, it is only used to maintain the absolute… read more »

Excel/Google Sheets: Absolute Reference

In Microsoft Excel spreadsheet or Google Sheets, the cell reference is the cell or cell area address or name in the worksheet, the cell reference contains two types of references, namely, relative references and absolute references. Absolute Reference An absolute reference in Excel or Google Sheets means that when copying a formula to another cell… read more »

Conditional formatting based on another column in Google Sheets or Excel

In Microsoft Excel Spreadsheet or google sheets, conditional formatting is used to highlight any cell based on a predefined condition and the value of those cells. In the previous article, we described how to format a specific column or cell range based on another cell value in Excel or google sheets. In Excel or Google… read more »

Conditional Formatting based on Another Cell in Google Sheets/Excel

In Microsoft Excel Spreadsheet or google sheets, when you want to format a specified cell or cell range based on the value of a different cell, for example, formatting the first row based on the value of a cell in a single column, you can use the conditional formatting function to create formatting formulas. This… read more »

Compare Two Strings in Excel/Google Sheets

This article will talk about how to compare two given strings in a Microsoft Excel spreadsheet or Google Sheets. How to compare two strings in Excel by using VBA macros to see if they are the same, if they are the same then return TRUE, if not then return FALSE. Suppose there is a worksheet… read more »

Compare effect of (non-annual) compounding periods on growth

This article will talk about how to calculate the trend or effect of future value changes for different compounding periods in a Microsoft Excel spreadsheet or Google Sheets via a formula. What is the period of compound interest? The compounding period is the time interval at which the bank calculates the interest rate on unpaid… read more »

Coefficient Of Variation in Excel/Google Sheet

This article will talk about how to calculate the coefficient of variation by formula in a Microsoft Excel spreadsheet or Google Sheets.  What is the Coefficient of Variation? The coefficient of variation is the ratio of the standard deviation of the original data to the mean of the original data. The coefficient of variation has… read more »

VLOOKUP Function & Formula in Excel

In our daily work, we often encounter the situation of finding data based on the original data and conditions provided, such as finding cell phone numbers based on name, finding employee names and positions based on HRID, etc. When we talk about the lookup function in excel workbook, most people will first think of using… read more »

Phone Number Format in Excel

Sometimes when we import phone numbers from external sources, the formatting of the phone numbers is removed. Even if in some situations the phone numbers are not imported from external resource, sometimes the phone numbers in worksheet are not displayed in the correct format, which is very common in daily work. When we are faced… read more »

Check Cell If Contains One of Many with Exclusions

In Microsoft Excel Spreadsheet or google sheets, when cells contain multiple strings, how can we accomplish this task if you want to check whether these cells contain more than one given string and exclude other given strings? In this article, we will discuss how to check if a cell contains more than one given string… read more »

If Cell Contain Specific Text

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of data or need to determine if a specific cell area contains text values. We may… read more »

Sidebar