Shamima Sultana

About author

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 3+ years. She has written 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
Creating Interactive KPI Scorecards in Google Sheets

In this article, we will show how to create interactive KPI scorecards in Google Sheets.

0
7 Excel Dashboard Design Principles That Make Data Beautiful

In this article, we will explore 7 Excel dashboard design principles that make data beautiful.

0
Real-time Currency Conversion in Google Sheets

In this article, we will create a real-time currency conversion system in Google Sheets.

0
How to Create Interactive Dashboards with Form Controls in Excel

In this article, we will show how to create interactive dashboards with form controls in Excel.

0
8 Power Query Tricks That Will Save You Hours of Work

In this article, we will share 8 Power Query tricks that will save you hours of work.

0
Building a Task Duration Estimator in Excel

By using Excel features and formulas you can calculate the estimated time required to complete tasks based on complexity, and available resources.

0
Using Claude’s Data Connectors to Analyze Your Google Sheets

In this article, we will show how you can use Claude to analyze your Google Sheets.

0
10 Excel Formulas for Power Users

In this article, we will list and explore Excel formulas for power users.

0
Excel Maps: Visualizing Geographic Data

In this article, we will show how to visualize geographic data using Excel Maps.

0
8 Excel Automation Ideas for Power Users

In this article, we will explore 8 Excel automation ideas for power users to enhance their productivity and workflow.

0
Creating Dynamic QR Codes in Google Sheets

You can create dynamic QR codes in Google Sheets that update automatically based on the changes in the spreadsheet.

0
Creating Burndown Charts in Google Sheets

In this article, we will show how to create burndown charts in Google Sheets.

0
How to Build Custom Add-ins for Enhanced Functionality

In this article, we will build custom add-ins for enhanced functionality.

0
Data Validation Techniques for Complex Business Rules in Excel

In this article, we will explore advanced data validation techniques for complex business rules in Excel with practical examples.

0
How to Create Your Own Reusable Functions with Advanced LAMBDA Functions in Excel

In this article, we will show how to create your own reusable functions with advanced LAMBDA functions in Excel for complex tasks.

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,

    You are most welcome. Thanks for your feedback and appreciation. Glad to hear that our post is helpful to you.

    Regards
    ExcelDemy

  3. Hello Kassamba Youssoufou Diaby,

    Thanks for your feedack and appreciation. We are attaching the Excel workbook. Download it from here:
    Dataset to Create Interactive Dashboards with Form Controls.xlsx

    Regards
    ExcelDemy

  4. Hello Ahmed,

    You are most welcome. Thanks for your feedback ad appreciation. Glad to hear that our article is beneficial to you. Keep Excel-ing with ExcelDemy!

    Regards
    ExcelDemy

  5. Hello Viv C,

    Thank you for your feedback! Unfortunately, some Excel Form Controls and interactive features may not work as expected in the online version of Excel through OneDrive. A possible workaround is to use Excel Desktop for full functionality and then sync the file to OneDrive for sharing.

    Alternatively, you can try using Microsoft Forms linked to an Excel file in OneDrive to collect responses dynamically.

    Regards
    ExcelDemy

  6. Hello Angel Mendivelso,

    Thank you so much! I’m glad you liked the dashboard. In Excel 2016, you can create a similar interactive dashboard using Form Controls like Combo Boxes, Check Boxes, and Option Buttons.
    Some advanced functions might not be available, but you can achieve similar results with Pivot Tables and Slicers.

    Regards
    ExcelDemy

  7. Hello Keith,

    Yes, we can give you the exact dataset to download. We are attaching the Excel workbook. Download it from here:
    Dataset to Create Interactive Dashboards with Form Controls.xlsx

    Regards
    ExcelDemy

  8. Hello Thomas Nhokuoth Payoi,

    You are most welcome. Thanks for your feedback and appreciation. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  9. Hello Tom,

    You can use a combination of Excel functions to achieve this. Here’s a formula approach using FILTER and SEARCH:

    Assume your list of terms is in cells A2:A7 and your text line is in B2 (“123 Blue Ridge Street”).

    Formula:
    =FILTER(A2:A7, ISNUMBER(SEARCH(A2:A7, B2)))
    This will return “Blue” if it’s present in the text line.

    Explanation:
    1. SEARCH(A2:A7, B2) looks for each term in the text and returns a number if it finds a match.
    2. ISNUMBER() checks if the result is a number, indicating a match.
    3. FILTER() returns the matching term(s).

    Regards
    ExcelDemy

  10. Hello Wade Kellogg,

    Thank you for your feedback and appreciation. It is kind of you! I’m glad you found the COUNTIF function helpful for categorizing income and expenses from your bank transactions. It’s fantastic to hear that it saved you time. Happy Excel-ing!

    Regards
    ExcelDemy

  11. Hello P Vinayakam,

    Thank you for reaching out! Extracting and organizing financial statements from multiple annual reports using Power BI is a valuable task for equity analysts. While we don’t specifically offer a structured Power BI course, I can share some key steps to help you get started:

    Load PDF Files: Use Get Data > PDF in Power BI to load all your annual reports from the folder.
    Data Cleanup: Use Transform Data in Power Query to extract the Profit & Loss Account, Balance Sheet, and Cash Flow tables.
    Sort Data: Apply sorting functions to organize the data in ascending order by fiscal year.
    Create Visuals and Reports: Build comprehensive tables or dashboards for better insights.

    Regards
    ExcelDemy

  12. Hello P Vinayakam,

    Thanks for your question! Handling large PDF files in Power BI and Power Query can be tricky, especially with lengthy reports like the ones you mentioned. When Power BI detects multiple tables and pages instead of a single combined dataset, try the following steps:

    Use the Navigator Pane Efficiently: Select only the required tables from each PDF. If there’s a pattern in table names (like Table001, Table002), select them carefully.

    Combine Tables Dynamically: Once you load the tables, go to Home >> Append Queries in Power Query to combine the tables. This will help merge the data from multiple pages or tables.

    Standardize Table Structures: Inconsistent table structures often prevent proper merging. Use Remove Columns, Rename Columns, and Align Headers to ensure the tables have a uniform structure.

    Filter Tables: If you notice irrelevant tables (like Table003, Page001), apply filters in Power Query to target only necessary data.

    Regards
    ExcelDemy

  13. Hello Tu,

    After inserting your name and Email address click on the Download button. Then Excel file will be sent to your inbox. Don’t forget to check your spam folder.

    Regards
    ExcelDemy

  14. Hello Bernhard K.,

    Thank you for your comment! Method 2, which uses the built-in Stocks data type, is available in Excel 2019 and later versions. However, please note that some features may be limited compared to Excel 365, which receives regular updates.

    Regards
    ExcelDemy

  15. Hello Maxwill Roberts,

    Thank you for your comment. Upon reviewing the article and dataset, we did not find any mention of that name. We usually use common names as a sample data set. If there is any specific section you’re referring to, please let us know so we can address it properly.

    Regards
    ExcelDemy

  16. Hello James,

    You are most welcome. Thank you for the wonderful feedback! We’re thrilled to hear that it worked so well for you and provided such impressive accuracy. Happy analyzing!

    Regards
    ExcelDemy

  17. Hello Carmen Isabel Castillo,

    Good day! To prevent Excel from converting PRO numbers to scientific notation and ensure they remain intact in CSV files, follow these steps:

    1. Format the PRO number column as Text in Excel before entering or importing data.
    2. Save the file as CSV UTF-8 format to preserve text formatting.
    3. Open the CSV file in a text editor (like Notepad) instead of Excel to verify the correct format.
    This prevents rounding and scientific notation issues.

    Regards
    ExcelDemy

  18. Hello Roland Crespo,

    You can address the issue by ensuring the links are updated without causing the lock. Try the following suggestions:

    Update Links Individually: Instead of using ActiveWorkbook.RefreshAll, use ActiveWorkbook.UpdateLink Name:=LinkSources, Type:=xlExcelLinks to update each link explicitly. This can help avoid conflicts with locked resources.

    Background Refresh: Ensure that the “Enable background refresh” option for queries is unchecked. Go to Data >> from Queries & Connections >> select Properties then disable background refresh.

    Disable Workbook Sharing: Workbook sharing can cause locking issues during link updates. If possible, unshare the workbook.

    Force Calculation: After updating the links, trigger a recalculation with Application.CalculateFull.

    Regards
    ExcelDemy

  19. Hello Mahmoud Mady,

    Sorry to hear you’re facing this issue. Since you’ve already tried the suggested solutions without success, I recommend checking for these additional factors:
    1. Ensure your mouse drivers are up to date.
    2. Test Excel in Safe Mode (Ctrl + click Excel).
    3. Verify any third-party add-ins are disabled.

    If the issue persists, consider reinstalling Excel.

    Regards
    ExcelDemy

  20. Hello Jessica,

    It sounds like you’re facing an issue with VLOOKUP when adding new products. If the product counts aren’t updating, the formula may not be referencing dynamic ranges. Try using structured tables (Ctrl + T) to automatically adjust ranges as new rows are added. Additionally, consider INDEX-MATCH or XLOOKUP (if available) for more flexibility.
    1. Convert to Table: Select your data, press Ctrl + T, and check My table has headers. This will help ranges adjust automatically.
    2. Check VLOOKUP Formula: Ensure it references the table columns instead of fixed ranges.
    3. Dynamic Range: Use structured references like =VLOOKUP([@[Product]],Table1,2,FALSE) instead of static cell ranges.
    4. Use INDEX-MATCH: For more flexibility, switch to INDEX and MATCH functions.

    Regards
    ExcelDemy

  21. Hello Alfred,

    Thank you for your suggestion! I understand how using checkboxes in Excel can seem tricky at times. We have a helpful video demonstration that you might find useful: How to Use Developer Checkboxes in Excel.
    It covers simple tasks like tracking attendance. Feel free to check it out and let us know if you have more questions!

    Regards
    ExcelDemy

  22. Hello Tomas Limeme,

    Reconciling two data sets with repeated entries can be challenging. Here’s a method you can try:

    1. Create Unique Identifiers: Combine multiple columns to create a unique reference for each transaction. Use the CONCAT or TEXTJOIN functions with key fields such as amount, date, and transaction type.
    Formula:
    =TEXTJOIN(“-“, TRUE, A2, B2, C2)
    This will help differentiate transactions that share the same amount but are different.

    2. Assign Serial Numbers: Use helper columns to check for duplicates and assign a serial number for matching.
    Formula:
    =IF(COUNTIF($D$2:D2, D2) = 1, MAX($E$1:E1) + 1, “”)
    Replace $D$2:D2 with the range of your unique reference column.

    3. Match Transactions: Use VLOOKUP or INDEX-MATCH to compare the serial numbers between the two datasets for reconciliation.
    Formula:
    =IFERROR(VLOOKUP(E2, $H$2:$I$500, 2, FALSE), “No Match”)

    This approach should help manage repeated entries and streamline the reconciliation process. Let me know if you need further clarification!

    Regards
    ExcelDemy

  23. Hello Marc,

    You are most welcome. Thanks for your feedback and appreciation. Glad to hear that our clear explanation is helpful to you. We always try to make Excel content accessible to all.

    Regards
    ExcelDemy

  24. Hello Jeremy,

    Thank you for your kind words! I’m always happy to help. Since you’re not a programmer, don’t worry—I’ll guide you through it step by step.

    To share your current code, here’s what you can do:

    Format it directly in the comment: Add backticks (`) before and after the code to format it neatly.
    Example:
    Your VBA code here
    Use a code-sharing platform: Upload the code to a site like Pastebin or GitHub and share the link here.

    Updated the VBA code that addresses all three user concerns from the first comment:

    
    Sub ExtractMergeDataFromMultipleFiles()
        Dim FilePath As String, FileName As String
        Dim wBook As Workbook, wsSource As Worksheet, wsDest As Worksheet
        Dim currentRow As Long, row As Long, col As Long
        Dim CompanyName As String
        
        ' Folder containing the files
        FilePath = "D:\saledata\"
        
        ' Set destination worksheet
        Set wsDest = ThisWorkbook.Sheets("Sheet1")
        currentRow = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row + 1
        
        ' Get the first file in the folder
        FileName = Dir(FilePath & "*.xlsx")
        
        ' Loop through all Excel files in the folder
        Do While FileName <> ""
            Set wBook = Workbooks.Open(FilePath & FileName)
            Set wsSource = wBook.Sheets("Sheet1")
            
            ' Extract company name from filename
            CompanyName = Mid(FileName, InStr(FileName, "–") + 2, InStrRev(FileName, ".") - InStr(FileName, "–") - 2)
            
            row = 2 ' Assuming row 1 is the header
            Do While Application.WorksheetFunction.CountA(wsSource.Rows(row)) > 0
                ' Copy data row to destination worksheet
                For col = 1 To 5
                    wsDest.Cells(currentRow, col).Value = wsSource.Cells(row, col).Value
                Next col
                ' Add company name in the next available column (Column F in this example)
                wsDest.Cells(currentRow, 6).Value = CompanyName
                currentRow = currentRow + 1
                row = row + 1
            Loop
            
            wBook.Close False
            FileName = Dir() ' Get next file
        Loop
    
        MsgBox "Data extraction and consolidation complete!"
    End Sub

    Key Changes:
    Extract Company Name: Uses Mid and InStr functions to extract the company name from the filename.
    Copy Multiple Rows: Loops through all rows until an entirely blank row is encountered.
    Handle Additional Files: Automatically processes all .xlsx files in the specified folder without needing manual code changes.

    Regards
    ExcelDemy

  25. Hello Jeremy,

    Sorry to hear the issue. It’s possible that the system flagged your comment due to the code content, as some platforms automatically block or filter comments containing extensive code for security reasons. I recommend removing or formatting sensitive parts and sharing a summary instead. Alternatively, if you still need help with the code, consider uploading it to a code-sharing platform and sharing the link here.

    Let me know if you need further guidance!

    Regards
    ExcelDemy

  26. Hello Cosmas Gabu,

    You’re most welcome! I’m so glad you found the content helpful. Keep exploring website, there are plenty of tutorials and guides to help you with your learning journey. If you have any questions or topics you’d like to learn more about, feel free to ask. Much love and happy learning!
    You will get all article category and list here: Learn Excel

    Regards
    ExcelDemy

  27. Hello Harry,

    Thank you for your comment! Here’s how you can achieve both requirements in Excel:

    1. Find Newly Added Names in Column B (Not in Column A)
    Use the following formula in a new column:
    =IF(COUNTIF(A:A, B1) = 0, B1, “”)

    This will display only the newly added names from Column B.

    2. Find Removed Names in Column A (Not in Column B)
    Use this formula in a new column:
    =IF(COUNTIF(B:B, A1) = 0, A1, “”)

    This will list the names in Column A that are missing in Column B.

    Regards
    ExcelDemy

  28. Hello Dmullins,

    Excel doesn’t have built-in settings to control where automatic text wrapping breaks. However, you can use manual line breaks by pressing Alt + Enter at desired points within the cell. This approach ensures consistent wrapping without breaking important numerical or formatted text. If your issue involves dashes breaking negative numbers, removing unnecessary spaces or formatting the text as a number may help prevent unwanted line breaks.

    Regards
    ExcelDemy

  29. Hello Jeremy,

    Thank you for your kind words! I’m glad Method 2 was helpful. Let’s address your questions one by one:

    Extracting Company Names: You can modify the code to extract the company name from the file name. Assuming the name always comes after ” – ” in the filename, you can use VBA string functions to parse and store the company name when reading each file.

    Copying Multiple Rows: Update the code to loop through all rows with data. You can use Do While or For loops to check if the row is entirely blank and copy only rows with data. Here’s a simple way:

    
    Do While Application.WorksheetFunction.CountA(CurrentRowRange) > 0
        'Copy row logic here
        Set CurrentRowRange = CurrentRowRange.Offset(1, 0)
    Loop

    This will stop once it encounters an entirely blank row.

    Handling Additional Files: The code already loops through all files in the folder. As long as you place new files in the same folder, you shouldn’t need to change the code. Just re-run the macro to include the new files.

    Regards
    ExcelDemy

  30. Hello Aravind Aji,

    For a large dataset on predictive modeling, you can explore platforms like Kaggle, UCI Machine Learning Repository, or government open data portals. These platforms provide datasets for various fields, including finance, healthcare, and more. If you’re working in Excel, you can also use add-ins like Power Query to import large datasets directly from online sources.

    Regards
    ExcelDemy

  31. Hello Vivek,

    Of course! Let me know how I can assist you, or feel free to share more details about what you’d like to discuss. You can use ExcelDemy Forum to post your query in detail.

    Regards
    ExcelDemy

  32. Hello Kimber,

    Great question! Method 2 doesn’t require a loop because it checks if the FilterMode property is True, indicating that at least one column is filtered.
    However, if you want to count the specific number of active filters, then yes, you’d need a loop to iterate through the columns and check which ones have filters applied. Let me know if you’d like an example of how to do that!

    Regards
    ExcelDemy

  33. Hello Jason,

    You are most welcome. Glad to hear that our article helped you to solve your problem. Thanks for your valuable feedback.

    Regards
    ExcelDemy

  34. Hello Abdul Quadir Ali,

    You are most welcome and thank you so much for your kind words! I’m so glad you found the explanation helpful and easy to understand. Your feedback truly means a lot!

    Regards
    ExcelDemy

  35. Hello Mariam Mohsen,

    Thank you for your kind words! I’m glad the guide was helpful to you. To ensure the VBA code isn’t deleted after closing Excel, you need to save the workbook as a Macro-Enabled Workbook (.xlsm). Here’s how:
    1. Click File > Save As and select Excel Macro-Enabled Workbook (.xlsm) from the file type dropdown.
    2. Save the file, and the code will remain intact when you reopen it.

    For sending automatic daily reminders without manual intervention:
    1. You can use the Workbook_Open event in VBA to trigger the reminder code automatically when the file is opened.
    2. Alternatively, you can schedule the macro to run daily using Task Scheduler in Windows, linking it to the workbook.

    Lastly, to avoid manual column inputs, you could enhance the VBA code to dynamically pick up data based on your predefined conditions. Let me know if you’d like a step-by-step explanation for any of these solutions!

    Regards
    ExcelDemy

  36. Hello Kurt Porter,

    You are most welcome. Glad to hear that the issue is solved. The solution sounds efficient.

    Regards
    ExcelDemy

  37. Hello Kurt Porter,

    Thank you for your feedback! I’m glad the tutorial was helpful! Regarding your issue, it seems the conditional formatting rule needs to account for the additional columns and rows introduced for events.

    To fix this, try updating the conditional formatting rule to include the logic for checking whether the events correspond to the current month. For example, you can use a formula like:

    =MONTH(cell_with_date)<>selected_month

    where cell_with_date is the date in your calendar and selected_month is the cell or variable representing the chosen month. Apply this rule to the entire range, including your event columns.

    If you need more specific guidance, feel free to share your current formula or setup, and I’d be happy to help further!

    Regards
    ExcelDemy

  38. Hello Faz,

    Thank you for pointing out the discrepancy in month 17’s total payments in the workbook provided. Let’s address this step by step to identify and resolve the issue.

    Possible Causes and Fixes:
    Formula for Total Payments:
    1. Double-check the formula calculating the total payment for month 17. It should accurately sum the minimum payments and any additional payments.
    2. Ensure there are no additional values being added inadvertently to the formula.

    Additional Payment Allocation:
    1. The additional payment ($500) might be allocated in a way that results in overpayment for one or more debts.
    2. Verify that the additional payment follows the logic of the debt snowball method, where payments are prioritized to the smallest remaining balance debt first, and once that is cleared, it moves to the next smallest debt.

    Interest Accumulation: Check if interest calculations for month 17 are accurate. Interest may increase the total amount due, especially if the remaining balances weren’t updated properly from previous months.

    Rounding Errors:
    Excel can sometimes introduce small discrepancies due to rounding. Review how payments and interest are rounded in the formulas to see if this contributes to the difference.

    Cell References:Ensure the formulas for month 17 are correctly referencing the cells for minimum payments, remaining balances, and additional payment allocation. If a formula accidentally pulls values from another month or an incorrect cell, it can lead to inflated totals.

    Steps to Adjust:
    1. Go to the “Payment” and “Remaining Balance” columns for month 17. Review each formula to ensure it aligns with the structure provided in the tutorial.
    2. Confirm that the additional payment is being fully utilized and distributed only to eligible debts.
    3. Use the Formula Evaluation feature in Excel (Formulas tab > Evaluate Formula) to step through your calculations for month 17 and see where the discrepancy arises.

  39. Hello Faz,

    Thank you for bringing this to our attention! The discrepancy in month 17’s total payments may arise due to how the payment formulas are structured or how the additional payment is being distributed across the debts.

    To resolve this, please check the following:
    1. Ensure the formulas in the Payment and Remaining Balance columns are correctly referencing the respective cells.
    2. Verify that the additional payment allocation follows the debt snowball method logic as explained in the tutorial.
    3. Recalculate and ensure no additional interest or overpayment is being applied inadvertently.

    Regards
    ExcelDemy

  40. Hello Priscianel Ramoga,

    Thank you for your kind words and thoughtful question! The Opening Cash Balance for each succeeding year is typically carried over from the Closing Cash Balance of the previous year.
    In Excel, this can be achieved by linking the cell of the prior year’s closing balance to the opening balance of the next year.
    For example, if the closing cash balance for Year 1 is in cell B10, the opening cash balance for Year 2 would reference it like =B10. This ensures the cash flow remains consistent across the years.

    Regards
    ExcelDemy

  41. Hello Manjunath,

    To calculate the number of working days between two dates, broken down by month, you can use the NETWORKDAYS.INTL function in combination with helper formulas to split the date range. Here’s how you can do it:

    Input your start and end dates:
    Start Date: 1-Dec-24
    End Date: 14-Jan-25

    Break the range by month:

    For December 2024: Use NETWORKDAYS.INTL(Start Date, EOMONTH(Start Date, 0), [weekend], [holidays]).
    Example:
    =NETWORKDAYS.INTL(DATE(2024,12,1), DATE(2024,12,31), 1)
    Adjust [weekend] to specify your weekend days and [holidays] if necessary.

    For January 2025: Use NETWORKDAYS.INTL(Start Date of January, End Date, [weekend], [holidays]).
    Example:
    =NETWORKDAYS.INTL(DATE(2025,1,1), DATE(2025,1,14), 1)

    Combine the results: The output will be split by month, as you need.

    For your input:
    Working days in Dec 2024: 18
    Working days in Jan 2025: 9

    Regards
    ExcelDemy

  42. Hello Jess Fraser,

    You are most welcome. Thanks for your feedback and appreciation. Glad to hear that article’s examples are helpful to you. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  43. Hello SS,

    Tanks for your feedback and appreciation. To exclude weekends in Method 2 of generating random dates, you can combine the WORKDAY function with RANDBETWEEN. Here’s an example:

    =WORKDAY(RANDBETWEEN(start_date, end_date)-1, 1)
    Replace start_date and end_date with your desired range.

    This ensures that the random date falls on a weekday. Adjust the range as needed for different date intervals.

    Regards
    ExcelDemy

  44. Hello Jessi,

    It sounds like the issue is related to cell references being linked across months. This typically happens when the formula or cell is set to reference the same location across different months.
    To fix it, check if absolute references (e.g., $I$9) are being used, and switch them to relative references (e.g., I9) for each month’s section. This way, each month will have independent entries.

    Regards
    ExcelDemy

  45. Hello Omarnader,

    It seems the issue might be due to the presence of tables or XML maps in your workbook, even if you don’t see them.
    Try removing any external connections, linked data, or unused named ranges. Another potential issue could be the file format; ensure it’s saved as an .xlsx file.
    Additionally, check if your company’s shared drive has specific permissions that might restrict sharing.

    Regards
    ExcelDemy

  46. Hello James,

    Thank you for the thoughtful feedback! I’m glad Solution #3 helped, and I completely agree with your perspective. You’re absolutely right that treating fractions as numbers is beneficial for further calculations.
    Your suggestion to move Solution #3 to #1 makes a lot of sense, especially when you want to ensure the fractions are recognized as numbers while keeping the correct format.
    It’s always great to discover a new, more efficient way to handle things in Excel, and your input adds a valuable point. Thanks again for sharing your insights!

    Regards
    ExcelDemy

  47. Hello Daniel Hudson,

    Thank you for your kind words and for turning to our page for Excel solutions!

    The method you described is quite common for selecting and copying content in Excel. Yes, the option to use the cursor to select the entire sheet, copy it, and paste using “Paste Special” is still available in Excel.

    To retrieve it:
    1. Select the entire sheet using the Select All button (the square between row numbers and column letters) or press Ctrl + A.
    2. Copy using Ctrl + C.
    3. Navigate to the desired location and use Paste Special (right-click >> Paste Special or Ctrl + Alt + V) to choose your paste option.
    If you’re having trouble accessing these features, please ensure your Excel version is updated. Let us know if you need further assistance!

    Regards
    ExcelDemy

  48. Hello Exhell,

    You are most welcome. Thanks for your appreciation and feedback. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  49. Hello Bart,

    We appreciate your feedback and understand that Excel can sometimes feel challenging, especially when it doesn’t behave as expected. If you’re encountering specific issues, we’d be happy to help address them or provide workarounds.

    Excel continues to be a powerful tool for data analysis and problem-solving, and with the right tips and tricks, it can be incredibly efficient. Please feel free to share any specific frustrations or examples—let’s work together to make Excel more useful for your needs!

    Regards
    ExcelDemy

  50. Hello Jonas,

    Thank you for your feedback! The formula =MOD(ROW(),2) should work correctly in Excel to identify odd and even rows.

    If Excel isn’t executing it, there might be an issue with the syntax or regional settings on your system. For example, some regional settings require a semicolon (;) instead of a comma (,).

    Please try using =MOD(ROW();2) and let me know if that resolves the issue! If the problem persists, feel free to share more details about your Excel version and settings so we can assist further.

    Regards
    ExcelDemy

  51. Dear Mr. / Engineer Adel Shagan,

    Thank you so much for your kind words and thoughtful message! I’m delighted to hear that you find my Excel lessons helpful and easy to understand. Your encouragement means a lot and inspires me to continue sharing knowledge about Excel functions and features.

    Please feel free to reach out if you have any specific topics or questions you’d like me to cover in future lessons. I’m always here to help.

    Regards
    ExcelDemy

  52. Hello Marla,

    Welcome to ExcelDemy. Thank you so much for your kind words and feedback! We’re thrilled to hear that the guide has been helpful in brushing up your Excel skills.
    Yes, visuals can make all the difference, and we’re glad they worked well for you! As for Access, it’s a powerful tool, and diving back into it can definitely open up new possibilities. We’re here to help if you need guidance or resources to get started with Access too.

    Best of luck with your journey back into data management!

    Regards
    ExcelDemy

  53. Hello Premierdigitalmark,

    To calculate goods delivered on time in Excel, follow these steps:

    Prepare your data: Include columns for Estimated Delivery Date and Actual Delivery Date.
    For example, if column A has Order IDs, column B has Estimated Dates, and column C has Actual Dates, proceed to step 2.

    Calculate if each delivery is on time: In a new column (e.g., column D), enter the formula:
    =IF(C2<=B2,"On Time","Late")

    This formula checks if the actual delivery date (C2) is on or before the estimated date (B2) and labels the delivery as “On Time” or “Late.”
    Count on-time deliveries:

    Use the COUNTIF function to count the “On Time” deliveries:
    =COUNTIF(D2:D100,”On Time”)

    Replace D2:D100 with the actual range of your data.

    This will give you the total number of goods delivered on time. If you also want the percentage, divide this count by the total number of deliveries. Let me know if you need further assistance!

    Regards
    ExcelDemy

  54. Hello OF,

    Thank you for your feedback! The highlight function in Spreadsheet Compare is indeed designed for visual comparison within the tool itself. Unfortunately, the exported results workbook doesn’t retain these highlights since it’s primarily focused on listing the differences as raw data. You could manually apply conditional formatting or other highlight options in Excel after exporting the results to recreate the visual distinction. Let me know if you need help with that!

    Regards
    ExcelDemy

  55. Hello Lucas de Oliveira Alves,

    Thank you for your kind feedback! I’m glad to hear the VBA code is working well for you.

    Regarding the issue with images not displaying on mobile devices, it could be due to how the image is embedded or attached in the email. Many mobile email clients do not support embedded images and might block them by default. Here are a few suggestions to address this:

    Use an External Image Hosting Service: Instead of embedding the image directly in the email body, you can upload the image to a reliable hosting service and include its URL in the email body. This ensures the image is accessible regardless of the email client.

    Check Email Client Compatibility: Ensure the image is embedded using standard practices that are supported by most email clients. Sometimes, tweaking the HTML format in the email body can resolve compatibility issues.

    Use Base64 Encoding: If possible, encode the image in Base64 and include it in the email’s HTML body. While this increases email size, it often improves compatibility across devices.

    Add the Image as an Attachment: If none of the above solutions work, consider attaching the image instead of embedding it. This ensures users can view the image by opening the attachment.

    Regards
    ExcelDemy

  56. Hello Jiri,

    Thank you for your question! To modify the Userform Datepicker.xlsm for a week starting on Monday, you can adjust the code in the DatePicker module. Typically, the weekday start is determined by a property or function within the code. Here’s a general approach:

    1. Open the VBA editor (Alt + F11) and navigate to the code for the DatePicker UserForm.
    2. Look for code that initializes or calculates the days of the week.
    3. Update it to adjust the first day of the week to Monday instead of Sunday. For example, you can use Weekday(date, vbMonday) to specify Monday as the first day.
    4. Save and test your modifications.

    If you’re unfamiliar with VBA or need further assistance, feel free to share more details, and I’d be happy to help!

    Regards
    ExcelDemy

  57. Hello Ray,

    Thank you for your kind words! Yes, you can create rules for this using conditional formatting and custom formulas. Here’s how you can set it up:

    Highlight dates between 0 and 60 days less than TODAY():
    1. Select your date range.
    2. Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
    3. Enter this formula:
    =AND(A1<=TODAY(), A1>=TODAY()-60)
    4. Set your desired formatting and click OK.

    Highlight dates between 61 and 90 days less than TODAY():
    Repeat the steps above with this formula:
    =AND(A1=TODAY()-90)
    Apply a different formatting style for this rule.

    Replace A1 with the first cell in your date range. These rules will work simultaneously to format cells based on your specified ranges. Let me know if you need further clarification!

    Regards
    ExcelDemy

  58. Hello Marian,

    You are most welcome. Thanks for your feedback and appreciation. Glad to hear that you liked our works. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  59. Hello Andrei,

    Good morning! Yes, you can achieve this by creating a calculated field in the Pivot Table. Here’s how you can set it up:

    Set Up Your Pivot Table:
    1. Select your source data and create a Pivot Table.
    2. Place Sales Person (Column C) in the Rows section.

    Create the Calculated Field:
    1. Click anywhere inside the Pivot Table.
    2. Go to the PivotTable Analyze tab on the ribbon >> from Fields, Items & Sets >> select Calculated Field.
    3. In the dialog box, give your calculated field a name, like “Average Sales Value.”
    4. Enter the formula: = Value / Volume.

    Adjust the Values Area: Add the calculated field to the Values area. It will automatically calculate the average sales value for each salesperson.

    Regards
    ExcelDemy

  60. Hello Tallnsknny,

    Thank you for your comment! Referencing a list from a different tab should work fine with Method 1. If the formula returns blanks when copied down, it may be due to relative references not adjusting correctly. Try using absolute references $Sheet1!$A$1:$A$10 in your formula where necessary.

    If you’d like us to review your spreadsheet, you can share it by ExcelDemy Forum. Let us know if this helps!

    Regards
    ExcelDemy

  61. Hello Dear,

    You are most welcome. Thanks for your appreciation and feedback. Keep exploring Excel with ExcelDemy to see more awesome contents.

    Regards
    ExcelDemy

  62. Hello Kholoud,

    You’re very welcome! I’m glad you found the resource helpful. Regarding Task 4 for Example 2 – Employee Management Data, could you share more details about the issue you’re facing?

    If you’d like, you can upload your work and questions directly to the ExcelDemy forum. This way, I or other community members can take a closer look and assist you further. Here’s the link to the forum: ExcelDemy Forum.

    Regards
    ExcelDemy

  63. Hello Alex,

    Hi, thank you for trying out the methods! The issue you’re facing with Option 3 not working after reopening the document is likely because the VBA code needs to be saved in a macro-enabled workbook. When saving your file, ensure you select Excel Macro-Enabled Workbook (*.xlsm) as the file format.

    Additionally, make sure macros are enabled when you reopen the document. You can do this by clicking Enable Content in the yellow bar at the top of Excel after opening the file.

    Let me know if you need further assistance!

    Regards
    ExcelDemy

  64. Hello Lisa,

    You can download the Excel and PDF files free of cost just by entering your valid email address in the download section. 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 inbox/spam folder to get the Excel file.

    Best Regards
    ExcelDemy

  65. Hello Jim Allen,

    To address the issue of auto-scaling with reference lines (like min, max, or average) while using specific datasets, you can create a dynamic range formula for the data axis.
    For example, use MIN and MAX functions with references to the desired dataset, ensuring the axis adjusts accordingly.
    This avoids Excel defaulting to reference lines. By setting the axis scale manually or with a formula tied to the dataset, the chart will scale based on the actual data values. This solution doesn’t require macros, making it accessible to non-Excel users.

    Regards
    ExcelDemy

  66. Hello Andy,

    To replace quotation marks in Excel, you can use the SUBSTITUTE function, but since quotation marks are used as delimiters for text, you’ll need to double them up. For instance, to replace a single quote with a double quote, you can use:
    =SUBSTITUTE(A1, “”””, “new_value”).
    To add quotes at the beginning or end of a string, you can concatenate them like this: =”””” & A1 & “”””. This should help with handling quotation marks in your data.

    Regards
    ExcelDemy

  67. Hello David M,

    Thanks for sharing the link! It’s great to hear that the solution for Excel 2010 works well in Office 365 too. It’s always helpful to find methods that can be applied across different versions of Excel. I’ll check out the detailed steps you’ve provided. You can also explore more ways to use conditional formatting with different color schemes in Excel on ExcelDemy’s site.

    Regards
    ExcelDemy

  68. Hello,

    You are most welcome. Thanks for your feedback. Glad to hear that our articles step-by-step examples are helpful to manage data analysis. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  69. Hello,

    You are most welcome. Thanks for your feedback. Glad to hear that our articles examples are helpful to streamline data collections. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  70. Hello Norm,

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

    Regards
    ExcelDemy

  71. Hello Rose Marra,

    The formula =AGGREGATE(3,5,B9) uses the AGGREGATE function, which can perform various operations on a range, such as summing, averaging, or finding the largest value, while ignoring errors or hidden rows.

    Here’s a breakdown:
    3: This is the function number, specifying that the formula will perform the COUNTA function, which counts non-empty cells.

    5: This is the options argument, where 5 means the function will ignore hidden rows and error values (e.g., cells with errors or those hidden by filters).

    So, this formula counts the non-empty cells while ignoring any hidden rows and errors.

    Regards
    ExcelDemy

  72. Hello,

    You are most welcome. Thanks for your feedback and appreciation. Glad to hear that you got to know about TRIM function from our article and it saved your so much time. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  73. Hello Shabnum,

    You are most welcome. Thanks for your feedback and appreciation, it means a lot to us. Glad to hear that our articles are helpful to you. Keep exploring Excel with ExcelDemy. Let us know if you have further queries!

    Regards
    ExcelDemy

  74. Hello Youssef Bahlawi,

    You are most welcome. Thanks for your feedback and appreciation. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  75. Hello Angela,

    Thank you for your kind words and thoughtful feedback! I’m glad you found the information and video clear and helpful.

    For your specific requirements, Excel alone may not provide the ideal solution since it lacks features for real-time interaction, voting visibility, and threaded comments. However, here are a few suggestions that might work for you:

    Microsoft Forms with Teams/SharePoint Integration: You could use Microsoft Forms for the survey and then share the results on a collaborative platform like Microsoft Teams or SharePoint. This would allow participants to view responses and engage in discussions in a comment thread format.

    Google Forms with Google Sheets: While Google Forms doesn’t support comment threads directly, you could use the linked Google Sheet to compile responses and then share the sheet with permissions for commenting. For discussion, linking to a shared Google Doc might work.

    Feel free to share more details if you’d like help exploring these options further!

    Regards
    ExcelDemy

  76. Hello Thomas,

    Thanks for your feedback and appreciation. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  77. Hello Hammy,

    Thank you for your comment! You can achieve this calculation in Excel with accuracy even if your numbers include decimals.

    Ensure Numbers Are in Numeric Format: First, check that your values in cells E6, C6, and D6 are formatted as numbers and not text. If they’re treated as text, Excel won’t calculate the formula correctly.

    To convert text to numbers, select the cells, go to the Data tab >> select Text to Columns.
    Alternatively, use =VALUE(cell) in another cell to convert text to a number.

    Input Your Formula: Use the formula you mentioned:
    =(E6-C6)/(C6-D6)

    Excel will handle the decimal points automatically as long as the values are in numeric format.

    Adjust Decimal Display: If you want to control how many decimal places are shown, select the result cell, go to the Home tab, and use the Increase/Decrease Decimal buttons in the Number section.

    Let me know if you face any issues or need further clarification!

    Regards
    ExcelDemy

  78. Hello,

    You are most welcome. Thanks for your feedback and appreciation. Glad to hear articles examples and detailed explanations and screenshots are helpful to you. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  79. Hello Mark Muntean,

    Thank you for bringing this up! The issue you’re encountering with the Stock data type misidentifying certain ticker symbols is a known limitation. Excel’s Stock tool relies on Microsoft’s data feed, which sometimes matches symbols to incorrect entries, especially when multiple assets share similar tickers across global exchanges.
    To work around this, you can:
    1. Specify the full name of the ETF or company (e.g., “iShares 20+ Year Treasury Bond ETF” instead of “TLT”) in the cell before converting it to the Stock data type.

    2.If that doesn’t resolve the issue, you can manually edit the linked data source:
    2.1. Right-click the cell containing the Stock data.
    2.2. Select Data Type >> Change and choose the correct entry from the list.

    3. For a more reliable solution, consider using a dedicated financial add-in like “Stock Connector,” which allows you to pull data directly from US exchanges using specified symbols.

    We understand this limitation can be frustrating, and we hope these tips help improve your experience!

    Regards
    ExcelDemy

  80. Hello Nourhan Essam,

    You can download the Excel file from the Download section.
    Here, I’m also attaching the Excel file link: Analysis of Price Volume Variance.xlsx

    Regards
    ExcelDemy

  81. Hello JN,

    To make the Advanced Filter work with both criteria (e.g., St = FL and Memb Notes = *FL-Seasonal*), you need to arrange the criteria in a specific way:

    1. Place the conditions for AND logic (both must be true) in the same row.
    2. Place the conditions for OR logic (either can be true) in separate rows.
    For your case:
    If you want both conditions to apply simultaneously (AND logic), ensure they are in the same row under their respective headers in the criteria range.
    If you’re facing issues, double-check the formatting of the criteria, such as using =”=FL” for exact matches or wildcard symbols like * for partial matches.

    Regards
    ExcelDemy

  82. Hello Theresa L Smith,

    It sounds like you’re experiencing an issue where strikethrough appears automatically after making changes, even though it’s not manually applied.
    Here are a few steps you can try to resolve this:
    Check for Conditional Formatting: Strikethrough could be applied via conditional formatting. To remove it, select the affected cells, go to the Home tab >> from Conditional Formatting >> select Clear Rules >> select Clear Rules from Entire Sheet.

    Check for Macros or VBA Code: If the workbook includes any macros or VBA code, it could be triggering the strikethrough. To check for this, press Alt + F11 to open the VBA editor and review any code that might be altering cell formatting.

    Disable Track Changes: If “Track Changes” is enabled, it can sometimes apply strikethrough to show revisions. To disable it, go to Review tab >> from Track Changes >> select Highlight Changes and uncheck any active options.

    By trying these, you should be able to stop the automatic strikethrough formatting in your workbook. Let me know if you need more help!

    Regards
    ExcelDemy

  83. Hello,

    You are most welcome. Thanks for your feedback and appreciation. Glad to hear our examples helped you to understand the Cells function properly.

    Regards
    ExcelDemy

  84. Hello Jon H,

    Thank you for sharing this tip! Replacing Chr(34) & Chr(34) with “” is a great clarification and should make the code more straightforward for others to understand and implement. This will surely be helpful for anyone working with Method 8!

    Regards
    ExcelDemy

  85. Hello Elsa Dakamo,

    That’s great to hear! Let me know if you have any questions or need more details.
    I’m happy to help!

    Regards
    ExcelDemy

  86. Hello Suresh_Excelist,

    Thank you for sharing this formula! It’s a clever use of the IFS function to handle multiple formatting conditions. The approach works well for customizing date formats based on specific criteria.

    Just a quick note: if the IFS function isn’t available in an older version of Excel, the formula could be rewritten using nested IF statements for compatibility. Also, depending on the locale settings, the date format strings like “dd/mm/yy” might need adjustment to work as expected.

    Feel free to share more such creative solutions!

    Regards
    ExcelDemy

  87. Hello,

    You are most welcome. Glad to hear this post is helpful to you and the examples helped you to understand the process. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  88. Hello Tochi,

    You are most welcome. Glad to hear that our step-by-step tutorial helped you. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  89. Hello Aadirya Yadav

    Thank you for your comment! You can download the related Excel file using the link below: Examples of Different Types of Lookups.xlsx

    Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  90. Hello Macel Williams,

    Thank you for your kind words! I’m thrilled that you enjoyed the article.
    While we currently don’t have a video format for this specific topic, we appreciate your suggestion and will consider creating one in the future.
    If you have any specific question or query feel free to ask here, I will be happy to help you.

    Regards
    ExcelDemy

  91. Hello Ryan,

    Yes, it’s possible to achieve this in Excel! You can use a combination of formulas to extract the matching word from a string.

    1. Use the SEARCH function to locate the position of your keyword within the string.
    2. Use the MID function to extract the word based on the position.
    If your lookup value is in cell A1 (e.g., “India”) and the text string is in B1 (e.g., “256 Indian Drive”), you can use the following formula:
    =IF(ISNUMBER(SEARCH(A1,B1)),TRIM(MID(B1,SEARCH(A1,B1),LEN(A1))),”Not Found”)
    This will return “Indian” if the word contains the keyword “India.” Adjust as needed for more specific cases.

    Let me know if you’d like further clarification!

    Regards
    ExcelDemy

  92. Hello RNair,

    Thank you for your input! The steps you mentioned are helpful for removing empty strings from cells.
    However, it’s worth noting that Step 2 isn’t always necessary, as the formula solutions provided already return blank cells (“”) when the conditions are met.
    If you need to fully clear the cells instead of keeping the formula, filtering and deleting them as you described is indeed a practical approach.
    Let me know if you have any additional questions or need further clarification!

    Regards
    ExcelDemy

  93. Hello Bryan Owens,

    To create an auto-rotating two-week schedule for your company, check out this article on creating a dynamic schedule in Excel. It explains how to set up formulas and link dates for automatic updates. You may also find this guide on making a roster in Excel useful, as it provides additional steps to help you design and manage your schedule efficiently. Both articles will support you in building a functional rotating schedule.

    Regards
    ExcelDemy

  94. Hello Jason Potter,

    Thank you for your detailed feedback!
    You’re absolutely correct that the formula for standard deviation (=2*SQRT(K37/(H10-2))) uses H10, which refers to the number of observations (n). I updated the article to clarify this.
    Additionally, your observation about K38 (standard deviation) being used to identify outliers rather than K37 (SSE) is accurate, and I’ll fix this inconsistency in the explanation.

    Regarding the standard deviation, you’re right—it can be taken directly from the regression output (Standard Error). I’ll revise the steps to make them clearer and more concise.

    Regards
    ExcelDemy

  95. Hello Aluwani,

    It sounds like you’re trying to apply a formula across multiple months in your leave tracker. Typically, you’ll need to use relative referencing or a dynamic formula to repeat the calculations. You can either copy the formula manually or use Excel’s drag-and-fill feature for cells with similar data patterns.
    To directly copy the formula you can download the Excel File: Create-Leave-Tracker-2025.xlsx

    For further help, check out the detailed guide in the video or feel free to provide more specifics about where you’re stuck!

    Regards
    ExcelDemy

  96. Hello Abdallah,

    Thank you for reaching out! We’d be happy to help you with your formula. Instead of email, we recommend posting your explanation and screenshots in the ExcelDemy Forum. This way, our community and experts can provide you with a faster and more comprehensive solution.

    Looking forward to your post on the forum!

    Best regards,
    ExcelDemy

  97. Hello Bob,

    Thank you for your feedback! I’m sorry to hear you’re encountering errors. To assist you better, could you please share the specific error message you’re receiving? Also, double-check that your cell references and formula syntax match the example provided.
    Sometimes, small differences like extra spaces or missing parentheses can cause issues. I’d be happy to guide you through it!

    Regards
    ExcelDemy

  98. Hello,

    You are most welcome. Thanks for your feedback and appreciation. Glad to hear that our step-by-step guide to carriage return helped you. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  99. Hello Robert Thompson,

    Thank you for sharing this useful approach! The formula-based method is indeed a powerful alternative to Power Query and VBA.
    Using LET and LAMBDA makes it dynamic and reusable, especially with named ranges or tables. This approach is great for those who prefer formula-based solutions and want to avoid VBA or external tools.
    Your explanation is clear, and the CrossJoin LAMBDA function adds even more flexibility. It’s a fantastic addition to the methods mentioned in the article. Thanks for contributing this valuable insight!

    Regards
    ExcelDemy

  100. Hello Jessica Benefiel,

    Thank you for your kind words! We’re glad you found this tutorial helpful. It sounds like you have a great plan for tracking your inventory more efficiently. Adding columns to the Stock In and Stock Out sheets to categorize items by date, order, or month is a smart approach. This way, you can easily analyze stock movement for specific time periods.

    To make it even more efficient, you can use Excel functions like SUMIFS to calculate totals based on multiple criteria (like date ranges or order numbers) rather than manually counting items. For example, you could track stock movement for each month automatically. This method will definitely save you time compared to counting 650 items manually.

    If you’d like more guidance on setting up these extra columns or formulas, feel free to ask. We’re happy to help you get it just right!

    Best regards,
    ExcelDemy

  101. Hello Ethan,

    The warning occurs when the destination workbook has fewer rows or columns than the source.
    Excel workbooks don’t have unlimited rows/columns, but there are set limits depending on your Excel version (e.g., 1,048,576 rows by 16,384 columns in modern versions).
    To avoid this, you can adjust the destination workbook’s row and column limits, but Excel doesn’t support dynamically resizing beyond these limits. You may need to copy data manually if resizing doesn’t work.

    Regards
    ExcelDemy

  102. Hello TTunstall,

    You are most welcome. Thanks for your feedback and suggestions. Glad to hear our detailed explanations helped you Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  103. Hello Clive Bay,

    To delete rows where “Undefined” appears in Column F, you can use the filter feature. Here’s how:

    1. Select your data range, then go to the Data tab and click Filter.
    2. In Column F, click the filter drop-down and uncheck everything except Undefined.
    3. Select the filtered rows, right-click, and choose Delete Row.
    4. Finally, remove the filter to restore the full data view.
    This will delete all rows containing “Undefined” without having to delete each one manually.

    Let me know if you need further assistance!

    Regards
    ExcelDemy

  104. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear that the SUBSTITUTE function method is helpful to you. Keep exploring Excel with ExcelDemy.

    Regards
    ExcelDemy

  105. Hello Arya,

    Before going 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

  106. Hello Amir,

    You are most welcome. Glad to hear it worked perfectly. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  107. Hello Amir,

    Try this updated code. This ensures the decimal portion is accurately converted into words.

    
    Function NumberToWords(ByVal MyNumber As Double) As String
        Dim Units As Variant, Tens As Variant
        Dim WholePart As Long, DecimalPart As Long, Words As String
    
        ' Initialize arrays for unit and tens words
        Units = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", _
                      "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", _
                      "Seventeen", "Eighteen", "Nineteen")
        Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
        
        ' Split number into whole part and decimal part
        WholePart = Int(MyNumber)
        DecimalPart = Round((MyNumber - WholePart) * 100, 0) ' Extract two-digit decimal part
    
        ' Convert the whole number to words
        Words = ConvertToWords(WholePart, Units, Tens)
        
        ' Combine whole part and decimal part into final result
        If DecimalPart > 0 Then
            NumberToWords = Words & " and " & DecimalPart & "/100"
        Else
            NumberToWords = Words & " and 0/100"
        End If
    End Function
    
    Function ConvertToWords(ByVal Number As Long, Units As Variant, Tens As Variant) As String
        Dim Result As String
    
        ' Handle Thousands
        If Number \ 1000 > 0 Then
            Result = Result & ConvertToWords(Number \ 1000, Units, Tens) & " Thousand "
            Number = Number Mod 1000
        End If
    
        ' Handle Hundreds
        If Number \ 100 > 0 Then
            Result = Result & Units(Number \ 100) & " Hundred "
            Number = Number Mod 100
        End If
    
        ' Handle Tens and Units
        If Number > 0 Then
            If Number < 20 Then
                Result = Result & Units(Number)
            Else
                Result = Result & Tens(Number \ 10)
                If Number Mod 10 > 0 Then
                    Result = Result & "-" & Units(Number Mod 10)
                End If
            End If
        End If
    
        ConvertToWords = Trim(Result)
    End Function
    

    Regards
    ExcelDemy

  108. Hello Amir,

    Here’s a VBA code that converts numbers to words and formats the decimal part as a fraction:

    
    Function NumberToWords(ByVal MyNumber)
        Dim Units As String, Tens As String, Temp As String, DecimalPlace As Integer
        Dim Dollars As String, Cents As String
    
        ' Initialize arrays for number to word conversion
        Units = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", _
                      "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
        Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
        
        ' Separate whole number and decimal part
        DecimalPlace = InStr(MyNumber, ".")
        If DecimalPlace > 0 Then
            Dollars = Left(MyNumber, DecimalPlace - 1)
            Cents = Mid(MyNumber, DecimalPlace + 1) & "00"
            Cents = Left(Cents, 2)
        Else
            Dollars = MyNumber
            Cents = "00"
        End If
        
        ' Convert whole number part to words
        Temp = ConvertToWords(Dollars, Units, Tens)
        NumberToWords = Temp & " and " & Cents & "/100"
    End Function
    
    Function ConvertToWords(ByVal Number, Units, Tens) As String
        Dim Result As String
        Dim Num As Double
        Num = Val(Number)
    
        If Num = 0 Then
            ConvertToWords = "Zero"
            Exit Function
        End If
    
        ' Handle Thousands
        If Num \ 1000 > 0 Then
            Result = Result & ConvertToWords(Num \ 1000, Units, Tens) & " Thousand "
            Num = Num Mod 1000
        End If
    
        ' Handle Hundreds
        If Num \ 100 > 0 Then
            Result = Result & Units(Num \ 100) & " Hundred "
            Num = Num Mod 100
        End If
    
        ' Handle Tens and Units
        If Num > 0 Then
            If Num < 20 Then
                Result = Result & Units(Num)
            Else
                Result = Result & Tens(Num \ 10)
                If (Num Mod 10) > 0 Then
                    Result = Result & "-" & Units(Num Mod 10)
                End If
            End If
        End If
    
        ConvertToWords = Trim(Result)
    End Function
    

    How It Works
    The number is split into whole and decimal parts. It converts the whole number part into words (e.g., 12875 becomes “Twelve Thousand Eight Hundred Seventy-Five”). The decimal part is displayed as a fraction (e.g., “38/100”).
    Use the function in Excel as:
    =NumberToWords(12875.38)
    The result will be:
    “Twelve Thousand Eight Hundred Seventy-Five and 38/100”

    Regards
    ExcelDemy

  109. Hello Jim,

    Thank you for your observation. The delimiter variable was declared but not used to split columns in the VBA code. To achieve this, we need to use Workbooks.OpenText instead of Workbooks.Open, and specify the delimiter.

    Here’s the updated part of the code:
    Workbooks.OpenText Filename:=openFiles(I), DataType:=xlDelimited, Semicolon:=True

    This ensures the CSV values are properly split into columns. If you’d like a more detailed explanation or further guidance, feel free to ask.

    Best regards,
    ExcelDemy

  110. Hello Sviat,

    You are most welcome. Thanks for your feedback! Glad to hear that first option really worked for you. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  111. Hello Sam Gardner,

    That’s a great suggestion! Using a recursive Lambda function to reverse a string in Excel is indeed an elegant and efficient approach.
    By setting up the function through the Name Manager, you can call it easily with any text and its length as parameters. It eliminates the need for complex formulas, making the process smoother.
    Thanks for sharing this method!

    Regards
    ExcelDemy

  112. Hello Paresh Kanti Paul,

    Thank you for reaching out. To copy and paste a screenshot of an Excel Data Table (like the one shown on the mentioned website) into an Excel worksheet, you can follow these steps:

    Take a Screenshot: Use the Snipping Tool (Windows) or Command + Shift + 4 (Mac) to capture the table as an image.
    Paste into Excel: Open your Excel worksheet, right-click where you want to insert the image, and choose Paste (or press Ctrl + V).
    Adjust Image: You can resize or move the image as needed within the worksheet.

    If you’d like to recreate the data as an editable table (instead of a static image), you can try these options:

    If you want to convert an image of a table into an editable Excel table, Excel Desktop also has a Data from Picture feature (previously available only on mobile).
    Here’s how you can do it:
    1. Go to the Data tab in Excel Desktop.
    2. Click From Picture and select Picture From File or Picture From Clipboard.
    3. Excel will process the image and convert it into an editable table.

    Regards
    ExcelDemy

  113. Hello Zeke Cray,

    Good morning! It sounds like the issue might be related to differences in the structure or layout of your second file.
    Range Selection: Ensure the range is actively selected in the second file before running the macro.
    Sheet Protection: If the sheet is protected, unprotect it to allow VBA access.
    Range Differences: Check if the second file’s range differs from the first. Update Selection.SpecialCells(xlCellTypeVisible) accordingly.
    Merged Cells: Remove any merged cells in the selection, as they can cause issues with PasteSpecial.

    If you still face issues, let me know any specific error messages or changes between the two files.

    Regards
    ExcelDemy

  114. Hello H S,

    Thank you for your feedback! I’m glad you found the article helpful. Regarding your question, you can use Excel formulas to find the names that are missing from one column compared to the other. Here’s a simple approach:

    1. Find names in Column A that are not in Column B:
    Use this formula in Column C (starting from C1):

    =IF(COUNTIF(B:B, A1)=0, A1, “”)
    Drag this formula down to check for all names in Column A that are missing in Column B.

    2. Find names in Column B that are not in Column A:
    Use this formula in Column D (starting from D1):

    =IF(COUNTIF(A:A, B1)=0, B1, “”)
    This will list names in Column B that are not found in Column A.

    3. Combine the results in one column (optional):
    You can combine the unique names from Columns C and D using Excel’s FILTER or UNIQUE function if needed.

    Regards
    ExcelDemy

  115. Hello,

    You are most welcome. Thanks for your feedback and appreciation. Glad to hear that our step-by-step guide is super helpful to you.

    Regards
    ExcelDemy

  116. Hello Mark,

    Thank you for your comment. To achieve your goal, you can follow these steps:
    Open the CSV: Use VBA’s Open statement to access the CSV file.
    Extract Data: Use Input or Line Input to read each line, then parse it using Split().
    Export to Excel: Write the extracted data into a predefined Excel template using Range.Value.

    If you’d like a detailed guide or VBA code, feel free to ask, and I’ll be happy to help!

    Best regards,
    ExcelDemy

  117. Hello Destiny Rivera,

    Yes, you can modify the schedule to display by calendar quarter or year. You would need to adjust the date formulas and ranges to reflect quarterly or yearly data. For quarters, use a formula that groups dates into one of the four quarters of the year. For a yearly view, adjust the schedule to display the dates and events by the full year.

    For Quarters:
    Use the MONTH function to categorize dates into quarters:
    =CHOOSE(MATCH(MONTH(A2),{1,4,7,10}),”Q1″,”Q2″,”Q3″,”Q4″)
    This formula checks the month of the date in cell A2 and returns “Q1” for January to March, “Q2” for April to June, etc.

    For Year:
    You can simply extract the year from a date:
    =YEAR(A2)

    Regards
    ExcelDemy

  118. Hello M. Conner,

    It seems the issue arises from how the last row in the destination sheet is being identified. Instead of directly pasting on the last row, it’s pasting over the header.
    Here’s the updated code incorporating the fix to ensure rows are pasted below the last occupied row in the destination sheet:

    Sub MoveRow_DeleteOriginal()
        Dim rg As Range
        Dim xc As Range
        Dim p As Long
        Dim q As Long
        Dim r As Long
        Dim lastRow As Long
    
        p = Worksheets("Intake Unit A-B").UsedRange.Rows.Count
        q = Worksheets("Stein E-F").UsedRange.Rows.Count
        If q = 1 Then
            If Application.WorksheetFunction.CountA(Worksheets("Stein E-F").UsedRange) = 0 Then q = 0
        End If
    
        Set rg = Worksheets("Intake Unit A-B").Range("Z1:Z" & p)
        On Error Resume Next
        Application.ScreenUpdating = False
    
        For r = 1 To rg.Count
            If CStr(rg(r).Value) = "Stein E-F" Then
                lastRow = Worksheets("Stein E-F").Cells(Rows.Count, 1).End(xlUp).Row + 1
                rg(r).EntireRow.Copy Destination:=Worksheets("Stein E-F").Rows(lastRow)
                rg(r).EntireRow.Delete
                r = r - 1
            End If
        Next
    
        Application.ScreenUpdating = True
    End Sub
    

    Changes Made:
    1. Identify the Last Row: The code now calculates the last row in the destination sheet dynamically using:
    lastRow = Worksheets(“Stein E-F”).Cells(Rows.Count, 1).End(xlUp).Row + 1

    2. Pasting Rows: Rows are now pasted below the last row to avoid overwriting the header.

    Regards
    ExcelDemy

  119. Hello Inderpal Singh,

    Thank you for your feedback! To achieve the format 06-Dec-24 13:03:00 in Excel without the colon between the date and time, you can use a formula to reformat it after inserting the date from date picker. Assuming your date and time are in cell A1, use:

    =TEXT(A1, “DD-MMM-YY hh:mm:ss”)
    This removes the colons and combines the date and time as desired.
    Or you can updae the existing VBA code’s date format to format the date and time as 06-Dec-24 130300 (removing the colons between time), you can adjust the ControlTipText property in the existing code. Here’s the updated section:

    Update the ControlTipText line:

    Controls("C" & (i)).ControlTipText = Replace(Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
        ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "dd-mmm-yy hh:mm:ss"), ":", "")
    

    Explanation:
    1. The Replace function removes the colons (:) from the formatted date and time.
    2. Format ensures the date is displayed in the dd-mmm-yy hh:mm:ss format before applying Replace.
    3. Incorporate this into each section of the code where ControlTipText is being set.

  120. Hello William,

    You can download the Excel and PDF files free of cost just by entering your valid email address in the download section. 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 inbox/spam folder to get the Excel file.

    Best Regards
    ExcelDemy

  121. Hello M. Conner,

    It seems that the issue you’re facing occurs because when the row is deleted, the loop skips the next row due to the r = r – 1 line, but that condition is inside the If block. Try moving the row deletion and the check for each sheet into separate conditional blocks. For automatic running, you can use the Workbook_Open event or set up a timer to call the macro periodically.

    Here’s a modified approach for your code:
    1. Ensure If CStr(rg(r).Value) for each sheet is checked properly.
    2. For automatic execution, use an event handler like Workbook_Open.

    Let me know if you’d like further assistance!

    Regards
    ExcelDemy

  122. Hello Etrek,

    This error occurs when a formula refers to its own cell directly or indirectly, which creates a circular reference. To resolve this, check the formulas in your worksheet and ensure they don’t refer back to their own cell. You may need to adjust the formula or move it to a different cell to break the circular reference. For more help, check Excel’s circular reference troubleshooting guide.

    Regards
    ExcelDemy

  123. Hello SM,

    To flag reports that were received late but are not currently overdue, you can adjust the formula to check if the report was turned in later than the due date. You can compare the actual received date with the due date and flag it if the received date is later. Here’s an example of how you can modify your formula:

    Assume column A contains the due date and column B contains the received date:
    =IF(B2>A2, “Late”, “On Time”)

    This formula will flag the reports as “Late” if the received date (B2) is after the due date (A2). You can also add conditional formatting to highlight these “Late” entries.

    For metrics on how often you go over due dates, you can count how many reports are flagged as “Late” using a COUNTIF function:
    =COUNTIF(C2:C100, “Late”)

    This will give you a count of all reports that were received late.

    I’m glad the article was helpful, and I hope this solution better suits your needs for tracking late submissions! Let me know if you need further assistance with this adjustment.

    Regards
    ExcelDemy

  124. Hello Daniel,

    Thank you for your kind words! I’m glad you found the guide helpful. For currency cells, the process can be a bit tricky, you can follow the following steps:

    1. Select your cells containing currency values.
    2. Go to the Home tab >> click Conditional Formatting >> select Icon Sets.
    3. Choose an Icon Set (e.g., 3 icons).
    4. Click Manage Rules and select the range you want to apply the formatting to.
    5. Click Edit Rule to set specific thresholds based on the currency ranges (e.g., $0–$100, $101–$500).
    6. Adjust the Icon Style and make sure the range is based on numeric values, not text.

    Regards
    ExcelDemy

  125. Hello M Wilson,

    You’re most welcome! I’m glad Method four worked for you. I agree, sometimes it feels like the simpler solutions from the past were more intuitive. But at least we have these new methods to work with now!

    Regards
    ExcelDemy

  126. Hello SM,

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

    Regards
    ExcelDemy

  127. Hello Josi,

    Yes, it is possible to move data from one sheet to another automatically while retaining the original data in the new sheet. This can be done using VBA (Visual Basic for Applications) to automate the process. The script can copy data to the target sheet and clear the original while keeping the target data intact.

    Here’s a VBA script to automate this process:

    1. Press Alt + F11 to open the VBA editor.
    2. Insert a Module and paste the following code:

    Sub MoveData()
        Dim wsSource As Worksheet
        Dim wsTarget As Worksheet
        Dim lastRow As Long
        
        Set wsSource = ThisWorkbook.Sheets("Source") 'Change to your source sheet name
        Set wsTarget = ThisWorkbook.Sheets("Target") 'Change to your target sheet name
        
        lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
        wsSource.Rows("1:" & lastRow).Copy
        wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        wsSource.Rows("1:" & lastRow).ClearContents
    End Sub

    3. Run the Macro to transfer and clear the source data.

    Regards
    ExcelDemy

  128. Hello Nil,

    You can modify the existing VBA code to remove the prefix “(” and suffix “)” after changing the font color by using the Replace function. Here’s a basic example to add at the end of your VBA script:

    
    With ActiveCell
        .Value = Replace(Replace(.Value, "(", ""), ")", "")
    End With

    This will remove the specified characters from the cell’s content. Adjust as needed for your specific case.

    Regards
    ExcelDemy

  129. Hello Joe,

    You are most welcome, Joe. Thanks for your feedback! We are glad to hear you liked our article. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  130. Hello Santiago,

    You’re welcome! Unfortunately, custom cell formats in Excel only change the appearance of the data (like number format or text display) and cannot modify the background color of a cell.

    To change the background color based on certain criteria, you would need to use Conditional Formatting. Although your table is dynamic, you can apply conditional formatting rules that automatically adjust to new data. For example, you could set a rule to change the background color when the cell value is 00.00.0000.

    Let me know if you’d like help setting that up. Have a wonderful day!

    Best regards,
    ExcelDemy

  131. Hello Connor,

    The error indicates that Power Automate is receiving an object instead of a string for the ‘To’ field in your email action. To fix this, ensure the email address is passed as a string, not an object. You can use string() in your expression or directly input the email as “[email protected]”. Double-check any dynamic content or expressions used in this field to ensure the correct format.

    Regards
    ExcelDemy

  132. Hello Donna,

    It seems like Outlook may not have fully initialized when the macro was first run. Here are a few steps to troubleshoot:
    Restart Your System: This clears any lingering background processes.
    Check Task Manager: Ensure no Outlook process is running. End any that are and retry.
    Verify Macro Settings: Make sure macros are enabled under Trust Center >> Macro Settings.
    Run Excel as Administrator: Sometimes permissions can block full integration with Outlook.

    Let me know if these steps resolve your issue!

    Regards
    ExcelDemy

  133. Hello Lerato,

    Hope you are doing well. To practice data entry, you can 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

  134. Hello David Wood,

    To expand the method for eigenvectors to a 6×6 matrix in Excel:

    1. Set up the 6×6 matrix in Excel.
    2. Use Excel’s MMULT and MINVERSE functions to compute matrix operations.

    For determinants, you’ll need submatrices (e.g., 5×5) for characteristic polynomial calculation. Excel lacks a direct determinant function for larger matrices, so use VBA:

    Function Determinant(M As Range) As Double
        Dim Matrix() As Double
        Dim n As Integer
        n = M.Rows.Count
        ReDim Matrix(1 To n, 1 To n)
        For i = 1 To n
            For j = 1 To n
                Matrix(i, j) = M.Cells(i, j).Value
            Next j
        Next i
        Determinant = Application.MDeterm(Matrix)
    End Function
    

    Use this to calculate the determinant iteratively for the characteristic equation.

    Regards
    ExcelDemy

  135. Hello Michal,

    You can follow our Learn Excel page to learn and master Excel. Learn Excel page contains all types of Excel tutorial categorically.

    Follow our youtube channel to learn Excel visually. ExcelDemy YouTube

    Regards
    ExcelDemy

  136. Hello HS,

    You can use the FILTER function to extract missing names from one column compared to another. Assuming your first list is in Column A and the second in Column B, enter this in Column C:

    =FILTER(A:A, ISNA(MATCH(A:A, B:B, 0)), “No Matches Found”)

    This formula will list names in Column A that do not appear in Column B. Adjust ranges as needed. Let me know if you need further clarification!

    Regards
    ExcelDemy

  137. Hello Nina,

    Thank you for your appreciation and feedback. It’s a thoughtful question! The horizontal line in the forest plot indeed represents the range of the confidence interval (CI). If it appears wider than expected, it could be due to formatting settings or incorrect data input for the lower and upper CI values. Ensure the data accurately reflects the desired CI limits, and double-check the chart settings.

    Feel free to provide more details or screenshots if the issue persists—happy to help!

    Best regards,
    ExcelDemy

  138. Hello Chukwuemerie,

    You are most welcome. Thanks for your appreciation. Here’s a VBA solution to generate pay slips for multiple employees:

    Set Up Your Workbook:

    1. Create a master sheet (EmployeeData) with columns for Employee ID, Name, Salary, and other details.
    2. Prepare a pay slip template on another sheet (PaySlip).

    VBA Code:

    Sub GeneratePaySlips()
        Dim wsData As Worksheet, wsSlip As Worksheet
        Dim lastRow As Long, i As Long
        Dim employeeID As Range, outputFolder As String
        
        Set wsData = ThisWorkbook.Sheets("EmployeeData")
        Set wsSlip = ThisWorkbook.Sheets("PaySlip")
        
        lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
        outputFolder = ThisWorkbook.Path & "\PaySlips\"
        If Dir(outputFolder, vbDirectory) = "" Then MkDir outputFolder
        
        For i = 2 To lastRow
            wsSlip.Range("B2").Value = wsData.Cells(i, 1).Value ' Employee ID
            wsSlip.Range("B3").Value = wsData.Cells(i, 2).Value ' Name
            wsSlip.Range("B4").Value = wsData.Cells(i, 3).Value ' Salary
            
            ' Save as PDF
            wsSlip.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=outputFolder & "PaySlip_" & wsData.Cells(i, 1).Value & ".pdf"
        Next i
        
        MsgBox "Pay slips generated!", vbInformation
    End Sub

    1. The macro loops through each employee in EmployeeData.
    2. It fills in the PaySlip template with their details.
    3. Saves each pay slip as a PDF in a designated folder.

    Regards
    ExcelDemy

  139. Hello,

    You are most welcome. Thanks for your feedback and appreciation. Glad to hear that our article helped you to understand VBA colorindex.

    Regards
    ExcelDemy

  140. Hello,

    You are most welcome. Thanks for your feedback and appreciation. Glad to hear that our template is super helpful to you.

    Regards
    ExcelDemy

  141. Hello Martin,

    Please check your email address. Excel file will be sent to your inbox. Kindly, check your spam folder also.

    Regards
    ExcelDemy

  142. Hello Nancy Poelvoorde,

    To add new employees to your 2025 leave tracker, you can follow a step-by-step guide similar to creating the original tracker. Unfortunately, we don’t have an exact tutorial for your case, but you can adapt the existing guide by inserting rows for new employees and updating formulas or validation.
    Existing Youtube Tutorial: How to Create Leave Tracker in Excel

    Here, I have shown the step of adding new employee to the leave tracker. Add New Employee in the Leave Tracker

    Regards
    ExcelDemy

  143. Hello Mohamed ali,

    To link the date picker to a specific cell without clicking it, you can modify the VBA code to directly reference the desired cell. For example, in the UserForm’s code, set the date picker value to update the specific cell directly:

    
    Private Sub Calendar1_Click()
        Sheets("Sheet1").Range("A1").Value = Calendar1.Value
        Me.Hide
    End Sub

    This will update the specific cell (like A1) with the selected date without needing to click the cell itself.

    Regards
    ExcelDemy

  144. Hello Candi,

    It sounds like the hidden sheets might not be easily visible due to either being very hidden (not just hidden through Excel’s standard options) or due to issues in the recovery process.
    Try checking if there are any hidden sheets using the VBA editor or the “Unhide” option in the ribbon. If the sheets were deeply hidden, you may need a VBA code to unhide them. Additionally, ensure that no additional sheets were excluded during the file recovery process.

    Regards
    ExcelDemy

  145. Hello NancyP,

    To add new employees to your leave tracker,
    1. Simply insert rows for the new employees and input their relevant data, such as names, leave balances, and other details.
    2. Then you need to adjust any formulas (such as leave accrual calculations) to accommodate the new rows. Include the newly added rows cell reference in the formulas.
    3. Ensure that any drop-down lists or data validation rules are updated as well to include the new employee names.

    Regards
    ExcelDemy

  146. Hello Nate,

    You can set the macro to run automatically each time the workbook is opened by placing the code in the Workbook_Open event within the VBA editor. This way, the macro will execute whenever the file is accessed, eliminating the need to manually run it. You can access this by going to ThisWorkbook in the VBA editor and adding the desired macro under the Workbook_Open event. This ensures the macro runs daily or whenever the workbook is opened.

    
     Private Sub Workbook_Open()
        ' Place your macro code here
        Call YourMacroName
    End Sub
    

    To add this, press Alt + F11 to open the VBA editor, then double-click ThisWorkbook under “Microsoft Excel Objects” and paste the code. Replace YourMacroName with the name of your macro. This will trigger the macro whenever the workbook is opened.

    Regards
    ExcelDemy

  147. Hello Youssef

    You can modify your code to use “Paste Special” for the paste operation. Specifically, you can use PasteSpecial to paste only values, formats, or any other attributes you need. Here’s an updated version of your code with the PasteSpecial method applied:

    Sub CopyPasteBelowTheLastCell()
        ' Set variables
        Dim wsSource As Worksheet
        Dim wsTarget As Worksheet
        Dim iSourceLastRow As Long
        Dim iTargetLastRow As Long
        
        ' Set variables for source and destination sheets
        Set wsSource = Worksheets("AutoFulfil")
        Set wsTarget = Worksheets("FolowUp")
        
        ' Find last used row in the source sheet based on data in column B
        iSourceLastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row
        
        ' Find first blank row in the destination sheet based on data in column B
        ' Offset property is to move the copied data 1 row down
        iTargetLastRow = wsTarget.Cells(wsTarget.Rows.Count, "B").End(xlUp).Offset(1).Row
        
        ' Copy data from the source range
        wsSource.Range("B2:L" & iSourceLastRow).Copy
        
        ' Paste data into the target sheet using PasteSpecial
        wsTarget.Range("B" & iTargetLastRow).PasteSpecial Paste:=xlPasteValues ' To paste values only
        wsTarget.Range("B" & iTargetLastRow).PasteSpecial Paste:=xlPasteFormats ' To paste formats
        
        ' Optionally, clear the clipboard after pasting
        Application.CutCopyMode = False
    End Sub

    Explanation of changes:
    wsSource.Range(“B2:L” & iSourceLastRow).Copy: This line still copies the range from the source sheet.
    wsTarget.Range(“B” & iTargetLastRow).PasteSpecial Paste:=xlPasteValues: This pastes only the values from the copied range.
    wsTarget.Range(“B” & iTargetLastRow).PasteSpecial Paste:=xlPasteFormats: This ensures that the formatting is also pasted.
    Application.CutCopyMode = False: Clears the clipboard and removes the “marching ants” effect after the copy-paste operation.

    This code will now paste both the values and formats from the source sheet to the target sheet below the last used row in column B. Let me know if you need any further assistance!

    Regards
    ExcelDemy

  148. Hello,

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

    Regards
    ExcelDemy

  149. Hello Irshad,

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

    Regards
    ExcelDemy

  150. Hello James Graber,

    It seems like you may have switched from a one-time Microsoft Office purchase to a subscription model with Microsoft 365. The older Excel files you purchased should still be available on your computer. Microsoft 365 offers the latest version of Excel along with cloud-based features, but it doesn’t affect your previous files. If you can’t find your files, try searching your device or checking your cloud storage if you’ve set up OneDrive.

    Regards
    ExcelDemy

  151. Hello Jeff,

    Thanks for your insightful feedback! You’re absolutely right that lottery predictions are mostly based on luck, and the approach in the article is more of an introductory exercise using Excel. For accurate forecasting, more advanced methods, such as machine learning and time series analysis, are necessary. While this article serves as a basic guide for Excel enthusiasts, we appreciate your perspective and will consider updating it with a clearer disclaimer about the limitations of such predictions.

    Regards
    ExcelDemy

  152. Hello Chansokong,

    Thank you for the update! The #VALUE! error for Northing might be due to incorrect references, data format, or an issue with the latitude values. Make sure the formula is correctly applied, the latitude and longitude are in decimal degrees, and there are no extra spaces or non-numeric characters. Additionally, ensure the latitude falls within the valid range for UTM zones.

    In our end everything is working perfectly.

    If everything seems correct, feel free to share your formula or more details, and I’d be happy to assist further!

    Regards
    ExcelDemy

  153. Hello T C Millichap,

    It sounds like the macro might not be checking all rows properly when there are consecutive zeros. This could happen if the loop skips over rows after deleting one. You can modify the code to ensure it doesn’t skip rows by adjusting how the loop is set up.

    Sub DeleteRow_DefinedRange()
        Dim Rng As Range
        Dim i As Long
    
        For i = 14 To 5 Step -1 ' Loop backwards to prevent skipping rows
            If Cells(i, 4).Value = 0 Then
                Rows(i).Delete
            End If
        Next i
    End Sub

    By looping backwards, you prevent skipping rows after deletion. This should address the issue of consecutive zeros. Let me know if you need further adjustments!

    Regards
    ExcelDemy

  154. Hell Chris,

    Yes, it’s possible to check a text from one range and a fill color from a different range. You’d need to use VBA to compare the criteria separately. One way is to loop through each range and check if the text in one matches your condition and the fill color in the other meets your specified color.

    Sub CompareTextAndColor()
        Dim rngText As Range, rngColor As Range
        Dim cellText As Range, cellColor As Range
        Dim MatchCount As Integer
        Dim ws As Worksheet
        
        Set ws = ThisWorkbook.Sheets("Sheet1") ' Change as needed
        Set rngText = ws.Range("A1:A10") ' Range with text
        Set rngColor = ws.Range("B1:B10") ' Range with colors
        
        MatchCount = 0
        
        For Each cellText In rngText
            Set cellColor = rngColor.Cells(cellText.Row - rngText.Row + 1, 1)
            If cellText.Value = "YourText" And cellColor.Interior.Color = RGB(255, 0, 0) Then
                MatchCount = MatchCount + 1
            End If
        Next cellText
        
        MsgBox "Matches found: " & MatchCount
    End Sub

    Replace “YourText” and RGB(255, 0, 0) with your criteria. Let me know if you need further clarification!

    Kind regards,
    ExcelDemy

  155. Hello Stanka,

    Thank you for reaching out! The error 5941 typically occurs when the Word document doesn’t have a content control matching the code’s reference. Please ensure:

    1. The targeted content control exists in your Word document.
    2. The Selection is correctly pointing to the content control.

    Try reviewing the document’s structure and rechecking the macro code. If you need further assistance, feel free to share more details, and I’ll be happy to help!

    Regards
    ExcelDemy

  156. Hello Jerrel,

    You are most welcome. Thanks for your feedback and appreciation. Glad to hear our content is udeful to you. Keep learning Excel with ExcelDemy.

    Regards
    ExcelDemy

  157. Hello Gene Bennington,

    It seems like the issue you’re describing might be related to a shading or highlight effect, possibly caused by conditional formatting or the text highlight tool in Numbers. Try these steps:

    1. Check for any conditional formatting rules and remove them if unnecessary. Go to Conditional Formatting >> select Clear Rules.
    2. Verify if a text highlight color is applied or not. If applied set it to “None” or clear it.
    3. Ensure the default background and text color settings are properly configured.

    Let me know if this helps or if further clarification is needed!

    Regards
    ExcelDemy

  158. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear our step-by-step explanation is helpful to you. Try these in your projects and let us know how it works.
    Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  159. Hello,

    You are most welcome. Thanks for your appreciation. Glad to hear our sample Excel is useful to you. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  160. Hello Jacob Muvingi,

    The values you’re getting differ likely because the Solver setup parameters, such as constraints or decision variable ranges, are not identical to the example in the article. Double-check the constraints, formulas in the objective cell, and Solver settings to ensure they match the given example. Small differences in input data or setup can lead to different outputs.

    Regards
    ExcelDemy

  161. Hello Cris,

    The error “Objective Cell contents must be a formula” occurs because Excel Solver requires the objective cell to contain a formula that calculates the outcome to optimize, such as the total cost or profit.
    If the cell only contains a value or lacks a calculative formula, Solver cannot determine how to adjust the variables to achieve the desired goal. Make sure the objective cell references other cells through a formula that depends on the decision variables.

    Regards
    ExcelDemy

  162. Hello Narol Jnr Akis,

    To download the practice workbook or datasheet go to the Download the Practice Workbooksection. You can download the Excel file from there.

    Regards
    ExcelDemy

  163. Hello Saffie Drammeh,

    Glad to hear that you enjoyed the article of securing Excel files.. Thanks for your feedback. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  164. Hello Mo Sheikh,

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

    Regards
    ExcelDemy

  165. Hello Jeff,

    Thank you for sharing your perspective! Your feedback is insightful. I understand your frustration and appreciate your feedback! While Excel may sometimes seem complex for simple tasks, there are ways to streamline your workflow, such as using Power Query or automating repetitive actions with macros. Microsoft continues to improve Excel based on user input, so your suggestions are valuable.

    Excel and LibreOffice Calc indeed cater to different user preferences and workflows. For ease of use in simpler tasks, LibreOffice may be preferable for some. However, Excel’s extensive capabilities can be invaluable for complex data analysis and automation. Balancing simplicity and functionality is always a challenge!

    If simplicity is your main goal, tutorials or add-ins might help ease the process. Don’t lose hope—there are always new tricks to make Excel work for you! Let us know if you’d like specific guidance.

    Regards
    ExcelDemy

  166. Hello Stanka,

    The issue may arise if the Microsoft Excel Object Library is not enabled in your VBA settings. This is a critical step mentioned in the article, so please ensure it has been followed:

    1. Open VBA editor (Alt + F11).
    2. Go to Tools > References.
    3. Check Microsoft Excel Object Library and click OK.
    If you’ve done this and still face issues, double-check the file path and worksheet name.

    Regards
    ExcelDemy

  167. Hello Jay,

    To ensure the checklist appends selections in any order, you can modify the code to check for and append all selected items each time the button is clicked. Here’s the updated code:

    Sub Button_Click()
        Dim buttonShape As Shape, listOption As String
        Dim M As Integer
        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"
    
            Dim resultStr As String
            Dim resultArr As Variant
            resultStr = Range("CheckListOutput").Value
    
            If resultStr <> "" Then
                resultArr = Split(resultStr, ";")
                For M = 0 To checkListBox.ListCount - 1
                    If IsInArray(checkListBox.List(M), resultArr) Then
                        checkListBox.Selected(M) = True
                    End If
                Next M
            End If
        Else
            checkListBox.Visible = False
            buttonShape.TextFrame2.TextRange.Characters.Text = "Click Here"
            listOption = ""
    
            For M = 0 To checkListBox.ListCount - 1
                If checkListBox.Selected(M) = True Then
                    listOption = listOption & checkListBox.List(M) & ";"
                End If
            Next M
    
            If listOption <> "" Then
                Range("CheckListOutput").Value = Left(listOption, Len(listOption) - 1)
            Else
                Range("CheckListOutput").Value = ""
            End If
        End If
    End Sub
    
    Function IsInArray(value As String, arr As Variant) As Boolean
        Dim element As Variant
        For Each element In arr
            If element = value Then
                IsInArray = True
                Exit Function
            End If
        Next element
        IsInArray = False
    End Function

    1. Added a loop to re-check all items dynamically.
    2. Used a helper IsInArray function that determines if an item is already in the saved list.
    3. Updates the checklist to reflect all selections dynamically.

    Regards
    ExcelDemy

  168. Hello Chansokong,

    Thank you for your comment! The #VALUE! error often occurs if there’s an issue with the formula or input data. Please double-check that all cell references are correct and that your latitude and longitude values are formatted properly. Ensure the cells don’t contain extra spaces or invalid characters.
    If the issue persists, feel free to share more details about your setup, and we’ll be happy to assist further!

    Regards
    ExcelDemy

  169. Hello Dr. Karumuri Sri Rama Murthy,

    You can download the Excel and PDF files free of cost just by providing your valid email address. To get the files go to the “Download the Practice Files” section of this post and enter your email address. Then check your email immediately after to get the download links.

    Best Regards
    ExcelDemy

  170. Hello Mridushman,

    You can download the Excel and PDF files 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

  171. Hello Mabel,

    Thank you for your kind feedback! It means a lot to us.

    Query for Overlapping Leaves: Add a helper column to concatenate names and leave dates using =A2&B2 (assuming Column A has names and Column B has leave dates).
    Use COUNTIFS to identify overlaps:

    =COUNTIFS(A:A, “Victoria”, B:B, B2) + COUNTIFS(A:A, “Hundo”, B:B, B2) + COUNTIFS(A:A, “Jonny”, B:B, B2)
    Highlight rows where the result >1.

    Public Holidays: Create a table for public holidays and link it using VLOOKUP or conditional formatting to flag dates in your tracker.
    Example formula:

    =IF(ISNUMBER(MATCH(B2, PublicHolidayRange, 0)), “Public Holiday”, “”)

    Regards
    ExcelDemy

  172. Hello Christie,

    Thanks for reaching out! To declare the database variable as a Worksheet type:

    Open your VBA Editor and ensure the variable is properly defined at the top of the module using:

    Dim database As Worksheet

    Assign it to your desired worksheet, like this:
    Set database = ThisWorkbook.Sheets(“SheetName”)

    If issues persist, ensure the sheet name is correct, and no other conflicting variables exist. Let me know if this helps!

    Best regards,
    ExcelDemy

  173. Hello David,

    Here are some article you will find more exercises.
    Excel Practice Test & Quiz
    Sample Excel File with Employee Data for Practice
    Advanced Excel Exercises with Solutions PDF

    Regards
    ExcelDemy

  174. Hello Florence,

    Thanks for your feedback. Glad to hear you learnt something new from our article. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  175. Hello Khaled,

    To create a 24/7 roster from January 1, 2025, to December 31, 2025, with 8 employees covering 3 shifts (S1: 7 AM-4 PM, S2: 1 PM-10 PM, S3: 10 PM-7 AM) and a 5/2 schedule:

    Define the Roster: Use a table in Excel with dates in rows and employees’ names in columns.
    Set Shifts: Assign shifts systematically, rotating employees to cover all shifts.
    Automate: Use formulas (like MOD) to cycle shifts and conditional formatting for visual clarity.
    Validate Coverage: Check all shifts are filled across weeks for 24/7 operation.

    Regards,
    ExcelDemy

  176. Hello Wally,

    Sorting can work after linking, but it depends on how the data is linked. If you’re linking to an external workbook and sorting the linked data in your current workbook, the formulas or references should still function correctly as long as the external workbook remains accessible.

    However, be cautious—sorting the linked data within the current workbook might alter references if you’re using formulas like INDEX or VLOOKUP based on a specific order. To avoid issues, it’s a good idea to ensure the linked workbook is open and double-check the formulas after sorting.

    Let me know if you need further assistance!

    Best Regards,
    ExcelDemy

  177. Hello Usman,

    You can download the practice file from here:
    When to Use Array Formulas vs. Dynamic Arrays in Excel.xlsx

    Regards
    ExcelDemy

  178. Hello Katrina,

    The issue lies in how COUNTIFS processes criteria. The function doesn’t accept logical operators directly without enclosing them in quotes. To fix the formula, modify it like this:

    =COUNTIFS(D2:D9, “[UIC]”, E2:E9, “>” & TODAY())

    1. [UIC] is a placeholder; ensure it matches values in D2:D9 or use a cell reference (e.g., A1).
    2. “& TODAY()” ensures the date comparison is dynamically evaluated.
    Double-check that E2:E9 contains valid dates and aligns with the D2:D9 range.

    Regards
    ExcelDemy

  179. Hello Chris,

    Thank you for your detailed feedback! We’ve updated the article to provide clearer explanations for how the formula works, addressing your observations. Here’s a summary:

    1. FIND and LEFT are used to locate and extract the address number.
    2. VALUE checks if the first character is numeric.
    3. ISERROR and IF determine if an address number exists and return it if valid.

    We appreciate your input and invite you to revisit the updated explanation. Thank you for helping us improve!

    Regards
    ExcelDemy

  180. Hello Rose,

    You can copy the code from the article. Or if you want to copy the code from our Excel workbook then follow the steps below.
    Open Visual Basic from the Developer tab.
    Open the Sheet1 to get the code.

    Regards
    ExcelDemy

  181. Hello Ondrej,

    Thank you for sharing your detailed requirements. I understand that you’re looking to consolidate specific data from multiple RUZ101*.CSV files located within date-named folders into a single Excel workbook. Given the structure and constraints you’ve described, I can provide you with a VBA macro that automates this process.

    Ensure that all your RUZ101*.CSV files are organized within a main directory, with each set of files placed in subfolders named by date (e.g., 2023/230414/). The folder structure should look something like this:

    Sub ConsolidateRUZ101Data()
        Dim MainFolder As String
        Dim DateFolder As String
        Dim FileName As String
        Dim wsMaster As Worksheet
        Dim wsTemp As Worksheet
        Dim LastRow As Long
        Dim StartRow As Long
        Dim EndRow As Long
        Dim i As Long
        Dim DateValue As String
        Dim FCol As Range
        Dim Cell As Range
        
        ' Set the path to your main directory
        MainFolder = "C:\Path\To\Your\MainDirectory\" ' <-- Change this to your main directory path
        
        ' Set the master worksheet
        Set wsMaster = ThisWorkbook.Sheets("Sheet1") ' Ensure you're on the correct sheet
        
        ' Initialize master sheet headers
        wsMaster.Cells.Clear
        wsMaster.Range("A1").Value = "Date"
        wsMaster.Range("B1").Value = "Data"
        LastRow = 2
        
        ' Loop through each date folder
        DateFolder = Dir(MainFolder, vbDirectory)
        Do While DateFolder <> ""
            If DateFolder <> "." And DateFolder <> ".." Then
                If (GetAttr(MainFolder & DateFolder) And vbDirectory) = vbDirectory Then
                    ' Loop through each RUZ101*.CSV file in the date folder
                    FileName = Dir(MainFolder & DateFolder & "\RUZ101*.CSV")
                    Do While FileName <> ""
                        ' Open the CSV file
                        Workbooks.Open Filename:=MainFolder & DateFolder & "\" & FileName, Format:=xlCSV
                        Set wsTemp = ActiveSheet
                        
                        ' Find the cell containing "Tagesbericht"
                        Set FCol = wsTemp.Columns("F")
                        For Each Cell In FCol
                            If Cell.Value = "Tagesbericht" Then
                                StartRow = Cell.Row
                                ' Get the date from 3 rows above
                                DateValue = wsTemp.Cells(StartRow - 3, "F").Value
                                
                                ' Find the end row containing "Bericht gelöscht"
                                For i = StartRow To wsTemp.Rows.Count
                                    If wsTemp.Cells(i, "F").Value = "Bericht gelöscht" Then
                                        EndRow = i
                                        Exit For
                                    End If
                                Next i
                                
                                ' Extract data between StartRow and EndRow
                                If EndRow > StartRow Then
                                    For i = StartRow + 1 To EndRow - 1 ' Assuming data is between these rows
                                        wsMaster.Cells(LastRow, "A").Value = DateValue
                                        wsMaster.Cells(LastRow, "B").Value = wsTemp.Cells(i, "F").Value
                                        LastRow = LastRow + 1
                                    Next i
                                End If
                                
                                Exit For ' Exit after finding the first "Tagesbericht"
                            End If
                        Next Cell
                        
                        ' Close the CSV file without saving
                        Workbooks(FileName).Close SaveChanges:=False
                        ' Get next file
                        FileName = Dir
                    Loop
                End If
            End If
            ' Get next folder
            DateFolder = Dir
        Loop
        
        MsgBox "Data consolidation complete!", vbInformation
    End Sub

    Replace “C:\Path\To\Your\MainDirectory\” with the actual path to your main directory containing the date-named folders.
    The macro assumes that Sheet1 in your master workbook is where you want the consolidated data. If not, change “Sheet1” to the appropriate sheet name.

    Regards
    ExcelDemy

  182. Hello Floyd,

    Thanks for your appreciation, it means a lot to us. I’m glad to hear that you found this material so helpful. It’s great knowing the guide provided exactly what you were looking for.
    If you need further guidance or have any specific questions, don’t hesitate to reach out—happy to help! Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  183. Hello Mabel,

    Thank you for your questions!

    Color coding leave types: You can use conditional formatting in Excel. Create rules based on leave type values (e.g., HD, SP, etc.) and assign different colors to each.

    Pre-inputting public holidays: List the public holidays in a separate range and use data validation to restrict leave selections to avoid those dates.

    Preventing overlapping leave: You can use formulas like COUNTIFS to track the leave dates for specific employees and highlight conflicts.

    Regards
    ExcelDemy

  184. Hello Jack Holuta,

    To identify the Class Name, you can inspect the HTML source code of the webpage you’re trying to scrape. Right-click on the element you want to extract and select “Inspect” or “Inspect Element” in your browser. This will open the Developer Tools, where you can locate the HTML code for the element and find its class name (often within a class=”…” attribute). From there, you can extract the required information using VBA.

    Regards
    ExcelDemy

  185. Hello Jack Holuta,

    To identify the Class Name, you can inspect the HTML source code of the webpage you’re trying to scrape. Right-click on the element you want to extract and select “Inspect” or “Inspect Element” in your browser. This will open the Developer Tools, where you can locate the HTML code for the element and find its class name (often within a class=”…” attribute). From there, you can extract the required information using VBA.

    Regards
    ExcelDemy

  186. Hello Arpit,

    Thanks for sharing your observation! It seems like the method works well for groups with a maximum of 500 members. For larger groups, you might need to explore other techniques or split the process into smaller chunks to extract all contacts.
    If you’re trying to extract contacts from a larger WhatsApp group, you can use WhatsApp’s API (with proper permissions) to export group members. Alternatively, you might consider using third-party tools or scripts designed to pull contacts, though you should ensure they comply with WhatsApp’s terms of service. Keep in mind that automation might be limited by WhatsApp’s restrictions to prevent spam.

    Regards
    ExcelDemy

  187. Hello Rob,

    Thanks for sharing your experience! You’re absolutely right—when freezing panes for both rows and columns, selecting the cell after the row and column you want to freeze is essential. In your case, selecting D5 allows the desired categories to remain visible while enabling the rest of the sheet to scroll. This method offers a great way to keep key information in view.

    Regards
    ExcelDemy

  188. Hello Mentor,

    You will need to modify the VBA function to return only the city and country from the OpenStreetMap response, update the code as follows:

    Adjust the URL to request JSON data instead of XML by adding &format=json. Parse the JSON response to extract only the “city” and “country” fields.
    Here’s the updated VBA code:

    
    Option Explicit
    Function ReverseGeocode(lati As Double, longi As Double) As String
        On Error GoTo ErrorHandler
        Dim http As Object, json As Object
        Dim URL As String
        URL = "https://nominatim.openstreetmap.org/reverse?lat=" & CStr(lati) & _
              "&lon=" & CStr(longi) & "&format=json"
        
        ' Create and send the HTTP request
        Set http = CreateObject("MSXML2.XMLHTTP.6.0")
        http.Open "GET", URL, False
        http.send
        
        ' Parse the JSON response
        Set json = JsonConverter.ParseJson(http.responseText)
        
        ' Extract city and country
        Dim city As String, country As String
        city = json("address")("city")
        country = json("address")("country")
        
        ' Return formatted result
        ReverseGeocode = city & ", " & country
        Exit Function
        
    ErrorHandler:
        ReverseGeocode = "Error: " & Err.Description
    End Function

    Important Notes:
    Ensure that you have a JSON parser installed in VBA (e.g., VBA-JSON).
    This code returns only the city and country as requested.

    Regards
    ExcelDemy

  189. Hello Nicolas,

    Thanks for your appreciation. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  190. Hello AM,

    Thanks for your appreciation. To keep leave data aligned with the correct employee after sorting, refer the names from the Month sheet into the summary sheet. This way, the data stays tied to each employee regardless of sorting changes.
    Alternatively, consider using Excel’s INDEX-MATCH or XLOOKUP functions to pull data dynamically based on employee IDs, which would adjust automatically if names are resorted.

    Regards
    ExcelDemy

  191. Hello TeresaT,

    You are most welcome. To adjust the formula for looking up multiple instances of the same product, try using FILTER with a lookup. For example:

    =FILTER(SalesTable[Sales Date], SalesTable[Product] = “Monitor”)

    This will return all sales dates for “Monitor” instead of just the first match. If you want to list all matches dynamically, you can also add an array formula to pull all relevant sales data across multiple rows. Let me know if you need further customization!

    Regards
    ExcelDemy

  192. Hello David Berg,

    You’re absolutely right! In this case, using relative ranges with MIN and MAX should work just fine.

    We used absolute references here to keep the range fixed ($D$5:$D$6), ensuring consistency when calculating the difference across cells. This approach avoids errors that could arise if the range were to shift when copying the formula to other cells, which is important in contexts where you want consistent values from the same range.

    Regards
    ExcelDemy

  193. Hello Malene,

    Thank you for your kind words!

    The alignment issue with the orange dots may be due to differences in axis scales or alignment settings. Try adjusting the primary and secondary axis settings to ensure they match, or use error bars to manually position the dots to align with the blue bars. You could also double-check the formatting options in the “Format Data Series” menu. Let me know if this helps!

    Regards
    ExcelDemy

  194. Hello TJ,

    Yes, there is a way to automate your parts breakdown in Excel by using a combination of formulas.

    If your master document has a detailed breakdown of parts for each item, and you want to pull this breakdown dynamically into your daily order list, here’s a simple approach using the FILTER function (available in Excel 365 and Excel 2019) or the VLOOKUP function combined with helper columns.

    If your breakdown list is well-structured, the FILTER function can pull in all matching parts for a given item. In a cell where you want the parts listed, you can use:

    =FILTER(Master!B2:D100, Master!A2:A100 = OrderList!A2, “No parts found”)

    Replace Master!B2:D100 with the range containing your parts details, and Master!A2:A100 with the range containing item names in your master list. OrderList!A2 would be the item name in your daily order sheet.

    Let me know if you’d like more detailed steps on any of these methods!

    Best Regards,
    ExcelDemy

  195. Hello John,

    To compare two worksheets follow the given steps:

    Day 1 (Worksheet 1):
    Cell B6: 304

    Day 2 (Worksheet 2):
    Cell B6: 412

    Cell C6 (Difference): 108

    The formula in cell C6 of Worksheet 2 would be:

    =Sheet2!B6 – Sheet1!B6

    This will display 108 in cell C6 as the difference between 412 (Day 2) and 304 (Day 1). If Day 2’s value is lower, the result would automatically show as a negative.

    Regards
    ExcelDemy

  196. Hello Vince,

    Thank you for your feedback! We appreciate your suggestion and understand that varied delimiters like “/” and “\” could enhance clarity. Our aim was to keep a consistent format to ensure easy-to-follow instructions, but we’ll definitely consider incorporating diverse delimiters in future examples to better suit user needs. Thanks again for helping us improve!

    Regards
    ExcelDemy

  197. Hello Kevin O’Boyle,

    To achieve this, you can use the TEXTJOIN function to concatenate notes with the dates for each subject. Here’s an approach you might try:

    1. In the footer cell for each subject, use TEXTJOIN with the delimiter you want (like a line break).
    2. Format your entries using a combination of TEXT (for the date) and CONCATENATE (for text and notes).
    Example formula for the footer cell:

    =TEXTJOIN(CHAR(10), TRUE, IF(A2:A10<>“”, TEXT(A2:A10, “mmm d, yyyy”) & ” ” & B2:B10 & ” (entered on ” & TEXT(A2:A10, “m/d/yy”) & “)”, “”))
    Replace A2:A10 with your date range and B2:B10 with notes. This will list each entry on a new line with the formatted date and note text.

    Regards
    ExcelDemy

  198. 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

  199. Hello Peter,

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

    Regards
    ExcelDemy

  200. 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

  201. 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

  202. Hello Dear,

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

    Regards
    ExcelDemy

  203. 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

  204. 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

  205. 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

  206. 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

  207. 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

  208. Hello Bikash Neupane,

    You are most welcome. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  209. Hello Manpreet Kaur kaur,

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

    Regards
    ExcelDemy

  210. 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

  211. 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

  212. 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

  213. 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

  214. 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

  215. 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

  216. 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

  217. Hello,

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

    Regards
    ExcelDemy

  218. Hello Asger,

    Please download the Excel file to get the fresh code.

    Regards
    ExcelDemy

  219. 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

  220. 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

  221. 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

  222. 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

  223. 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

  224. 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

  225. Hello Rebecca A,

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

    Regards
    ExcelDemy

  226. 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

  227. Hello,

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

    Regards
    ExcelDemy

  228. 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

  229. 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

  230. 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

  231. 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

  232. Hello Spencer Kapazira,

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

    Regards
    ExcelDemy

  233. 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

  234. 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

  235. 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

  236. 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

  237. 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

  238. 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

  239. Hello,

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

    Regards
    ExcelDemy

  240. 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

  241. 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

  242. 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

  243. 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

  244. 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

  245. 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

  246. 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

  247. 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

  248. 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

  249. 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

  250. 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

  251. 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

  252. Hello Lethabo Mokoti,

    You are most welcome. Keep learning Excel with ExcelDemy!

    Regards
    ExcelDemy

  253. 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

  254. 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

  255. 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

  256. Hello,

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

    Regards
    ExcelDemy

  257. 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

  258. 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

  259. 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

  260. Hello Van Weaver,

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

    Regards
    ExcelDemy

  261. 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

  262. Hello,

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

    Regards
    ExcelDemy

  263. 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

  264. 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

  265. 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

  266. 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

  267. Hello,

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

    Regards
    ExcelDemy

  268. Hello,

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

    Regards
    ExcelDemy

  269. 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

  270. 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

  271. 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

  272. 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

  273. 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

  274. 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

  275. 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

  276. 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

  277. 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

  278. 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

  279. 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

  280. 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

  281. 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

  282. 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

  283. 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

  284. 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

  285. 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

  286. 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

  287. 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

  288. 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

  289. 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

  290. 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

  291. 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

  292. 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

  293. 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

  294. 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

  295. 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

  296. 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

  297. 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

  298. 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

  299. 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

  300. 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

  301. Hello Eduardo,

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

    Regards
    ExcelDemy

  302. 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

  303. 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

  304. Hello Raj,

    Thanks for your appreciation. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  305. 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

  306. 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

  307. Hello,

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

    Regards
    ExcelDemy

  308. Hello,

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

    Regards
    ExcelDemy

  309. Hello Louis,

    Thanks for your appreciation. Keep exploring Excel with ExcelDemy!

    Regards
    ExcelDemy

  310. 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

  311. 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

  312. 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

  313. 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

  314. 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

  315. 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

  316. Hello AT William,

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

    Regards
    ExcelDemy

  317. 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

  318. 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

  319. 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

  320. 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

  321. 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

  322. 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

  323. 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

  324. 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

  325. 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

  326. 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

  327. 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

  328. 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

  329. Hello,

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

    Regards
    ExcelDemy

  330. 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

  331. 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

  332. 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

  333. 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

  334. 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

  335. 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

  336. 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

  337. 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

  338. 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

  339. 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

  340. Hello Esther,

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

    Regards
    ExcelDemy

  341. Hello Nguyen,

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

    Regards
    ExcelDemy

  342. 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

  343. 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

  344. 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

  345. 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

  346. 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

  347. 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

  348. Hello Akhila,

    Here is the sample dataset of Healthcare Insurance Claim Patients

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

    Regards
    ExcelDemy

  349. 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

  350. 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

  351. 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

  352. 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

  353. 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

  354. 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

  355. 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

  356. 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

  357. Hello Shimmy,

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

    Regards
    ExcelDemy

  358. 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

  359. 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

  360. 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

  361. 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

  362. 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

  363. 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

  364. 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

  365. 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

  366. 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

  367. 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

  368. 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

  369. 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

  370. 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

  371. 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

  372. Hello Ahmed,

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

    Regards
    ExcelDemy

  373. 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

  374. 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

  375. 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

  376. 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

  377. 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

  378. 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

  379. 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

  380. 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

  381. 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

  382. 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

  383. 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

  384. 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

  385. 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

  386. 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

  387. 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

  388. Hello Nidhi,

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

    Regards
    ExcelDemy

  389. 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

  390. 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

  391. 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

  392. 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

  393. Hello Mr. John,

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

    Regards
    ExcelDemy

  394. 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

  395. 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

  396. 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

  397. 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

  398. 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

  399. 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

  400. 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

  401. Hello Paul,

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

    Regards
    ExcelDemy

  402. 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

  403. 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

  404. 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

  405. 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

  406. 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

  407. 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

  408. 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

  409. 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

  410. 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

  411. 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

  412. 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

  413. 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

  414. 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

  415. 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

  416. 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

  417. 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

  418. 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

  419. Hello Yufeng,

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

    Regards
    ExcelDemy

  420. 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

  421. 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

  422. 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

  423. 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

  424. 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

  425. 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

  426. 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

  427. 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

  428. 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

  429. 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

  430. 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

  431. 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

  432. 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

  433. 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

  434. 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

  435. 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

  436. 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

  437. 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

  438. 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

  439. 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

  440. 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

  441. 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

  442. 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

  443. 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

  444. 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

  445. 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

  446. 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

  447. 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

  448. 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

  449. 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

  450. 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

  451. 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

  452. 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

  453. 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

  454. 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

  455. 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

  456. 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

  457. 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

  458. 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

  459. 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

  460. 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

  461. 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

  462. 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

  463. 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

  464. 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

  465. 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

  466. 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

  467. 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

  468. 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

  469. 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

  470. 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

  471. 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

  472. 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