Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft Office Suites, and Excel to Data Analysis, Data Science, VBA, Python, and developing Excel Applications.
Let us show you how to use Power Query and Google Sheets’ REGEX functions for advanced data cleaning while comparing them.
This post will provide the most useful 10 free Excel templates that can help you streamline various aspects of your work.
In this tutorial, we will create a complete Google Sheets App with Forms and Scripts.
In this tutorial, we will use Python with Excel to read from and write in spreadsheets using the libraries.
This tutorial will show how you can supercharge Excel with Python within Excel and via traditional external libraries.
This tutorial will show you tips and tricks to create stunning charts in Excel.
Excel Interest Rate Calculator: Knowledge Hub Effective Interest Method of Amortization Excel Interest Rate Swap Calculation Excel Interest Rate ...
We'll use a single column with some values in the Currency format. Method 1 - Using the Number Format to Convert Currency to Numbers Select the ...
To demonstrate how to change the column width into cm (centimeters) in Excel, we'll use a dataset of order details with multiple columns. We've used ...
In this article, we will describe in detail how to create a proforma invoice for advance payment in Excel. A Proforma invoice is used to request payment from a ...
The image below showcases phone numbers with extensions before and after formatting: Method 1 - Using a Custom Number Format Each phone number ...
The sample dataset contains Hyperlinks. Solution 1 - The Pound (#) Sign is Not Accepted in Hyperlinks If the link contains the Pound (#) sign, it ...
We'll use a sample dataset that contains the salary information of a particular employee with the Employee Name, Region, and Salary columns. How to ...
Method 1 - Remove Percentage Using General Format in Excel Select the cell or cell range from where you want to remove the percentage. Open the Home tab ...
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 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 Peter,
Thanks for your appreciation, it means a lot to us. Keep exploring Excel with ExcelDemy!
Regards
ExcelDemy
Hello Cass,
Thanks for your appreciation. Here’s the updated VBA code with adjustments to skip Sundays and continue from Monday.
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 Adam,
Thank you for reaching out! For assistance with finding details on pending and ongoing investment and investigation cases, as well as the locations of bonds and bail companies in Nueces County, I recommend contacting the local court or county records office.
They should have up-to-date information on bonds, bail companies, and related public records. Alternatively, consulting with a legal advisor may help provide the most accurate insights based on your needs. Let us know if there’s anything specific we can help with!
Regards
ExcelDemy
Hello Dear,
You are most welcome. Thanks for your appreciation. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello Jick Sylvanus,
To list only the names of all Physics teachers using INDEX and MATCH, you can combine these with FILTER (if available) or an IF array formula.
You can use FILTER (for newer Excel versions) if you have Excel 365 or Excel 2019:
=FILTER(A2:A100, C2:C100=”Physics”)
This will show all names in column A where the discipline in column C is “Physics”.
You can use INDEX-MATCH, if FILTER is unavailable.
=IFERROR(INDEX(A$2:A$100, SMALL(IF(C$2:C$100=”Physics”, ROW(A$2:A$100)-ROW(A$2)+1), ROW(1:1))), “”)
To use this array formula (press Ctrl+Shift+Enter after typing).
Drag this down to list all Physics teachers. Let me know if you need further help!
Regards
ExcelDemy
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 Asger,
It seems like there are still a few challenges with the date format and the auto-hide functionality. Here’s a refined approach:
Let’s force the date format explicitly in each control to avoid any discrepancy. By handling it this way, we ensure all dates display in dd/mm/yyyy.
Update the Create_Calendar procedure as follows:
If the calendar is not hiding after selection, it may be that the event isn’t firing as expected. Try placing the following line inside each button’s event handler where a date is selected:
If you encounter issues with function names, ensure they match throughout the code, especially when calling or referencing functions. This should clear up any lingering format or visibility issues.
This should address both format consistency and auto-closing the calendar. Let me know if this resolves it, or if I can assist further!
Regards
ExcelDemy
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 Aiza,
To calculate total sales, use the SUM function. For example, if your sales data is in cells B2 to B10, the formula is:
=SUM(B2:B10)
To sum values based on specific criteria, use SUMIF. For example, to sum sales over $100 in the same range, use:
=SUMIF(B2:B10, “>100”)
The SUMIF formula helps you total values only when they meet your chosen criteria. Let me know if you need more details!
Regards
ExcelDemy
Hello Bikash Neupane,
You are most welcome. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello Manpreet Kaur kaur,
You are most welcome. Thanks for your appreciation. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello Santiago,
You can handle this by using a custom cell format. To replace 00.00.0000 with a placeholder (like #) or hide it, try using this custom format: dd.mm.yyyy;@;.
You can add ; after each section to control how positive, negative, zero, and text values display, respectively.
For example, dd.mm.yyyy;@;#;”” will show # for zero values. Custom formatting, however, only changes the display, not the underlying data.
Regards
ExcelDemy
Hello Edel Whelan,
It looks like you’re encountering a problem with the formatDateTime function in Power Automate. This issue often arises if the syntax for the formatDateTime function is incorrect or if there’s a typo.
Double-check that you’re using the function as formatDateTime(triggerOutputs()?[‘headers’][‘Date’],’yyyy-MM-dd’) or a similar format. Ensure that the date format aligns with Power Automate’s requirements and that all parentheses are correctly placed.
Regards
ExcelDemy
Hello Morea Steven,
You are most welcome. Glad to hear that our step by step process helped you to create a paysilp. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello Bouchaib,
Welcome to ExcelDemy. You can offer your solution in our <a href=”https://exceldemy.com/forum/” rel=”noopener” target=”_blank”>ExcelDemy Forum.
Regards
ExcelDemy
Hello Jeremy Brundrett,
Hope you are doing well. Feel free to post any queries regarding Excel or this article. We are here to help you. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello Asger
You are most welcome. However, different formats might show due to mixed regional settings or VBA interpreting some dates incorrectly. To enforce consistency, we can modify the code to always use a single format, regardless of system settings.
Here’s an updated version that ensures the date format is consistently dd/mm/yyyy:
To ensure the calendar automatically hides after a date is selected, you can modify the code to make the calendar invisible after a date is picked. Add the following code to the Calendar1_Click event, which will trigger each time a date is chosen:
If you’re using buttons for each day as clickable dates, include the Calendar1.Visible = False line in the click event for each button:
This approach ensures the calendar closes automatically after a date selection. Let me know if this solves it!
Regards
ExcelDemy
Hello Hans Hallebeek,
You are most welcome. Thanks for your feedback and the tip. It will be useful for our users. Keep sharing Excel tips with ExcelDemy!
Regards
ExcelDemy
Hello,
Thanks for your appreciation. Glad to hear that our explanations is helpful to you. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello Asger,
Please download the Excel file to get the fresh code.
Regards
ExcelDemy
Hello Johnny Laguna,
You are most welcome. Glad the solution helped you reclaim your spreadsheet from those endless columns. It’s great to hear that Command-Shift worked perfectly on your Mac for selecting and deleting the extra columns. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello Julian Sanjeev,
Thank you for your kind words! To modify the hyperlink so that it references a friendly name in B2 instead of A3, you can adjust the formula accordingly. For linking to specific cells within the same sheet, simply use the cell references directly in your hyperlink formula.
For example, you can use =HYPERLINK(“#Sheet1!B2”, “January Go”) to create links to specific cells. If you need further assistance, feel free to ask!
Regards
ExcelDemy
Hello Tashia Bramhan,
It seems there might be issues with the named ranges or the way validation is being applied to multiple ranges. Here are some potential fixes:
Check Named Ranges: Ensure ValidationOptions, FBCommentsValidation, and Resolved-Pending are named ranges defined in the workbook. Validation won’t work if any names are undefined or misspelled.
Order of Operations: Refreshing the workbook (ActiveWorkbook.RefreshAll) between each validation setup may be unnecessary and could disrupt the code flow. Try placing it at the end only.
Direct Range Reference: Instead of Range(“Export[Distributor Comments]”), try specifying cell ranges directly, like Range(“A1:A10”).
Regards
ExcelDemy
Hello Andrea L McCormack,
Yes, you can adapt the FV function to calculate a balloon payment with extra quarterly principal payments. To do this, you would adjust the payment parameter in the FV function to include both the regular payment and the extra principal.
However, depending on the loan structure, this might require setting up a more detailed cash flow model. Excel’s PMT and IPMT functions can also be useful for tracking how each payment impacts the remaining balance leading to the final balloon payment.
Let’s consider an example:
Loan amount: $50,000
Interest rate: 5% annually (1.25% quarterly)
Loan term: 5 years (20 quarters)
Regular quarterly payment: $1,000
Additional quarterly payment: $200
Determine the adjusted quarterly payment: Sum your regular payment and extra principal payment:
1000+200=1200
Use the FV formula: In Excel, use =FV(rate, nper, pmt, pv):
=FV(1.25%, 20, -1200, -50000)
The result will be the remaining balance after 20 quarters, giving you the balloon payment amount at the end of the term with quarterly extra payments.
Regards
ExcelDemy
Hello Nacho,
Thank you for sharing this helpful workaround! It’s good to know that disabling Clipboard History can sometimes resolve this stubborn issue.
If anyone else is still facing freezing when copying and pasting, trying this method could be an effective alternative. It’s also worth restarting Excel and checking for any updates, as these sometimes impact clipboard behavior.
Thanks again, and we appreciate your contribution!
Regards
ExcelDemy
Hello,
You are most welcome. Glad to hear that our solution helped you to solve copy-paste issue. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello Rebecca A,
You are most welcome. Glad to hear that the method 5 solved your problem. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello Adriaan,
Thank you for your feedback! The issue you mentioned may be due to saving the workbook as a regular .xlsx file, which doesn’t retain macros. Try saving the file as a .xlsm (macro-enabled workbook) instead. This should keep the macro intact even after closing and reopening Excel. Let me know if this solves the problem!
Regards
ExcelDemy
Hello,
You are most welcome. Glad to hear that solution worked for you to fix the issue. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello JW,
You are most welcome. Thank you for your feedback! Follow the given steps to solve your queries.
Extracting the full name of the commenter: You can modify your macro to extract the full name by using the .Author property instead of the initials.
.Cells(j, 2).Value = cmt.Author ‘This extracts the full name of the commenter
Troubleshooting the extraction of page numbers: The issue with extracting page numbers could be related to how the document is structured or the version of Word you’re using. To troubleshoot, try using this code to ensure it’s correctly identifying the page.
.Cells(j, 4).Value = cmt.Scope.Information(wdActiveEndPageNumber)
Double-check that your document has page numbers and that cmt.Scope refers to the correct part of the comment range. You could also try using wdStartOfRange or wdEndOfRange in case the wdActiveEndPageNumber isn’t providing accurate results.
Regards
ExcelDemy
Hello Ty Calkins,
The issue with your formula lies in the use of the IF function. In Excel, IF requires three arguments: a condition, a result if the condition is true, and a result if the condition is false. You’re missing those components in your current formula.
Correct Formula:
=IF(ISERROR(VLOOKUP(F52,C17:G24,5,FALSE)), “Value not found”, VLOOKUP(F52,C17:G24,5,FALSE))
VLOOKUP(F52, C17:G24, 5, FALSE): This looks for the value in F52 within the range C17:G24 and returns the value from the 5th column.
ISERROR: This checks if the VLOOKUP produces an error (e.g., if the value is not found).
“Value not found”: This is the result if the VLOOKUP results in an error.
VLOOKUP(F52, C17:G24, 5, FALSE): This is the result if the VLOOKUP successfully finds a match.
Regards
ExcelDemy
Hello,
You are so welcome. Thanks for your feedback and appreciation. Glad to hear that the step by step guide is easy to follow for mail
merging. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello Andy,
Thank you for your valuable input! Including the root of the server in the “Trusted Sites” section of Internet Options is an important step when working in intranet environments.
By adding the server path using file://192.168.0.#, you ensure that macros and other features dependent on file access work smoothly across the network.
Additionally, checking the “Require server verification” option adds an extra layer of security by verifying the server’s authenticity. This is particularly useful in enhancing both functionality and security when dealing with macros on internal networks.
Regards
ExcelDemy
Hello Spencer Kapazira,
You are so welcome. Glad to hear that you got the data set. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello,
You are so welcome. Thanks for your feedback and appreciation. Glad to hear that the step by step instructions made it easy to create a leave record. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello Ben,
You are most welcome. You can modify the VBA code to narrow the search down to a specific column and display all the information for the matching row. You will need to Update the search logic to limit the search to your desired column, e.g., the “Location” column.
1. Added Search_Column to specify which column to search in (you can adjust this to match your “Location” column).
2. The PartialMatch function checks the cell value in the specified column and copies the entire row if a match is found.
Regards
ExcelDemy
Hello Selinay,
The GET.CELL function retrieves specific information about a cell. In this case, 38 is the code for the background color index of the cell. The formula returns the color index of cell B5 in the ‘Get Cell’ sheet.
The “38” is a code used in the GET.CELL function to retrieve the color index of a cell. It identifies the background color.
If the you want to get different information, such as font color or cell format, you can replace “38” with the appropriate code.
Here is a list of some GET.CELL function codes:
38: Returns the background color of the cell.
24: Returns the font color.
63: Returns if the cell contains a formula (TRUE/FALSE).
17: Returns the number format of the cell.
50: Returns the width of the cell.
64: Returns if the cell is locked (TRUE/FALSE).
These codes help retrieve specific cell information.
GET.CELL is a legacy Excel function, which needs to be used within named ranges to work properly in modern versions of Excel.
Regards
ExcelDemy
Hello,
You are so welcome. Thanks for your feedback and appreciation. Glad to hear that the step by step guide is helpful to you. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello Devindi,
To copy this VBA Date picker in each row in my your table, you will need to create a dynamic date picker.
Here are the steps to create a dynamic date picker in each row of your Excel table:
1. Set up the UserForm with a date picker control (as shown in the article).
2. Use the Worksheet_SelectionChange event to trigger the date picker when a cell in your “Participation Date” column is selected.
3. Capture the date in the DatePickerForm code, handle the selection:
Once the date is picked, insert it into the active cell.
Ensure that the date picker works dynamically across all rows of your table.
Regards
ExcelDemy
Hello Muhidin Tahir,
Thanks for your appreciation. Glad to hear that our community services are helping Excel users. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello,
Sorry for the inconvenience. You can create the the test case sheet from this image.
Regards
ExcelDemy
Hello Fortune,
In Your Products list you can add products of your choice. You will need to update the range according to your product list.
Regards
ExcelDemy
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,
You are most welcome. Thanks for your appreciation. Glad to hear that the article’s step by step explanations is helpful to you. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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:
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,
You are most welcome. Thanks for your appreciation. Glad to hear that the article’s step by step explanations is helpful to you. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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.
This code will check the date in column B and send the email if the date matches today’s date.
Regards
ExcelDemy
Hello Liliana,
You are most welcome. You can adapt Method 1 from this article on sending emails based on dates. Here’s an updated VBA code snippet that checks if the date in column B matches today’s date before sending the email.
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 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 Tomasz,
You are most welcome. Glad to hear that you found the expected solution. Thanks for your appreciation and feedback. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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 Lethabo Mokoti,
You are most welcome. 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 Simon,
You are most welcome. Thanks for your appreciation and feedback. To hardcode the ranges and remove the input boxes from the script, you can directly assign the ranges in the code. Replace the lines where Application.InputBox is used with specific references like this.
To stop auto-adjusting row widths when pasting, remove the Columns.AutoFit line.
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.
Regards
ExcelDemy
Hello,
You are most welcome. Thanks for your insightful feedback. Keep learning Excel with ExcelDemy!
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 you found the examples clear and insightful. Keep exploring Excel with ExcelDemy!
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 Van Weaver,
You are most welcome. Glad to hear that you got your desired result. 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,
Thanks for your appreciation. Glad to hear that our Excel template is 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 Van Weaver,
To sum a defined range of columns while still applying criteria for the rows, you can use the SUMPRODUCT function combined with INDEX and MATCH. For summing through a range of weeks up to today, consider using a dynamic approach.
1. Define the range for weeks in columns.
2. Use a dynamic condition like COLUMN()<=MATCH(TODAY(), your_week_range, 0) to sum only up to today.
3. Apply row criteria within the SUMPRODUCT for filtering.
Regards
ExcelDemy
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 Paul,
You can use this updated VBA code that will send an email when a cell in column F is >= 0 and include information from columns A and F in the email body.
Option Explicit Dim Rng As Range Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next ' Check if more than one cell is changed If Target.Cells.Count > 1 Then Exit Sub ' Limit the range to A2:O48 Set Rng = Intersect(Me.Range("A2:O48"), Target) If Rng Is Nothing Then Exit Sub ' Check if the cell in column F is >= 0 If Not IsNumeric(Target.Value) Then Exit Sub If Target.Column = 6 And Target.Value >= 0 Then Call SendEmail(Target.Row) End If End Sub Sub SendEmail(rowNum As Long) Dim mApp As Object Dim mMail As Object Dim mMailBody As String Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("2024 service") ' Change to your sheet name Set mApp = CreateObject("Outlook.Application") Set mMail = mApp.CreateItem(0) ' Prepare email body with info from column A and F mMailBody = "Hello," & vbNewLine & vbNewLine & _ "Please note that item in row " & rowNum & " requires attention." & vbNewLine & _ "A: " & ws.Cells(rowNum, 1).Value & vbNewLine & _ "F: " & ws.Cells(rowNum, 6).Value & vbNewLine & _ vbNewLine & "Regards," & vbNewLine & "Outlet Team" ' Send email On Error Resume Next With mMail .To = "[email protected]" ' Change to recipient’s email .Subject = "Alert: Cell in Column F >= 0" .Body = mMailBody .Display ' or you can use .Send to send the email directly End With On Error GoTo 0 ' Clean up Set mMail = Nothing Set mApp = Nothing End Sub
Worksheet_Change Event triggers when any cell in the range A2 changes. Sends an email when a cell in column F is greater than or equal to 0. Includes the information from columns A and F for the corresponding row in the email body.
Regards
ExcelDemy
Hello,
You are most welcome. Glad to hear that you found the article helpful. Keep exploring Excel with ExcelDemy!
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.
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 Monika Salinas,
It seems like you’re facing a common issue with links to external workbooks in Excel.
If your links stop working after the first click, it might be due to the linked workbook being closed or moved.
Make sure the file path remains valid and the linked workbook is open when you access it.
Additionally, check if there are any settings in Excel that might restrict external links.
Regards
ExcelDemy
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:
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 Roel,
To address your first concern about receiving multiple emails due to the same expiration date, you can modify the code to ensure only one email is sent per unique expiration date. This can be done by adding a simple check to track which dates have already triggered an email. Here’s how you can adjust the code:
This update ensures that for any particular expiration date, only one email will be sent.
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 Leonardo,
The issue you’re encountering with VLOOKUP returning the incorrect time (e.g., 8:00 returning as 7:00) may be related to how Excel handles date and time values. Double-check that both columns are formatted exactly the same, not just visually but also in terms of underlying data types (i.e., as date/time values).
Sometimes even slight formatting differences or time zone offsets can cause such issues. You might also try using TEXT functions to standardize the format.
If VLOOKUP is fetching a time that’s off by an hour, consider verifying any regional settings or daylight saving time adjustments.
Regards
ExcelDemy
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 Paul,
You are most welcome. To automate email notifications when a cell turns red, you can update your existing VBA code to check the cell color. You can use the Interior.Color property to identify red cells. Once a red cell is found, compile the data from that row and include it in the email body.
Sample VBA Code:
1. This code checks the specified range (A1) in Sheet1 for red cells. If a red cell is found, it compiles information from that row and sends an email.
2. Remember to update the Range, Sheet name, and recipient email address as needed.
3. Make sure to enable macros and allow programmatic access to Outlook.
Regards
ExcelDemy
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 Fiona,
To calculate the time difference across shifts that span multiple days, you’ll need to adjust your formula. Instead of just using the MOD function, you can directly subtract the end time from the start time while ensuring the dates are included.
For example, if your end time is in cell D2 and your start time is in cell C2, you can use:
=(D2 + IF(D2 < C2, 1, 0)) - C2 This formula adds 1 day if the end time is earlier than the start time. Regards ExcelDemy
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 Muhammad Azhan,
To create a graph of kurtosis values in Excel, follow these steps:
1. Create a table with your values and corresponding labels.
2. Highlight the relevant cells.
3. Go to the Insert tab >> select the desired chart type (e.g., bar or line chart).
4. Use chart tools to add titles, labels, and adjust formatting as needed.
Regards
ExcelDemy
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,
You are most welcome. Thanks for your appreciation. Glad to hear that our functions breakdown helped you to understand the function arguments. keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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,
You are most welcome. Thanks for your appreciation. Glad to hear our step-by-step guide is helpful to you. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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,
You are most welcome. Thanks for your appreciation. Glad to hear that our tutorial helped you to remove carriage returns. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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 Jay,
You are most welcome and thank you for your kind words! To change the individual segment colors for just one month, follow these steps:
1. Select the chart.
2. Select the bar segment for January that you want to modify.
3. Right-click the segment and choose Format Data Point.
4. In the Format Pane, change the Fill color to your desired choice.
Regards
ExcelDemy
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,
You are most welcome. Thanks for your appreciation. Glad to hear that our step by step guide helped you to follow the instructions easily. Learn Excel with ExcelDemy!
Regards
ExcelDemy
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,
You are most welcome. Glad to hear that our tutorial is lifesaver to you. Thanks for your appreciation. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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 Denise Groves,
You are most welcome. Glad to hear that our tutorial helped you to visualize nesting substitute formulas. Keep exploring Excel formulas with ExcelDemy!
Regards
ExcelDemy
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:
Regards
ExcelDemy
Hello Matthew Senko,
It seems that you’re encountering a syntax error in your Power Automate flow. The issue is likely related to how the date is formatted in your query. Make sure that the date is enclosed in double quotes and use the correct format.
Instead of: Deadline eq ‘2024-10-03’
Try using: Deadline eq ‘2024-10-03T00:00:00Z’
If the issue persists, please double-check your query syntax. Let me know if you need further assistance!
Regards
ExcelDemy
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 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 Laurie,
Here, oFolder object not being correctly initialized within the Do While Coll_queue.Count > 0 loop. That’s why the error is occurring. This object is necessary to iterate through files and subfolders, but it’s missing an assignment before looping over subfolders and files.
To fix this issue, assigning oFolder from the collection at the beginning of the loop.
This ensures oFolder is properly initialized and recognized when iterating over its subfolders and files.
Updated Excel File:
List All Files in Folders & SubFolders Including File Details with Excel VBA.xlsm
Regards
ExcelDemy
Hello Eduardo,
De nada! You are most welcome. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello kellyd,
To solve your issue, you can use the INDEX and MATCH functions along with a filter to look up the first non-blank status for each site code.
In the “Status Lookup” column, use this formula:
=INDEX($C$15:$C$23,MATCH(1,($B15=$B$15:$B$23)*($C$15:$C$23<>“”),0))
After entering this formula, press Ctrl+Shift+Enter (if you’re not using Excel 365) to treat it as an array formula.
This will return the first non-blank status for each site code.
Regards
ExcelDemy
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 Raj,
Thanks for your appreciation. 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 Steve,
You can address the error by ensuring there is enough space in the target worksheet for the CSV data to be imported without overlapping existing tables or XML mappings. Consider moving the existing table to another location or importing the CSV data into a new worksheet and then linking it to the existing formulas or tables.
Regards
ExcelDemy
Hello,
You are most welcome. Thanks for your appreciation. Glad to hear that our examples helped you.
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 Steve,
It’s clear you’re facing performance bottlenecks with Excel, and your frustration is understandable given your powerful hardware. Excel, while widely used, doesn’t fully utilize multi-core CPUs or GPUs for many tasks. Here are some steps and alternatives you could try:
To optimize Excel usage you can disable volatile functions like NOW() and RAND() recalculate constantly, slowing Excel down.
Overuse Conditional Formatting can heavily impact performance, especially on large datasets.
Set Excel to manual calculations when working on large datasets.
Excel doesn’t effectively leverage GPUs or advanced multi-threading. However, you can check File > Options > Advanced > Enable hardware graphics acceleration (though this often doesn’t make significant differences in complex datasets).
Try to enable multithreading by going to File > Options > Advanced > Formulas > Enable multi-threaded calculation. While Excel doesn’t scale well across many cores, this can still help somewhat with certain tasks.
Explore Alternatives:
Power BI: It’s optimized for large datasets and leverages more powerful backend engines for data processing.
Python (Pandas/NumPy): If you handle large matrix-like data, Python libraries can offer much faster performance and can use multiple cores efficiently.
R or SQL-based tools: These are better at handling larger datasets, parallel processing, and complex operations.
Consider Excel Online: While Excel desktop has limitations, Excel Online, backed by Microsoft’s cloud, sometimes offers better performance in dealing with larger datasets since it uses cloud resources.
Despite its strengths, Excel’s legacy architecture doesn’t yet match the potential of modern hardware. Exploring these steps or alternative tools might improve your workflow and reduce frustration.
Regards
ExcelDemy
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:
This triggers a message box whenever the TextBox content changes. You can replace TextBox1 with your TextBox’s name.
Regards
ExcelDemy
Hello Timothy,
It seems the issue is due to the protection on the data sheets preventing your mainSht from accessing the necessary data. You can modify your VBA to temporarily unprotect the sheets when running the code.
You can also try setting the UserInterfaceOnly property when protecting the sheets. This allows VBA code to modify protected sheets while still restricting user interaction:
This way, your VBA code can still manipulate the data without fully unprotecting the sheets each time. I hope this helps! Let me know if you need further clarification or additional resources.”
This approach ensures the sheets remain protected for users but accessible for the code execution.
Regards
ExcelDemy
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 invoice format is helpful to you. Keep learning Excel with ExcelDemy!
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 AT William,
You are most welcome. Thanks for your kind words. Keep exploring 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 Edward M,
You can definitely modify the formula to reference a date in another cell instead of hardcoding it. For example, if you have a date in cell A1, you can use the formula =COUNTIF(E5:E27,”>” & A1) to make it dynamic. This way, you won’t have to change the formula every time you need a different date range.
Regards
ExcelDemy
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 Maddie,
It seems the custom number format you’re using might be interacting with the display unit settings in Excel.
Set the display units to “None”. Display units in Excel (e.g., “Thousands”, “Millions”) automatically append prefixes like “K” or “M”, and in this case, that’s why the “K” is appearing.
Use this custom format: 0.00,, “GW” or #.##,,”GW”
The double commas ,, divide the number by 1,000 twice, which effectively formats the number in thousands. The result should display values in thousands with “GW” as the unit but without adding “K”.
By setting the display units to “None” and using the format above, the unwanted “K” should no longer appear, and the values will remain visible.
Regards
ExcelDemy
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:
This code reads the quantity from the spin button and prints the label sheet that many times.
Regards
ExcelDemy
Hello Maddie,
To display units in thousands without “K”. You can use the format #.##,,”GW” for thousands (without “K”).
This removes the extra “K” while formatting the number in thousands.
Regards
ExcelDemy
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.
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 Shan,
Glad to hear. To dynamically feed cell addresses from an Excel file (starting at cell B3), you can modify the VBA code to read the list directly from that file.
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 Shan,
To highlight the range with changes, you’ll need to use VBA to highlight the changed cells in the original Excel file.
If the compare tool generates a range like (A101, B95, etc.). You’ll need to copy this list.
Open the workbook where changes occurred. Then, run the following VBA script to loop through the list and highlight the cells.
It will highlight the specified cells with a yellow background.
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 Gail Anthony,
Yes, it is possible for a cell to reference its own sheet name in Excel. You can use a formula like this:
=MID(CELL(“filename”, A1), FIND(“]”, CELL(“filename”, A1)) + 1, 255)
This formula extracts the sheet name from the full file path returned by the CELL(“filename”, A1) function. Just ensure the workbook is saved, as the filename includes the sheet reference.
Regards
ExcelDemy
Hello,
You are most welcome. Glad to hear that our tips are helpful to you. Keep exploring Excel with ExcelDemy!
Regards
ExcelDemy
Hello بهروز مظفری,
To merge all data from different sheets to one single workbook. You can follow the step by step guide of these articles:
How to Combine Multiple Worksheets into One Workbook?
How to Merge Sheets in Excel – Must-Know Tips and Techniques
How to Merge Multiple Sheets into One Sheet with VBA in Excel?
Regards
ExcelDemy
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 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.
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 Ravi Patel,
To ensure that images match the corresponding name in column A, you can compare the image name with the text in column A (e.g., abc in column A should match abc.jpg), and then insert that image into column B.
In the original code, it opens a dialog box to select multiple images, and then it adds them one by one in adjacent cells. If the image names don’t match, you would need to adapt this logic based on image names stored in column A. To automate the matching with your scenario, make sure the file names match the corresponding data in column A.
Regards
ExcelDemy
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.
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 Fin,
The “-2” or “-3” are the different adjustments for kurtosis calculations. In this article, “-2” is used in the context of excess kurtosis, which helps measure how heavy or light-tailed the distribution is compared to a normal distribution.
Normally, kurtosis is 3 for a normal distribution. Some methods subtract 3 from kurtosis (excess kurtosis), while in other cases, adjustments like “-2” are made based on specific analysis needs. It’s essential to know which method or software you’re using.
Regards
ExcelDemy
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 Frank,
Thanks for your suggestion. It’s the beauty of Excel it provides better formula with updated functions. FILTER function is one of the most dynamic and useful function. Keep contributing Excel tips with ExcelDemy!
Regards
ExcelDemy
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 Esther,
You are most welcome. Thanks for your appreciation it means a lot to us. 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 Sanjay Dutt Dobhal,
To calculate the time difference you need to insert the date and time based on your regional settings of Excel.
Format the date and time based on your settings then use the CONCATENATE formula to calculate the time difference from date and time.
=CONCATENATE(C2,””,D2)-CONCATENATE(A2,””,B2)
Right-click the time difference cell >> choose “Format Cells” >> select “Custom” and enter the format [h]:mm
Regards
ExcelDemy
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 AML,
You can add IF statement to adjust font color based on background brightness in the VBA code. You can add a condition that sets the font color to white for darker backgrounds and black for lighter ones.
Condition to add lighter color on a darker background and vice-versa:
Place this inside the loop where you’re setting background colors. This will ensure visibility based on the background color.
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:
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
Hello Amy,
You can try using a combination of SEARCH and INDEX-MATCH.
In cell U2 on Tab 1, try the following formula:
=IFERROR(INDEX(‘Tab 2’!B:B, MATCH(TRUE, ISNUMBER(SEARCH(‘Tab 2’!A:A, T2)), 0)), “”)
1. SEARCH(‘Tab 2’!A:A, T2) checks if any value from column A on Tab 2 is found in the text in column T on Tab 1.
2. INDEX(‘Tab 2’!B:B, …) returns the corresponding value from column B on Tab 2.
3. IFERROR handles cases where no match is found.
This should return the desired result in column U of Tab 1.
Regards
ExcelDemy
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 Akhila,
Here is the sample dataset of Healthcare Insurance Claim Patients
Download the Dataset: Healthcare-Insurance-Claim-Patients-Dataset.xlsx
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 Adnan,
You are most welcome. Thanks for your appreciation. The “50” and “100” columns represent performance benchmarks. The “50” column typically indicates a baseline or minimum acceptable performance, while the “100” column represents the ideal or target performance level. The “Actual” column shows the real performance values. The “Score” column reflects how close the actual performance is to these benchmarks, and it is multiplied by the corresponding weight to calculate the “Weighted Score.” The total weighted scores are summed to determine overall performance (82 in this case).
Regards
ExcelDemy
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 Dan,
Gmail doesn’t natively allow sending emails via VBA due to security restrictions. However, you can try to send emails via Gmail by using CDO (Collaboration Data Objects) with SMTP.
Google may block this unless you’ve allowed less secure apps or set up an app-specific password if you’re using two-factor authentication (2FA).
For Google Sheets, using Google Apps Script would be more appropriate since it integrates seamlessly with Gmail. Here’s how you can approach both:
Apps Script is directly integrated with Google’s ecosystem, making it an easier option for automating email alerts, and it’s more secure than trying to bypass restrictions in Gmail for VBA.
Regards
ExcelDemy
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,
In our Excel file the code is working perfectly without errors.
The possible reasons of getting errors in you end might be: Selecting a range at the edge of the worksheet or having non-contiguous selections.
You can add a check to avoid out-of-bounds errors:
This prevents the code from trying to access an offset that doesn’t exist (the last row in the selection).
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 Franck,
You are most welcome. Since you don’t have Office 365, you can try using Google Sheets with the GOOGLEFINANCE function to import financial data for free, or explore third-party APIs like Alpha Vantage. These are good alternatives to the STOCKHISTORY function in Excel.
Regards
ExcelDemy
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 Shimmy,
You are most welcome. Glad to hear that you found our article helpful. 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.
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 DD,
You’re right. The method in the article works well for fixed text but doesn’t apply to dynamic text like formulas (=A1, etc.). In Excel, there isn’t a built-in feature to rotate text 180 degrees for cell references or formulas.
you can use a shape or text box, link the shape’s text to the formula (e.g., =A1), and then rotate the shape. While this doesn’t work directly inside a cell, it provides a solution for visualizing rotated dynamic text. It may not be ideal for every case but can help when dealing with variable data.
Regards
ExcelDemy
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:
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:
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 HR,
If you want to input 0.5 as half day leave. You can use the following formula for Half Day (HD) column: =SUMPRODUCT(–($D9:$AH9=”HD”)*0.5)
It will look for all occurrences of “HD” within the range $D9:$AH9. Each “HD” will be counted as 0.5.
You will need to format the cells with 2 decimal places to properly show the decimal number otherwise it will show you rounded 1.
Regards
ExcelDemy
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 Calvet,
You are most welcome. Thanks for your appreciation. We are glad to her that you found our article helpful and informative. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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 Azada Rudnicki,
We use Microsoft Excel 365 to demonstrate the examples. The reason of getting 0 is GET.CELL function is not working properly or not defined in the Name Manager. IF function works with existing values there is no problem in the formula.
Before using the IF formula you must need to create the name manager by using GET.CELL function.
Steps:
=GET.CELL(38,'Example 1'!B5)
=CellColor
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 Musthafa,
To prepare manual bin card you can follow the steps of our article,
1. Design a standard bin card with fields like Item Name, Item Code, Quantity, etc.
2. Ensure your item data is stored in a separate Excel worksheet with columns for Item Name, Code, Quantity, etc.
3. To automatically pull data from your stock worksheet to the bin card, use the VLOOKUP function (or INDEX-MATCH for more flexibility).
For example, if the item code is in cell A2 of your bin card, and your item data is in a worksheet named “StockList,” the formula to fetch the item name might look like this:
=VLOOKUP(A2, StockList!A2:E100, 2, FALSE)
This formula will search for the item code in column A of “StockList” and return the item name from column B.
You can use VBA code to automatically loops through each item in the stock list, populates the bin card template, and prints it. Ensure that your bin card is properly formatted for printing. You can do this by setting print areas and page breaks.
Run the VBA to automatically fill and print bin cards for all items in your stock.
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,
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 Weronika,
To change the calendar’s weekday order you will need to change the labels name and update the VBA code date filling logic.
To start with Monday and end with Sunday in the Excel date picker, change the order of the day labels (e.g., Label1.Caption = “Mon”, Label7.Caption = “Sun”).
Now, use the updated Create_Calender procedure. Changed the Weekday function to use vbMonday, ensuring that the week starts on Monday. and the rest of the logic remains the same to ensure proper date filling and formatting.
Download the Excel File:
Calendar-Date-Starts-with-Monday.xlsm
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 Don,
Thanks for your feedback! This article is written based on the Windows version of MS Office 365, where dynamic named ranges works across charts.
It seems Excel 2016 on Mac behaves differently, allowing only workbook-level named ranges and replacing worksheet references with the workbook name. Unfortunately, for existing charts, this dynamic range may not apply correctly, and creating new charts might be the only solution on Mac.
We appreciate your insight, and we’ll consider these differences in future content!
Regards
ExcelDemy
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 Anne K,
You can modify the logic of calculating Normal hours (column N) and OT hours (column P), specifically targeting the total at the bottom of each column. If Normal hours (column N) is less than 38, then part of the OT hours (column P) should be added to reach 38. Once Normal hours reach 38, any remaining OT should stay in column P.
In the cell where you total your Normal hours (say N20), you can use this formula to adjust for the scenario where OT hours should be added to the Normal hours if the total normal hours are less than 38.
Updated Formula:
=MIN(38, SUM(N12:N19) + SUM(P12:P19))
In the cell where you total your OT hours (say P20), adjust the OT formula so that it only shows overtime that is truly above 38 hours of work.
Updated Formula:
=MAX(0, SUM(L12:L19) – 38)
This should solve the issue where overtime hours need to “make up” for missing normal hours but still allows for extra OT beyond 38 hours. Let me know if this works for you or if you need any further tweaks!
Regards
ExcelDemy
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 HC,
Thank you for your comment! The confusion arises from auto-rounding the numbers (not showing decimal places). The actual values used in the calculation are a weighted average selling price of 140.5000 and a weighted average variable cost of 103.3000, resulting in a contribution margin of 37.2000 per unit.
Calculation will be:
50000 / (140.5000 – 103.3000) = 50000 / 37.2000 ≈ 1344.0860 units as the break-even point.
I’ve take four decimal places to avoid confusion.
Apologies for the earlier confusion, and I hope this clarifies it!
Regards
ExcelDemy
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 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 Dear,
You are most welcome. We are glad to hear that our resources are fantastic to test your Excel skills from different aspects. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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 absolutely right—thank you for catching that! The correct range to sum Daniel’s marks should indeed be =SUM(C5:F5), which sums the marks across all subjects for Daniel. I’ve updated the article to reflect this change, ensuring it matches the subsequent structured reference example. I appreciate your attention to detail and for bringing this to my attention!
Regards
ExcelDemy
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 Joe,
Yes you can create a new cell style of your choice in Excel. In our article we have shown the steps to craete customized new cell style.
How to Create New Cell Styles in Excel
Excel provides the flexibility for users to create custom cell styles.
Steps:
For example, we can demonstrate this with a fill color such as “Gold Lighter 80%“.
After creating the style, you can easily apply it to selected cells using the Cell Styles option from the Home tab.
Custom cell styles are available only in the specific workbook where they were created.
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 Shibani Agrawal,
You can use our sample supply chain data modify it based on your requirements.
Download the Excel file:
Dataset of Supply Chain: Sample Supply Chain Dataset.xlsx
Hello Nidhi,
You are most welcome. Thanks for reading our article. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello Georg,
You are most welcome. Glad to hear that you found our article great and useful. Yes, this article will help you to know everything about Scatter Chart.
Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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 David,
You are most welcome. Thanks for your feedback! The INDEX is an array function it recalculates every time there’s a change in the referenced data, especially if you’re working with large datasets for these reasons the file may slowdown. Though OFFSET is volatile function, but in some cases, calculates faster based on the data structure and size.
If your dataset is extensive, you might see better performance with OFFSET.
It’s great to hear that OFFEST is more efficient for your data type. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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 Mr. John,
You are most welcome. 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 Candy Neal,
To count how many users were active during June 2024, you can use a formula that checks if the active date is before or equal to the end of the month and if the inactive date (if it exists) is after the beginning of the month.
Insert the following formula in cell D2 and drag it down:
=IF(AND(B2<=DATE(2024,6,30), OR(C2="", C2>=DATE(2024,6,1))), 1, 0)
If you want to use it for other months change the date range in the formula.
To count total active users, insert the following formula in E2 cell:
=SUM(D2:D6)
Download the Excel file:
Count Total Active Users.xlsx
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 Puri Nabaraj,
Thank you so much for your kind words! We’re glad to hear that our resources and blogs have helped you learn Excel formulas in such a short time. Your support motivates us to keep providing valuable content. Keep up the great work, and feel free to reach out if you have any questions or need further assistance!
Thanks again for your valuable feedback. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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 Okto,
You are most welcome. Glad to hear that the formula was great to you. We always try to provide the best useful working formulas to ease your works.
Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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 Paul,
You are most welcome. Glad to hear that the budget sheet is helpful for you. 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 Jon Peltier,
We appreciate your input. The base values in the stacked waterfall chart are calculated by starting with an initial base of 10,000 and adjusting each subsequent month’s base based on the sales flow. The base values are correctly calculated by considering the net change (positive minus negative values). The chart is intended to reflect cumulative totals, not simply stacked values. However, I appreciate your input and will ensure that this methodology is explained more clearly to avoid any confusion.
Regards
ExcelDemy
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 Antony,
You are most welcome. Glad to hear that helped you to solve your problem. If you have any more questions, feel free to ask!
Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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 Mohammad Afzal,
Glad to hear that you learned well. Our aim is to help you learn Excel easily. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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 ExcelUser1,
Thank you for your feedback! The article primarily focuses on different methods to paste or display content, which may not fully address issues with copying merged cells directly. The challenge often lies in Excel’s limitations when copying merged cells.
If you’re facing specific issues with copying, we recommend trying unmerging cells before copying, or using VBA as outlined in the article. If this doesn’t resolve your issue, feel free to share more details, and we’ll be happy to assist further!
Regards
ExcelDemy
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.
You can adjust the includeChart parameter as needed to ensure that only the pivot table is included in the email body.
Regards
ExcelDemy
Hello Pat Grappe,
It might be a version difference or an issue with Excel’s settings. You can try the following steps:
1. Ensure that the cells where the labels will be printed are selected.
2. Go to the Home tab in Excel.
3. Click on the Borders option in the Font group, and choose the desired border style from the dropdown.
If the Grid option is not visible, this workaround will still allow them to set borders properly.
Regards
ExcelDemy
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 Michael,
You are most welcome. You can auto populate events in the calendar sheet from your entry data sheet but Excel formulas has limitations over handling multiple overlapping events. Formulas will concatenate multiple events into a single cell, but each overlapping event will be separated by a line break or other delimiters.
You can use the following formula in your event cell to auto populate events.
=IFERROR(TEXTJOIN(CHAR(10), TRUE, FILTER(‘Entry Data’!$B$2:$B$100, (‘Entry Data’!$D$2:$D$100 <= B10) * ('Entry Data'!$D$2:$D$100 + 'Entry Data'!$C$2:$C$100 - 1 >= B10) * (‘Entry Data’!$E$2:$E$100 = 8) * (‘Entry Data’!$F$2:$F$100 = 2024))), “”)
Based on Month please change the Month Number and Cell reference for each month and each cell.
If an event spans multiple days, the formula will check if the current day falls within the event duration and will display the event in the cell. If multiple events occur on the same day, they will all be concatenated in the same cell.
Download the Excel file:
Monthly Event Chart
Regards
ExcelDemy
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.
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 Jennifer Almeida,
In our existing VBA code, the previously stored values in the output cell are overwritten when a new selection is made. This happens during the Button_Click event when the new selection is written directly into CheckListOutput, replacing the previous value.
To preserve previous selections, we adjusted the logic so that the newly selected value is appended to the existing content rather than overwriting it. Here’s an update to your code:
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 Jason Ngo,
Thanks for your suggestion, we appreciate it deeply.Enabling Excel 4.0 macros and restarting the file should indeed help with resolving the #block error. Your step-by-step explanation is really helpful, especially the reminder to close and reopen the file for the changes to take effect. Much appreciated.
Regards
ExcelDemy
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 Yufeng,
You are most welcome. Thanks for your appreciation it means a lot to us. 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:
Regards
ExcelDemy
Hello Heather,
May be the problem is related to Excel’s limitations when applying multiple conditional formatting rules or when handling a large range with VBA. This could be causing the code to slow down or enter an infinite loop. A possible solution is to break the range into smaller sections or optimize the code by limiting the number of FormatConditions applied at once.
Here, I’m Reviewing the loop logic and error handling, could also help prevent the program from freezing.
I used FormatConditions.AddUniqueValues with xlDuplicate to highlight duplicates directly.
Then used the Chunk size logic is retained to process large ranges in smaller sections.
All the ranges are based on our existing Excel sheet.
Use the updated VBA code:
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 Stephen,
It’s not possible to set the color or grayscale options to appear as default when opening the print menu in Excel. Those settings are controlled at the system level via your printer preferences. In Excel, you can still access these settings, but each time you print, you’ll need to manually adjust the color options unless they’ve been preset in your system’s printer properties.
To adjust your printer preferences at the system level rather follow these steps:
1. Go to your Devices and Printers settings in your operating system.
2. Right-click on your printer >> select Printing Preferences, and configure your preferred settings there.
This will set your defaults across all programs, including Excel, without needing to change them each time in the print menu.
Regards
ExcelDemy
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:
In the first InputBox insert the dataset range.
Then choose the column number to spilt the data.
Regards
ExcelDemy
Hello Demba,
We have created a dataset for Customs Compliance Monthly Report in Excel based on your given fields.
Download the Excel file from here : Customs Compliance Monthly Report.xlsx
Regards
ExcelDemy
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 Fritz,
The VBA code is working fine in our end.
Make sure to follow all the steps:
Create the drop down list first then place the VBA code in that sheet.
Double-check that the VBA code is correctly placed in the Sheet where the data validation with drop down list exists.
Also, ensure that there are no conflicts with other existing macros or data validation settings. If the issue persists, try testing the code in a new worksheet to see if it works there.
You can download our workbook for testing purpose: Selecting Multiple Options from Drop Down.xlsm
Regards
ExcelDemy
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 Balaji,
All the solutions are available in the Solution sheet of the Excel Workbook. Excel file is given in the Download practice Workbook section.
All the solutions are given part by part in the Solution sheet.
Regards
ExcelDemy
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 Mel,
You can use a custom formula in Excel’s conditional formatting to highlight cells in column B and C based on the presence or absence of specific text (deed) in another column G.
Follow the steps given below:
1. Select the range in columns B and C that you want to format.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose “Use a formula to determine which cells to format”.
4. Enter the following formula: =ISERROR(SEARCH(“deed”,$G1))
This will highlight cells in columns B and C if the corresponding cell in column G does not contain “deed.”
5. Set the formatting style and click OK.
Regards
ExcelDemy
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 Don,
There is no formula in cell C5, and cells C5 only contain marks. So, there’s no risk of creating a circular reference. The steps should work as described. Can you please check out this is the right article you commented?
Thanks for your feedback.
Regards
ExcelDemy
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 Mohammad Afzal,
It’s great decision. ExcelDemy can help you to start your learning. Our website contents are designed to help you learn Excel from basics to advanced.
Follow our Learn Excel page here you will find all the topics related to Excel.
Explore all the Tutorial categories to explore more. We also provide courses to learn Excel technology.
Regards
ExcelDemy
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.
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 Nithin,
Your deep learning projects some of the tasks you can accomplish in Excel, such as organizing data and performing calculations. But the full scope of your project would require additional software and programming.
1. You will need to use Optical Character Recognition(OCR) software to scan the question paper and answer sheet.
It will recognize and extract question numbers and their corresponding marks.
2. Then use Python scripts to process OCR output, map questions to answers, and recognize marks.
3. Finally you can use Python libraries like pandas and openpyxl to create and format an Excel file with the extracted data.
Regards
ExcelDemy
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 Alex,
You are most welcome. Thanks for your appreciation, it’s great to hear that you found the article helpful.
To get cell color from a specific cell you can use the user defined function.
Copy paste the VBA code:
Use this function in a cell to reference the specific cell from another sheet whose color you want to retrieve.
You also can follow this article: How to Get Cell Color in Excel
Regards
ExcelDemy
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 Kurt Kruger,
Our existing code generate Code 128 barcodes, primarily focusing on Code 128B, and can switch to Code 128C for efficient numeric encoding when needed. Modified the existing VBA code to include Code 128A encoding, which includes upper-case letters, control characters, and special characters. The start, checksum, and stop characters are added to generate a valid Code 128A barcode.
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.
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.
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 Jim Green,
You are most welcome. Your appreciation means a lot to us. We are grateful that you found the insights helpful. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello David Silberberg,
To apply the conditional formatting in column H based on the data of column F and it’s formatting you need to follow the steps given below:
To highlight the entire column H.
Go to the Home tab >> click Conditional Formatting >> select New Rule.
Choose Use a formula to determine which cells to format.
Enter the formula =ISNUMBER(SEARCH(“BofA”, $F2)).
To Set Formatting:
Click on Format.
Set the font and fill colors to match those used in column F for “BofA”.
Then, select OK to apply the formatting.
Again, click OK to apply the rule.
By following this step, any cell in column H will automatically reflect the same font and fill colors as column F when the corresponding cell in column F contains “BofA”.
Download the Excel file:
Copy Conditional Formatting.xlsx
Regards
ExcelDemy
Hello Falak Niaz,
Certainly! Your formula correctly extracts the data between the two dashes. Your feedback and suggestion will be helpful for other users.
If you want you can simplify it by using MID function.
=MID(B29, FIND(“-“, B29) + 1, FIND(“-“, B29, FIND(“-“, B29) + 1) – FIND(“-“, B29) – 1)
Thanks for sharing your formula.
Regards
ExcelDemy
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 Errol,
Great to hear that you found the video interesting. You will need to use VBA code and several steps to create a form in Excel for a library database that updates with data from worksheets, step by step procedures:
Create the worksheets (Books and Users). Design the form with text boxes and buttons. Then run the VBA code.
Step 1: Organize Data in Worksheets
Books Worksheet: Include columns such as Book ID, Title, Author, Genre, etc.
Users Worksheet: Include columns such as User ID, Name, Class, etc.
Step 2: Create the Form
Insert Form Controls.
Use Insert > Form Controls to add text boxes and buttons for input.
Step 3: Add VBA Code to Handle Data
Open the VBA Editor (Alt + F11).
Insert a New Module and copy paste the following code.
Importing Books
Importing Users
Adding New Book
Adding New User
This setup will allow you to efficiently manage your library database, importing data from worksheets and updating forms without manual entry for each record.
Regards
ExcelDemy
Hello Jennifer,
Thank you for your feedback. Our template auto-calculates the “DATE DUE” field based on your inputs, ensuring accuracy and efficiency. All input fields are editable, and no password is required.
We just freeze the row-20 two maintain the template dashboard.
To unfreeze it select row-20 >> from View >> select Unfreeze Panes.
Please check our how to use this template section carefully.
If you encounter specific issues, please share the details, including screenshots, so we can assist you better. We are committed to resolving any problems you may face.
Regards
ExcelDemy
Hello Adnan,
You are most welcome. We are glad to hear that you found this article extremely helpful. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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 Bjangaiah,
Thanks for your appreciation. We are glad to hear that it was helpful to you. We always try our best to provide a proper and detail explanation. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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.
Download the Excel File:
Auto Sort Team Members Name and Assign Team
Regards
ExcelDemy
Hello Joe Bardswich,
Your statement is not clear whether you referring to AutoFill or Auto Calculations. I am providing solution for both of them.
If AutoFill is not working please check out this article: Excel Autofill is Not Working
If Auto Calculations is not working check out this article: Excel Formulas Not Calculating Automatically
If this solutions doesn’t work for you, please provide more details about what you were able to do before and what is not working now. You can attach dummy image of your dataset. This will help me give you the most accurate advice.
Regards
ExcelDemy
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.
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 Rechelyn Cañete,
You are most welcome. To develop your Excel skills you can explore the given links. Download the files from the articles to learn and practice the exercises.
Excel Data for Practice Free Download
Excel Practice Exercises PDF with Answers
Explore these categories you will find out more data entry practice here: Data Entry Practice Test & Quiz
Interview Questions with Excel Topics
MCQ Questions on MS Excel
To develop your skill from beginner to advanced level you can explore our Learn Excel page.
Regards
ExcelDemy
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 Marissa Coetzee,
Sorry to hear your problem. This issue may caused due to regional settings or data formatting differences. Make sure your regional settings in Excel match those of your teammate.
Also, check for any leading/trailing spaces or non-numeric characters in your data. Cleaning your data using the Text to Columns or Find & Replace features might help.
Or you can use the VALUE function to convert the values in number then use the Get Data option.
Clean your data then use proper data format before using the Get Data option.
Regards
ExcelDemy
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 Enrique Arizmendi,
You are most welcome. Your appreciation means a lot to us. We are glad to hear that our article helped you to explore the possibilities of Excel. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
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
Hello Kristal,
Thanks for sharing this solution! It’s a great solution for highlighting overdue dates and those due within 30 days.
To avoid formatting blank cells, you need to use a more specific condition.
You can apply conditional formatting with the formula: =AND(A1<>“”, A1
This formula will check if the cell is not blank (A1<>“”), and then it will verify if the date is less than 30 days from today (A1. This ensures that only non-blank cells with dates within the next 30 days are formatted.
If you need further assistance please let us know. Keep learning Excel with ExcelDemy.
Regards
ExcelDemy
Hello Rich Courtney,
The green circle with an arrow at the tip of your pointer is related to the Caps Lock feature. This is a new feature in Safari on macOS to indicate that Caps Lock is enabled.
To turn off or disable this feature, you can try the following steps:
System Preferences:
1. Open System Preferences.
2. Go to Keyboard.
3. Under the Keyboard tab, look for any settings related to Caps Lock or Keyboard shortcuts and disable any relevant options.
Safari Preferences:
1. Open Safari.
2. Go to Safari > Preferences (or press Cmd + ,).
3. Look for any settings that might be related to this feature under the Advanced or Accessibility tabs and disable them if found.
Accessibility Settings:
1. Open System Preferences.
2. Go to Accessibility.
3. Look for any settings related to keyboard or pointer and disable any features related to Caps Lock notifications.
Sometimes reinstalling the application can help resolve unexpected behavior.
If none of these steps work, consider reaching out to Apple Support for more detailed assistance. They might provide a specific way to disable this new feature or offer an alternative solution.
Regards
ExcelDemy
Hello Alvin,
This VBA macro sends an email if the date in a selected range matches Today’s date. It prompts the user to select a range of cells and checks each cell in the range. If a cell’s value matches the current date, it asks the user for the email subject, sender, recipient, CC, BCC, and message body. It then uses Outlook to send the email with the provided details.
In your date range you must include the date which will match Today’s date.
To understand the logic I added a debugging option here:
If you need further customization or have a specific dataset, please provide more details about how you want the VBA code to work.
Regards
ExcelDemy
Hello Rav,
You are most welcome. To add the headers along with the searched values use the following updated VBA code:
Output:
Regards
ExcelDemy
Hello Undent,
Sorry to hear your problem. But our Method-1 is working perfectly. Can you check your IF condition based on your case or dataset.
Here, I’m attaching a image where IF function returns the values based on the conditions.
If you want you can share your case here.
Regards
ExcelDemy
Hello Gordan,
It’s a common issue with Excel pivot tables and slicers. You can follow the steps given below to maintain the formatting of your pivot table when using slicers:
Use Cell Styles:
Instead of relying on pivot table styles, try applying cell styles to the pivot table.
Select the cells you want to format, go to the “Home” tab, and choose “Cell Styles“. This approach can help preserve formatting when slicers are used.
Use a VBA Code:
If you’re comfortable with VBA, you can use a VBA script to reapply formatting whenever the pivot table is updated.
Right-click the sheet tab with your pivot table, choose “View Code,” and paste this code into the worksheet module.
Create a Custom Pivot Table Style:
You can create a custom pivot table style that includes all your desired formatting. To do this:
Click on your pivot table.
Go to “Design” > “PivotTable Styles” > “New PivotTable Style.”
Define your style, including fonts, colors, borders, etc.
Apply this custom style to your pivot table.
By trying these methods, you should be able to maintain your pivot table’s formatting even when using slicers. Let me know if you need more specific guidance on any of these topic.
Regards
ExcelDemy
Hello Veekay,
You are most welcome. We try to summarize all possible solutions so that you can use any of the solution based on your dataset type. Keep solving Excel problems with ExcelDemy.
Regards
ExcelDemy
Hello Morgan Trevino,
Checked method-2 to confirm the issue. Method-2 is working perfectly:
To ensure that only overdue dates are highlighted using method 2, please check the conditional formatting formula used. The formula should compare the dates with today’s date.
You also can use the formula: = A1 (replace A1 with the appropriate cell reference for your dates). This formula will highlight only the dates that are earlier than today’s date.
If you need further guidance, please share the exact formula you used so I can help you correct it.
Regards
ExcelDemy
Hello Larry,
Based on your previous comment.
To add the date to column K of the matched row of TCN of column B, you will need to use the Date function. It will provide the current date.
Here, I updated the VBA code to add the date. Make sure to place this code in the appropriate worksheet module where you are scanning the barcodes.
Regards
ExcelDemy
Hello Guy,
You cannot open the same Excel sheet in safe mode without closing it first. Safe mode is designed to start Excel without add-ins or other customizations, which requires restarting the application. If you are experiencing issues with a specific sheet, try saving your work, close Excel, then reopen it in safe mode using the Ctrl key method:
1. Press and hold the Ctrl key.
2. While holding Ctrl, click on the Excel icon to open it.
3. A prompt will appear asking if you want to start Excel in safe mode. Click Yes.
This method allows you to open a new instance of Excel in safe mode while keeping your current sheet open.
Regards
ExcelDemy
Hello Larry,
To add the date to column K of the matched row of TCN of column B, you will need to use the Date function. It will provide the current date.
Here, I updated the VBA code to add the date. Make sure to place this code in the appropriate worksheet module where you are scanning the barcodes.
Regards
ExcelDemy
Hello Srivatsan Guru,
You are most welcome. It’s great to hear that it was helpful to you. Keep learning Excel with ExcelDemy.
Regards
ExcelDemy
Hello AMG,
You are most welcome. It’s great to hear that Method-7 was helpful to you and it worked perfectly. Keep learning Excel with ExcelDemy.
Regards
ExcelDemy
Hello Jerry,
Method-3 is working perfectly. To get the page number in a proper way you need to insert the page number first. Follow the given steps to insert the page number in a selected cell.
First select a cell >> go to Developer tab >> from Macros >> select Page_Number_Selected_Cell().
It will insert the page number.
Remember to select the cell sequentially to add page numbers.
Output:
Regards
ExcelDemy
Hello Michel Claes,
You are most welcome. Thank you for your feedback! I’m glad you found the article helpful.
Static oldval(6 To 10) As Variant: Declares a static array oldval to store previous values of cells F6 to F10, so the values persist between subroutine calls.
The variable “oldval” is initialized at the first run of the subroutine to store the initial cell value before any changes occur. It’s updated only when a change is not detected to retain the previous value for comparison. This way, it can accurately identify when a change happens in the cell value.
If you have any further questions or need more clarification, feel free to ask!
Regards
ExcelDemy
Hello Rolands,
You are most welcome. Thank you for your feedback! I’m glad the solution worked for you. Replacing Target.Address with Target.Column = 5 is a great adjustment for your setup with multiple drop-down cells in column E. It’s wonderful to hear that it continues to work even for newly created and copy-pasted rows.
We used Target.Address initially to target specific cells, making it easy to apply the method to designated areas. This approach provides precision and control, especially useful for varying drop-down list configurations.
Regards
ExcelDemy
Hello Melody,
Thank you for your interest! Unfortunately, we are unable to provide a PDF version of the content from the Learn Excel page on ExcelDemy. But you can always access and study the material directly on our website at any time. If you have any specific topics or questions, feel free to let us know, and we’ll be happy to help!
Regards
ExcelDemy
Hello Hari Lalam,
You are most welcome. It is great to hear that the Google Sheets method accomplished your task. Keep learning Excel with ExcelDemy.
Regards
ExcelDemy
Hello Johnson Helen,
You are most welcome. We are glad to hear that it was really helpful to you. In our Practice Test & Quiz category you will find out more data entry practice here: Data Entry Practice Test & Quiz
Keep learning Excel with ExcelDemy.
Regards
ExcelDemy
Hello Raif,
You are most welcome. We are glad to hear that our example worked for you. You can explore more article related to VBA. Keep learning Excel with ExcelDemy.
Regards
ExcelDemy
Hello Samwel Gurt,
You are most welcome. We are glad to hear that our article is excellent and satisfying to you. You can explore more article related to these topic. Keep learning Excel with ExcelDemy.
Regards
ExcelDemy
Hello Samwel Gurt,
Thank you so much. We are glad to hear that you loved our article. You can explore more article related to these topic. Keep learning Excel with ExcelDemy.
Regards
ExcelDemy
Hello Me,
You are most welcome. Your appreciation means a lot to us. You can explore more article related to these topic. Keep learning Excel with ExcelDemy.
Regards
ExcelDemy
Hello Tony Jin,
You are most welcome. Your appreciation means a lot to us. You can explore more article related to these topic. Keep learning Excel with ExcelDemy.
Regards
ExcelDemy
Hello Matthew Richard Raison,
The problem with the VBA code not working for a different roster template could be due to differences in the sheet structure. While using different sheet structure update the VBA code according to you new sheet:
1. Ensure the cells for month and year are correctly referenced (W2 and AC2).
2. Adjust the CopyToSheet subroutine to match the layout and structure of your specific roster sheet.
3. Verify that the new sheet created is an exact replica by manually inspecting the VBA code to ensure all ranges and cell references align with your roster’s layout.
Make these adjustments, and the code should work as intended.
Regards
ExcelDemy
Hello Claudia Sgiarovello,
On our end the VBA code is working perfectly. Rechecked the code to confirm it.
Make sure you replace the curly quotation marks with straight quotation marks to avoid syntax errors in VBA. If the problem persists, double-check the name of the range “CheckListOutput” to ensure it matches exactly with what is in your Excel workbook.
1. Ensure all quotes are straight quotes (“).
2. Check that “CheckListOutput” matches the actual named range in your Excel workbook.
3. Verify that “checkList” matches the name of your ActiveX listbox control.
If these steps don’t resolve the issue, consider providing more details about any specific error messages or behaviors you’re encountering.
Regards
ExcelDemy
Hello Muskan Gadodia,
To Load/Export the table into an Excel worksheet follow these steps:
Select the sheet name (Departmental Sheet) and Right-Click on the sheet. Then, select Load To.
Import Data dialog box will pop up
From there select Table.
Then select the location where you want to put the data. Here, selected A1 cell.
Finally, data is exported to a table.
You will get the steps in our updated article also.
Regards
ExcelDemy
Hello Anonymous,
Thank you for your feedback! I’ve updated the document to include detailed explanations for each example, showing exactly how each formula works step-by-step. This should make it easier for you to understand how to adapt the formulas for your own spreadsheets.
I’m glad the initial examples were helpful, and I hope the added explanations will provide the deeper understanding you’re looking for. If you have any more questions or need further clarification, please feel free to ask. Happy spreadsheeting!
Regards
ExcelDemy
Hello Idrissa A.Kamara,
You’re most welcome! I’m glad to hear that the materials and tutorial have been helpful in enhancing your Excel skills and intelligence. Your dedication to learning and improvement is truly inspiring. God bless you as well, and if you ever need further assistance or have more questions, feel free to reach out. Keep up the great work!
Keep leaning Excel with ExcelDemy.
Regards
ExcelDemy
Hello Bob,
Thank you for bringing this to our attention. Our template is designed to handle various loan amounts and interest rates. I’ve checked the template again by changing loan amount, interest , tenure etc and it’s working perfectly. I will request you to check, How to Use This Template part of the article.
Templates’ intial image:
Different Loan Amount and Interest Rate:
Another changes:
It sounds like there might be a formatting or input error causing the #VALUE! error. Please ensure that:
Loan Amount and Interest Rate Formats: The values are entered as plain numbers without any special characters or spaces. For example, enter 200000 for $100,000 and 8 for an interest rate of 8%.
Correct Cells: The values are being entered in the correct cells specified for loan amount and interest rate.
If you’ve checked these and are still encountering issues, please feel free to share a screenshot or more details about the error. We’ll be happy to assist further.
Thank you for your patience and understanding.
Regards
ExcelDemy
Hello Faye,
The formula provided calculates the straight-line (great-circle) distance between two points, not accounting for roads, highways, or specific travel routes. This is why you notice a discrepancy when comparing it to Google Maps or Bing, which calculate driving or walking distances along actual travel paths.
The straight-line distance is the shortest path over the earth’s surface, which does not reflect the true travel distance that might involve various roads and pathways.
Thanks for commenting and asking questions! It’s great that you’re diving into the details and eager to learn. No worries, it’s not a dumb question at all! Learning these differences is part of the process. Keep up the good work!
Regards
ExcelDemy
Hello Andrew,
There is a way to set up to be used repeatedly and routinely but for that you will need to install Adobe Acrobat SDK this is necessary for controlling Acrobat via VBA. Then you will need to use VBA code and Task Schedular.
Copy the VBA code to find the PDF file from a directory.
Now, set up the Task Scheduler:
1. Open Task Scheduler
Search for “Task Scheduler” in the Windows Start menu and open it.
2. Create a Basic Task
o Click on “Create Basic Task…” in the Actions pane.
o Name your task (e.g., “Automate PDF to Excel”) and provide a description.
o Click “Next”.
3. Set Trigger
o Choose when you want the task to start (e.g., Daily, Weekly).
o Click “Next” and set the start date and time.
o Click “Next”.
4. Set Action
o Choose “Start a program” and click “Next”.
o In the “Program/script” field, enter the path to the Excel executable (e.g., C:\Program Files\Microsoft Office\root\Office365\EXCEL.EXE).
o In the “Add arguments (optional)” field, enter the path to your Excel workbook (e.g., “C:\path\to\your\workbook.xlsm”).
o Click “Next”.
5. Finish
o Review your settings and click “Finish”.
6. Configure Task
o Locate your new task in the Task Scheduler Library.
o Right-click it and select “Properties”.
o Go to the “Actions” tab and click “Edit”.
o In the “Add arguments (optional)” field, add the following to run the specific macro:
/e /mImportPDFData
o Click “OK” and then “OK” again to save your changes.
This setup will automatically open the Excel file and run the specified macro at the scheduled times.
Regards
ExcelDemy
Hello Sid,
You can use the Application.OnKey method to assign a macro to a specific key. To handle the “Insert” key, you need to use the correct key code.To use the Insert key as an event along with Application.OnKey do the followings:
Copy paste the following code in the Moduel:
Then copy paste the following code in ThisWorkbook:
The Workbook_Open event, will set the Application.OnKey to assign the “Insert” key to this macro and the Workbook_BeforeClose event, clear the key assignment when closing the workbook.
Regards
ExcelDemy
Hello Paolo,
You are most welcome. We are glad to hear that you found our solution easy and efficient. Keep finding Excel solutions with ExcelDemy.
Regards
ExcelDemy
Hello Nate,
You are most welcome. We are glad that our solution worked perfectly for you. Keep finding Excel solutions with ExcelDemy.
Regards
ExcelDemy
Hello Bhabani,
Please fill out this form properly to get the FREE Advanced Excel Exercises with Solutions
I received the exercise sheet via email after filling out the form.
Regards
ExcelDemy
Hello Karin Williams,
You are most welcome. Thanks for your appreciation, it means a lot to us. We are glad to hear that you found our article most comprehensive. Keep learning Excel with ExcelDemy.
Regards
ExcelDemy
Hello Keith Miller,
I understand your frustration with transitioning from MSQuery to Power Query, especially after being familiar with MSQuery for so long. The learning curve can be a bit daunting, but the good news is that Power Query is incredibly powerful and can handle the task you described quite efficiently.
To join two tables into one data set without resorting to Access or using numerous VLOOKUP formulas, Power Query is indeed your best bet.
Please follow this article to Combine Two Tables Using Power Query in Excel
To create relationship between two tables you can follow the given steps too:
Let’s say, we have two tables describing different products ordered by some customers from separate addresses and their respective prices.
I have created the first column named Customer Information with headings: Customer ID, Name, and Address.
Another table named Order Information has headings like: Name, Product, and Price.
It is noticeable that there must be a common column to create a relationship between the tables.
Here, I will show the method of creating relationships using Pivot Table. In order to demonstrate this method, proceed with the following steps.
Steps:
I have named the first table Customer and the second table Order.
So, these are the steps you can follow to create a relationship between tables using the Pivot Table option.
Read More: How to Create Data Model Relationships in Excel
Download the Excel File: Creating Relationship Between Tables.xlsx
Regards
ExcelDemy
Hello SH,
The VBA code is working perfectly. Similarities of two columns are highlighted in Red color.
Here, I am uploading a video of VBA code:
Video of VBA code Highlighting Similarities
Kindly use the following code again:
Please click on Yes to highlight the similarities.
Regards
ExcelDemy
Hello Daz C,
Making AllTaskList sheet a table is not an issue. May be Named Range is not working properly that’s why VLOOKUP is not getting the lookup values.
Please, check the tasklist from Named Manager.
Go to Formulas >> from Defined Names >> select Name Manager.
Check the Tasklist contains =’AllTaskList (2)’!$B:$G
N.B: You can change your list based on your sheet.
Here, I made the AllTaskList a table to check either it’s a problem or not.
Then, checked the Task Detail sheet to see VLLOKKUP is working or not.
Here VLOOKUP is working perfectly.
I am uploading the updated Excel file, please download it from here:
Creating a Task Tracker Using Table.xlsx
Reagards
ExcelDemy
Hello Leo Mulhern,
The Excel file is working fine in my end. I updated the stocks name and it is working.
Track Stock Prices:
Updated Stock Prices:
Again, I uploaded the Excel file for you:
Track Stocks in Excel (Updated).xlsx
If new file keeps shutting down, try these steps to fix the issue:
Update Excel: Make sure Excel is up to date.
Disable Add-Ins: Open Excel in Safe Mode (hold Ctrl while opening Excel), then disable add-ins from File -> Options -> Add-Ins.
Repair Office: Go to Control Panel -> Programs -> Programs and Features, find Microsoft Office, right-click, select Change, and choose Quick Repair.
Regards
ExcelDemy
Hello Flipmode,
Thank you for your feedback and for highlighting the importance of accurate formulas. However, the provided formula in the article is giving correct results for the USD to EUR conversion as shown in the example table. The VLOOKUP formula used is correctly referencing the exchange rate and multiplying it with the USD value. Here, we are converting currencies from USD to any other currency.
USD to EURO:
USD to ARS:
N.B: In our article we used exchange rate data of 9/27/2022
Thank you for your feedback. If your specific examples work for reversing the lookup, then it’s a wonderful solution. We appreciate your suggestions and hope other users find your insights useful. Thanks for your contribution!
Regards
ExcelDemy
Hello Julie Binks,
Based on your table you can match data to return sales. Use the following formula to get data based on partial name.
=VLOOKUP(“*”&E5&”*”,B4:C11,2,FALSE)
Here is the sample data with output:
Regards
ExcelDemy
Hello Ahmed,
You are most welcome. Thanks for your appreciation it means a lot to us. Keep learning Excel with us.
Regards
ExcelDemy
Hello JM Kim,
Here, created a single bar chart horizontal with two values Active an Inactive or 1 and 0 which is displayed with different color and x axis it time value.
Download the Excel File: Single Horizontal Bar Chart Showing Active Inactive Status.xlsx
Regards
ExcelDemy
Hello Kumar Chavan,
You need to create two sheets to present your expenses.
Firstly, create Income and Expenditure Account sheet to record all incomes and expenditures for the year, including the deferred income adjustment for repairs.
Then, create Balance Sheet to present the assets, liabilities, and equity as of the year-end date.
Here is your Example Format: Template for Income and Expenditure Account and Balance Sheet.xlsx
Regards
ExcelDemy
Hello JZ,
NYSE ticker SNOW for Snowflake is included in Excel database.
To get the data follow the steps below.
Insert SNOW in any cell then click on Stocks from Data tab.
You will get all the data by selecting the fields.
Regards
ExcelDemy
Hello Imtiaz,
To do so you need to use two VBA code in your Excel workbook. One in the Module to update date and another in the first sheet where you will update the date. Make sure to replace “Sheet1” with the actual name of the sheet where you will enter the date, and “A1” with the cell reference where the date is located.
Copy and paste the following VBA code into the new module:
To automatically update the date whenever the date is changed, you need to use the Worksheet_Change event.
In the VBA Editor, double-click the sheet where you will enter the date (e.g., Sheet1) in the Project Explorer window.
Then, paste the following code:
Again, replace “$A$1” with the actual cell reference where the date is located.
Now, whenever you change the date in the specified cell on your primary sheet (e.g., Sheet1), the date will automatically be updated in the corresponding cell on all other sheets.
VBA code will assume that the date is located in the same cell on each sheet.
Regards
ExcelDemy
Hello Ramesh Satyanarayanamurthy,
To search multiple text in single cell by using the IF,ISNUMBER and SEARCH function, use the following formula:
=IF(ISNUMBER(SEARCH(“Service”, B5)), “Server Status”, IF(ISNUMBER(SEARCH(“Application”, B5)), “Application Status”, IF(ISNUMBER(SEARCH(“Connect”, B5)), “Connectivity”, IF(ISNUMBER(SEARCH(“CPU Utilization”, B5)), “CPU Utilization”, “”))))
Output:
Regards
ExcelDemy
Hello Jacob,
Thanks a lot for sharing this solution. Defining the validation string using a ListObject is indeed a smart approach. By turning the data into a table, we can avoid manually editing column addresses and ensure the code remains functional even if the table moves.
I appreciate the suggestion to make both sets of data into tables for easier referencing. This will definitely help in maintaining the code’s robustness and flexibility.
Regards
ExcelDemy
Hello Patrick Raimond,
To use the iterative formula you need to do the following changes in Excel options
Enable Iterative Calculations:
First, go to File > Options > Formulas.
Then, select Enable iterative calculation.
Today’s Output:
Regards
ExcelDemy
Hello Tejas,
If the search cell has multiple criteria, you can use the following formula:
=TEXTJOIN(“, “, TRUE, IF(ISNUMBER(SEARCH($E$4:$E$5, B4)), $E$4:$E$5, “”))
It checks if any of the values in the criteria range are found within the text in cell B4. The SEARCH function identifies the presence of these values, and ISNUMBER confirms their existence. The IF function returns the matching values, while TEXTJOIN concatenates them into a single string, separated by commas, ignoring empty results. This allows multiple matches to be displayed in one cell.
Regards
ExcelDemy
Hello D.A.
Here updated the VLOOKUPCMT function and named VLOOKUPCMT_New where handled threaded comments in newer versions of Excel.
Added deletion of existing threaded comments and handling of threaded comments
To ensure that the function can handle both old-style comments and new threaded comments. Use the following updated code:
This will copy the comments. To copy notes use the previous code.
Regards
ExcelDemy
Hello Neet,
You are most welcome. We are glad to hear that our article solved your problem. Thanks for your appreciation. Keep learning Excel with us.
Regards
ExcelDemy
Hello Tara Man,
You are most welcome. We are glad to hear that you got what you needed. Keep learning Excel with us.
Regards
ExcelDemy
Hello Muhammad Farg,
Yes, there are multiple ways to merge data under same headlines. In this article we showed how to merge sheets step by step: How to Merge Sheets in Excel
You can follow this VBA code to merge the data and arrange it at the same time under one headline:
Before using the code make sure both of your sheets contains the same headings.
Sheet1 & Sheet2:
Output:
Excel File: Merge Data in Same Headings.xlsx
Regards
ExcelDemy
Hello Agung,
To handle the leave request for next month we updated our existing template. Added a new sheet to enter leave requests. Based on this sheet modified the existing formulas to consider leave request.
You will get a roaster template without leave request:
=IF(AND(INDEX(LeaveRequests!$B$2:$B$11,MATCH(‘Roster 24”7’!$B$8,LeaveRequests!$A$2:$A$11,0),1)<=E$7,INDEX(LeaveRequests!$C$2:$C$11,MATCH('Roster 24''7'!$B$8,LeaveRequests!$A$2:$A$11,0),1)>=E$7), IF(OR($C8=””,E$7=””), “”,IF(D8= “”,C8, INDEX(Settings_Shift_Legend,IF(MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1>COUNTA(Settings_Shift_Legend),1,MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1))))&”- On leave”, IF(OR($C8=””,E$7=””), “”,IF(D8= “”,C8, INDEX(Settings_Shift_Legend,IF(MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1>COUNTA(Settings_Shift_Legend),1,MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1)))))
Here is the formula to show leave requests:
=IF(AND(INDEX(LeaveRequests!$B$2:$B$11,MATCH(‘Roster 24”7’!$B$8,LeaveRequests!$A$2:$A$11,0),1)<=E$7,INDEX(LeaveRequests!$C$2:$C$11,MATCH('Roster 24''7'!$B$8,LeaveRequests!$A$2:$A$11,0),1)>=E$7), IF(OR($C8=””,E$7=””), “”,IF(D8= “”,C8, INDEX(Settings_Shift_Legend,IF(MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1>COUNTA(Settings_Shift_Legend),1,MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1))))&” – Leave Req”, IF(OR($C8=””,E$7=””), “”,IF(D8= “”,C8, INDEX(Settings_Shift_Legend,IF(MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1>COUNTA(Settings_Shift_Legend),1,MATCH(RIGHT(D8,2),Settings_Shift_Legend,0)+1)))))
Download the Excel File: 24×7 Shift Roaster Template with Leave Requests
Regards
ExcelDemy
Hello Guadalupe Valdez,
To get value from another workbook you can use the VLOOKUP function.
In Worksheet2:
In cell B2 Enter the following formula:
=IFERROR(VLOOKUP($A2,[Worksheet1.xlsx]Sheet1!$A$2:$D$5, 2, FALSE), “”)
This formula looks up the Slot number in A2 of Worksheet2 in the range A2
of Worksheet1. The IFERROR function will return an empty string if no match is found.
Then, drag the formula down to A440 cell.
Use the same formula by changing the column number for First and Last name.
In cell C2 of Worksheet2, enter the following formula:
=IFERROR(VLOOKUP($A2, [Worksheet1.xlsx]Sheet1!$A$2:$D$440, 3, FALSE), “”)
In cell D2 of Worksheet2, enter the following formula:
=IFERROR(VLOOKUP($A2, [Worksheet1.xlsx]Sheet1!$A$2:$D$440, 4, FALSE), “”)
Final Output:
Here, I am attaching the Excel Files:
Worksheet1.xlsxhttps://www.exceldemy.com/wp-content/uploads/2024/06/Worksheet2.xlsx
Worksheet2.xlsx
Regards
ExcelDemy
Hello Michelle,
Yes, you can add a check in the VBA script to see if the search box is empty before proceeding with the search. If the search box is empty, the subroutine can exit early without performing any operations, preventing the script from unnecessarily processing all the data and causing lag.
Here, added IsEmpty() and a simple comparison to an empty string (“”) to check if the Search_Cell is empty before running the rest of the code.
If the search cell is empty, it displays a message box alerting the user and exits the subroutine early with Exit Sub.
Regards
ExcelDemy
Hello Derek,
We are glad to hear that our solution helped you. Thanks for your appreciation. Keep learning Excel with us.
Regards
ExcelDemy
Hello Yulissa Alvarez,
Based on your given scenario created a dummy dataset to auto populate one sheet values based on information from another sheet.
Here I used INDEX-MATCH functions to get data from another sheet dynamically.
Use the following formulas:
Task1: =INDEX(SheetC!$D$2:$D$13, MATCH(1, (SheetC!$A$2:$A$13=SheetB!$B2) * (SheetC!$B$2:$B$13=SheetB!$C2) * (SheetC!$C$2:$C$13=SheetB!$D2), 0))
Task2: =INDEX(SheetC!$E$2:$E$13, MATCH(1, (SheetC!$A$2:$A$13=SheetB!$B2) * (SheetC!$B$2:$B$13=SheetB!$C2) * (SheetC!$C$2:$C$13=SheetB!$D2), 0))
Task3: =INDEX(SheetC!$F$2:$F$13, MATCH(1, (SheetC!$A$2:$A$13=SheetB!$B2) * (SheetC!$B$2:$B$13=SheetB!$C2) * (SheetC!$C$2:$C$13=SheetB!$D2), 0))
Task4: =INDEX(SheetC!$G$2:$G$13, MATCH(1, (SheetC!$A$2:$A$13=SheetB!$B2) * (SheetC!$B$2:$B$13=SheetB!$C2) * (SheetC!$C$2:$C$13=SheetB!$D2), 0))
If you want to add more task just change the cell-refernce.
Output:
You can Download the Excel file:
Auto Populate Values from Another Sheet
Regards
ExcelDemy
Hello Michelle,
The “Run-time error ‘9’: Subscript out of range” occurs when a specified sheet or range does not exist. Please check the sheet names and ranges in your Searched_Sheets and Searched_Ranges arrays are correctly spelled and match exactly with your Excel workbook.
I made some changes in the code to check whether sheets and ranges exist in your Excel workbook.
Make sure to update this according to your Excel workbook:
Searched_Sheets = Array(“Dataset 1”, “Dataset 2”) # Update it with your sheet name.
Searched_Ranges = Array(“B5:F23”, “B5:F23”) # Update it with your ranges.
Added SheetExists function it will check if a sheet exists before accessing it.
Error Handling will check for the existence of sheets to prevent “Subscript out of range” errors.
Regards
ExcelDemy
Hello Daniele,
Buongiorno! Se la colonna della data è formattata correttamente ma la tabella pivot mostra ancora le date ogni giorno senza raggrupparle per mesi o anni, prova questi passaggi:
Assicurati che tutte le date siano nel formato corretto e che non siano presenti valori di testo.
Fai clic con il pulsante destro del mouse su qualsiasi data nella tabella pivot, seleziona “Gruppo” e scegli il raggruppamento desiderato (ad esempio, mesi o anni).
Se il problema persiste, controlla le opzioni avanzate in Excel per assicurarti che l’opzione “Raggruppa date nel menu Filtro automatico” sia abilitata.
Segui questi articoli per risolvere il tuo problema:
[Fix] Cannot Group Dates in Pivot Table
Excel Pivot Table Not Grouping Dates by Month (4 Solutions)
How to Group Dates in Pivot Table: 7 Methods
Good morning! If your date column is correctly formatted but the pivot table still shows dates daily without grouping by months or years, try these steps:
1. Ensure all dates are in the correct format and there are no text values.
2. Right-click any date in the pivot table, select “Group,” and choose the desired grouping (e.g., months or years).
If this doesn’t work, check the advanced options in Excel to ensure ‘Group dates in the AutoFilter menu’ is enabled.
If you still face problem, check out this articles:
[Fix] Cannot Group Dates in Pivot Table
Excel Pivot Table Not Grouping Dates by Month (4 Solutions)
How to Group Dates in Pivot Table: 7 Methods
Regards
ExcelDemy
Hello Lucy Jackson,
Thanks for your appreciation, it means a lot to us. We are glad to hear that our article solved your problem. Keep learning Excel with us.
Regards
ExcelDemy
Hello Tracy,
Thanks for your appreciation, it means a lot to us. Keep learning Excel with us.
Regards
ExcelDemy
Hello Zayaan,
You are most welcome. Thanks for your appreciation. We have a category of pivot table and data analysis. We will create more pivot table for data analysis and power pivot. Keep learning Excel with us.
Please explore our this section.
Pivot Table in Excel
Data Analysis in Excel
Power Pivot
Regards
ExcelDemy
Hello Bryan,
You are most welcome. Thanks for your appreciation. Keep learning Excel with us.
Regards
ExcelDemy
Hello Praveen
As we used digit limit in our existing code that’s why it is showing this warning.
Don’t worry! We have updated the existing VBA user-defined function to overcome the problem and work with much larger numbers. In the Indian numbering system, we use terms such as Thousand, Lakh, Crore, Arab, Kharab, Neel, Padma, and Shankh to express large numbers. So, the user-defined function will return the word-converted result using these terms. Moreover, we will use an Excel built-in TEXT function to get accurate results for huge numbers.
Use the following updated code:
=AdvancedWord(TEXT(A1,"#.00"))
Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well. Good luck.
DOWNLOAD SOLUTION WORKBOOK
Regards
ExcelDemy
Dear,
Thanks for your appreciation. Keep learning Excel with us.
Regards
ExcelDemy
Hello Lisa,
We are glad to hear that our article helped you. It means a lot to us. Keep Learning Excel with us.
Regards
ExcelDemy
Hello Alphonse
Thanks for sharing an important aspect of variable declaration. You are absolutely correct.
In VBA, when we declare multiple variables in a single line, like Dim Str1, Str2, Str3, Str4, Str5 As String, only the last variable (in this case, Str5) is declared as the specified type (String). The others (Str1, Str2, Str3, and Str4) are implicitly declared as Variant types. So, If we expect all variables to hold string values, declaring them explicitly as String ensures they behave consistently.
Why This Matters: Variants consume more memory than specific types like String. Explicit type declarations help prevent errors related to type mismatches. Moreover, string operations like concatenation might behave differently if the variable is not explicitly declared as a String. Again, If we perform validations or transformations assuming the data type is String, having a Variant could lead to bugs or incorrect results.
So, our Improved Excel VBA Sub-procedure can be following:
Thanks again for sharing your expertise in ExcelDemy Community. Stay blessed.
Regards
ExcelDemy
Hello Lisa,
Yes, you can streamline data copied from a bank statement in a Word document to an Excel spreadsheet.
Follow the steps given below:
1. Copy the data from your Word document.
2. Open Excel and paste the data into a spreadsheet.
3. Use the “Text to Columns” feature in Excel to split the data into columns based on delimiters like spaces or tabs.
4. Organize the data by month using Excel’s sorting and filtering tools.
You can follow this articles too: How to Copy from Word to Excel into Multiple Cells (3 Ways)
How to Convert Word Table to Excel Spreadsheet: 6 Quick Methods
How to Convert Word to Excel Keeping the Formatting – 2 Easy Methods
How to Convert Word to Excel with Columns (2 Methods)
Regards
ExcelDemy
Hello John Facey,
Yes, Excel has a limitation in handling very large numbers due to its floating-point arithmetic, which can lead to precision errors. When squaring 111,111,111, the correct result is 12,345,678,987,654,321, but Excel is displaying 12,345,678,987,654,300 due to this limitation. This discrepancy is because Excel can only precisely handle integers up to 15 digits. To get the exact result, consider using specialized software or programming languages that support arbitrary-precision arithmetic.
Regards
ExcelDemy
Hello Sia,
To modify the VBA code to round up the sum result to the nearest integer, you can use the Application.WorksheetFunction.Ceiling function. Use the following updated code to get your desired result:
The formula will be : =SumByColor(A1, A1:C1)
Regards
ExcelDemy
Hello Alphonse,
You are most welcome Your appreciation means a lot to us.
Regards
ExcelDemy
Hello J,
Thanks for your comment and for noticing the fact! You are right about losing conditional formatting when the pivot table is refreshed.
When a pivot table is refreshed, custom formatting will reset because the data structure may change; for example, new rows or columns might be added. Excel may not reapply the formatting rules applied to specific cells if the layout of the pivot table changes. So, in this case, you need to manually reapply the conditional formatting rules each time the pivot table is refreshed.
Regards
ExcelDemy
Hello Enrique Rafhael
Thanks for visiting our blog and sharing your problem. The existing formula returns an array containing both latitude and longitude. If you are not using Microsoft 365, you need to press Ctrl+Shift+Enter instead of pressing Enter.
However, If you want to avoid returning an array and want to get latitude and longitude individually, follow these steps:
Hopefully, following this procedure, you can avoid returning an array. Good luck.
Regards
ExcelDemy
Hello Onkar,
Please try this updated VBA code. Replace “C:\YourFolderPath\” with the path to your files. This updated code will ensures headers are copied only from the first file.
Regards
ExcelDemy
Hello Cosmin,
To fix your issue, use the following formula.
Improved Excel Formula: =INT(B5/300) & ” days ” & INT(MOD(B5/300, 1) * 5) & ” hours ” & MOD(B5, 60) & ” minutes”
Regards
ExcelDemy
Hello Rebecca,
Thanks for your compliments! Your appreciation means a lot to us.
You can create a countdown from a specific date, excluding weekends and holidays, using a combination of the TODAY, ABS and NETWORKDAYS.INTL functions in Excel.
Excel Formula: =ABS(NETWORKDAYS.INTL($C5,TODAY(),1,$C$11:$D$12))
Hopefully, you have found the solution you were looking for. You can download the workbook used to solve your problem from the following link:
CountDown Excel File.xlsx
Regards
ExcelDemy
Hello Mp3 juice,
You are most welcome. Thanks for your appreciation. Our free templates are ready to use.
Regards
ExcelDemy
Hello Valarmathi,
You are most welcome. Thanks for your appreciation.
Regards
ExcelDemy
Hello Emrah,
You are most welcome. To adapt DeepL you may use third party Add-ins, Power Query and VBA code. But VBA code will be more complex as you need to use JSON converter.
You can use the Add-ins “Text Translator for Excel”
Using Power Query with DeepL API
Use the Power Query to call the DeepL API directly from Excel.
Follow the steps given below:
Step 1: Get Your API Key from DeepL
Sign up on the DeepL website and get your API key.
Step 2: Open Excel and Launch Power Query
Open Data tab > from Other Sources > select Web.
Step 3: Configure the API Request
In the Web dialog box, enter the DeepL API URL with your query parameters,
Insert YOUR_API_KEY with your actual API key and modify the text and target_lang parameters as needed.
Then click OK.
Step 4: Transform the Data
Power Query will open a new window with the API response.
Use the available transformation tools to parse the JSON response and extract the translated text.
Finally, Close & Load to import the data back into Excel.
Regards
ExcelDemy
Hello Umeeksha Sharma,
Thank you for sharing your solution! Creating a new Excel file and importing the problematic file is indeed a practical approach. This method can help ensure the dates are correctly categorized in years and months format. If you need further assistance or have any other tips to share, feel free to let us know!
Regards
ExcelDemy
Hello IDn,
Thanks for your appreciation. It means a lot to us.
Regards
ExcelDemy
Hello Amara,
Here I’m attaching a sample dataset for CRM and Supply chain. You can modify this dataset based on your requirements.
Dataset of CRM:CRM Dataset.xlsx
Dataset of Supply Chain: Supply Chain Dataset.xlsx
Regards
ExcelDemy
Hello Abdul,
Excel file is given in the download section of the article. You also can download the template with formula from this link : Ageing Formula 30 60 90 Days.xlsx
Regards
ExcelDemy
Hello Michael George Eichbaum,
Can you please specify which portion of the article seems complex or hard to you? We will update this part for you.
But this is the easiest way to insert horizontal slicer in Excel.
Regards
ExcelDemy
Hello Nhung,
Your appreciation means a lot to us. We are glad that our article helped you to fix your issues.
Regards
ExcelDemy
Hello John,
You are most welcome. Glad to hear that it saved you from fiddling tasks.
Regards
ExcelDemy
Hello Slides Downloader,
We are glad to hear that it worked for you and you got relief from frustrating tasks. Thanks for your appreciation. It means a lot to us.
Regards
ExcelDemy
Hello Merry Gizaw,
You are most welcome.
Regards
ExcelDemy
Hello Laura,
Sorry to hear your issue. Here, I am suggesting some troubleshooting steps to resolve the problem. But I will encourage you to submit your Excel file in our ExcelDemy Forum so that we can inspect it thoroughly.
1. Remove Non-Printable Characters:
Use =CLEAN(C2) to clean the data.
2. Check for Merged Cells:
Ensure there are no merged cells in your dataset.
Data Consistency:
3. Make sure all data in Column C are of the same type (text).
4. Sort by Multiple Columns:
Use Data > Sort,
and add levels to sort by Column C first and then Column E.
5. Manual Inspection:
Check the middle section where the sort restarts for any anomalies.
6. New Worksheet:
Copy and paste the data into a new worksheet and try sorting again.
Regards
ExcelDemy
Hello Selva,
You are most welcome.
Regards
ExcelDemy
Hello Kitonski,
Here, I made some fixes. Please try this updated code:
Used the shell.Run command which is synchronous (True as the last parameter), ensuring the Python script completes before the VBA code proceeds.
Using the Now function to calculate the start and end times accurately.
To be safe used the Shell execution uses the correct quotation marks to avoid syntax errors.
Regards
ExcelDemy
Hello Hassan Ibrahim Mohammed,
Hope you are doing well. I will suggest you to go through the article again. You can download the dataset to try the calculations.
1: Likert Scale Range and Its Effect on Final Results
Here, each Likert scale response (ranging from 1 to 5) is assigned a numerical value. These values are added up for each respondent to get a total score. For example, if a respondent’s answers to five questions are 4, 3, 5, 2, and 1, the total score is 15. This summed score helps in analyzing the overall sentiment or opinion of the respondents. Higher scores indicate more positive responses, while lower scores indicate more negative responses.
2: Adding Non-Parametric Tests to Excel
To add non-parametric tests like the Mann-Whitney U test or the Kruskal-Wallis test to your Excel sheet, you can use the dataset from the article. But Excel does not have built-in non-parametric test functions, you need to manually calculate them using formulas and the Data Analysis ToolPak (If not available in Ribbon, get it from Excel options). For example, you can rank your Likert scale data using the RANK function and then apply the necessary formulas to perform the tests. It may require detailed setup and understanding of the test procedures, but it can be done effectively in Excel.
Regards
ExcelDemy
Hello Patricia,
To solve this issue:
First, add columns for monthly interest and new balance.
Then, for each debt, include columns for monthly interest and new balance.
Use the following formula to calculate monthly interest:
Monthly Interest = Previous Balance * (Annual Interest Rate / 12)
Example:
Debt 1 (D2): =C2*0.17/12
Debt 2 (H2): =G2*0.19/12
Debt 3 (L2): =K2*0.20/12
Use the following formula to calculate new balance:
New Balance = Previous Balance + Monthly Interest – Payment
Example:
Debt 1 (E2): =C2+D2-B2
Debt 2 (I2): =G2+H2-F2
Debt 3 (M2): =K2+L2-J2
Regards
ExcelDemy
Hello Sandy,
You can use this formula:
=OR(WEEKDAY([@DATE], 2) = 6, WEEKDAY([@DATE], 2) = 7)
This checks if the [@DATE] column value is a Saturday (6) or Sunday (7).
Regards
ExcelDemy
Hello Poppy Lukhele,
Could you be more specific? If possible share any sample data.
Regards
ExcelDemy
Hello Mary Grace,
Please use this updated code to get your desired result:
Here I added an input box to select the column with the names of the recipients (XRcptsName ). Then, updated the email body to include the recipient’s actual name and removed the word “text.”
Regards
ExcelDemy
Hello Paul Tupper,
Yes, we can use this formula too. Thanks for your suggestions, we really appreciate it.
Regards
ExcelDemy
Hello Paul Tupper,
Of course, you can use this formula too. Both are doing the same calculations. Our formula is simpler nothing else. Thanks for your suggestions, we really appreciate it.
Regards
ExcelDemy
Hello George Jululian,
You are most welcome.
Regards
ExcelDemy
Hello Hen,
Thanks for your appreciation.
Regards
ExcelDemy
Dear,
You are most welcome.
Regards
ExcelDemy
Hello Jeromy Adofo,
You are most welcome. please stay connected with us.
Regards
ExcelDemy
Hello Myron,
You are most welcome.
Regards
ExcelDemy
Hello Aleksandar,
You can concatenate the cell values into a single string to get one QR code from different cell values.
You can use the following code:
Regards
ExcelDemy
Hello Muhammad Afzaal Jutt,
You need to use a combination of functions like SUBSTITUTE, TEXTJOIN, FILTERXML, and SUM. Currently, Excel does not directly provide a simple formula for this without VBA.
You can use the following formula:
=SUM(FILTERXML("<t><s>" & SUBSTITUTE(SUBSTITUTE(A1, " ", "</s><s>"), "$", "") & "</s></t>", "//s[number(.)=.]"))
Regards
ExcelDemy
Hello Ckwong,
To get the result as State Name. You need to use the state name column as an array of INDEX function. Just replace the cell range from C5:C12 to B5:B12.
You can use the following formula:
=INDEX(B5:B12, MATCH(TRUE, INDEX(C5:C12>700000, 0), 0))
Regards
ExcelDemy
Hello Esraa Yaseen,
You are most welcome. We are glad to help you.
Regards
ExcelDemy
Hello Prabhakar Srinivasagam,
You are most welcome. Your kind words means a lot to us. We are always here to help you.
Regards
ExcelDemy
Hello Abinsh,
You are most welcome.
Regards
ExcelDemy
Hello Jen,
Thanks for coming back, hopefully it will work this time. If it doesn’t work, you can let us know your problem in the comment section below.
Regards
ExcelDemy
Hello Usama Jamil,
You are most welcome.
Regards
ExcelDemy
Hello Atif Hassan,
Thanks for your appreciation. You can use the following formula to use XLOOKUP with tolerance level:
=XLOOKUP(TRUE, IF(ABS($C$2:$C$4 – $A$2) <= $B$2, TRUE, FALSE), $D$2:$D$4, "Not Found")
https://www.exceldemy.com/wp-content/uploads/2024/05/XLOOKUP-Based-on-Customized-Criteria.gif
Please download the Excel file for better understanding:
XLOOKUP with Tolerance Level.xlsx
Regards
ExcelDemy
Hello Danielle Davies,
You are most welcome. Your appreciation means a lot to us.
Regards
ExcelDemy
Hello Vito,
Don’t be sorry Vito. Let us know your feedback, hopefully it will work.
Regards
ExcelDemy
Hello Jose,
You are most welcome. Your appreciation means a lot to us.
Regards
ExcelDemy
Hell MM,
You are most welcome.
Regards
ExcelDemy
Hello Michael,
You are most welcome.
Regards
ExcelDemy
Hello T Althoff,
Our updated article indeed includes three templates of different cases, each addressing different scenarios. You can modify these templates to recalculate interest and principal based on irregular payment dates, they offer versatile solutions for various other scenarios.
Regards
ExcelDemy
Hello Reza Fadlilah,
Here are some articles from where you can download dataset of different types to practice your skills.
Excel Sample Data (Free Download 13 Sample Datasets)
Excel Data for Analysis (Free Download 11 Suitable Datasets)
Excel Data for Practice Free Download
Regards
ExcelDemy
Dear Ayebusiwa Oyesanmi Emmanuel,
Answers are given in the Solution sheet of Excel File which is in Download Practice Workbook section.
Regards
ExcelDemy
Hello Emon,
You can download the Cheat Sheet free of cost just by providing your valid email address. To get the files go to the “Download Excel Formulas Cheat Sheet PDF & Excel Files” section of this post and enter your email address. Then check your email to get the download links.
Best Regards
ExcelDemy
Hello Abdul,
It’s glad to hear and you are most welcome.
Regards
ExcelDemy
Hello Rhonda Rodriguez,
Thanks for your appreciation. You are most welcome.
Regards
ExcelDemy
Hello Michailmqo,
You can mail to [email protected].
Regards
ExcelDemy
Hello Rajkapoor,
Thanks for your appreciation and you are most welcome.
Regards
ExcelDemy
Hello Deborah Yassen,
You are most welcome.
Regards
ExcelDemy
Hello Kukuh Romansyah,
You are most welcome (Sama-sama). It’s glad to hear that you found it useful.
Regards
ExcelDemy
Hello Lukas Cenjar,
This VBA code won’t work on MAC but here we are giving you a sample code that might work on MAC. We updated our existing code based on MAC requirements.
1. We used AppleScript to interact with the Mail application on macOS to create and send the email.
2. Then, used MacScript to run AppleScript commands from VBA to handle Mac-specific scenarios.
Regards
ExcelDemy
Hello ER,
We are glad to hear that our article was helpful to you. Thanks for your appreciation. Keep learning Excel.
Regards
ExcelDemy
Hello Jan,
You need to scale the image to show the borders.
You can use the following updated code to do so.
Here, I used 0.9,to scale 90% of the cell’s width and height.
Next, used LockAspectRatio to maintain the aspect ratio of the pictures.
Then, Adjusted the alignment using Left and Top, it will center the picture within the cell after resizing.
Regards
ExcelDemy
Hello Alami,
Yes. you need to select the cell in the InputBox every time you want to clear the cells.
But if you want to avoid using InputBox, I’m giving you two different code to do so.
Mentioned the range then clear cells:
Select the cells then run the VBA code:
Regards
ExcelDemy
Hello A.Nirmala Rani,
You can the following formula for the volume of a cylindrical segment:
Where:
V is the volume in liters,
L is the length of the cylinder in meters,
R is the radius of the cylinder in meters,
h is the height of the liquid level in meters.
Given the specific dimensions:
L=5.0 meters (500 cm),
R=1.0 meter (100 cm),
h varies from 0.01 meters (1 cm) to 2.0 meters (200 cm)
This formula calculates the volume of the liquid based on the height from the base up to the liquid level within a horizontal cylindrical tank. To use this formula, ensure to convert all measurements (radius, length, and height) to meters before applying them in the calculation.
At 1 cm height: Approximately 9.41 liters
At 2 cm height: Approximately 26.59 liters
At 3 cm height: Approximately 48.77 liters
At 4 cm height: Approximately 74.97 liters
At 5 cm height: Approximately 104.62 liters
At 10 cm height: Approximately 293.63 liters
———————————————————
To see the full list of calculated volumes for each centimeter increment from 1 cm to 100 cm for the horizontal cylindrical tank, Download the Excel File.
List of Calculated Volumes.xlsx
Regards
ExcelDemy
Dear Curt,
It’s glad to hear that our article solved your issue. You are most welcome.
Regards
ExcelDemy
Hello Kevin,
Here the needle is pointing at 45 but as we kept the number inside the pie chart may seem 55 but actual point is 45. For your better understanding I am attaching another article for the clear view. Sorry for the confusion.
Please read this article from step 6: How to Create a Gauge Chart in Excel (With Easy Steps)
You can watch these two images to clear the confusion.
Regards
ExcelDemy
Hello Harry Ingram,
You can use this formula to extract only the numbers, including the decimal point.
=TEXTJOIN(“”, TRUE, IFERROR(IF(ISNUMBER(SEARCH(MID(B5, ROW(INDIRECT(“1:” & LEN(B5))), 1), {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″,”.”})), MID(B5, ROW(INDIRECT(“1:” & LEN(B5))), 1), “”), “”))
Feel free to download the Excel file from the link below, you’ll find examples of the formula.
Excel file: Extract Decimal Point Numbers
Regards,
ExcelDemy
Hello Paresh Kanti Paul,
This article includes two dynamic ways for currency conversion.
Real Time Currency Converter in Excel
Regards
ExcelDemy
Hello Martin,
We are glad to hear that you found this article useful! Including a real-life example in tutorials can indeed make the steps more relatable and easier to understand. Applying these models to specific scenarios like maximizing profit by determining the production units for products A and B is a great way to leverage linear programming. We will include this in our next update.
Regards
ExcelDemy
Hello Karunesh Pandey,
As calibration details varies with lab type we can provide you the criteria. You can fill out your information and edit the list.
Regards
ExcelDemy
Hello Gary Ward,
Thanks for your valuable suggestion we will include this in our article.
Regards
ExcelDemy
Hello Liz,
You can avoid the absolute reference ($ sign). Here absolute reference is used so that it can avoid error if anyone copy the formula in another cell.
Regards
ExcelDemy
Hello Alphonse,
We are glad to hear from you again! Don’t worry about missed message, technical glitches happen to the best of us. We are glad to hear that the code for counting Mondays worked well for you. Thank you so much for expressing your gratitude—it’s always means a lot to us to know when something was helpful. You are most welcome and cheers to you too.
Regards
ExcelDemy
Hello Cynthia Satterwhite,
Neither any problem nor any solution are given. These dataset are given to practice by yourself.
Regards
ExcelDemy
Hello Nilanjan,
Excel for Mac doesn’t support the WorksheetFunction.WebService method due to differences in how Excel for Mac interacts with web services compared to Excel for Windows. In Mac, you need to directly access Google Translate’s API using HTTP requests or using a different method provided by Google Translate.
Replace “YOUR_API_KEY” with your actual Google Cloud Platform API key.
Remember to enable the “Microsoft XML, v6.0” reference in the VBA editor (under Tools > References) to use CreateObject(“MSXML2.XMLHTTP”) for making HTTP requests.
Regards
ExcelDemy
Hello Umang,
Here are some articles from where you can download dataset of different types to practice your skills.
Excel Sample Data (Free Download 13 Sample Datasets)
Excel Data for Analysis (Free Download 11 Suitable Datasets)
Excel Data for Practice Free Download
Regards
ExcelDemy
Hello Sameul Smith,
You can follow any of the methods of your choice to make your list. If you face any problem regarding it you can share your list with us. If you want to upload any image or Excel file you can post it on our ExcelDemy Forum.
Regards
ExcelDemy
Hello Fredrick Aringo,
You must use the last line to do AutoFilter based on criteria.
Please avoid the first line. We updated our code.
Regards
ExcelDemy
Dear Bharath L,
You are most welcome. We are glad that our solution worked for you.
Regards
ExcelDemy
Hello,
These datasets are given to use it for practice purpose. Currently we don’t have any solution of the task. But if you want you can send your completed task, we will review it for you.
Regards
ExcelDemy
Hello NadineB,
You are most welcome. Thanks a lot for your appreciation.
Regards
ExcelDemy
Hello Valen,
Can you mention the URL, please? In our site all URL’s of this article are working perfectly.
Regards
ExcelDemy
Hello Dianne Waginauru,
You are most welcome and thanks for your appreciation. All the very best for you project management journey.
Regards
ExcelDemy
Hello Gwenn,
Thanks for your suggestion. We deeply appreciate the solution provided in the comments. Thank you for your helpful contribution!
Regards
ExcelDemy
Hello Ameer Ahmed,
We don’t have any dataset you asked for and your question is not clear enough. Our datasets are given in the article.
Regards
ExcelDemy
Hello Gaurav,
You are most welcome.
Regards
ExcelDemy
Hello Joellah,
Thanks for your appreciation, it means a lot to us. We are trying our best to provide Excellent articles. Keep reading to boost your knowledge.
Regards
ExcelDemy
Hello Leonardo Lagos,
You can use the HYPERLINK function in Excel 360 online version.
Insert the following formula in your formula bar: =HYPERLINK(“#’SheetName’!A1”, “Link Text”)
But unfortunately, you can’t directly use the context menu to add a hyperlink to another sheet in Excel Online. You’ll need to use the formula approach described above.
Regards
ExcelDemy
Hello Shaul Bel,
Thanks for your appreciation. You’re welcome and I’m really glad to hear that the SUBTOTAL formula worked well for you in achieving the desired results. If you have any more questions or need further assistance with formulas or anything else, feel free to ask!
Regards
ExcelDemy
Hello Michelle G,
Thank you for reaching out to us. We request your attention to the above article to get your desired template on bonds purchased at a discount using the effective interest rate method.
Your requested update is incorporated into our template. Please refer to the sheet named ‘Effective Interest (Discount)’ for the template on bonds purchased at a discount using the effective interest rate method. Click on the three dots (…) if you don’t find the template in your downloaded file.
Regards
ExcelDemy
Hello Martin,
Excel for Mac does not offer right-clicking option like Excel for Windows.
You can double click on the macro button or you can try the control click.
A common workaround is to delete the existing button and create a new one, typing the correct text when you first insert it. While this might seem inefficient but you can “edit” the button text.
Regards
ExcelDemy
Hello ScubaCamper,
Thanks for your appreciation. You are getting the error with asterisk because your formula attempts to multiply by 1 (*1) directly within the MID function’s parameters, that is not correct. You need to apply the multiplication outside of the MID function but within the array operation.
Please try this formula:
=TEXTJOIN(“”, TRUE, IFERROR(MID(B5, ROW(INDIRECT(“1:” & LEN(B5))), 1)* 1, “”))
In your formula style the formula would be: textjoin(“”,true,iferror((mid([cellref],row(indirect(“1:”&LEN([cellref]))),1)*1),””))
The purpose of multiplying by 1 (*1) in Excel formulas often is to convert text numbers to actual numeric values.
Regards
ExcelDemy
Hello Dan,
You can use this VBA code to delete sheet named “BETA”, from multiple Excel files which are in same folder.
Regards
ExcelDemy
Hello Michelle,
You are most welcome. Your appreciation means a lot to us.
Regards
ExcelDemy
Hello Terry Wayne Sutfin,
Thanks for your appreciation, it means a lot to us.
Regards
ExcelDemy
Hello Margit,
You are most welcome.
Regards
ExcelDemy
Hello Jitendra Sahu,
Thanks for your kind words. Our goal is to make Excel easy for everyone.
Regards
ExcelDemy
Hello Melissa,
We are glad that our article solved your problem. You are most welcome. Thanks for your appreciation.
Regards
ExcelDemy
Hello Robert,
You are most welcome. Thanks for your appreciation.
Regards
ExcelDemy
Hello Ayokanmi,
Thanks for your appreciation. You are most welcome. Our paid courses are in Udemy not in WordPress. But we have 7500++ free tutorials and 340+ YouTube videos. You can explore these learning materials for free.
Regards
ExcelDemy
Hello Feven,
You are most welcome.
Regards
ExcelDemy
Hello Brent Hamilton,
Thank you for your feedback. We used Microsoft 365 at work. We apologize for any confusion caused. The reason the “Show Changes” feature only becomes available after saving the file on OneDrive for real-time collaboration and tracking of changes. If you save it on OneDrive, it allows Excel to track changes made by different users and enables the “Show Changes” feature to display those revisions. This integration with OneDrive enhances collaboration and ensures that users can effectively manage changes made to shared documents.
Regards
ExcelDemy
Hello David Rascati,
You are most welcome. If you want you can share your code here so that we can debug it to find out the reason.
Regards
ExcelDemy
Hello Mervyn,
Thanks for visiting our blog and sharing your questions. You wanted to clarify the difference between a Table and a Dynamic Table.
Table and Dynamic table may look similar but they are not same.
In the context of the article,
Regular Table – is created manually by the user within Excel’s interface.
Dynamic Table – is generated automatically using VBA code.
When data is turned into a table, Excel automatically adds functions like sorting, filtering, and structured referencing. These tables are dynamic and adjust automatically when the data changes.
But a Dynamic Table in the article’s context likely refers to a table created dynamically using VBA code from a range. It is able to automatically manage specific criteria or changes.
Regards
ExcelDemy
Hello Phil Hall,
If you want the Excel Workbook of this article you will get it from Download Practice Workbook section.
If you want the get the advanced exercises from “Get FREE Advanced Excel Exercises with Solutions!” button please fill your information correctly in the form. Next, you will get the exercise list in your given email. For your concern I tried it again it’s working perfectly.
Regards
ExcelDemy
Hello Vijay,
Thanks for your appreciation.
Regards
ExcelDemy
Dear RB,
You are most welcome.
Regards
ExcelDemy
Dear SKM,
You are most welcome.
Regards
ExcelDemy
Dear Sudarshan Dhuru,
You are most welcome. Thanks for your appreciation it means a lot to us.
Regards
ExcelDemy
Dear Xyand,
You are most welcome. Thanks for appreciation. We updated the image of Method 6.
Regards
ExcelDemy
Hello Saeed Anwar,
You can download the Cheat Sheet free of cost just by providing your valid email address. To get the files go to the “Download Excel Formulas Cheat Sheet PDF & Excel Files” section of this post and enter your email address. Then check your email immediately after to get the download links.
Best Regards
ExcelDemy
Hello Minzameera,
Hope you are doing well. Here we calculated the overtime based on your criteria. We assumed that this time spans are for one person. If these are for different person you can modify the formula.
Formula ta calculate working hours: =(IF(B2
Formula to calculate the total working hours: =C2+C3
Formula to calculate the overtime, here we assumed the working hours are 8: =IF(D2>B6,D2-B6,”No Overtime”)
Regards
ExcelDemy
Hi Paulinus Friday,
You are most welcome. Data Files are available in the Excel Practice file section.
Regards
ExcelDemy
Hello ابو معاذ,
You are most welcome.
Regards
ExcelDemy
Hello Dwbi,
You are most welcome.
Regards
ExcelDemy
Hello Rao,
We will try to upload it in our up coming videos.
Regards
ExcelDemy
Hello Jessica H,
You are most welcome.
Regards
ExcelDemy
Hello Jennifer,
You are most welcome.
Regards
ExcelDemy
Hello Federico Cofederi,
To send Email without pressing “Send” you can use .Send property in your code instead of .Display.
Regards
ExcelDemy
Hello Obinna,
You are most welcome.
Regards
ExcelDemy
Hello Prakash,
You are most welcome.
Regards
ExcelDemy
Hello Salomé Magerstein,
You are most welcome.
Regards
ExcelDemy
Hello Kristi,
You are most welcome.
Regards
ExcelDemy
Hello Muhammad Ismail Jumarang,
You are most welcome.
Regards
ExcelDemy
Hello Lory,
You can use the following formula: =IFERROR(INDEX(‘Round 1′!$B$3:$AJ$25,MATCH(A5,’Round 1’!$B$3:$B$25, 0),34),””)
It will leave a cell blank if no matches are found.
Regards
ExcelDemy
Hello Kndn,
The Measuring unit of the result is “Mile”.
Hello,
You are most welcome.
Regards
ExcelDemy
Hello,
You are welcome.
Regards
ExcelDemy
Dear Icarus Builders,
You are most welcome.
Regards
ExcelDemy
Hello Vini,
Updated the code spilt data into multiple Excel file.
Regards
ExcelDemy
Hello Syed Mohisn Ahmed,
Updated the code in the marked section. Where I replaced “Rupees” text from condition.
You can use the following code.
Regards
ExcelDemy
Hello,
Thanks for reaching out to us.
First, remember that rules and instructions for interest calculation after garnishment order vary among countries and states. Next, post-judgment interest accrues only on the unpaid balance of the judgment.
However, to calculate interest if the judgment was turned into a garnishment, you can follow the steps below.
1. Take your judgment amount and deduct the amount of garnishment.
2. Multiply it by your post-judgment rate (%).
3. Take the total and divide it by 365 (the number of days in a year).
4. With the amount of post-judgment interest per day (in step 3), multiply it by the number of days from your date of judgment to the date you file your execution.
Simply, use the following formula to calculate interest if the judgment was turned into a garnishment:
Interest after Garnishment = (Judgment Amount – Garnished Amount) ( Interest Rate / 365) Interim Days
Thanks again for your thoughtful comments, especially the points you made about garnishment. It adds valuable perspective to our discussion.
Regards,
Abdullah Al Masud,
ExcelDemy Team
Hello Muhammad Asif,
You are most welcome.
Regards
ExcelDemy
Hello Joseph Jabatie,
You are welcome. You can get the notes or Excel workbook from the Download section.
Regards
ExcelDemy
Hello Mohammed.
Thanks for your appreciation. Our content is completely free for our users learning but don’t use it for your business purpose without proper agreement.
Regards
ExcelDemy