Welcome to my Excel Formula Section. Here you will find brief articles for Excel that use Formula or help with Cell formatting. You can read about any updates to this section or follow my work on Facebook. If you have used any of my other work and wish to donate, you can do so on my Donate Page

Use the Quick Search to find an article of choice. You can also Sort the list either ascending or descending by Name, or by the links that have a File available to download:







Custom Formats that Hide Zeros and Align Cell Content

[Back to Search]

Here are some different Custom Formats to Format and align numbers with zeros. To use these Custom Formats, right-click->Format Cells and then add one of the Formats below as a Custom Cell Format. You can also use these for Custom Formatting in Pivot Tables where you don't want to display zeros

' // display zero's as wide line, positive & negative numbers (right align by default)
#;-#;"—"

' // alternative display zero's as wide line, positive & negative numbers (right align by default)
0;-0;—;@

' // display zero's as wide line, positive & negative percentages (%) to 1 decimal place (right align by default)
0%;-0%;"—"

' // alternative display zero's as wide line, positive & negative percentages (%) to 1 decimal place (right align by default)
0.0%;-0.0%;—;@

' // display zero's, thousands with comma, positive & negative numbers, right aligns without cell alignment
#,##0;-#,##0;"—"

' // display zero's, thousands with comma, positive & negative numbers, left align numbers & hyphens (wide line)
'     requires you to also left align cells for when you enter zero.  the character following the '*' character will be used to pad out the Cell
#,##0* ;-#,##0* ;"—"

' // hide zero's for number or the return value from a Formula that equals zero but leave Text unchanged
0;;;@
 





Custom Format a Percentage with a +/- prefix and Highlight it Red

[Back to Search]

To display Percentages (%) with +/- & Red Colour Highlighting for negative Percentages you can use one of the following Custom Formats:

' // add to C1 with values in A1 & B1
=IF(AND(A1=0,B1>0),1,IF(A1,(B1-A1)/A1,0))

' // add this Custom Format to the Cell C1
+0.0%;[Red]-0.0%;0.0%
 





Lookup the Last Non-blank Value in a Row & Return the Header

[Back to Search]

To lookup the Last Non-blank Value in a Row and Return the Header (for example a Week Number), assuming that you have the data layout as per below, you can use the following Formula entered into Cell "G2" (you must ensure that your Range does not contain any 0 (zero values) so Find & Replace them with blanks). If using BEx for reporting, you can display zeros as Blank:
Headers are in Columns C1:F1
Data to scan is in Rows C2:F4

=LOOKUP(9.999999999E+307,C2:F2,$C$1:$F$1)

' // capture #N/A Error, for example, if you had no Sales data you could display "Never Sold"
=IFERROR(LOOKUP(9.999999999E+307,C2:F2,$C$1:$F$1),"Never Sold")
 





Lookup Numbers Stored As Text in Vlookup, Index or Match

[Back to Search]

To lookup Numbers that may be stored as Text (there are many examples where data pulled from BEx, BI Reports & SAP extracts leave Numbers stored like this) you can convert the Cells to Numbers or you can use the following methods if you don't want to convert them every time you extract data:

' // when you know the width of the Number ie. it will always be 8 digits, you can pad to 8 digits like this
=VLOOKUP(Text(A1,"00000000"), ...

' // coerse to Integer sometimes works
=VLOOKUP(Int(A1), ...

' // I prefer this one as it does not matter what length the Number may be
=VLOOKUP(A1&"", ...

' // another method is to use a number format like this
VLOOKUP(TEXT($B9,"00#"), ...

' // use this when using an Index, Match Formula on data stored as Text when trying to find a Number
=INDEX(C:C,MATCH(A1&"",B:B,0))

 
You can also use the Data->Text To Columns and press Finish to remove prefixed 0 (zeros), Numbers stored as Text and Numbers preceded with an apostrophe. In fact you can use this to split values with formatting (for example, '24 PC') into 2 Columns or even split just the value back into the same Column





Sum Numbers Stored As Text or with a Custom Cell Format containing '—' (dash)

[Back to Search]

To SUM Numbers that may be stored as Text (assuming your Range to SUM is 'A1:C1') you can use an Array Formula with double negative signs like this (the curly braces will appear when you press CTRL+SHIFT+ENTER):

' // enter this in D1
=SUM(--(A1:C1))

' // this will look like the following in the Cell when you press CTRL+SHIFT+ENTER
{=SUM(--(A1:C1))}
 
Another method to SUM Numbers where a Range has a '—' (dash) in it (assuming your Range to SUM is 'A1:C1') and you are using the Custom Format #,##0;-#,##0;"—", is to use an Array Formula together with the SUBSTITUTE Function like this:
' // enter this in D1
=SUM(--SUBSTITUTE(A1:C1,"—",0))

' // this will look like the following in the Cell when you press CTRL+SHIFT+ENTER
{=SUM(--SUBSTITUTE(A1:C1,"—",0))}
 
It works by substituting all occurances of '—' with zero (0) so you get {"4,321","0","997"} in the Array instead of {"4,321","—","997"}.





Get the ISO Week Number from a Date

[Back to Search]

To lookup an ISO Week Number using a Calendar Day Date use the following Formula:

' // enter a Date into Cell "A1"
30/03/2015

' // enter this formula into Cell "A2"
="ISO Week "&INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7)

' // result of the formula
ISO Week 14
 





Multiple 3 Column Lookup to Return Single or Concatenated 2 Column Values

[Back to Search]

To lookup the Gender from 3 Columns of data Name, Age & Gender Columns "A2:C5" with "A1", "B1" and "C1" being the Headers, "E2:E3" being the 2 Search Criteria, you can use the following Array Formula (SHIFT+CTRL+ENTER) in "F2":

=INDEX(C2:C5,MATCH(E2&E3,(A2:A5)&(B2:B5),0),1)
 
Alternatively download the Example Multiple Lookups Using Array Formula.xlsx which features 2 Examples of using Multiple Values to return data





Count Unique Values in a Large Range using a Defined Name

[Back to Search]

To count the unique values in a large Range I find this is one of the quickest Formula - remember to enter the Formula as an Array Formula (SHIFT+CTRL+ENTER). The keyword Range should be a Range i.e. "A6:A5000" stored as a Defined Name (or Named Range) and should be replaced when you enter it. For very large Ranges or entire Columns you should use VBA

' // you need a Defined Name called Range.  remember to enter this as an Array Formula by pressing CTRL+SHIFT+ENTER
=SUM(IF(FREQUENCY(IF(range<>"",MATCH(range,range,0)),ROW(range)-MIN(ROW(range))),1))
 





Convert Numbers Stored as Text when using Formula

[Back to Search]

There are a few ways to convert Numbers stored as Text using Formula. You can use =VALUE() or =NUMBERVALUE() for floating point Numbers. You can even use =INT() if you just have whole Numbers or =T() to retain formatting. If you have a Number stored as Text in Cell A1 use any of the following:

' // converts a Number stored as Text into a Number
=VALUE(A1)

' // whole Number
=INT(A1)

' // Converts text to a number, in a locale-independent way (new for Excel 2013)
=NUMBERVALUE(A1)

' // if you have a Number stored as Text like 1,200 this will retain its comma formatting
=T(A1)
 





Dynamic Pivot Table Data Sources from a Defined Name or Named Range

[Back to Search]

Using a dynamic Defined Name or Named Range as a data source for a Pivot Table enables you to Paste in any size of data and then refresh the Pivot Table without re-defining the data source Range for the Pivot Table. Providing the same Headers are included the Pivot Table can be refreshed for the new Range. If you include additional Columns with new Headers the Pivot Table will accept these as well

I make the assumption that you already have some source data and a Pivot Table linked to the Range. So, in the Worksheet containing your Pivot Table source data with Headers, create a Defined Name called Data using the Ribbon Tab "Formula" and the Name Manager. Then add the following Formula:

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
 
Click OK. Close the Name Manager. Re-open the Name Manager and you will see that the Defined Name has taken the name of the Worksheet in its Formula:
=OFFSET(data!$A$1,0,0,COUNTA(data!$A:$A),COUNTA(data!$1:$1))
 
Now simply use the Defined Name "Data" as the Range for the Pivot Table. Click on the Pivot Table. On the Ribbon in "PIVOTTABLETOOLS" context Menu, click on "ANALYZE" Group and then select the "Change Data Source" Control. Underneath the Heading "Choose the data that you want to analyze" and for the Radio Button option "Select a Table or range" enter the following:
=Data
 
Click OK. Now right-click and refresh your Pivot Table. No changes will occur. Now Paste in a new Range to include the existing Headers but either longer Rows or additional Columns and right-click refresh again on your Pivot Table. The Pivot Table data source will have expanded and the Pivot Table will include the new data





Dynamic Named Range or Defined Name to Capture a Range using Offset when zeros (0) are included or to Capture a List of Text Names

[Back to Search]

Here is an example of using a dynamic Named Range to capture a Range when zeros (0) are included. It always captures the last Row down of the last Numeric Cell entry NOT including zero (0). The Cells in the Range are also formatted with a '—'. The Range starts in Cell 'D7' on Sheet1. Try adjusting the Percentages to zero - when you check the Defined Name you will see that it still captures the Range correctly, including any Blank Columns if Columns after the Blank Column have values

=OFFSET(Sheet1!$D$7,0,0,COUNTA(Sheet1!$D$7:$D$99988),LOOKUP(9.99E+307,1/(Sheet1!$D$7:$O$7),COLUMN(Sheet1!$D$7:$O$7)-3))
 
Dynamic Named Ranges.xlsx

Here is a Formula that you can use to capture a list of names. The beauty of this Formula is that it ignores Blank Names but still captures the entire list of Text. Enter data in Column "B", your Header should be in Cell "B5" and then the list of Names should be entered below the Header

=OFFSET(Summary!$B$6,0,0,MATCH("*",Summary!$B:$B,-1)-5, 1)
 
Here is a Formula that you can use to capture a list of Numeric data including Blank Cells - I use this in my Holiday Planner
=OFFSET(Summary!$B$6,0,0,MATCH(9.99999999999999E+307,Summary!$B:$B,1)-5, 1)
 





Top Ten Ranking List using a Formula

[Back to Search]

Here is an example of how to implement a Top Ten ranking linst for Supplier Number and Supplier Name together with the Percentage that they achieved. You can choose how many rankings you wish to view. If the percentages are the same they will still be given an order of ranking

The Formula used to create the ranking is shown below:

=C1+10^-6*ROWS($A1:A$1)
 
Top Ten List.xlsx





R1C1 Locking Formula in VBA

[Back to Search]

Here is an example of how to implement R1C1 Lock Formula in VBA for an Excel Formula. Locking Formula when using R1C1 (normally used after recording a Macro to do something in Excel) can be a little tricky especially understanding when it is absolute or relative and then trying to adjust the Code to Lock a Row or a Column when your Code is trying to Copy & Paste or just Copy down the Formula

R1C1 Lock Formula.xlsm





Repeating Values in One Column (n) Times in Another Column Formula

[Back to Search]

Here is a method to repeat the values in Column 'A', (n) number of times in Column 'B' and output the results in Column 'C'. The Formula is an Array Formula requiring you to press CTRL+SHIFT+ENTER:

So with a list in Column A and the respective 'X' times to repeat the elements of the list in Column 'B', enter the following Array Formula into Column 'C'. You will need to extend the Ranges if you want to add more Products to the list
Now when you type 4 in Column B for 'Product A', 'Product A' will be repeated 4 times in Column C

' // enter the following array formula in Cell C2
=IFERROR(INDEX($A$2:$A$5,MATCH(TRUE,MMULT(--(ROW($A$2:$A$5)>=TRANSPOSE(ROW($A$2:$A$5))),$B$2:$B$5)>=ROWS($1:1),0)),"")

' // this will look like the following in the Cell when you press CTRL+SHIFT+ENTER
{=IFERROR(INDEX($A$2:$A$5,MATCH(TRUE,MMULT(--(ROW($A$2:$A$5)>=TRANSPOSE(ROW($A$2:$A$5))),$B$2:$B$5)>=ROWS($1:1),0)),"")}
 
Repeat Value X Times.xlsx





Create a Unique List of Text Items from a List Formula (Case Insensitive)

[Back to Search]

Here is an example of how to create a unique list of text items in Column 'B' from a list of multiple items in Column 'A' using Excel Formula

Enter some text items in Column 'A' including a Header (I used Product 1, Product 2, etc.) and then Copy & Paste the Array Formula below into Cell 'B2' and drag down. You will see a unique list built from the items that you entered in Column 'A'. The result of your list should also be Sorted ascending. Please note it is Case Insensitive, so 'a' is treated the same as 'A' and therefore will only be repeated once.

' // enter this array formula in Cell B2 for a list of Items in Column A.  drag the Formula down to create the unique list
=IFERROR(INDEX($A$2:$A$20,MATCH(SUM(COUNTIF($A$2:$A$20,B$1:B1)),COUNTIF($A$2:$A$20,"<" &$A$2:$A$20),0)),"")

' // this will look like the following in the Cell when you press CTRL+SHIFT+ENTER
{=IFERROR(INDEX($A$2:$A$20,MATCH(SUM(COUNTIF($A$2:$A$20,B$1:B1)),COUNTIF($A$2:$A$20,"<" &$A$2:$A$20),0)),"")}
 
Unique List Formula.xlsx





SUM Columns by Column Criteria & Row Criteria (using SUM, IF, INDEX & MATCH)

[Back to Search]

Here are two Formula to SUM across Columns by some Criteria and to SUM across Columns matching Criteria whilst also matching Criteria in a Row

A list of Products are detailed in Column 'A' together with repeated Column Headers, "A, B, A, D, D". Select a Column Header in Cell 'B9' from the first Drop-down to SUM that Column Header. Select a Column Header in Cell 'B14' from the first Drop-down and a Product from the second Drop-down in Cell 'B15' to SUM the Products across all repeated Columns for the Column Header. You can modify these Formula to suit your own needs

' // formula for example 1. enter
=SUM(IF($B$1:$F$1=$B$9,$B$2:$F$4))

' // this will look like the following in the Cell when you press CTRL+SHIFT+ENTER
{=SUM(IF($B$1:$F$1=$B$9,$B$2:$F$4))}

' // formula for example 2. enter
=SUM(INDEX(IF($B$1:$F$1=$B$14,$B$2:$F$4),MATCH($B$15,$A$2:$A$4,0)))

' // this will look like the following in the Cell when you press CTRL+SHIFT+ENTER
{=SUM(INDEX(IF($B$1:$F$1=$B$14,$B$2:$F$4),MATCH($B$15,$A$2:$A$4,0)))}
 
SUM Columns by Column Criteria & Row Criteria.xlsx





Reverse a List using Formula

[Back to Search]

Here is a quick way of reversing a List using Formula. For a list in Cells "A1:A5", enter the following in Cell "B1" and drag down to "B5". You must have items all of the way down from "A1:A5" otherwise the Formula will places zeros for empty Cells. The list will now be reversed

' // for a list of items in Cells "A1:A5" enter this formula in Cell "B1" & drag down to reverse the list
=INDEX($A$1:$A$5,6-ROWS($A$1:A1))
 





Sort a Text List Alphabetically using Formula

[Back to Search]

Here is a quick way to Sort a Text List Alphabetically. For a list in Cells B2:B5, enter the following =COUNTIF in Cell "A2" and drag down to "A5". Then enter the =VLOOKUP to sort the Items

' // for a list in Cells "B2:B5" enter this formula in Cell "A2"
=COUNTIF($B$2:$B$5,"<="&B2)

' // for a list in Cells "B2:B5" enter this formula in Cell "C2"
=VLOOKUP(ROW()-ROW($B$1),$A$2:$B$5,2,FALSE)
 





Colour Banding Row Formula (starting at Row Y & Banding every Y Rows or by Criteria) or using a Rollover

[Back to Search]

Here are a couple of quick ways to apply a Formula to Colour Band your report data starting at Row Y & Banding every Y Rows or by Criteria

To Colour Band report Data every other Row using Formula, select the entire data Range in your Excel Workbook that you want to Colour Band and add a Conditional Format from the "Styles" Group on the "Home" Tab (Manage Rules->New Rule->Use a formula to determine which cells to format) using the Formula below (I selected "B6:D105"). Change the X in the Formula to the Row Down number that the Selected Range begins (I chose to enter 7 so that the Colour Banding was applied after the first Row within my Selected Range). Change the Y in the Formula to the number of Rows you want to highlight (so for every other Row change it to the number 2). Choose your desired Fill Formatting


' // Example 1, Formula for the example above
=MOD(ROW()-7,1*2)+1<=1
 
' // Formula to copy - modify the X & Y for your own selected report Range
=MOD(ROW()-X,1*Y)+1<=1

' // Example 2, Formula to start at Row 10 and Colour Band every 5 Rows 
=MOD(ROW()-10,1*5)+1<=1
 
To Colour Band Report Data by Criteria as Formula, enter some Names in a Blank Worksheet from Cell "C6" downwards. Include the Surname "Mark" in Cell "C11" and Cell "C13". Select the entire Range of entered data and add a Conditional Format from the "Styles" Group on the "Home" Tab (Manage Rules->New Rule->Use a formula to determine which cells to format) using the Formula below. Click Format and select a Fill Colour. Then Click OK and then Apply. The two Cells with the Text "Mark" will be Colour Banded.
=$C6="Mark"
 
Colour-Banding.xls

You can download an example file for Excel 2007/2010 by Bert van Zandbergen to see a Rollover technique in action:
Colour Banding with Hyperlink Rollover.xlsm





Removing Whitespace (extra spaces) from Cells

[Back to Search]

Here are a couple of ways to remove whitespace from Cells (extra spaces) using Trim() and Substitute(). Copy or enter the following into Cell "A1" without the quotes: " 5 600 ". Example 1 will remove the leading & trailing whitespace. Example 2 will remove all whitespace. Example 3 will remove all whitespace and format the Cell to a number using the Text() function (Please note: you will then still have to Convert the Cell to a number using the little drop-down arrow)

' // Example 1 - remove the leading & trailing whitespace
=TRIM(A1)

' // Example 2 - remove all whitespace
=SUBSTITUTE(A1," ","")

' // Example 3 - remove all whitespace and format the Cell to a number
=TEXT(SUBSTITUTE(A1," ",""),"#,##0")
 





SUMIFS Accross Columns of Data to Exclude some Criteria

[Back to Search]

Here are a couple of ways to SUMIF across Columns of data to exclude some Criteria - you need to download the accompanying File to see the Formula in action. 4 Columns of data from "A1:D7" including 'Product', 'Product Description', 'Depot' and 'Stock' are used for the Examples

' // Example 1 - SUMIF Column 'D' but exclude Products containing the letter 'C' in Column 'A'
=SUMIFS($D$2:$D$7,$A$2:$A$7,"<>*C*")

' // Example 2 - SUMIF Column 'D' but exclude Products containing the letter 'C' in Column 'A' & Depots including 'Depot A' in Column 'C'
=SUMIFS($D$2:$D$7,$A$2:$A$7,"<>*C*",$C$2:$C$7,"<>Depot A")
 
SUMIFS to Exclude Criteria.xlsx





Get the Number of Days in a specific Month from a Date

[Back to Search]

Here are a couple of ways to get the number of days in a Month given a Date in Cell "A1":

' // Example 1 - add Formula to Cell "A2"
=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)

' // Example 2 - add Formula to Cell "A2"
=DAY(EOMONTH(A1,0))
 





Calculate the Date Difference Between Two Dates ie. Find out Your Age

[Back to Search]

Here are a couple of ways to calculate the Date Difference between two Dates and will calculate your age in years and then Years, Months & Days:

' // Example 1 - Years
=DATEDIF("25-Jul-1968", TODAY(),"Y")

' // Years... for a Date in Cell "A1"
=DATEDIF(A1, TODAY(),"Y")

' // Example 2 - Years, Months & Days
=DATEDIF("25-Jul-1968", TODAY(),"Y") & " Years " & DATEDIF("25-Jul-1968", TODAY(),"YM") & " Months " & DATEDIF("25-Jul-1968", TODAY(),"MD") & " Days"

' // Years, Months & Days... for a Date in Cell "A1"
=DATEDIF(A1, TODAY(),"Y") & " Years " & DATEDIF(A1, TODAY(),"YM") & " Months " & DATEDIF(A1, TODAY(),"MD") & " Days"
 





Backwards VLOOKUP Using CHOOSE

[Back to Search]

Here is a way to perform a backwards VLOOKUP using the CHOOSE Function. Consider a list of Products in Cells "A2:A5" (Product A, Product B... etc.) and a list of Depots in Cells "B2:B5" (Depot A, Depot B... etc.) then a VLOOKUP on the first occurance of "Depot B" will return "Product B" using the formula below:

' // Example 
=VLOOKUP("Depot B",CHOOSE({1,2},$B$2:$B$5,$A$2:$A$5),2,0)
 
Backwards VLOOKUP using CHOOSE.xlsx





SUMPRODUCT in a Pivot Table containing Zeros or Zero's Formatted as '—' (dash) by some Criteria

[Back to Search]

Here is a way to SUM by Criteria in a Pivot Table which also has a Custom Cell Format: #,##0;-#,##0;"—". The Range for the Criteria is D27:D1002 and the Range to SUM is E27:BE1002 in the Pivot Table - this is actually outside of the Pivot Table data being displayed but the great thing about Pivots is that they expand and contract the Range allowing me to use this Fornula to capture any horizon of Final Forecast and Total Sales ie. 1 Week or 16 Weeks

' // Example 
=SUMPRODUCT(($D$27:$D$1002="Final Forecast")*$E$27:$BE$1002)
=SUMPRODUCT(($D$27:$D$1002="Total Sales")*$E$27:$BE$1002)
 





SUM Values in a Column until the Next Empty Cell & a method to SUM current Sales data against last years Sales data for the same periods up until the last current Sales entry (ie. Year on Year - YoY Comparison for Same Months)

[Back to Search]

Here is a way to SUM a Column until you hit the first Blank Cell. With a Header in "B1" and data from "B2" until "B13" add the following Array Formula in Cell "B14" remembering to press CTRL+ENTER:

' // Formula
=SUM(B2:INDEX(B2:B13,MATCH(TRUE,(B2:B13=""),0)))

' // this will look like the following in the Cell when you press CTRL+SHIFT+ENTER
{=SUM(B2:INDEX(B2:B13,MATCH(TRUE,(B2:B13=""),0)))}
 
Year on Year - YoY Comparison for Same Periods
Here is a method to SUM current Sales data against last years Sales data for the same periods up until the last current Sales entry, open and view the Formula in the following Workbook: SUM Values in Columns until a Blank Cell.xlsx





MOD Patterns - Incrementing Numbers to use in INDEX to return 1st, 2nd, 3rd Values for Matching or to Create Repeated Patterns

[Back to Search]

Here is a way to increment a MOD pattern by a variable to create a positive varying pattern. This can be used in the INDEX() function to return a specific value in a list. Here is the Formula - Copy & Paste this into a Cell below "A1" and replace "X" with a number like 1,2,3 etc. or use a Cell or Defined Name that changes to create your pattern - drag the Cell down to create the MOD pattern.

' // replace 'X' with a number of your choice and drag the Cell down
=MOD(ROWS(A$1:A1)-1,X)+1

' // create a pattern like 1,2,3,4 by entering the Formula below and dragging it down
=MOD(ROWS(A$1:A1)-1,4)+1
 
If you replace "X" with 2, you can use it to return (or repeatedly return) the 1st and then 2nd item in a list or Range ie. to return the 1st and 2nd item in the Range "$A$1:$A$4" you can use the following Formula:
' // build a pattern by continually returning the first and second Item in a list in the Range "A1:A4"
=INDEX($A$1:$A$4,MOD(ROWS(A$1:A1)-1,2)+1)
 
The above Formula will make the pattern repeat for the length of Rows that you drag it down ie. if the Formula returned "Fish" and "Milk", then dragging it down a number of Rows will give you "Fish","Milk","Fish","Milk", etc.





MOD Patterns - SUM Numbers Every nth Row

[Back to Search]

This Formula will SUM the Cells from a Range every 3rd Row (adjustable by changing the 3):

' // replace '3' with a number of your choice.  press CTRL+SHIFT+ENTER to enter the Formula with data in the Range "A1:A9" 
=SUM(A1:A9*(MOD(ROW(A1:A9),3)=0))
 
Here is a File detailing way to SUM numbers every nth Row - the Formula in Column B just demonstates the way MOD works every 3rd Row. SUM Numbers Every nth Row.xlsx





How to Quickly Multiply lots of Cells by a Number like 100

[Back to Search]

Here is a way to Multiple lots of Cells by a Number like 100:

1. Type 100 in a blank Cell
2. Copy the Cell
3. Select the Cells that you want to Multiply by 100
4. Right-click, Paste Special, Operation Multiply
5. Remove the original 100 Number from the blank Cell

You can do the same for Addition or Subtraction and use a different Number in the Cell that you Copy





How to Quickly Enter the same Value or Character to lots of Cells like 'a'

[Back to Search]

Here is a way to add the same Value or Character to lots of Cells at the same time:

1. Highlight the Range of Cells that you want to add the Value or Character into
2. Type the Value or Character ie. a
3. Press CTRL+ENTER (Control plus the Enter Button) at the same time





How to Prevent or Remove the word (Blank) in Pivot Tables

[Back to Search]

I have read a lot on this Subject and can't help thinking everybody is tackling it the wrong way! To prevent the word (Blank) from being in your Pivot Table simply add the character (') without the Brackets to any Blank Cells in your SOURCE DATA. That's all there is to it, refresh your Pivot Table and all of the (Blanks) will vanish





How to Make a Proper String - Capitalise (Upper) First Character of each Word & Lowercase all other Characters

[Back to Search]

To Capitalise or make the first character of each Word uppercase you can use the PROPER Function. With the String "the cat sat oN the mAT" in Cell "A1" use the following Formula in "A2":

' // this Formula will Capitalise a String in Cell "A1"
=PROPER(A1)
 
The result will be "The Cat Sat On The Mat"





How to To Insert Special Characters or Symbols in a Text String

[Back to Search]

The Formula below inserts currency symbols, using the CHAR function:

="Exchange Rates Chart (" & CHAR(163) & "), Euro (" & CHAR(128) & ") and Dollar (" & (CHAR(36) & ")")
 
The result will be "Exchange Rates Chart (£), Euro(€) and Dollar ($)"





SUM or AVERAGE a Dynamic Range from End of Row Range Backwards X Columns Wide

[Back to Search]

The Formula below can be used where you have a Row of Data (starting H9 through to BH9 - data may not be up to the last Cell in this Range but this will be the maximum that it ever will be) and you want to SUM or AVERAGE the last 12 Cells of data from the end of the Row data backwards - if the 12 is substituted for a selected number 1-X, then your Formula will become dynamic, counting backwards 12 Columns and allowing the calculation to be 12 Columns wide:

' // SUM Formula, last Column of data, 12 Columns Back
=SUM(OFFSET($H9,,COUNTA($H9:BH9)-12,,12))

' // Average Formula, last Column of data, 12 Columns Back
=AVERAGE(OFFSET($H9,,COUNTA($H9:BH9)-12,,12))
 





How to Derive the Last X Weeks Average from Weekly Columns of values

[Back to Search]

The Formula below can be used where you want to get the Average dynamically. Suppose you have a report with data in weeks across the page and you want to specify the week to look at and get the last 4, 8 or 12 Weeks Average from that week - this Formula will do just that:

' // where H9:BH9 are the weeks, H6:BH6 are the actual numbers of the weeks, where F5 is the current week, F6 is the number of weeks back to Average (see the File below for an Example)
=AVERAGEIFS(H9:BH9,H$6:BH$6,">"&$F$5-$F$6,H$6:BH$6,"<="&$F$5)
 
File: Average of X Weeks from Weekly Columns.xlsx





Identify Duplicates in a List using Formula

[Back to Search]

The Formula below can be used to identify duplicates in a list. Then you can simply Filter the lists to view the duplicates, Copy and Paste them out or Delete them from the list. If you want to find the duplicates across 2 Columns (ie. Product & Depot duplicates), concatenate the Column values into another Column and then use the same Formula on the concatenated Column:

' // where the list starts in A6 add this Formula to B6 - drag down for as many items in your list
=IF(COUNTIF($A$6:$A6, A6)=1,0,1)
 
File: Identify Duplicates in a List using Formula.xlsx





How to Find & Replace Negative Floating Point Numbers

[Back to Search]

The Pattern below can be used to Find & Replace Negative Floating Point Numbers in Excel. Simply press CTRL+F and using Replace enter '-*' or '-*.*' without the apostrophe's and what you want to replace the contents with ie. leave Blank for Nothing. You can also press the 'Find All' Button after using CRTL+F to display all of the negative values found and their Cell references):

' // find all negative floating point numbers (locates the largest numbers first if you press Find All after CTRL+F)
-*

' // find all negative floating point numbers alternative (locates the smallest numbers first if you press Find All after CTRL+F)
-*.*

' // to find values like -0.01 you could use
-*.0*
 





How to Get the Sheet Name from Tab using a Formula

[Back to Search]

Method 1: To get the Sheet Name from a Tab using a Formula you can use the following (Please note: the Workbook must be saved first):

 
=RIGHT(CELL("Filename",A1),LEN(CELL("Filename",A1))-FIND("]",CELL("Filename",A1)))
 
Method 2: My preferred method which allows you to switch Workbooks without getting a #Value error:
 
' // add the Formula below to Cell "A1"
=CELL("filename",A1)

' // add the Formula below to Cell "A2"
="'"&MID(A1,FIND("]",A1)+1,256)&"'!"
 
' // RESULT for a Workbook called Dynamic Chart.xlsx and a Worksheet Tab called Dynamic Chart in A1:
C:\Archive\- a_My Programming\- a_My Excel\Working\Dynamic Chart\[Dynamic Chart.xlsx]Dynamic Chart
' // RESULT for a Tab called Dynamic Chart in A2:
'Dynamic Chart'!





How to Reference BEx Values where only a Fuzzy Asterix is Displayed for the Value

[Back to Search]

Sometimes when bringing in BI Data, values will only display as an Asterix '*'. This is because of the way that BEx aggregates values across different Units of Measure / Pack Types etc. It cannot display 'HL' or 'PC' if Products are different it displays a default Asterix. This makes it difficult to SUM or even reference the values. You can remove the Formatting in BEx and all will work well, however you can also use 'TEXT' or '&0' to SUM or reference the values.

To reference a value and coerse it into a Custom Format like '1,116.615 HL' you can use the following:

 
' // the Formula
=TEXT(A1,"#,##0.000 ""HL""")

' // the Custom Format used in the Formula above
#,##0.000 "HL"
 
You can also use the following to SUM data - bear in mind it will not have an underlying Format and will be up to 14 significant places:
 
=SUM(A1:C3)&0
 





How to Reverse or Sort a Column of Data that Cannot usually be Sorted

[Back to Search]

Sometimes you may wish to sort data in a Column like this: '01.2015, 02.2015, 01.2016' which would sort incorrectly like this '01.2015, 01.2016, 02.2015'. To reverse or Sort a Column of Data that Cannot usually be Sorted do the following:

1. Add a column next to the column you'd like to flip, reverse or sort
2. Fill that column with numbers, starting with 1 in the first Row, then 2 in the next Row and dragging down to the number of rows in the column you'd like to reverse
3. Select both columns and click Data > Sort
4. Sort by the second Column Ascending or Descending





How to Get Address of the Last Cell in a Range of Columns

[Back to Search]

To get the Address of the Last Cell in a Range of Columns you can use the following Array Formula (entered by pressing CTRL+SHIFT+ENTER). Add some data to Row 1 for as many Columns as you like and add the Formula below to A2:

 
' // add this Formula to Cell "A2" and press CTRL+SHIFT+ENTER
=CELL("address",INDEX($1:$1,MAX(($1:$1<>"")*(COLUMN($1:$1)))))
 
I entered some data up to Column E in Row 1, therefore I got the following result from the Formula which includes any Blank Cells:
 
$E$1
 





How to Get Column Number of Last Cell in a Range of Columns

[Back to Search]

To get the Column Number of the Last Cell in a Range of Columns you can use the following Array Formula (entered by pressing CTRL+SHIFT+ENTER). Add some data to Row 1 for as many Columns as you like and add the Formula below to A2:

 
' // add this Formula to Cell "A2" and press CTRL+SHIFT+ENTER
=MAX((1:1<>"")*COLUMN(1:1))
 
I entered some data up to Column E in Row 1, therefore I got the following result from the Formula which includes any Blank Cells:
 
5
 





How to Get the Row Number of Last Cell in a Range for a Column Containing Numeric or Date Data

[Back to Search]

To get the Row Number of Last Cell in a Range for Column 'A' enter the following Match Formula in Cell "B1":

 
' // enter the Formula below into Cell "B1".  the data must be Numeric or a Date ie. 5,6,7 or 01/04/2017
=MATCH(1,A:A,-1)
 





How to Get the Date from an ISO Week Number

[Back to Search]

To get the Date from a Week Number given 53.2015, 01.2016, 03.2017 etc. with the result being a Monday Date, you can use the following Formula - ensure that your dates are in Row 2, Column A, B, C etc. and Paste this Formula into Cell "A1", then drag right:

 
' // enter some ISO Week Numbers in the Format of 53.2015, 01.2016, 03.2017 etc.  add the Formula below to Cell "A1" & drag right...
=DATE(INT(RIGHT(A2,4)), 1, -2) - WEEKDAY(DATE(INT(RIGHT(A2,4)), 1, 3)) + INT(LEFT(A2,2)) * 7
 
You will now see the correct Monday Dates if you format the Cells in the first Row ie. 28/12/2015, 02/01/2017 and 16/01/2017





Average Numbers Ignore Zero

[Back to Search]

There are a few ways to average numbers and ignore zero which may give a different result than a simple =AVERAGE() Formula:

 
' // normal average including zero's - A1:D1 contains 1, 0, 3, 0 & equals '1'
=AVERAGE(A1:D1)

' // method 1 to ignore zero - A1:D1 contains 1, 0, 3, 0 & equals '2'
=AVERAGEIF(A1:D1,"<>0")

MARK GET FORMULA!!!

' // method 2 to ignore zero - A1:D1 contains 1, 0, 3, 0 & equals '2' (array Formula entered pressing CTRL+SHIFT+ENTER)
{=AVERAGE(0+A1:D1)}

 





Index Match

[Back to Search]

Index, Match can be used instead of VLOOKUP as it is faster and can reference entire Columns without changing Formula. Consider the following data, 2 Columns with Name and Age in Column A and Column B respectively. With a list of different Names in Column D, add the following Formula to Cell "E2":

 
' // simple Index, Match Formula
=INDEX(B:B,MATCH(D2,A:A,0))

' // means Index the Column that contains the value that you want to bring back
=INDEX(B:B

' // means match the value in Cell "D2" - this is the lookup value
,MATCH(D2

' // means match the value in Cell "D2" to a Column containing a list of like values
,A:A

' // means make an exact match
,0))
 





Extract Product Code and Description from a Cell with Forward Slash Separator

[Back to Search]

If you have a Cell that contains 2 items separated by a Slash ie. '12345 / TaittingerMosaicNocRse BOT 6X0,75' and you want to extract either the first item or the last item, in this instance the 'Product Code', '12345' and the 'Product Description', 'TaittingerMosaicNocRse BOT 6X0,75', then you can use the Code below or download the File at the bottom of the article to see how it's done and/or adjust for other items/separators

 
' // Cell Text in Cell "C2"
12345 / TaittingerMosaicNocRse BOT 6X0,75

' // Product (left part) - assumes the Cell to extract is in "C2" add this Formula to Cell "A2"
=INT(TRIM(LEFT(C2,FIND("/",C2,1)-1)))

' // Product Description (right part) - assumes the Cell to extract is in "C2" add this Formula to Cell "B2"
=TRIM(RIGHT(C2,LEN(C2)-FIND("/",C2,1)))
 
File: Extract Product Code & Description.xlsx (9.49 KB)





Boolean Replacement for IF And Boolean Switch

[Back to Search]

Here is a replacement for the =IF Function. We will use a Boolean result of a number of tests as the 'Index'+1 in and pass this into a =CHOOSE Function. The beauty of this is that we don't even have to test for a zero (0) Index as this will be an Index of 1 automatically. Also we can write any other Function into any of the Index slots

Every time one of our tests is correct a multiplier by 1, 2, 3 etc. will give a new Index that we can pass into the =CHOOSE Function

So in this simple example, we test for a positive number, a negative number or zero (0) and react accordingly all wrapped up in a neat little Formula

 
' // will test for a positive number, a negative number and if the result is zero (0) will display a zero result as a zero (0)
=CHOOSE(((A3+B3>0)*1+(A3+B3<0)*2)+1,0,A3+B3,A3+B3*-1)

' // will test for a positive number, a negative number and if the result is zero (0) will display a zero result as Nothing ie. "", Blank
=CHOOSE(((A3+B3>0)*1+(A3+B3<0)*2)+1,"",A3+B3,A3+B3*-1)
 
For an Example of a 3-way Boolean Switch using only '2' checks giving "Equal to zero","Larger than zero","Less than zero", please see the File Boolean Switch.xlsx below:

File: Replacement for IF.xlsx (9.39 KB)
File: Boolean Switch.xlsx (12.1 KB)





How to Get the Last Row Number using Formula (various)

[Back to Search]

Here are some methods to get the last Row number of data in Column "B" containing Numeric or Text with or without Headers. You can then use Index to return the Contents of the Row:

 
' // these Formula must have Numeric and Text somewher in the Column to Work (you can add 1 to a Row and hide the Row if your data does not contain both by default)
=MAX(MATCH(1E+306,Summary!$B:$B,1),MATCH("*",Summary!$B:$B,-1))

=MAX(MATCH(9.99999999999999E+307,B:B),MATCH(REPT("z",255),B:B))


' // these Formula are Array Formula and do not require both Text and Numeric data to be present but will return the last Row
'    - remember you can use these Formula in Defined Names as they are evaluated
'    - Copy & Paste without the curly braces
{=MAX(ROW(B:B)*(B:B<>""))}

{=MAX(IF(NOT(ISBLANK(B:B)),ROW(B:B)))}

{=MATCH(9.99999999999999E+307,1/(1-ISBLANK(B3:INDEX(B:B,ROWS(B:B)))))+ROW(B3)-1+ISBLANK(B3:INDEX(B:B,ROWS(B:B)))}

' // this Formula will bring back the Cell entry in the last Row again Text or Numeric
=LOOKUP(2,1/(LEN(B:B)>0),B:B)
 





Get or Parse out Cell Contents Containing a Delimiter

[Back to Search]

Here is a Formula to parse out last occurance of 'some Text' from a Cell containing the '/' Forward Slash Delimiter. The Formula works by Substituting (in essence, Padding) each occurance of the '/' Forward Slash with a ' ' Space the Length of "B1" and then returning the last 'n' digits the Length of "B1" which will be the last occurance Padded out to a Length of 16. Finally =TRIM is used to remove the Whitespace leaving the '10000'

In Cell "b2" the data is '10/10/B001/10000' and we want to return the '10000' part, the last part after the last '/' Forward Slash

 
' // in Cell "B1" using =RIGHT, the data 10/10/B001/10000 is parsed by the Formula below giving us the last part after the '/' Forward Slash, '10000'
=INT(TRIM(RIGHT(SUBSTITUTE(B1,"/",REPT(" ",LEN(B1))),LEN(B1))))

' // similarly, using =LEFT will give us the first occurance '10'
=INT(TRIM(LEFT(SUBSTITUTE(B2,"/",REPT(" ",LEN(B2))),LEN(B2))))

' // to get the second occurance we can use the following Formula
=TRIM(MID(B1,LEN(LEFT(B1,FIND("|",SUBSTITUTE(B1,"/","|",1))+1)),LEN(LEFT(B1,FIND("|",SUBSTITUTE(B1,"/","|",2))))-LEN(LEFT(B1,FIND("|",SUBSTITUTE(B1,"/","|",1))+1))))

' // to get the third occurance we can use the following Formula
=TRIM(MID(B1,LEN(LEFT(B1,FIND("|",SUBSTITUTE(B1,"/","|",2))+1)),LEN(LEFT(B1,FIND("|",SUBSTITUTE(B1,"/","|",3))))-LEN(LEFT(B1,FIND("|",SUBSTITUTE(B1,"/","|",2))+1))))

' // this Formula builds up the Parsing, first getting '10', then '10/10' and then finally '10/10/B001'
=LEFT( B1, FIND( "|", SUBSTITUTE( B1, "/", "|", 1) ) - 1 )
=LEFT( B1, FIND( "|", SUBSTITUTE( B1, "/", "|", 2) ) - 1 )
=LEFT( B1, FIND( "|", SUBSTITUTE( B1, "/", "|", 3) ) - 1 )

' // more Examples - get the first occurance before the '/' which is '10'
=LEFT(B1, SEARCH("/",B1,1)-1)

' // get the second occurance before the '/', again '10'
=MID(B1, SEARCH("/",B1) + 1, SEARCH("/",B1,SEARCH("/",B1)+1) - SEARCH("/",B1) - 1)
 





Find First and Last Sold In Formula

[Back to Search]

If you want to find out the First or Last time a value appears across many Columns of data, for example the First time a Sale was made in 2016 or the Last time something Sold being in 2017, then you can use the Formula below or open the Workbooks to see how I do it

 
// first Sold In Formula
=INDEX(D$1:CU$1,MATCH(TRUE,INDEX(D2:CU2<>"",),0))

// Last Sold In Formula which also tells you if something 'Never Sold' at all within the Range
=IFERROR(LOOKUP(9.999999999E+307,C2:FO2,$C$1:$FO$1),"Never Sold")
 
File: First Sold In Example.xlsx (996 KB)
File: Last Sold In Example.xlsx (773 KB)





Transpose Block of N Rows to Columns

[Back to Search]

If you want to Transpose a Block of 5 Rows of data into Columns then you can use the following Formula - modify it to do less or more than 5 Columns by adjusting the number '5'

 
=INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1))
 
File: Transpose N Rows to Columns.xlsx (9.52 KB)




Using SUMPRODUCT with Filtered or Hidden Rows

[Back to Search]

Sometimes you may want to COUNT or SUM Multiple Columns so that if a Row is Filtered in an Autofilter or the Row is Hidden it will not be included in the Formula ie. I want to COUNT and SUM Sales for Products where the percentage share >95%, with data including Headers in Cells "B4:E8", then I could use the following Formulas (check out the File for more information). The Formula use SUMPRODUCT, SUBTOTAL, OFFSET & MIN to calculate the values

 
' my data in Cells "B4:E8" and Cell "C14" = 95 (add a Filter to the data Range and the Formula below - filter the Range)
Product	Product Description									 Sales
10001	Funkin RaspMoj Car 6X1	72.1%				£31,655.86
10002	Cragganmore 12 BOT 6x0,7	96.9%		£26,564.20
10003	Smirnof &Cranb CAN 12X0,25	99.2%	  £20,990.45
10004	Funkin Lemon Car 5x1 Puree	10.8%	   £11,328.00

' COUNT
=SUMPRODUCT((D5:D8>=C14)*(SUBTOTAL(103,OFFSET(E5,ROW(E5:E8)-MIN(ROW(E5:E8)),0))))

' SUM
=SUMPRODUCT((D5:D8>=C14)*(SUBTOTAL(109,OFFSET(E5,ROW(E5:E8)-MIN(ROW(E5:E8)),0))))
 
File: Using SUMPRODUCT with Hidden or Filtered Rows.xlsx (10.0 KB)




How to Setup and Format a Rolling ISO Week Number

[Back to Search]

Sometimes you may want to have a rolling ISO Week Number in your Workbook that is Formatted differently and that you can drag right across all of your Columns as Headers. I will give you 2 Formulas that you can use to do this. The first one is in 2 parts, meaning that from a Date in Cell "A1" you have to enter both Formulas - the second one can be dragged across your Worksheet. The second one is a single Formula that can just simply be dragged across your Worksheet. The Formula will produce rolling ISO Week numbers as your headers using the Format X.XXX ie. 7.2018, 8.2018, 9.2018, 10.2018 -> 52.2018, 1.2019 etc.

You can easily change the formatting by prefixing with ="wk "& to add a 'wk' prefix ie. wk 1.2019, wk 2.2019 or even use Text() to format the Week numbers 1->53 as 01, 02, 03 etc. using Text(,"00") as the wrapper for the first part of the INT Formula - simple...

 
' 2 parts - add the Date in Cell "A1", add Formula 1 to any Cell for the current ISO Week and then add Formula 2 to any Cell and drag across
=INT(($A$1-DATE(YEAR($A$1-WEEKDAY($A$1-1)+4),1,3)+WEEKDAY(DATE(YEAR($A$1-WEEKDAY($A$1-1)+4),1,3))+5)/7)&"."&YEAR($A$1-WEEKDAY($A$1-1)+4)
=INT(($A$1+INT((COLUMN(A:A))*7)-DATE(YEAR($A$1+INT((COLUMN(A:A))*7)-WEEKDAY($A$1+INT((COLUMN(A:A))*7)-1)+4),1,3)+WEEKDAY(DATE(YEAR($A$1+INT((COLUMN(A:A))*7)-WEEKDAY($A$1+INT((COLUMN(A:A))*7)-1)+4),1,3))+5)/7)&"."&YEAR($A$1+INT((COLUMN(A:A))*7)-WEEKDAY($A$1+INT((COLUMN(A:A))*7)-1)+4)

' single Formula - add the Date to Cell "A1" and drag across
=INT(($A$1+INT((COLUMNS($A$1:A1)-1)*7)-DATE(YEAR($A$1+INT((COLUMNS($A$1:A1)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A1)-1)*7)-1)+4),1,3)+WEEKDAY(DATE(YEAR($A$1+INT((COLUMNS($A$1:A1)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A1)-1)*7)-1)+4),1,3))+5)/7)&"."&YEAR($A$1+INT((COLUMNS($A$1:A1)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A1)-1)*7)-1)+4)

' using a Text format to show leading zeros
=TEXT(INT(($A$1+INT((COLUMNS($A$1:A4)-1)*7)-DATE(YEAR($A$1+INT((COLUMNS($A$1:A4)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A4)-1)*7)-1)+4),1,3)+WEEKDAY(DATE(YEAR($A$1+INT((COLUMNS($A$1:A4)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A4)-1)*7)-1)+4),1,3))+5)/7),"00")&"."&YEAR($A$1+INT((COLUMNS($A$1:A4)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A4)-1)*7)-1)+4)

' adding on a Week prefix
="wk "&TEXT(INT(($A$1+INT((COLUMNS($A$1:A7)-1)*7)-DATE(YEAR($A$1+INT((COLUMNS($A$1:A7)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A7)-1)*7)-1)+4),1,3)+WEEKDAY(DATE(YEAR($A$1+INT((COLUMNS($A$1:A7)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A7)-1)*7)-1)+4),1,3))+5)/7),"00")&"."&YEAR($A$1+INT((COLUMNS($A$1:A7)-1)*7)-WEEKDAY($A$1+INT((COLUMNS($A$1:A7)-1)*7)-1)+4)
 
File: How to Setup and Format a Rolling ISO Week Number.xlsx (12.0 KB)




Use Columns() as Zero in a Formula

[Back to Search]

Sometimes you may want to use the Columns() Function in a Formula but have the initial result coerse to Zero (0) - this Formula will do that for you (both methods are shown below):

 
' standard method
COLUMN(A:A)

' setting the first result to zero (0)
=(COLUMNS($A$1:A1)-1)*7
 




Conditional Format Highlight Dates in Current Year

[Back to Search]

If you want to highlight Dates in a Column that are in the current Year if a File is opened say in 2 Years time or if the Dates are in a specific Year ie. 2018 no matter when the File is opened at some future Date, then check out the Conditional Format Formula and the example File below:

 
' current Year any time the file is opened in the future
=YEAR(F3)=YEAR(TODAY())

' specific Year no matter whenever the file is opened in the future
=YEAR(H3)=2018
 
File: Date Format.xlsx (12.0 KB)