Al Ikram Amit

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

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

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

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

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

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