Copy data from workbooks in folder and subfolders
I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by […]
Extract unique distinct records from two data sets
The picture above shows an array formula in cell B9:C13 that extracts unique distinct records from two tables in cell […]
How to position month and year between chart tick marks
The picture above shows a line chart with month and year labels between tick marks instead of date values below […]
How to copy non contiguous cell ranges
If you try to copy multiple cell ranges on a worksheet that don't have the same number of rows or […]
Search for a file in folder and subfolders (VBA)
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
How to use the NOMINAL function
The NOMINAL function calculates the nominal annual interest rate based on the effective rate and the number of compounding periods […]
The MUNIT function calculates the identity matrix for a given dimension. Array formula in cell B2: =MUNIT(3) The formula above returns an […]
The RAND function calculates a random real number greater than or equal to 0 and less than 1. The function […]
How to use the MULTINOMIAL function
The MULTINOMIAL function calculates the ratio of the factorial of a sum of values to the product of factorials. Formula […]
How to use the MINIFS function
The MINIFS function calculates the smallest value based on a given set of criteria. Formula in cell E3: =MINIFS(C3:C10,B3:B10,"A") The […]
The MINA function returns the smallest number. Text values and blanks are ignored, boolean value TRUE evaluates to 1 and FALSE […]
The easiest way to check if a cell has a value is, in my opinion, to use the equal sign […]
How to use the MINVERSE function
The MINVERSE function calculates the inverse matrix for a given array. This function is useful for solving equations with multiple variables. […]
Count a given pattern in a cell value
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
How to create a stacked column chart
The stacked column chart is great for comparing parts of a whole and how they change over time or categories. […]
To be able to resize a chart you must first select it, you do that by left-click on the chart with […]
How to use the NORM.INV function
The NORM.INV function calculates the inverse of the normal cumulative distribution for a given mean and standard deviation. Formula in […]
How to use the SPLIT function [VBA]
The picture above shows a user-defined function (UDF) that splits the string in cell B3 using delimiting character "|". The SPLIT function […]
Most Excel charts consist of an x-axis and a y-axis, Excel allows you to easily change the looks of […]
How to graph a Normal Distribution
The chart above is built using the NORM.DIST function and is called Normal Distribution or Bell Curve chart. This curve is often […]
How to use the NORM.DIST function
The NORM.DIST function calculates the normal distribution for a given mean and standard deviation. Formula in cell C7: =NORM.DIST(C2,C3,C4,C5) Excel […]
How to use the WEBSERVICE function
The WEBSERVICE function gets data from a web service on the Internet or Intranet. The function returns a #VALUE error […]
Extract k-th word in cell value
The formula displayed above in cell range D3:D9 extracts a word based its position in a cell value. For example, […]
The clustered column chart allows you to graph data in vertical bars, this layout makes it easy to compare values […]
How to align a chart with the cell grid
This trick is so simple and also an incredible time-saver if you want to build beautiful worksheets or dashboards where […]
The picture above shows different values in column B and a formula in column C that tries to identifies the […]
I highly recommend you keep the original data and only copy the data excluding blank rows and paste to a […]
The VAR.S function tries to estimate the variance based on a sample of the population. The function ignores logical and […]
The picture above shows data in column B, some cells contain nothing, they are blank. I will now go through […]
The VAR.P function returns the variance based on the entire population. The function ignores logical and text values. Variance shows […]
In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]
The DVARP function returns the variance of an entire population. The numbers are in a column of records in a dataset […]
How to quickly select blank cells
In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of […]
How to add a macro to your Excel Quick Access Toolbar
The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your […]
How to quickly select a cell range
Selecting cell ranges in Excel can sometimes be a real pain scrolling forever it seems. There is a quick and easy […]
Highlight cells based on coordinates
The picture above shows Conditional Formatting highlighting cells in cell range F3:Y22 based on row and column values in column B […]
The DSUM function adds numbers in a database that meets a condition or criteria. Formula in cell B15: =DSUM(B5:D12, 3, B2:D3) […]
How to use the DSTDEVP function
The DSTDEVP function calculates the standard deviation based on a population. The function also allows you to specify criteria applied […]
How to use the DSTDEV function
The DSTDEV function calculates an estimation of the standard deviation based on a sample of a population. The function also […]
How to use the DPRODUCT function
The PRODUCT function multiplies numbers that match a condition or criteria in a database. Formula in cell B15: =PRODUCT (B6:D12, 3, […]
The DMIN function extracts the smallest number from a column in a database whose records match a condition or criteria. […]
The DMAX function extracts the maximum number from a column in a database whose records match a condition or criteria. […]
The DGET function fetches a value from a column in a database whose records meet a condition or criteria. Formula […]
How to use the DCOUNTA function
The DCOUNTA function counts nonempty cells in a column you specify, in a database where records also meet a condition […]
How to use the DCOUNT function
The DCOUNT function counts cells containing numbers and that meet a condition or criteria. Formula in cell D3: =DCOUNT(B6:D12,3,B2:C3) The […]
How to use the DAVERAGE function
The DAVERAGE function calculates an average based on values in a list or database that meet specific conditions. Formula in […]
How to use the FORECAST.LINEAR function
The FORECAST.LINEAR function calculates a value based on existing x and y values using linear regression. Use this function to […]
The following formula in cell D3 counts cells with values stored as text. =SUMPRODUCT(ISTEXT(B3:B14)*1) In other words, cells containing nothing, errors, […]
The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. […]
How to use the BITXOR function
The BITXOR function calculates a decimal number that is a result of a bitwise comparison "XOR" of two numbers. XOR […]
Welcome! I am 