Al Ikram Amit

About author

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant value. He is interested in Data Analysis with MS Excel, AutoCAD, Maxsurf, Rhinoceros, ANSYS, MATLAB, and Python.

Designation

Excel & VBA Content Developer at ExcelDemy in SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

B.Sc. in Naval Architecture & Marine Engineering, BUET.

Expertise

Microsoft Office, AutoCAD, Maxsurf, Rhinoceros, ANSYS, MATLAB, C++, Python

Experience

  • Technical Content Writing
  • Undergraduate Projects
    • Numerical Prediction of Flow Around the Legs of an Offshore Structure.

Latest Posts From Al Ikram Amit

0
EMI Calculator Excel

  Download the Practice Workbook EMI Calculator.xlsx Overview of the EMI Calculation The Equated Monthly Installment, or EMI, is payment for ...

0
How to Create and Remove Floating Cells in Excel

Method 1 - Using Watch Window to Create Floating Cells To make particular info available all the time while scrolling through big data in Excel, add a ...

0
Markers in Excel (Add and Customize)

In this tutorial, we will show how to insert and customize markers in Excel. We will also show how to add pictures as data markers and how to add markers in ...

0
Excel Power Map (Create, Customize & Share)

Download the Practice Workbook Using Power Map in Excel.xlsx How to Enable the Power Map Add-in in Excel Go to File and select Options. An ...

0
How to Make Good Excel Color Combinations (5 Methods)

Method 1 - Using a Color Combination Based on Values Steps: Choose the set of cells containing the values you want to make a good color combination.  We ...

1
Excel VBA Worksheet Change Event Based on Multiple Cells

Example 1 - VBA Worksheet Change Event Based on the Continuous Multiple Cells Steps: In Excel, you can open the VBA Editor by pressing "Alt + F11" or by ...

1
How to Set One Cell Value in Another Worksheet with Excel VBA

Method 1 - Directly Setting One Cell Value in Another Sheet Steps: Press Alt + F11 to open the VBA Macro. Click on the Insert. Select the Module. ...

0
VBA Code to Create Pivot Table with Dynamic Range in Excel

We will use a sample dataset containing First Name, Last Name, Email, and Gross Pay to demonstrate creating a pivot table with a dynamic range. Step ...

0
Using Excel VBA to Declare a Global Variable – 2 Methods

This is an overview. Download Practice Workbook Download the practice workbook. Declare Global Variable.xlsm   Method 1 - “Dim” ...

0
How to Use the Excel VBA INDEX MATCH Function in Another Worksheet – 3 Methods

This is an overview: The sample dataset showcases Names. Date and Quantity sold. The dataset is in sheet1 and the result sheets are sheet2 and sheet ...

0
Sparklines Are Not Showing in Excel (6 Simple Solutions)

Here's a sample dataset with sparklines that we'll troubleshoot. Sparklines Are Not Showing in Excel: 6 Simple Solutions We'll use the sample sales ...

0
How to Concatenate Different Fonts in Excel – 2 Methods

Method 1 - Use the Cells.Value Property in VBA to Join Different Fonts Go to the Developer tab >> select Visual Basic. You can also press Alt + ...

0
How to Circle Something in Excel (2 Suitable Ways)

In this tutorial, we will explain how to circle something in Excel using 2 different methods. We'll use the following data set containing Name, Age, and ...

0
How to Create All Combinations of 4 Columns in Excel (2 Ways)

We have some digits in 4 columns. We are going to merge these columns with multiple combinations. Method 1 - Create All Combinations of 4 Columns ...

0
How to Find Slope of Logarithmic Graph in Excel (with Easy Steps)

By adjusting a few axis format variables in Excel, we can simply plot a log-log graph. Both axes in the log-log or natural log-log graph are really on a ...

Browsing All Comments By: Al Ikram Amit
  1. Dear NU,

    Thank you for your engagement with our content. If you’re using the provided code, please ensure it aligns with your specific dataset and pivot table configuration. To assist you further, could you kindly provide the specific dataset you are working with? Your dataset will be invaluable in ensuring we accurately address your query.

    Alternatively, you may also try the following modified code, which aims to rectify the issue:

    Sub FilterPivotTable()
    Dim filtvalues As Variant
    Dim i As Integer, j As Integer
    Dim pvt As PivotField
    Dim pitm As PivotItem
    ' Set the filter range with values to filter on
    filtvalues = Range("I5:I6")
    ' Set the pivot field object
    Set pvt = ActiveSheet.PivotTables _
    ("SalesPivotTable").PivotFields("Product")
    ' Clear existing filters
    pvt.ClearAllFilters
    ' Apply filter to pivot table
    For i = 1 To pvt.PivotItems.Count
    Set pitm = pvt.PivotItems(i)
    Dim found As Boolean
    found = False
    For j = 1 To UBound(filtvalues, 1) - LBound(filtvalues, 1) + 1
    If pitm.Name = filtvalues(j, 1) Then
    found = True
    Exit For
    End If
    Next j
    If Not found Then
    pitm.Visible = False
    Else
    pitm.Visible = True
    End If
    Next i
    End Sub 

    Note: This modified code includes a boolean variable found to track whether a matching item is found in filtvalues.
    If no match is found, pitm.Visible is set to False to hide the item.
    If a match is found, pitm.Visible is set to True to show the item.
    We appreciate your cooperation and look forward to resolving this matter promptly.

    Best regards,

    Al Ikram Amit

    ExcelDemy

  2. Hi Gracie,

    I see you encountered an issue with the formula provided earlier. The circular reference error occurs because the formula is attempting to reference the same range (M$4:M4) it’s currently located in, which creates a circular dependency.

    To address this, I recommend using a VBA solution to find the top 5 most frequent numbers and their frequencies in the range M4:N9. VBA allows us to perform more complex calculations and avoid circular reference problems.

    To use this VBA code, press Alt+F11 to open the VBA editor in Excel. Then, click Insert>> Module to insert a new module. Copy and paste the code into the module.

    Sub FindTop5FrequentNumbers()
        Dim dataRange As Range
        Dim resultRange As Range
        Dim frequencyRange As Range
        Dim dict As Object
        Dim cell As Range
        Dim i As Integer
        Dim keyArray As Variant    
        ' Set the range that contains your sample data (E2:I57)
        Set dataRange = ThisWorkbook.Worksheets("Sheet1").Range("E2:I57") ' Adjust the sheet name and range as per your data   
        ' Set the range where you want to output the top 5 most frequent numbers (M4:M9)
        Set resultRange = ThisWorkbook.Worksheets("Sheet1").Range("M4:M9") ' Adjust the sheet name and range as per your choice   
        ' Set the range where you want to output the corresponding frequencies (N4:N9)
        Set frequencyRange = ThisWorkbook.Worksheets("Sheet1").Range("N4:N9") ' Adjust the sheet name and range as per your choice  
        ' Create a dictionary to store the frequencies of each number
        Set dict = CreateObject("Scripting.Dictionary")  
        ' Loop through each cell in the data range to count the occurrences of each number
        For Each cell In dataRange
            If IsNumeric(cell.Value) Then
                If dict.Exists(cell.Value) Then
                    dict(cell.Value) = dict(cell.Value) + 1
                Else
                    dict.Add cell.Value, 1
                End If
            End If
        Next cell  
        ' Convert the dictionary keys (numbers) to an array
        keyArray = dict.keys   
        ' Sort the array based on the frequencies in descending order
        For i = LBound(keyArray) To UBound(keyArray) - 1
            For j = i + 1 To UBound(keyArray)
                If dict(keyArray(j)) > dict(keyArray(i)) Then
                    ' Swap elements
                    temp = keyArray(i)
                    keyArray(i) = keyArray(j)
                    keyArray(j) = temp
                End If
            Next j
        Next i
        ' Output the top 5 most frequent numbers and their frequencies to the result and frequency ranges
        For i = 1 To 5
            If i <= UBound(keyArray) + 1 Then
                resultRange.Cells(i, 1).Value = keyArray(i - 1)
                frequencyRange.Cells(i, 1).Value = dict(keyArray(i - 1))
            Else
                resultRange.Cells(i, 1).Value = ""
                frequencyRange.Cells(i, 1).Value = ""
            End If
        Next i
    End Sub

    This VBA code will find the top 5 most frequent numbers in the data range E2:I57 and display them in the range M4:M9 and their corresponding frequencies in N4:N9. Remember to adjust the sheet name(We have used the sheet name “Sheet1“) and range references in the code are matched with your data.


    If you have any further questions or need more assistance, feel free to ask.

    Best regards,
    Al Ikram Amit
    Team ExcelDemy

  3. Dear KEN,

    Thank you for your question. If your data is spread across columns A, B, C, and D, you can still work out the 5 most frequent numbers and their frequencies using Excel formulas. Here’s a step-by-step approach:

    1. Assuming that your data starts from row 5, you can use the following formula in cell F5 to get the 5 most frequent numbers:

    =IFERROR(MODE(IF(COUNTIF(F$4:F4,$A$5:$D$23)={0},$A$5:$A$23)),"")

    1. To calculate the frequency of each of the top 5 most frequent numbers, you can use the following formula in cell G5:

    =COUNTIF($A$5:$D$23,F5)

    1. Copy the formulas down by dragging the fill handle for cells F5:F9 and G5:G9 to get the top 5 most frequent numbers and their frequencies.

    I hope this helps! If you have any further questions, feel free to ask.

    Best regards

    Al Ikram Amit

    Team ExcelDemy

  4. Dear KUNAR,

    Thank you for your comment. It seems that you are encountering an issue with the “Edit Credentials” prompt in Excel. This prompt usually appears when there is a need to specify the connection details or credentials for accessing external data sources. Make sure you have marked the box in the Privacy Levels window.

    If you are still experiencing issues or have any further questions, please let me know, and I’ll be happy to assist you further.

    Best regards

    Al Ikram Amit

    Team ExcelDemy

  5. Dear ASKA,

    Thank you for bringing the mistake to our attention. We apologize for the error in my previous response. You are correct, the name of the table in Excel should be “MyTable,” not “Table1”.

    I apologize for any confusion caused by the incorrect information. If you have any further questions or need assistance with any other topics, please feel free to ask.

    Best regards,

    Al Ikram Amit

    Team ExcelDemy

  6. Dear ATEEB,
    Thank you for reaching out. I’m here to help you with your Excel queries. Regarding your questions about creating alerts and blinking or flashing text in Excel, please find the answers below:
    How to create a pop-up alert in Excel

    To create a pop-up alert in Excel, you can use a combination of VBA (Visual Basic for Applications) code and Excel’s event triggers. Here’s an example code snippet that you can use:

    Copy and paste this code into the sheet module (e.g., press Alt+F11 to open the Visual Basic Editor, find the relevant sheet in the Project Explorer, and double-click on it to open its code window). Replace “$A$1” with the cell reference that should trigger the alert.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$A$1" Then ' Change the cell reference to the desired cell triggering the alert
            MsgBox "Alert: Cell A1 has been changed!" ' Customize the alert message as needed
        End If
    End Sub

    Customize the alert message within the MsgBox function to suit your requirements. After changing cell A1, the alert message will appear.

    How to blink or flash text of a specific cell in Excel

    Excel does not provide a built-in feature to directly make text blink or flash. However, you can achieve a similar effect by using VBA code to toggle the cell’s font color.
    In the following code, we’re toggling the font color between red and black for the specified cell (in this case, cell A1). Adjust the cell reference and customize the number of blinks and the timing (in seconds) to meet your specific requirements.
    Here’s an example code snippet that demonstrates this:

    Sub BlinkText()
    Dim rng As Range
    Set rng = Range("A1") ' Change the cell reference to the desired cell to blink
    For i = 1 To 5 ' Number of times to blink
    rng.Font.Color = RGB(255, 0, 0) ' Change font color to red
    Application.Wait Now + TimeValue("00:00:01") ' Wait for 1 second
    rng.Font.Color = RGB(0, 0, 0) ' Change font color back to black
    Application.Wait Now + TimeValue("00:00:01") ' Wait for 1 second
    Next i
    End Sub
    
    I hope these solutions address your needs. If you have any further questions or need additional assistance, please feel free to ask.
    Best regards
    Al Ikram Amit
    Team ExcelDemy
  7. Dear Fremont,

    Thank you for sharing your experience and the variation you encountered regarding frozen panes in Excel. Your observation highlights an important aspect of freezing panes, particularly when not all initial rows or columns are visible during the freezing process. In such cases, the unfrozen rows or columns that are not displayed can become inaccessible for scrolling.

    Your solution to unfreeze everything is indeed a valid approach to overcome this limitation. By unfreezing all rows and columns, you can regain full control and visibility of the entire worksheet, allowing for seamless scrolling and navigation.

    We appreciate your input and valuable contribution to the discussion. If you have any further questions or need assistance with any other topics related to Excel, please feel free to ask.

    Best regards,

    Al Ikram Amit

    Team ExcelDemy

  8. Dear A,

    Are you using mobile version of Google drive?
    The Settings option in the latest web version of Google drive is as it is in this post. It will be better if you can send a screenshot to our forum (here is our forum link: https://exceldemy.com/forum/). So we can understand what problem you are facing.

    Regards.
    Al Ikram Amit
    Team ExcelDemy

  9. Hello KP MENON,

    Greetings! We appreciate you contacting us and commenting on our Excel blog post with your query. If the VBA code is not working properly in Office 365, you can provide the Excel file here. We will try our best to give the updated version of that VBA code compatible with Office 365. As ExcelDemy is currently providing the best solutions of Excel related problems, feel free to provide your problems in the blogpost.

    Moreover, in the following section, we have provided some troubleshooting options:

    1. When there is a problem with the Excel workbook or the VBA code being executed, the “Run-time error ‘1004’: Application-defined or Object-defined error” commonly occurs. 
    2. References that are either missing or broken could be the result of improperly installed external libraries or references on the laptop running Office 365. Make sure all necessary references are present and correctly selected by checking the VBA project’s references (in the VBA editor, choose “Tools” -> “References”). If any references are missing or are flagged as “MISSING,” you might need to update or reconfigure them to work with Office 365.
    3. Another possibility is that the problem is unique to the file or the environment in which it is being run. To see if the error still occurs, try running the VBA program with Office 365 on a different laptop. 

    You might need to give more information about the VBA code and the events that preceded the error to troubleshoot the specific error more thoroughly. Reviewing the specific line of code where the error appears can also shed light on where the problem originated.

    Regards

    Al Ikram Amit

    Team ExcelDemy

  10. Hello CALEB,

    Greetings! We appreciate you contacting us and commenting on our Excel blog post with your query. We appreciate your interest and are available to help. Enter the following VBA code in your module and correct the range in that code. Hopefully, this will implement the “Enter” function key in Excel which means the code will activate the next blank cell.

    Sub ActivateFirstBlankCell()
        Dim cell As Range
        ' Loop through each cell in the range D5:D11
        For Each cell In Range("D5:D11")
            ' Check if the cell is blank
            If cell.Value = "" Then
                ' Activate the blank cell
                cell.Activate
                Exit Sub ' Exit the loop once the first blank cell is found
            End If
        Next cell
    End Sub

    The result will be like this.

    If you enter =CHAR(13) in cell A1 and =CODE128(A1) in cell B1 in Excel, the following will happen:

    1. Enter will be represented in cell A1 and will display a carriage return character, but it will not be visibly represented. A carriage return is a non-printable control character that represents a line break or the “Enter” key.
    2. In cell B1, the formula =CODE128(A1) will attempt to encode the content of cell A1 as a Code 128 barcode. However, since the content of cell A1 is a non-printable character (carriage return), it may not be directly interpretable by the CODE128 function. The CODE128 function is typically designed to encode printable ASCII characters.

    I’ll be pleased to help you further if you can give me more information or a sample of your Excel sheet if the issue continues.

    Regards

    Al Ikram Amit

    Team ExcelDemy

  11. Hello SUERINA JUILINA,

    Greetings! We appreciate you contacting us and leaving a comment on our Excel blog post with your query. We appreciate your interest and are available to help.

    There are lackings in describing your particular problem. However here is the VBA code that probably can solve your particular problem:

    Sub Page_Number_Selected_Cell()
        Dim mVCount As Integer
        Dim mHCount As Integer
        Dim mVBreak As VPageBreak
        Dim mHBreak As HPageBreak
        Dim mNumPage As Integer    
        mHCount = 1
        mVCount = 1
        If ActiveSheet.PageSetup.Order = xlDownThenOver Then
            mHCount = ActiveSheet.HPageBreaks.Count + 1
        Else
            mVCount = ActiveSheet.VPageBreaks.Count + 1
        End If 
        mNumPage = 1  
        For Each mVBreak In ActiveSheet.VPageBreaks
            If mVBreak.Location.Column > ActiveCell.Column Then Exit For
            mNumPage = mNumPage + mHCount
        Next 
        For Each mHBreak In ActiveSheet.HPageBreaks
            If mHBreak.Location.Row > ActiveCell.Row Then Exit For
            mNumPage = mNumPage + mVCount
        Next   
        Dim totalPages As Integer
        totalPages = 2
        ActiveCell.Value = "Page " & mNumPage & " of " & totalPages
    End Sub

    Finally, “Page 2 of 2” is shown as a Page Number like in the following image:

    I’ll be pleased to help you further if you can give me more information or a sample of your Excel sheet if the issue continues.

    Regards

    Al Ikram Amit

    Team ExcelDemy

  12. Hello MAHIN,

    Greetings! We appreciate you contacting us and leaving a comment on our Excel blog post with your query. We appreciate your interest and are available to help.

    First of all, you have inserted a leading zero by any of the following means:

    • Converting a number into a Text for adding the leading zero
    • Using the TEXT function to add leading zero

    Now, there could be a few reasons why the SUM function in Excel is failing to deliver any results for your data. Here are some recommendations to aid in troubleshooting the issue:

    • The cell is previously formatted as Text. After converting them into the Number from Home tab the problem can still persist.

    • Try to copy your cells and paste them into the new destination. After pasting it in the new cells Trace Error button will appear. Click on that button and select Convert to Number.

    • Here is the final output after converting it to a number.

    • Look for hidden characters or spaces: On occasion, Excel may fail to recognize values in the cells as numbers because of hidden characters or trailing spaces. Use the CLEAN feature to get rid of any concealed characters. For instance, if your data is in cell A1 and you want to clean it up, you can use the formula “=CLEAN(A1)” in another cell, and then use the SUM function on the cleaned values.
    • Look for any mistakes in other cells: The SUM function may occasionally return null if other cells in the range you’re trying to sum include errors like #VALUE! or #DIV/0!. Examine the other cells in the range, and correct any mistakes you find.

    You should be able to fix the Excel SUM function returning null problem using the abovementioned techniques. I’ll be pleased to help you further if you can give me more information or a sample of your Excel sheet if the issue continues.

    Regards

    Al Ikram Amit

    Team ExcelDemy

  13. Hello KAZEM,

    Greetings! We appreciate you contacting us and leaving a comment on our Excel blog post with your query. We appreciate your interest and are available to help.

    The “subscript out of range” error frequently happens when the active sheet does not contain the table with the specified name.

    Please double-check the table name you entered into the InputBox when prompted by the tName variable. Verify again that the table name corresponds to the one on your worksheet.

    Additionally, make sure the table is on the active sheet and not on another sheet. The table is assumed to be present by the code in the active sheet.

    You can try the following troubleshooting steps if the problem continues:

    1. Make sure there are no leading or trailing spaces in the table name and that the spelling is accurate.
    2. Verify that the table is present in the current sheet and not in another sheet.
    3. Make sure the table name is distinct within the worksheet and isn’t shared with any other Excel objects (like named ranges, charts, etc.).
    4. Verify that the appropriate workbook is where you are running the code.

    You should be able to fix the “subscript out of range” error by making sure these things are correct.

    I sincerely hope you find this useful.

    Regards

    Al Ikram Amit

    Team ExcelDemy

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo