User Posts: Musiha Mahfuza Mukta
How to Group Data in Pivot Table (3 Different Examples)

It is difficult to deal with a large dataset, right? But if you can convert the dataset into some groups (based on data type) then it will be easier. And for ...

Character Code for Check Mark in Excel (5 Suitable Findings)

Say, you want to make a to-do list. So, what will you do? Make a list and after finishing the work, you will give a check mark on that job, right? So, it is ...

Excel VBA to Custom Sort (5 Easy Examples)

Sorting means organizing data in ascending or descending order. So, you can easily visualize sorted extensive data. Also, you can find any information at a ...

Excel VBA with For Loop Decrement (5 Examples)

When you need to go backward in a loop like you want to check a range from the bottom to the top, in that case, you can use the For Next loop with a negative ...

How to Use Excel VBA Border Weight Property (8 Examples)

When you have a large dataset and want to change the border thickness or even when you want to put a border within your dataset, you can use the Excel ...

How to Merge and Center Selected Cells in Excel (4 Ways)

You may need to use the same values in some cells. In that case, you don’t need to insert the same cell value repeatedly, simply you can merge and center those ...

How to Use Implicit Intersection Operator in Excel (4 Examples)

Sometimes you may need to use an implicit intersection operator in Excel. In this article, I will show you how, and where to use an implicit intersection (@) ...

How to Use IF-THEN Statements with Text in Excel (7 Examples)

Sometimes you may need to use some combination of functions in Excel for searching a particular text or returning the answer as text. So, if you are looking ...

How to Make Floating Bar Chart in Excel (2 Easy Ways)

Sometimes for data visualization, you may need to create a floating bar chart in Excel. So, if you are looking for how to make this chart in Excel, then you ...

How to Create a Date Picker in Excel Using VBA (with Easy Steps)

A date picker is one kind of dynamic calendar that allows the user to select a particular date. So, if you are looking for how to create a date picker with the ...

How to Shade Area Between Two Lines in a Chart in Excel 

To increase the visuality of a data range, you may need to shade the area between two lines of an Excel chart. So, if you are looking for how to shade the area ...

How to Perform Drawing of Objects with VBA in Excel (2 Examples)

Sometimes you may need to draw a shape or an object in Excel. So, if you are looking for drawing objects using VBA code in Excel, then you have come to the ...

How to Calculate Volatility for Black Scholes in Excel (2 Methods)

Sometimes you may need to calculate volatility using the Black-Scholes formula in Excel. So, if you are looking for how to calculate volatility for the ...

How to Perform Scaling for Numbers in Excel (7 Effective Ways)

When you have a dataset that contains numbers with more than 4 digits, then you may need to scale them for lowering those digits. So, if you are looking for ...

How to Create Project Pipeline in Excel (2 Suitable Methods)

Sometimes for business or institute purposes, you may need to create a project pipeline in Excel to track all your projects easily. So, if you are looking for ...

Browsing All Comments By: Musiha Mahfuza Mukta
  1. Reply Avatar photo
    Musiha Mahfuza Mukta May 3, 2023 at 11:28 AM

    Thank you, EAC for your comment. The possible solution is given below.
    • Select all the cells by clicking the triangle where row and column headers coincide.
    • Next, open the Format Cells by pressing Ctrl+1 >> Select the Protection option >> Uncheck the Locked option to unlock cells >> Click on OK.

    • Select the data range which you want to lock.
    • Again, press Ctrl+1 >> The Format Cells dialog box will pop up >> Select Protection >> Next check on the Locked option >> Click on OK.
    • Go to the Review tab in the ribbon >> Select Protect Sheet from the Protect group.
    • A Protect Sheet dialog box will appear >> Set any password in the password box >> Check on the Protect worksheet and contents of locked cells>> Check both Select locked cells, Select unlocked cells.
    • A Confirm Password dialog box will appear >> Rewrite your given password >> Click on OK.

    Now, try to edit the cells. Then you will get a warning from Microsoft Excel that you can’t change anything. To edit or enter any value, you have unprotected the Excel sheet with that password first.

    Furthermore, you can see this article for more details How to Protect Excel Cells from Being Edited.

  2. Reply Avatar photo
    Musiha Mahfuza Mukta Mar 27, 2023 at 1:53 PM

    Thank you, CARLOS for your comment. You have to use the correct array (Sales Rep
    or B5:B14) in INDEX function and Rank Column (D5:D14) in MATCH function. Also, while using the Fill Handle icon, you have to freeze both arrays. The most important part, you must write Rank 1,2,3.. manually in General format in F column.
    There may have extra space or Apostrophe (‘) in the F column where you insert Rank numbers manually. You should remove all extra spaces.
    You can see our article related MATCH function error. The link is:
    For getting basic idea of INDEX-MATCH function you can see the examples from this article
    Still, you are facing the problem then please comment with your used formula and sample dataset.
    Musiha Mahfuza Mukta| Team Exceldemy.

  3. Reply Avatar photo
    Musiha Mahfuza Mukta Mar 27, 2023 at 12:44 PM

    Thanks, KYLE, for your query. If there are identical values, then it will give same Rank. Also, the RANK function will skip one Rank. For your better understanding, I’m changing the sales value of Janifer to $9158. So, Zuschuss and Janifer get the same rank (2nd). Thus, the 3rd rank will be missed. Here, you must re-write the Rank of F7 cell to “2” and change the array for INDEX-MATCH function using in G7 an H7 cell. Basically, you need to set the array without Zuschuss information. Below, I have attached the whole scenario. getting duplicate rank
    There is another way, if you want to get unique Rank for all. Like Zuschuss comes first than Janifer so Zuschuss will get 2nd rank and Janifer will get 3rd rank. In this case, you just need to change the formula in D column given below: =RANK(C5,$C$5:$C$14,0)+COUNTIF($C$5:C5,C5)-1
    You don’t need to change the array of INDEX-MATCH function. getting unique rank
    Musiha Mahfuza Mukta| Team Exceldemy

  4. Reply Avatar photo
    Musiha Mahfuza Mukta Mar 27, 2023 at 11:41 AM

    Hi ATIF, Thanks for your comment. Here, I made a dataset keeping the Text value in A column, Date value in B column and Counter will be in C column. If you provide your Excel file, then it will be more useful. As per my understanding, I am providing the following VBA code.
    From Developer tab >> go to Visual Basic >> Insert a Module >> copy the code in that >> from Macros >> Run the code.
    code for days counter

    Sub Days_Counter()
    Count = 0
    For i = 1 To 11
    If Range("B1").Cells(i) <> Range("B1").Cells(i + 1) _
    And Range("A1").Cells(i) = "Normal" Then
        Count = Count + 1
        Range("C1").Cells(i) = Count
    Else: Range("C1").Cells(i) = 0
    End If
    Next i
    End Sub

    You can see the outcome below.
    Where the counter counts single value for same date. I have highlighted the same date. Also, for “Abnormal” text the counter didn’t count.
    In the code, you must mention total Row number of your dataset in For Next loop.
    If you want only the text part, you can remove this portion

    Range("B1").Cells(i) <> Range("B1").Cells(i + 1)

    from code.
    Hopefully this will work. If you are still facing problem, then please comment with more details or the sample dataset.
    Musiha Mahfuza Mukta| Team Exceldemy

  5. Reply Avatar photo
    Musiha Mahfuza Mukta Mar 9, 2023 at 12:11 PM

    Thank you, MICHAEL KAIR for your comment. As per my understanding, there is no duplicate code. Actually, I have written the code part by part with detail description. Also, in the end, I have attached the complete code for making the Date Picker. That’s why, it seems to you the code is used twice but actually, the last one in Step 7 (last code) is the complete one. You should use only that one in your module. And the other codes are for explanation purpose.
    I hope this will solve your problem. Please let us know if you face any further problems.

    Musiha Mahfuza Mukta,

  6. Reply Avatar photo
    Musiha Mahfuza Mukta Feb 27, 2023 at 10:58 AM

    Thank you ZOYSA for your comment. Below, I have attached two formulas for your problem.
    I have written the data from the A2 cell and C2 cell. According to your question, I have considered the dataset till A10 and C10.
    Firstly, write the below formula in the E2 cell or the cell from where the NAME will be started.

    Then copy this formula up to E10 or your dataset’s end cell.
    Then use another formula in the F4 cell.

  7. Reply Avatar photo
    Musiha Mahfuza Mukta Feb 19, 2023 at 12:33 PM

    Thank you PHUC YU for your comment. Actually, I have tried these methods too and the methods are working perfectly. Here, you can also zoom out the Excel file directly by clicking the Minus(-) sign situated right most corner of the file.

    In case of, you are using an older version than 2013 of Excel then these methods may not work. Or, if you have any bugs or issues in your laptop then these would not work. I thing you were facing a different problem which is not related to this articles.

  8. Reply Avatar photo
    Musiha Mahfuza Mukta Feb 19, 2023 at 11:13 AM

    Thank you ROB for your comment. Here, ROW function is working as the row index number of the INDEX function. Actually, it should be ROW(B2:D9)=> {2;3;4;5;6;7;8;9} which denotes respectively the 2nd, 3rd, 4th…. up to 9th row number of this ($B$4:$D$12) array of INDEX function. Here, we ignore the 1st row of ($B$4:$D$12) this array as 1st row contains the Club Name’s not any player Name’s.
    On the other hand, you have to use B4:D4 row in the MATCH function which will search for the Club Name’s and act as the column index number in the INDEX function.
    Say, when you choose Borussia Dortmund or C4 cell from the list of C14 cell then the MATCH function will return 2 and thus the column index number of INDEX function will be 2 so INDEX function will give all the rows (except 1st one) of 2nd column of the given array which means all the player name’s of the Borussia Dortmund club.
    So, you can use the formula like the below one. =IFERROR(INDEX($B$4:$D$12,ROW(B2:D9),MATCH($C$14,$B$4:$D$4,0)),””)
    Use of INDEX, MATCH, ROW functions

  9. Hello MP ROY,
    Thank you for your comment. I have tried these codes and they are working perfectly, except the code in Example3. For that particular code, you can use a new workbook. While I was using a new workbook the code has been perfectly worked there. But you have to be careful about the name of worksheet. You have to use exact worksheet number and name in the code.
    Team ExcelDemy

  10. Hello, J KUMAR.
    Thank you for your comment. I have tried the code too and the code is absolutely right and perfectly working. But you are facing problem because most probably your device is running out of virtual memory. So, when you are trying this code in Excel, at that time you should close all other applications. Also, you should use an individual module for this code.

  11. Hello, CHRIS.
    Thank you for your comment. Actually, with the help of method 4, you are converting numbers into text. But when you re-entered any new value then the cell will hold that value excluding the apostrophe. Basically, the past value along with the apostrophe completely had gone away. So, if you want to keep the apostrophe then you should select that cell (containing new value) and run the Macros again. Then, you will see the apostrophe again with the new value.

Advanced Excel Exercises with Solutions PDF