Posts

7 great keyboard shortcuts for selecting cells quickly.

Syntax AND(Expression1, Expression2, Expression3, … ) Expression1 (required) – This is an expression that can evaluate to either TRUE or FALSE.Expression2, Expression3 etc… (optional) – These are additional expressions you want to test. Example In this example we test two expressions. As seen, the result is only true when both expressions evaluated are true.

Syntax COLUMN(Range) Range (optional) – This is a single cell or range which you want the column number from. For ranges the column number of the upper left most cell will be returned. If no range is selected, the column in which the formula appears is returned. Example In this example we get the column number based on a single cell reference, no cell reference and a reference to a range of cells....

Character count of the full text string. This is given by the LEN(Text) part of the formula.Character count of the text string with all the characters we’re trying to count removed. This is given by the LEN(SUBSTITUTE(Text,Character,””)) part of the formula. In our example LEN(“Apples and bananas”) results in 18 since there are 18 characters in the string. In our example SUBSTITUTE(“Apples and bananas”,”a”,””) results in the text string “Apples nd bnns”, where each occurrence of “a” has been removed....

Read this post about How To Use The VBA Code You Find Online to find out how to use this VBA code. When you have this code in your worksheet, you might also want an easy and obvious way for someone to use it like adding a button to run it. You can find out how to do that in this post about How To Add A Form Control Button To Run Your VBA Code....

If you’re unfamiliar with VBA, then it’s probably a good idea to read this post about How To Use The VBA Code You Find Online before continuing reading and working with the completed workbook. For this code you will need the Microsoft Outlook 16.0 Object Library enabled. In the visual basic editor, go to Tools then References and check the box next to it and press OK button to enable it....

MOD(Range,2)=1 will create an array of Boolean values where TRUE will mean the number is odd and FALSE will mean the number is not odd. We then multiply this Boolean array by the Range to get an array where each value is either an odd value from the Range or a 0. This is because NTRUE equals N and NFALSE equals 0 for any number N. In our example MOD({1....

Get The Template This is a straight forward billing invoice template with a bit of style. The template is designed to look like a ruled line note pad for a bit of fun. The invoice table includes a quantity (QTY) and Unit Price column to allow for a detailed breakdown of the billed amount. A Description for the billed items can be added to the Description tab and then can be easily added from a drop down list in the Invoice tab....

A barcode is a representation of data using parallel lines that vary in width and spacing. Barcodes are most commonly used for inventory management and point-of-sale systems to scan and track products. If you work with inventory or product data you might need to create scannable barcodes for the items you track. This post will show you how you can generate your own barcodes in Excel. Generate a Barcode with a Font Did you know that you can use install and use new fonts for your Excel spreadsheets?...

A random sample is a selection of records chosen from a larger population in such a way that each record has an equal chance of being chosen. The goal is to ensure that the sample reflects the population as accurately as possible. If any one group is over-sampled or under-sampled, then it can skew the results and lead to inaccurate conclusions. Random sampling is essential for statistical work because it helps to eliminate any bias....

Syntax CHOOSE(Index, Value1, Value2, Value3,…) Index (required) – This is the index value for the item you want to return from the list. The index number refers to the order of the list. 1 will return the 1st item in the list, 2 will return the 2nd item in the list and so on.Value1 (required) – This is the first item in the list.Value2, Value3 etc… (optional) – These are the other items in the list....

Syntax DATEVALUE(Text) Text (required) – This is the date stored as text you would like to convert to a serial number. Example In this example we convert a few different text dates to the serial number.

A User Defined Function To Check If A Sheet Exists Within The Current Workbook This is a simple VBA function that will return true if the current workbook contains a sheet with the exact name passed through the function and returns false otherwise. This function is not case sensitive so Sheet1 and SHEET1 are considered to be the same (sheet names in Excel are not case sensitive). Here the VBA is formatted as a user defined function....

Enable the summary statistics. Wow, the average salary for the Galactic Empire is $5,890,400, sign me up!

How can you get the Nth item from that list? One solution would be to use text to column and actually separate out each list into different columns then use an INDEX function on those column. The problem with this is in the process you’ve destroyed the original list and it’s now across many different columns. A simpler solution might be to create a user defined function in VBA for this....

In our example ISBLANK(Range) creates the following array. MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE},0) then looks for the first exact match to a FALSE value within this array. This returns the value 4 since the first FALSE value is in the 4th position and this is our first non blank cell is in the range.

This error is caused when you improperly implement the concept of spilling an array in your Excel formula. This post explores this spilling concept and discusses the fixes for errors associated with spilling formulas. What Does Spill Mean in Excel? Excel was introduced to spilling arrays with the release of Excel 365 in January 2020. The basics of spilling are that you enter a single formula in a cell and the results are returned in multiple cells!...

Syntax XOR(Expression1, Expression2, Expression3, … ) Expression1 (required) – This is an expression that can evaluate to either TRUE or FALSE.Expression2, Expression3 etc… (optional) – These are additional expressions you want to test. Example In this example we test two expressions. As seen, the result is only true when exactly one of the expressions evaluated is true.

If you’re going to be using VBA or recording macros in Excel then you’re going to want to add the Developer tab to your ribbon. Excel’s Developer tab is hidden by default and you will need to go into the options to enable it. The Developer tab will allow easy access to various features not otherwise accessible. Open the visual basic editor (VBE).Record macros.Run your recorded macros and VBA modules....

Example Generic Formula What It Does This formula will take the average of a numerical range then return the count of the items in the range that are less than the average. How It Works AVERAGE(Numbers) will return the arithmetic mean or average of the Numbers. In our example this is (13+5+7+9+20+3+4+6)/8 = 8.4. “<"&8.4 then creates the text string “<8.4" which is used as a condition in our COUNTIF function....