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* ;"—"
 





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 to Capture a Range using Offset when zeros (0) are included

[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





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)