Convert Excel Formula to Power Query M Language

J

jajatidev

Board Regular
Joined
Jul 29, 2016
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Hi,
Requesting assistance to convert the following Excel formulas in Power Query;

Excel Formula: 
=
IF(AND([@CompleteDelivery]="N",[@OrderQty]=[@ConfirmedQty]),"Allocated",
IF(AND([@CompleteDelivery]="N",[@ConfirmedQty]>0,[@ConfirmedQty]<[@OrderQty]),"Partially Allocated",
IF(AND([@CompleteDelivery]="Y",SUMIF([SalesOrderNo],[@SalesOrderNo],[OrderQty])=SUMIF([SalesOrderNo],[@SalesOrderNo],[ConfirmedQty])),"Allocated",
IF(AND([@CompleteDelivery]="Y",SUMIF([SalesOrderNo],[@SalesOrderNo],[ConfirmedQty])>0,SUMIF([SalesOrderNo],[@SalesOrderNo],[ConfirmedQty])<SUMIF([SalesOrderNo],[@SalesOrderNo],[OrderQty])),
"Partially Allocated",
"Pending Allocation"))))
Excel Formula: 
=IF([@CompleteDelivery]="N",IF([@UnconfirmedQty]>0,[@ProductNo],""),TEXTJOIN(" / ",,UNIQUE(FILTER([ProductNo],([SalesOrderNo]=[@SalesOrderNo])*([UnconfirmedQty]>0),""))))
Excel Formula: 
=IF([@SKUMix]="","",LEN(TRIM([@SKUMix]))-LEN(SUBSTITUTE([@SKUMix],"/",""))+1)

ReceiveDateCustomerReqDateSalesOrderNoProductNoOrderQtyConfirmedQtyUnconfirmedQtyCompleteDeliveryOrder StatusSKUMixNoOfImpactedSKUs
6/22/20239/15/20231129353801NR73A-K1H101NPending Allocation1NR73A-K1H1
7/3/20237/7/2023113430548A1A30A-KGJ101NPending AllocationA1A30A-KGJ1
7/4/20237/8/20231134937101NR73A-K1H110NAllocated
7/5/20237/9/2023113512547A1A30A-KGJ101NPending AllocationA1A30A-KGJ1
7/6/20237/10/20231135438135RA32F-KGJ101NPending Allocation5RA32F-KGJ1
7/7/20237/11/2023113561139A1A30A-KGJ101NPending AllocationA1A30A-KGJ1
7/8/20237/12/2023113705782A1A77A-KGJ1055NPartially AllocatedA1A77A-KGJ1
7/9/20237/13/2023113725103N7L37A110NAllocated
7/10/20237/14/20231139009462Z600F-KGJ101NPending Allocation2Z600F-KGJ1
7/11/20237/15/20231139338933JA05AN-150101NPending Allocation3JA05AN-1501
7/12/20237/16/2023113969730A1A56A-KGJ101NPending AllocationA1A56A-KGJ1
7/13/20237/17/20231139877505RA32F-KGJ110NAllocated
7/14/20237/18/20231140021502Z613F-KGJ211NPartially Allocated2Z613F-KGJ1
7/15/20237/19/20231140021505RA32F-KGJ110NAllocated
7/16/20237/20/20231140034522Z600F-KGJ523NPartially Allocated2Z600F-KGJ1
7/17/20237/21/20231140051802Z600F-KGJ110NAllocated
7/18/20237/22/2023114016353A1A77A-KGJ101NPending AllocationA1A77A-KGJ1
7/19/20237/23/20231101134333GZ25A-KGJ101YPartially Allocated3GZ25A-KGJ / D7P27A2
7/20/20237/24/2023110113433K5L35A110YPartially Allocated3GZ25A-KGJ / D7P27A2
7/21/20237/25/20231101134333GZ25A-KGJ101YPartially Allocated3GZ25A-KGJ / D7P27A2
7/22/20237/26/2023110113433K5L35A110YPartially Allocated3GZ25A-KGJ / D7P27A2
7/23/20237/27/2023110113433Y3D03A110YPartially Allocated3GZ25A-KGJ / D7P27A2
7/24/20237/28/2023110113433Z7C07A110YPartially Allocated3GZ25A-KGJ / D7P27A2
7/25/20237/29/20231101134333PZ35A-KGJ110YPartially Allocated3GZ25A-KGJ / D7P27A2
7/26/20237/30/2023110113433D7P27A110YPartially Allocated3GZ25A-KGJ / D7P27A2
7/27/20237/31/20231101134333PZ35A-KGJ110YPartially Allocated3GZ25A-KGJ / D7P27A2
7/28/20238/1/2023110113433D7P27A101YPartially Allocated3GZ25A-KGJ / D7P27A2
7/29/20238/2/20231104339073GZ25A-KGJ101YPartially Allocated3GZ25A-KGJ1
7/30/20238/3/2023110433907K5L35A110YPartially Allocated3GZ25A-KGJ1
7/31/20238/4/2023110433907Y3D03A110YPartially Allocated3GZ25A-KGJ1
8/1/20238/5/2023110433907Z7C07A110YPartially Allocated3GZ25A-KGJ1
8/2/20238/6/20231104339073GZ25A-KGJ101YPartially Allocated3GZ25A-KGJ1
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Try the following code.
Note: Remove the Order Status, SKUMix, and NoOfImpactedSKUs columns before creating the source table - Table1 in the sample below.

Power Query: 
let
    // Note: The last three columns are not included 
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SourceBuffered = Table.Buffer(Source),

    TotOrderQty = Table.AddColumn(SourceBuffered, "TotOrderQty", 
                                    each Table.Group(
                                        Table.SelectRows(SourceBuffered, (r) => [SalesOrderNo] = r[SalesOrderNo]), 
                                        {"SalesOrderNo"}, 
                                        {{"TotOrderQty", each List.Sum([OrderQty])}}
                                    ){0}[TotOrderQty]),
    TotConfQty = Table.AddColumn(TotOrderQty, "TotConfQty", 
                                    each Table.Group(
                                            Table.SelectRows(SourceBuffered, (r) => [SalesOrderNo] = r[SalesOrderNo]), 
                                            {"SalesOrderNo"}, 
                                            {{"TotConfirmedQty", each List.Sum([ConfirmedQty])}}
                                    ){0}[TotConfirmedQty]),

    OrderStatus = Table.AddColumn(TotConfQty, "OrderStatus", 
                        (r) => if r[CompleteDelivery] = "N" and r[OrderQty] = r[ConfirmedQty]
                            then "Allocated"
                            else
                                if r[CompleteDelivery]="N" and r[ConfirmedQty]>0 and r[ConfirmedQty] < r[OrderQty]
                                    then "Partially Allocated"
                                    else 
                                        if r[TotOrderQty] = r[TotConfQty]
                                            then "Allocated"
                                            else 
                                                if r[TotConfQty] > 0 and r[TotConfQty] < r[TotOrderQty]                           
                                                    then "Partially Allocated"
                                                    else "Pending Allocation"
            ),

    SKUMix = Table.AddColumn(OrderStatus, "SKUMix",
                each if [CompleteDelivery]="N"
                        then if [UnconfirmedQty] > 0 
                                then [ProductNo]
                                else ""
                        else 
                            Text.Combine(
                                List.Distinct(
                                    Table.SelectRows(SourceBuffered, (r) => ([SalesOrderNo] = r[SalesOrderNo] and r[UnconfirmedQty] > 0))[ProductNo]
                                ), 
                                " / "
                            )
            ),
    NoOfImpactedSKUs = Table.AddColumn(SKUMix, "NoOfImpactedSKUs",
                        each if [SKUMix] = ""
                                then ""
                                else Text.Length(Text.Select([SKUMix], {"/"})) + 1
                        ),
    Result = Table.RemoveColumns(NoOfImpactedSKUs,{"TotOrderQty", "TotConfQty"})
in
    Result
 
Upvote 0
smozgur said:
Try the following code.
Note: Remove the Order Status, SKUMix, and NoOfImpactedSKUs columns before creating the source table - Table1 in the sample below.

Power Query: 
let
    // Note: The last three columns are not included
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SourceBuffered = Table.Buffer(Source),

    TotOrderQty = Table.AddColumn(SourceBuffered, "TotOrderQty",
                                    each Table.Group(
                                        Table.SelectRows(SourceBuffered, (r) => [SalesOrderNo] = r[SalesOrderNo]),
                                        {"SalesOrderNo"},
                                        {{"TotOrderQty", each List.Sum([OrderQty])}}
                                    ){0}[TotOrderQty]),
    TotConfQty = Table.AddColumn(TotOrderQty, "TotConfQty",
                                    each Table.Group(
                                            Table.SelectRows(SourceBuffered, (r) => [SalesOrderNo] = r[SalesOrderNo]),
                                            {"SalesOrderNo"},
                                            {{"TotConfirmedQty", each List.Sum([ConfirmedQty])}}
                                    ){0}[TotConfirmedQty]),

    OrderStatus = Table.AddColumn(TotConfQty, "OrderStatus",
                        (r) => if r[CompleteDelivery] = "N" and r[OrderQty] = r[ConfirmedQty]
                            then "Allocated"
                            else
                                if r[CompleteDelivery]="N" and r[ConfirmedQty]>0 and r[ConfirmedQty] < r[OrderQty]
                                    then "Partially Allocated"
                                    else
                                        if r[TotOrderQty] = r[TotConfQty]
                                            then "Allocated"
                                            else
                                                if r[TotConfQty] > 0 and r[TotConfQty] < r[TotOrderQty]                          
                                                    then "Partially Allocated"
                                                    else "Pending Allocation"
            ),

    SKUMix = Table.AddColumn(OrderStatus, "SKUMix",
                each if [CompleteDelivery]="N"
                        then if [UnconfirmedQty] > 0
                                then [ProductNo]
                                else ""
                        else
                            Text.Combine(
                                List.Distinct(
                                    Table.SelectRows(SourceBuffered, (r) => ([SalesOrderNo] = r[SalesOrderNo] and r[UnconfirmedQty] > 0))[ProductNo]
                                ),
                                " / "
                            )
            ),
    NoOfImpactedSKUs = Table.AddColumn(SKUMix, "NoOfImpactedSKUs",
                        each if [SKUMix] = ""
                                then ""
                                else Text.Length(Text.Select([SKUMix], {"/"})) + 1
                        ),
    Result = Table.RemoveColumns(NoOfImpactedSKUs,{"TotOrderQty", "TotConfQty"})
in
    Result
Click to expand...
Hi, Is it possible to have a narrative? specially role played by the following characters (r), r, => in the code for an understanding.
 
Upvote 0
I used that method for the SUMIF function. Basically, I am grouping rows by SalesOrderNo field and aggregate the various column values by SalesOrderNo. I would use the following code for this (you can also use the UI to generate this code):
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.Group(Source, {"SalesOrderNo"}, {{"TotOrderQty", each List.Sum([OrderQty]), type number}, {"TotConfQty", each List.Sum([ConfirmedQty]), type number}})
in
    Result

However, I don't want to group the rows, only use the group total of certain columns. So, let's start grouping only one SalesOrderNo:
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.Group(
                Table.SelectRows(Source, each [SalesOrderNo] = 110113433), 
                {"SalesOrderNo"}, 
                {{"TotOrderQty", each List.Sum([OrderQty])}}
            )
in
    Result

It will give the following result:
1692195262853.png

So, I can get the TotOrderQty by using direct record / row access (see the addition: {0}[TotOrderQty]).
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.Group(
                Table.SelectRows(Source, each [SalesOrderNo] = 110113433), 
                {"SalesOrderNo"}, 
                {{"TotOrderQty", each List.Sum([OrderQty])}}
            ){0}[TotOrderQty]
in
    Result

I do this for each row and add a new column for each SalesOrderNo instead of hardcoded SalesOrderNo: 110113433:

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SourceBuffered = Table.Buffer(Source),
    TotOrderQty = Table.AddColumn(SourceBuffered, "TotOrderQty", 
                                    each Table.Group(
                                        Table.SelectRows(SourceBuffered, (r) => [SalesOrderNo] = r[SalesOrderNo]), 
                                        {"SalesOrderNo"}, 
                                        {{"TotOrderQty", each List.Sum([OrderQty])}}
                                    ){0}[TotOrderQty])
in
    TotOrderQty

So, what's the role of (r) instead of using each in the Table.SelectRows() function?

The each keyword is actually a shortcut for (_) => function identifier where _ is the subject being iterated in the function, which is a "row" for Table.SelectRows() function. When you use each, you can refer to a column by using its name, simply [SalesOrderNo] instead of _[SalesOrderNo]. However, the Table.SelectRows() function is a nested function and if we use each then we won't be able to refer to the outer field that is passed by the Table.AddColumn() function. Instead, I used the function identifier by using a variable name, r, and this way I can access both outer [SalesOrderNo] and the inner r[SalesOrderNo] column values.

I hope I didn't make it more complicated to understand.

The same thing can be also achieved without adding those Total columns separately as I did but simply using a group table by using the following modified code. In this case, I create a group table, and then merge this table with the source table to get the total columns.

Power Query: 
let
    // Note: The last three columns are not included 
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SourceBuffered = Table.Buffer(Source),

    Qtys = Table.Group(Source, {"SalesOrderNo"}, {{"TotOrderQty", each List.Sum([OrderQty]), type number}, {"TotConfQty", each List.Sum([ConfirmedQty]), type number}}),

    Merge = Table.NestedJoin(Source, "SalesOrderNo", Qtys, "SalesOrderNo", "Merge"),

    Expand = Table.ExpandTableColumn(Merge, "Merge", {"TotOrderQty", "TotConfQty"}),

    OrderStatus = Table.AddColumn(Expand, "OrderStatus", 
                        each if [CompleteDelivery] = "N" and [OrderQty] = [ConfirmedQty]
                            then "Allocated"
                            else
                                if [CompleteDelivery]="N" and [ConfirmedQty]>0 and [ConfirmedQty] < [OrderQty]
                                    then "Partially Allocated"
                                    else 
                                        if [TotOrderQty] = [TotConfQty]
                                            then "Allocated"
                                            else 
                                                if [TotConfQty] > 0 and [TotConfQty] < [TotOrderQty]                           
                                                    then "Partially Allocated"
                                                    else "Pending Allocation"
            ),

    SKUMix = Table.AddColumn(OrderStatus, "SKUMix",
                each if [CompleteDelivery]="N"
                        then if [UnconfirmedQty] > 0 
                                then [ProductNo]
                                else ""
                        else 
                            Text.Combine(
                                List.Distinct(
                                    Table.SelectRows(SourceBuffered, (r) => ([SalesOrderNo] = r[SalesOrderNo] and r[UnconfirmedQty] > 0))[ProductNo]
                                ), 
                                " / "
                            )
            ),
    NoOfImpactedSKUs = Table.AddColumn(SKUMix, "NoOfImpactedSKUs",
                        each if [SKUMix] = ""
                                then ""
                                else Text.Length(Text.Select([SKUMix], {"/"})) + 1
                        ),
    Result = Table.RemoveColumns(NoOfImpactedSKUs,{"TotOrderQty", "TotConfQty"})
in
    Result
 
Upvote 1
Solution
smozgur said:
I used that method for the SUMIF function. Basically, I am grouping rows by SalesOrderNo field and aggregate the various column values by SalesOrderNo. I would use the following code for this (you can also use the UI to generate this code):
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.Group(Source, {"SalesOrderNo"}, {{"TotOrderQty", each List.Sum([OrderQty]), type number}, {"TotConfQty", each List.Sum([ConfirmedQty]), type number}})
in
    Result

However, I don't want to group the rows, only use the group total of certain columns. So, let's start grouping only one SalesOrderNo:
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.Group(
                Table.SelectRows(Source, each [SalesOrderNo] = 110113433),
                {"SalesOrderNo"},
                {{"TotOrderQty", each List.Sum([OrderQty])}}
            )
in
    Result

It will give the following result:
View attachment 97257
So, I can get the TotOrderQty by using direct record / row access (see the addition: {0}[TotOrderQty]).
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.Group(
                Table.SelectRows(Source, each [SalesOrderNo] = 110113433),
                {"SalesOrderNo"},
                {{"TotOrderQty", each List.Sum([OrderQty])}}
            ){0}[TotOrderQty]
in
    Result

I do this for each row and add a new column for each SalesOrderNo instead of hardcoded SalesOrderNo: 110113433:

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SourceBuffered = Table.Buffer(Source),
    TotOrderQty = Table.AddColumn(SourceBuffered, "TotOrderQty",
                                    each Table.Group(
                                        Table.SelectRows(SourceBuffered, (r) => [SalesOrderNo] = r[SalesOrderNo]),
                                        {"SalesOrderNo"},
                                        {{"TotOrderQty", each List.Sum([OrderQty])}}
                                    ){0}[TotOrderQty])
in
    TotOrderQty

So, what's the role of (r) instead of using each in the Table.SelectRows() function?

The each keyword is actually a shortcut for (_) => function identifier where _ is the subject being iterated in the function, which is a "row" for Table.SelectRows() function. When you use each, you can refer to a column by using its name, simply [SalesOrderNo] instead of _[SalesOrderNo]. However, the Table.SelectRows() function is a nested function and if we use each then we won't be able to refer to the outer field that is passed by the Table.AddColumn() function. Instead, I used the function identifier by using a variable name, r, and this way I can access both outer [SalesOrderNo] and the inner r[SalesOrderNo] column values.

I hope I didn't make it more complicated to understand.

The same thing can be also achieved without adding those Total columns separately as I did but simply using a group table by using the following modified code. In this case, I create a group table, and then merge this table with the source table to get the total columns.

Power Query: 
let
    // Note: The last three columns are not included
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SourceBuffered = Table.Buffer(Source),

    Qtys = Table.Group(Source, {"SalesOrderNo"}, {{"TotOrderQty", each List.Sum([OrderQty]), type number}, {"TotConfQty", each List.Sum([ConfirmedQty]), type number}}),

    Merge = Table.NestedJoin(Source, "SalesOrderNo", Qtys, "SalesOrderNo", "Merge"),

    Expand = Table.ExpandTableColumn(Merge, "Merge", {"TotOrderQty", "TotConfQty"}),

    OrderStatus = Table.AddColumn(Expand, "OrderStatus",
                        each if [CompleteDelivery] = "N" and [OrderQty] = [ConfirmedQty]
                            then "Allocated"
                            else
                                if [CompleteDelivery]="N" and [ConfirmedQty]>0 and [ConfirmedQty] < [OrderQty]
                                    then "Partially Allocated"
                                    else
                                        if [TotOrderQty] = [TotConfQty]
                                            then "Allocated"
                                            else
                                                if [TotConfQty] > 0 and [TotConfQty] < [TotOrderQty]                          
                                                    then "Partially Allocated"
                                                    else "Pending Allocation"
            ),

    SKUMix = Table.AddColumn(OrderStatus, "SKUMix",
                each if [CompleteDelivery]="N"
                        then if [UnconfirmedQty] > 0
                                then [ProductNo]
                                else ""
                        else
                            Text.Combine(
                                List.Distinct(
                                    Table.SelectRows(SourceBuffered, (r) => ([SalesOrderNo] = r[SalesOrderNo] and r[UnconfirmedQty] > 0))[ProductNo]
                                ),
                                " / "
                            )
            ),
    NoOfImpactedSKUs = Table.AddColumn(SKUMix, "NoOfImpactedSKUs",
                        each if [SKUMix] = ""
                                then ""
                                else Text.Length(Text.Select([SKUMix], {"/"})) + 1
                        ),
    Result = Table.RemoveColumns(NoOfImpactedSKUs,{"TotOrderQty", "TotConfQty"})
in
    Result
Click to expand...
Thank you for the explanation. It's not confusing. At times it feels daunting because I'm trying to run where I don't even know how to walk.
Yesterday I followed the steps and was able to capture the codes for the desired results.
However, without having a complete understanding it felt like copy paste. Now there is some level of clarity.
 
Upvote 0
jajatidev said:
Thank you for the explanation. It's not confusing. At times it feels daunting because I'm trying to run where I don't even know how to walk.
Yesterday I followed the steps and was able to capture the codes for the desired results.
However, without having a complete understanding it felt like copy paste. Now there is some level of clarity.
Click to expand...
You're welcome. Glad to hear it helps. I often write comments in my sample codes, but I also prefer OP to ask for further information in case of a learning desire - which makes me happier, and also save time to explain only the necessary parts instead of everything.

The M language is not different than writing in any programming language if you have any programming experience. It is just a lot of functions to know which one to use when but once you start, it becomes easier and clear. There are times that I write many lines of complicated codes that could be actually done by using a few simple functions or using methods. Living sample above: creating a simple group table and merging to get the calculated columns instead of adding calculated columns row by row that would be likely less efficient due to making the calculation for each row in the first method. It is nice to know that it is possible both way, but it is important to learn that one of them is more efficient for that particular case.
 
Upvote 0
smozgur said:
You're welcome. Glad to hear it helps. I often write comments in my sample codes, but I also prefer OP to ask for further information in case of a learning desire - which makes me happier, and also save time to explain only the necessary parts instead of everything.

The M language is not different than writing in any programming language if you have any programming experience. It is just a lot of functions to know which one to use when but once you start, it becomes easier and clear. There are times that I write many lines of complicated codes that could be actually done by using a few simple functions or using methods. Living sample above: creating a simple group table and merging to get the calculated columns instead of adding calculated columns row by row that would be likely less efficient due to making the calculation for each row in the first method. It is nice to know that it is possible both way, but it is important to learn that one of them is more efficient for that particular case.
Click to expand...
Hi @smozgur,
I'm stuck in a section of the proposed solution. The "Sources Buffer" isn't working as it is slowing down the refresh rate to such an extent that the report I'm developing is becoming ineffective. I was able to address the Order Status section. However, I'm getting undone in the below section where the source buffered is applied. would you be able to suggest an alternative approach that doesn't involve buffering the data?

Power Query: 
SKUMix = Table.AddColumn(OrderStatus, "SKUMix",
                each if [CompleteDelivery]="N"
                        then if [UnconfirmedQty] > 0
                                then [ProductNo]
                                else ""
                        else
                            Text.Combine(
                                List.Distinct(
                                    Table.SelectRows(SourceBuffered, (r) => ([SalesOrderNo] = r[SalesOrderNo] and r[UnconfirmedQty] > 0))[ProductNo][/COLOR]
                                ),
                                " / "
                            )
            ),
    NoOfImpactedSKUs = Table.AddColumn(SKUMix, "NoOfImpactedSKUs",
                        each if [SKUMix] = ""
                                then ""
                                else Text.Length(Text.Select([SKUMix], {"/"})) + 1
                        )
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel Power Query & Power Pivot: Ageing Trend
Replies
1
Views
288
jajatidev
J
J
  • Solved
Need assistance with Power Query
2
Replies
11
Views
657
smozgur
smozgur
M
  • Question Question
converting Excel formulas to Power Query M Language
Replies
3
Views
723
mdshuja
M
L
  • Question Question
Help win unpivot super slow query
2
Replies
15
Views
1K
labello27
L
A
  • Solved
Power Query to arrange 8 Different Headers as two different columns
Replies
5
Views
298
ashish128
A

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top