User Posts: Yousuf Khan
[Fixed!] Excel Map Chart Not Working (11 Possible Solutions)

Excel Map Chart is an effective tool for delivering insights and displaying data. However, occasionally customers encounter problems with the Map Chart not ...

How to Create Electricity Cost Calculator in Excel (with Easy Steps)

Microsoft Excel is very useful to build a power energy cost calculator. We can use Excel formulas to create the calculator efficiently saving a lot of time and ...

How to Fix When Excel Not Responding Without Losing Data

Has your Microsoft Excel file suddenly ceased to function and begun to display the Excel Not Responding error message? Further, while attempting to operate on ...

How to Create Fixed Rate Mortgage Calculator in Excel

Microsoft Excel computes costs associated with our mortgages, such as interest and monthly payments, which is one of its powerful features. If you are a little ...

How to Find Minimum Value That Is Greater Than 0 in Excel

If you are hoping to find the minimum value greater than zero in your large Excel dataset, you have come to the right place. As business analysts, we use Excel ...

How to Create Maternity Leave Calculator in Excel

Microsoft Excel is a powerful software. We use Excel formulas and functions in our education, business, and workplaces. Moreover, using Excel, HR can track ...

How to Create Fully Automatic Invoice in Excel (with Easy Steps)

Microsoft Excel is a powerful tool. We use Excel Functions in our education, business, and financial analysis. Furthermore, as bookkeepers, we have to build ...

How to Use VBA Intersect Method in Excel (4 Practical Examples)

The entire Excel application is under VBA Application Object. Moreover, Excel provides different application methods like the VBA Intersect method to return a ...

How to Use IF Function with OR and AND Statement in Excel

By checking for a condition and delivering a result if it is TRUE or FALSE, the IF function enables us to logically compare a value to what we anticipate. What ...

How to Use NOMINAL Function in Excel

Microsoft Excel is a handy software. We use Excel functions for our education, business, and financial analyses. Moreover, in light of the effective rate and ...

How to Insert Leader Lines into Doughnut Chart in Excel

Microsoft Excel is a powerful software. We create different charts with leader lines in our business analyses and presentations. Unfortunately, Excel does not ...

How to Calculate Partial Correlation in Excel (3 Suitable Ways)

Microsoft Excel is a powerful software. We use Excel functions and features for our educational, business, and other mathematical analyses such as stock ...

How to Find Average with Blank Cells in Excel (4 Easy Ways)

There are several distinct Microsoft Excel functions for determining the average of integer values. Additionally, there is a quick non-formula way. This ...

How to Hide Filter Arrows from Pivot Table in Excel (3 Easy Ways)

While generating a Pivot Table in Microsoft Excel, various Filed Buttons including Filter Arrows appear automatically at the table headers. As a user, we might ...

[Fixed!] EDATE Function Not Working in Excel

Businesses use Excel functions like EDATE for a variety of tasks, including tracking data records, scheduling, and producing analytical reports. If using Excel ...

Browsing All Comments By: Yousuf Khan
  1. Dear BZOIRO,

    Yes, you can modify the SearchAll function in the given VBA code to search for each word in the cells separately. I have added the modified code in the Excel file below. This modification splits the search value into words using the Split function and loops through each word to find it in the range. If all words are found, the function merges the found ranges into a single range and thoroughly returns it. If any of the words are not found, the function exits and returns Nothing. Here’s how to use this for your case:

    1. Initially, assign Macro “SearchMultipleSheetsIndividually” in the Search command box and click Run.
    Assigning Macro
    2. Afterward, type the word you wish to look for. Also, don’t forget to use the Wildcards (*, ? etc) while searching. For instance, if you wish to look for a cell of 3 words and has “Emily” in the middle, type *Emily* and then perform searching. Alternatively, if it happens to be the first word, try Emily*.
    Search Results

    Download the Excel file to get the modified VBA code and practice by yourself. I hope it works for you.
    Best Regards,
    Yousuf Khan (ExcelDemy Team)

  2. Dear LUNDER,

    My understanding of your statement implies that there is an issue with the calculations being performed in Microsoft Excel, specifically with the trigonometric functions cosine (cos), cosine (cos), and sine (sin) for certain angle values.

    Further, I concur that the error seems to affect the precision of the results starting from the 17th decimal place, which makes the calculated values for the angles cos 90°; cos 270°; sin 180° to be inaccurate. However, it is important to note that the internal representation of floating-point numbers in computers can lead to inaccuracies, especially when working with very small or large numbers, or when performing multiple mathematical operations.

    In these cases, you can take the following steps to resolve this issue.

    1. Use the RADIANS function to convert the angle from degrees to radians before evaluating the trigonometric functions.
    2. Increase the number of decimal places used in the calculations by using the ROUND or ROUNDUP functions.

    Best Regards,
    Yousuf Khan (ExcelDemy Team)

  3. Dear TONY,

    It’s possible that the delay is due to the amount of data being processed by the validation formula. To reduce the delay, you can try the following:

    1. Turn off screen updating and calculation during the execution of the macro to reduce the time it takes to display the changes.
    2. Consider using a different method for data validation, such as using a custom function or a Macro.

    As requested, I have added a revised version of the VBA code with the optimizations under Private Sub Worksheet_Change in Module 3. I hope, these modifications will improve the performance of the code, as it minimizes the amount of visual updates that occur during the process. This will reduce the amount of time it takes for the changes to be made in the sheet as you type in. Download the Excel file and use the desired code.
    Best Regards,
    Yousuf Khan (ExcelDemy Team)

  4. Hello ASRA,

    The Data Type option in the Data tab in Microsoft Excel is available in both the online and offline versions of Excel.

    The offline version of Microsoft Excel is part of the Microsoft Office suite, which can be purchased and installed on your computer. Once you have installed Microsoft Office, you can access Microsoft Excel from the start menu or by double-clicking on an existing Excel file on your computer. The Data Type option in the Data tab will be available in the Excel application after installation. I hope this helps.

    Yousuf Khan (ExcelDemy Team)

  5. Dear GORAN ZORIC,

    Thank you for your comment! We are glad you took the time to review the article. Regarding the trade direction (BUY or SELL), it is actually represented in the Long / Short (L/S) column in the trading journal template. The trade direction entry can be either a “Buy” (long) or “Sell” (short) and the template will automatically add this field after you enter your entry and exit prices. We apologize for any confusion and hope this clarifies the matter. Thank you for bringing this to our attention.

    Yousuf Khan (ExcelDemy Team)

  6. We apologize for the inconvenience. We are aware of the missing source reference and are working on updating the explanation. Further, we recommend using the code given before the explanation. Thank you for bringing this to our attention.

  7. Hello FAHIM,

    Yes, it is possible to highlight rows and columns of multiple active cells using conditional formatting in Microsoft Excel. To do so, you can use the following steps:

    1. Initially, select the cells you want to apply the conditional formatting to.
    2. Afterward, go to the Home tab and click the Conditional Formatting button in the Styles group.
    3. Later, choose New Rule from the drop-down menu.
    4. Next, select “Use a formula to determine which cells to format.”
    5. Meanwhile in the formula box, enter the following formula:
    6. Further, click the Format button and select the fill color you want to use.
    7. Lastly, click OK to close the Format Cells dialog box.
    This approach uses a relative reference for the row and column in the formula, allowing the conditional formatting to adjust automatically as cells are selected.

  8. Hello RUDOLF,

    Thank you for bringing this to our attention! Yes, checking the Data Validation is a good suggestion to find and remove external links in Excel. Although, external links in Data Validation are not directly removable through Data Validation itself. However, by updating the source data for the Data Validation drop-down list, you can remove any external links that may be present.

    Yousuf Khan (ExcelDemy Team)

  9. Hello Mike,

    Thank you for sharing your experience with us!

    I understand you are getting non-clickable link text when you are copying the URL from web browsers. Also, the Paste Special command is not responding which would solve the issue in your case. Unfortunately, Paste Special command does not offer the Paste Link option while pasting web URLs.

    But Excel directly inserts a hyperlink to a copied URL when you:

    1. Copy the URL from a web browser >> select the cell where you want to paste >> paste the URL in the Formula Bar.

    2. Alternatively, you can directly paste the URL to a cell >> go to Formula Bar >> press Enter.

    This will convert the Text link to Hyperlink. You can also use the HYPERLINK function to get clickable URL links(read the article).

    How to Use Excel HYPERLINK Function

    Yousuf Shovon

  10. Hello MISLED READER,

    Thank you for reading our article and your feedback. You have specified an incorrect use of the IPMT function that does not consider the start and end dates. And, you are correct about the fact that the method calculates the interest payable in the 1st period of a 5 year note represented by the hardcoded “1” used in the “PER” section of the formula. The IPMT function returns interest rates based on periods and not specific calendar dates. Thank you for pointing out the article gap to us. We will fix the issue with correct information. For now, we will show you another method here.

    You can use the other Excel basic formula to calculate the interest between two dates. Or, enter a custom VBA function as described below.

    This function will take the loan details, including start and end dates, as input arguments and return the interest amount.
    Here is the syntax and arguments of the CalculateInterest function I have created:

    Function CalculateInterest(Principal As Double, AnnualInterestRate As Double, _
        LoanTermInYears As Integer, CompoundingPeriodsPerYear As Integer, _
        StartDate As Date, EndDate As Date) As Double

    Here are the steps to implement the function:

    1. Save the below VBA code to a Module.

    Function CalculateInterest(Principal As Double, AnnualInterestRate As Double, _
        LoanTermInYears As Integer, CompoundingPeriodsPerYear As Integer, _
        StartDate As Date, EndDate As Date) As Double
        Dim MonthlyInterestRate As Double
        Dim TotalPeriods As Integer
        Dim Interest As Double
        MonthlyInterestRate = AnnualInterestRate / CompoundingPeriodsPerYear
        TotalPeriods = LoanTermInYears * CompoundingPeriodsPerYear
        Interest = -Principal * WorksheetFunction.IPmt(MonthlyInterestRate, _
            DateDiff("m", StartDate, EndDate) + 1, TotalPeriods, 1, 0)
        CalculateInterest = Interest
    End Function

    2. Now, enter the VBA function in cell C11 >> press Enter key to get the interest.

    Using CalculateInterest function in cell C11

    This formula calculates the interest between February 22, 2022, and March 24, 2022, based on the provided loan details.

    Feel free to let us know your future queries and suggestions as we always appreciate them. Thank you.

    Yousuf Shovon

  11. Hello Anthony,

    Please, see the forum post to ignore cells when applying the SORT function.

    Ignoring Null Cells In The Sort Function

    Yousuf Shovon

  12. Hello Neil,

    Yes, there is a way to name the resultant files using Excel VBA. I am modifying the VBA code to split the column into multiple workbooks given above. This code will rename the new files as month names using monthNames () array.

    Here is the modified code:

    Sub SplitSheet_IntoMultipleWorkbooks_BasedOnColumn_withnames()
        Dim objWorksheet As Excel.Worksheet
        Dim nLastRow, nRow, nNextRow As Integer
        Dim strColumnValue As String
        Dim objDictionary As Object
        Dim varColumnValues As Variant
        Dim varColumnValue As Variant
        Dim objExcelWorkbook As Excel.Workbook
        Dim objSheet As Excel.Worksheet
        Set objWorksheet = ActiveSheet
        nLastRow = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row
        Set objDictionary = CreateObject("Scripting.Dictionary")
        For nRow = 2 To nLastRow
            strColumnValue = objWorksheet.Range("C" & nRow).Value
            If objDictionary.Exists(strColumnValue) = False Then
                objDictionary.Add strColumnValue, 1
            End If
        varColumnValues = objDictionary.Keys
        Dim monthNames() As String
        monthNames = Split("January,February,March,April,May,June,July,August,September,October,November,December", ",")
        For i = LBound(varColumnValues) To UBound(varColumnValues)
            varColumnValue = varColumnValues(i)
            Set objExcelWorkbook = Excel.Application.Workbooks.Add
            Set objSheet = objExcelWorkbook.Sheets(1)
            objSheet.Name = monthNames(i Mod 12) & ".xls"
            For nRow = 2 To nLastRow
                If CStr(objWorksheet.Range("C" & nRow).Value) = CStr(varColumnValue) Then
                    nNextRow = objSheet.Range("A" & objSheet.Rows.Count).End(xlUp).Row + 1
                    objSheet.Range("A" & nNextRow).Select
                End If
            ' Save the workbook with the appropriate name
            objExcelWorkbook.SaveAs ThisWorkbook.Path & "\" & monthNames(i Mod 12) & ".xls"
            objExcelWorkbook.Close SaveChanges:=False
    End Sub

    1. Enter the code in a module >> close the Visual Basic window.
    2. Go to the sheet you want to split >> Click on Developer >> Macros.
    3. In Macro dialog, select SplitSheet_IntoMultipleWorkbooks_BasedOnColumn_withnames >> Run.
    Macro Dialog
    4. Consequently, the new files appear with declared names.
    Created Sheets with Names

    Note: The new files are created in .xls format as you requested. You can change the format in the code if you want.

    I hope this was helpful to you. Let me know if you have any further queries.

    Best Regards,
    Yousuf Shovon

  13. Dear Mike,

    I understand you wish to insert a range to have the text as input. Here is the modified code to do so.

    Public Sub SendReminderMail()
        'Declare the variables
        Dim XDueDates As Range
        Dim XRcptsEmails As Range
        Dim xMailContents As Range
        Dim xMailContent2s As Range
        Dim xDueDate As Range
        Dim xRcptEmail As Range
        Dim xMailContent As Range
        Dim xMailContent2 As Range
        Dim xRngDn As Range
        Dim xCrtOut As Object
        Dim xValDateRng As Variant
        Dim xValSendRng As Variant
        Dim k As Long
        Dim xMailSections As Object
        Dim xFinalRw As Long
        Dim CrVbLf As String
        Dim xMsg As String
        Dim xSubEmail As String
        On Error Resume Next
        'To select the date column insert an input box
        Set XDueDates = Application.InputBox("Select the range for Deadline/Due Date column:", "ExcelDemy", , , , , , 8)
        If XDueDates Is Nothing Then Exit Sub
        'Insert an input box for selecting the recipients
        Set XRcptsEmails = Application.InputBox("Choose the range for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
        If XRcptsEmails Is Nothing Then Exit Sub
        'To enter the task Name, insert an input box
        Set xMailContents = Application.InputBox("In your email, choose the range with the Task Name:", "ExcelDemy", , , , , , 8)
        If xMailContents Is Nothing Then Exit Sub
        'To enter the task details, insert an input box
        Set xMailContent2s = Application.InputBox("In your email, choose the range with the Task Details:", "ExcelDemy", , , , , , 8)
        If xMailContent2s Is Nothing Then Exit Sub
        'Count rows for the due dates
        xFinalRw = XDueDates.Rows.Count
        'Set command to open MS Outlook Application
        Set xCrtOut = CreateObject("Outlook.Application")
        'Apply For loop to conduct the operation in each row one by one
        For k = 1 To xFinalRw
            xValDateRng = ""
            xValDateRng = XDueDates.Cells(k).Value
            'Apply If condition for the Due Date values
            If xValDateRng  "" Then
                'Condition set to send mail if the difference between due dates and current date is greater than 1 and less than 7 days
                'Means 1 < X< 7, X = Due Date - Current Date
                If CDate(xValDateRng) - Date  0 Then
                    xValSendRng = XRcptsEmails.Cells(k).Value
                    'Create the subject, body and text contents with the required variables
                    xSubEmail = xMailContents.Cells(k).Value & " on " & xValDateRng
                    CrVbLf = ""
                    xMsg = ""
                    xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
                    xMsg = xMsg & "Text: " & xMailContents.Cells(k).Value & CrVbLf
                    xMsg = xMsg & "Text: " & xMailContent2s.Cells(k).Value & CrVbLf
                    xMsg = xMsg & ""
                    'Create the email
                    Set xMailSections = xCrtOut.CreateItem(0)
                    'Define the position to place the Subject, Body, and Recipients Address
                    With xMailSections
                        .Subject = xSubEmail
                        .To = xValSendRng
                        .HTMLBody = xMsg
                    End With
                    Set xMailSections = Nothing
                End If
            End If
        Next k
        Set xCrtOut = Nothing
    End Sub

    Also, you can insert a data range by adding variables. To know more about this, see this comment.


    We are glad these methods were helpful to you. Though it seems, you are facing issues with the INDEX & MATCH method for multiple columns. If you are talking about the #NA! error using the given formula, it can be solved with an easy step. Just fill up the Product column with the proper value first and enter the given formula afterward. Thus, you will obtain the desired result.

    Try this way and let us know if it works.

    Yousuf Khan Shovon

  15. Hello JACK MACEY,

    I understand you are facing issues while creating the weekly and monthly sheets using the same given code. The provided code works perfectly for Daily frequency. But for Weekly and Monthly intervals, you have to adjust the parameter of the frequencies in the code accordingly.

    In this line below, we have declared the parameter of the frequency.


    We have to add the interval=1wk or interval=1mo parameters to the URL, which specify the Weekly and Monthly intervals.

    Here are the modified codes.

    For Weekly frequency,

        Ticker = Excel.CurrentWorkbook(){[Name="Ticker"]}[Content]{0}[Column1],
        StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content]{0}[Column1],
        EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content]{0}[Column1],
        Source = Csv.Document(Web.Contents(""&Ticker&"?interval=1wk&period1"&StartDate&"&period2"&EndDate), [Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
        #"Changed Type"

    And for Monthly frequency,

        Ticker = Excel.CurrentWorkbook(){[Name="Ticker"]}[Content]{0}[Column1],
        StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content]{0}[Column1],
        EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content]{0}[Column1],
        Source = Csv.Document(Web.Contents(""&Ticker&"?interval=1mo&period1"&StartDate&"&period2"&EndDate), [Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
        #"Changed Type"

    Try this way. And, let me know if it works.

    Yousuf Khan Shovon

  16. Thank you for your very useful suggestion, VAIBHAV SRIVASTAVA. I understand you suggested summing a text string which is much more relatable to this article. And to do so, you can merge the SUM, VALUE, and TEXTSPLIT functions. Here is the combined formula:


    Summing Text Strings

    Don’t hesitate if you have further suggestions for us. Thanks again.

    Yousuf Khan Shovon

  17. Dear COLM,

    Thank you for your very useful suggestion. I am including the note in the article with much appreciation.

    Yousuf Khan.

  18. Hello NISHANTH VJ,

    Thank you for your concern. Though I am afraid I am having trouble understanding the query clearly. Assuming you wish to modify the formula to account for the cost even in different forms every time you check in, you can use the weighted average cost of goods sold (COGS) method. This method calculates the average cost of all goods sold during a period, taking into account the varying costs of different ingredients.

    The formula:

    Food Cost Percentage = (Total Cost of Goods Sold ÷ Total Revenue) x 100

    To calculate the total cost of goods sold, you can use the following formula:

    Total Cost of Goods Sold = (Beginning Inventory + Purchases - Ending Inventory) x Weighted Average Cost per Unit

    To calculate the weighted average cost per unit, you can use the following formula:

    Weighted Average Cost per Unit = Total Cost of Goods Available for Sale ÷ Total Units Available for Sale

    By using this modified formula, you can account for the fluctuations in the cost of food and beverage and calculate your food cost percentage accurately, even if the cost of ingredients varies over time.

    Yousuf Khan Shovon


    I understand your frustration with trying to find specific INDEX/MATCH assistance for your Excel project. Here, I’ll create the Summary tab using INDEX/MATCH functions to connect the other four tabs.

    To begin, let’s assume that your four tabs are named Dataset1, Dataset2, Dataset3, and Summary. To retrieve data from the Dataset1 tab, use the following formula:


    Note: Be sure to update the tab name and range of cells to match the appropriate tab and data range.

    I hope this helps! Let me know if you have any further questions.

    Yousuf Khan Shovon

  20. Hello FRANK,

    Glad to know the basic methods were useful to you. We further appreciate your valuable insights in addition to these methods. We will look into the advanced techniques and add them in the future. Thank you.

    Yousuf Khan

  21. Hello ARCHIE,

    I understand you wish to fix the #VALUE! error for VLOOKUP. In your case, the reason this is returning #VALUE! error for alpha-numeric values is that it is trying to convert the text values to numbers using the VALUE function. The VALUE function is designed to convert text that represents a number to an actual number. When it encounters alpha-numeric values, it cannot convert them to numbers, hence the error.

    Fortunately, you can fix this by modifying the formula by replacing the VALUE function with the TEXT function. The TEXT function converts a value to text in a specific number format. Try the modified formula given below:
    Let me know with a demo dataset if you still face issues. Good luck!
    Note: Make sure data are sorted in ascending to descending order in the lookup table.
    Yousuf Khan

  22. Hello MONTY,

    Thank you so much for your useful suggestion. Our Exceldemy team will include the note in the article as soon as possible. We always appreciate any further concerns or suggestions you may have.

  23. Hello ADITYA,

    You’re welcome! We are glad to hear that you found the solution to your issue. Unprotecting the workbook can indeed resolve the issue of being unable to set the visible property of the Worksheet class. It’s great to see that your dynamic VBA code is working as expected. Let us know if there’s anything else we can help with.

    Yousuf Khan

  24. Hi MICHAEL,

    The errors you’ve mentioned could indicate that the workbook is either very hidden or corrupted. There are two levels of worksheet hiding: hidden and very hidden. From a user’s perspective, the difference is that a very hidden sheet cannot be made visible through the Excel user interface, and the only way to unhide it is with VBA.

    Since you’ve used VBA to work with the worksheets and they still appear hidden, you can try a different VBA code to check if they’re very hidden and unhide them. Here’s the VBA code to unhide all very hidden sheets:

    Sub UnhideVeryHiddenSheets()
      Dim wks As Worksheet
      For Each wks In Worksheets
          If wks.Visible = xlSheetVeryHidden Then wks.Visible = xlSheetVisible
    End Sub

    Note: This code only works for very hidden sheets, not worksheets that are hidden normally. If you want to display all hidden sheets, use the code below.

    Sub UnhideAllSheets()
      Dim wks As Worksheet
      For Each wks In ActiveWorkbook.Worksheets
        wks.Visible = xlSheetVisible
      Next wks
    End Sub

    Give it a try and let me know if it works.

    Best Regards,
    Yousuf Khan

Advanced Excel Exercises with Solutions PDF