Shamima Sultana

Hello,

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

Regards
ExcelDemy

Hello Saqib Haroon,

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

Regards
ExcelDemy

Hello Mostain,

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

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

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

Regards
ExcelDemy

Hello Lili,

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

Sub SendEmailsBasedOnDates()
    Dim ws As Worksheet
    Dim i As Long, LastRow As Long
    Dim EmailDate As Date
    Dim OutApp As Object, OutMail As Object

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

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

Regards
ExcelDemy

Hello Lucie,

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

Regards
ExcelDemy

Hello Ian Lavell,

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

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

Regards
ExcelDemy

Hello Simon Gray,

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

Regards
ExcelDemy

Hello Ian Lavell

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

Regards
ExcelDemy

Hello Paul,

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

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

Regards
ExcelDemy

Hello Erika Póta,

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

Regards
ExcelDemy

Hello,

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

Regards
ExcelDemy

Hello,

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

Regards
ExcelDemy

Hello George Shawnessey,

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

Here’s a suggestion:

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

Regards
ExcelDemy

Hello Sergio Zuniga,

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

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

Regards
ExcelDemy

Hello,

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

Regards
ExcelDemy

Hello Paul,

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

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

Sub SendEmailOnRedCell()
    Dim rng As Range
    Dim cell As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim emailBody As String
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("2024 service") ' Change to your sheet name
    Set rng = ws.Range("F2:F48") ' Check only column F

    ' Create Outlook application
    Set OutApp = CreateObject("Outlook.Application")

    ' Loop through each cell in column F
    For Each cell In rng
        If cell.Interior.Color = RGB(255, 0, 0) Then ' Check if the cell is red
            emailBody = "Information from row " & cell.Row & ": " & vbCrLf
            emailBody = emailBody & "A: " & ws.Cells(cell.Row, 1).Value & vbCrLf ' Column A
            emailBody = emailBody & "E: " & ws.Cells(cell.Row, 5).Value & vbCrLf ' Column E

            ' Create and send the email
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = "[email protected]" ' Change to recipient’s email
                .Subject = "Alert: Red Cell Detected"
                .Body = emailBody
                .Send
            End With
            Set OutMail = Nothing
        End If
    Next cell

    ' Clean up
    Set OutApp = Nothing
End Sub

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

Regards
ExcelDemy

Hello Roel,

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

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

Sub SendEmail(OutApp As Object, emailBody As String, subject As String)
    Dim OutMail As Object
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = "[email protected]" ' Change this to your email address
        .Subject = subject
        .HTMLBody = "<p>" & emailBody & "</p>"
        .Send ' Uncomment this line to send the email automatically
    End With
End Sub

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

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

Regards
ExcelDemy

Hello Bleona,

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

Regards
ExcelDemy

Hello Dear,

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

Regards
ExcelDemy

Hello Dear,

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

Regards
ExcelDemy

Hello Raj,

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

Regards
ExcelDemy

Hello Peter,

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

Regards
ExcelDemy

Hello Kevin Slabbert,

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

Regards
ExcelDemy

Hello Ian,

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

Regards
ExcelDemy

Hello Ano Victory,

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

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

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

Regards
ExcelDemy

Hello Shubham,

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

Regards
ExcelDemy

Hello Joshua Goodman,

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

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

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

Regards
ExcelDemy

Hello Ron,

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

Regards
ExcelDemy

Hello JaneM,

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

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

Copy-Paste the VBA code:

Sub CombineSheets()
    Dim ws As Worksheet
    Dim MergedSheet As Worksheet
    Dim LastRow As Long
    Dim MergedRow As Long
    Dim i As Integer

    ' Create a new worksheet for the merged data
    Set MergedSheet = ThisWorkbook.Worksheets.Add
    MergedSheet.Name = "MergedData"

    ' Start merging from the first row
    MergedRow = 1

    ' Loop through each worksheet
    For i = 1 To 11 ' Assuming Sheet1, Sheet1(2), ... , Sheet1(11)
        On Error Resume Next ' Ignore errors temporarily
        Set ws = ThisWorkbook.Worksheets("Sheet1(" & i & ")")
        On Error GoTo 0 ' Turn error handling back on

        If Not ws Is Nothing Then ' Check if the worksheet exists
            LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Find the last used row in the current sheet

            ' Copy the used range to the merged sheet
            ws.Range("A1:A" & LastRow).EntireRow.Copy Destination:=MergedSheet.Range("A" & MergedRow)

            ' Update the merged row for the next paste
            MergedRow = MergedRow + LastRow
        End If

        ' Reset ws for the next iteration
        Set ws = Nothing
    Next i

    MsgBox "Sheets combined successfully!"
End Sub

Regards
ExcelDemy

Hello Jim,

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

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

Regards
ExcelDemy

Hello Charlie,

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

Regards
ExcelDemy

Hello Eduardo,

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

Regards
ExcelDemy

Hello Moegamat Shakier Stuurman,

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

Regards
ExcelDemy

Hello Minhaj,

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

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

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

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

Regards
ExcelDemy

Hello,

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

Regards
ExcelDemy

Hello Louis,

Thanks for your appreciation. Keep exploring Excel with ExcelDemy!

Regards
ExcelDemy

Hello Hans Hallebeek,

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

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

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

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

Regards
ExcelDemy

Hello Angufibo David,

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

Regards
ExcelDemy

Hello,

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

Regards
ExcelDemy

Hello Edward M,

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

Regards
ExcelDemy

Hello Roy E. McCarthy,

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

After freezing first row:

Regards
ExcelDemy

Hello Grace,

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

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

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

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

Regards
ExcelDemy

Hello Shan,

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

Sub HighlightChangesFromFile()
    Dim ws As Worksheet
    Dim changesWs As Worksheet
    Dim rng As Range
    Dim cellAddresses As Variant
    Dim i As Integer
    Dim lastRow As Long

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

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

Regards
ExcelDemy

Hello,

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

Regards
ExcelDemy

Hello Doreen Bagola,

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

Regards
ExcelDemy

Hello,

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

Regards
ExcelDemy

Hello Jeremy,

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

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

=(G22 – G21) * 24

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

Regards
ExcelDemy

Hello Suzanna Cocker,

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

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

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

Regards
ExcelDemy

Hello Sophie,

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

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Define the range where the status is located, adjust "C" to your status column
    If Not Intersect(Target, Me.Range("C1:C" & Me.UsedRange.Rows.Count)) Is Nothing Then
        If Target.Value = "done" Then
            ' Call the MoveRow_DeleteOriginal subroutine when status is "done"
            Call MoveRow_DeleteOriginal(Target.Row)
        End If
    End If
End Sub

Sub MoveRow_DeleteOriginal(RowNum As Long)
    Dim rg As Range
    Dim xc As Range
    Dim p As Long
    Dim q As Long
    Dim r As Long
    p = Worksheets("VBA delete original").UsedRange.Rows.Count
    q = Worksheets("Sheet1").UsedRange.Rows.Count
    If q = 1 Then
        If Application.WorksheetFunction.CountA(Worksheets("Sheet1").UsedRange) = 0 Then q = 0
    End If
    
    ' Specify the target row
    Set rg = Worksheets("VBA delete original").Range("C" & RowNum)
    
    ' If the condition is met (e.g., "Cable" or "done"), copy and delete the row
    On Error Resume Next
    Application.ScreenUpdating = False
    If CStr(rg.Value) = "done" Then
        rg.EntireRow.Copy Destination:=Worksheets("Sheet1").Range("A" & q + 1)
        rg.EntireRow.Delete
        q = q + 1
    End If
    Application.ScreenUpdating = True
End Sub

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

Regards
ExcelDemy

Hello Kevin,

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

Regards
ExcelDemy

Hello,

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

Regards
ExcelDemy

Hello Nguyen,

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

Regards
ExcelDemy

Hello Emz,

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

Regards
ExcelDemy

Hello Jacquelyn Luna,

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

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

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

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

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

Regards
ExcelDemy

Hi Akhila,

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

Regards
ExcelDemy

Hello Lacy,

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

Regards
ExcelDemy

Hello Ken Mambo,

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

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

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

This approach will give you cumulative totals month by month.

Regards
ExcelDemy

Hello Aisling,

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

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

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

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

Regards
ExcelDemy

Hello Kelly,

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

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

Regards
ExcelDemy

Hello Y2mateOfficial,

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

Regards
ExcelDemy

Hello MP,

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

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

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

Regards
ExcelDemy

Hello Sathish,

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

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

Regards
ExcelDemy

Hello Savoir-faire,

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

Regards
ExcelDemy

Hello Nafiu Gide,

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

Regards
ExcelDemy

Hello Franck,

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

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

Regards
ExcelDemy

Hello Terry,

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

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

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

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

Downlaod Excel File:
Mortgage-Interest-Comparison.xlsx

Regards
ExcelDemy

Hello MP,

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

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

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

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

Regards
ExcelDemy

Hello Ahmed,

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

Regards
ExcelDemy

Hello Tigran,

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

Formula:

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

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

Regards
ExcelDemy

Hello Nick Throp,

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

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

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

Regards
ExcelDemy

Hello Nguyen,

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

Attached the Excel File:
Practice Exercises for SUMIF.xlsm

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

Regards
ExcelDemy

Hello Brea Kelley,

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

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

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

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

Hello Damien Connolly,

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

Regards
ExcelDemy

Hello Chad Sellers,

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

Regards
ExcelDemy

Hello Rat,

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

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

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

Regards
ExcelDemy

Hello Nidhi,

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

Regards
ExcelDemy

Hello Vanessa,

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

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

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

Regards
ExcelDemy

Hello Mate,

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

Thanks for your appreciation. Keep learning Excel with ExcelDemy!

Regards
ExcelDemy

Hello Amber,

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

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

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

Download the Excel file:
Remove Duplicates First Occurences.xlsx

Regards
ExcelDemy

Hello Enamul Sekh,

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

Best Regards
ExcelDemy

Hello Jerry Boor,

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

Regards
ExcelDemy

Hello Dear,

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

Keep learning Excel with ExcelDemy!

Regards
ExcelDemy

Hello Maeenuddin Khan,

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

Regards
ExcelDemy

Hello,

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

Regards
ExcelDemy

Hello Monica,

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

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

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

Regards
ExcelDemy

Hello Deanna,

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

Let us know if you need further help!

Regards
ExcelDemy

Hello Duncan,

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

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

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

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

Regards
ExcelDemy

Hello Michael,

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

Regards
ExcelDemy

Hello Mill,

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

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

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

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

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

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

Regards
ExcelDemy

Hello Wulan Ramayani,

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

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

Regards
ExcelDemy

Hello Babajide,

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

Regards
ExcelDemy

Hello Dennis,

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

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

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

Regards
ExcelDemy

Hello Kay D,

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

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

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

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

Regards
ExcelDemy

Hello Miran,

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

Here is the updated VBA code:

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

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

Regards
ExcelDemy

Hello Joost,

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

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

Regards
ExcelDemy

Hello Kevin,

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

Thanks for your feedback. Keep exploring Excel with ExcelDemy.

Regards
ExcelDemy

Hello Craig,

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

Regards
ExcelDemy

Hello Femi,

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

Regards
ExcelDemy

Hello Saad,

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

Regards
ExcelDemy

Hello Iliyana,

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

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

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

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

Regards
ExcelDemy

Hello Lorcan,

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

Try the following steps to troubleshoot,

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

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

Regards
ExcelDemy

Hello Don,

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

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

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

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

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

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

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

Regards
ExcelDemy

Hello Imtiaz,

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

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

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

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

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

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

Regards
ExcelDemy

Hello David Silberberg,

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

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

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

Regards
ExcelDemy

Hello Yngwie,

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

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

Regards
ExcelDemy

Hello Jennifer,

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

Please check our how to use this template section carefully.

Regards
ExcelDemy

Hello Matador,

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

Regards
ExcelDemy

Hello Kenneth,

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

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

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

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

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

Regards
ExcelDemy

Hello Djeeni,

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

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

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

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

Regards
ExcelDemy

Hello Johnrey Cambaya,

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

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

Regards
ExcelDemy

Hello David McKenna,

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

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

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

Regards
ExcelDemy

Hello Ebsa,

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

Create your dataset with the following columns.

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

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

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

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

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

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

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

Regards
ExcelDemy