Shamima Sultana

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

Hello KJ,

You can absolutely achieve this using the VLOOKUP function. Since your ID Numbers are the common link between both sheets, VLOOKUP can help pull the “Date Built” from the second sheet to the first.

Here’s a step-by-step example:
1. Suppose your First Sheet is named DataSheet and the Second Sheet is BuildSheet.
2. In BuildSheet, column A has ID Numbers and column B has the corresponding Date Built.
3. In DataSheet, you want to pull the Date Built next to the ID Number in column A.
Use this formula in DataSheet (assuming ID is in A2):

=VLOOKUP(A2, BuildSheet!A:B, 2, FALSE)
This tells Excel to look up the ID from DataSheet!A2 in BuildSheet!A:A, and return the matching value from column B (the second column), where the Date Built is.

If you want to avoid errors in case an ID is missing, wrap it with IFERROR:
=IFERROR(VLOOKUP(A2, BuildSheet!A:B, 2, FALSE), “Not Found”)

Let me know if your sheet names or column structure are different—I’d be happy to adjust the formula for you!

Regards
ExcelDemy

​Hello Nat,​

Thanks for reaching out!​ To build a monthly appointment schedule for your clients in Excel, a great starting point is this detailed guide:​
How to Create a Monthly Schedule in Excel (3 Simple Ways)​

This article walks you through three practical methods:​
Using Excel Templates: Quickly set up a schedule by customizing built-in templates.
Manual Creation: Design a schedule from scratch with full control over layout and formatting.
Interactive Combo Box: Add dropdowns to switch between months dynamically.​

Each method includes step-by-step instructions with visuals, making it easy to follow along.​ If you need a downloadable template or further assistance customizing it for your specific needs, feel free to ask!

Regards
ExcelDemy

Hello Ahmed,

Thank you for the clarification! You’re absolutely right, Example 5 demonstrates replacing one value across the entire workbook, while Example 4 shows how to replace multiple values but only within a single sheet.

To achieve what you’re asking—replacing multiple values from a list across the entire workbook—you can combine the logic from both examples. Here’s a simple version of the code that does exactly that:

Sub ReplaceMultipleValuesWorkbook()
    Dim ws As Worksheet
    Dim lookupRange As Range
    Dim cell As Range
    Dim findValue As Variant, replaceValue As Variant
    Dim i As Long

    ' Set your lookup table range (assumes it's on Sheet1, columns A and B)
    Set lookupRange = Worksheets("Sheet1").Range("A2:B10") ' Adjust range as needed

    ' Loop through each sheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then ' Skip the sheet with the lookup table
            For Each cell In ws.UsedRange
                For i = 1 To lookupRange.Rows.Count
                    findValue = lookupRange.Cells(i, 1).Value
                    replaceValue = lookupRange.Cells(i, 2).Value
                    If cell.Value = findValue Then
                        cell.Value = replaceValue
                    End If
                Next i
            Next cell
        End If
    Next ws
End Sub

What it does:
1. Looks for a list of values in Sheet1!A2:B10 (column A = find, column B = replace).
2. Applies replacements in all other worksheets.

Let me know if you need help customizing the lookup range or skipping certain sheets!

Regards
ExcelDemy

Hello F. Snipes,

Thank you for your question! To calculate a 5-year loan with an 8% annual interest rate using Excel, you can use the PMT function for equal monthly payments or create a loan amortization schedule using the reducing balance method.

Here’s a quick method using PMT:
=PMT(8%/12, 5*12, -Loan_Amount)

1. 8%/12 is the monthly interest rate.
2. 5*12 is the total number of payments (months).
3. Loan_Amount is the total loan you took (make sure it’s entered as a positive number).

Regards
ExcelDemy

Hello Tolani,

You are most welcome. Thanks for your feedback, we appreciate it. Keep exploring Excel with ExcelDemy!

Regards
ExcelDemy

Hello Mohammad Afzal,

Walaikum-assalam. Thank you for your kind feedback! We’re glad you found the tutorial helpful. It’s great to know you learnt Power BI, surely adds value to how you engage with these tutorials.

You’re right that Power BI adoption varies across organizations, but it’s encouraging to see interest like yours continuing strong.

We regularly publish tutorials under our Power BI Basics, feel free to explore and stay updated with the latest content.

Regards
ExcelDemy

Hello Theodor Lowe,

Thank you for reaching out, and we’re sorry to hear you’re having trouble with the download.

Please ensure the email address was entered correctly. Sometimes, there may be a slight delay in receiving the file link. If you’ve already checked your spam/junk folder and still haven’t received it, we’d be happy to assist further.

Can you please try again:
1. Enter your Name:
2. Enter your Email:
3. Click Download.

Kindly share your email with us, and we’ll send the file to you manually. We appreciate your patience and interest in our resources!

Regards
ExcelDemy

Hello Dick Baker,

Thank you for your comment and for sharing your perspective!

You’re absolutely right, while IFERROR() and IFNA() are incredibly useful for handling error scenarios, the absence of a built-in IFBLANK() function does leave a noticeable gap for handling blank cells more efficiently. As you mentioned, having to call functions like VLOOKUP() twice just to manage blanks is not optimal, especially for larger datasets where performance matters.

We agree that a native IFBLANK() function would simplify formulas and improve usability. Your suggestion is valid, and we hope Microsoft considers incorporating such a feature in future Excel updates.

Thanks again for your thoughtful input, it adds great value to the discussion!

Regards
ExcelDemy

Hello Rebecca,

Thanks for your kind words, Rebecca! I’m glad to hear that the steps helped you figure it out. Feel free to reach out if you need further assistance or have more questions. Happy Excel-ing!

Regards
ExcelDemy

Hello Matthew,

You are most welcome. To count the number of activities containing both words, or more than one, you can use the COUNTIFS function to account for multiple criteria in one formula. Since the order of the words is not fixed, the wildcard * can be used to match any word before or after the specified criteria.

Here’s how you can modify your formula:

For activities that contain both “animal” and “food”, use:

=COUNTIFS(‘activity list’!$A$1:$A$500, “*animal*”, ‘activity list’!$A$1:$A$500, “*food*”)
This formula will count activities that contain both “animal” and “food” in any order within the cell.

If you want to extend this to count activities that contain all three words (“animal”, “food”, and “habitat”), use:

=COUNTIFS(‘activity list’!$A$1:$A$500, “*animal*”, ‘activity list’!$A$1:$A$500, “*food*”, ‘activity list’!$A$1:$A$500, “*habitat*”)
The COUNTIFS function allows you to add as many criteria as needed, just make sure each word has its corresponding condition in the formula.

Regards
ExcelDemy

Hello John Folkard,

Thanks, John, for pointing that out! You’re absolutely right, if array is used before it’s properly declared and dimensioned, VBA will throw a “Sub or Function not defined” error, especially if the code mistakenly tries to call array like a procedure.

Please ensure:
1. Dim array() As Variant is declared at the top of the module (outside any Sub).
2. ReDim array(7 To 42, lastCol) is called before any use of array.

That array is not accidentally written as array() in a line by itself, which would be interpreted as a call to a procedure named array.

Regards
ExcelDemy

Hello Isukpong Sunday,

Please share your workbook in our ExcelDemy Forum with proper explanation. We would be happy to help you.

Regards
ExcelDemy

Hi Sarah MacDermott,

Thank you so much for your kind words! I’m really glad the tracker was helpful to you so far.

What you’re describing is absolutely possible in Excel, and it’s a very meaningful use case. With a few adjustments, we can definitely adapt the template to suit your specific needs.

From what you explained, you need:
1. A way to input multiple service types (face-to-face, non-face-to-face, travel),
2. A system to log each service with time and date,
3. A formula to calculate the cost per service (based on time and rate),
4. And most importantly, a running balance that carries over across billing periods and updates in real time.

This can be done using a combination of formulas like IF, SUMIFS, INDEX, MATCH, and a well-organized table layout.

I’d be happy to walk you through the steps or help you create a customized version of the tracker. Since you mentioned your Excel skills are basic, I can keep the instructions simple and visual.

Regards
ExcelDemy

Hello Robert,

You’re absolutely right! When you zoom in too much, Excel may temporarily switch to system-default fonts for performance reasons. Zooming out can help the actual font render correctly again. It’s a helpful workaround!

Regards
ExcelDemy

Hello Andrew,

Thanks for sharing! This is the custom VBA function from the article that adds leading zeros, very handy!
Just a small note: make sure to use a regular minus sign – instead of the long dash – in the String(iPack – Len(sSource), “0”) part to avoid syntax errors in the VBA editor.

Regards
ExcelDemy

Hello Kevin J Brooks,

Thanks for your comment. Could you clarify what you meant regarding the Related Articles and Compliance? We’d love to hear your thoughts!

Regards
ExcelDemy

Hello Rob,

Yes, it’s definitely possible to apply conditional formatting to weekend days even when using the “Long Date” format like “Sunday, April 6, 2025”. The formatting is based on the actual date value, not the display format. You can use a formula like:

=OR(WEEKDAY(A1)=1, WEEKDAY(A1)=7)

This checks if the date in cell A1 is a Saturday (7) or Sunday (1). Just apply this formula in the conditional formatting rule, and it will highlight weekends regardless of whether the date is shown in long or short format.

Regards
ExcelDemy

Hello,

Thanks for your feedback. Glad to hear you that you liked our article. Keep exploring Excel with ExcelDemy!

Regards
ExcelDemy

Hello Prema,

That’s a clever approach! Using the NOW() function in a cell and referencing it in a drop-down menu is a neat workaround for inserting timestamps. It’s great to see how you’ve combined Excel’s built-in functionality with your experience using VBA macros for a simple solution. Thanks for sharing!

Regards
ExcelDemy

Hello,

You are most welcome. Thanks for your feedback and appreciation. Glad to hear that you found our article’s step-by-step explanation helpful. Try this in your project and let us know.

Regards
ExcelDemy

Hello Usman,

Thank you so much! I’m really glad you found it useful. I’ve now attached the sample Excel file to help you practice better. Feel free to download it from the article, hope it helps! Let me know if you have any questions.

Regards
ExcelDemy

Hello Rajesh,

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

Regards
ExcelDemy

Hello KT,

You are most welcome. Thanks for your feedback and appreciation. Glad to hear that our article saved your hours of copy and pasting.

Regards
ExcelDemy

Hello Emily,

Thanks for following up. Sure! Let me explain more clearly how to get the mouse wheel to work in a ListBox created in VBA (UserForm) using ActiveX control.

Step-by-Step Setup
Step 1:
1. Insert a UserForm and ListBox
2. Press Alt + F11 to open the VBA Editor.
3. Right-click on your project > Insert > UserForm.
4. From the toolbox, drag and drop a ListBox onto the UserForm. (This is an ActiveX-style ListBox by default in UserForms.)
5. Name it ListBox1 if it’s not already.

Step 2:
1. Create a Class to Handle Scroll-Like Behavior
2. In the VBA editor, go to Insert > Class Module.
3. In the Properties window (F4), change the name to clsMouseWheel.

Paste this code into the class:

' In Class Module: clsMouseWheel
Public WithEvents LB As MSForms.ListBox

Private Sub LB_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyPageUp Then
        LB.TopIndex = Application.WorksheetFunction.Max(0, LB.TopIndex - 5)
    ElseIf KeyCode = vbKeyPageDown Then
        LB.TopIndex = LB.TopIndex + 5
    End If
End Sub

Step 3:
1. Connect the Class in Your UserForm
2. In the UserForm code, add this:

Dim mw As clsMouseWheel

Private Sub UserForm_Initialize()
    Set mw = New clsMouseWheel
    Set mw.LB = Me.ListBox1

    ' Fill the ListBox with example data
    Dim i As Integer
    For i = 1 To 100
        Me.ListBox1.AddItem "Item " & i
    Next i
End Sub

Important Notes:
1. Unfortunately, VBA UserForms do not natively support the mouse wheel unless you use advanced Windows API calls (which get quite technical and need 32-bit vs 64-bit handling).
2. This solution mimics scrolling by using the Page Up / Page Down keys. It’s the most stable workaround in standard VBA.

If you’re not seeing the ListBox scroll or can’t activate it:
1. Double-check that you’re using a UserForm ListBox (not one directly on a worksheet).
2. Ensure ActiveX controls are enabled (especially if your Excel settings or security options are blocking them).

Regards
ExcelDemy

Hello Meghna Desai,

Great question. To exclude rows containing specific text like “pant” using Excel’s Advanced Filter, you can use the following setup in your criteria range:

If your column is called Item, then:
1. In the criteria range, use the same column header: Item
2. In the row below, enter this formula:
<>*pant*
This tells Excel to filter only rows not containing the word “pant”. The * is a wildcard, and <> means “not equal to”. So <>*pant* excludes any text that has “pant” anywhere in it.

Then apply the Advanced Filter as usual:
1. Go to the Data tab → Advanced (in the Sort & Filter group).
2. Set your List range and Criteria range.
3. Choose to filter in place or copy to another location.

Another Solution:
1. Add a new row above your data for criteria.
2. In the first cell of your criteria row (say, cell F1), type a different header than your data (e.g., Criteria).
3. In F2, enter the following formula (assuming your “Item” column is column C):
=ISERROR(SEARCH(“pant”, C2))
Then use the Advanced Filter, selecting this criteria range.

This formula will include only rows where “pant” is NOT found. The key is using SEARCH (to find “pant”) and wrapping it in ISERROR to exclude those matches.

Regards
ExcelDemy

Hello Mr1098,

You are most welcome. Thanks for your feedback and appreciation. Glad to hear that our post helped you a lot. Keep exploring Excel with ExcelDemy!

Regards
ExcelDemy

Hello Ng Chin Hwa,

You can split “1000 – 1200” into two cells using Excel’s Text to Columns feature:

1. Select the cell with “1000 – 1200”.
2. Go to the Data tab → Click Text to Columns.
3. Choose Delimited → Click Next.
4. Check the Other box and enter the dash (–) as the delimiter.
5. Click Finish.

Now “1000” will go into one cell and “1200” into the next!

Regards
ExcelDemy

Hello Ryan,

You are most welcome and glad to hear the VBA code worked for most of your SKUs!

For the two barcodes you’re having trouble with — especially those containing double zeroes — it’s likely due to how the Code 128 encoding handles certain characters or control codes.

The strings you shared (Íq Sq,>oÎ and Íu B[hX{Î) are the encoded representations, but barcode scanners sometimes have issues if the font isn’t correctly applied or if the encoding doesn’t match the expected format (like Code 128 Auto vs. Code 128 Subset B).

Here are a few quick tips that might help:
1. Make sure the barcode font (e.g., “Code 128”) is applied to the encoded text only, not the raw number.
2. Try adding Start and Stop characters (usually Start B and Stop for Code 128), but the VBA might already do this.
3. Ensure your scanner is configured to read Code 128 barcodes (some scanners let you toggle symbologies).
4. Try increasing the font size or printing the barcode in higher resolution—blurry or small barcodes can fail to scan.

Let me know if you’d like help tweaking the VBA code or testing these two SKUs more closely! Happy to help further.

Regards
ExcelDemy

Hello Stephen Dolson,

Thanks for your comment! You’re right! the calculator uses 67 as the default Full Retirement Age (FRA), but you can easily change it to 66 years and 8 months.

Here’s how to do it, even if you’re not an Excel expert:

Convert your FRA to months:
66 years × 12 = 792 months
792 + 8 months = 800 months

In the spreadsheet, look for any formulas or cells that use 804 (which is 67 years × 12 months) and replace it with 800.

This small change will adjust the break-even calculation to match your correct FRA. If you’d like, I can walk you through exactly where to update it!

Regards
ExcelDemy

Hello Graham,

Great question! If the workbook is already open, it’s generally better to reference it directly instead of reopening it, as reopening can cause issues like hangs or multiple instances. You can check if it’s open using a loop through Workbooks, and then reference it accordingly.

Also, keeping it open while your primary workbook is active (especially if you’ll need it again) can reduce overhead and avoid unexpected behavior. Just make sure to manage memory and references properly to prevent Excel from hanging. The issue you’re seeing might be related to trying to reopen an already open workbook in a new Sub without checking its state first.

Regards
ExcelDemy

Hello Джон,

It sounds like you’re looking for something more advanced, like a true 3D vector table rather than just a visual effect. A table of vectors where each cell represents a 1D matrix along the Z-axis is a neat idea!
Excel’s limitations make it tough to perform complex manipulations like CFD directly, but Octave or other specialized software could certainly handle that. It’s fascinating how you’re thinking of expanding beyond Excel’s typical use cases!

Regards
ExcelDemy

Hello Raymond Martin,

Thanks for your comment! It sounds like the small size of your first market (0.45%) could be causing visibility issues in the chart. One suggestion is to adjust the axis scaling or perhaps consider combining smaller markets into a grouped category for better clarity.

Regarding the issue with the midpoints, it could be worth reviewing the data format and ensuring that all columns and markets align correctly. With 10 columns and markets, it’s important to ensure there aren’t any data inconsistencies that might affect the outcome.

Feel free to share more details if you need further assistance!

Regards
ExcelDemy

Hello Abraham Poo,

Thank you for your kind words! I truly appreciate your feedback.

Regarding your question about the alpha (scale) value of 1,807.811, it was derived using the trendline equation from the Weibull probability plot in Excel. The steps to obtain it are:

Plot the data using the natural logarithm of failure times on the x-axis and the double natural logarithm of (1 – median rank) on the y-axis.
Add a trendline to the scatter plot and select the linear regression option to obtain the equation of the line.
The intercept of the trendline represents the log of the scale parameter (ln(α)).

To get alpha (α), apply the exponential function:
= intercept

If you’re using Excel, you can calculate it with the formula:
=EXP(intercept_value)

If you’re getting a different result, please check whether you’re correctly applying Bernard’s approximation for the median rank and ensure that the trendline equation is displayed in the correct format. Let me know if you need further clarification!

Best regards,
ExcelDemy

Hello Tibor,

Yes, it’s absolutely possible to create an Excel application for work registration with start and stop times. You can achieve this using Excel formulas or VBA macros for automation.

Simple Approach with Formulas
Use three columns: Start Time, Stop Time, and Duration
Use the formula:
=B2-A2
This will calculate the time difference when the start and stop times are recorded.

Automated Approach with VBA
If you want buttons to start and stop the timer automatically, you can use the following VBA script:

VBA Script for Start/Stop Button
1. Open Excel and press ALT + F11 to open the VBA Editor.
2. Click Insert > Module and paste the following code:

Dim startTime As Double

Sub StartWork()
    startTime = Now
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = startTime
End Sub

Sub StopWork()
    Dim stopTime As Double
    stopTime = Now
    Dim lastRow As Integer
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("B" & lastRow).Value = stopTime
    Range("C" & lastRow).Formula = "=B" & lastRow & "-A" & lastRow
End Sub

How It Works
1. The StartWork macro records the start time in column A.
2. The StopWork macro records the stop time in column B and calculates the time difference in column C.
3. Each work session is saved in a new row.
How to Use It
Create two buttons:
1. Assign “StartWork” to the “Start” button
2. Assign “StopWork” to the “Stop” button
3. Click “Start” at the beginning of a task, then “Stop” when finished.

This will create a simple work log in Excel.

Regards
ExcelDemy

Hello Smoosh

You’re absolutely right! This method imports data from an Excel file into Google Sheets, but it doesn’t establish a live sync between the original Excel file and Google Sheets.
You can check out this article for Sync Excel to Google Sheets.

If you’re looking for real-time synchronization, you might need to use Microsoft Power Automate, Google Apps Script, or third-party integration tools like Zapier.

Let me know if you need further help.

Regards
ExcelDemy

Hello Abdul,

You are most welcome. You’re absolutely right—if the structure of the website changes frequently, the setup may need adjustments accordingly. However, there are a few ways to handle this more efficiently:

1. Use Web Scraping with Dynamic Selectors: Instead of relying on fixed table structures or elements, you can use web scraping tools like Power Query (with XPath or CSS selectors), VBA, or Python (BeautifulSoup/Selenium) to extract data dynamically.

2. API Integration (If Available): If the website provides an API, it’s the best way to fetch structured data without worrying about layout changes.

3. Automating Adjustments: If you’re using Power Query, try to build a flexible query that captures patterns rather than fixed positions. For advanced scenarios, Python scripts can be set to detect structural changes and adjust accordingly.

4. Manual Updates When Necessary: If the website structure changes significantly, some manual reconfiguration will be required, but keeping scripts adaptable can minimize effort.

Let us know more details about your specific use case, and we’d be happy to suggest a tailored approach!

Regards
ExcelDemy

Hello Jorry Tio,

You’re very welcome, Jorry! I’m glad you found the introduction to MS Access helpful. Keep exploring, and feel free to ask if you have any questions along the way. Looking forward to your progress with Access!

Regards
ExcelDemy

Hello FrustartedUser,

You’re right! The article explains how to send bulk emails but doesn’t cover file attachments.
To attach a file in Power Automate, you need to use the ‘Get file content using path‘ action from OneDrive or SharePoint and then map it to the ‘Attachments‘ field in the ‘Send an email (V2)‘ action. This allows each email to include its corresponding attachment from the Excel sheet.

Step 1: Prepare Your Excel File
Create an Excel sheet with columns:
1. Email Address: The recipient’s email.
2. Subject: The subject of the email.
3. Body: The email content.
4. File Path: (Optional) If you have multiple attachments, store the file path or file name in a column.

Step 2: Upload Files to OneDrive or SharePoint
OneDrive Method:
1. Upload all the files you need to attach to OneDrive.
2. Copy the file path or file name to the Excel sheet.
SharePoint Method:
1. Store attachments in a SharePoint Document Library.
2. Make sure the filenames match those listed in Excel.

Step 3: Create a Power Automate Flow
1. Go to Power Automate.
2. Click Create → Automated cloud flow.
3. Select “When a row is added, modified, or deleted” (Excel connector).
4. Connect your Excel file stored in OneDrive or SharePoint.

Step 4: Add a “Get File Content” Action
1. Click “+ New Step” → Choose “Get File Content”.
2. If using OneDrive, select “Get file content using path”:
3. Set File Path to the value from your Excel sheet.
4. If using SharePoint, select “Get file content”:
5. Use the file identifier from the Document Library.

Step 5: Add the “Send an Email” Action
1. Click “+ New Step” and select “Send an email (V2)” (Outlook 365).
2. Fill in:
To → Select the email address column from Excel.
Subject → Select the subject column.
Body → Select the body column.
Attachments → Click “Add attachment”.
File Name → Use the file name from Excel.
File Content → Use the output from “Get file content”.

Step 6: Save and Test the Flow
1. Click Save and Test the flow.
2. Add a new row to your Excel sheet and see if the email is sent with the attachment.

Regards
ExcelDemy

Hello Mandy,

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

Regards
ExcelDemy

Hello Phil,

You can use the FILTER function (for Excel 365/2021) or a combination of INDEX and SMALL to retrieve multiple IDs corresponding to the same rank.

Try this formula if you’re using Excel 365/2021:
=FILTER(X5:X30, W5:W30=3)
This will return all IDs where the rank is 3.

If you’re using an older version of Excel, use this array formula:
=INDEX(X$5:X$30, SMALL(IF(W$5:W$30=3, ROW(W$5:W$30)-ROW(W$5)+1), ROW(A1)))
1. Press Ctrl + Shift + Enter after typing the formula (if using older Excel).
2. Drag it down to get multiple results.

Let me know if you need further clarification!

Regards
ExcelDemy

Hello John,

You are most welcome. Thanks for your appreciation and feedback. Glad to hear that our articles’ information’s are useful. Keep exploring Excel with ExcelDemy!

Regards
ExcelDemy

Hello AIG,

You are most welcome. Thank you so much for your kind words! I’m really glad to hear that the instructions were clear and helped you successfully combine your sheets. It’s always rewarding to know that the guide made a difference! If you ever have any other Excel or data-related questions, feel free to ask. Happy spreadsheeting!

Regards
ExcelDemy

Hello John,

You’re welcome! You can apply conditional formatting to an entire row by using a formula. Select the entire range of rows where you want the rule to apply, then use a formula like
=A2>A1 (adjust for your specific columns). This way, the whole row will turn green if the new values are greater than the previous row. Let me know if you need more details!

Regards
ExcelDemy

Hello MR. Nigussie Wabella,

If the number has a decimal place, you can modify the formula to handle the fractional part separately. Typically, you would convert the integer part using the existing logic and then append the decimal portion as individual digits or words.

For example, if the number is 1234.56, you might represent it as “One thousand two hundred thirty-four point five six” or “One thousand two hundred thirty-four and fifty-six hundredths” depending on your preferred format.

To achieve this in Excel, you may need to extract the decimal portion using MOD(A1,1) and process it separately. Let me know if you need a specific formula for your use case!

Regards
ExcelDemy

Hello Richard Pugh

No worries, I’d be happy to help. You can use the IF function in Excel to achieve this. Try this formula:

=MAX(A1 – B1, 0)

Or, if you prefer using an IF statement, you can use:
=IF(A1 – B1 < 0, 0, A1 - B1)

Both formulas will subtract the value in B1 from A1 and display 0 if the result is negative. Otherwise, it will show the actual result. Just replace A1 and B1 with the cells you’re working with.

Regards
ExcelDemy

Hello Nick,

​To automate the process of inserting a picture from a database sheet into a specific cell on another sheet using VBA, you can use the following approach:​

Assign the Picture to a Cell in the Database Sheet: Ensure that each picture in your database sheet is placed within its respective cell. This can be done manually by right-clicking the picture, selecting “Format Picture,” navigating to the “Properties” tab, and choosing “Move and size with cells.”

Use VBA to Copy and Insert the Picture into the Target Sheet: The VBA code below demonstrates how to copy a picture from the database sheet and insert it into a specific cell on the target sheet:

Sub InsertPictureFromDatabase()
    Dim wsDatabase As Worksheet
    Dim wsTarget As Worksheet
    Dim pic As Picture
    Dim rng As Range
    Dim picName As String
    Dim targetCell As Range

    ' Set the database and target sheets
    Set wsDatabase = ThisWorkbook.Sheets("Database") ' Change to your database sheet name
    Set wsTarget = ThisWorkbook.Sheets("TargetSheet") ' Change to your target sheet name

    ' Specify the cell in the database sheet that contains the picture
    Set rng = wsDatabase.Range("A1") ' Change to the cell containing your picture

    ' Specify the cell in the target sheet where the picture will be inserted
    Set targetCell = wsTarget.Range("B2") ' Change to your desired target cell

    ' Loop through pictures in the database sheet to find the one that intersects with rng
    For Each pic In wsDatabase.Pictures
        If Not Intersect(pic.TopLeftCell, rng) Is Nothing Then
            ' Copy the picture
            pic.Copy

            ' Activate the target sheet and paste the picture
            wsTarget.Activate
            wsTarget.Paste Destination:=targetCell

            ' Exit the loop once the picture is found and copied
            Exit For
        End If
    Next pic
End Sub

Explanation of the Code:
1. wsDatabase and wsTarget are set to reference the database and target sheets, respectively.​
2. rng specifies the cell in the database sheet that contains the picture you want to copy.​
3. targetCell specifies the cell in the target sheet where the picture will be inserted.​
4. The code loops through all pictures in the database sheet to find the one that intersects with the specified cell (rng). Once found, it copies and pastes the picture into the target cell on the target sheet.​

This method ensures that the picture is placed within the desired cell on the target sheet without the need for manual intervention.

Regards
ExcelDemy

Hello Sarah Thornberry,

Glad you found it helpful! The red warning triangles usually indicate an issue with the data format or missing values.
Try checking for extra spaces, special characters, or inconsistent formatting in those rows.
You can also hover over the warning triangle to see the error message. If the issue persists, consider reformatting the data as plain text or checking if any column headers are mismatched. Let me know if you need more help!

Regards
ExcelDemy

Hello Felix Kiptoo,

Yes, things do change! Excel functions keep evolving, making tasks even easier. Let us know if you need any help with the IF function!

Regards
ExcelDemy

Hello Abolfazl,

Hope you’re doing well. Since Holt-Winters Exponential Smoothing requires a proper trend and seasonality pattern, having 14 months of data might not be enough for accurate forecasting, depending on your dataset’s characteristics.

Could you check if your data includes a clear seasonal pattern? Also, ensure that your time series data is structured correctly in Excel, following the smoothing model requirements.

Since Holt-Winters Exponential Smoothing requires at least two years of data to capture seasonality properly, having only 14 months may not provide an accurate forecast. However, you can try the following steps to improve your model:

Solution:
Extend Data Period: If possible, include more historical data (preferably at least 24 months) to allow Excel to detect trends and seasonal variations better.

Use Moving Averages Instead:
1. If you don’t have a full two years of data, try a Simple Moving Average (SMA) or Exponential Smoothing (without seasonality) to get a trend-based forecast.
2. In Excel, use =AVERAGE(range) for a moving average.

Apply Holt’s Linear Trend Model (if seasonality is not strong):
1. Instead of the triple exponential smoothing, use Holt’s method (Double Exponential Smoothing) in Excel.
2. Go to Data → Forecast Sheet and select Linear Forecast instead of Seasonal.

Check Alpha, Beta, and Gamma Parameters:
1. If you are using Holt-Winters in Excel, ensure that Alpha (Level), Beta (Trend), and Gamma (Seasonality) values are properly adjusted.
2. Try reducing Gamma or setting it to zero if seasonality is not evident.
If your dataset has irregular patterns, a linear regression model in Excel using LINEST or FORECAST.ETS.LINEAR might work better for short-term predictions.

Let me know if you need further details!

Best regards,
ExcelDemy

Hello Fatima,

Yes! You can use a single formula to calculate and display the difference between two dates in the “xx YRS xx MOS xx DYS” format without using multiple helper cells. Try this formula:
=DATEDIF(A1,B1,”y”) & ” YRS ” & DATEDIF(A1,B1,”ym”) & ” MOS ” & DATEDIF(A1,B1,”md”) & ” DYS”

Explanation:
DATEDIF(A1, B1, “y”) → Returns the number of full years.
DATEDIF(A1, B1, “ym”) → Returns the number of months after counting full years.
DATEDIF(A1, B1, “md”) → Returns the number of days after counting full months.

This formula will give you a single result like: “5 YRS 8 MOS 12 DYS”

Regards
ExcelDemy

Hi Heather,

If the Ribbon Display Options icon is not visible on your screen, you can try these alternatives:

Use the Keyboard Shortcut: Press Ctrl + F1 to toggle the ribbon visibility.
Customize the Ribbon Manually:
1. Go to File > Options > Customize Ribbon.
2. Check if the “File” menu is missing. If so, click Reset or add it manually.
Check for Full-Screen Mode: Press Esc or Ctrl + Shift + F1 to exit full-screen mode.
Try Safe Mode: Open Excel in Safe Mode by holding Ctrl while launching Excel to see if an add-in is causing the issue.

Regards
ExcelDemy

Hello JoJo,

You are most welcome. Thanks for your feedback and appreciation. Glad to hear that method 3 solved your issue. Keep exploring Excel with ExcelDemy!

Regards
ExcelDemy

Hello Peter,

Thanks for your feedback! I just tested the demo Excel file on my side, and it seems to be working fine. Could you please check if the file downloaded correctly and that external links are enabled in Excel?
Schedule in Excel.xlsx

If you’re still facing issues, let me know the specific problem you’re encountering, and I’ll be happy to assist!

Regards
ExcelDemy

Hello Athikula,

You are most welcome. Keep exploring Excel with ExcelDemy!

Regards
ExcelDemy

Hello Morten Mo,

You are most welcome. Glad to hear that the smart quotes worked. Keep excel-ing with ExcelDemy!

Regards
ExcelDemy

Hello Morten Mo,

Thank you! If you want a fixed/hard-coded headers area (M1:Y1), you can set the headers like this: “Set Headers = Range(“M1:Y1″)”.
Let me know if you need further clarification!

Regards
ExcelDemy

Hello Sysfs,

Or maybe ‘Creative Ways to Misspell John’ . Though, to be fair, everyone needs a bit of variety in their name game—keeps things interesting! Who knows, maybe ‘Jhon’ or ‘Jonh’ is the next big trend in baby names!

Regards
ExcelDemy

Hello Morten Mo,

Thanks for pointing that out! You’re right—the previous line I referred to might not have matched your macro. Here’s the correct modification that ensures only values (not formulas) are pasted into the Consolidated sheet:

Replace the part where data is pasted with this:

ws.Range(ws.Cells(Row_1, Col_1), ws.Cells(Row_last, Column_last)).Copy
wX.Range("A" & wX.Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False

Key Fix: Using .PasteSpecial xlPasteValues ensures only values are pasted, and Application.CutCopyMode = False clears the clipboard after pasting.

Here’s the updated macro code that pastes only values (no formulas) when combining data from multiple sheets into the “Consolidated” sheet:

Sub combine_multiple_sheets()

    Dim Row_1, Col_1, Row_last, Column_last As Long
    Dim headers As Range
    Dim wX As Worksheet
    Dim WB As Workbook
    Dim ws As Worksheet
    
    ' Set references
    Set wX = Worksheets("Consolidated")
    Set WB = ThisWorkbook
    Set headers = Application.InputBox("Choose the Headers", Type:=8)
    
    ' Copy headers to the consolidated sheet
    headers.Copy
    wX.Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    ' Define starting row and column for data
    Row_1 = headers.Row + 1
    Col_1 = headers.Column
    Debug.Print Row_1, Col_1
    
    ' Loop through each worksheet
    For Each ws In WB.Worksheets
        If ws.Name <> "Consolidated" Then
            ws.Activate
            
            ' Determine the last row and column with data
            Row_last = ws.Cells(Rows.Count, Col_1).End(xlUp).Row
            Column_last = ws.Cells(Row_1, Columns.Count).End(xlToLeft).Column
            
            ' Copy data range and paste only values into Consolidated sheet
            ws.Range(ws.Cells(Row_1, Col_1), ws.Cells(Row_last, Column_last)).Copy
            wX.Range("A" & wX.Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        End If
    Next ws
    
    ' Activate the Consolidated worksheet
    wX.Activate

End Sub

Regards
ExcelDemy

Hello PeterK,

Thank you for the feedback. I completely agree that consistent coloring can improve clarity. To elaborate, here’s a step-by-step guide on how to ensure the same color is applied to similar years across categories in a grouped bar chart in Excel:

Select a Data Series: Click on one of the bars representing a specific year.
Open the Format Pane: Right-click the selected bar and choose “Format Data Series” from the context menu.
Choose Fill Options: In the Format Data Series pane, navigate to the “Fill” section and select “Solid fill.”
Set the Color: Choose the specific color you want to use for that year.
Repeat for All Bars: Apply these steps to every bar representing that year across all categories, ensuring you use the same color each time.
Verify Consistency: Once completed, double-check that all bars corresponding to the same year have identical colors, which will help viewers easily decode the chart.
Using these steps, the color will serve as an effective visual cue, aiding in a quicker and more accurate interpretation of the data.

Regards
ExcelDemy

Hello Paresh Kanti Paul,

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

Regards
ExcelDemy

Hello Ginny,

To resolve both the “Run-time error -2147024809 (80070057): the specified value is out of range” and the issue with clicking checkboxes after protecting the worksheet, please follow these updated steps:
1. Modify the Code to Handle Protected Worksheets Correctly
Replace the part of your code that updates the button text with this version to ensure compatibility with protected worksheets:

Sub UpdateButtonText()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim buttonShape As Shape

    ' Unprotect the worksheet before changing button text
    ws.Unprotect Password:="YourPassword"

    ' Find the button shape by its name
    On Error GoTo ErrorHandler
    Set buttonShape = ws.Shapes("YourButtonName")  ' Replace with your button name
    buttonShape.TextFrame2.TextRange.Characters.Text = "Click Here to Select Company"

    ' Protect the worksheet again (allow macros and objects)
    ws.Protect Password:="YourPassword", UserInterfaceOnly:=True, AllowFormattingCells:=True, DrawingObjects:=False
    Exit Sub

ErrorHandler:
    MsgBox "Error: Unable to find or update the button. Please check the button name.", vbExclamation
End Sub

Explanation:
UserInterfaceOnly:=True ensures macros can modify the sheet even when it’s protected.
DrawingObjects:=False allows interaction with form controls (including buttons and checkboxes).

2. Allow Clicking Checkboxes on a Protected Worksheet
Ensure that the protection settings allow interaction with objects and checkboxes:

2.1. Go to Review → Protect Sheet.
2.2. Enter the password.
2.3. Check Edit objects and Use PivotTable reports. This allows interaction with ActiveX controls and form elements like checkboxes.

3. Additional Tips to Avoid VBA Debug Errors
Verify that the button name in this line is correct:
Set buttonShape = ws.Shapes(“YourButtonName”)

To check the button name:
1. Click Developer → Design Mode.
2. Select the button → Right-click → Choose Properties.
3. Look at the Name field and use that exact name in the code.

Best,
ExcelDemy

Hello Timo,

Yes, you can fix the position of the plot area within the chart using VBA by explicitly setting the .Top and .Left properties of the plot area. This way, it will stay in a fixed location regardless of legend size. For example:

Sub FixPlotAreaPosition()
    Dim cht As Chart
    Set cht = ActiveSheet.ChartObjects("Chart 1").Chart
    
    With cht.PlotArea
        .Width = 300
        .Height = 200
        .Top = 50   ' Fixed distance from top of chart
        .Left = 50  ' Fixed distance from left of chart
    End With
End Sub

If you’d like to keep the legend from resizing as well, you can set its .AutoScaleFont property to False and define fixed dimensions using .Width, .Height, .Top, and .Left:

With cht.Legend
    .AutoScaleFont = False
    .Width = 100
    .Height = 50
    .Top = 50
    .Left = 400
End With

This approach should keep both the plot area and legend from shifting while scrolling through slides. Let me know if you need further customization!

Regards
ExcelDemy

Hello Morten Mo,

To fix the issue of the macro pasting formulas instead of values, you can modify the macro by adding PasteSpecial xlPasteValues to paste only the values.

Replace this line in the macro:
ws.Range(“A1”).Copy Destination:=masterSheet.Range(“A” & lastRow + 1)
With this:
ws.Range(“A1”).Copy
masterSheet.Range(“A” & lastRow + 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False

This will ensure that only the calculated values are pasted into the master sheet. Let me know if this works!

Regards
ExcelDemy

Hello Ginny,

Please check out our Youtube tutorial: How to Create Drop Down Checklist in Excel-Video

It is also attached in our article.

Regards
ExcelDemy

Hello Watt,

You’re absolutely right! When deleting rows in a loop from top to bottom, the row numbers shift, causing the loop to skip rows. The best approach is to iterate from the bottom up using For i = lastRow To 1 Step -1.
This ensures that row shifts don’t affect the loop. Thanks for pointing that out and sharing the Stack Overflow link – it’s a great reference for this common issue!

Regards
ExcelDemy

Hello Galwin,

Congratulations on your new job as a Document Controller! I understand how time-consuming it can be to count colored cells manually. Fortunately, Excel offers a few ways to automate this task.

If you want the count to update automatically whenever cell colors change, I recommend using VBA (Visual Basic for Applications) since regular Excel formulas don’t dynamically update based on color changes. Here’s a simple VBA solution:

1. Press Alt + F11 to open the VBA editor.
2. Go to Insert > Module to add a new module.
Paste this code:

Function CountColoredCells(rng As Range, colorCell As Range) As Long
    Dim count As Long
    Application.Volatile
    For Each cell In rng
        If cell.Interior.Color = colorCell.Interior.Color Then count = count + 1
    Next cell
    CountColoredCells = count
End Function

Press Ctrl + S to save the macro and close the editor.
In your Excel sheet, use the formula like this:
=CountColoredCells(A1:D10, F1)
Replace A1:D10 with your data range and F1 with a cell that has the color you want to count.

Automatic Update
To make sure the count updates when colors change:

Press Alt + F11, go to ThisWorkbook in the editor, and paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.Calculate
End Sub

This ensures the function recalculates every time you update your worksheet.

Regards
ExcelDemy

Hello Carolyn Suubi,

You are most welcome. Thanks for your feedback and appreciation. Glad to hear that it is so helpful to you. Keep exploring Excel with ExcelDemy.

Regards
ExcelDemy

Hello Pritha,

This comment does not seem to be related to the topic of the article. If you need help with leave applications, please check relevant resources or clarify your query.

Regards
ExcelDemy

Hello Humberto Jova

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

Regards
ExcelDemy

Hello Ron,

It sounds like Power Query is trying to modify your query structure, which is causing the issue. Here are a few things to check:

Ensure Correct Syntax: Your query should start with =Excel.CurrentWorkbook(), and the next step should reference this output correctly. Make sure each step logically follows from the previous one.

Manually Adjust the Query: Instead of letting Power Query modify it, try manually checking each step in the Advanced Editor. Look at the last step in your query and ensure it’s being referenced properly.

Check for Blank Functions: If Power Query is adding an unexpected blank function, try deleting that line and manually adjusting the query.

Workbook Data Visibility: Make sure your data tables or named ranges are properly defined in your workbook. Power Query only detects named tables/ranges, not random cell data.

Try Restarting Excel: Sometimes, restarting Excel and refreshing the query can resolve unexpected issues.

Regards
ExcelDemy

Hello Chris,

You are most welcome. Thanks for your feedback and appreciation. Glad to hear that this macro saved your time and made your task easier. Keep Excel0ing with ExcelDemy!

Regards
ExcelDemy

Hello Shantosh Kumar Dwivedi,

Thank you for your kind words! I’m glad you found the article helpful. For your specific requirements, you can achieve this using Mail Merge with VBA in Word, along with some automation techniques. Here’s how you can approach each point:

Populate all rows into a single Word document:
1. Instead of creating separate Word files, use the NEXT RECORD field in Mail Merge to insert all rows into the same document.
2. Alternatively, a VBA macro can loop through the Excel data and append each row dynamically to a designated section of the Word document.

Dynamically select different Excel files:
1. This can be handled by adding a VBA script that allows the user to browse and select an Excel file dynamically.
2. The script would ensure that the selected file maintains the same structure (column names) while updating the data.

Repeat only a selected portion of the Word document:
1. You can achieve this by inserting a Content Control or a specific table within Word where the repeated content is dynamically inserted.
2. Since some sections are populated via Quick Parts (Document Property in SharePoint), ensure that the VBA script or Mail Merge only updates the designated area while keeping the SharePoint metadata intact.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Hello Dear,

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

Regards
ExcelDemy

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

Hello Martin,

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

Regards
ExcelDemy

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

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

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

Hello Irshad,

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

Regards
ExcelDemy

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

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

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

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

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

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

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

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

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

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

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

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

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

Hello Usman,

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

Regards
ExcelDemy

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

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

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

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

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

Hello Nicolas,

Thanks for your appreciation. Keep learning Excel with ExcelDemy!

Regards
ExcelDemy

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

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

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

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

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

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

Hello Dear,

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

Regards
ExcelDemy

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

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

Hello Bikash Neupane,

You are most welcome. Keep learning Excel with ExcelDemy!

Regards
ExcelDemy