Shamima Sultana

About author

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft Office Suites, and Excel to Data Analysis, Data Science, VBA, Python, and developing Excel Applications.

Designation

Project Manager at ExcelDemy in SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

B.sc in Computer Science and Engineering (CSE), East West University.

Expertise

Data Analysis, Content Writing, C, C++, Python, JavaScript (Basic), HTML, SQL, MySQL, PostgreSQL, Microsoft Office.

Experience

  • Project Management
  • Technical Content Writing
  • Team Management
  • Software Engineer
    • Data Architecture [Analysis and Diagram]
    • Data Governance [Using Excel and Postgre SQL]
    • 3D Object Visualization [Using Python]
  • Undergraduate Projects
    • Industrial Visit Planning Website
    • Database Management System
    • Factoid Question Answering System (over Bangla Comprehension)

Summary

  • Currently working as Project Manager of ExcelDemy
  • Started technical content writing of Excel & VBA in October 2021 later prompted as Team Leader for Content Management.
  • I worked at Adipster Tech Limited as a Software Engineer from November 2020 – May 2021. I’ve Done several real-life projects and documented several projects with analytical reports.

Research & Publication

Latest Posts From Shamima Sultana

0
Advanced Data Cleaning: Power Query vs. Google Sheets’ REGEX

Let us show you how to use Power Query and Google Sheets’ REGEX functions for advanced data cleaning while comparing them.

0
10 Free Excel Templates

This post will provide the most useful 10 free Excel templates that can help you streamline various aspects of your work.

0
Creating a Full Google Sheets App with Forms and Scripts

In this tutorial, we will create a complete Google Sheets App with Forms and Scripts.

0
Using Python with Excel

In this tutorial, we will use Python with Excel to read from and write in spreadsheets using the libraries.

0
5 Advanced Excel Techniques for Financial Modeling

This post will show 5 advanced techniques for financial modeling.

0
Supercharging Excel with Python

This tutorial will show how you can supercharge Excel with Python within Excel and via traditional external libraries.

0
Create Stunning Charts in Excel: Tips and Tricks from the Pros

This tutorial will show you tips and tricks to create stunning charts in Excel.

0
Excel Interest Rate Calculator

Excel Interest Rate Calculator: Knowledge Hub Effective Interest Method of Amortization Excel Interest Rate Swap Calculation Excel Interest Rate ...

0
How to Convert Currency to a Number in Excel (6 Easy Ways)

We'll use a single column with some values in the Currency format. Method 1 - Using the Number Format to Convert Currency to Numbers Select the ...

0
How to Set Excel Column Width to cm Unit (with Easy Steps)

To demonstrate how to change the column width into cm (centimeters) in Excel, we'll use a dataset of order details with multiple columns. We've used ...

0
How to Create Proforma Invoice for Advance Payment in Excel

In this article, we will describe in detail how to create a proforma invoice for advance payment in Excel. A Proforma invoice is used to request payment from a ...

3
How to Format a Phone Number with Extension in Excel – 3 Methods

The image below showcases phone numbers with extensions before and after formatting: Method 1 - Using a Custom Number Format Each phone number ...

1
The Hyperlink in Excel is Not Working  -3 Solutions

The sample dataset contains Hyperlinks. Solution 1 - The Pound (#) Sign is Not Accepted in Hyperlinks If the link contains the Pound (#) sign, it ...

4
How to Sort Numbers in Excel (8 Quick Ways)

We'll use a sample dataset that contains the salary information of a particular employee with the Employee Name, Region, and Salary columns. How to ...

2
How to Remove Percentage in Excel (5 Quick Ways)

Method 1 - Remove Percentage Using General Format in Excel Select the cell or cell range from where you want to remove the percentage. Open the Home tab ...

Browsing All Comments By: Shamima Sultana
  1. Hi Arda
    Hope you are doing well.

    I checked the code you mentioned above and it works. To make it more clear I’m attaching some images with the code.

    Here, I tried the exact code in the same dataset.
    MsgBox Range("E5").End(xlToRight).Offset(0, 1).Address

    You can see the result $G$5.

    Again I changed the dataset slightly.

    Here, the result is also based on the location.

    NB. If it doesn’t help you then please send your dataset to [email protected] or [email protected]

    Thanks
    Shamima Sultana
    ExcelDemy

  2. Hello Saini Dauge,

    I’d be glad to help! To set up a daily warehouse report for automotive settings, we can track essential data like incoming/outgoing parts, stock levels, and daily processing metrics. If you can provide specifics on what you need, like tracking inventory or workflow stages, I can suggest a template and formulas that would work best for you.

    Regards
    ExcelDemy

  3. Hello Peter,

    Thanks for your appreciation, it means a lot to us. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  4. Hello Cass,

    Thanks for your appreciation. Here’s the updated VBA code with adjustments to skip Sundays and continue from Monday.

    
    Sub SEQUENTIAL_DATES_EXCLUDE_SUNDAYS()
        Dim active_workbook As Workbook
        Set active_workbook = ActiveWorkbook    
        Dim sheet_arr() As Variant
        Dim total_sheets As Variant
        total_sheets = active_workbook.Sheets.Count - 1
        ReDim sheet_arr(total_sheets)
        
        Dim i As Integer
        For i = 0 To total_sheets
            sheet_arr(i) = active_workbook.Sheets(i + 1).Name
        Next i
        
        Dim First_Date As Date
        First_Date = DateValue(InputBox("Enter the First Date: "))
        Dim Increment As Integer
        Increment = CInt(InputBox("Enter the Increment: "))
        
        Dim Count As Integer
        Count = 0
        Sheets(sheet_arr(0)).Range(Selection(1).Address).Formula = First_Date
        
        Dim j As Integer
        Dim k As Variant
        Dim currentDate As Date
        currentDate = First_Date    
        For j = 0 To UBound(sheet_arr)
            For Each k In Selection
                ' Skip Sundays
                Do While Weekday(currentDate, vbMonday) > 6
                    currentDate = currentDate + 1
                Loop
                Sheets(sheet_arr(j)).Range(k.Address) = currentDate
                currentDate = currentDate + Increment
            Next k
        Next j
    End Sub

    This code skips Sundays by checking if the weekday of currentDate is Sunday (Weekday(currentDate, vbMonday) > 6). If it is, it advances to the next date (Monday).
    It continues filling dates across the sheets with the specified increment, excluding Sundays.

    Regards
    ExcelDemy

  5. Hello Adam,

    Thank you for reaching out! For assistance with finding details on pending and ongoing investment and investigation cases, as well as the locations of bonds and bail companies in Nueces County, I recommend contacting the local court or county records office.
    They should have up-to-date information on bonds, bail companies, and related public records. Alternatively, consulting with a legal advisor may help provide the most accurate insights based on your needs. Let us know if there’s anything specific we can help with!

    Regards
    ExcelDemy

  6. Hello Dear,

    You are most welcome. Thanks for your appreciation. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  7. Hello Jick Sylvanus,

    To list only the names of all Physics teachers using INDEX and MATCH, you can combine these with FILTER (if available) or an IF array formula.

    You can use FILTER (for newer Excel versions) if you have Excel 365 or Excel 2019:

    =FILTER(A2:A100, C2:C100=”Physics”)
    This will show all names in column A where the discipline in column C is “Physics”.

    You can use INDEX-MATCH, if FILTER is unavailable.

    =IFERROR(INDEX(A$2:A$100, SMALL(IF(C$2:C$100=”Physics”, ROW(A$2:A$100)-ROW(A$2)+1), ROW(1:1))), “”)

    To use this array formula (press Ctrl+Shift+Enter after typing).
    Drag this down to list all Physics teachers. Let me know if you need further help!

    Regards
    ExcelDemy

  8. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear that our clear instructions helped you to share your Excel file easily. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  9. Hello Asger,

    It seems like there are still a few challenges with the date format and the auto-hide functionality. Here’s a refined approach:

    Let’s force the date format explicitly in each control to avoid any discrepancy. By handling it this way, we ensure all dates display in dd/mm/yyyy.

    Update the Create_Calendar procedure as follows:

    Sub Create_Calendar()
    
        Dim formatString As String
        formatString = "dd/mm/yyyy"  ' Enforce a consistent date format
    
        For i = 1 To 42
            Dim currentDate As Date
            currentDate = DateAdd("d", i - Weekday(CDate(Month_Box.Value & "/1/" & Year_Box.Value)), _
                                   CDate(Month_Box.Value & "/1/" & Year_Box.Value))
            
            Controls("C" & i).Caption = Format(currentDate, "d")
            Controls("C" & i).ControlTipText = Format(currentDate, formatString)
            
            ' Set highlight for current month dates and today's date
            If Format(currentDate, "mmmm") = Month_Box.Value Then
                Controls("C" & i).BackColor = &HFFFFFF
                Controls("C" & i).Font.Bold = True
                If Format(currentDate, formatString) = Format(Date, formatString) Then Controls("C" & i).SetFocus
            Else
                Controls("C" & i).BackColor = &H8000000F
                Controls("C" & i).Font.Bold = False
            End If
        Next i
    
    End Sub

    If the calendar is not hiding after selection, it may be that the event isn’t firing as expected. Try placing the following line inside each button’s event handler where a date is selected:

    Private Sub C1_Click()
        DateTextBox.Value = Format(DateAdd("d", 0, Calendar1.Value), "dd/mm/yyyy") ' Use consistent date format
        Calendar1.Visible = False  ' Hide calendar after selecting a date
    End Sub

    If you encounter issues with function names, ensure they match throughout the code, especially when calling or referencing functions. This should clear up any lingering format or visibility issues.

    This should address both format consistency and auto-closing the calendar. Let me know if this resolves it, or if I can assist further!

    Regards
    ExcelDemy

  10. Hello Joe,

    You can insert a character between each word in cells with multiple words using Excel’s SUBSTITUTE function combined with TRIM and FIND functions**. Here’s one way to do it:

    Replace Each Space with the Character: Use a formula like:

    =SUBSTITUTE(A1, ” “, “|”)

    This replaces each space in A1 with |, creating the output you’re looking for, e.g., john|Dole|facility|open|tomorrow. Apply this to each cell for consistent results.

    Regards
    ExcelDemy

  11. Hello Aiza,

    To calculate total sales, use the SUM function. For example, if your sales data is in cells B2 to B10, the formula is:

    =SUM(B2:B10)

    To sum values based on specific criteria, use SUMIF. For example, to sum sales over $100 in the same range, use:

    =SUMIF(B2:B10, “>100”)

    The SUMIF formula helps you total values only when they meet your chosen criteria. Let me know if you need more details!

    Regards
    ExcelDemy

  12. Hello Bikash Neupane,

    You are most welcome. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  13. Hello Manpreet Kaur kaur,

    You are most welcome. Thanks for your appreciation. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  14. Hello Santiago,

    You can handle this by using a custom cell format. To replace 00.00.0000 with a placeholder (like #) or hide it, try using this custom format: dd.mm.yyyy;@;.
    You can add ; after each section to control how positive, negative, zero, and text values display, respectively.
    For example, dd.mm.yyyy;@;#;”” will show # for zero values. Custom formatting, however, only changes the display, not the underlying data.

    Regards
    ExcelDemy

  15. Hello Edel Whelan,

    It looks like you’re encountering a problem with the formatDateTime function in Power Automate. This issue often arises if the syntax for the formatDateTime function is incorrect or if there’s a typo.
    Double-check that you’re using the function as formatDateTime(triggerOutputs()?[‘headers’][‘Date’],’yyyy-MM-dd’) or a similar format. Ensure that the date format aligns with Power Automate’s requirements and that all parentheses are correctly placed.

    Regards
    ExcelDemy

  16. Hello Morea Steven,

    You are most welcome. Glad to hear that our step by step process helped you to create a paysilp. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  17. Hello Bouchaib,

    Welcome to ExcelDemy. You can offer your solution in our <a href=”https://exceldemy.com/forum/” rel=”noopener” target=”_blank”>ExcelDemy Forum.

    Regards
    ExcelDemy

  18. Hello Jeremy Brundrett,

    Hope you are doing well. Feel free to post any queries regarding Excel or this article. We are here to help you. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  19. Hello Asger

    You are most welcome. However, different formats might show due to mixed regional settings or VBA interpreting some dates incorrectly. To enforce consistency, we can modify the code to always use a single format, regardless of system settings.

    Here’s an updated version that ensures the date format is consistently dd/mm/yyyy:

    
    Sub Create_Calendar()
        
        Dim formatString As String
        formatString = "dd/mm/yyyy" ' Set a consistent date format
        
        For i = 1 To 42
            If i < Weekday(CDate(Month_Box.Value & "/1/" & Year_Box.Value)) Then
                Controls("C" & i).Caption = Format(DateAdd("d", i - Weekday(CDate(Month_Box.Value & "/1/" & Year_Box.Value)), _
                CDate(Month_Box.Value & "/1/" & Year_Box.Value)), "d")
                
                Controls("C" & i).ControlTipText = Format(DateAdd("d", i - Weekday(CDate(Month_Box.Value & "/1/" & Year_Box.Value)), _
                CDate(Month_Box.Value & "/1/" & Year_Box.Value)), formatString)
                
            ElseIf i >= Weekday(CDate(Month_Box.Value & "/1/" & Year_Box.Value)) Then
                Controls("C" & i).Caption = Format(DateAdd("d", i - Weekday(CDate(Month_Box.Value & "/1/" & Year_Box.Value)), _
                CDate(Month_Box.Value & "/1/" & Year_Box.Value)), "d")
                
                Controls("C" & i).ControlTipText = Format(DateAdd("d", i - Weekday(CDate(Month_Box.Value & "/1/" & Year_Box.Value)), _
                CDate(Month_Box.Value & "/1/" & Year_Box.Value)), formatString)
            End If
            
            ' Highlight current month and set focus on today’s date
            If Format(DateAdd("d", i - Weekday(CDate(Month_Box.Value & "/1/" & Year_Box.Value)), _
            CDate(Month_Box.Value & "/1/" & Year_Box.Value)), "mmmm") = Month_Box.Value Then
                
                Controls("C" & i).BackColor = &HFFFFFF
                Controls("C" & i).Font.Bold = True
                
                If Format(DateAdd("d", i - Weekday(CDate(Month_Box.Value & "/1/" & Year_Box.Value)), _
                CDate(Month_Box.Value & "/1/" & Year_Box.Value)), formatString) = Format(Date, formatString) Then
                    Controls("C" & i).SetFocus
                End If
                
            Else
                Controls("C" & i).BackColor = &H8000000F
                Controls("C" & i).Font.Bold = False
            End If
        Next i
    
    End Sub
    

    To ensure the calendar automatically hides after a date is selected, you can modify the code to make the calendar invisible after a date is picked. Add the following code to the Calendar1_Click event, which will trigger each time a date is chosen:

    
    Private Sub Calendar1_Click()
        DateTextBox.Value = Format(Calendar1.Value, "dd/mm/yyyy")  ' Use consistent date format
        Calendar1.Visible = False  ' Hide the calendar automatically after selection
    End Sub

    If you’re using buttons for each day as clickable dates, include the Calendar1.Visible = False line in the click event for each button:

    
    Private Sub C1_Click()
        DateTextBox.Value = Format(DateAdd("d", 0, Calendar1.Value), "dd/mm/yyyy")  ' Adjust format here
        Calendar1.Visible = False
    End Sub
    

    This approach ensures the calendar closes automatically after a date selection. Let me know if this solves it!

    Regards
    ExcelDemy

  20. Hello Hans Hallebeek,

    You are most welcome. Thanks for your feedback and the tip. It will be useful for our users. Keep sharing Excel tips with ExcelDemy!

    Regards
    ExcelDemy

  21. Hello,

    Thanks for your appreciation. Glad to hear that our explanations is helpful to you. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  22. Hello Asger,

    Please download the Excel file to get the fresh code.

    Regards
    ExcelDemy

  23. Hello Johnny Laguna,

    You are most welcome. Glad the solution helped you reclaim your spreadsheet from those endless columns. It’s great to hear that Command-Shift worked perfectly on your Mac for selecting and deleting the extra columns. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  24. Hello Julian Sanjeev,

    Thank you for your kind words! To modify the hyperlink so that it references a friendly name in B2 instead of A3, you can adjust the formula accordingly. For linking to specific cells within the same sheet, simply use the cell references directly in your hyperlink formula.
    For example, you can use =HYPERLINK(“#Sheet1!B2”, “January Go”) to create links to specific cells. If you need further assistance, feel free to ask!

    Regards
    ExcelDemy

  25. Hello Tashia Bramhan,

    It seems there might be issues with the named ranges or the way validation is being applied to multiple ranges. Here are some potential fixes:

    Check Named Ranges: Ensure ValidationOptions, FBCommentsValidation, and Resolved-Pending are named ranges defined in the workbook. Validation won’t work if any names are undefined or misspelled.

    Order of Operations: Refreshing the workbook (ActiveWorkbook.RefreshAll) between each validation setup may be unnecessary and could disrupt the code flow. Try placing it at the end only.

    Direct Range Reference: Instead of Range(“Export[Distributor Comments]”), try specifying cell ranges directly, like Range(“A1:A10”).

    Regards
    ExcelDemy

  26. Hello Andrea L McCormack,

    Yes, you can adapt the FV function to calculate a balloon payment with extra quarterly principal payments. To do this, you would adjust the payment parameter in the FV function to include both the regular payment and the extra principal.
    However, depending on the loan structure, this might require setting up a more detailed cash flow model. Excel’s PMT and IPMT functions can also be useful for tracking how each payment impacts the remaining balance leading to the final balloon payment.

    Let’s consider an example:
    Loan amount: $50,000
    Interest rate: 5% annually (1.25% quarterly)
    Loan term: 5 years (20 quarters)
    Regular quarterly payment: $1,000
    Additional quarterly payment: $200

    Determine the adjusted quarterly payment: Sum your regular payment and extra principal payment:
    1000+200=1200

    Use the FV formula: In Excel, use =FV(rate, nper, pmt, pv):
    =FV(1.25%, 20, -1200, -50000)

    The result will be the remaining balance after 20 quarters, giving you the balloon payment amount at the end of the term with quarterly extra payments.

    Regards
    ExcelDemy

  27. Hello Nacho,

    Thank you for sharing this helpful workaround! It’s good to know that disabling Clipboard History can sometimes resolve this stubborn issue.
    If anyone else is still facing freezing when copying and pasting, trying this method could be an effective alternative. It’s also worth restarting Excel and checking for any updates, as these sometimes impact clipboard behavior.
    Thanks again, and we appreciate your contribution!

    Regards
    ExcelDemy

  28. Hello,

    You are most welcome. Glad to hear that our solution helped you to solve copy-paste issue. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  29. Hello Rebecca A,

    You are most welcome. Glad to hear that the method 5 solved your problem. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  30. Hello Adriaan,

    Thank you for your feedback! The issue you mentioned may be due to saving the workbook as a regular .xlsx file, which doesn’t retain macros. Try saving the file as a .xlsm (macro-enabled workbook) instead. This should keep the macro intact even after closing and reopening Excel. Let me know if this solves the problem!

    Regards
    ExcelDemy

  31. Hello,

    You are most welcome. Glad to hear that solution worked for you to fix the issue. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  32. Hello JW,

    You are most welcome. Thank you for your feedback! Follow the given steps to solve your queries.

    Extracting the full name of the commenter: You can modify your macro to extract the full name by using the .Author property instead of the initials.
    .Cells(j, 2).Value = cmt.Author ‘This extracts the full name of the commenter

    Troubleshooting the extraction of page numbers: The issue with extracting page numbers could be related to how the document is structured or the version of Word you’re using. To troubleshoot, try using this code to ensure it’s correctly identifying the page.

    .Cells(j, 4).Value = cmt.Scope.Information(wdActiveEndPageNumber)

    Double-check that your document has page numbers and that cmt.Scope refers to the correct part of the comment range. You could also try using wdStartOfRange or wdEndOfRange in case the wdActiveEndPageNumber isn’t providing accurate results.

    Regards
    ExcelDemy

  33. Hello Ty Calkins,

    The issue with your formula lies in the use of the IF function. In Excel, IF requires three arguments: a condition, a result if the condition is true, and a result if the condition is false. You’re missing those components in your current formula.
    Correct Formula:

    =IF(ISERROR(VLOOKUP(F52,C17:G24,5,FALSE)), “Value not found”, VLOOKUP(F52,C17:G24,5,FALSE))

    VLOOKUP(F52, C17:G24, 5, FALSE): This looks for the value in F52 within the range C17:G24 and returns the value from the 5th column.
    ISERROR: This checks if the VLOOKUP produces an error (e.g., if the value is not found).
    “Value not found”: This is the result if the VLOOKUP results in an error.
    VLOOKUP(F52, C17:G24, 5, FALSE): This is the result if the VLOOKUP successfully finds a match.

    Regards
    ExcelDemy

  34. Hello,

    You are so welcome. Thanks for your feedback and appreciation. Glad to hear that the step by step guide is easy to follow for mail
    merging. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  35. Hello Andy,

    Thank you for your valuable input! Including the root of the server in the “Trusted Sites” section of Internet Options is an important step when working in intranet environments.
    By adding the server path using file://192.168.0.#, you ensure that macros and other features dependent on file access work smoothly across the network.
    Additionally, checking the “Require server verification” option adds an extra layer of security by verifying the server’s authenticity. This is particularly useful in enhancing both functionality and security when dealing with macros on internal networks.

    Regards
    ExcelDemy

  36. Hello Spencer Kapazira,

    You are so welcome. Glad to hear that you got the data set. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  37. Hello,

    You are so welcome. Thanks for your feedback and appreciation. Glad to hear that the step by step instructions made it easy to create a leave record. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  38. Hello Ben,

    You are most welcome. You can modify the VBA code to narrow the search down to a specific column and display all the information for the matching row. You will need to Update the search logic to limit the search to your desired column, e.g., the “Location” column.

    Sub SearchMultipleSheets()
        Main_Sheet = "VBA"
        Search_Cell = "B5"
        SearchType_Cell = "C5"
        Paste_Cell = "B9"
        Searched_Sheets = Array("Dataset 1", "Dataset 2")
        Searched_Ranges = Array("B5:F23", "B5:F23")
        Copy_Format = True
        Search_Column = 2 ' Adjust this to the correct column number for "Location"    
        ' Clear previous search results
        Last_Row = Sheets(Main_Sheet).Range(Paste_Cell).End(xlDown).Row
        Last_Column = Sheets(Main_Sheet).Range(Paste_Cell).End(xlToRight).Column
        Set Used_Range = Sheets(Main_Sheet).Range(Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column), Cells(Last_Row, Last_Column))
        Used_Range.ClearContents
        Used_Range.ClearFormats    
        ' Get search term and search type
        Value1 = Sheets(Main_Sheet).Range(Search_Cell).Value
        If Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Sensitive" Then
            Case_Sensitive = True
        ElseIf Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Insensitive" Then
            Case_Sensitive = False
        Else
            MsgBox ("Choose a Search Type.")
            Exit Sub
        End If    
        ' Search through sheets and ranges
        Count = -1
        For S = LBound(Searched_Sheets) To UBound(Searched_Sheets)
            Set Rng = Sheets(Searched_Sheets(S)).Range(Searched_Ranges(S))
            For i = 1 To Rng.Rows.Count
                Value2 = Rng.Cells(i, Search_Column).Value ' Search only in the specified column
                If PartialMatch(Value1, Value2, Case_Sensitive) = True Then
                    Count = Count + 1
                    Rng.Rows(i).Copy ' Copy entire row if match found
                    Set Paste_Range = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row + Count, Range(Paste_Cell).Column)
                    If Copy_Format = True Then
                        Paste_Range.PasteSpecial Paste:=xlPasteAll
                    Else
                        Paste_Range.PasteSpecial Paste:=xlPasteValues
                    End If
                End If
            Next i
        Next S    
        Application.CutCopyMode = False
    End Sub
    Function PartialMatch(Value1, Value2, Case_Sensitive)
        Matched = False
        If Case_Sensitive = True Then
            If InStr(1, Value2, Value1, vbBinaryCompare) > 0 Then
                Matched = True
            End If
        Else
            If InStr(1, LCase(Value2), LCase(Value1), vbTextCompare) > 0 Then
                Matched = True
            End If
        End If
        PartialMatch = Matched
    End Function

    1. Added Search_Column to specify which column to search in (you can adjust this to match your “Location” column).
    2. The PartialMatch function checks the cell value in the specified column and copies the entire row if a match is found.

    Regards
    ExcelDemy

  39. Hello Selinay,

    The GET.CELL function retrieves specific information about a cell. In this case, 38 is the code for the background color index of the cell. The formula returns the color index of cell B5 in the ‘Get Cell’ sheet.

    The “38” is a code used in the GET.CELL function to retrieve the color index of a cell. It identifies the background color.
    If the you want to get different information, such as font color or cell format, you can replace “38” with the appropriate code.

    Here is a list of some GET.CELL function codes:

    38: Returns the background color of the cell.
    24: Returns the font color.
    63: Returns if the cell contains a formula (TRUE/FALSE).
    17: Returns the number format of the cell.
    50: Returns the width of the cell.
    64: Returns if the cell is locked (TRUE/FALSE).

    These codes help retrieve specific cell information.

    GET.CELL is a legacy Excel function, which needs to be used within named ranges to work properly in modern versions of Excel.

    Regards
    ExcelDemy

  40. Hello,

    You are so welcome. Thanks for your feedback and appreciation. Glad to hear that the step by step guide is helpful to you. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  41. Hello Devindi,

    To copy this VBA Date picker in each row in my your table, you will need to create a dynamic date picker.

    Here are the steps to create a dynamic date picker in each row of your Excel table:

    1. Set up the UserForm with a date picker control (as shown in the article).

    2. Use the Worksheet_SelectionChange event to trigger the date picker when a cell in your “Participation Date” column is selected.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        ' Specify the column for participation dates
        If Not Intersect(Target, Me.Columns("C")) Is Nothing Then
            ' Show the DatePicker when a cell in column C is selected
            DatePickerForm.Show
        End If
    End Sub

    3. Capture the date in the DatePickerForm code, handle the selection:

    Private Sub DatePicker1_Change()
        ' Set the active cell's value to the selected date
        ActiveCell.Value = DatePicker1.Value
        Unload Me
    End Sub

    Once the date is picked, insert it into the active cell.

    Ensure that the date picker works dynamically across all rows of your table.

    Regards
    ExcelDemy

  42. Hello Muhidin Tahir,

    Thanks for your appreciation. Glad to hear that our community services are helping Excel users. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  43. Hello,

    Sorry for the inconvenience. You can create the the test case sheet from this image.

    Regards
    ExcelDemy

  44. Hello Fortune,

    In Your Products list you can add products of your choice. You will need to update the range according to your product list.

    Regards
    ExcelDemy

  45. Hello Saqib Haroon,

    In this article we used Excel’s stocks data to import stocks. Here is the list of Stocks financial data sources.
    Saudi Arabia and Pakistan markets stock prices are not listed in Microsoft Excel stocks. You can use the Yahoo Finance API to get the stock prices if stocks are listed in there.

    Regards
    ExcelDemy

  46. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear that the article’s step by step explanations is helpful to you. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  47. Hello Mostain,

    You are most welcome and thank you for your kind words! We will definitely consider creating a tutorial on this. Meanwhile, a VBA solution would work well for your needs.
    Here’s a VBA solution to search through a closed workbook, retrieve matching rows from all sheets, and paste them into the active workbook without case sensitivity:

    Sub SearchInClosedWorkbook()
        Dim wbPath As String
        Dim wb As Workbook, ws As Worksheet
        Dim searchValue As String
        Dim cell As Range, lastRow As Long, foundCell As Range
        Dim openWB As Workbook  
        wbPath = "C:\Path\To\Your\MasterWorkbook.xlsx" ' Update with your path
        searchValue = InputBox("Enter search value:")
        
        Set openWB = ActiveWorkbook ' Set reference to your active workbook
        Set wb = Workbooks.Open(wbPath) ' Open the closed workbook    
        For Each ws In wb.Sheets
            Set foundCell = ws.Cells.Find(What:=searchValue, LookAt:=xlPart, MatchCase:=False)
            If Not foundCell Is Nothing Then
                lastRow = openWB.Sheets(1).Cells(openWB.Sheets(1).Rows.Count, 1).End(xlUp).Row + 1
                ws.Rows(foundCell.Row).Copy Destination:=openWB.Sheets(1).Cells(lastRow, 1)
            End If
        Next ws    
        wb.Close False ' Close the master workbook
    End Sub

    This code prompts for a search term, opens a closed workbook, finds the value across all sheets (ignoring case sensitivity), and copies the entire row to the last row of the active workbook. Simply update the workbook path, and it should work perfectly for your needs!

    Regards
    ExcelDemy

  48. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear that the article’s step by step explanations is helpful to you. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  49. Hello Lili,

    You are most welcome. You can adapt Method 1 from this article on sending emails based on dates. Here’s an updated VBA code snippet that checks if the date in column B matches today’s date before sending the email.

    Sub SendEmailsBasedOnDates()
        Dim ws As Worksheet
        Dim i As Long, LastRow As Long
        Dim EmailDate As Date
        Dim OutApp As Object, OutMail As Object
    
        Set ws = ThisWorkbook.Sheets("Sheet1")
        LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        For i = 2 To LastRow
            EmailDate = ws.Cells(i, 2).Value
            If EmailDate = Date Then
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)            
                With OutMail
                    .To = ws.Cells(i, 1).Value
                    .Subject = "Reminder"
                    .Body = "This is your reminder email."
                    .Send
                End With
            End If
        Next i
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub

    This code will check the date in column B and send the email if the date matches today’s date.

    Regards
    ExcelDemy

  50. Hello Liliana,

    You are most welcome. You can adapt Method 1 from this article on sending emails based on dates. Here’s an updated VBA code snippet that checks if the date in column B matches today’s date before sending the email.

    Sub SendEmailsBasedOnDates()
        Dim ws As Worksheet
        Dim i As Long, LastRow As Long
        Dim EmailDate As Date
        Dim OutApp As Object, OutMail As Object
    
        Set ws = ThisWorkbook.Sheets("Sheet1")
        LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        For i = 2 To LastRow
            EmailDate = ws.Cells(i, 2).Value
            If EmailDate = Date Then
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)            
                With OutMail
                    .To = ws.Cells(i, 1).Value
                    .Subject = "Reminder"
                    .Body = "This is your reminder email."
                    .Send
                End With
            End If
        Next i
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub

    This code will check the date in column B and send the email if the date matches today’s date.

    Regards
    ExcelDemy

  51. Hello Lucie,

    You are most welcome. Glad you found the guide helpful! Regarding the error with union(body(‘Select’),body(‘Select’)), it’s possible the issue might be with the apostrophes. Make sure you’re using straight quotes (‘) instead of curly ones (‘ or ’). Additionally, double-check that both body(‘Select’) expressions reference valid and correctly named outputs.
    If everything looks correct and the issue persists, feel free to share more details. I’d be happy to help troubleshoot further!

    Regards
    ExcelDemy

  52. Hello Lucie,

    You are most welcome. Glad you found the guide helpful! Regarding the error with union(body(‘Select’),body(‘Select’)), it’s possible the issue might be with the apostrophes. Make sure you’re using straight quotes (‘) instead of curly ones (‘ or ’). Additionally, double-check that both body(‘Select’) expressions reference valid and correctly named outputs.
    If everything looks correct and the issue persists, feel free to share more details. I’d be happy to help troubleshoot further!

    Regards
    ExcelDemy

  53. Hello Ian Lavell,

    You are most welcome. Thank you so much for your feedback! I’m glad the explanation helped, and no worries at all about being a “slow learner”. We all have our own pace, and it’s great that you’re exploring VBA!

    You’re absolutely right about applying the same logic to CommandButtons. The code you’ve shared looks great, and it’s fantastic that you’re combining the button caption with the ComboBox values to set the date.
    Keep up the great work! Feel free to ask if you need any further clarifications.

    Regards
    ExcelDemy

  54. Hello Tomasz,

    You are most welcome. Glad to hear that you found the expected solution. Thanks for your appreciation and feedback. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  55. Hello Simon Gray,

    You are most welcome. Glad to hear that the solution worked. Thanks for your appreciation and feedback. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  56. Hello Lethabo Mokoti,

    You are most welcome. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  57. Hello Ian Lavell

    You are most welcome and thank you for following along! The “Compile Error: Variable not defined” on i likely occurs because the variable i hasn’t been declared.
    Try adding Dim i As Integer at the beginning of your code, before the For i = 1 to 41 line. This declares i as an integer, ensuring it’s recognized in the loop. Also, ensure that the rest of the subroutine is correctly typed, and the range/variables are properly referenced.

    Regards
    ExcelDemy

  58. Hello Simon,

    You are most welcome. Thanks for your appreciation and feedback. To hardcode the ranges and remove the input boxes from the script, you can directly assign the ranges in the code. Replace the lines where Application.InputBox is used with specific references like this.

    Set title_range = Worksheets("Sheet1").Range("A1:E1") ' Adjust the sheet name and range
    Set verticle_range = Worksheets("Sheet1").Range("A2:A100") ' Adjust as needed

    To stop auto-adjusting row widths when pasting, remove the Columns.AutoFit line.

    ' Sheets(dataset(i) & "").Columns.AutoFit ' Comment or remove this line

    Regards
    ExcelDemy

  59. Hello Paul,

    Each line of the email body starts and ends with vbNewLine for formatting. The _ at the end of each line allows you to split longer lines across multiple lines for readability. You can follow this breakdown to understand how each line flows.

    ' Build the email body with info from column A and F
        mMailBody = "Hello," & vbNewLine & vbNewLine & _    ' First line of email
                    "Please note that item in row " & rowNum & " requires attention." & vbNewLine & _   ' Mention row number
                    "A: " & ws.Cells(rowNum, 1).Value & vbNewLine & _    ' Include column A value in email
                    "F: " & ws.Cells(rowNum, 6).Value & vbNewLine & _    ' Include column F value in email
                    vbNewLine & "Regards," & vbNewLine & "Outlet Team"    ' Closing the email
        
        ' Send the email using Outlook
        On Error Resume Next
        With mMail
            .To = "[email protected]"   ' Recipient's email
            .Subject = "Alert: Cell in Column F >= 0"   ' Email subject
            .Body = mMailBody   ' Email body containing row info
            .Display   ' Use .Send to send directly without preview
        End With

    Regards
    ExcelDemy

  60. Hello,

    You are most welcome. Thanks for your insightful feedback. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  61. Hello Erika Póta,

    Thanks for your input! While your method works perfectly, our original approach isn’t wrong. It just serves a different purpose by allowing for flexibility with additional color formats. Your simplified version works well and avoids the error. However, the Select Case structure could still be useful if more color options or formats are needed in future modifications. I appreciate your solution for directly calculating the RGB values.
    Our solution is working fine:

    Regards
    ExcelDemy

  62. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear that you found the examples clear and insightful. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  63. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear that these shortcuts is saving your time while working in Excel. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  64. Hello Van Weaver,

    You are most welcome. Glad to hear that you got your desired result. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  65. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear that the examples are helpful to you. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  66. Hello,

    Thanks for your appreciation. Glad to hear that our Excel template is helpful to you. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  67. Hello George Shawnessey,

    It looks like the issue comes from the syntax of the Application.OnKey method. To make F12 act as the “Break” key, unfortunately, you cannot use {Break} as an argument directly, as “Break” isn’t a valid key constant for OnKey. However, you can define a custom macro to handle a “break-like” functionality.

    Here’s a suggestion:

    Define F12 to trigger a macro:
    Sub CreateBreakKey() Application.OnKey "{F12}", "BreakMacro" ' Assign F12 to trigger BreakMacro End Sub Sub BreakMacro() MsgBox "Break key activated!" ' Define your desired action here End Sub
    This setup triggers BreakMacro when F12 is pressed, allowing you to customize the “break” functionality.

    Regards
    ExcelDemy

  68. Hello Van Weaver,

    To sum a defined range of columns while still applying criteria for the rows, you can use the SUMPRODUCT function combined with INDEX and MATCH. For summing through a range of weeks up to today, consider using a dynamic approach.

    1. Define the range for weeks in columns.
    2. Use a dynamic condition like COLUMN()<=MATCH(TODAY(), your_week_range, 0) to sum only up to today.
    3. Apply row criteria within the SUMPRODUCT for filtering.

    Regards
    ExcelDemy

  69. Hello Sergio Zuniga,

    To keep the original timestamps intact when filtering or sorting while still allowing updates when changes are made, you’ll need to modify the VBA code. Use a Worksheet_Change event to record the time only when a new entry is made or a change occurs, rather than recalculating the time every time the cell is referenced.
    1. Right-click the sheet tab and choose View Code.
    2. Insert this VBA code.
    Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then If Target.Cells.Count = 1 Then If Target.Value "" Then If Me.Cells(Target.Row, "C").Value = "" Then Me.Cells(Target.Row, "C").Value = Format(Now, "dd-mm-yy hh:mm:ss") End If Else Me.Cells(Target.Row, "C").ClearContents End If End If End If End Sub

    Time is recorded in column C when a value is entered in column B.
    The timestamp won’t update during sorting or filtering.
    If a value is changed manually, the timestamp will only update if the cell in column C is empty.

    Regards
    ExcelDemy

  70. Hello Paul,

    You can use this updated VBA code that will send an email when a cell in column F is >= 0 and include information from columns A and F in the email body.
    Option Explicit Dim Rng As Range Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next ' Check if more than one cell is changed If Target.Cells.Count > 1 Then Exit Sub ' Limit the range to A2:O48 Set Rng = Intersect(Me.Range("A2:O48"), Target) If Rng Is Nothing Then Exit Sub ' Check if the cell in column F is >= 0 If Not IsNumeric(Target.Value) Then Exit Sub If Target.Column = 6 And Target.Value >= 0 Then Call SendEmail(Target.Row) End If End Sub Sub SendEmail(rowNum As Long) Dim mApp As Object Dim mMail As Object Dim mMailBody As String Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("2024 service") ' Change to your sheet name Set mApp = CreateObject("Outlook.Application") Set mMail = mApp.CreateItem(0) ' Prepare email body with info from column A and F mMailBody = "Hello," & vbNewLine & vbNewLine & _ "Please note that item in row " & rowNum & " requires attention." & vbNewLine & _ "A: " & ws.Cells(rowNum, 1).Value & vbNewLine & _ "F: " & ws.Cells(rowNum, 6).Value & vbNewLine & _ vbNewLine & "Regards," & vbNewLine & "Outlet Team" ' Send email On Error Resume Next With mMail .To = "[email protected]" ' Change to recipient’s email .Subject = "Alert: Cell in Column F >= 0" .Body = mMailBody .Display ' or you can use .Send to send the email directly End With On Error GoTo 0 ' Clean up Set mMail = Nothing Set mApp = Nothing End Sub
    Worksheet_Change Event triggers when any cell in the range A2 changes. Sends an email when a cell in column F is greater than or equal to 0. Includes the information from columns A and F for the corresponding row in the email body.

    Regards
    ExcelDemy

  71. Hello,

    You are most welcome. Glad to hear that you found the article helpful. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  72. Hello,

    You are most welcome. Glad to hear that you found the article helpful. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  73. Hello Paul,

    You are most welcome. It looks like there are a couple of issues in your VBA code that need fixing:

    1. The range check should specifically look at column F only.
    2. You need to correct the index for the columns A and E in the email body.

    
    Sub SendEmailOnRedCell()
        Dim rng As Range
        Dim cell As Range
        Dim OutApp As Object
        Dim OutMail As Object
        Dim emailBody As String
        Dim ws As Worksheet
    
        Set ws = ThisWorkbook.Sheets("2024 service") ' Change to your sheet name
        Set rng = ws.Range("F2:F48") ' Check only column F
    
        ' Create Outlook application
        Set OutApp = CreateObject("Outlook.Application")
    
        ' Loop through each cell in column F
        For Each cell In rng
            If cell.Interior.Color = RGB(255, 0, 0) Then ' Check if the cell is red
                emailBody = "Information from row " & cell.Row & ": " & vbCrLf
                emailBody = emailBody & "A: " & ws.Cells(cell.Row, 1).Value & vbCrLf ' Column A
                emailBody = emailBody & "E: " & ws.Cells(cell.Row, 5).Value & vbCrLf ' Column E
    
                ' Create and send the email
                Set OutMail = OutApp.CreateItem(0)
                With OutMail
                    .To = "[email protected]" ' Change to recipient’s email
                    .Subject = "Alert: Red Cell Detected"
                    .Body = emailBody
                    .Send
                End With
                Set OutMail = Nothing
            End If
        Next cell
    
        ' Clean up
        Set OutApp = Nothing
    End Sub

    The code now checks only column F (F2) for red cells.
    Corrected the indices for columns A and E (1 for A, 5 for E).

    Regards
    ExcelDemy

  74. Hello Monika Salinas,

    It seems like you’re facing a common issue with links to external workbooks in Excel.
    If your links stop working after the first click, it might be due to the linked workbook being closed or moved.
    Make sure the file path remains valid and the linked workbook is open when you access it.
    Additionally, check if there are any settings in Excel that might restrict external links.

    Regards
    ExcelDemy

  75. Hello Roel,

    To ensure your system automatically sends an email when the expiration date reaches 30 days, you need to adjust your VBA code slightly. The apostrophe before the .Send line is preventing the email from being sent. Removing that apostrophe will allow the email to be sent automatically.

    Here’s how you can modify the SendEmail sub-procedure:

    Sub SendEmail(OutApp As Object, emailBody As String, subject As String)
        Dim OutMail As Object
        Set OutMail = OutApp.CreateItem(0)
    
        With OutMail
            .To = "[email protected]" ' Change this to your email address
            .Subject = subject
            .HTMLBody = "<p>" & emailBody & "</p>"
            .Send ' Uncomment this line to send the email automatically
        End With
    End Sub

    Make sure to replace “[email protected]” with your actual email address. Additionally, ensure that your main subroutine correctly checks for the conditions to trigger the email alert based on cell color changes.

    If you follow these steps, you should receive automatic email notifications as intended.

    Regards
    ExcelDemy

  76. Hello Roel,

    To address your first concern about receiving multiple emails due to the same expiration date, you can modify the code to ensure only one email is sent per unique expiration date. This can be done by adding a simple check to track which dates have already triggered an email. Here’s how you can adjust the code:

    Sub SendEmailAlerts()
        Dim ws As Worksheet
        Dim cell As Range
        Dim expiryDate As Date
        Dim today As Date
        Dim emailBody As String
        Dim OutApp As Object
        Dim sentDates As Object
    
        Set ws = ThisWorkbook.Sheets("Sheet1")
        today = Date
        Set sentDates = CreateObject("Scripting.Dictionary") ' Track sent emails by date
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
        For Each cell In ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
            If IsDate(cell.Value) Then
                expiryDate = cell.Value        
                ' Check if the expiry date has already triggered an email
                If Not sentDates.exists(expiryDate) Then
                    If expiryDate <= today Then
                        emailBody = "The test certificate for " & cell.Offset(0, -1).Value & " has expired on " & expiryDate & "."
                        Call SendEmail(OutApp, emailBody, "Expiry Alert: Red")
                    ElseIf expiryDate <= today + 30 Then
                        emailBody = "The test certificate for " & cell.Offset(0, -1).Value & " will expire on " & expiryDate & "."
                        Call SendEmail(OutApp, emailBody, "Expiry Alert: Yellow")
                    End If             
                    ' Mark the expiry date as having been processed
                    sentDates.Add expiryDate, True
                End If
            End If
        Next cell
        Set OutApp = Nothing
    End Sub

    This update ensures that for any particular expiration date, only one email will be sent.

    Regards
    ExcelDemy

  77. Hello Bleona,

    You can fix the runtime error 6 overflow by handling the large dataset more efficiently. Since you have over 1 million rows, Excel may be exceeding its memory limits. You can follow these steps:
    1. Use smaller data batches for processing.
    2. Switch from Integer to Long data types in your VBA code, as Integer can only handle values up to 32,767.
    3. Consider using Power Query for merging large datasets instead of VBA, as it handles large data better.

    Regards
    ExcelDemy

  78. Hello Leonardo,

    The issue you’re encountering with VLOOKUP returning the incorrect time (e.g., 8:00 returning as 7:00) may be related to how Excel handles date and time values. Double-check that both columns are formatted exactly the same, not just visually but also in terms of underlying data types (i.e., as date/time values).
    Sometimes even slight formatting differences or time zone offsets can cause such issues. You might also try using TEXT functions to standardize the format.
    If VLOOKUP is fetching a time that’s off by an hour, consider verifying any regional settings or daylight saving time adjustments.

    Regards
    ExcelDemy

  79. Hello Dear,

    You are most welcome. Glad to hear that our article helped you to create relational database in Excel. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  80. Hello Paul,

    You are most welcome. To automate email notifications when a cell turns red, you can update your existing VBA code to check the cell color. You can use the Interior.Color property to identify red cells. Once a red cell is found, compile the data from that row and include it in the email body.

    Sample VBA Code:

    Sub SendEmailOnRedCell()
        Dim rng As Range
        Dim cell As Range
        Dim OutApp As Object
        Dim OutMail As Object
        Dim emailBody As String
        Dim ws As Worksheet
    
        Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
        Set rng = ws.Range("A1:D10") ' Change to your specific range
    
        ' Create Outlook application
        Set OutApp = CreateObject("Outlook.Application")
    
        ' Loop through each cell in the range
        For Each cell In rng
            If cell.Interior.Color = RGB(255, 0, 0) Then ' Check if the cell is red
                emailBody = "Information from row " & cell.Row & ": " & vbCrLf
                emailBody = emailBody & "A: " & ws.Cells(cell.Row, 1).Value & vbCrLf
                emailBody = emailBody & "B: " & ws.Cells(cell.Row, 2).Value & vbCrLf
                emailBody = emailBody & "C: " & ws.Cells(cell.Row, 3).Value & vbCrLf
                emailBody = emailBody & "D: " & ws.Cells(cell.Row, 4).Value & vbCrLf
    
                ' Create and send the email
                Set OutMail = OutApp.CreateItem(0)
                With OutMail
                    .To = "[email protected]" ' Change to recipient's email
                    .Subject = "Alert: Red Cell Detected"
                    .Body = emailBody
                    .Send
                End With
                Set OutMail = Nothing
            End If
        Next cell
    
        ' Clean up
        Set OutApp = Nothing
    End Sub

    1. This code checks the specified range (A1) in Sheet1 for red cells. If a red cell is found, it compiles information from that row and sends an email.
    2. Remember to update the Range, Sheet name, and recipient email address as needed.
    3. Make sure to enable macros and allow programmatic access to Outlook.

    Regards
    ExcelDemy

  81. Hello Dear,

    Thanks for your appreciation. We are glad to hear that out step-by-step guide is helpful to you. You can use our guide to create a sales report. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  82. Hello Fiona,

    To calculate the time difference across shifts that span multiple days, you’ll need to adjust your formula. Instead of just using the MOD function, you can directly subtract the end time from the start time while ensuring the dates are included.

    For example, if your end time is in cell D2 and your start time is in cell C2, you can use:
    =(D2 + IF(D2 < C2, 1, 0)) - C2 This formula adds 1 day if the end time is earlier than the start time. Regards ExcelDemy

  83. Hello Raj,

    To create a horizontal bar chart with a line combo in Excel,
    1. First Insert a regular Bar chart by selecting your data.
    2. Then, right-click on the data series you want to change to a line and choose Change Series Chart Type.
    3. Select the Combo option, and then choose Line for the desired series.
    4. Adjust the chart layout and format as needed.

    Regards
    ExcelDemy

  84. Hello Muhammad Azhan,

    To create a graph of kurtosis values in Excel, follow these steps:

    1. Create a table with your values and corresponding labels.
    2. Highlight the relevant cells.
    3. Go to the Insert tab >> select the desired chart type (e.g., bar or line chart).
    4. Use chart tools to add titles, labels, and adjust formatting as needed.

    Regards
    ExcelDemy

  85. Hello Peter,

    Thanks for your feedback and solution. Hopefully it would be useful for the users. That’s a clever approach! Using a concatenated string to identify empty rows while avoiding zeros is effective. After you locate those rows with “somestring0,” deleting them is straightforward. This method provides a great alternative when sorting or filtering isn’t an option.

    Regards
    ExcelDemy

  86. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear that our functions breakdown helped you to understand the function arguments. keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  87. Hello Kevin Slabbert,

    To right-align values in a data table in Excel charts, you’ll need to adjust the alignment of the data in the worksheet itself.
    Go to the Home tab >> select Align Right.
    Unfortunately, Excel doesn’t offer direct control over alignment within the chart’s data table, so the workaround is to format the source data in the worksheet. This change will reflect in the data table of the chart.

    Regards
    ExcelDemy

  88. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear our step-by-step guide is helpful to you. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  89. Hello Ian,

    Thank you for your feedback! Unfortunately, Excel doesn’t allow direct editing of ranges with relative addressing. Your suggested approach of creating a new named range and updating references seems to be the best workaround. Another option could be to use a formula in the new range to pull in data from the existing named range while expanding it. This way, you can keep your original named range intact for reference.
    To create a new named range that expands on an existing one, you can use the INDEX function combined with OFFSET. Example formula:
    =OFFSET(NamedRangeStart, 0, 0, ROWS(NamedRangeStart), COLUMNS(NamedRangeStart) + AdditionalColumns)
    Replace NamedRangeStart with your existing named range, and AdditionalColumns with the number of extra columns you want to include. This formula effectively creates a dynamic range that expands based on your specifications.

    Regards
    ExcelDemy

  90. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear that our tutorial helped you to remove carriage returns. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  91. Hello Ano Victory,

    I’m glad you found it interesting! To force related selections when the first selection is changed (e.g., Fish/Salmon -> Fruit/Salmon), you could use dependent data validation. This way, the second selection will reset or limit options based on the first choice. You’ll need to set up a named range for each category and then apply data validation rules for the second dropdown accordingly.

    You can use a Custom Data Validation with an IF statement to restrict invalid subcategory selections. For example, let’s say “Fish” is selected in the first dropdown, and you want to force the user to only choose “Salmon” or “Tuna” in the second dropdown. You can apply a formula like this in the second dropdown:
    =IF(E1=”Fish”,OR(F1=”Salmon”,F1=”Tuna”),OR(F1=”Apple”,F1=”Orange”))
    This will ensure that the second selection matches the first dropdown category.

    You can adjust this formula based on your category-subcategory structure.

    Regards
    ExcelDemy

  92. Hello Jay,

    You are most welcome and thank you for your kind words! To change the individual segment colors for just one month, follow these steps:

    1. Select the chart.
    2. Select the bar segment for January that you want to modify.
    3. Right-click the segment and choose Format Data Point.
    4. In the Format Pane, change the Fill color to your desired choice.

    Regards
    ExcelDemy

  93. Hello Shubham,

    You can download the stock data like yahoo finance. Use the STOCKHISTORY function. Follow this article to get the stock prices with your required fields.
    Download Historical Stock Data into Excel (with Easy Steps)

    Regards
    ExcelDemy

  94. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear that our step by step guide helped you to follow the instructions easily. Learn Excel with ExcelDemy!

    Regards
    ExcelDemy

  95. Hello Joshua Goodman,

    The spill error you’re experiencing could be due to extra data being included in your formula’s output. Please do the followings:

    Ensure your formula only returns the employee data without repeating the department column.
    You can use FILTER in combination with UNIQUE or INDEX to target just the employees.
    Make sure the dragged formula doesn’t overlap the department list, which might trigger the spill error.

    Feel free to share your formula with dataset for further investigation.

    Regards
    ExcelDemy

  96. Hello,

    You are most welcome. Glad to hear that our tutorial is lifesaver to you. Thanks for your appreciation. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  97. Hello Ron,

    You are most welcome. Glad to hear that method 2 worked for you. Thanks for your appreciation. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  98. Hello Denise Groves,

    You are most welcome. Glad to hear that our tutorial helped you to visualize nesting substitute formulas. Keep exploring Excel formulas with ExcelDemy!

    Regards
    ExcelDemy

  99. Hello JaneM,

    “Subscript Out of Range Error” occurs when trying to reference a worksheet that does not exist. In your case, ensure that the names of the sheets you are trying to access match exactly with what is in the workbook. To solve this issue you can use the following VBA code.

    The code will dynamically checks for the existence of the sheets Sheet1(2) to Sheet1(11) using a loop. It will copy the entire rows based on the last used row in each sheet instead of using UsedRange, which may cause issues if you have formatting or other non-data cells.
    The code creates a single new worksheet named MergedData and does not insert any blank sheets.

    Copy-Paste the VBA code:

    Sub CombineSheets()
        Dim ws As Worksheet
        Dim MergedSheet As Worksheet
        Dim LastRow As Long
        Dim MergedRow As Long
        Dim i As Integer
    
        ' Create a new worksheet for the merged data
        Set MergedSheet = ThisWorkbook.Worksheets.Add
        MergedSheet.Name = "MergedData"
    
        ' Start merging from the first row
        MergedRow = 1
    
        ' Loop through each worksheet
        For i = 1 To 11 ' Assuming Sheet1, Sheet1(2), ... , Sheet1(11)
            On Error Resume Next ' Ignore errors temporarily
            Set ws = ThisWorkbook.Worksheets("Sheet1(" & i & ")")
            On Error GoTo 0 ' Turn error handling back on
    
            If Not ws Is Nothing Then ' Check if the worksheet exists
                LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Find the last used row in the current sheet
    
                ' Copy the used range to the merged sheet
                ws.Range("A1:A" & LastRow).EntireRow.Copy Destination:=MergedSheet.Range("A" & MergedRow)
    
                ' Update the merged row for the next paste
                MergedRow = MergedRow + LastRow
            End If
    
            ' Reset ws for the next iteration
            Set ws = Nothing
        Next i
    
        MsgBox "Sheets combined successfully!"
    End Sub

    Regards
    ExcelDemy

  100. Hello Matthew Senko,

    It seems that you’re encountering a syntax error in your Power Automate flow. The issue is likely related to how the date is formatted in your query. Make sure that the date is enclosed in double quotes and use the correct format.
    Instead of: Deadline eq ‘2024-10-03’
    Try using: Deadline eq ‘2024-10-03T00:00:00Z’

    If the issue persists, please double-check your query syntax. Let me know if you need further assistance!

    Regards
    ExcelDemy

  101. Hello Jim,

    Thanks for the update! If you’re still encountering issues with Week 1, consider using the ISOWEEKNUM function to get the correct ISO week number:
    =ISOWEEKNUM(A1)
    This function aligns with your region’s definition of Week 1.

    To account for ISO week numbering where Week 1 starts on the first week with at least four days, you can use the following formula:
    =DATE(B5,1,4) – WEEKDAY(DATE(B5,1,4), 2) + (A5-1)*7
    This should help correct the offset.
    N.B: Leap years shouldn’t shift by a full week, but they could affect the day counts.

    Regards
    ExcelDemy

  102. Hello Jim,

    Thanks for the update! If you’re still encountering issues with Week 1, consider using the ISOWEEKNUM function to get the correct ISO week number:
    =ISOWEEKNUM(A1)
    This function aligns with your region’s definition of Week 1.

    To account for ISO week numbering where Week 1 starts on the first week with at least four days, you can use the following formula:
    =DATE(B5,1,4) – WEEKDAY(DATE(B5,1,4), 2) + (A5-1)*7
    This should help correct the offset.
    N.B: Leap years shouldn’t shift by a full week, but they could affect the day counts.

    Regards
    ExcelDemy

  103. Hello Charlie,

    To create a radar chart with 6 different scales, you can apply the same method shown in the article for multiple scales. Each axis in a radar chart can represent a separate scale.
    You just need to ensure that your data is set up accordingly with six distinct series and adjust the axis limits for each. If you need help with a specific part of the process, feel free to ask!

    Regards
    ExcelDemy

  104. Hello Laurie,

    Here, oFolder object not being correctly initialized within the Do While Coll_queue.Count > 0 loop. That’s why the error is occurring. This object is necessary to iterate through files and subfolders, but it’s missing an assignment before looping over subfolders and files.

    To fix this issue, assigning oFolder from the collection at the beginning of the loop.

    Do While Coll_queue.Count > 0
        Set oFolder = Coll_queue(1) 'Assign folder before removing it
        Coll_queue.Remove 1 'dequeue
        ' rest of the loop
    

    This ensures oFolder is properly initialized and recognized when iterating over its subfolders and files.

    Updated Excel File:
    List All Files in Folders & SubFolders Including File Details with Excel VBA.xlsm

    Regards
    ExcelDemy

  105. Hello Eduardo,

    De nada! You are most welcome. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  106. Hello kellyd,

    To solve your issue, you can use the INDEX and MATCH functions along with a filter to look up the first non-blank status for each site code.

    In the “Status Lookup” column, use this formula:

    =INDEX($C$15:$C$23,MATCH(1,($B15=$B$15:$B$23)*($C$15:$C$23<>“”),0))

    After entering this formula, press Ctrl+Shift+Enter (if you’re not using Excel 365) to treat it as an array formula.

    This will return the first non-blank status for each site code.

    Regards
    ExcelDemy

  107. Hello Moegamat Shakier Stuurman,

    You are most welcome. Thanks for your appreciation. Glad to hear that you are going to use it to track aging analysis. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  108. Hello Raj,

    Thanks for your appreciation. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  109. Hello Minhaj,

    In the formula =INDEX($B$140:$B$144,MATCH($C$146,$C$140:$C$144,1)+1), the +1 is used to return the value from the next row after the matched value.
    Formula Explanation:

    MATCH($C$146,$C$140:$C$144,1): This part searches for the value in cell C146 (which contains “Today’s Date”) within the range C140:C144 (the list of birthdays). The 1 at the end of the MATCH function indicates that the function looks for the largest value that is less than or equal to C146. So, it will return the position of the latest birthday before or on the current date.

    +1: After the MATCH function finds the closest previous birthday, the +1 moves the selection to the next row in the range, which corresponds to the next upcoming birthday.

    INDEX($B$140:$B$144,…): This part retrieves the name of the person who has the next upcoming birthday based on the MATCH result (which is adjusted by +1 to move to the next entry).

    Regards
    ExcelDemy

  110. Hello Steve,

    You can address the error by ensuring there is enough space in the target worksheet for the CSV data to be imported without overlapping existing tables or XML mappings. Consider moving the existing table to another location or importing the CSV data into a new worksheet and then linking it to the existing formulas or tables.

    Regards
    ExcelDemy

  111. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear that our examples helped you.

    Regards
    ExcelDemy

  112. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear that our examples helped you.

    Regards
    ExcelDemy

  113. Hello Louis,

    Thanks for your appreciation. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  114. Hello Steve,

    It’s clear you’re facing performance bottlenecks with Excel, and your frustration is understandable given your powerful hardware. Excel, while widely used, doesn’t fully utilize multi-core CPUs or GPUs for many tasks. Here are some steps and alternatives you could try:

    To optimize Excel usage you can disable volatile functions like NOW() and RAND() recalculate constantly, slowing Excel down.
    Overuse Conditional Formatting can heavily impact performance, especially on large datasets.
    Set Excel to manual calculations when working on large datasets.
    Excel doesn’t effectively leverage GPUs or advanced multi-threading. However, you can check File > Options > Advanced > Enable hardware graphics acceleration (though this often doesn’t make significant differences in complex datasets).

    Try to enable multithreading by going to File > Options > Advanced > Formulas > Enable multi-threaded calculation. While Excel doesn’t scale well across many cores, this can still help somewhat with certain tasks.

    Explore Alternatives:
    Power BI: It’s optimized for large datasets and leverages more powerful backend engines for data processing.
    Python (Pandas/NumPy): If you handle large matrix-like data, Python libraries can offer much faster performance and can use multiple cores efficiently.
    R or SQL-based tools: These are better at handling larger datasets, parallel processing, and complex operations.
    Consider Excel Online: While Excel desktop has limitations, Excel Online, backed by Microsoft’s cloud, sometimes offers better performance in dealing with larger datasets since it uses cloud resources.

    Despite its strengths, Excel’s legacy architecture doesn’t yet match the potential of modern hardware. Exploring these steps or alternative tools might improve your workflow and reduce frustration.

    Regards
    ExcelDemy

  115. Hello Hans Hallebeek,

    It’s great to hear that you love to play with VBA. VBA is interesting. Here’s a sample code to handle an ActiveX TextBox Change event in a worksheet:

    1. Insert an ActiveX TextBox from the Developer tab.
    2. Open the VBA editor (Alt + F11).
    3. In the Project Explorer, find the worksheet with the TextBox.
    4. Now, write the following code in the worksheet’s code window:

    Private Sub TextBox1_Change()
        MsgBox "TextBox content changed!"
    End Sub

    This triggers a message box whenever the TextBox content changes. You can replace TextBox1 with your TextBox’s name.

    Regards
    ExcelDemy

  116. Hello Timothy,

    It seems the issue is due to the protection on the data sheets preventing your mainSht from accessing the necessary data. You can modify your VBA to temporarily unprotect the sheets when running the code.

    
    Sheets("SheetName").Unprotect "YourPassword"
    ' Your code here to pull data
    Sheets("SheetName").Protect "YourPassword"

    You can also try setting the UserInterfaceOnly property when protecting the sheets. This allows VBA code to modify protected sheets while still restricting user interaction:

    
    Sheets("SheetName").Protect Password:="YourPassword", UserInterfaceOnly:=True

    This way, your VBA code can still manipulate the data without fully unprotecting the sheets each time. I hope this helps! Let me know if you need further clarification or additional resources.”

    This approach ensures the sheets remain protected for users but accessible for the code execution.

    Regards
    ExcelDemy

  117. Hello Angufibo David,

    Thank you so much for your kind words! I’m glad you found the material helpful. We’re always working to provide more detailed and practical examples, especially with Excel and VBA. If you have any specific topics you’d like to see covered, feel free to share!

    Regards
    ExcelDemy

  118. Hello,

    You are most welcome. Thanks for your kind words. Glad to hear that the invoice format is helpful to you. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  119. Hello,

    You are most welcome. Thanks for your kind words. Glad to hear that the registry edit solution worked perfectly for you. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  120. Hello AT William,

    You are most welcome. Thanks for your kind words. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  121. Hello Edward M,

    You are most welcome. Thanks for your appreciation. Glad to hear that it is helpful to you. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  122. Hello Edward M,

    You can definitely modify the formula to reference a date in another cell instead of hardcoding it. For example, if you have a date in cell A1, you can use the formula =COUNTIF(E5:E27,”>” & A1) to make it dynamic. This way, you won’t have to change the formula every time you need a different date range.

    Regards
    ExcelDemy

  123. Hello Roy E. McCarthy,

    We are using MS Office 365. In Excel, once you freeze any row or column, the option will change to “Unfreeze Panes” instead of showing “Freeze Panes” again. This helps users know that something is already frozen. If you want to freeze or unfreeze rows or columns, you need to select the appropriate option based on your current status.
    Before freezing anything:

    After freezing first row:

    Regards
    ExcelDemy

  124. Hello Maddie,

    It seems the custom number format you’re using might be interacting with the display unit settings in Excel.
    Set the display units to “None”. Display units in Excel (e.g., “Thousands”, “Millions”) automatically append prefixes like “K” or “M”, and in this case, that’s why the “K” is appearing.

    Use this custom format: 0.00,, “GW” or #.##,,”GW”
    The double commas ,, divide the number by 1,000 twice, which effectively formats the number in thousands. The result should display values in thousands with “GW” as the unit but without adding “K”.

    By setting the display units to “None” and using the format above, the unwanted “K” should no longer appear, and the values will remain visible.

    Regards
    ExcelDemy

  125. Hello Grace,

    It is possible to connect a spin button with a print button. The spin button can adjust the quantity of labels from the next sheet, and the print button can trigger printing based on the value selected. You would need to use a combination of VBA code to link the spin button’s value to the print process, specifying the number of copies using the spin button control.

    First, insert the Spin Button and assign it a cell for its value (e.g., Sheet1!A1).
    Then, add the Print Button and assign the following VBA code:

    Private Sub PrintButton_Click()
        Dim qty As Integer
        qty = Sheet1.Range("A1").Value ' Get spin button value
        
        For i = 1 To qty
            Sheets("LabelsSheet").PrintOut
        Next i
    End Sub

    This code reads the quantity from the spin button and prints the label sheet that many times.

    Regards
    ExcelDemy

  126. Hello Maddie,

    To display units in thousands without “K”. You can use the format #.##,,”GW” for thousands (without “K”).
    This removes the extra “K” while formatting the number in thousands.

    Regards
    ExcelDemy

  127. Hello Shan,

    To dynamically feed cell addresses from an Excel file (starting at cell B3), you can modify the VBA code to read the list directly from that file.

    Sub HighlightChangesFromFile()
        Dim ws As Worksheet
        Dim changesWs As Worksheet
        Dim rng As Range
        Dim cellAddresses As Variant
        Dim i As Integer
        Dim lastRow As Long
    
        ' Define the sheet to highlight and the sheet with cell addresses
        Set ws = ThisWorkbook.Sheets("Sheet1")
        Set changesWs = Workbooks("ChangesFile.xlsx").Sheets("Sheet1") ' Adjust workbook name
        
        ' Get the last row in column B for dynamic range
        lastRow = changesWs.Cells(changesWs.Rows.Count, "B").End(xlUp).Row
        
        ' Get the range of addresses from B3 to the last row
        Set rng = changesWs.Range("B3:B" & lastRow)
        
        ' Loop through each address and highlight it
        For Each cell In rng
            ws.Range(cell.Value).Interior.Color = RGB(255, 255, 0) ' Highlight color
        Next cell
    End Sub

    This will read the cell addresses from column B of the other file and highlights them in the current workbook. Remember to replace “ChangesFile.xlsx” with the actual name of your file.

    Regards
    ExcelDemy

  128. Hello Shan,

    Glad to hear. To dynamically feed cell addresses from an Excel file (starting at cell B3), you can modify the VBA code to read the list directly from that file.

    Sub HighlightChangesFromFile()
        Dim ws As Worksheet
        Dim changesWs As Worksheet
        Dim rng As Range
        Dim cellAddresses As Variant
        Dim i As Integer
        Dim lastRow As Long
    
        ' Define the sheet to highlight and the sheet with cell addresses
        Set ws = ThisWorkbook.Sheets("Sheet1")
        Set changesWs = Workbooks("ChangesFile.xlsx").Sheets("Sheet1") ' Adjust workbook name
        
        ' Get the last row in column B for dynamic range
        lastRow = changesWs.Cells(changesWs.Rows.Count, "B").End(xlUp).Row
        
        ' Get the range of addresses from B3 to the last row
        Set rng = changesWs.Range("B3:B" & lastRow)
        
        ' Loop through each address and highlight it
        For Each cell In rng
            ws.Range(cell.Value).Interior.Color = RGB(255, 255, 0) ' Highlight color
        Next cell
    End Sub

    This will read the cell addresses from column B of the other file and highlights them in the current workbook. Remember to replace “ChangesFile.xlsx” with the actual name of your file.

    Regards
    ExcelDemy

  129. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear that you will apply the examples in your project. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  130. Hello Shan,

    To highlight the range with changes, you’ll need to use VBA to highlight the changed cells in the original Excel file.

    If the compare tool generates a range like (A101, B95, etc.). You’ll need to copy this list.
    Open the workbook where changes occurred. Then, run the following VBA script to loop through the list and highlight the cells.

    Sub HighlightChanges()
        Dim ws As Worksheet
        Dim rng As Range
        Dim cellAddresses As Variant
        Dim i As Integer
        
        ' Define the sheet and cell range to highlight
        Set ws = ThisWorkbook.Sheets("Sheet1")
        
        ' List of cell addresses
        cellAddresses = Array("A101", "B95", "B100", "B101", "C95", "C100", "C101", "D101")
        
        ' Loop through each address and highlight it
        For i = LBound(cellAddresses) To UBound(cellAddresses)
            ws.Range(cellAddresses(i)).Interior.Color = RGB(255, 255, 0) ' Highlight color
        Next i
    End Sub

    It will highlight the specified cells with a yellow background.

    Regards
    ExcelDemy

  131. Hello Doreen Bagola,

    The formula we used to calculate annual leave can be adjusted for leave donations and administrative leave, but you’ll likely need to customize it depending on how these types of leaves are tracked.
    For instance, you can add extra columns or rules to include donated or administrative leave alongside the regular annual leave calculations. This will ensure all leave types are accounted for. You may need to adjust data validation or leave balance formulas accordingly.

    Regards
    ExcelDemy

  132. Hello Gail Anthony,

    Yes, it is possible for a cell to reference its own sheet name in Excel. You can use a formula like this:

    =MID(CELL(“filename”, A1), FIND(“]”, CELL(“filename”, A1)) + 1, 255)

    This formula extracts the sheet name from the full file path returned by the CELL(“filename”, A1) function. Just ensure the workbook is saved, as the filename includes the sheet reference.

    Regards
    ExcelDemy

  133. Hello,

    You are most welcome. Glad to hear that our tips are helpful to you. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  134. Hello بهروز مظفری,

    To merge all data from different sheets to one single workbook. You can follow the step by step guide of these articles:
    How to Combine Multiple Worksheets into One Workbook?
    How to Merge Sheets in Excel – Must-Know Tips and Techniques
    How to Merge Multiple Sheets into One Sheet with VBA in Excel?

    Regards
    ExcelDemy

  135. Hello Jeremy,

    You are most welcome. Thanks for your appreciation. To calculate the elapsed time between two date-time entries in Excel, you can subtract the two cells and display the result in decimal hours. But you must need to use the date and time format properly. You can’t use colon(:) in between date and time.
    Use this formula to subtract G21 from G22:
    =G22 – G21
    Format the result cell as [h]:mm:ss.
    Select the cell T21 >> Right-click >> select Format Cells >> select Custom >> type [h]:mm:ss

    Multiply the result by 24 to get the answer in hours:

    =(G22 – G21) * 24

    Excel naturally handles dates and times. When subtracting times that span over midnight, the same formula will work.

    Regards
    ExcelDemy

  136. Hello Jeremy,

    You are most welcome. Thanks for your appreciation. To calculate the elapsed time between two date-time entries in Excel, you can subtract the two cells and display the result in decimal hours. But you must need to use the date and time format properly. You can’t use colon(:) in between date and time.
    Use this formula to subtract G21 from G22:
    =G22 – G21
    Format the result cell as [h]:mm:ss.
    Select the cell T21 >> Right-click >> select Format Cells >> select Custom >> type [h]:mm:ss

    Multiply the result by 24 to get the answer in hours:

    =(G22 – G21) * 24

    Excel naturally handles dates and times. When subtracting times that span over midnight, the same formula will work.

    Regards
    ExcelDemy

  137. Hello Suzanna Cocker,

    You are most welcome. Thanks for your appreciation. To remove the pictures added via VBA, you can create another macro assigned to a button to delete them. Copy paste the VBA code that will remove all the shapes (including images) from the sheet.

    Sub RemovePictures()
        Dim Pic As Shape
        For Each Pic In ActiveSheet.Shapes
            If Pic.Type = msoPicture Then
                Pic.Delete
            End If
        Next Pic
    End Sub

    This code will delete all pictures in the active sheet when the button is clicked. You can add this to your existing setup.

    Regards
    ExcelDemy

  138. Hello Ravi Patel,

    To ensure that images match the corresponding name in column A, you can compare the image name with the text in column A (e.g., abc in column A should match abc.jpg), and then insert that image into column B.

    Sub InsertMultiplePictures()
        Dim Pictures() As Variant
        Dim PictureFormat As String
        Dim PicRng As Range
        Dim PicShape As Shape
        Dim PicRowIndex As Long
        Dim PicColIndex As Long
        Dim lLoop As Long
        
        On Error Resume Next
        PictureFormat = "Images (*.jpg), *.jpg"
        Pictures = Application.GetOpenFilename(PictureFormat, MultiSelect:=True)
        
        PicColIndex = Application.ActiveCell.Column
        If IsArray(Pictures) Then
            PicRowIndex = Application.ActiveCell.Row
            For lLoop = LBound(Pictures) To UBound(Pictures)
                Set PicRng = Cells(PicRowIndex, PicColIndex)
                Set PicShape = ActiveSheet.Shapes.AddPicture(Pictures(lLoop), _
                             msoFalse, msoCTrue, PicRng.Left, PicRng.Top, PicRng.Width, PicRng.Height)
                PicRowIndex = PicRowIndex + 1
            Next
        End If
    End Sub

    In the original code, it opens a dialog box to select multiple images, and then it adds them one by one in adjacent cells. If the image names don’t match, you would need to adapt this logic based on image names stored in column A. To automate the matching with your scenario, make sure the file names match the corresponding data in column A.

    Regards
    ExcelDemy

  139. Hello Sophie,

    To automatically moves a row when the status is marked as “done”. You can use the Worksheet_Change event along with the existing code for moving rows.

    Private Sub Worksheet_Change(ByVal Target As Range)
        ' Define the range where the status is located, adjust "C" to your status column
        If Not Intersect(Target, Me.Range("C1:C" & Me.UsedRange.Rows.Count)) Is Nothing Then
            If Target.Value = "done" Then
                ' Call the MoveRow_DeleteOriginal subroutine when status is "done"
                Call MoveRow_DeleteOriginal(Target.Row)
            End If
        End If
    End Sub
    
    Sub MoveRow_DeleteOriginal(RowNum As Long)
        Dim rg As Range
        Dim xc As Range
        Dim p As Long
        Dim q As Long
        Dim r As Long
        p = Worksheets("VBA delete original").UsedRange.Rows.Count
        q = Worksheets("Sheet1").UsedRange.Rows.Count
        If q = 1 Then
            If Application.WorksheetFunction.CountA(Worksheets("Sheet1").UsedRange) = 0 Then q = 0
        End If
        
        ' Specify the target row
        Set rg = Worksheets("VBA delete original").Range("C" & RowNum)
        
        ' If the condition is met (e.g., "Cable" or "done"), copy and delete the row
        On Error Resume Next
        Application.ScreenUpdating = False
        If CStr(rg.Value) = "done" Then
            rg.EntireRow.Copy Destination:=Worksheets("Sheet1").Range("A" & q + 1)
            rg.EntireRow.Delete
            q = q + 1
        End If
        Application.ScreenUpdating = True
    End Sub

    This will check the changes in the “Status” column (Column C in this case). If the status changes to “done”, the MoveRow_DeleteOriginal subroutine is triggered. You can modify the range and the condition (Target.Value = “done”) as needed for your specific use case.

    Regards
    ExcelDemy

  140. Hello Fin,

    The “-2” or “-3” are the different adjustments for kurtosis calculations. In this article, “-2” is used in the context of excess kurtosis, which helps measure how heavy or light-tailed the distribution is compared to a normal distribution.
    Normally, kurtosis is 3 for a normal distribution. Some methods subtract 3 from kurtosis (excess kurtosis), while in other cases, adjustments like “-2” are made based on specific analysis needs. It’s essential to know which method or software you’re using.

    Regards
    ExcelDemy

  141. Hello Kevin,

    Thanks for your solution. Hope it will be helpful for our Excel users. Thanks for contributing. Let’s explore Excel with ExcelDemy!

    Regards
    ExcelDemy

  142. Hello Frank,

    Thanks for your suggestion. It’s the beauty of Excel it provides better formula with updated functions. FILTER function is one of the most dynamic and useful function. Keep contributing Excel tips with ExcelDemy!

    Regards
    ExcelDemy

  143. Hello,

    You are most welcome. Thanks for your appreciation it means a lot to us. We focus on the formula explanation too. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  144. Hello Esther,

    You are most welcome. Thanks for your appreciation it means a lot to us. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  145. Hello Nguyen,

    You are most welcome. It’s okay don’t feel sorry. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  146. Hello Sanjay Dutt Dobhal,

    To calculate the time difference you need to insert the date and time based on your regional settings of Excel.
    Format the date and time based on your settings then use the CONCATENATE formula to calculate the time difference from date and time.
    =CONCATENATE(C2,””,D2)-CONCATENATE(A2,””,B2)
    Right-click the time difference cell >> choose “Format Cells” >> select “Custom” and enter the format [h]:mm

    Regards
    ExcelDemy

  147. Hello Emz,

    The purpose of computing or finding the running total in a pivot table is to observe cumulative values over time or sequential categories. It will help you to identify trends, monitor progress, and understand how data accumulates.
    For example, in sales data, a running total allows you to see how much revenue has been generated up to a certain point. It provides insights into overall performance, allowing for easier analysis of growth or decline. This is especially useful in tracking inventory, financial data, or project timelines.

    Regards
    ExcelDemy

  148. Hello AML,

    You can add IF statement to adjust font color based on background brightness in the VBA code. You can add a condition that sets the font color to white for darker backgrounds and black for lighter ones.

    Condition to add lighter color on a darker background and vice-versa:

    If ColorIndex < 15 Then
        cell.Font.Color = vbWhite ' Light font for dark backgrounds
    Else
        cell.Font.Color = vbBlack ' Dark font for light backgrounds
    End If

    Place this inside the loop where you’re setting background colors. This will ensure visibility based on the background color.

    Regards
    ExcelDemy

  149. Hello Jacquelyn Luna,

    To save stopwatch times into a column in the same spreadsheet, you can modify the VBA code in Excel. You will need to modify the StopTimer() subroutine to save the stopwatch times into a column in Excel.

    First, go to the module and update the code of Stop button.
    Add the following code to save the time into the next available row in a specific column:

    Sub StopTimer()
        Application.OnTime EarliestTime:=countDown, Procedure:="StartTimer", Schedule:=False
        
        ' Find the next empty row in column A
        Dim lastRow As Long
        lastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        ' Save the time from cell B4 into the next available row in column A
        Sheets("Sheet1").Cells(lastRow, 1).Value = Range("B4").Value
    End Sub
    
    

    The lastRow variable finds the next empty row in column A. Sheets(“Sheet1”).Cells(lastRow, 1).Value = Range(“B4”).Value saves the current stopwatch time from cell B4 into column A.

    This will store each time result when you stop the timer. Adjust the sheet and cell references as needed for your workbook.

    Regards
    ExcelDemy

  150. Hello Amy,

    You can try using a combination of SEARCH and INDEX-MATCH.

    In cell U2 on Tab 1, try the following formula:

    =IFERROR(INDEX(‘Tab 2’!B:B, MATCH(TRUE, ISNUMBER(SEARCH(‘Tab 2’!A:A, T2)), 0)), “”)

    1. SEARCH(‘Tab 2’!A:A, T2) checks if any value from column A on Tab 2 is found in the text in column T on Tab 1.
    2. INDEX(‘Tab 2’!B:B, …) returns the corresponding value from column B on Tab 2.
    3. IFERROR handles cases where no match is found.

    This should return the desired result in column U of Tab 1.

    Regards
    ExcelDemy

  151. Hi Akhila,

    You are most welcome. Thank you for your comment! We provide sample datasets to help you get started, and you can easily extend or modify them as needed. Feel free to add more rows to suit your specific use case or dataset requirements. The sample data we’ve provided is designed to be a foundation for building your own healthcare insurance claim datasets.

    Regards
    ExcelDemy

  152. Hello Akhila,

    Here is the sample dataset of Healthcare Insurance Claim Patients

    Download the Dataset: Healthcare-Insurance-Claim-Patients-Dataset.xlsx

    Regards
    ExcelDemy

  153. Hello Lacy,

    To add “Debt 5” to the multiple credit card payoff calculator, you need to copy the structure for the previous debts. Simply add a new row for Debt 5 and input the same type of information as the other debts, such as balance, interest rate, and monthly payment. Ensure that all the formulas used for the other debts are also applied to Debt 5, especially in the summary and payoff sections.

    Regards
    ExcelDemy

  154. Hello Adnan,

    You are most welcome. Thanks for your appreciation. The “50” and “100” columns represent performance benchmarks. The “50” column typically indicates a baseline or minimum acceptable performance, while the “100” column represents the ideal or target performance level. The “Actual” column shows the real performance values. The “Score” column reflects how close the actual performance is to these benchmarks, and it is multiplied by the corresponding weight to calculate the “Weighted Score.” The total weighted scores are summed to determine overall performance (82 in this case).

    Regards
    ExcelDemy

  155. Hello Ken Mambo,

    You are most welcome. To add a cumulative totals table for days present or absent using the article’s attendance sheet, you can follow these steps:

    Add a table with rows representing months and columns for each employee.
    In the cumulative table, for each month use:
    =COUNTIF(Range_of_Attendance, “P”) // For Present Days
    =COUNTIF(Range_of_Attendance, “A”) // For Absent Days

    For cumulative data, use:
    =SUM(January_Cell:Current_Month_Cell)

    This approach will give you cumulative totals month by month.

    Regards
    ExcelDemy

  156. Hello Dan,

    Gmail doesn’t natively allow sending emails via VBA due to security restrictions. However, you can try to send emails via Gmail by using CDO (Collaboration Data Objects) with SMTP.

    Sub SendEmailGmail()
        Dim objMessage As Object
        Set objMessage = CreateObject("CDO.Message")
    
        objMessage.Subject = "Test Email"
        objMessage.From = "[email protected]"
        objMessage.To = "[email protected]"
        objMessage.TextBody = "This is a test email."
    
        With objMessage.Configuration.Fields
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "your-password"
            .Update
        End With
        
        objMessage.Send
    End Sub

    Google may block this unless you’ve allowed less secure apps or set up an app-specific password if you’re using two-factor authentication (2FA).

    For Google Sheets, using Google Apps Script would be more appropriate since it integrates seamlessly with Gmail. Here’s how you can approach both:

    
    function sendEmail() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var recipient = "[email protected]";
      var subject = "Email from Google Sheets";
      var body = "This is an automated email from Google Sheets.";
      
      MailApp.sendEmail(recipient, subject, body);
    }

    Apps Script is directly integrated with Google’s ecosystem, making it an easier option for automating email alerts, and it’s more secure than trying to bypass restrictions in Gmail for VBA.

    Regards
    ExcelDemy

  157. Hello Aisling,

    Thanks for your appreciation. To add more interests you’ll need to extend the interest rate list manually and adjust the formulas to include the new entries.
    Here’s how to do it:

    1. Add the additional 10 interest rates (or however many you need) in the interest rate table.

    2. Modify any formulas that reference the original range to accommodate the extended list.

    If you’re facing difficulties, ensure all formulas reflect the new range.

    Regards
    ExcelDemy

  158. Hello,

    In our Excel file the code is working perfectly without errors.
    The possible reasons of getting errors in you end might be: Selecting a range at the edge of the worksheet or having non-contiguous selections.

    You can add a check to avoid out-of-bounds errors:

    If Not rg.Offset(1, 0) Is Nothing Then
        If rg.Value = rg.Offset(1, 0).Value And rg.Value <> "" Then
            Range(rg, rg.Offset(1, 0)).Merge
            GoTo MergeCells
        End If
    End If 

    This prevents the code from trying to access an offset that doesn’t exist (the last row in the selection).

    Regards
    ExcelDemy

  159. Hello Kelly,

    To extend the Highlight Active Row functionality outside a table, you can apply Conditional Formatting using a formula. Here’s how:

    1. Select the entire range (or the rows you want to highlight).
    2. Go to Home > Conditional Formatting > New Rule.
    3. Choose “Use a formula to determine which cells to format.”
    4. Enter the formula: =ROW()=CELL(“row”)
    5. Select your preferred format.
    This will highlight the active row across the selected range, even outside of tables.

    Regards
    ExcelDemy

  160. Hello Franck,

    You are most welcome. Since you don’t have Office 365, you can try using Google Sheets with the GOOGLEFINANCE function to import financial data for free, or explore third-party APIs like Alpha Vantage. These are good alternatives to the STOCKHISTORY function in Excel.

    Regards
    ExcelDemy

  161. Hello Y2mateOfficial,

    You are most welcome. Glad to hear that you found our template super helpful. Our calculator helps to calculate my bank interest efficiently. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  162. Hello Shimmy,

    You are most welcome. Glad to hear that you found our article helpful. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  163. Hello MP,

    You are most welcome. Modified the VBA code by unprotecting the worksheet at the start of the macro and reprotecting it at the end.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim OldValue As String
        Dim NewValue As String
        On Error Resume Next
        
        ' Check if the change is within the validation range
        If Not Intersect(Target, Range("YourRange")) Is Nothing Then
            Application.EnableEvents = False
            
            ' Unprotect the worksheet
            ActiveSheet.Unprotect Password:="YourPassword"
            
            ' Store the old value
            OldValue = Target.Value
            
            ' Store the new value entered
            NewValue = Target.Text
            
            ' If the new value is not empty
            If OldValue <> "" Then
                If InStr(1, OldValue, NewValue) = 0 Then
                    Target.Value = OldValue & ", " & NewValue
                Else
                    Target.Value = NewValue
                End If
            End If
            
            Application.EnableEvents = True
            
            ' Reprotect the worksheet
            ActiveSheet.Protect Password:="YourPassword", UserInterfaceOnly:=True
        End If
    End Sub

    Unprotects the worksheet when a selection is made. Reprotects it afterward using UserInterfaceOnly:=True, allowing macros to work while the sheet remains protected.
    Make sure to replace “YourPassword” with your actual password and “YourRange” with the cell range that contains the drop-down list.

    Regards
    ExcelDemy

  164. Hello DD,

    You’re right. The method in the article works well for fixed text but doesn’t apply to dynamic text like formulas (=A1, etc.). In Excel, there isn’t a built-in feature to rotate text 180 degrees for cell references or formulas.
    you can use a shape or text box, link the shape’s text to the formula (e.g., =A1), and then rotate the shape. While this doesn’t work directly inside a cell, it provides a solution for visualizing rotated dynamic text. It may not be ideal for every case but can help when dealing with variable data.

    Regards
    ExcelDemy

  165. Hello Sathish,

    To insert pictures horizontally across a row (e.g., A1, B1, C1…), changed the loop to increment the column index (PicColIndex) instead of the row index.
    Here’s the updated code:

    Sub InsertMultiplePicturesHorizontal()
        Dim Pictures() As Variant
        Dim PictureFormat As String
        Dim PicRng As Range
        Dim PicShape As Shape
        On Error Resume Next
        Pictures = Application.GetOpenFilename(PictureFormat, MultiSelect:=True)
        PicColIndex = Application.ActiveCell.Column
        If IsArray(Pictures) Then
            PicRowIndex = Application.ActiveCell.Row
            For lLoop = LBound(Pictures) To UBound(Pictures)
                Set PicRng = Cells(PicRowIndex, PicColIndex)
                Set PicShape = ActiveSheet.Shapes.AddPicture(Pictures(lLoop), msoFalse, msoCTrue, PicRng.Left, PicRng.Top, PicRng.Width, PicRng.Height)
                PicColIndex = PicColIndex + 1 ' Increment column instead of row
            Next
        End If
    End Sub

    Regards
    ExcelDemy

  166. Hello Sathish,

    To insert pictures horizontally across a row (e.g., A1, B1, C1…), changed the loop to increment the column index (PicColIndex) instead of the row index.
    Here’s the updated code:

    Sub InsertMultiplePicturesHorizontal()
        Dim Pictures() As Variant
        Dim PictureFormat As String
        Dim PicRng As Range
        Dim PicShape As Shape
        On Error Resume Next
        Pictures = Application.GetOpenFilename(PictureFormat, MultiSelect:=True)
        PicColIndex = Application.ActiveCell.Column
        If IsArray(Pictures) Then
            PicRowIndex = Application.ActiveCell.Row
            For lLoop = LBound(Pictures) To UBound(Pictures)
                Set PicRng = Cells(PicRowIndex, PicColIndex)
                Set PicShape = ActiveSheet.Shapes.AddPicture(Pictures(lLoop), msoFalse, msoCTrue, PicRng.Left, PicRng.Top, PicRng.Width, PicRng.Height)
                PicColIndex = PicColIndex + 1 ' Increment column instead of row
            Next
        End If
    End Sub

    Regards
    ExcelDemy

  167. Hello Savoir-faire,

    Thanks for pointing this out! Using OR with arrays can sometimes lead to unreliable results, especially in non-array formulas or older Excel versions. It’s better to use more robust methods like MATCH or COUNTIF, which handle list comparisons more efficiently and avoid edge cases where OR might fail. We used OR in this context is its simplicity and readability. For users working with Excel 365 or newer versions, this method provides an easy way to compare multiple values without needing complex functions like MATCH or COUNTIF. It’s particularly helpful for small datasets and straightforward checks. Additionally, the OR function works well for quick visual comparisons in smaller spreadsheets.

    Regards
    ExcelDemy

  168. Hello HR,

    If you want to input 0.5 as half day leave. You can use the following formula for Half Day (HD) column: =SUMPRODUCT(–($D9:$AH9=”HD”)*0.5)
    It will look for all occurrences of “HD” within the range $D9:$AH9. Each “HD” will be counted as 0.5.

    You will need to format the cells with 2 decimal places to properly show the decimal number otherwise it will show you rounded 1.

    Regards
    ExcelDemy

  169. Hello Nafiu Gide,

    There are functions that can arrange columns based on their date by rows. To combine sorting columns by dates and rearranging rows dynamically, you can use the SORT function along with INDEX to reference the sorted columns and match the data accordingly.
    Formula:
    =INDEX(A2:D5,,MATCH(SORT(A1:D1),A1:D1,0))
    This formula sorts the dates in A1:D1 and aligns the data in A2:D5 accordingly.

    Regards
    ExcelDemy

  170. Hello Calvet,

    You are most welcome. Thanks for your appreciation. We are glad to her that you found our article helpful and informative. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  171. Hello Franck,

    Yahoo Finance has recently changed its API access, leading to authentication failures for many users starting around September 6, 2024. Users are encountering errors such as “unauthorized” or “not found,” it seems that Yahoo has either restricted the free access to its data or implemented new authentication methods.

    You can use the alternatives STOCKHISTORY function in Excel (for Office 365 users) or exploring paid options from Yahoo may be necessary.

    Regards
    ExcelDemy

  172. Hello Azada Rudnicki,

    We use Microsoft Excel 365 to demonstrate the examples. The reason of getting 0 is GET.CELL function is not working properly or not defined in the Name Manager. IF function works with existing values there is no problem in the formula.
    Before using the IF formula you must need to create the name manager by using GET.CELL function.

    Steps:

    • From the Formulas tab, select Define Name.

    • A box will appear.
    • Write a name (in this case I wrote CellColor) in the Name: section of the box.
    • Use the following formula in Refers to and press OK.

    =GET.CELL(38,'Example 1'!B5)

    • Select cell D5 and insert the following formula:

    =CellColor

    Regards
    ExcelDemy

  173. Hello Terry,

    To compare the total cost of a $500,000 mortgage with simple and compound interest (annual compounding) at 5% over 20 years, you can create a spreadsheet with two sections:

    Simple Interest Formula: I=P*r*t
    Use the formula =500000 * 5% * 20 to calculate the total interest for simple interest.

    Compound Interest Formula: =500000 * (1 + 5%)^20 – 500000
    In Excel: =FV(B2, B3, 0, -B1) – B1

    Now calculate the differences and display them in a printout or spreadsheet.

    Downlaod Excel File:
    Mortgage-Interest-Comparison.xlsx

    Regards
    ExcelDemy

  174. Hello Musthafa,

    To prepare manual bin card you can follow the steps of our article,
    1. Design a standard bin card with fields like Item Name, Item Code, Quantity, etc.
    2. Ensure your item data is stored in a separate Excel worksheet with columns for Item Name, Code, Quantity, etc.
    3. To automatically pull data from your stock worksheet to the bin card, use the VLOOKUP function (or INDEX-MATCH for more flexibility).
    For example, if the item code is in cell A2 of your bin card, and your item data is in a worksheet named “StockList,” the formula to fetch the item name might look like this:
    =VLOOKUP(A2, StockList!A2:E100, 2, FALSE)
    This formula will search for the item code in column A of “StockList” and return the item name from column B.

    You can use VBA code to automatically loops through each item in the stock list, populates the bin card template, and prints it. Ensure that your bin card is properly formatted for printing. You can do this by setting print areas and page breaks.
    Run the VBA to automatically fill and print bin cards for all items in your stock.

    Sub PrintBinCards()
        Dim itemRow As Integer
        Dim lastRow As Integer
        lastRow = Sheets("StockList").Cells(Rows.Count, 1).End(xlUp).Row
        
        For itemRow = 2 To lastRow
            ' Copy item data to bin card template
            Sheets("BinCard").Range("A2").Value = Sheets("StockList").Cells(itemRow, 1).Value 'Item code
            Sheets("BinCard").Range("B2").Value = Sheets("StockList").Cells(itemRow, 2).Value 'Item name
            Sheets("BinCard").Range("C2").Value = Sheets("StockList").Cells(itemRow, 3).Value 'Quantity
            ' Add any other necessary fields here
            
            ' Print the bin card
            Sheets("BinCard").PrintOut
        Next itemRow
    End Sub

    Regards
    ExcelDemy

  175. Hello MP,

    Glad to hear that all the VBA code is working perfectly. To address the issue where the multiple selection drop-down stops working after protecting the worksheet, you need to modify the VBA code to allow edits to the specific cells while keeping the rest of the sheet protected. You can use the UserInterfaceOnly:=True parameter when protecting the sheet, which allows macros to make changes while maintaining protection for users.

    You can add UserInterfaceOnly:=True in the VBA code,

    Private Sub Workbook_Open()
        Worksheets("YourSheetName").Protect Password:="YourPassword", UserInterfaceOnly:=True
    End Sub

    It will run without unprotecting the sheet. Add this code in the Workbook_Open event to ensure the protection is applied every time the workbook is opened. Provide “YourSheetName” and “YourPassword” accordingly.

    Regards
    ExcelDemy

  176. Hello Weronika,

    To change the calendar’s weekday order you will need to change the labels name and update the VBA code date filling logic.
    To start with Monday and end with Sunday in the Excel date picker, change the order of the day labels (e.g., Label1.Caption = “Mon”, Label7.Caption = “Sun”).
    Now, use the updated Create_Calender procedure. Changed the Weekday function to use vbMonday, ensuring that the week starts on Monday. and the rest of the logic remains the same to ensure proper date filling and formatting.

    Sub Create_Calender()
    
        ' Adjust day calculation to start the week on Monday instead of Sunday
        For i = 1 To 42
            ' Use vbMonday in Weekday function to start week on Monday
            If i < Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value), vbMonday) Then
                Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value), vbMonday)), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
                
                Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value), vbMonday)), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "m/d/yyyy")
            
            ElseIf i >= Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value), vbMonday) Then
                Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value), vbMonday)), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
                
                Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value), vbMonday)), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "m/d/yyyy")
            End If
    
            ' Adjust formatting for dates within the current month
            If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value), vbMonday)), _
            ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "mmmm") = ((Month_Box.Value)) Then
                If Controls("C" & (i)).BackColor <> &HFFFFFF Then Controls("C" & (i)).BackColor = &HFFFFFF
                Controls("C" & (i)).Font.Bold = True
                
                ' Set focus on today's date
                If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value), vbMonday)), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "m/d/yyyy") = Format(This_Day, "m/d/yyyy") Then Controls("C" & (i)).SetFocus
            Else
                If Controls("C" & (i)).BackColor <> &H80000016 Then Controls("C" & (i)).BackColor = &H8000000F
                Controls("C" & (i)).Font.Bold = False
            End If
        Next i
    End Sub

    Download the Excel File:
    Calendar-Date-Starts-with-Monday.xlsm

    Regards
    ExcelDemy

  177. Hello Ahmed,

    You are most welcome. Thanks for your appreciation it means a lot to us. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  178. Hello Don,

    Thanks for your feedback! This article is written based on the Windows version of MS Office 365, where dynamic named ranges works across charts.
    It seems Excel 2016 on Mac behaves differently, allowing only workbook-level named ranges and replacing worksheet references with the workbook name. Unfortunately, for existing charts, this dynamic range may not apply correctly, and creating new charts might be the only solution on Mac.

    We appreciate your insight, and we’ll consider these differences in future content!

    Regards
    ExcelDemy

  179. Hello Tigran,

    You can use a combined formula to search with two tags in Excel. Use the SEARCH function for each tag and the AND or OR operators to meet your criteria.

    Formula:

    =AND(ISNUMBER(SEARCH(“Tag1”, A2)), ISNUMBER(SEARCH(“Tag2”, A2)))

    This formula will return TRUE if both tags are found in the text, or FALSE otherwise. Based on your criteria and need you can adjust this formula tags and reference.

    Regards
    ExcelDemy

  180. Hello Anne K,

    You can modify the logic of calculating Normal hours (column N) and OT hours (column P), specifically targeting the total at the bottom of each column. If Normal hours (column N) is less than 38, then part of the OT hours (column P) should be added to reach 38. Once Normal hours reach 38, any remaining OT should stay in column P.

    In the cell where you total your Normal hours (say N20), you can use this formula to adjust for the scenario where OT hours should be added to the Normal hours if the total normal hours are less than 38.
    Updated Formula:
    =MIN(38, SUM(N12:N19) + SUM(P12:P19))

    In the cell where you total your OT hours (say P20), adjust the OT formula so that it only shows overtime that is truly above 38 hours of work.
    Updated Formula:
    =MAX(0, SUM(L12:L19) – 38)

    This should solve the issue where overtime hours need to “make up” for missing normal hours but still allows for extra OT beyond 38 hours. Let me know if this works for you or if you need any further tweaks!

    Regards
    ExcelDemy

  181. Hello Nick Throp,

    The reason of the getting 438 error is you don’t have the on behalf permission. To set the “From” field in an email using VBA in Outlook, you must need to have “Send As” or “Send on Behalf” permissions for the generic email you want to use. Without these permissions, .From property will result in a 438 error.
    To use .From property you should contact to IT admin to obtain the required permissions for the generic email address. Once permissions are granted, the VBA code should work without throwing an error.

    Here’s an example of setting the “From” field:
    MailItem.SentOnBehalfOfName = “[email protected]

    This approach will allow you to send emails on behalf of another account if you get the permission.

    Regards
    ExcelDemy

  182. Hello HC,

    Thank you for your comment! The confusion arises from auto-rounding the numbers (not showing decimal places). The actual values used in the calculation are a weighted average selling price of 140.5000 and a weighted average variable cost of 103.3000, resulting in a contribution margin of 37.2000 per unit.

    Calculation will be:

    50000 / (140.5000 – 103.3000) = 50000 / 37.2000 ≈ 1344.0860 units as the break-even point.
    I’ve take four decimal places to avoid confusion.

    Apologies for the earlier confusion, and I hope this clarifies it!

    Regards
    ExcelDemy

  183. Hello Nguyen,

    We checked the Exercise 02 it’s working perfectly in the problem sheet.

    Attached the Excel File:
    Practice Exercises for SUMIF.xlsm

    Please verify that the values in G6:G93 are numeric and match the criteria in K9 cell exactly, without extra spaces or formatting differences.

    Regards
    ExcelDemy

  184. Hello Nguyen,

    We checked the Exercise 02 it’s working perfectly in the problem sheet.

    Attached the Excel File:
    Practice Exercises for SUMIF.xlsm

    Please verify that the values in G6:G93 are numeric and match the criteria in K9 cell exactly, without extra spaces or formatting differences.

    Regards
    ExcelDemy

  185. Hello Brea Kelley,

    Yes, you can auto populate venue prices in Column G of the Events sheet based on the venue listed in Column F by using the VLOOKUP function.

    Use the following formula:
    =IFERROR(VLOOKUP(F2, ‘Venue Costs’!$A$1:$B$6, 2, FALSE), “Price not found”)

    Change the cell reference of of Venue Costs sheet based on your data.

    Download the Excel file:
    Auto Populate Value from Another Sheet.xlsx
    Regards
    ExcelDemy

  186. Hello Dear,

    You are most welcome. We are glad to hear that our resources are fantastic to test your Excel skills from different aspects. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  187. Hello Damien Connolly,

    Yes, setting the variable Q to 7 in the VBA code means the loop will start from the 7th sheet, skipping sheets 1 through 6. The number 7 is used to specify where the data consolidation begins, so only sheets from the 7th onward will be included. This choice allows you to omit the first six sheets from the consolidation process. You can adjust the number if you want to include or exclude different sheets.

    Regards
    ExcelDemy

  188. Hello Chad Sellers,

    Thank you for your feedback! You’re absolutely right—thank you for catching that! The correct range to sum Daniel’s marks should indeed be =SUM(C5:F5), which sums the marks across all subjects for Daniel. I’ve updated the article to reflect this change, ensuring it matches the subsequent structured reference example. I appreciate your attention to detail and for bringing this to my attention!

    Regards
    ExcelDemy

  189. Hello Chad Sellers,

    Thank you for your feedback! You’re correct that structured table references typically apply to the same row, which can cause confusion after sorting changes the row order. I’ve updated the article to include a section that addresses how structured references behave after sorting and how to maintain consistent references to the same cell even when the row order changes.. You can use INDEX-MATCH combination or the XLOOKUP function to maintain references correctly after sorting. Your input is greatly appreciated!

    Regards
    ExcelDemy

  190. Hello Joe,

    Yes you can create a new cell style of your choice in Excel. In our article we have shown the steps to craete customized new cell style.
    How to Create New Cell Styles in Excel

    Excel provides the flexibility for users to create custom cell styles.

    Steps:

    • Go to the Home tab.
    • Select Cell Styles from the Style group.
    • Choose New Cell Style.

    creating new cell styles in Excel

    • In the Style window, give the style a name, such as Custom Style 1, and choose its elements, such as number format, font, and borders.
    • Click the Format option to customize its elements.

    style window for customization

    • Use the Format Cells window for further customization, and observe changes in the preview or sample panel.

    For example, we can demonstrate this with a fill color such as “Gold Lighter 80%“.

    • Click OK to save.

    format cells window for custom cell styles

    After creating the style, you can easily apply it to selected cells using the Cell Styles option from the Home tab.

    applying custom cell styles in Excel

    Custom cell styles are available only in the specific workbook where they were created.

    Regards
    ExcelDemy

  191. Hello Rat,

    It is possible to return a single value based on muliple values of another column. You can do it by using combination of the nested IF and OR functions without needing six separate formulas.

    Use the following formula and drag the fill handle down to apply the formula to the rest of the cells in Column B.
    =IF(OR(A2=”Red”, A2=”Orange”, A2=”Yellow”), “Warm”, IF(OR(A2=”Green”, A2=”Blue”, A2=”Purple”), “Cool”, “”))

    The formula uses OR to check if the color in Column A matches any “Warm” colors (Red, Orange, Yellow) and returns “Warm”; if it matches any “Cool” colors (Green, Blue, Purple), it returns “Cool”. If none of these are true, it returns an empty value.

    Regards
    ExcelDemy

  192. Hello Shibani Agrawal,

    You can use our sample supply chain data modify it based on your requirements.

    Download the Excel file:
    Dataset of Supply Chain: Sample Supply Chain Dataset.xlsx

  193. Hello Nidhi,

    You are most welcome. Thanks for reading our article. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  194. Hello Georg,

    You are most welcome. Glad to hear that you found our article great and useful. Yes, this article will help you to know everything about Scatter Chart.
    Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  195. Hello Vanessa,

    You will need to extend the grouping manually to add new columns to an existing grouping without removing and reapplying the grouping. Follow the steps below to do so:

    1. If your current group is collapsed, click the plus sign (+) next to the grouped columns to expand them.
    2. Select the new columns that are adjacent to the existing grouped columns.
    3. Go to the Data tab >> from Outline group >> select Group.

    Now, Excel will automatically add the new columns to the existing group.

    Regards
    ExcelDemy

  196. Hello David,

    You are most welcome. Thanks for your feedback! The INDEX is an array function it recalculates every time there’s a change in the referenced data, especially if you’re working with large datasets for these reasons the file may slowdown. Though OFFSET is volatile function, but in some cases, calculates faster based on the data structure and size.
    If your dataset is extensive, you might see better performance with OFFSET.

    It’s great to hear that OFFEST is more efficient for your data type. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  197. Hello Mate,

    You are most welcome. We are glad to hear that the you found the article helpful. The AutoFit shortcut method is really a time saver. Please try this method in your Excel project.

    Thanks for your appreciation. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  198. Hello Mr. John,

    You are most welcome. Thanks for your appreciation. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  199. Hello Amber,

    It is possible to find and delete multiple values at once. To delete the first occurrences of duplicate values you can use a helper column to find out those values then apply filter to delete it.

    Insert the following formula in a helper column:
    =IF(COUNTIF(A2:A12, A2) > 1, IF(COUNTIF(A$2:A2, A2)=1, “Delete”, “Keep”), “Keep”)
    It will check the name appears more than once. If it does, it marks the first occurrence with “Delete” and subsequent duplicates with “Keep.” For unique names it will also return “Keep”.

    Now, to apply filter to your data from Data tab >> select Filter.
    Then select Delete from helper column.
    Finally, select all the names and press on Delete.

    Download the Excel file:
    Remove Duplicates First Occurences.xlsx

    Regards
    ExcelDemy

  200. Hello Candy Neal,

    To count how many users were active during June 2024, you can use a formula that checks if the active date is before or equal to the end of the month and if the inactive date (if it exists) is after the beginning of the month.

    Insert the following formula in cell D2 and drag it down:

    =IF(AND(B2<=DATE(2024,6,30), OR(C2="", C2>=DATE(2024,6,1))), 1, 0)
    If you want to use it for other months change the date range in the formula.

    null

    To count total active users, insert the following formula in E2 cell:

    =SUM(D2:D6)

    Download the Excel file:
    Count Total Active Users.xlsx

  201. Hello Enamul Sekh,

    You can download the Excel file free of cost just by providing your valid email address. All the answers are given in the Excel file. To get the files go to the Top 100 MCQ of Excel section of this post and enter your email address. Then check your email immediately after to get the download links.
    Please insert your name and email address then click on Download button.

    Best Regards
    ExcelDemy

  202. Hello Puri Nabaraj,

    Thank you so much for your kind words! We’re glad to hear that our resources and blogs have helped you learn Excel formulas in such a short time. Your support motivates us to keep providing valuable content. Keep up the great work, and feel free to reach out if you have any questions or need further assistance!

    Thanks again for your valuable feedback. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  203. Hello Jerry Boor,

    Thank you for pointing that out! The $50,000 interest would indeed be for the entire year, not just one day’s interest. The formula and method used are correct, but the value for time was incorrectly set, which caused the confusion. I have since updated the article to reflect the correct time value (1/365) for daily interest. I appreciate your input in helping to ensure the accuracy of the content. Please feel free to check out the revised version of the article. Thanks again for your valuable feedback!

    Regards
    ExcelDemy

  204. Hello Okto,

    You are most welcome. Glad to hear that the formula was great to you. We always try to provide the best useful working formulas to ease your works.
    Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  205. Hello Dear,

    Thanks for your appreciation! Glad to hear that the Excel templates are helpful for organizing your tax calculations. Feel free to reach out if you need any further assistance while working on your tax returns. Your feedback means a lot!

    Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  206. Hello Paul,

    You are most welcome. Glad to hear that the budget sheet is helpful for you. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  207. Hello Maeenuddin Khan,

    Glad to hear your appreciation. Thank you so much for your kind words! It’s great that the explanation helped you with finding a suitable analytical tool in Excel for your research. It’s always rewarding to know that the content makes a positive impact. If you have any further questions or need more guidance, feel free to reach out anytime. Keep up the great work with your research!

    Regards
    ExcelDemy

  208. Hello Jon Peltier,

    We appreciate your input. The base values in the stacked waterfall chart are calculated by starting with an initial base of 10,000 and adjusting each subsequent month’s base based on the sales flow. The base values are correctly calculated by considering the net change (positive minus negative values). The chart is intended to reflect cumulative totals, not simply stacked values. However, I appreciate your input and will ensure that this methodology is explained more clearly to avoid any confusion.

    Regards
    ExcelDemy

  209. Hello,

    Thanks for your feedback! While it’s true that three of the methods involve opening Excel, we included them to cater to users who may already have Excel open or prefer built-in Excel tools. The aim was to offer a variety of approaches for different user needs. However, if you’re strictly looking for methods that avoid opening Excel, the PowerShell and third-party tool methods are the best fit. We appreciate your thoughts and will keep this in mind for future articles.

    Regards
    ExcelDemy

  210. Hello Antony,

    You are most welcome. Glad to hear that helped you to solve your problem. If you have any more questions, feel free to ask!
    Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  211. Hello Monica,

    Yes you will need to use Option 2 to show time in legend.
    To handle a pie chart with time values and show those times in the legend, follow these steps given below:

    1. Format you time data as time in Excel.
    2. Next, use Method 2 from the article, which involves adding a helper column that combines both the time data and category labels.
    3. Create your pie chart using this combined helper column as the legend, ensuring that the time is displayed as part of the legend entries.

    This will allow you to show both time and categories in the legend effectively.

    Regards
    ExcelDemy

  212. Hello Mohammad Afzal,

    Glad to hear that you learned well. Our aim is to help you learn Excel easily. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  213. Hello Deanna,

    Thank you! We are glad to hear your appreciation. If you don’t know the start date yet, you can set up the formula to reference the cell where the start date will be entered later. This way, once you input the start date, the calculation will automatically update. You can set up your sheet and place all the required formulas then based on your input calculation will happen automatically.

    Let us know if you need further help!

    Regards
    ExcelDemy

  214. Hello ExcelUser1,

    Thank you for your feedback! The article primarily focuses on different methods to paste or display content, which may not fully address issues with copying merged cells directly. The challenge often lies in Excel’s limitations when copying merged cells.
    If you’re facing specific issues with copying, we recommend trying unmerging cells before copying, or using VBA as outlined in the article. If this doesn’t resolve your issue, feel free to share more details, and we’ll be happy to assist further!

    Regards
    ExcelDemy

  215. Hello Duncan,

    The problem occurs because both the pivot table and its associated chart are copied and pasted into the email body simultaneously, overlapping each other. To resolve this, you should separate the chart from the pivot table when copying. You will need to adjust the code to specifically copy the range of cells containing the pivot table, ensuring that it does not include the chart.

    We updated the VBA code where a Boolean parameter (includeChart) was added. When set to False, it will delete the chart shapes before exporting the image to ensure that only the pivot table is copied.
    In the PasteRangeinMail subroutine, this parameter is set to False, so charts are excluded by default.

    Sub PasteRangeinMail()
        Dim FilePath As String
        Dim Outlook As Object
        Dim OutlookMail As Object
        Dim HTMLBody As String
        Dim rng As Range
        On Error Resume Next
        Set rng = Selection
        If rng Is Nothing Then Exit Sub
        With Application
            .Calculation = xlManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        Set Outlook = CreateObject("outlook.application")
        Set OutlookMail = Outlook.CreateItem(olMailItem)
        Call createImage(ActiveSheet.Name, rng.Address, "RangeImage", False) ' Pass False to exclude charts
        FilePath = Environ$("temp") & "\"
        HTMLBody = "<span LANG=EN>" _
                 & "<p class=style1><span LANG=EN><font FACE=Times New Roman SIZE=4>" _
                 & "Dear Concerned," _
                 & "<br>" _
                 & "This is the Excel data you requested for:<br> " _
                 & "<br>" _
                 & "<img src='cid:RangeImage.jpg'>" _
                 & "<br>" _
                 & "<br>Kind Regards!!!!!</font></span>"
        With OutlookMail
            .Subject = ""
            .HTMLBody = HTMLBody
            .Attachments.Add FilePath & "RangeImage.jpg", olByValue
            .To = "[email protected]"
            .CC = " "
            .Display
        End With
    End Sub
    Sub createImage(SheetName As String, rngAddrss As String, nameFile As String, includeChart As Boolean)
        Dim rngJpg As Range
        Dim Shape As Shape
        ThisWorkbook.Activate
        Worksheets(SheetName).Activate
        Set rngJpg = ThisWorkbook.Worksheets(SheetName).Range(rngAddrss)
        rngJpg.CopyPicture
        With ThisWorkbook.Worksheets(SheetName).ChartObjects.Add(rngJpg.Left, rngJpg.Top, rngJpg.Width, rngJpg.Height)
            .Activate
            If Not includeChart Then ' If charts are excluded
                For Each Shape In ActiveSheet.Shapes
                    Shape.Delete
                Next Shape
            End If
            .Chart.Paste
            .Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
        End With
        Worksheets(SheetName).ChartObjects(Worksheets(SheetName).ChartObjects.Count).Delete
        Set rngJpg = Nothing
    End Sub
    

    You can adjust the includeChart parameter as needed to ensure that only the pivot table is included in the email body.

    Regards
    ExcelDemy

  216. Hello Pat Grappe,

    It might be a version difference or an issue with Excel’s settings. You can try the following steps:

    1. Ensure that the cells where the labels will be printed are selected.
    2. Go to the Home tab in Excel.
    3. Click on the Borders option in the Font group, and choose the desired border style from the dropdown.

    If the Grid option is not visible, this workaround will still allow them to set borders properly.

    Regards
    ExcelDemy

  217. Hello Michael,

    You are most welcome. Keep learning Excel with ExcelDemy. We provided a formula to auto populate events from entry data to calendar sheet.

    Regards
    ExcelDemy

  218. Hello Michael,

    You are most welcome. You can auto populate events in the calendar sheet from your entry data sheet but Excel formulas has limitations over handling multiple overlapping events. Formulas will concatenate multiple events into a single cell, but each overlapping event will be separated by a line break or other delimiters.

    You can use the following formula in your event cell to auto populate events.
    =IFERROR(TEXTJOIN(CHAR(10), TRUE, FILTER(‘Entry Data’!$B$2:$B$100, (‘Entry Data’!$D$2:$D$100 <= B10) * ('Entry Data'!$D$2:$D$100 + 'Entry Data'!$C$2:$C$100 - 1 >= B10) * (‘Entry Data’!$E$2:$E$100 = 8) * (‘Entry Data’!$F$2:$F$100 = 2024))), “”)

    Based on Month please change the Month Number and Cell reference for each month and each cell.

    If an event spans multiple days, the formula will check if the current day falls within the event duration and will display the event in the cell. If multiple events occur on the same day, they will all be concatenated in the same cell.

    Download the Excel file:
    Monthly Event Chart

    Regards
    ExcelDemy

  219. Hello Mill,

    The VBA code is tailored to operate within a specific workbook context and may not be compatible as a stand-alone Excel Add-in without some adjustments. Add-ins typically work across multiple workbooks, so you need to use the modified code to ensure it can handle different active workbooks, rather than just ThisWorkbook.

    To make this VBA code work as an add-in, follow the steps given below:

    1. Replace references to ThisWorkbook with ActiveWorkbook.
    2. Create a new module within the VBA editor and place your code there. This is necessary for turning it into an add-in.

    Sub combine_multiple_sheets_addin()
        Dim Row_1, Col_1, Row_last, Column_last As Long
        Dim headers As Range
        Dim WB As Workbook
        Dim wX As Worksheet
        Dim Ws As Worksheet
        
        Set WB = ActiveWorkbook ' Change from ThisWorkbook to ActiveWorkbook
        Set wX = WB.Sheets.Add
        wX.Name = "Consolidated"
        
        ' InputBox to select headers
        Set headers = Application.InputBox("Choose the Headers", Type:=8)
        headers.Copy wX.Range("A1")
        
        Row_1 = headers.Row + 1
        Col_1 = headers.Column
        
        ' Loop through each worksheet in the active workbook
        For Each Ws In WB.Worksheets
            If Ws.Name <> "Consolidated" Then
                Ws.Activate
                Row_last = Cells(Rows.Count, Col_1).End(xlUp).Row
                Column_last = Cells(Row_1, Columns.Count).End(xlToLeft).Column
                Range(Cells(Row_1, Col_1), Cells(Row_last, Column_last)).Copy _
                    wX.Range("A" & wX.Cells(Rows.Count, 1).End(xlUp).Row + 1)
            End If
        Next Ws
        
        Worksheets("Consolidated").Activate
    End Sub

    3. Once the code is ready, save the workbook as an Excel Add-in (.xlam) file..
    4. Install and test the add-in to verify that it works across different workbooks.

    Now, this you can use this code as an add-in. If any further issues arise, you can refine the code further depending on their specific needs.

    Regards
    ExcelDemy

  220. Hello Jennifer Almeida,

    In our existing VBA code, the previously stored values in the output cell are overwritten when a new selection is made. This happens during the Button_Click event when the new selection is written directly into CheckListOutput, replacing the previous value.

    To preserve previous selections, we adjusted the logic so that the newly selected value is appended to the existing content rather than overwriting it. Here’s an update to your code:

    Sub Button_Click()
        Dim buttonShape As Shape, listOption As String, M As Integer
        Dim xP As String
        Set buttonShape = ActiveSheet.Shapes(Application.Caller)
        Set checkListBox = ActiveSheet.checkList
    
        If checkListBox.Visible = False Then
            checkListBox.Visible = True
            buttonShape.TextFrame2.TextRange.Characters.Text = "Tick the Passed Students"
            resultStr = Range("CheckListOutput").Value
            If resultStr <> "" Then
                resultArr = Split(resultStr, ";")
                For M = checkListBox.ListCount - 1 To 0 Step -1
                    xP = checkListBox.List(M)
                    If resultArr(0) = xP Then
                        checkListBox.Selected(M) = True
                        Exit For
                    End If
                Next M
            End If
        Else
            checkListBox.Visible = False
            buttonShape.TextFrame2.TextRange.Characters.Text = "Click Here"
            For M = checkListBox.ListCount - 1 To 0 Step -1
                If checkListBox.Selected(M) = True Then
                    listOption = checkListBox.List(M)
                    Exit For ' Ensure only one selection
                End If
            Next M
    
            ' Append the new selection if it's not already in the output
            If listOption <> "" Then
                If InStr(Range("CheckListOutput").Value, listOption) = 0 Then
                    If Range("CheckListOutput").Value <> "" Then
                        Range("CheckListOutput").Value = Range("CheckListOutput").Value & ";" & listOption
                    Else
                        Range("CheckListOutput").Value = listOption
                    End If
                End If
            End If
        End If
    End Sub
    

    Regards
    ExcelDemy

  221. Hello Wulan Ramayani,

    To develop you excel skill learn and practice the exercises given below.
    Excel Data for Practice Free Download
    Excel Practice Exercises PDF with Answers
    Explore these categories you will find out more data entry practice here: Data Entry Practice Test & Quiz
    Interview Questions with Excel Topics
    MCQ Questions on MS Excel

    To develop your skill from beginner to advanced level you can explore our Learn Excel page.

    Regards
    ExcelDemy

  222. Hello Jason Ngo,

    Thanks for your suggestion, we appreciate it deeply.Enabling Excel 4.0 macros and restarting the file should indeed help with resolving the #block error. Your step-by-step explanation is really helpful, especially the reminder to close and reopen the file for the changes to take effect. Much appreciated.

    Regards
    ExcelDemy

  223. Hello Babajide,

    Thanks! We are glad to hear that you found it great. We try our best to provide excellent services. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  224. Hello Yufeng,

    You are most welcome. Thanks for your appreciation it means a lot to us. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  225. Hello Dennis,

    Thank you for the feedback! To use dynamic dropdowns, using UserInterfaceOnly = True can indeed allow macros to run while keeping the sheet protected. The UserInterfaceOnly setting allows VBA to make changes, but it doesn’t allow certain actions like adding or modifying data validation directly on a protected sheet.

    Here’s an updated VBA code that temporarily unprotects the sheet to apply the data validation and then reprotects it:

    Sub Dynmc_DropDown_Range_Protected()
        Dim wrk As Worksheet: Set wrk = Worksheets(3)
        Dim nameText As String
        Dim nameRng As Range
        
        ' Temporarily unprotect the sheet
        wrk.Unprotect Password:="password"
        
        nameText = "DynamicList"
        Set nameRng = wrk.Range("$C:C")
        ThisWorkbook.Names.Add Name:=nameText, RefersTo:=nameRng
        With wrk.Cells(3, "B").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="=" & nameRng.Address
        End With
        
        ' Reprotect the sheet after making changes
        wrk.Protect Password:="password", UserInterfaceOnly:=True
    End Sub
    

    Regards
    ExcelDemy

  226. Hello Heather,

    May be the problem is related to Excel’s limitations when applying multiple conditional formatting rules or when handling a large range with VBA. This could be causing the code to slow down or enter an infinite loop. A possible solution is to break the range into smaller sections or optimize the code by limiting the number of FormatConditions applied at once.
    Here, I’m Reviewing the loop logic and error handling, could also help prevent the program from freezing.

    I used FormatConditions.AddUniqueValues with xlDuplicate to highlight duplicates directly.
    Then used the Chunk size logic is retained to process large ranges in smaller sections.
    All the ranges are based on our existing Excel sheet.
    Use the updated VBA code:

    
    Sub HighlightDuplicatesWithColors()
        Dim ws As Worksheet
        Dim rng As Range
        Dim cell As Range
        Dim chunkSize As Long
        Dim i As Long, lastRow As Long
    
        Set ws = ThisWorkbook.Sheets("VBA") ' Adjust the sheet name
        lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ' Find the last used row in column A
        chunkSize = 100 ' Set the chunk size to 100 rows (adjust as needed)
        
        On Error GoTo ErrorHandler ' Enable error handling
        For i = 5 To lastRow Step chunkSize
            ' Set the range for the current chunk
            Set rng = ws.Range("C" & i & ":C" & Application.Min(i + chunkSize - 1, lastRow))
            ' Remove any previous conditional formatting
            rng.FormatConditions.Delete
            ' Apply conditional formatting for duplicates
            With rng
                .FormatConditions.AddUniqueValues
                .FormatConditions(1).DupeUnique = xlDuplicate
                .FormatConditions(1).Interior.Color = RGB(255, 255, 0) ' Set the color for duplicates
            End With
        Next i
        MsgBox "Duplicate highlighting completed."
        Exit Sub
    ErrorHandler:
        MsgBox "An error occurred: " & Err.Description
        Resume Next
    End Sub
    

    Regards
    ExcelDemy

  227. Hello Kay D,

    The issue with using 248 rows in your INDEX and SMALL formula is not the problem, as Excel can handle thousands of rows in such functions. It sounds like the issue may be related to how the INDEX and SMALL functions are handling the range of cells. If the formula is getting values from the wrong row, there could be a mismatch between the row numbers used in the SMALL function and the actual row numbers in your data.

    Make sure that the ranges in your formula are correctly aligned with your dataset. Double-check that the criteria and ranges match, and ensure there are no offset errors.

    Adjust the “-4” ensure that the offset corresponds to the starting row of your range. Try modifying the offset if necessary, depending on your data structure.
    Check Criteria Range: Ensure the $C$5:$C$12 range is aligned with your criteria column.

    You can share your formula if you want. We can help to troubleshoot it further!

    Regards
    ExcelDemy

  228. Hello Stephen,

    It’s not possible to set the color or grayscale options to appear as default when opening the print menu in Excel. Those settings are controlled at the system level via your printer preferences. In Excel, you can still access these settings, but each time you print, you’ll need to manually adjust the color options unless they’ve been preset in your system’s printer properties.

    To adjust your printer preferences at the system level rather follow these steps:
    1. Go to your Devices and Printers settings in your operating system.
    2. Right-click on your printer >> select Printing Preferences, and configure your preferred settings there.
    This will set your defaults across all programs, including Excel, without needing to change them each time in the print menu.

    Regards
    ExcelDemy

  229. Hello Miran,

    You are most welcome. To split data into separate sheets based on different columns like Sales Person or Region, you will need to adjust the VBA code.

    Here is the updated VBA code:

    Sub SplitExcelSheet_byColumn()
        Dim WorkRng As Range
        Dim xWs As Worksheet
        Dim uniqueValues As Collection
        Dim cell As Range
        Dim NewSheet As Worksheet
        Dim ExcelTitleId As String
        Dim columnNumber As Integer
        Dim i As Long
        
        On Error Resume Next
        ExcelTitleId = "Column to Split By"
        
        ' Select the range and the column number to split by
        Set WorkRng = Application.InputBox("Select the range:", "Split Excel Sheet by Column", Type:=8)
        columnNumber = Application.InputBox("Enter the column number to split by (e.g., 1 for Column A):", ExcelTitleId, 1, Type:=1)
        
        Set uniqueValues = New Collection
        ' Start loop from the second row to skip headers
        For Each cell In WorkRng.Columns(columnNumber).Cells
            If cell.Row > WorkRng.Cells(1, 1).Row And cell.Value <> "" Then
                On Error Resume Next
                uniqueValues.Add cell.Value, CStr(cell.Value)
                On Error GoTo 0
            End If
        Next cell
        
        Application.ScreenUpdating = False
        Set xWs = WorkRng.Parent
        
        ' Create new sheets based on unique values
        For i = 1 To uniqueValues.Count
            WorkRng.AutoFilter Field:=columnNumber, Criteria1:=uniqueValues(i)
            
            Set NewSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            NewSheet.Name = uniqueValues(i)
            
            WorkRng.SpecialCells(xlCellTypeVisible).Copy NewSheet.Range("A1")
            WorkRng.AutoFilter
        Next i
        
        Application.ScreenUpdating = True
    End Sub
    

    In the first InputBox insert the dataset range.
    Then choose the column number to spilt the data.

    Regards
    ExcelDemy

  230. Hello Demba,

    We have created a dataset for Customs Compliance Monthly Report in Excel based on your given fields.

    Download the Excel file from here : Customs Compliance Monthly Report.xlsx

    Regards
    ExcelDemy

  231. Hello Joost,

    Hope you are doing well. Thank you for your feedback! Applying the same color to all bars in a group might not be ideal if they represent different years or categories. It’s better to use different colors or shades for clarity. This helps in distinguishing the data within the group more effectively. That’s why we avoided same color across the grouped bar chart.

    But if it is helpful to you to visualize the bars then you can do it.
    As Excel doesn’t automatically apply the same color across the grouped bar chart categories. You can manually adjust the colors by selecting each data series and applying the same color.
    Follow the steps to do so:
    1. Click on one of the bars you want to change.
    2. Right-click and select Format Data Series.
    3. Choose Fill and pick your desired color.
    Repeat for each data series to maintain consistent coloring across all groups.

    Regards
    ExcelDemy

  232. Hello Fritz,

    The VBA code is working fine in our end.

    Make sure to follow all the steps:
    Create the drop down list first then place the VBA code in that sheet.
    Double-check that the VBA code is correctly placed in the Sheet where the data validation with drop down list exists.
    Also, ensure that there are no conflicts with other existing macros or data validation settings. If the issue persists, try testing the code in a new worksheet to see if it works there.

    You can download our workbook for testing purpose: Selecting Multiple Options from Drop Down.xlsm

    Regards
    ExcelDemy

  233. Hello Kevin,

    Great to hear that it worked great. You’re absolutely correct Method 6 returns the row numbers where there’s a match, not the number of rows. We updated the article to correct this typo. Appreciate your attention to detail!

    Thanks for your feedback. Keep exploring Excel with ExcelDemy.

    Regards
    ExcelDemy

  234. Hello Balaji,

    All the solutions are available in the Solution sheet of the Excel Workbook. Excel file is given in the Download practice Workbook section.

    All the solutions are given part by part in the Solution sheet.

    Regards
    ExcelDemy

  235. Hello Craig,

    Yes. It is possible to download the graph. We always provide a ready to use Excel file in our Download section.
    Please download the chart from here: Plot Sieve Analysis Graph.xlsx

    Regards
    ExcelDemy

  236. Hello Mel,

    You can use a custom formula in Excel’s conditional formatting to highlight cells in column B and C based on the presence or absence of specific text (deed) in another column G.

    Follow the steps given below:

    1. Select the range in columns B and C that you want to format.
    2. Go to Home > Conditional Formatting > New Rule.
    3. Choose “Use a formula to determine which cells to format”.
    4. Enter the following formula: =ISERROR(SEARCH(“deed”,$G1))
    This will highlight cells in columns B and C if the corresponding cell in column G does not contain “deed.”
    5. Set the formatting style and click OK.

    Regards
    ExcelDemy

  237. Hello Femi,

    Thanks for your appreciation. You will need to update the report card based on the student ID then you will be able to print the results for all names of the list.
    While changing the name please update marks of each student.

    Regards
    ExcelDemy

  238. Hello Don,

    There is no formula in cell C5, and cells C5 only contain marks. So, there’s no risk of creating a circular reference. The steps should work as described. Can you please check out this is the right article you commented?
    Thanks for your feedback.

    Regards
    ExcelDemy

  239. Hello Saad,

    Thanks for your feedback. Apologies for the typo. You are right the correct answer should be “Alexander – Home Theater”. We’ve updated it now. Thanks for your understanding!

    Regards
    ExcelDemy

  240. Hello Mohammad Afzal,

    It’s great decision. ExcelDemy can help you to start your learning. Our website contents are designed to help you learn Excel from basics to advanced.
    Follow our Learn Excel page here you will find all the topics related to Excel.
    Explore all the Tutorial categories to explore more. We also provide courses to learn Excel technology.

    Regards
    ExcelDemy

  241. Hello Iliyana,

    Hope you are doing well. Sorry to hear your problem. While executing the code you need to follow the steps.
    First, open the sheet from where you want to copy the filtered values. Suppose you selected Dataset sheet.
    Go to Developer tab >> from Macros >> select Sub Copy_AutoFiltered_VisibleRows_NewSheet().
    Now, you will get the filtered values in Sheet3. Make sure you have created a sheet named Sheet3 otherwise you will get the error out of “Subscript out of range” and it will show a yellow fill in Worksheets(“Sheet3”).Range(“a1”).PasteSpecial Paste:=xlPasteAll line.

    If you want to get your filtered values in your desired sheet. Just name it in the code.

    Sub Copy_AutoFiltered_VisibleRows_NewSheet()
    'Declares CatSites i.e. Category of the Sites
    Dim CatSites As String
    'Education is the filtering criteria
    CatSites = "Education"
    'AutoFilter for a specific category of the sites which is in Column B
    ActiveSheet.Range("A1:E14").AutoFilter
    ActiveSheet.Range("A1:E14").AutoFilter field:=2, Criteria1:=CatSites
    'Copy only visible cells
    ActiveSheet.Range("A1:E14").SpecialCells(xlCellTypeVisible).Copy
    'Provide your sheet name instead of Sheet3 or create a sheet name it Sheet3.
    Worksheets("Sheet3").Range("A1").PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False
    'Remove AutoFilter
    ActiveSheet.AutoFilterMode = False
    End Sub
    

    If you still find the issue please attach a image or error name and it’s description. We will be happy to help you.

    Regards
    ExcelDemy

  242. Hello Nithin,

    Your deep learning projects some of the tasks you can accomplish in Excel, such as organizing data and performing calculations. But the full scope of your project would require additional software and programming.

    1. You will need to use Optical Character Recognition(OCR) software to scan the question paper and answer sheet.
    It will recognize and extract question numbers and their corresponding marks.

    2. Then use Python scripts to process OCR output, map questions to answers, and recognize marks.

    3. Finally you can use Python libraries like pandas and openpyxl to create and format an Excel file with the extracted data.

    Regards
    ExcelDemy

  243. Hello Lorcan,

    As all the steps are sequential the issue of not seeing the option to add dynamic content in the ‘To’ box when creating the ‘Send an Email (v2)’ action in Power Automate might be due to a bug or a temporary glitch.

    Try the following steps to troubleshoot,

    1. Refresh your browser or reopen Power Automate.
    2. Clear your browser cache.
    3. Ensure all fields are properly configured before the ‘Send an Email (v2)’ action.
    4. Check for any updates to Power Automate.

    If the issue persists, consider reaching out to Microsoft support for further assistance. You can try our 2nd method until the issue is solved.

    Regards
    ExcelDemy

  244. Hello Alex,

    You are most welcome. Thanks for your appreciation, it’s great to hear that you found the article helpful.
    To get cell color from a specific cell you can use the user defined function.
    Copy paste the VBA code:

    Function GetCellColor(rng As Range) As Long
        GetCellColor = rng.Interior.Color
    End Function
    

    Use this function in a cell to reference the specific cell from another sheet whose color you want to retrieve.

    You also can follow this article: How to Get Cell Color in Excel

    Regards
    ExcelDemy

  245. Hello Don,

    It seems you’re facing issues with dynamic named ranges on Mac OS Excel v16.16. Our article is written based on based on Windows OS Excel 365.

    Here are some steps you may try to troubleshoot the problem.

    Date =OFFSET(Combined!$A$2, 0, 0, COUNTA(Combined!$A:$A)-1, 1)
    Value =OFFSET(Combined!$B$2, 0, 0, COUNTA(Combined!$B:$B)-1, 1)

    When replacing the series formula in your chart, ensure it looks like this.
    =SERIES(“Combined”, Combined!Date, Combined!Value, 1)

    If automatic updates are causing errors, manually update the data ranges:

    1. Right-click on the chart and select Select Data.
    2. Edit the series and input the named ranges manually.

    Ensure that your Excel on Mac OS is up to date. Sometimes, these issues are resolved with software updates. There might be compatibility issues between Windows Excel 365 and Mac OS Excel v16.16. Dynamic named ranges sometimes behave differently across platforms.

    Regards
    ExcelDemy

  246. Hello Kurt Kruger,

    Our existing code generate Code 128 barcodes, primarily focusing on Code 128B, and can switch to Code 128C for efficient numeric encoding when needed. Modified the existing VBA code to include Code 128A encoding, which includes upper-case letters, control characters, and special characters. The start, checksum, and stop characters are added to generate a valid Code 128A barcode.

    Option Explicit
    Public Function Code128A(SourceString As String) As String
        Dim Counter As Integer
        Dim CheckSum As Long
        Dim mini As Integer
        Dim dummy As Integer
        Dim Code128A_Barcode As String
        
        If Len(SourceString) > 0 Then
            For Counter = 1 To Len(SourceString)
                Select Case Asc(Mid(SourceString, Counter, 1))
                    Case 0 To 95
                    Case Else
                        MsgBox "Invalid character in barcode string" & vbCrLf & vbCrLf & "Please only use characters supported by Code 128A", vbCritical
                        Code128A = ""
                        Exit Function
                End Select
            Next
            
            Code128A_Barcode = Chr(103) ' Start code for Code 128A
            
            For Counter = 1 To Len(SourceString)
                dummy = Asc(Mid(SourceString, Counter, 1))
                If dummy >= 0 And dummy <= 31 Then
                    Code128A_Barcode = Code128A_Barcode & Chr(dummy + 64)
                ElseIf dummy >= 32 And dummy <= 95 Then
                    Code128A_Barcode = Code128A_Barcode & Chr(dummy - 32)
                End If
            Next
            
            ' Calculate checksum
            CheckSum = 103 ' Start code value
            For Counter = 1 To Len(SourceString)
                dummy = Asc(Mid(SourceString, Counter, 1))
                If dummy >= 0 And dummy <= 31 Then
                    dummy = dummy + 64
                ElseIf dummy >= 32 And dummy <= 95 Then
                    dummy = dummy - 32
                End If
                CheckSum = (CheckSum + Counter * dummy) Mod 103
            Next
            
            ' Append checksum character
            If CheckSum < 95 Then
                Code128A_Barcode = Code128A_Barcode & Chr(CheckSum + 32)
            Else
                Code128A_Barcode = Code128A_Barcode & Chr(CheckSum + 100)
            End If
            
            ' Append stop character
            Code128A_Barcode = Code128A_Barcode & Chr(106)
        End If
        
        Code128A = Code128A_Barcode
    End Function
    

    Regards
    ExcelDemy

  247. Hello Imtiaz,

    You’re welcome! I understand that working with VBA may seems complex if you’re not familiar with it. Here’s a step-by-step guide to use the VBA code:

    To open the VBA editor press Alt + F11.
    In the VBA editor, go to Insert >> select Module. This will create a new module where you can paste the VBA code.
    Copy and Paste the First Code in the module.

    Sub UpdateDateInAllSheets()
        Dim ws As Worksheet
        Dim dateValue As Variant
        
        ' Change "Sheet1" to the name of the sheet where you will enter the date
        dateValue = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
        
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Sheet1" Then
                ws.Range("A1").Value = dateValue
            End If
        Next ws
    End Sub
    

    Next, in the VBA editor, find Sheet1 (or the name of your sheet where you’ll enter the date) in the Project Explorer on the left side.
    You can also Double-click on Sheet1 to open its code window.
    Now, copy paste the 2nd code in the Sheet.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$A$1" Then
            Call UpdateDateInAllSheets
        End If
    End Sub
    

    Make sure to replace “$A$1” with the actual cell reference where you will be entering the date if it’s different.
    Now, go back to your Excel sheet. Enter a date in the specified cell (e.g., A1) on Sheet1 and check if the date gets updated in the corresponding cell on all other sheets.

    Regards
    ExcelDemy

  248. Hello Jim Green,

    You are most welcome. Your appreciation means a lot to us. We are grateful that you found the insights helpful. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  249. Hello David Silberberg,

    To apply the conditional formatting in column H based on the data of column F and it’s formatting you need to follow the steps given below:

    To highlight the entire column H.
    Go to the Home tab >> click Conditional Formatting >> select New Rule.
    Choose Use a formula to determine which cells to format.
    Enter the formula =ISNUMBER(SEARCH(“BofA”, $F2)).
    To Set Formatting:

    Click on Format.
    Set the font and fill colors to match those used in column F for “BofA”.
    Then, select OK to apply the formatting.
    Again, click OK to apply the rule.

    By following this step, any cell in column H will automatically reflect the same font and fill colors as column F when the corresponding cell in column F contains “BofA”.

    Download the Excel file:
    Copy Conditional Formatting.xlsx

    Regards
    ExcelDemy

  250. Hello Falak Niaz,

    Certainly! Your formula correctly extracts the data between the two dashes. Your feedback and suggestion will be helpful for other users.
    If you want you can simplify it by using MID function.

    =MID(B29, FIND(“-“, B29) + 1, FIND(“-“, B29, FIND(“-“, B29) + 1) – FIND(“-“, B29) – 1)

    Thanks for sharing your formula.

    Regards
    ExcelDemy

  251. Hello Yngwie,

    Before proceeding to advance level to develop your Excel skills you can explore the given links. Download the files from the articles to learn and practice the exercises.
    Excel Data for Practice Free Download
    Excel Practice Exercises PDF with Answers
    Explore these categories you will find out more data entry practice here: Data Entry Practice Test & Quiz
    Interview Questions with Excel Topics
    MCQ Questions on MS Excel

    To develop your skill from beginner to advanced level you can explore our Learn Excel page.

    Regards
    ExcelDemy

  252. Hello Errol,

    Great to hear that you found the video interesting. You will need to use VBA code and several steps to create a form in Excel for a library database that updates with data from worksheets, step by step procedures:
    Create the worksheets (Books and Users). Design the form with text boxes and buttons. Then run the VBA code.

    Step 1: Organize Data in Worksheets
    Books Worksheet: Include columns such as Book ID, Title, Author, Genre, etc.
    Users Worksheet: Include columns such as User ID, Name, Class, etc.

    Step 2: Create the Form
    Insert Form Controls.
    Use Insert > Form Controls to add text boxes and buttons for input.

    Step 3: Add VBA Code to Handle Data
    Open the VBA Editor (Alt + F11).
    Insert a New Module and copy paste the following code.

    Importing Books

    Sub ImportBooks()
        Dim wsBooks As Worksheet, wsForm As Worksheet
        Dim i As Long, lastRow As Long
        Set wsBooks = ThisWorkbook.Sheets("Books")
        Set wsForm = ThisWorkbook.Sheets("Form")
    
        lastRow = wsBooks.Cells(wsBooks.Rows.Count, 1).End(xlUp).Row
        For i = 2 To lastRow
            wsForm.Cells(i, 1).Value = wsBooks.Cells(i, 1).Value 'Book ID
            wsForm.Cells(i, 2).Value = wsBooks.Cells(i, 2).Value 'Title
            wsForm.Cells(i, 3).Value = wsBooks.Cells(i, 3).Value 'Author
            wsForm.Cells(i, 4).Value = wsBooks.Cells(i, 4).Value 'Genre
        Next i
    End Sub
    

    Importing Users

    Sub ImportUsers()
        Dim wsUsers As Worksheet, wsForm As Worksheet
        Dim i As Long, lastRow As Long
        Set wsUsers = ThisWorkbook.Sheets("Users")
        Set wsForm = ThisWorkbook.Sheets("Form")
    
        lastRow = wsUsers.Cells(wsUsers.Rows.Count, 1).End(xlUp).Row
        For i = 2 To lastRow
            wsForm.Cells(i, 5).Value = wsUsers.Cells(i, 1).Value 'User ID
            wsForm.Cells(i, 6).Value = wsUsers.Cells(i, 2).Value 'Name
            wsForm.Cells(i, 7).Value = wsUsers.Cells(i, 3).Value 'Class
        Next i
    End Sub
    

    Adding New Book

    Sub AddNewBook()
        Dim wsBooks As Worksheet
        Dim nextRow As Long
        Set wsBooks = ThisWorkbook.Sheets("Books")
        nextRow = wsBooks.Cells(wsBooks.Rows.Count, 1).End(xlUp).Row + 1
        
        wsBooks.Cells(nextRow, 1).Value = InputBox("Enter Book ID")
        wsBooks.Cells(nextRow, 2).Value = InputBox("Enter Book Title")
        wsBooks.Cells(nextRow, 3).Value = InputBox("Enter Author")
        wsBooks.Cells(nextRow, 4).Value = InputBox("Enter Genre")
    End Sub
    

    Adding New User

    Sub AddNewUser()
        Dim wsUsers As Worksheet
        Dim nextRow As Long
        Set wsUsers = ThisWorkbook.Sheets("Users")
        nextRow = wsUsers.Cells(wsUsers.Rows.Count, 1).End(xlUp).Row + 1
        
        wsUsers.Cells(nextRow, 1).Value = InputBox("Enter User ID")
        wsUsers.Cells(nextRow, 2).Value = InputBox("Enter User Name")
        wsUsers.Cells(nextRow, 3).Value = InputBox("Enter Class")
    End Sub
    

    This setup will allow you to efficiently manage your library database, importing data from worksheets and updating forms without manual entry for each record.

    Regards
    ExcelDemy

  253. Hello Jennifer,

    Thank you for your feedback. Our template auto-calculates the “DATE DUE” field based on your inputs, ensuring accuracy and efficiency. All input fields are editable, and no password is required.
    We just freeze the row-20 two maintain the template dashboard.
    To unfreeze it select row-20 >> from View >> select Unfreeze Panes.

    Please check our how to use this template section carefully.

    If you encounter specific issues, please share the details, including screenshots, so we can assist you better. We are committed to resolving any problems you may face.

    Regards
    ExcelDemy

  254. Hello Adnan,

    You are most welcome. We are glad to hear that you found this article extremely helpful. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  255. Hello Matador,

    You are most welcome. We are glad to hear that you found the lesson easy and helpful! Thank you for your kind words. If you have any more questions or need further assistance, feel free to ask! Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  256. Hello Bjangaiah,

    Thanks for your appreciation. We are glad to hear that it was helpful to you. We always try our best to provide a proper and detail explanation. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  257. Hello Kenneth,

    To auto sort the list alphabetize based on their names you can use the SORT function.
    Here, I used the SORT function to sort your data automatically each time you enter new player name.
    =SORT(‘Team Memebers’!A2:E100, 2, 1)

    To make a team used the TEXTJOIN and FILTER function.
    =TEXTJOIN(“, “, TRUE, FILTER($A$2:$A$21, $E$2:$E$21=H1))

    If you are comfortable with VBA then you also can use the code instead of SORT function.
    Insert the code in the Team Members sheet.

    Private Sub Workbook_Open()
        Sheets("Team Memebrs").Range("A2:E21").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo
    End Sub
    

    Download the Excel File:
    Auto Sort Team Members Name and Assign Team

    Regards
    ExcelDemy

  258. Hello Joe Bardswich,

    Your statement is not clear whether you referring to AutoFill or Auto Calculations. I am providing solution for both of them.

    If AutoFill is not working please check out this article: Excel Autofill is Not Working

    If Auto Calculations is not working check out this article: Excel Formulas Not Calculating Automatically

    If this solutions doesn’t work for you, please provide more details about what you were able to do before and what is not working now. You can attach dummy image of your dataset. This will help me give you the most accurate advice.

    Regards
    ExcelDemy

  259. Hello Djeeni,

    Thank you for your feedback. We appreciate it. Expanding the VBA example to handle ranges with multiple columns is a fantastic suggestion. Here’s a detailed approach:

    To handle multiple columns, iterate through each row within the specified range and concatenate cell values (e.g., combining first name and last name) to form a unique string for each row. Use this concatenated string as a key in a scripting dictionary to ensure each value is stored only once, maintaining uniqueness.
    After processing all rows, output the unique values from the dictionary to the specified location in the worksheet.

    Sub Uniquedata()
        Dim rng As Range
        Dim InputRng As Range, OutRng As Range
        Dim dt As Object
        Set dt = CreateObject("Scripting.Dictionary")
        Dim xTitleId As String
        xTitleId = "Select Range"
        
        ' Prompt user to select the input range
        Set InputRng = Application.Selection
        Set InputRng = Application.InputBox("Range:", xTitleId, InputRng.Address, Type:=8)
        
        ' Prompt user to select the output range
        Set OutRng = Application.InputBox("Output to (single cell):", xTitleId, Type:=8)
        
        ' Loop through each row in the selected range
        Dim row As Range
        Dim key As String
        For Each row In InputRng.Rows
            key = ""
            ' Concatenate cell values in each row
            For Each rng In row.Cells
                key = key & rng.Value
            Next rng
            ' Add concatenated value to the dictionary
            If key <> "" Then
                dt(key) = ""
            End If
        Next row
        
        ' Output unique concatenated values
        OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
    End Sub
    

    This approach will efficiently identify and store unique combinations of cell values across multiple columns. We will update the article with the detailed VBA code example to illustrate this process. Thank you for your valuable input.

    Regards
    ExcelDemy

  260. Hello Rechelyn Cañete,

    You are most welcome. To develop your Excel skills you can explore the given links. Download the files from the articles to learn and practice the exercises.
    Excel Data for Practice Free Download
    Excel Practice Exercises PDF with Answers
    Explore these categories you will find out more data entry practice here: Data Entry Practice Test & Quiz
    Interview Questions with Excel Topics
    MCQ Questions on MS Excel

    To develop your skill from beginner to advanced level you can explore our Learn Excel page.

    Regards
    ExcelDemy

  261. Hello Johnrey Cambaya,

    You are most welcome. To develop your Excel skills you can explore the given links. Download the files from the articles to learn and practice the exercises.
    Excel Data for Practice Free Download
    Excel Practice Exercises PDF with Answers
    Explore these categories you will find out more data entry practice here: Data Entry Practice Test & Quiz
    Interview Questions with Excel Topics
    MCQ Questions on MS Excel

    To develop your skill from beginner to advanced level you can explore our Learn Excel page.

    Regards
    ExcelDemy

  262. Hello Marissa Coetzee,

    Sorry to hear your problem. This issue may caused due to regional settings or data formatting differences. Make sure your regional settings in Excel match those of your teammate.
    Also, check for any leading/trailing spaces or non-numeric characters in your data. Cleaning your data using the Text to Columns or Find & Replace features might help.
    Or you can use the VALUE function to convert the values in number then use the Get Data option.

    Clean your data then use proper data format before using the Get Data option.
    Regards
    ExcelDemy

  263. Hello David McKenna,

    To adjust the column width you need select right icon. As there appears multiple icons based on position when you select a whole column. Perhaps you selected the copy icon instead of width icon.

    To adjust width you must place the cursor in the column header then select the plus icon with arrow. Please check the image to see the icon.

    To copy the column you need to select the icon marked in the given image.

    Regards
    ExcelDemy

  264. Hello Enrique Arizmendi,

    You are most welcome. Your appreciation means a lot to us. We are glad to hear that our article helped you to explore the possibilities of Excel. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  265. Hello Ebsa,

    You are most welcome. To create a forest plot for categories and sub categories follow the steps given below.

    Create your dataset with the following columns.

    Category: The main category.
    Subcategory: The subcategories under each main category.
    Estimate: The effect estimate or mean value for each subcategory.
    Lower CI: The lower bound of the confidence interval for each estimate.
    Upper CI: The upper bound of the confidence interval for each estimate.

    First, insert a Scatter Plot:
    1. Select the columns for the subcategories and the estimates.
    2. Go to Insert > Chart > Scatter > Scatter with Straight Lines and Markers.

    Next, add Error Bars:
    1. Click on the chart to activate the Chart Tools.
    2. Go to Chart Tools > Layout > Error Bars > More Error Bar Options.
    3. Select Both for direction and specify Custom for the error amount.
    4. Use your Lower CI and Upper CI values for the custom error amount. Enter these values manually.

    Next, Customize the Chart:
    1. Add titles, labels, and adjust the axes as needed.
    2. To add a vertical line at the point of no effect (usually zero or one), draw a line using the Shapes tool and place it at the appropriate point on the X-axis.

    Next, Format the Chart:
    1. Adjust the colors, line styles, and marker shapes to differentiate between categories and subcategories.
    2. Add data labels if necessary for clarity.

    Finally, Group Categories and Subcategories:
    1. If you have multiple main categories, use different colors or shapes to represent each main category.
    2. Add a legend to help differentiate between the categories.

    By following these steps, you can create a clear and informative forest plot in Excel that displays categories with their respective subcategories, including confidence intervals for each estimate.

    Regards
    ExcelDemy

  266. Hello Kristal,

    Thanks for sharing this solution! It’s a great solution for highlighting overdue dates and those due within 30 days.
    To avoid formatting blank cells, you need to use a more specific condition.

    You can apply conditional formatting with the formula: =AND(A1<>“”, A1

    This formula will check if the cell is not blank (A1<>“”), and then it will verify if the date is less than 30 days from today (A1. This ensures that only non-blank cells with dates within the next 30 days are formatted.

    If you need further assistance please let us know. Keep learning Excel with ExcelDemy.

    Regards
    ExcelDemy

  267. Hello Rich Courtney,

    The green circle with an arrow at the tip of your pointer is related to the Caps Lock feature. This is a new feature in Safari on macOS to indicate that Caps Lock is enabled.

    To turn off or disable this feature, you can try the following steps:

    System Preferences:

    1. Open System Preferences.
    2. Go to Keyboard.
    3. Under the Keyboard tab, look for any settings related to Caps Lock or Keyboard shortcuts and disable any relevant options.

    Safari Preferences:

    1. Open Safari.
    2. Go to Safari > Preferences (or press Cmd + ,).
    3. Look for any settings that might be related to this feature under the Advanced or Accessibility tabs and disable them if found.

    Accessibility Settings:

    1. Open System Preferences.
    2. Go to Accessibility.
    3. Look for any settings related to keyboard or pointer and disable any features related to Caps Lock notifications.

    Sometimes reinstalling the application can help resolve unexpected behavior.

    If none of these steps work, consider reaching out to Apple Support for more detailed assistance. They might provide a specific way to disable this new feature or offer an alternative solution.

    Regards
    ExcelDemy

  268. Hello Alvin,

    This VBA macro sends an email if the date in a selected range matches Today’s date. It prompts the user to select a range of cells and checks each cell in the range. If a cell’s value matches the current date, it asks the user for the email subject, sender, recipient, CC, BCC, and message body. It then uses Outlook to send the email with the provided details.
    In your date range you must include the date which will match Today’s date.
    To understand the logic I added a debugging option here:

    Sub SendEmail01()
        Dim Range_Select As Range
        Dim Date_Range As Range
        Dim Cell_Address As String
        Dim Subject, Email_From, Email_To, Cc, Bcc, Email_Text As String
        Dim Email_Obj, Single_Mail As Object
        Dim DateMatched As Boolean
        DateMatched = False
        
        On Error Resume Next
        
        ' Get the selected range of cells
        Cell_Address = ActiveWindow.RangeSelection.Address
        Set Range_Select = Application.InputBox("Select a range:", "Message Box", Cell_Address, , , , , 8)
        If Range_Select Is Nothing Then Exit Sub
        
        ' Loop through each cell in the selected range
        For Each Date_Range In Range_Select
            ' Check if the cell's value matches today's date
            If Date_Range.Value = Date Then
                DateMatched = True
                Exit For
            End If
        Next
        
        ' If no dates matched today's date, exit the sub
        If Not DateMatched Then
            MsgBox "No dates in the selected range match today's date."
            Exit Sub
        End If
        
        ' Prompt the user for email details
        Subject = Application.InputBox("Subject: ", "Message", , , , , , 2)
        Email_From = Application.InputBox("Send from: ", "Message Box", , , , , , 2)
        Email_To = Application.InputBox("Send to: ", "Message Box", , , , , , 2)
        If Email_To = "" Then Exit Sub
        Cc = Application.InputBox("CC: ", "Message Box", , , , , , 2)
        Bcc = Application.InputBox("BCC: ", "Message Box", , , , , , 2)
        Email_Text = Application.InputBox("Message Body: ", "Message Box", , , , , , 2)
        
        ' Create and send the email using Outlook
        Set Email_Obj = CreateObject("Outlook.Application")
        Set Single_Mail = Email_Obj.CreateItem(0)
        With Single_Mail
            .Subject = Subject
            .To = Email_To
            .Cc = Cc
            .Bcc = Bcc
            .Body = Email_Text
            .Send
        End With
    End Sub
    

    If you need further customization or have a specific dataset, please provide more details about how you want the VBA code to work.

    Regards
    ExcelDemy

  269. Hello Rav,

    You are most welcome. To add the headers along with the searched values use the following updated VBA code:

    Dim field As String
    Dim headersAdded As Boolean
    
    Private Sub ComboBox1_Change()
        Dim col_no As Integer
        Dim col_headers
        col_headers = Array("B", "C", "D")
    
        For col_no = 2 To 4
            If ActiveSheet.Cells(4, col_no).Value = Me.ComboBox1.Value Then
                field = col_headers(col_no - 2)
            End If
        Next
    
        Me.ListBox1.Clear
        Me.TextBox1.Value = ""
        Me.TextBox1.SetFocus
        headersAdded = False
    End Sub
    
    Private Sub ListBox1_Click()
    
    End Sub
    
    Private Sub TextBox1_Change()
        On Error Resume Next
    
        If Me.TextBox1.Text = "" Then
            Me.ListBox1.Clear
            headersAdded = False
            Exit Sub
        End If
    
        Me.ListBox1.Clear
        ' Add headers to ListBox
        If Not headersAdded Then
            With Me.ListBox1
                .AddItem ""
                .List(.ListCount - 1, 0) = ActiveSheet.Cells(4, 2).Value
                .List(.ListCount - 1, 1) = ActiveSheet.Cells(4, 3).Value
                .List(.ListCount - 1, 2) = ActiveSheet.Cells(4, 4).Value
            End With
            headersAdded = True
        End If
    
        Dim row_no As Integer
        Dim last_row_no As Integer
        last_row_no = ActiveSheet.Range("B100").End(xlUp).Row
        For row_no = 5 To last_row_no
            letter = Len(Me.TextBox1.Text)
            If UCase(Left(ActiveSheet.Cells(row_no, field).Value, letter)) = UCase(Me.TextBox1.Text) Then
                With Me.ListBox1
                    .AddItem ""
                    .List(.ListCount - 1, 0) = ActiveSheet.Cells(row_no, "B").Value
                    .List(.ListCount - 1, 1) = ActiveSheet.Cells(row_no, "C").Value
                    .List(.ListCount - 1, 2) = ActiveSheet.Cells(row_no, "D").Value
                End With
            End If
        Next
    End Sub
    
    Private Sub UserForm_Initialize()
        Dim col_no As Integer
        For col_no = 2 To 4
            Me.ComboBox1.AddItem ActiveSheet.Cells(4, col_no).Value
        Next
    
        With Me.ListBox1
            .ColumnCount = 3
            .ColumnWidths = "100;100;100" ' Adjust the widths as needed
        End With
        headersAdded = False
    End Sub
    

    Output:

    Regards
    ExcelDemy

  270. Hello Undent,

    Sorry to hear your problem. But our Method-1 is working perfectly. Can you check your IF condition based on your case or dataset.
    Here, I’m attaching a image where IF function returns the values based on the conditions.

    If you want you can share your case here.

    Regards
    ExcelDemy

  271. Hello Gordan,

    It’s a common issue with Excel pivot tables and slicers. You can follow the steps given below to maintain the formatting of your pivot table when using slicers:

    Use Cell Styles:
    Instead of relying on pivot table styles, try applying cell styles to the pivot table.
    Select the cells you want to format, go to the “Home” tab, and choose “Cell Styles“. This approach can help preserve formatting when slicers are used.

    Use a VBA Code:

    If you’re comfortable with VBA, you can use a VBA script to reapply formatting whenever the pivot table is updated.
    Right-click the sheet tab with your pivot table, choose “View Code,” and paste this code into the worksheet module.

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
        Dim pt As PivotTable
        Set pt = Target
        
        ' Apply your formatting here
        With pt.TableRange2
            .Font.Name = "Arial"
            .Font.Size = 10
            ' Add other formatting options as needed
        End With
    End Sub
    

    Create a Custom Pivot Table Style:

    You can create a custom pivot table style that includes all your desired formatting. To do this:
    Click on your pivot table.
    Go to “Design” > “PivotTable Styles” > “New PivotTable Style.”
    Define your style, including fonts, colors, borders, etc.
    Apply this custom style to your pivot table.

    By trying these methods, you should be able to maintain your pivot table’s formatting even when using slicers. Let me know if you need more specific guidance on any of these topic.

    Regards
    ExcelDemy

  272. Hello Veekay,

    You are most welcome. We try to summarize all possible solutions so that you can use any of the solution based on your dataset type. Keep solving Excel problems with ExcelDemy.

    Regards
    ExcelDemy

  273. Hello Morgan Trevino,

    Checked method-2 to confirm the issue. Method-2 is working perfectly:

    To ensure that only overdue dates are highlighted using method 2, please check the conditional formatting formula used. The formula should compare the dates with today’s date.

    You also can use the formula: = A1 (replace A1 with the appropriate cell reference for your dates). This formula will highlight only the dates that are earlier than today’s date.

    If you need further guidance, please share the exact formula you used so I can help you correct it.

    Regards
    ExcelDemy

  274. Hello Larry,

    Based on your previous comment.

    To add the date to column K of the matched row of TCN of column B, you will need to use the Date function. It will provide the current date.

    Here, I updated the VBA code to add the date. Make sure to place this code in the appropriate worksheet module where you are scanning the barcodes.

    Private Sub Worksheet_Change(ByVal target As Range)
        If Not Intersect(target, Columns("M")) Is Nothing Then
            Z = Intersect(target, Columns("M")).Value
            If IsNumeric(Z) Then
                x = Application.Evaluate("MATCH(" & Z & ",B:B,0)")
            Else
                x = Application.Evaluate("MATCH(" & Chr(34) & Z & Chr(34) & ",B:B,0)")
            End If
            If Not IsError(x) Then
                Application.Goto Cells(x, 15)
                Cells(x, 11).Value = Date & " " & Time ' Adds the current date to column K
            End If
        End If
    End Sub
    

    Regards
    ExcelDemy

  275. Hello Guy,

    You cannot open the same Excel sheet in safe mode without closing it first. Safe mode is designed to start Excel without add-ins or other customizations, which requires restarting the application. If you are experiencing issues with a specific sheet, try saving your work, close Excel, then reopen it in safe mode using the Ctrl key method:

    1. Press and hold the Ctrl key.
    2. While holding Ctrl, click on the Excel icon to open it.
    3. A prompt will appear asking if you want to start Excel in safe mode. Click Yes.

    This method allows you to open a new instance of Excel in safe mode while keeping your current sheet open.

    Regards
    ExcelDemy

  276. Hello Larry,

    To add the date to column K of the matched row of TCN of column B, you will need to use the Date function. It will provide the current date.

    Here, I updated the VBA code to add the date. Make sure to place this code in the appropriate worksheet module where you are scanning the barcodes.

    Private Sub Worksheet_Change(ByVal target As Range)
        If Not Intersect(target, Columns("M")) Is Nothing Then
            Z = Intersect(target, Columns("M")).Value
            If IsNumeric(Z) Then
                x = Application.Evaluate("MATCH(" & Z & ",B:B,0)")
            Else
                x = Application.Evaluate("MATCH(" & Chr(34) & Z & Chr(34) & ",B:B,0)")
            End If
            If Not IsError(x) Then
                Application.Goto Cells(x, 15)
                Cells(x, 11).Value = Date & " " & Time ' Adds the current date to column K
            End If
        End If
    End Sub
    

    Regards
    ExcelDemy

  277. Hello Srivatsan Guru,

    You are most welcome. It’s great to hear that it was helpful to you. Keep learning Excel with ExcelDemy.

    Regards
    ExcelDemy

  278. Hello AMG,

    You are most welcome. It’s great to hear that Method-7 was helpful to you and it worked perfectly. Keep learning Excel with ExcelDemy.

    Regards
    ExcelDemy

  279. Hello Jerry,

    Method-3 is working perfectly. To get the page number in a proper way you need to insert the page number first. Follow the given steps to insert the page number in a selected cell.
    First select a cell >> go to Developer tab >> from Macros >> select Page_Number_Selected_Cell().
    It will insert the page number.
    Remember to select the cell sequentially to add page numbers.

    Output:

    Regards
    ExcelDemy

  280. Hello Michel Claes,

    You are most welcome. Thank you for your feedback! I’m glad you found the article helpful.
    Static oldval(6 To 10) As Variant: Declares a static array oldval to store previous values of cells F6 to F10, so the values persist between subroutine calls.
    The variable “oldval” is initialized at the first run of the subroutine to store the initial cell value before any changes occur. It’s updated only when a change is not detected to retain the previous value for comparison. This way, it can accurately identify when a change happens in the cell value.
    If you have any further questions or need more clarification, feel free to ask!

    Regards
    ExcelDemy

  281. Hello Rolands,

    You are most welcome. Thank you for your feedback! I’m glad the solution worked for you. Replacing Target.Address with Target.Column = 5 is a great adjustment for your setup with multiple drop-down cells in column E. It’s wonderful to hear that it continues to work even for newly created and copy-pasted rows.

    We used Target.Address initially to target specific cells, making it easy to apply the method to designated areas. This approach provides precision and control, especially useful for varying drop-down list configurations.

    Regards
    ExcelDemy

  282. Hello Melody,

    Thank you for your interest! Unfortunately, we are unable to provide a PDF version of the content from the Learn Excel page on ExcelDemy. But you can always access and study the material directly on our website at any time. If you have any specific topics or questions, feel free to let us know, and we’ll be happy to help!

    Regards
    ExcelDemy

  283. Hello Hari Lalam,

    You are most welcome. It is great to hear that the Google Sheets method accomplished your task. Keep learning Excel with ExcelDemy.

    Regards
    ExcelDemy

  284. Hello Johnson Helen,

    You are most welcome. We are glad to hear that it was really helpful to you. In our Practice Test & Quiz category you will find out more data entry practice here: Data Entry Practice Test & Quiz

    Keep learning Excel with ExcelDemy.

    Regards
    ExcelDemy

  285. Hello Raif,

    You are most welcome. We are glad to hear that our example worked for you. You can explore more article related to VBA. Keep learning Excel with ExcelDemy.

    Regards
    ExcelDemy

  286. Hello Samwel Gurt,

    You are most welcome. We are glad to hear that our article is excellent and satisfying to you. You can explore more article related to these topic. Keep learning Excel with ExcelDemy.

    Regards
    ExcelDemy

  287. Hello Samwel Gurt,

    Thank you so much. We are glad to hear that you loved our article. You can explore more article related to these topic. Keep learning Excel with ExcelDemy.

    Regards
    ExcelDemy

  288. Hello Me,

    You are most welcome. Your appreciation means a lot to us. You can explore more article related to these topic. Keep learning Excel with ExcelDemy.

    Regards
    ExcelDemy

  289. Hello Tony Jin,

    You are most welcome. Your appreciation means a lot to us. You can explore more article related to these topic. Keep learning Excel with ExcelDemy.

    Regards
    ExcelDemy

  290. Hello Matthew Richard Raison,

    The problem with the VBA code not working for a different roster template could be due to differences in the sheet structure. While using different sheet structure update the VBA code according to you new sheet:

    1. Ensure the cells for month and year are correctly referenced (W2 and AC2).
    2. Adjust the CopyToSheet subroutine to match the layout and structure of your specific roster sheet.
    3. Verify that the new sheet created is an exact replica by manually inspecting the VBA code to ensure all ranges and cell references align with your roster’s layout.
    Make these adjustments, and the code should work as intended.

    Regards
    ExcelDemy

  291. Hello Claudia Sgiarovello,

    On our end the VBA code is working perfectly. Rechecked the code to confirm it.

    Make sure you replace the curly quotation marks with straight quotation marks to avoid syntax errors in VBA. If the problem persists, double-check the name of the range “CheckListOutput” to ensure it matches exactly with what is in your Excel workbook.

    1. Ensure all quotes are straight quotes ().
    2. Check that “CheckListOutput” matches the actual named range in your Excel workbook.
    3. Verify that “checkList” matches the name of your ActiveX listbox control.

    If these steps don’t resolve the issue, consider providing more details about any specific error messages or behaviors you’re encountering.

    Regards
    ExcelDemy

  292. Hello Muskan Gadodia,

    To Load/Export the table into an Excel worksheet follow these steps:
    Select the sheet name (Departmental Sheet) and Right-Click on the sheet. Then, select Load To.

    Selecting Sheet

    Import Data dialog box will pop up
    From there select Table.
    Then select the location where you want to put the data. Here, selected A1 cell.

    Finally, data is exported to a table.

    You will get the steps in our updated article also.

    Regards
    ExcelDemy

  293. Hello Anonymous,

    Thank you for your feedback! I’ve updated the document to include detailed explanations for each example, showing exactly how each formula works step-by-step. This should make it easier for you to understand how to adapt the formulas for your own spreadsheets.

    I’m glad the initial examples were helpful, and I hope the added explanations will provide the deeper understanding you’re looking for. If you have any more questions or need further clarification, please feel free to ask. Happy spreadsheeting!

    Regards
    ExcelDemy

  294. Hello Idrissa A.Kamara,

    You’re most welcome! I’m glad to hear that the materials and tutorial have been helpful in enhancing your Excel skills and intelligence. Your dedication to learning and improvement is truly inspiring. God bless you as well, and if you ever need further assistance or have more questions, feel free to reach out. Keep up the great work!
    Keep leaning Excel with ExcelDemy.

    Regards
    ExcelDemy

  295. Hello Bob,

    Thank you for bringing this to our attention. Our template is designed to handle various loan amounts and interest rates. I’ve checked the template again by changing loan amount, interest , tenure etc and it’s working perfectly. I will request you to check, How to Use This Template part of the article.

    Templates’ intial image:


    Different Loan Amount and Interest Rate:


    Another changes:

    It sounds like there might be a formatting or input error causing the #VALUE! error. Please ensure that:

    Loan Amount and Interest Rate Formats: The values are entered as plain numbers without any special characters or spaces. For example, enter 200000 for $100,000 and 8 for an interest rate of 8%.
    Correct Cells: The values are being entered in the correct cells specified for loan amount and interest rate.
    If you’ve checked these and are still encountering issues, please feel free to share a screenshot or more details about the error. We’ll be happy to assist further.

    Thank you for your patience and understanding.

    Regards
    ExcelDemy

  296. Hello Faye,

    The formula provided calculates the straight-line (great-circle) distance between two points, not accounting for roads, highways, or specific travel routes. This is why you notice a discrepancy when comparing it to Google Maps or Bing, which calculate driving or walking distances along actual travel paths.
    The straight-line distance is the shortest path over the earth’s surface, which does not reflect the true travel distance that might involve various roads and pathways.

    Thanks for commenting and asking questions! It’s great that you’re diving into the details and eager to learn. No worries, it’s not a dumb question at all! Learning these differences is part of the process. Keep up the good work!

    Regards
    ExcelDemy

  297. Hello Andrew,

    There is a way to set up to be used repeatedly and routinely but for that you will need to install Adobe Acrobat SDK this is necessary for controlling Acrobat via VBA. Then you will need to use VBA code and Task Schedular.
    Copy the VBA code to find the PDF file from a directory.

    Sub ImportPDFsFromDirectory()
        Dim AcroApp As Object
        Dim AVDoc As Object
        Dim PDDoc As Object
        Dim jso As Object
        Dim field As Object
        Dim i As Integer
        Dim ws As Worksheet
        Dim filePath As String
        Dim folderPath As String
        Dim fileName As String
        
        ' Set the folder path
        folderPath = "C:\path\to\your\pdf\directory\"
        
        ' Set the worksheet
        Set ws = ThisWorkbook.Sheets("Sheet1")
        i = 1
        
        ' Create Adobe Acrobat objects
        Set AcroApp = CreateObject("AcroExch.App")
        
        ' Loop through all PDF files in the directory
        fileName = Dir(folderPath & "*.pdf")
        Do While fileName <> ""
            filePath = folderPath & fileName
            Set AVDoc = CreateObject("AcroExch.AVDoc")
            
            If AVDoc.Open(filePath, "") Then
                Set PDDoc = AVDoc.GetPDDoc
                Set jso = PDDoc.GetJSObject
                
                ' Extract and write PDF data to Excel
                For Each field In jso.GetFieldNames
                    ws.Cells(i, 1).Value = field
                    ws.Cells(i, 2).Value = jso.GetField(field).Value
                    i = i + 1
                Next field
                
                AVDoc.Close True
            End If
            
            fileName = Dir
        Loop
        
        ' Close Acrobat application
        AcroApp.Exit
    End Sub
    

    Now, set up the Task Scheduler:
    1. Open Task Scheduler
    Search for “Task Scheduler” in the Windows Start menu and open it.

    2. Create a Basic Task

    o Click on “Create Basic Task…” in the Actions pane.
    o Name your task (e.g., “Automate PDF to Excel”) and provide a description.
    o Click “Next”.

    3. Set Trigger
    o Choose when you want the task to start (e.g., Daily, Weekly).
    o Click “Next” and set the start date and time.
    o Click “Next”.

    4. Set Action
    o Choose “Start a program” and click “Next”.
    o In the “Program/script” field, enter the path to the Excel executable (e.g., C:\Program Files\Microsoft Office\root\Office365\EXCEL.EXE).
    o In the “Add arguments (optional)” field, enter the path to your Excel workbook (e.g., “C:\path\to\your\workbook.xlsm”).
    o Click “Next”.

    5. Finish
    o Review your settings and click “Finish”.

    6. Configure Task
    o Locate your new task in the Task Scheduler Library.
    o Right-click it and select “Properties”.
    o Go to the “Actions” tab and click “Edit”.
    o In the “Add arguments (optional)” field, add the following to run the specific macro:
    /e /mImportPDFData
    o Click “OK” and then “OK” again to save your changes.

    This setup will automatically open the Excel file and run the specified macro at the scheduled times.

    Regards
    ExcelDemy

  298. Hello Sid,

    You can use the Application.OnKey method to assign a macro to a specific key. To handle the “Insert” key, you need to use the correct key code.To use the Insert key as an event along with Application.OnKey do the followings:

    Copy paste the following code in the Moduel:

    Sub InsertRowAndAutofill()
        Dim Target As Range
        Set Target = Application.ActiveCell
        Target.Offset(2).EntireRow.Insert
        Target.EntireRow.Copy Target.Offset(2).EntireRow
        On Error Resume Next
        Target.Offset(2).EntireRow.SpecialCells(xlConstants).ClearContents
    End Sub

    Then copy paste the following code in ThisWorkbook:
    The Workbook_Open event, will set the Application.OnKey to assign the “Insert” key to this macro and the Workbook_BeforeClose event, clear the key assignment when closing the workbook.

    Private Sub Workbook_Open()
        Application.OnKey "{INSERT}", "InsertRowAndAutofill"
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Application.OnKey "{INSERT}"
    End Sub
    

    Regards
    ExcelDemy

  299. Hello Paolo,

    You are most welcome. We are glad to hear that you found our solution easy and efficient. Keep finding Excel solutions with ExcelDemy.

    Regards
    ExcelDemy

  300. Hello Nate,

    You are most welcome. We are glad that our solution worked perfectly for you. Keep finding Excel solutions with ExcelDemy.

    Regards
    ExcelDemy

  301. Hello Bhabani,

    Please fill out this form properly to get the FREE Advanced Excel Exercises with Solutions

    I received the exercise sheet via email after filling out the form.

    Regards
    ExcelDemy

  302. Hello Karin Williams,

    You are most welcome. Thanks for your appreciation, it means a lot to us. We are glad to hear that you found our article most comprehensive. Keep learning Excel with ExcelDemy.

    Regards
    ExcelDemy

  303. Hello Keith Miller,

    I understand your frustration with transitioning from MSQuery to Power Query, especially after being familiar with MSQuery for so long. The learning curve can be a bit daunting, but the good news is that Power Query is incredibly powerful and can handle the task you described quite efficiently.

    To join two tables into one data set without resorting to Access or using numerous VLOOKUP formulas, Power Query is indeed your best bet.
    Please follow this article to Combine Two Tables Using Power Query in Excel

    To create relationship between two tables you can follow the given steps too:
    Let’s say, we have two tables describing different products ordered by some customers from separate addresses and their respective prices.

    I have created the first column named Customer Information with headings: Customer ID, Name, and Address.

    Create Relationship Between Tables in Excel

    Another table named Order Information has headings like: Name, Product, and Price.

    Create Relationship Between Tables in Excel

    It is noticeable that there must be a common column to create a relationship between the tables.

    Here, I will show the method of creating relationships using Pivot Table. In order to demonstrate this method, proceed with the following steps.

    Steps:

    • First of all, select the data range of the first table (i. e. Customer Information)> go to the Insert tab> click Table.

    Create Relationship Between Tables in Excel

    • Then, click OK on the Create Table dialogue box.

    Create Relationship Between Tables in Excel

    • Now, your table will be created.

    Create Relationship Between Tables in Excel

    • Here, follow the same procedure for the other table (i.e. Order Information).

    Create Relationship Between Tables in Excel

    • Now, click on the Table > go to the Table Design tab> assign a name for the table.

    I have named the first table Customer and the second table Order.

    Create Relationship Between Tables in Excel

    • After that, click on the Customer table> go to the Insert tab> and click Pivot Table.

    • Here, PivotTable from table or range dialogue box will show up. Mark  Add this data to the Data Model box and click OK.

    • Now, the Pivot Table Fields will show up.

    • Now, select the data type from both the tables (i.e. Address from the first table and Price from the second table) and click CREATE.

    • After that, Create Relationship dialogue box will show up. Here, assign the table names and the common column (i.e. Name) and click OK.

    • Finally, Excel will create a relationship between the two tables.

    Create Relationship Between Tables in Excel

    So, these are the steps you can follow to create a relationship between tables using the Pivot Table option.

    Read More: How to Create Data Model Relationships in Excel

    Download the Excel File: Creating Relationship Between Tables.xlsx

    Regards
    ExcelDemy

  304. Hello SH,

    The VBA code is working perfectly. Similarities of two columns are highlighted in Red color.
    Here, I am uploading a video of VBA code:

    Video of VBA code Highlighting Similarities

    Kindly use the following code again:

    Sub highlight_similar_text()
        
        On Error Resume Next
        If ActiveWindow.RangeSelection.Count > 1 Then
          Text = ActiveWindow.RangeSelection.AddressLocal
        Else
          Text = ActiveSheet.UsedRange.AddressLocal
        End If
    One:
        Set Range1 = Application.InputBox("First Range:", "Exceldemy", Text, , , , , 8)
        If Range1 Is Nothing Then Exit Sub
        If Range1.Columns.Count > 1 Or Range1.Areas.Count > 1 Then
            MsgBox "You select multiple ranges or columns ", vbInformation, "Exceldemy"
            GoTo One
        End If
    Two:
        Set Range2 = Application.InputBox("Second Range:", "Exceldemy", "", , , , , 8)
        If Range2 Is Nothing Then Exit Sub
        If Range2.Columns.Count > 1 Or Range2.Areas.Count > 1 Then
            MsgBox "You select multiple ranges or columns ", vbInformation, "Exceldemy"
            GoTo Two
        End If
        If Range1.CountLarge <> Range2.CountLarge Then
           MsgBox "Ranges should have the same numbers of cells ", vbInformation, "Exceldemy"
           GoTo Two
        End If
        Differ = (MsgBox("Select Yes to highlight similarities, Select No to highlight differences ", vbYesNo + vbQuestion, "Exceldemy") = vbNo)
        Application.ScreenUpdating = False
        Range2.Font.ColorIndex = xlAutomatic
        For I = 1 To Range1.Count
            Set FirstCell = Range1.Cells(I)
            Set SecondCell = Range2.Cells(I)
            If FirstCell.Value2 = SecondCell.Value2 Then
                If Not Differ Then SecondCell.Font.Color = vbRed
            Else
                xLen = Len(FirstCell.Value2)
                For J = 1 To xLen
                    If Not FirstCell.Characters(J, 1).Text = SecondCell.Characters(J, 1).Text Then Exit For
                Next J
                If Not Differ Then
                    If J <= Len(SecondCell.Value2) And J > 1 Then
                        SecondCell.Characters(1, J - 1).Font.Color = vbRed
                    End If
                Else
                    If J <= Len(SecondCell.Value2) Then
                        SecondCell.Characters(J, Len(SecondCell.Value2) - J + 1).Font.Color = vbRed
                    End If
                End If
            End If
        Next
        Application.ScreenUpdating = True
    End Sub

    Please click on Yes to highlight the similarities.

    Regards
    ExcelDemy

  305. Hello Daz C,

    Making AllTaskList sheet a table is not an issue. May be Named Range is not working properly that’s why VLOOKUP is not getting the lookup values.

    Please, check the tasklist from Named Manager.
    Go to Formulas >> from Defined Names >> select Name Manager.
    Check the Tasklist contains =’AllTaskList (2)’!$B:$G

    N.B: You can change your list based on your sheet.

    Here, I made the AllTaskList a table to check either it’s a problem or not.

    Then, checked the Task Detail sheet to see VLLOKKUP is working or not.

    Here VLOOKUP is working perfectly.

    I am uploading the updated Excel file, please download it from here:
    Creating a Task Tracker Using Table.xlsx

    Reagards
    ExcelDemy

  306. Hello Leo Mulhern,

    The Excel file is working fine in my end. I updated the stocks name and it is working.

    Track Stock Prices:

    Updated Stock Prices:

    Again, I uploaded the Excel file for you:
    Track Stocks in Excel (Updated).xlsx

    If new file keeps shutting down, try these steps to fix the issue:

    Update Excel: Make sure Excel is up to date.
    Disable Add-Ins: Open Excel in Safe Mode (hold Ctrl while opening Excel), then disable add-ins from File -> Options -> Add-Ins.
    Repair Office: Go to Control Panel -> Programs -> Programs and Features, find Microsoft Office, right-click, select Change, and choose Quick Repair.

    Regards
    ExcelDemy

  307. Hello Flipmode,

    Thank you for your feedback and for highlighting the importance of accurate formulas. However, the provided formula in the article is giving correct results for the USD to EUR conversion as shown in the example table. The VLOOKUP formula used is correctly referencing the exchange rate and multiplying it with the USD value. Here, we are converting currencies from USD to any other currency.
    USD to EURO:

    USD to ARS:

    N.B: In our article we used exchange rate data of 9/27/2022

    Thank you for your feedback. If your specific examples work for reversing the lookup, then it’s a wonderful solution. We appreciate your suggestions and hope other users find your insights useful. Thanks for your contribution!

    Regards
    ExcelDemy

  308. Hello Julie Binks,

    Based on your table you can match data to return sales. Use the following formula to get data based on partial name.

    =VLOOKUP(“*”&E5&”*”,B4:C11,2,FALSE)

    Here is the sample data with output:

    Regards
    ExcelDemy

  309. Hello Ahmed,

    You are most welcome. Thanks for your appreciation it means a lot to us. Keep learning Excel with us.

    Regards
    ExcelDemy

  310. Hello JM Kim,

    Here, created a single bar chart horizontal with two values Active an Inactive or 1 and 0 which is displayed with different color and x axis it time value.

    Download the Excel File: Single Horizontal Bar Chart Showing Active Inactive Status.xlsx

    Regards
    ExcelDemy

  311. Hello Kumar Chavan,

    You need to create two sheets to present your expenses.

    Firstly, create Income and Expenditure Account sheet to record all incomes and expenditures for the year, including the deferred income adjustment for repairs.

    Then, create Balance Sheet to present the assets, liabilities, and equity as of the year-end date.

    Here is your Example Format: Template for Income and Expenditure Account and Balance Sheet.xlsx

    Regards
    ExcelDemy

  312. Hello JZ,

    NYSE ticker SNOW for Snowflake is included in Excel database.
    To get the data follow the steps below.
    Insert SNOW in any cell then click on Stocks from Data tab.

    You will get all the data by selecting the fields.

    Regards
    ExcelDemy

  313. Hello Imtiaz,

    To do so you need to use two VBA code in your Excel workbook. One in the Module to update date and another in the first sheet where you will update the date. Make sure to replace “Sheet1” with the actual name of the sheet where you will enter the date, and “A1” with the cell reference where the date is located.
    Copy and paste the following VBA code into the new module:

    Sub UpdateDateInAllSheets()
        Dim ws As Worksheet
        Dim dateValue As Variant
        
        ' Change "Sheet1" to the name of the sheet where you will enter the date
        dateValue = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
        
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Sheet1" Then
                ws.Range("A1").Value = dateValue
            End If
        Next ws
    End Sub
    

    To automatically update the date whenever the date is changed, you need to use the Worksheet_Change event.
    In the VBA Editor, double-click the sheet where you will enter the date (e.g., Sheet1) in the Project Explorer window.
    Then, paste the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$A$1" Then
            Call UpdateDateInAllSheets
        End If
    End Sub
    

    Again, replace “$A$1” with the actual cell reference where the date is located.

    Now, whenever you change the date in the specified cell on your primary sheet (e.g., Sheet1), the date will automatically be updated in the corresponding cell on all other sheets.
    VBA code will assume that the date is located in the same cell on each sheet.

    Regards
    ExcelDemy

  314. Hello Ramesh Satyanarayanamurthy,

    To search multiple text in single cell by using the IF,ISNUMBER and SEARCH function, use the following formula:
    =IF(ISNUMBER(SEARCH(“Service”, B5)), “Server Status”, IF(ISNUMBER(SEARCH(“Application”, B5)), “Application Status”, IF(ISNUMBER(SEARCH(“Connect”, B5)), “Connectivity”, IF(ISNUMBER(SEARCH(“CPU Utilization”, B5)), “CPU Utilization”, “”))))

    Output:

    Regards
    ExcelDemy

  315. Hello Jacob,

    Thanks a lot for sharing this solution. Defining the validation string using a ListObject is indeed a smart approach. By turning the data into a table, we can avoid manually editing column addresses and ensure the code remains functional even if the table moves.
    I appreciate the suggestion to make both sets of data into tables for easier referencing. This will definitely help in maintaining the code’s robustness and flexibility.

    Regards
    ExcelDemy

  316. Hello Patrick Raimond,

    To use the iterative formula you need to do the following changes in Excel options

    Enable Iterative Calculations:

    First, go to File > Options > Formulas.
    Then, select Enable iterative calculation.

    Today’s Output:

    Regards
    ExcelDemy

  317. Hello Tejas,

    If the search cell has multiple criteria, you can use the following formula:
    =TEXTJOIN(“, “, TRUE, IF(ISNUMBER(SEARCH($E$4:$E$5, B4)), $E$4:$E$5, “”))

    It checks if any of the values in the criteria range are found within the text in cell B4. The SEARCH function identifies the presence of these values, and ISNUMBER confirms their existence. The IF function returns the matching values, while TEXTJOIN concatenates them into a single string, separated by commas, ignoring empty results. This allows multiple matches to be displayed in one cell.

    Regards
    ExcelDemy

  318. Hello D.A.

    Here updated the VLOOKUPCMT function and named VLOOKUPCMT_New where handled threaded comments in newer versions of Excel.
    Added deletion of existing threaded comments and handling of threaded comments
    To ensure that the function can handle both old-style comments and new threaded comments. Use the following updated code:

    Function VLOOKUPCMT_New(lookup_value As Variant, table_array As Range, col_index_num As Long, range_lookup As Long) As Variant
        Application.Volatile
        Dim xReturn As Variant
        Dim yCell As Range
        Dim commentThread As CommentThreaded
    
        xReturn = Application.Match(lookup_value, table_array.Columns(1), range_lookup)
        If IsError(xReturn) Then
            VLOOKUPCMT_New = "Not Found"
        Else
            Set yCell = table_array.Columns(col_index_num).Cells(1)(xReturn)
            VLOOKUPCMT_New = yCell.Value
            With Application.Caller
                ' Delete existing comment or threaded comment
                If Not .Comment Is Nothing Then .Comment.Delete
                If Not .CommentThreaded Is Nothing Then .CommentThreaded.Delete
                
                ' Add new threaded comment if exists
                If Not yCell.CommentThreaded Is Nothing Then
                    Set commentThread = yCell.CommentThreaded
                    .AddCommentThreaded commentThread.Text
                End If
            End With
        End If
    End Function
    

    Copy Comments Using UDF Function

    This will copy the comments. To copy notes use the previous code.

    Regards
    ExcelDemy

  319. Hello Neet,

    You are most welcome. We are glad to hear that our article solved your problem. Thanks for your appreciation. Keep learning Excel with us.

    Regards
    ExcelDemy

  320. Hello Tara Man,

    You are most welcome. We are glad to hear that you got what you needed. Keep learning Excel with us.

    Regards
    ExcelDemy

  321. Hello Muhammad Farg,

    Yes, there are multiple ways to merge data under same headlines. In this article we showed how to merge sheets step by step: How to Merge Sheets in Excel

    You can follow this VBA code to merge the data and arrange it at the same time under one headline:
    Before using the code make sure both of your sheets contains the same headings.
    Sheet1 & Sheet2:

    
    Sub combine_multiple_sheets()
    Dim Row_1, Col_1, Row_last, Column_last As Long
    Dim headers As Range
    Set WB = ThisWorkbook
    Set wX = WB.Sheets.Add
    wX.Name = "Consolidated"
    Set headers = Application.InputBox("Choose the Headers", Type:=8)
    headers.Copy wX.Range("A1")
    Row_1 = headers.Row + 1
    Col_1 = headers.Column
    For Each Ws In WB.Worksheets
        If Ws.Name <> "Consolidated" Then
            Ws.Activate
            Row_last = Cells(Rows.Count, Col_1).End(xlUp).Row
            Column_last = Cells(Row_1, Columns.Count).End(xlToLeft).Column
            Range(Cells(Row_1, Col_1), Cells(Row_last, Column_last)).Copy wX.Range("A" & wX.Cells(Rows.Count, 1).End(xlUp).Row + 1)
        End If
    Next Ws
    Worksheets("Consolidated").Activate
    End Sub
    

    Output:

    Excel File: Merge Data in Same Headings.xlsx

    Regards
    ExcelDemy

  322. Hello Agung,

    To handle the leave request for next month we updated our existing template. Added a new sheet to enter leave requests. Based on this sheet modified the existing formulas to consider leave request.

    You will get a roaster template without leave request:
    =IF(AND(INDEX(LeaveRequests!$B$2:$B$11,MATCH(‘Roster 24”7’!$B$8,LeaveRequests!$A$2:$A$11,0),1)<=E$7,INDEX(LeaveRequests!$C$2:$C$11,MATCH('Roster 24''7'!$B$8,LeaveRequests!$A$2:$A$11,0),1)>=E$7), IF(OR($C8=””,E$7=””), “”,IF(D8= “”,C8, INDEX(Settings_Shift_Legend,IF(MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1>COUNTA(Settings_Shift_Legend),1,MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1))))&”- On leave”, IF(OR($C8=””,E$7=””), “”,IF(D8= “”,C8, INDEX(Settings_Shift_Legend,IF(MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1>COUNTA(Settings_Shift_Legend),1,MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1)))))

    24x7 Shif Roaster Template

    Here is the formula to show leave requests:
    =IF(AND(INDEX(LeaveRequests!$B$2:$B$11,MATCH(‘Roster 24”7’!$B$8,LeaveRequests!$A$2:$A$11,0),1)<=E$7,INDEX(LeaveRequests!$C$2:$C$11,MATCH('Roster 24''7'!$B$8,LeaveRequests!$A$2:$A$11,0),1)>=E$7), IF(OR($C8=””,E$7=””), “”,IF(D8= “”,C8, INDEX(Settings_Shift_Legend,IF(MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1>COUNTA(Settings_Shift_Legend),1,MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1))))&” – Leave Req”, IF(OR($C8=””,E$7=””), “”,IF(D8= “”,C8, INDEX(Settings_Shift_Legend,IF(MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1>COUNTA(Settings_Shift_Legend),1,MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1)))))

    24x7 Shift Roaster with Leave Requests

    Download the Excel File: 24×7 Shift Roaster Template with Leave Requests

    Regards
    ExcelDemy

  323. Hello Guadalupe Valdez,

    To get value from another workbook you can use the VLOOKUP function.

    In Worksheet2:
    In cell B2 Enter the following formula:
    =IFERROR(VLOOKUP($A2,[Worksheet1.xlsx]Sheet1!$A$2:$D$5, 2, FALSE), “”)
    This formula looks up the Slot number in A2 of Worksheet2 in the range A2
    of Worksheet1. The IFERROR function will return an empty string if no match is found.
    Then, drag the formula down to A440 cell.

    Use the same formula by changing the column number for First and Last name.

    In cell C2 of Worksheet2, enter the following formula:
    =IFERROR(VLOOKUP($A2, [Worksheet1.xlsx]Sheet1!$A$2:$D$440, 3, FALSE), “”)
    In cell D2 of Worksheet2, enter the following formula:
    =IFERROR(VLOOKUP($A2, [Worksheet1.xlsx]Sheet1!$A$2:$D$440, 4, FALSE), “”)
    Final Output:
    Get-Data-from-Another-Worksheet

    Here, I am attaching the Excel Files:
    Worksheet1.xlsxhttps://www.exceldemy.com/wp-content/uploads/2024/06/Worksheet2.xlsx
    Worksheet2.xlsx

    Regards
    ExcelDemy

  324. Hello Michelle,

    Yes, you can add a check in the VBA script to see if the search box is empty before proceeding with the search. If the search box is empty, the subroutine can exit early without performing any operations, preventing the script from unnecessarily processing all the data and causing lag.

    Here, added IsEmpty() and a simple comparison to an empty string (“”) to check if the Search_Cell is empty before running the rest of the code.
    If the search cell is empty, it displays a message box alerting the user and exits the subroutine early with Exit Sub.

    Sub SearchMultipleSheets()
        Main_Sheet = "VBA"
        Search_Cell = "B5"
        SearchType_Cell = "C5"
        Paste_Cell = "B9"
        Searched_Sheets = Array("Dataset 1", "Dataset 2")
        Searched_Ranges = Array("B5:F23", "B5:F23")
        Copy_Format = True
    
        ' Check if the search box is empty
        If IsEmpty(Sheets(Main_Sheet).Range(Search_Cell).Value) Or Sheets(Main_Sheet).Range(Search_Cell).Value = "" Then
            MsgBox "Search box is empty. Please enter a value to search.", vbExclamation
            Exit Sub
        End If
    
        Last_Row = Sheets(Main_Sheet).Range(Paste_Cell).End(xlDown).Row
        Last_Column = Sheets(Main_Sheet).Range(Paste_Cell).End(xlToRight).Column
        Set Used_Range = Sheets(Main_Sheet).Range(Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column), Cells(Last_Row, Last_Column))
        Used_Range.ClearContents
        Used_Range.ClearFormats
    
        Value1 = Sheets(Main_Sheet).Range(Search_Cell).Value
        Count = -1
    
        If Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Sensitive" Then
            Case_Sensitive = True
        ElseIf Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Insensitive" Then
            Case_Sensitive = False
        Else
            MsgBox ("Choose a Search Type.")
            Exit Sub
        End If
    
        For S = LBound(Searched_Sheets) To UBound(Searched_Sheets)
            Set Rng = Sheets(Searched_Sheets(S)).Range(Searched_Ranges(S))
            For i = 1 To Rng.Rows.Count
                For j = 1 To Rng.Columns.Count
                    Value2 = Rng.Cells(i, j).Value
                    If PartialMatch(Value1, Value2, Case_Sensitive) = True Then
                        Count = Count + 1
                        Rng.Rows(i).Copy
                        Set Paste_Range = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row + Count, Range(Paste_Cell).Column)
                        If Copy_Format = True Then
                            Paste_Range.PasteSpecial Paste:=xlPasteAll
                        Else
                            Paste_Range.PasteSpecial Paste:=xlPasteValues
                        End If
                    End If
                Next j
            Next i
        Next S
        Application.CutCopyMode = False
    End Sub
    

    Regards
    ExcelDemy

  325. Hello Derek,

    We are glad to hear that our solution helped you. Thanks for your appreciation. Keep learning Excel with us.

    Regards
    ExcelDemy

  326. Hello Yulissa Alvarez,

    Based on your given scenario created a dummy dataset to auto populate one sheet values based on information from another sheet.

    Here I used INDEX-MATCH functions to get data from another sheet dynamically.
    Use the following formulas:
    Task1: =INDEX(SheetC!$D$2:$D$13, MATCH(1, (SheetC!$A$2:$A$13=SheetB!$B2) * (SheetC!$B$2:$B$13=SheetB!$C2) * (SheetC!$C$2:$C$13=SheetB!$D2), 0))
    Task2: =INDEX(SheetC!$E$2:$E$13, MATCH(1, (SheetC!$A$2:$A$13=SheetB!$B2) * (SheetC!$B$2:$B$13=SheetB!$C2) * (SheetC!$C$2:$C$13=SheetB!$D2), 0))
    Task3: =INDEX(SheetC!$F$2:$F$13, MATCH(1, (SheetC!$A$2:$A$13=SheetB!$B2) * (SheetC!$B$2:$B$13=SheetB!$C2) * (SheetC!$C$2:$C$13=SheetB!$D2), 0))
    Task4: =INDEX(SheetC!$G$2:$G$13, MATCH(1, (SheetC!$A$2:$A$13=SheetB!$B2) * (SheetC!$B$2:$B$13=SheetB!$C2) * (SheetC!$C$2:$C$13=SheetB!$D2), 0))

    If you want to add more task just change the cell-refernce.
    Output:

    You can Download the Excel file:
    Auto Populate Values from Another Sheet

    Regards
    ExcelDemy

  327. Hello Michelle,

    The “Run-time error ‘9’: Subscript out of range” occurs when a specified sheet or range does not exist. Please check the sheet names and ranges in your Searched_Sheets and Searched_Ranges arrays are correctly spelled and match exactly with your Excel workbook.

    I made some changes in the code to check whether sheets and ranges exist in your Excel workbook.
    Make sure to update this according to your Excel workbook:
    Searched_Sheets = Array(“Dataset 1”, “Dataset 2”) # Update it with your sheet name.
    Searched_Ranges = Array(“B5:F23”, “B5:F23”) # Update it with your ranges.

    Added SheetExists function it will check if a sheet exists before accessing it.
    Error Handling will check for the existence of sheets to prevent “Subscript out of range” errors.

    Sub SearchMultipleSheets()
        Dim Main_Sheet As String
        Dim Search_Cell As String
        Dim SearchType_Cell As String
        Dim Paste_Cell As String
        Dim Searched_Sheets As Variant
        Dim Searched_Ranges As Variant
        Dim Copy_Format As Boolean
        Dim Last_Row As Long
        Dim Last_Column As Long
        Dim Used_Range As Range
        Dim Value1 As String
        Dim Case_Sensitive As Boolean
        Dim Count As Long
        Dim S As Integer
        Dim i As Long
        Dim j As Long
        Dim Rng As Range
        Dim Value2 As String
        Dim Paste_Range As Range
    
        ' Initialize variables
        Main_Sheet = "VBA"
        Search_Cell = "B5"
        SearchType_Cell = "C5"
        Paste_Cell = "B9"
        Searched_Sheets = Array("Dataset 1", "Dataset 2")
        Searched_Ranges = Array("B5:F23", "B5:F23")
        Copy_Format = True
    
        ' Clear the previous results
        Last_Row = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column).End(xlDown).Row
        Last_Column = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column).End(xlToRight).Column
        Set Used_Range = Sheets(Main_Sheet).Range(Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column), Cells(Last_Row, Last_Column))
        Used_Range.ClearContents
        Used_Range.ClearFormats
    
        ' Get the search value and type
        Value1 = Sheets(Main_Sheet).Range(Search_Cell).Value
        Count = -1
        If Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Sensitive" Then
            Case_Sensitive = True
        ElseIf Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Insensitive" Then
            Case_Sensitive = False
        Else
            MsgBox "Choose a Search Type."
            Exit Sub
        End If
    
        ' Search through the sheets and ranges
        For S = LBound(Searched_Sheets) To UBound(Searched_Sheets)
            If SheetExists(Searched_Sheets(S)) Then
                Set Rng = Sheets(Searched_Sheets(S)).Range(Searched_Ranges(S))
                For i = 1 To Rng.Rows.Count
                    For j = 1 To Rng.Columns.Count
                        Value2 = Rng.Cells(i, j).Value
                        If PartialMatch(Value1, Value2, Case_Sensitive) = True Then
                            Count = Count + 1
                            Rng.Rows(i).Copy
                            Set Paste_Range = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row + Count, Range(Paste_Cell).Column)
                            If Copy_Format = True Then
                                Paste_Range.PasteSpecial Paste:=xlPasteAll
                            Else
                                Paste_Range.PasteSpecial Paste:=xlPasteValues
                            End If
                        End If
                    Next j
                Next i
            Else
                MsgBox "Sheet " & Searched_Sheets(S) & " not found."
            End If
        Next S
        Application.CutCopyMode = False
    End Sub
    
    Function PartialMatch(Value1 As String, Value2 As String, Case_Sensitive As Boolean) As Boolean
        Dim Matched As Boolean
        Dim i As Long
    
        Matched = False
        For i = 1 To Len(Value2)
            If Case_Sensitive Then
                If Mid(Value2, i, Len(Value1)) = Value1 Then
                    Matched = True
                    Exit For
                End If
            Else
                If Mid(LCase(Value2), i, Len(Value1)) = LCase(Value1) Then
                    Matched = True
                    Exit For
                End If
            End If
        Next i
        PartialMatch = Matched
    End Function
    
    Function SheetExists(ByVal SheetName As String) As Boolean
        On Error Resume Next
        SheetExists = Not Sheets(SheetName) Is Nothing
        On Error GoTo 0
    End Function

    Regards
    ExcelDemy

  328. Hello Daniele,

    Buongiorno! Se la colonna della data è formattata correttamente ma la tabella pivot mostra ancora le date ogni giorno senza raggrupparle per mesi o anni, prova questi passaggi:

    Assicurati che tutte le date siano nel formato corretto e che non siano presenti valori di testo.
    Fai clic con il pulsante destro del mouse su qualsiasi data nella tabella pivot, seleziona “Gruppo” e scegli il raggruppamento desiderato (ad esempio, mesi o anni).
    Se il problema persiste, controlla le opzioni avanzate in Excel per assicurarti che l’opzione “Raggruppa date nel menu Filtro automatico” sia abilitata.

    Segui questi articoli per risolvere il tuo problema:

    [Fix] Cannot Group Dates in Pivot Table
    Excel Pivot Table Not Grouping Dates by Month (4 Solutions)
    How to Group Dates in Pivot Table: 7 Methods

    Good morning! If your date column is correctly formatted but the pivot table still shows dates daily without grouping by months or years, try these steps:

    1. Ensure all dates are in the correct format and there are no text values.
    2. Right-click any date in the pivot table, select “Group,” and choose the desired grouping (e.g., months or years).
    If this doesn’t work, check the advanced options in Excel to ensure ‘Group dates in the AutoFilter menu’ is enabled.

    If you still face problem, check out this articles:
    [Fix] Cannot Group Dates in Pivot Table
    Excel Pivot Table Not Grouping Dates by Month (4 Solutions)
    How to Group Dates in Pivot Table: 7 Methods

    Regards
    ExcelDemy

  329. Hello Lucy Jackson,

    Thanks for your appreciation, it means a lot to us. We are glad to hear that our article solved your problem. Keep learning Excel with us.

    Regards
    ExcelDemy

  330. Hello Tracy,

    Thanks for your appreciation, it means a lot to us. Keep learning Excel with us.

    Regards
    ExcelDemy

  331. Hello Zayaan,

    You are most welcome. Thanks for your appreciation. We have a category of pivot table and data analysis. We will create more pivot table for data analysis and power pivot. Keep learning Excel with us.

    Please explore our this section.
    Pivot Table in Excel
    Data Analysis in Excel
    Power Pivot

    Regards
    ExcelDemy

  332. Hello Bryan,

    You are most welcome. Thanks for your appreciation. Keep learning Excel with us.

    Regards
    ExcelDemy

  333. Hello Praveen

    As we used digit limit in our existing code that’s why it is showing this warning.

    Don’t worry! We have updated the existing VBA user-defined function to overcome the problem and work with much larger numbers. In the Indian numbering system, we use terms such as Thousand, Lakh, Crore, Arab, Kharab, Neel, Padma, and Shankh to express large numbers. So, the user-defined function will return the word-converted result using these terms. Moreover, we will use an Excel built-in TEXT function to get accurate results for huge numbers.

    Use the following updated code:

    Function AdvancedWord(SNum As String) As String
    
        Dim zDPInt As Integer
        Dim zArrPlace As Variant
        Dim zRStr_Paisas As String
        Dim zNumStr As String
        Dim zP As Integer
        Dim zTemp As String
        Dim zStrTemp As String
        Dim zRStr As String
    
        zArrPlace = Array("", " Thousand ", " Lakh ", " Crore ", " Arab ", " Kharab ", " Neel ", " Padma ", " Shankh ")
    
        If SNum = "" Then
            AdvancedWord = ""
            Exit Function
        End If
    
        zNumStr = Trim(CStr(SNum))
    
        If zNumStr = "" Then
            AdvancedWord = ""
            Exit Function
        End If
    
        zRStr = ""
    
        If (Val(zNumStr) >= 1E+19) Then
            AdvancedWord = "Digit exceeds Maximum limit"
            Exit Function
        End If
    
        zDPInt = InStr(zNumStr, ".")
    
        If zDPInt > 0 Then
            If (Len(zNumStr) - zDPInt) = 1 Then
                zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
            ElseIf (Len(zNumStr) - zDPInt) > 1 Then
                zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
            End If
            zNumStr = Trim(Left(zNumStr, zDPInt - 1))
        End If
    
        If InStr(zNumStr, "E") > 0 Then
            zNumStr = CDec(zNumStr)
        End If
    
        zP = 0
    
        Do While zNumStr <> ""
            If Len(zNumStr) > 2 Then
                If zP = 0 Then
                    zTemp = Right(zNumStr, 3)
                    zNumStr = Left(zNumStr, Len(zNumStr) - 3)
                Else
                    zTemp = Right(zNumStr, 2)
                    zNumStr = Left(zNumStr, Len(zNumStr) - 2)
                End If
            Else
                zTemp = zNumStr
                zNumStr = ""
            End If
    
            zStrTemp = ""
    
            If Val(zTemp) > 99 Then
                zStrTemp = word_GetH(Right(zTemp, 3), zP)
            ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
                zStrTemp = word_GetT(Right(zTemp, 2))
            ElseIf Val(zTemp) < 10 Then
                zStrTemp = word_GetD(Right(zTemp, 2))
            End If
    
            If zStrTemp <> "" Then
                zRStr = zStrTemp & zArrPlace(zP) & zRStr
            End If
    
            zP = zP + 1
        Loop
    
        If zRStr = "" Then
            zRStr = "No Rupees"
        Else
            zRStr = "Rupees " & zRStr
        End If
    
        If zRStr_Paisas <> "" Then
            zRStr_Paisas = " and " & zRStr_Paisas & " Paisas"
        End If
    
        AdvancedWord = zRStr & zRStr_Paisas & " Only"
    
    End Function
    
    Function word_GetH(zStrH As String, zP As Integer) As String
        
        Dim zRStr As String
    
        If Val(zStrH) < 1 Then
            word_GetH = ""
            Exit Function
        Else
            zStrH = Right("000" & zStrH, 3)
            If Mid(zStrH, 1, 1) <> "0" Then
                zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
            End If
    
            If Mid(zStrH, 2, 1) <> "0" Then
                zRStr = zRStr & word_GetT(Mid(zStrH, 2))
            Else
                zRStr = zRStr & word_GetD(Mid(zStrH, 3))
            End If
        End If
    
        word_GetH = zRStr
    
    End Function
    
    Function word_GetT(zTStr As String) As String
        
        Dim zTArr1 As Variant
        Dim zTArr2 As Variant
        Dim zRStr As String
    
        zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
        zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
    
        If Val(Left(zTStr, 1)) = 1 Then
            zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
        Else
            If Val(Left(zTStr, 1)) > 0 Then
                zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
            End If
            zRStr = zRStr & word_GetD(Right(zTStr, 1))
        End If
    
        word_GetT = zRStr
        
    End Function
    
    Function word_GetD(zDStr As String) As String
        
        Dim zArr_1 As Variant
    
        zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")
    
        If Val(zDStr) > 0 Then
            word_GetD = zArr_1(Val(zDStr) - 1)
        Else
            word_GetD = ""
        End If
        
    End Function

    Press Alt plus F11, click on Insert followed by Module, paste the given code in the module and Save

  334. Return to the sheet and select the intended cell.
  335. Apply the following formula: =AdvancedWord(TEXT(A1,"#.00"))
  336. Drag the Fill Handle icon to copy the formula down.
    Choose the intended cell, apply the given formula, drag the Fill Handle icon to copy the formula down
  337. Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    ExcelDemy

  • Dear,

    Thanks for your appreciation. Keep learning Excel with us.

    Regards
    ExcelDemy

  • Hello Lisa,

    We are glad to hear that our article helped you. It means a lot to us. Keep Learning Excel with us.

    Regards
    ExcelDemy

  • Hello Alphonse

    Thanks for sharing an important aspect of variable declaration. You are absolutely correct.

    In VBA, when we declare multiple variables in a single line, like Dim Str1, Str2, Str3, Str4, Str5 As String, only the last variable (in this case, Str5) is declared as the specified type (String). The others (Str1, Str2, Str3, and Str4) are implicitly declared as Variant types. So, If we expect all variables to hold string values, declaring them explicitly as String ensures they behave consistently.

    Why This Matters: Variants consume more memory than specific types like String. Explicit type declarations help prevent errors related to type mismatches. Moreover, string operations like concatenation might behave differently if the variable is not explicitly declared as a String. Again, If we perform validations or transformations assuming the data type is String, having a Variant could lead to bugs or incorrect results.

    So, our Improved Excel VBA Sub-procedure can be following:

    Sub Multiple_Lines()
        
        'variable declaration
        Dim Str1 As String, Str2 As String, Str3 As String, Str4 As String, Str5 As String
        
        Str1 = Range("B4")
        Str2 = Range("C4")
        Str3 = Range("D4")
        Str4 = Range("E4")
        Str5 = Range("F4")
        
        'create MsgBox with a new line
        MsgBox "The following information has been given" _
        & vbNewLine & Str1 & "," & Str2 & "," _
        & Str3 & "," & Str4 & "," & Str5
    
    End Sub

    Thanks again for sharing your expertise in ExcelDemy Community. Stay blessed.

    Regards
    ExcelDemy

  • Hello Lisa,

    Yes, you can streamline data copied from a bank statement in a Word document to an Excel spreadsheet.
    Follow the steps given below:

    1. Copy the data from your Word document.
    2. Open Excel and paste the data into a spreadsheet.
    3. Use the “Text to Columns” feature in Excel to split the data into columns based on delimiters like spaces or tabs.
    4. Organize the data by month using Excel’s sorting and filtering tools.

    You can follow this articles too: How to Copy from Word to Excel into Multiple Cells (3 Ways)
    How to Convert Word Table to Excel Spreadsheet: 6 Quick Methods
    How to Convert Word to Excel Keeping the Formatting – 2 Easy Methods
    How to Convert Word to Excel with Columns (2 Methods)

    Regards
    ExcelDemy

  • Hello John Facey,

    Yes, Excel has a limitation in handling very large numbers due to its floating-point arithmetic, which can lead to precision errors. When squaring 111,111,111, the correct result is 12,345,678,987,654,321, but Excel is displaying 12,345,678,987,654,300 due to this limitation. This discrepancy is because Excel can only precisely handle integers up to 15 digits. To get the exact result, consider using specialized software or programming languages that support arbitrary-precision arithmetic.

    Regards
    ExcelDemy

  • Hello Sia,

    To modify the VBA code to round up the sum result to the nearest integer, you can use the Application.WorksheetFunction.Ceiling function. Use the following updated code to get your desired result:

    Function SumByColor(CellColor As Range, SumRange As Range)
        Dim SumResult As Double
        Dim Cell As Range
    
        Application.Volatile
    
        For Each Cell In SumRange
            If Cell.Interior.Color = CellColor.Interior.Color Then
                SumResult = SumResult + Cell.Value
            End If
        Next Cell
    
        SumByColor = Application.WorksheetFunction.Ceiling(SumResult, 1)
    End Function
    

    The formula will be : =SumByColor(A1, A1:C1)

    Comment-reply-of-color-cells

    Regards
    ExcelDemy

  • Hello Alphonse,

    You are most welcome Your appreciation means a lot to us.

    Regards
    ExcelDemy

  • Hello J,

    Thanks for your comment and for noticing the fact! You are right about losing conditional formatting when the pivot table is refreshed.

    When a pivot table is refreshed, custom formatting will reset because the data structure may change; for example, new rows or columns might be added. Excel may not reapply the formatting rules applied to specific cells if the layout of the pivot table changes. So, in this case, you need to manually reapply the conditional formatting rules each time the pivot table is refreshed.

    Regards
    ExcelDemy

  • Hello Enrique Rafhael

    Thanks for visiting our blog and sharing your problem. The existing formula returns an array containing both latitude and longitude. If you are not using Microsoft 365, you need to press Ctrl+Shift+Enter instead of pressing Enter.

    However, If you want to avoid returning an array and want to get latitude and longitude individually, follow these steps:

    1. Press Alt+F11 to open VBA Editor.
    2. Click on Insert, followed by Module.
    3. Paste the following code in the module and save it:
      Option Explicit
      
      Private Const WGS84_A As Double = 6378137#
      Private Const WGS84_E As Double = 0.081819190842622
      
      Function UTMToLatitude(Easting As Double, Northing As Double, Zone As String) As Double
      
          Dim zoneNumber As Integer
          Dim zoneLetter As String
          Dim latitude As Double
      
          zoneNumber = Val(Left(Zone, Len(Zone) - 1))
          zoneLetter = Right(Zone, 1)
      
          Call ConvertUTMToLat(Easting, Northing, zoneNumber, zoneLetter, latitude)
      
          UTMToLatitude = latitude
      
      End Function
      
      Function UTMToLongitude(Easting As Double, Northing As Double, Zone As String) As Double
      
          Dim zoneNumber As Integer
          Dim zoneLetter As String
          Dim longitude As Double
      
          zoneNumber = Val(Left(Zone, Len(Zone) - 1))
          zoneLetter = Right(Zone, 1)
      
          Call ConvertUTMToLon(Easting, Northing, zoneNumber, zoneLetter, longitude)
      
          UTMToLongitude = longitude
      
      End Function
      
      Sub ConvertUTMToLat(Easting As Double, Northing As Double, zoneNumber As Integer, zoneLetter As String, ByRef latitude As Double)
      
          Dim k0 As Double
          k0 = 0.9996
      
          Dim E As Double, N As Double
          Dim A As Double, eccSquared As Double, eccPrimeSquared As Double
          Dim M As Double, mu As Double
          Dim e1 As Double, J1 As Double, J2 As Double, J3 As Double, J4 As Double, J5 As Double
          Dim FPhi1 As Double, C1 As Double, T1 As Double, R1 As Double, N1 As Double, D As Double
      
          E = Easting - 500000#
          If UCase(zoneLetter) < "N" Then
              N = Northing - 10000000#
          Else
              N = Northing
          End If
      
          A = WGS84_A
          eccSquared = WGS84_E ^ 2
          eccPrimeSquared = eccSquared / (1 - eccSquared)
      
          M = N / k0
          mu = M / (A * (1 - eccSquared / 4 - 3 * eccSquared ^ 2 / 64 - 5 * eccSquared ^ 3 / 256))
      
          e1 = (1 - Sqr(1 - eccSquared)) / (1 + Sqr(1 - eccSquared))
      
          J1 = 3 * e1 / 2 - 27 * e1 ^ 3 / 32
          J2 = 21 * e1 ^ 2 / 16 - 55 * e1 ^ 4 / 32
          J3 = 151 * e1 ^ 3 / 96
          J4 = 1097 * e1 ^ 4 / 512
      
          FPhi1 = mu + J1 * Sin(2 * mu) + J2 * Sin(4 * mu) + J3 * Sin(6 * mu) + J4 * Sin(8 * mu)
      
          C1 = eccPrimeSquared * Cos(FPhi1) ^ 2
          T1 = Tan(FPhi1) ^ 2
          R1 = A * (1 - eccSquared) / (1 - eccSquared * Sin(FPhi1) ^ 2) ^ 1.5
          N1 = A / Sqr(1 - eccSquared * Sin(FPhi1) ^ 2)
          D = E / (N1 * k0)
      
          latitude = FPhi1 - (N1 * Tan(FPhi1) / R1) * (D ^ 2 / 2 - (5 + 3 * T1 + 10 * C1 - 4 * C1 ^ 2 - 9 * eccPrimeSquared) * D ^ 4 / 24 + (61 + 90 * T1 + 298 * C1 + 45 * T1 ^ 2 - 252 * eccPrimeSquared - 3 * C1 ^ 2) * D ^ 6 / 720)
          latitude = latitude * 180 / Application.WorksheetFunction.Pi()
      
      End Sub
      
      Sub ConvertUTMToLon(Easting As Double, Northing As Double, zoneNumber As Integer, zoneLetter As String, ByRef longitude As Double)
      
          Dim k0 As Double
          k0 = 0.9996
      
          Dim E As Double, N As Double
          Dim A As Double, eccSquared As Double, eccPrimeSquared As Double
          Dim M As Double, mu As Double
          Dim e1 As Double, J1 As Double, J2 As Double, J3 As Double, J4 As Double, J5 As Double
          Dim FPhi1 As Double, C1 As Double, T1 As Double, R1 As Double, N1 As Double, D As Double
      
          E = Easting - 500000#
          If UCase(zoneLetter) < "N" Then
              N = Northing - 10000000#
          Else
              N = Northing
          End If
      
          A = WGS84_A
          eccSquared = WGS84_E ^ 2
          eccPrimeSquared = eccSquared / (1 - eccSquared)
      
          M = N / k0
          mu = M / (A * (1 - eccSquared / 4 - 3 * eccSquared ^ 2 / 64 - 5 * eccSquared ^ 3 / 256))
      
          e1 = (1 - Sqr(1 - eccSquared)) / (1 + Sqr(1 - eccSquared))
      
          J1 = 3 * e1 / 2 - 27 * e1 ^ 3 / 32
          J2 = 21 * e1 ^ 2 / 16 - 55 * e1 ^ 4 / 32
          J3 = 151 * e1 ^ 3 / 96
          J4 = 1097 * e1 ^ 4 / 512
      
          FPhi1 = mu + J1 * Sin(2 * mu) + J2 * Sin(4 * mu) + J3 * Sin(6 * mu) + J4 * Sin(8 * mu)
      
          C1 = eccPrimeSquared * Cos(FPhi1) ^ 2
          T1 = Tan(FPhi1) ^ 2
          R1 = A * (1 - eccSquared) / (1 - eccSquared * Sin(FPhi1) ^ 2) ^ 1.5
          N1 = A / Sqr(1 - eccSquared * Sin(FPhi1) ^ 2)
          D = E / (N1 * k0)
      
          longitude = (D - (1 + 2 * T1 + C1) * D ^ 3 / 6 + (5 - 2 * C1 + 28 * T1 - 3 * C1 ^ 2 + 8 * eccPrimeSquared + 24 * T1 ^ 2) * D ^ 5 / 120) / Cos(FPhi1)
          longitude = zoneNumber * 6 - 183 + longitude * 180 / Application.WorksheetFunction.Pi()
      
      End Sub

    4. Return to the sheet and choose cell E6.
    5. Apply the following formula and hit Enter: =UTMToLatitude($B6, $C6, $D6)
    6. Now, drag the Fill Handle icon to cell E16.
    7. Again, choose cell F6 and apply the formula: =UTMToLongitude($B6, $C6, $D6)
    8. Drag the Fill Handle icon to copy the formula down.

    Hopefully, following this procedure, you can avoid returning an array. Good luck.

    Regards
    ExcelDemy

  • Hello Onkar,

    Please try this updated VBA code. Replace “C:\YourFolderPath\” with the path to your files. This updated code will ensures headers are copied only from the first file.

    
    Sub MergeFiles()
        Dim FolderPath As String, Filename As String, Sheet As Worksheet
        Dim HeaderCopied As Boolean
        FolderPath = "C:\YourFolderPath\"
        Filename = Dir(FolderPath & "*.xlsx")
        HeaderCopied = False
    
        Do While Filename <> ""
            With Workbooks.Open(Filename:=FolderPath & Filename)
                For Each Sheet In .Sheets
                    If HeaderCopied = False Then
                        Sheet.Rows(1).Copy Destination:=ThisWorkbook.Sheets(1).Range("A1")
                        HeaderCopied = True
                    End If
                    Sheet.Rows("2:" & Sheet.Rows.Count).Copy _
                        Destination:=ThisWorkbook.Sheets(1).Range("A" & ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1)
                Next Sheet
                .Close False
            End With
            Filename = Dir
        Loop
    End Sub
    

    Regards
    ExcelDemy

  • Hello Cosmin,

    To fix your issue, use the following formula.
    Improved Excel Formula: =INT(B5/300) & ” days ” & INT(MOD(B5/300, 1) * 5) & ” hours ” & MOD(B5, 60) & ” minutes”

    Regards
    ExcelDemy

  • Hello Rebecca,

    Thanks for your compliments! Your appreciation means a lot to us.

    You can create a countdown from a specific date, excluding weekends and holidays, using a combination of the TODAY, ABS and NETWORKDAYS.INTL functions in Excel.

    Excel Formula: =ABS(NETWORKDAYS.INTL($C5,TODAY(),1,$C$11:$D$12))

    Comment-reply-of-countdown

    Hopefully, you have found the solution you were looking for. You can download the workbook used to solve your problem from the following link:
    CountDown Excel File.xlsx

    Regards
    ExcelDemy

  • Hello Mp3 juice,

    You are most welcome. Thanks for your appreciation. Our free templates are ready to use.

    Regards
    ExcelDemy

  • Hello Valarmathi,

    You are most welcome. Thanks for your appreciation.

    Regards
    ExcelDemy

  • Hello Emrah,

    You are most welcome. To adapt DeepL you may use third party Add-ins, Power Query and VBA code. But VBA code will be more complex as you need to use JSON converter.

    You can use the Add-ins “Text Translator for Excel”
    DeepL Translator

    Using Power Query with DeepL API

    Use the Power Query to call the DeepL API directly from Excel.
    Follow the steps given below:

    Step 1: Get Your API Key from DeepL
    Sign up on the DeepL website and get your API key.
    Step 2: Open Excel and Launch Power Query
    Open Data tab > from Other Sources > select Web.

    Step 3: Configure the API Request
    In the Web dialog box, enter the DeepL API URL with your query parameters,
    Insert YOUR_API_KEY with your actual API key and modify the text and target_lang parameters as needed.
    Then click OK.

    Step 4: Transform the Data
    Power Query will open a new window with the API response.
    Use the available transformation tools to parse the JSON response and extract the translated text.
    Finally, Close & Load to import the data back into Excel.

    Regards
    ExcelDemy

  • Hello Umeeksha Sharma,

    Thank you for sharing your solution! Creating a new Excel file and importing the problematic file is indeed a practical approach. This method can help ensure the dates are correctly categorized in years and months format. If you need further assistance or have any other tips to share, feel free to let us know!

    Regards
    ExcelDemy

  • Hello IDn,

    Thanks for your appreciation. It means a lot to us.

    Regards
    ExcelDemy

  • Hello Amara,

    Here I’m attaching a sample dataset for CRM and Supply chain. You can modify this dataset based on your requirements.
    Dataset of CRM:CRM Dataset.xlsx
    Dataset of Supply Chain: Supply Chain Dataset.xlsx

    Regards
    ExcelDemy

  • Hello Abdul,

    Excel file is given in the download section of the article. You also can download the template with formula from this link : Ageing Formula 30 60 90 Days.xlsx

    Regards
    ExcelDemy

  • Hello Michael George Eichbaum,

    Can you please specify which portion of the article seems complex or hard to you? We will update this part for you.
    But this is the easiest way to insert horizontal slicer in Excel.

    Regards
    ExcelDemy

  • Hello Nhung,

    Your appreciation means a lot to us. We are glad that our article helped you to fix your issues.

    Regards
    ExcelDemy

  • Hello John,

    You are most welcome. Glad to hear that it saved you from fiddling tasks.

    Regards
    ExcelDemy

  • Hello Slides Downloader,

    We are glad to hear that it worked for you and you got relief from frustrating tasks. Thanks for your appreciation. It means a lot to us.

    Regards
    ExcelDemy

  • Hello Merry Gizaw,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Laura,

    Sorry to hear your issue. Here, I am suggesting some troubleshooting steps to resolve the problem. But I will encourage you to submit your Excel file in our ExcelDemy Forum so that we can inspect it thoroughly.

    1. Remove Non-Printable Characters:

    Use =CLEAN(C2) to clean the data.

    2. Check for Merged Cells:

    Ensure there are no merged cells in your dataset.
    Data Consistency:

    3. Make sure all data in Column C are of the same type (text).

    4. Sort by Multiple Columns:

    Use Data > Sort,
    and add levels to sort by Column C first and then Column E.

    5. Manual Inspection:

    Check the middle section where the sort restarts for any anomalies.

    6. New Worksheet:

    Copy and paste the data into a new worksheet and try sorting again.

    Regards
    ExcelDemy

  • Hello Selva,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Kitonski,

    Here, I made some fixes. Please try this updated code:
    Used the shell.Run command which is synchronous (True as the last parameter), ensuring the Python script completes before the VBA code proceeds.
    Using the Now function to calculate the start and end times accurately.
    To be safe used the Shell execution uses the correct quotation marks to avoid syntax errors.

    Sub RunPythonScript()
    
        Dim exe As String, pth As String
        Dim fileToOpen As Variant
        Dim wsMaster As Worksheet
        Dim wbTextImport As Workbook
        Dim myDir As String
        Dim startTime As Doubleks:
        Dim endTime As Double
        Dim totalTime As Double
        Dim shell As Object
        Dim command As String
    
        myDir = "C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF"
    
        exe = "C:\Users\uie34719\AppData\Local\Programs\Python\Python39\python.exe"
        pth = "C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF\Main_Network_Script-WithCSV.py"
    
        startTime = Now
    
        Set shell = CreateObject("WScript.Shell")
        command = """" & exe & """ """ & pth & """"
        shell.Run command, 1, True ' The last parameter True makes the execution synchronous
    
        endTime = Now
        totalTime = (endTime - startTime) * 24 * 60 * 60 ' Convert time difference to seconds
    
        MsgBox totalTime & " seconds"
    
        ' Schedule OkGo to run after the total time
        Application.OnTime Now + TimeValue("0:00:" & Round(totalTime)), "OkGo", schedule:=True
    
    End Sub
    
    Sub OkGo()
    
        Dim fileToOpen As String
        Dim wsMaster As Worksheet
        Dim wbTextImport As Workbook
    
        Range("E2:H1048576").ClearContents
    
        fileToOpen = "C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF\1CSV_OUTPUT.csv"
    
        Workbooks.OpenText fileToOpen
    
        Set wbTextImport = ActiveWorkbook
    
        Set wsMaster = ThisWorkbook.Worksheets("Main_Template")
    
        wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("E2")
    
        wbTextImport.Close False
    
    End Sub
    

    Regards
    ExcelDemy

  • Hello Hassan Ibrahim Mohammed,

    Hope you are doing well. I will suggest you to go through the article again. You can download the dataset to try the calculations.

    1: Likert Scale Range and Its Effect on Final Results

    Here, each Likert scale response (ranging from 1 to 5) is assigned a numerical value. These values are added up for each respondent to get a total score. For example, if a respondent’s answers to five questions are 4, 3, 5, 2, and 1, the total score is 15. This summed score helps in analyzing the overall sentiment or opinion of the respondents. Higher scores indicate more positive responses, while lower scores indicate more negative responses.

    2: Adding Non-Parametric Tests to Excel
    To add non-parametric tests like the Mann-Whitney U test or the Kruskal-Wallis test to your Excel sheet, you can use the dataset from the article. But Excel does not have built-in non-parametric test functions, you need to manually calculate them using formulas and the Data Analysis ToolPak (If not available in Ribbon, get it from Excel options). For example, you can rank your Likert scale data using the RANK function and then apply the necessary formulas to perform the tests. It may require detailed setup and understanding of the test procedures, but it can be done effectively in Excel.

    Regards
    ExcelDemy

  • Hello Patricia,

    To solve this issue:
    First, add columns for monthly interest and new balance.
    Then, for each debt, include columns for monthly interest and new balance.

    Use the following formula to calculate monthly interest:

    Monthly Interest = Previous Balance * (Annual Interest Rate / 12)

    Example:
    Debt 1 (D2): =C2*0.17/12
    Debt 2 (H2): =G2*0.19/12
    Debt 3 (L2): =K2*0.20/12

    Use the following formula to calculate new balance:

    New Balance = Previous Balance + Monthly Interest – Payment

    Example:
    Debt 1 (E2): =C2+D2-B2
    Debt 2 (I2): =G2+H2-F2
    Debt 3 (M2): =K2+L2-J2

    Regards
    ExcelDemy

  • Hello Sandy,

    You can use this formula:
    =OR(WEEKDAY([@DATE], 2) = 6, WEEKDAY([@DATE], 2) = 7)
    This checks if the [@DATE] column value is a Saturday (6) or Sunday (7).

    Regards
    ExcelDemy

  • Hello Poppy Lukhele,

    Could you be more specific? If possible share any sample data.

    Regards
    ExcelDemy

  • Hello Mary Grace,

    Please use this updated code to get your desired result:
    Here I added an input box to select the column with the names of the recipients (XRcptsName ). Then, updated the email body to include the recipient’s actual name and removed the word “text.”

    Public Sub SendReminderMail()
    'Declare the variables
        Dim XDueDate As Range
        Dim XRcptsEmail As Range
        Dim XRcptsName As Range
        Dim xMailContent As Range
        Dim xRngDn As Range
        Dim xCrtOut As Object
        Dim xValDateRng As String
        Dim xValSendRng As String
        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 a input box
        Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)
        If XDueDate Is Nothing Then Exit Sub
        'Insert a input box for selecting the recipients
        Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
        If XRcptsEmail Is Nothing Then Exit Sub
        Set XRcptsName = Application.InputBox("Choose the column for the name of the recipients:", "ExcelDemy", , , , , , 8)
        If XRcptsName Is Nothing Then Exit Sub
        'To enter the text mail, insert a input box
        Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", , , , , , 8)
        If xMailContent Is Nothing Then Exit Sub
        'Count rows for the due dates
        xFinalRw = XDueDate.Rows.Count
        Set XDueDate = XDueDate(1)
        Set XRcptsEmail = XRcptsEmail(1)
        Set xMailContent = xMailContent(1)
        Set XRcptsName = XRcptsName(1)
        '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 = XDueDate.Offset(k - 1).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 <= 7 And CDate(xValDateRng) - Date > 0 Then
                xValSendRng = XRcptsEmail.Offset(k - 1).Value
                'Create the subject, body and text contents with the required variables
                xSubEmail = xMailContent.Offset(k - 1).Value & " on " & xValDateRng
                CrVbLf = "<br><br>"
                xMsg = "<HTML><BODY>"
                xMsg = xMsg & "Dear " & XRcptsName.Offset(k - 1).Value & CrVbLf
                xMsg = xMsg & xMailContent.Offset(k - 1).Value & CrVbLf
                xMsg = xMsg & "</BODY></HTML>"
                '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
                    .Display
                    
                    '.Send
                
                End With
                Set xMailSections = Nothing
            End If
        End If
        Next
        Set xCrtOut = Nothing
    End Sub
    

    Regards
    ExcelDemy

  • Hello Paul Tupper,

    Yes, we can use this formula too. Thanks for your suggestions, we really appreciate it.

    Regards
    ExcelDemy

  • Hello Paul Tupper,

    Of course, you can use this formula too. Both are doing the same calculations. Our formula is simpler nothing else. Thanks for your suggestions, we really appreciate it.

    Regards
    ExcelDemy

  • Hello George Jululian,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Hen,

    Thanks for your appreciation.

    Regards
    ExcelDemy

  • Dear,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Jeromy Adofo,

    You are most welcome. please stay connected with us.

    Regards
    ExcelDemy

  • Hello Myron,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Aleksandar,

    You can concatenate the cell values into a single string to get one QR code from different cell values.

    You can use the following code:

    Sub GenerateQRCode()
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name as necessary
        
        Dim cell As Range
        Dim qrString As String
        qrString = ""
        
        ' Loop through the cells you want to include in the QR code
        For Each cell In ws.Range("A1:A10") ' Specify your range
            qrString = qrString & cell.Value & " " ' Concatenate values with a separator if needed
        Next cell
        
        ' Generate QR Code
        Dim qrCode As Object
        Set qrCode = ws.Pictures.Insert("https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=" & qrString)
        
        With qrCode
            .ShapeRange.LockAspectRatio = msoFalse
            .Width = 150
            .Height = 150
            .Left = ws.Cells(1, 3).Left
            .Top = ws.Cells(1, 3).Top
        End With
    End Sub

    Regards
    ExcelDemy

  • Hello Muhammad Afzaal Jutt,

    You need to use a combination of functions like SUBSTITUTE, TEXTJOIN, FILTERXML, and SUM. Currently, Excel does not directly provide a simple formula for this without VBA.

    You can use the following formula:
    =SUM(FILTERXML("<t><s>" & SUBSTITUTE(SUBSTITUTE(A1, " ", "</s><s>"), "$", "") & "</s></t>", "//s[number(.)=.]"))

    Regards
    ExcelDemy

  • Hello Ckwong,

    To get the result as State Name. You need to use the state name column as an array of INDEX function. Just replace the cell range from C5:C12 to B5:B12.
    You can use the following formula:
    =INDEX(B5:B12, MATCH(TRUE, INDEX(C5:C12>700000, 0), 0))

    Regards
    ExcelDemy

  • Hello Esraa Yaseen,

    You are most welcome. We are glad to help you.

    Regards
    ExcelDemy

  • Hello Prabhakar Srinivasagam,

    You are most welcome. Your kind words means a lot to us. We are always here to help you.

    Regards
    ExcelDemy

  • Hello Abinsh,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Jen,

    Thanks for coming back, hopefully it will work this time. If it doesn’t work, you can let us know your problem in the comment section below.

    Regards
    ExcelDemy

  • Hello Usama Jamil,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Atif Hassan,

    Thanks for your appreciation. You can use the following formula to use XLOOKUP with tolerance level:
    =XLOOKUP(TRUE, IF(ABS($C$2:$C$4 – $A$2) <= $B$2, TRUE, FALSE), $D$2:$D$4, "Not Found")

    https://www.exceldemy.com/wp-content/uploads/2024/05/XLOOKUP-Based-on-Customized-Criteria.gif

    Please download the Excel file for better understanding:
    XLOOKUP with Tolerance Level.xlsx

    Regards
    ExcelDemy

  • Hello Danielle Davies,

    You are most welcome. Your appreciation means a lot to us.

    Regards
    ExcelDemy

  • Hello Vito,

    Don’t be sorry Vito. Let us know your feedback, hopefully it will work.

    Regards
    ExcelDemy

  • Hello Jose,

    You are most welcome. Your appreciation means a lot to us.

    Regards
    ExcelDemy

  • Hell MM,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Michael,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello T Althoff,

    Our updated article indeed includes three templates of different cases, each addressing different scenarios. You can modify these templates to recalculate interest and principal based on irregular payment dates, they offer versatile solutions for various other scenarios.

    Regards
    ExcelDemy

  • Hello Reza Fadlilah,

    Here are some articles from where you can download dataset of different types to practice your skills.
    Excel Sample Data (Free Download 13 Sample Datasets)
    Excel Data for Analysis (Free Download 11 Suitable Datasets)
    Excel Data for Practice Free Download

    Regards
    ExcelDemy

  • Dear Ayebusiwa Oyesanmi Emmanuel,

    Answers are given in the Solution sheet of Excel File which is in Download Practice Workbook section.
    Answer sheet

    Soluation-of-Advanced-MCQs-for-Exam

    Regards
    ExcelDemy

  • Hello Emon,

    You can download the Cheat Sheet free of cost just by providing your valid email address. To get the files go to the “Download Excel Formulas Cheat Sheet PDF & Excel Files” section of this post and enter your email address. Then check your email to get the download links.

    Best Regards
    ExcelDemy

  • Hello Abdul,

    It’s glad to hear and you are most welcome.

    Regards
    ExcelDemy

  • Hello Rhonda Rodriguez,

    Thanks for your appreciation. You are most welcome.

    Regards
    ExcelDemy

  • Hello Michailmqo,

    You can mail to [email protected].

    Regards
    ExcelDemy

  • Hello Rajkapoor,

    Thanks for your appreciation and you are most welcome.

    Regards
    ExcelDemy

  • Hello Deborah Yassen,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Kukuh Romansyah,

    You are most welcome (Sama-sama). It’s glad to hear that you found it useful.

    Regards
    ExcelDemy

  • Hello Lukas Cenjar,

    This VBA code won’t work on MAC but here we are giving you a sample code that might work on MAC. We updated our existing code based on MAC requirements.
    1. We used AppleScript to interact with the Mail application on macOS to create and send the email.
    2. Then, used MacScript to run AppleScript commands from VBA to handle Mac-specific scenarios.

    Public Sub SendReminderMail()
        'Declare the variables
        Dim XDueDate As Range
        Dim XRcptsEmail As Range
        Dim xMailContent As Range
        Dim xFinalRw As Long
        Dim xValDateRng As String
        Dim xValSendRng As String
        Dim k As Long
        Dim xSubEmail As String
        Dim xMsg As String
        Dim AppleScript As String
    
        'Input box for the deadline/due date column
        Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", Type:=8)
        If XDueDate Is Nothing Then Exit Sub
        
        'Input box for email addresses
        Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", Type:=8)
        If XRcptsEmail Is Nothing Then Exit Sub
        
        'Input box for email content
        Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", Type:=8)
        If xMailContent Is Nothing Then Exit Sub
        
        'Count rows for due dates
        xFinalRw = XDueDate.Rows.Count
    
        'Loop through each row
        For k = 1 To xFinalRw
            xValDateRng = XDueDate.Cells(k).Value
            
            'Check if the due date is within the next week
            If xValDateRng <> "" And CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
                xValSendRng = XRcptsEmail.Cells(k).Value
                xSubEmail = xMailContent.Cells(k).Value & " on " & xValDateRng
                xMsg = "Dear " & xValSendRng & vbCrLf & "Text : " & xMailContent.Cells(k).Value
    
                'AppleScript to send email
                AppleScript = "tell application ""Mail""" & vbCrLf
                AppleScript = AppleScript & "set newMessage to make new outgoing message with properties {subject:""" & xSubEmail & """, content:""" & xMsg & """, visible:true}" & vbCrLf
                AppleScript = AppleScript & "tell newMessage" & vbCrLf
                AppleScript = AppleScript & "make new to recipient at end of to recipients with properties {address:""" & xValSendRng & """}" & vbCrLf
                AppleScript = AppleScript & "send" & vbCrLf
                AppleScript = AppleScript & "end tell" & vbCrLf
                AppleScript = AppleScript & "end tell"
    
                'Run AppleScript from VBA
                MacScript (AppleScript)
            End If
        Next
    End Sub
    

    Regards
    ExcelDemy

  • Hello ER,

    We are glad to hear that our article was helpful to you. Thanks for your appreciation. Keep learning Excel.

    Regards
    ExcelDemy

  • Hello Jan,

    You need to scale the image to show the borders.
    You can use the following updated code to do so.
    Here, I used 0.9,to scale 90% of the cell’s width and height.
    Next, used LockAspectRatio to maintain the aspect ratio of the pictures.
    Then, Adjusted the alignment using Left and Top, it will center the picture within the cell after resizing.

    Sub InsertMultiplePictures()
        Dim Pictures() As Variant
        Dim PictureFormat As String
        Dim PicRng As Range
        Dim PicShape As Shape
        Dim ScaleWidth As Single
        Dim ScaleHeight As Single
    
        ' Define scale factors (90% of the cell size)
        ScaleWidth = 0.9
        ScaleHeight = 0.9
    
        On Error Resume Next
        Pictures = Application.GetOpenFilename(PictureFormat, MultiSelect:=True)
        PicColIndex = Application.ActiveCell.Column
    
        If IsArray(Pictures) Then
            PicRowIndex = Application.ActiveCell.Row
            For lLoop = LBound(Pictures) To UBound(Pictures)
                Set PicRng = Cells(PicRowIndex, PicColIndex)
                Set PicShape = ActiveSheet.Shapes.AddPicture(Pictures(lLoop), msoFalse, msoCTrue, PicRng.Left, PicRng.Top, PicRng.Width, PicRng.Height)
                ' Scale the picture size
                With PicShape
                    .LockAspectRatio = msoTrue
                    .Width = PicRng.Width * ScaleWidth
                    .Height = PicRng.Height * ScaleHeight
                    ' Center the picture in the cell
                    .Left = PicRng.Left + (PicRng.Width - .Width) / 2
                    .Top = PicRng.Top + (PicRng.Height - .Height) / 2
                End With
                PicRowIndex = PicRowIndex + 1
            Next
        End If
    End Sub
    

    Regards
    ExcelDemy

  • Hello Alami,

    Yes. you need to select the cell in the InputBox every time you want to clear the cells.
    But if you want to avoid using InputBox, I’m giving you two different code to do so.

    Mentioned the range then clear cells:

    Sub Clear_Range()
        Sheets("Sheet1").Range("A1:B10").Clear
    End Sub
    

    Select the cells then run the VBA code:

    Sub Clear_from_Selection()
        Selection.ClearContents
    End Sub
    

    Regards
    ExcelDemy

  • Hello A.Nirmala Rani,

    You can the following formula for the volume of a cylindrical segment:
    Mesaurements
    Where:
    V is the volume in liters,
    L is the length of the cylinder in meters,
    R is the radius of the cylinder in meters,
    h is the height of the liquid level in meters.

    Given the specific dimensions:
    L=5.0 meters (500 cm),
    R=1.0 meter (100 cm),
    h varies from 0.01 meters (1 cm) to 2.0 meters (200 cm)

    This formula calculates the volume of the liquid based on the height from the base up to the liquid level within a horizontal cylindrical tank. To use this formula, ensure to convert all measurements (radius, length, and height) to meters before applying them in the calculation.

    At 1 cm height: Approximately 9.41 liters
    At 2 cm height: Approximately 26.59 liters
    At 3 cm height: Approximately 48.77 liters
    At 4 cm height: Approximately 74.97 liters
    At 5 cm height: Approximately 104.62 liters
    At 10 cm height: Approximately 293.63 liters
    ———————————————————
    To see the full list of calculated volumes for each centimeter increment from 1 cm to 100 cm for the horizontal cylindrical tank, Download the Excel File.
    List of Calculated Volumes.xlsx

    Regards
    ExcelDemy

  • Dear Curt,

    It’s glad to hear that our article solved your issue. You are most welcome.

    Regards
    ExcelDemy

  • Hello Kevin,

    Here the needle is pointing at 45 but as we kept the number inside the pie chart may seem 55 but actual point is 45. For your better understanding I am attaching another article for the clear view. Sorry for the confusion.
    Please read this article from step 6: How to Create a Gauge Chart in Excel (With Easy Steps)
    You can watch these two images to clear the confusion.

    Speedometer Speedometer

    Regards
    ExcelDemy

  • Hello Harry Ingram,

    You can use this formula to extract only the numbers, including the decimal point.
    =TEXTJOIN(“”, TRUE, IFERROR(IF(ISNUMBER(SEARCH(MID(B5, ROW(INDIRECT(“1:” & LEN(B5))), 1), {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″,”.”})), MID(B5, ROW(INDIRECT(“1:” & LEN(B5))), 1), “”), “”))

    Feel free to download the Excel file from the link below, you’ll find examples of the formula.
    Excel file: Extract Decimal Point Numbers

    Regards,
    ExcelDemy

  • Hello Paresh Kanti Paul,

    This article includes two dynamic ways for currency conversion.
    Real Time Currency Converter in Excel

    Regards
    ExcelDemy

  • Hello Martin,

    We are glad to hear that you found this article useful! Including a real-life example in tutorials can indeed make the steps more relatable and easier to understand. Applying these models to specific scenarios like maximizing profit by determining the production units for products A and B is a great way to leverage linear programming. We will include this in our next update.

    Regards
    ExcelDemy

  • Hello Karunesh Pandey,

    As calibration details varies with lab type we can provide you the criteria. You can fill out your information and edit the list.

    Calibration details

    Equipment Name Serial Number Manufacturer Model Calibration Frequency Last Calibration Date Next Due Date Technician Initials Comments
    pH Meter 12345 ABC Inc. Model X Monthly 3/15/2024 4/15/2024 AB
    Balance 67890 XYZ Corp. Model Y Monthly 3/20/2024 4/20/2024 CD
    Spectrophotometer 13579 DEF Ltd. Model Z Monthly 3/10/2024 4/10/2024 EF
    Pipettes (10-100µl) 24680 GHI Co. Model A Monthly 3/25/2024 4/25/2024 GH

    Regards
    ExcelDemy

  • Hello Gary Ward,

    Thanks for your valuable suggestion we will include this in our article.

    Regards
    ExcelDemy

  • Hello Liz,

    You can avoid the absolute reference ($ sign). Here absolute reference is used so that it can avoid error if anyone copy the formula in another cell.

    Regards
    ExcelDemy

  • Hello Alphonse,

    We are glad to hear from you again! Don’t worry about missed message, technical glitches happen to the best of us. We are glad to hear that the code for counting Mondays worked well for you. Thank you so much for expressing your gratitude—it’s always means a lot to us to know when something was helpful. You are most welcome and cheers to you too.

    Regards
    ExcelDemy

  • Hello Cynthia Satterwhite,

    Neither any problem nor any solution are given. These dataset are given to practice by yourself.

    Regards
    ExcelDemy

  • Hello Nilanjan,

    Excel for Mac doesn’t support the WorksheetFunction.WebService method due to differences in how Excel for Mac interacts with web services compared to Excel for Windows. In Mac, you need to directly access Google Translate’s API using HTTP requests or using a different method provided by Google Translate.

    Function TranslateText(text_str As String, src_lang As String, trgt_lang As String) As String
        Dim url As String
        Dim xmlhttp As Object
        Dim response As String
        Dim json As Object
        
        ' Construct the URL for Google Translate API
        url = "https://translation.googleapis.com/language/translate/v2?key=YOUR_API_KEY&q=" & _
                VBA.URLEncode(text_str) & "&source=" & src_lang & "&target=" & trgt_lang
    
        ' Send HTTP request to Google Translate API
        Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
        xmlhttp.Open "GET", url, False
        xmlhttp.send
        
        ' Parse the JSON response
        response = xmlhttp.responseText
        Set json = JsonConverter.ParseJson(response)
        
        ' Extract and return the translated text
        TranslateText = json("data")("translations")(1)("translatedText")
    End Function
    

    Replace “YOUR_API_KEY” with your actual Google Cloud Platform API key.
    Remember to enable the “Microsoft XML, v6.0” reference in the VBA editor (under Tools > References) to use CreateObject(“MSXML2.XMLHTTP”) for making HTTP requests.

    Regards
    ExcelDemy

  • Hello Umang,

    Here are some articles from where you can download dataset of different types to practice your skills.
    Excel Sample Data (Free Download 13 Sample Datasets)
    Excel Data for Analysis (Free Download 11 Suitable Datasets)
    Excel Data for Practice Free Download

    Regards
    ExcelDemy

  • Hello Sameul Smith,

    You can follow any of the methods of your choice to make your list. If you face any problem regarding it you can share your list with us. If you want to upload any image or Excel file you can post it on our ExcelDemy Forum.

    Regards
    ExcelDemy

  • Hello Fredrick Aringo,

    You must use the last line to do AutoFilter based on criteria.

    ActiveSheet.Range(“A1:E14”).AutoFilter field:=2, Criteria1:=CatSites
    

    Please avoid the first line. We updated our code.

    Regards
    ExcelDemy

  • Dear Bharath L,

    You are most welcome. We are glad that our solution worked for you.

    Regards
    ExcelDemy

  • Hello,

    These datasets are given to use it for practice purpose. Currently we don’t have any solution of the task. But if you want you can send your completed task, we will review it for you.

    Regards
    ExcelDemy

  • Hello NadineB,

    You are most welcome. Thanks a lot for your appreciation.

    Regards
    ExcelDemy

  • Hello Valen,

    Can you mention the URL, please? In our site all URL’s of this article are working perfectly.

    Regards
    ExcelDemy

  • Hello Dianne Waginauru,

    You are most welcome and thanks for your appreciation. All the very best for you project management journey.

    Regards
    ExcelDemy

  • Hello Gwenn,

    Thanks for your suggestion. We deeply appreciate the solution provided in the comments. Thank you for your helpful contribution!

    Regards
    ExcelDemy

  • Hello Ameer Ahmed,

    We don’t have any dataset you asked for and your question is not clear enough. Our datasets are given in the article.

    Regards
    ExcelDemy

  • Hello Gaurav,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Joellah,

    Thanks for your appreciation, it means a lot to us. We are trying our best to provide Excellent articles. Keep reading to boost your knowledge.

    Regards
    ExcelDemy

  • Hello Leonardo Lagos,

    You can use the HYPERLINK function in Excel 360 online version.
    Insert the following formula in your formula bar: =HYPERLINK(“#’SheetName’!A1”, “Link Text”)

    But unfortunately, you can’t directly use the context menu to add a hyperlink to another sheet in Excel Online. You’ll need to use the formula approach described above.

    Regards
    ExcelDemy

  • Hello Shaul Bel,

    Thanks for your appreciation. You’re welcome and I’m really glad to hear that the SUBTOTAL formula worked well for you in achieving the desired results. If you have any more questions or need further assistance with formulas or anything else, feel free to ask!

    Regards
    ExcelDemy

  • Hello Michelle G,

    Thank you for reaching out to us. We request your attention to the above article to get your desired template on bonds purchased at a discount using the effective interest rate method.

    Your requested update is incorporated into our template. Please refer to the sheet named ‘Effective Interest (Discount)’ for the template on bonds purchased at a discount using the effective interest rate method. Click on the three dots (…) if you don’t find the template in your downloaded file.

    bond purchase sheet

    Regards
    ExcelDemy

  • Hello Martin,

    Excel for Mac does not offer right-clicking option like Excel for Windows.
    You can double click on the macro button or you can try the control click.
    A common workaround is to delete the existing button and create a new one, typing the correct text when you first insert it. While this might seem inefficient but you can “edit” the button text.

    Regards
    ExcelDemy

  • Hello ScubaCamper,

    Thanks for your appreciation. You are getting the error with asterisk because your formula attempts to multiply by 1 (*1) directly within the MID function’s parameters, that is not correct. You need to apply the multiplication outside of the MID function but within the array operation.

    Please try this formula:
    =TEXTJOIN(“”, TRUE, IFERROR(MID(B5, ROW(INDIRECT(“1:” & LEN(B5))), 1)* 1, “”))

    In your formula style the formula would be: textjoin(“”,true,iferror((mid([cellref],row(indirect(“1:”&LEN([cellref]))),1)*1),””))

    The purpose of multiplying by 1 (*1) in Excel formulas often is to convert text numbers to actual numeric values.

    Regards
    ExcelDemy

  • Hello Dan,

    You can use this VBA code to delete sheet named “BETA”, from multiple Excel files which are in same folder.

    Sub DeleteSheetFromMultipleWorkbooks()
        Dim wsPath As String
        Dim wsName As String
        Dim wb As Workbook
        Dim filename As String
        Dim sheetFound As Boolean
        
        ' Folder path containing the Excel files (end with a backslash)
        wsPath = "C:\YourFolderPathHere\"
        
        ' Name of the worksheet you want to delete
        wsName = "BETA"
        
        ' File extension to look for (xlsx for modern Excel files, xls for older ones)
        filename = Dir(wsPath & "*.xlsx")
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        While filename <> ""
            Set wb = Workbooks.Open(wsPath & filename)
            sheetFound = False
            
            On Error Resume Next
            sheetFound = Not wb.Sheets(wsName) Is Nothing
            On Error GoTo 0
            
            If sheetFound Then
                Application.DisplayAlerts = False
                wb.Sheets(wsName).Delete
                Application.DisplayAlerts = True
                wb.Save
            End If
       
            wb.Close SaveChanges:=False
            filename = Dir() ' Get next file
        Wend
        
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        
        MsgBox "BETA Sheet is Removed"
    End Sub
    

    Regards
    ExcelDemy

  • Hello Michelle,

    You are most welcome. Your appreciation means a lot to us.

    Regards
    ExcelDemy

  • Hello Terry Wayne Sutfin,

    Thanks for your appreciation, it means a lot to us.

    Regards
    ExcelDemy

  • Hello Margit,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Jitendra Sahu,

    Thanks for your kind words. Our goal is to make Excel easy for everyone.

    Regards
    ExcelDemy

  • Hello Melissa,

    We are glad that our article solved your problem. You are most welcome. Thanks for your appreciation.

    Regards
    ExcelDemy

  • Hello Robert,

    You are most welcome. Thanks for your appreciation.

    Regards
    ExcelDemy

  • Hello Ayokanmi,

    Thanks for your appreciation. You are most welcome. Our paid courses are in Udemy not in WordPress. But we have 7500++ free tutorials and 340+ YouTube videos. You can explore these learning materials for free.

    Regards
    ExcelDemy

  • Hello Feven,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Brent Hamilton,

    Thank you for your feedback. We used Microsoft 365 at work. We apologize for any confusion caused. The reason the “Show Changes” feature only becomes available after saving the file on OneDrive for real-time collaboration and tracking of changes. If you save it on OneDrive, it allows Excel to track changes made by different users and enables the “Show Changes” feature to display those revisions. This integration with OneDrive enhances collaboration and ensures that users can effectively manage changes made to shared documents.

    Regards
    ExcelDemy

  • Hello David Rascati,

    You are most welcome. If you want you can share your code here so that we can debug it to find out the reason.

    Regards
    ExcelDemy

  • Hello Mervyn,

    Thanks for visiting our blog and sharing your questions. You wanted to clarify the difference between a Table and a Dynamic Table.

    Table and Dynamic table may look similar but they are not same.
    In the context of the article,
    Regular Table – is created manually by the user within Excel’s interface.
    Dynamic Table – is generated automatically using VBA code.

    When data is turned into a table, Excel automatically adds functions like sorting, filtering, and structured referencing. These tables are dynamic and adjust automatically when the data changes.

    But a Dynamic Table in the article’s context likely refers to a table created dynamically using VBA code from a range. It is able to automatically manage specific criteria or changes.

    Regards
    ExcelDemy

  • Hello Phil Hall,

    If you want the Excel Workbook of this article you will get it from Download Practice Workbook section.

    If you want the get the advanced exercises from “Get FREE Advanced Excel Exercises with Solutions!” button please fill your information correctly in the form. Next, you will get the exercise list in your given email. For your concern I tried it again it’s working perfectly.

    Exercise mail

    Regards
    ExcelDemy

  • Hello Vijay,

    Thanks for your appreciation.

    Regards
    ExcelDemy

  • Dear RB,

    You are most welcome.

    Regards
    ExcelDemy

  • Dear SKM,

    You are most welcome.

    Regards
    ExcelDemy

  • Dear Sudarshan Dhuru,

    You are most welcome. Thanks for your appreciation it means a lot to us.

    Regards
    ExcelDemy

  • Dear Xyand,

    You are most welcome. Thanks for appreciation. We updated the image of Method 6.

    Regards
    ExcelDemy

  • Hello Saeed Anwar,

    You can download the Cheat Sheet free of cost just by providing your valid email address. To get the files go to the “Download Excel Formulas Cheat Sheet PDF & Excel Files” section of this post and enter your email address. Then check your email immediately after to get the download links.

    Best Regards
    ExcelDemy

  • Hello Minzameera,

    Hope you are doing well. Here we calculated the overtime based on your criteria. We assumed that this time spans are for one person. If these are for different person you can modify the formula.

    Formula ta calculate working hours: =(IF(B2

    Formula

    Formula to calculate the total working hours: =C2+C3

    formula 2

    Formula to calculate the overtime, here we assumed the working hours are 8: =IF(D2>B6,D2-B6,”No Overtime”)

    overtime

    Regards
    ExcelDemy

  • Hi Paulinus Friday,

    You are most welcome. Data Files are available in the Excel Practice file section.

    Regards
    ExcelDemy

  • Hello ابو معاذ,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Dwbi,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Rao,

    We will try to upload it in our up coming videos.

    Regards
    ExcelDemy

  • Hello Jessica H,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Jennifer,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Federico Cofederi,

    To send Email without pressing “Send” you can use .Send property in your code instead of .Display.

    Option Explicit
    Sub Send_Email_Condition()
        Dim xSheet As Worksheet
        Dim mAddress As String, mSubject As String, eName As String
        Dim eRow As Long, x As Long
        Set xSheet = ThisWorkbook.Sheets("Conditions")
        With xSheet
            eRow = .Cells(.Rows.Count, 5).End(xlUp).Row
            For x = 5 To eRow
                If .Cells(x, 4) >= 1 And .Cells(x, 5) = "Obama" Then
                    mAddress = .Cells(x, 3)
                    mSubject = "Request For Payment"
                    eName = .Cells(x, 2)
                    Call Send_Email_With_Multiple_Condition(mAddress, mSubject, eName)
                End If
            Next x
        End With
    End Sub
    Sub Send_Email_With_Multiple_Condition(mAddress As String, mSubject As String, eName As String)
        Dim pApp As Object
        Dim pMail As Object
        Set pApp = CreateObject("Outlook.Application")
        Set pMail = pApp.CreateItem(0)
        With pMail
            .To = mAddress
            .CC = ""
            .BCC = ""
            .Subject = mSubject
            .Body = "Mr./Mrs. " & eName & ", Please pay the due amount within the next week." _
            & vbNewLine & "The exact amount is attached with this email."
            .Attachments.Add ActiveWorkbook.FullName 'Send The File via Email
            .Send 
        End With
        Set pMail = Nothing
        Set pApp = Nothing
    End Sub
    

    Regards
    ExcelDemy

  • Hello Obinna,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Prakash,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Salomé Magerstein,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Kristi,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Muhammad Ismail Jumarang,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Lory,

    You can use the following formula: =IFERROR(INDEX(‘Round 1′!$B$3:$AJ$25,MATCH(A5,’Round 1’!$B$3:$B$25, 0),34),””)
    It will leave a cell blank if no matches are found.

    Regards
    ExcelDemy

  • Hello Kndn,

    The Measuring unit of the result is “Mile”.

  • Hello,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello,

    You are welcome.

    Regards
    ExcelDemy

  • Dear Icarus Builders,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Vini,

    Updated the code spilt data into multiple Excel file.

    Sub Split_Data_Multiple_Files()
        Dim L As Long
        Dim DS As Worksheet
        Dim VCL As Integer
        Dim XCL As Long
        Dim MARY As Variant
        Dim title As String
        Dim titlerow As Integer
        Dim newWorkbook As Workbook
        Dim newSheet As Worksheet
        Dim filePath As String
        
        Application.ScreenUpdating = False
        
        VCL = Application.InputBox(prompt:="Which column would you like to filter by?", title:="Filter column", Type:=1)
        Set DS = ActiveSheet
        L = DS.Cells(DS.Rows.Count, VCL).End(xlUp).Row
        title = "A1"
        titlerow = DS.Range(title).Cells(1).Row
        XCL = DS.Columns.Count
        DS.Cells(3, XCL) = "Unique"
        
        For X = 2 To L
            On Error Resume Next
            If DS.Cells(X, VCL) <> "" And Application.WorksheetFunction.Match(DS.Cells(X, VCL), DS.Columns(XCL), 0) = 0 Then
                DS.Cells(DS.Rows.Count, XCL).End(xlUp).Offset(1) = DS.Cells(X, VCL)
            End If
        Next
        
        MARY = Application.WorksheetFunction.Transpose(DS.Columns(XCL).SpecialCells(xlCellTypeConstants))
        DS.Columns(XCL).Clear
        
        For X = 2 To UBound(MARY)
            DS.Range(title).AutoFilter field:=VCL, Criteria1:=MARY(X) & ""
            Set New_Workbook = Workbooks.Add
            Set New_Sheet = newWorkbook.Sheets(1)
            DS.Range("A" & titlerow & ":A" & L).EntireRow.Copy New_Sheet.Range("A4")
            DS.AutoFilterMode = False
            New_Sheet.Columns.AutoFit
            filePath = ThisWorkbook.Path & "\" & MARY(X) & ".xlsx"
            New_Workbook.SaveAs filePath
            New_Workbook.Close False
        Next
        
        DS.Activate
        Application.ScreenUpdating = True
    End Sub
    

    Regards
    ExcelDemy

  • Hello Syed Mohisn Ahmed,

    Updated the code in the marked section. Where I replaced “Rupees” text from condition.

    Updated Code

    You can use the following code.

    Function word(SNum As String)
    'Declare the Variables
    Dim zDPInt As Integer
    Dim zArrPlace As Variant
    Dim zRStr_Paisas As String
    Dim zNumStr As String
    Dim zP As Integer
    Dim zTemp As String
    Dim zStrTemp As String
    Dim zRStr As String
    Dim zBp As Integer
    zArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
    On Error Resume Next
    If SNum = "" Then
      word = ""
      Exit Function
    End If
    zNumStr = Trim(Str(SNum))
    If zNumStr = "" Then
      word = ""
      Exit Function
    End If
     
    zRStr = ""
    zBp = 0
    If (zNumStr > 999999999.99) Then
        word = "Digit excced Maximum limit"
        Exit Function
    End If
    zDPInt = InStr(zNumStr, ".")
    If zDPInt > 0 Then
        If (Len(zNumStr) - zDPInt) = 1 Then
           zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
        ElseIf (Len(xNumStr) - xDPInt) > 1 Then
           zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
        End If
            zNumStr = Trim(Left(zNumStr, zDPInt - 1))
        End If
        zP = 1
        Do While zNumStr <> ""
            If (zP >= 2) Then
                zTemp = Right(zNumStr, 2)
            Else
                If (Len(zNumStr) = 2) Then
                    zTemp = Right(zNumStr, 2)
                ElseIf (Len(zNumStr) = 1) Then
                    zTemp = Right(zNumStr, 1)
                Else
                    zTemp = Right(zNumStr, 3)
                End If
            End If
            zStrTemp = ""
            If Val(zTemp) > 99 Then
                zStrTemp = word_GetH(Right(zTemp, 3), zBp)
                If Right(Trim(xStrTemp), 3) <> "Lac" Then
                zBp = zBp + 1
                End If
            ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
                zStrTemp = word_GetT(Right(zTemp, 2))
            ElseIf Val(zTemp) < 10 Then
                zStrTemp = word_GetD(Right(zTemp, 2))
            End If
            If zStrTemp <> "" Then
                zRStr = zStrTemp & zArrPlace(zP) & zRStr
            End If
            If zP = 2 Then
                If Len(zNumStr) = 1 Then
                    zNumStr = ""
                Else
                    zNumStr = Left(zNumStr, Len(zNumStr) - 2)
                End If
           ElseIf zP = 3 Then
                If Len(zNumStr) >= 3 Then
                     zNumStr = Left(zNumStr, Len(zNumStr) - 2)
                Else
                    zNumStr = ""
                End If
            ElseIf zP = 4 Then
              zNumStr = ""
        Else
            If Len(zNumStr) <= 2 Then
            zNumStr = ""
        Else
            zNumStr = Left(zNumStr, Len(zNumStr) - 3)
            End If
        End If
            zP = zP + 1
    Loop
        If zRStr = "" Then
           zRStr = "No Rupees"
        Else
           zRStr = " Rupees " & zRStr
        End If
        If zRStr_Paisas <> "" Then
           zRStr_Paisas = " and " & zRStr_Paisas & " Paisas"
        End If
        word = zRStr & zRStr_Paisas & " Only"
        End Function
    Function word_GetH(zStrH As String, zBp As Integer)
    Dim zRStr As String
    If Val(zStrH) < 1 Then
        word_GetH = ""
        Exit Function
        'Converts a Number from 100 to 999 into Word
    Else
       zStrH = Right("000" & zStrH, 3)
       If Mid(zStrH, 1, 1) <> "0" Then
            If (zBp > 0) Then
             zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Lac "
            Else
             zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
            End If
        End If
        If Mid(zStrH, 2, 1) <> "0" Then
            zRStr = zRStr & word_GetT(Mid(zStrH, 2))
        Else
            zRStr = zRStr & word_GetD(Mid(zStrH, 3))
        End If
    End If
        word_GetH = zRStr
    End Function
    Function word_GetT(zTStr As String)
        Dim zTArr1 As Variant
        Dim zTArr2 As Variant
        Dim zRStr As String
        'Converts a Number from 10 to 19 into Word
        
        zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
        'Converts a Number from 20 to 99 into Word
        
        zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
        Result = ""
        If Val(Left(zTStr, 1)) = 1 Then
            zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
        Else
            If Val(Left(zTStr, 1)) > 0 Then
                zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
            End If
            zRStr = zRStr & word_GetD(Right(zTStr, 1))
        End If
          word_GetT = zRStr
    End Function
    Function word_GetD(zDStr As String)
    Dim zArr_1() As Variant
    'Converts a Number from 1 to 9 into Word
    
        zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")
        If Val(zDStr) > 0 Then
            word_GetD = zArr_1(Val(zDStr) - 1)
        Else
            word_GetD = ""
        End If
    End Function
    

    Regards
    ExcelDemy

  • Hello,

    Thanks for reaching out to us.

    First, remember that rules and instructions for interest calculation after garnishment order vary among countries and states. Next, post-judgment interest accrues only on the unpaid balance of the judgment.

    However, to calculate interest if the judgment was turned into a garnishment, you can follow the steps below.
    1. Take your judgment amount and deduct the amount of garnishment.
    2. Multiply it by your post-judgment rate (%).
    3. Take the total and divide it by 365 (the number of days in a year).
    4. With the amount of post-judgment interest per day (in step 3), multiply it by the number of days from your date of judgment to the date you file your execution.

    Simply, use the following formula to calculate interest if the judgment was turned into a garnishment:
    Interest after Garnishment = (Judgment Amount – Garnished Amount) ( Interest Rate / 365) Interim Days

    Thanks again for your thoughtful comments, especially the points you made about garnishment. It adds valuable perspective to our discussion.

    Regards,
    Abdullah Al Masud,
    ExcelDemy Team

  • Hello Muhammad Asif,

    You are most welcome.

    Regards
    ExcelDemy

  • Hello Joseph Jabatie,

    You are welcome. You can get the notes or Excel workbook from the Download section.

    Regards
    ExcelDemy

  • Hello Mohammed.

    Thanks for your appreciation. Our content is completely free for our users learning but don’t use it for your business purpose without proper agreement.

    Regards
    ExcelDemy