In this article, we'll explore 3D scatter plot in Excel. You will learn a step-by-step procedure to display a 3D scatter chart. Especially these graphs uncover ...
When it comes to software, Microsoft Excel is in a league of its own. Thanks to its many useful features, we may fully use any data. This article will cover ...
One of the most helpful software you can use is Microsoft Excel. It is possible to do an endless number of things with a dataset by utilizing Excel's ...
Microsoft Excel is considered one of the most valuable tools currently accessible. With Excel's tools and capabilities, it is feasible to perform an endless ...
Microsoft Excel is one of the most beneficial programs you can use. Using Excel's features and tools, you can do an almost infinite number of things with a ...
Microsoft Excel is a helpful program. You can conduct infinite operations on a dataset using Excel's tools and capabilities. Regularly, we must search for data ...
The tool known as Microsoft Excel is quite helpful. You can execute an infinite number of operations on a dataset using Excel's tools and capabilities. When ...
Microsoft Excel is a practical application. With Excel's tools and capabilities, you can perform an endless number of operations on a dataset. Frequently, we ...
Microsoft Excel is an intuitive tool that more than lives up to its excellent reputation. Thanks to Excel's tools and functions, we can perform various actions ...
Microsoft Excel is one of the most valuable tools available. Using Excel's capabilities and tools, a dataset can be utilized in an almost limitless way. This ...
Microsoft Excel is a practical application. With Excel's tools and capabilities, you can perform an endless number of operations on a dataset. Frequently, we ...
Microsoft Excel is one of the most useful software you can get. Using Excel's features and tools, it is possible to do an infinite number of things with a ...
Microsoft Excel is an intuitive tool that more than lives up to its illustrious reputation. We can perform many actions on a dataset thanks to Excel's tools ...
Excel by Microsoft is a user-friendly program that more than lives up to its stellar reputation. We can do various operations on a dataset thanks to Excel's ...
Microsoft Excel is, in fact, a beneficial program. We can do many things with a given dataset using Excel's tools and features. In Excel, we often need to ...
- 1
- 2
- 3
- 4
- Next Page »
Hello Raj
Thank you for reaching out with your comment. You encountered a different result than what was described in the post. I assume that you missed inserting positive numbers in the range C16:C17. However, once those values were included, I discovered a result identical to what is described in the post. Therefore, It’s essential to ensure all intended data is inputted correctly.
Regards
Lutfor Rahman Shimanto
Hello Charlotte Fahey


Thank you for reporting on this fascinating issue. I experience the same problems when a postal code begins with 0, and it is essential to preserve the leading zero when entering data into the system or application.
The Postal Code column must be formatted as text. Following that, insert the desired data.
Now, adhere to the methods mentioned in this article. Ideally, you will observe the desired results.
Regards
Lutfor Rahman Shimanto
Thank you for bringing this issue to my attention, William Wyatt. I understand that you have been experiencing difficulties using formulas on cells formatted as fractions in your workbook. I apologize for any confusion or frustration this may have caused you.
I have gone through this article and did not experience any of your issues. I am using Microsoft 365 to investigate this case. Could you share your workbook with us via email to better understand your situation? I would appreciate it if you could assist me more effectively.
Regards
Lutfor Rahman Shimanto
Hello DEBB WOLFE


We appreciate your comment. I understand your difficulty, and you can avoid the issue by importing the CVS file into the existing worksheet.
Change the column type in the Power Query window to text, as this article mentions. Next, select the Home tab. Select Close & Load and then Close & Load To at a later time.
As a result, the Import Data window will display. Check the Existing Worksheet and then press OK.
Thus, you will be able to solve the problem.
Regards
Lutfor Rahman Shimanto
Hello ARON HOLMBERG
Thank you for reporting your issues. To count the number of components for each station, when the Station ID may be in one of three different columns, you can use the SUMPRODUCT function.
=SUMPRODUCT((InputSheet!$B:$B=B5)+(InputSheet!$C:$C=B5)+(InputSheet!$D:$D=B5))
This formula will test each of the three columns for the station ID and return 1 if it’s present in any of the columns and 0 if it’s not. Then, SUMPRODUCT will sum up the results, giving you the components for the station.
This solution is more elegant than creating a new column that combines the three columns, as it avoids the need to manipulate the data. If you would like a copy of the illustrated workbook, please click the link provided below this section.
https://www.exceldemy.com/wp-content/uploads/2023/01/To-Count-the-Number-of-Components-for-Each-Station.xlsx
Best regards,
Lutfor Rahman Shimanto
Hello ABIGAYLE PAULSON
Thank you for reporting on this fascinating issue. I have reviewed this article and found an interesting idea to solve your problem. For illustration, let’s walk through 2nd Example. Filtering the dataset (Sheet3) based on cell values on another sheet (Sheet4). The context filters the dataset for Apple or Tomato products.
VBA Code:
If the changed cell is either C2 or E2, the macro will execute and filter the data in “Sheet3” based on the new values in these cells. Note that the Worksheet_Change event must be placed in the code module for the “Sheet4” worksheet.
The changes we must make to the original code to create the auto-filtering behavior:
1) We have to add a Private Sub Worksheet_Change(ByVal Target As Range) procedure to the code module for the “Sheet4” worksheet. This procedure runs automatically whenever a cell value is changed on this worksheet.
2) We can add an If statement to check whether the changed cell is in Sheet4 and C2 or E2. If so, the macro continues executing; if not, it exits without doing anything.
3) We need to move the Dim statements for product1 and product2 inside the If statement, so they are only declared if the macro is going to run.
4) The rest of the macro code is the same as the original code, so it will apply the same filter to “Sheet3” based on the new values in “Sheet4” whenever the cell value changes.
By adding this Worksheet_Change procedure to the code module, the macro will run automatically whenever a change is made to the specified cells in “Sheet4” without manually opening and running the macro.
Regards
Lutfor Rahman Shimanto
Hello NATS CO
Thanks for reaching out and posting your comment. You have given us wonderful advice, which is very appreciated. You want to modify the existing formula you have given in such a way that it will look up only a cell with value and ignore all blank cells from bottom to top.
I am delighted to inform you that I have developed such a formula by modifying your given formula. I have also developed a User-defined function using Excel VBA.
Modified Excel Formula:
Excel VBA User-defined Function:
Open the VBA Editor => Paste the following code in a module => Save.
Return to the sheet => Use the user-defined function like other worksheet functions.
I hope the idea will fulfil your requirements. Good luck!
Regards
Lutfor Rahman Shimanto
Hello AISHWARYA ROY
Thanks for reaching out and posting your query with such clarity. You want to add new rows with certain information using a cell value. Some rows contain text that can be a mixture of three texts: peanut butter and jelly sandwich. You want to add the new rows accordingly and insert values like 1. Peanut butter, 2. Jelly and 3. Bread.
I am delighted to inform you that I have developed an Excel VBA procedure that will fulfil your requirements.
Navigate to Developer >> click on Visual Basic.
Due to this, the VBA Editor window will appear.
Finally, you will see an output like the following one.
I am also attaching the solution workbook to help you understand better. Good luck!
WORKBOOK
Regards
Lutfor Rahman Shimanto
Hello Nadeem Khatri
Thanks for reaching out and posting your comment. You are right about the Percentage Completion column showing 100%. Thank you once again for noticing the interesting issue. You are also correct about the formula used in the article having no error.
Reason: The Percentage Completion column shows 100% for each task. It is important to note that we are using TODAY as a reference day. All the tasks are completed 100% for the dataset shown in the article according to today’s dates.
Note:
Things to Remember:
When inserting date values within the start date and end date columns, ensure all the dates are in the same format.
Solution:
To demonstrate, I am using the dataset mentioned in this article. However, I will use some other date values. Here, you will see an improved version of the formula mentioned in the article to avoid negative values.
Select cell E5 >> apply the following formula >> drag Fill Handle to cell E8.
I hope the idea and concept are crystal clear to you now. Stay Blessed.
Regards
Lutfor Rahman Shimanto
Hello DENNIS
Thanks for reaching out and noticing the problem. You are right about the Date values not being formatted as expected for 1st to 12th.
I am delighted to inform you that I have come up with a solution. You must choose a date format consistently while inserting date values in cells.
In this case, use the exact Date Picker mentioned in the article, which typically chooses the data format as mm-dd-yyyy. Next, you have to create an extra command button that will be responsible for inserting the chosen date value in cell B10. And the Excel VBA Event procedure will convert the date format into dd-mm-yyyy.
VBA Code for CommandButton1: Double-click on the inserted CommandButton1 >> insert the following code and Save. You can also change the destination cell by modifying the code.
Excel VBA Event Procedure: Insert the following code in the intended sheet module described in my previous reply.
OUTPUT:
Hopefully, the idea will resolve your issue. Stay Blessed.
Regards
Lutfor Rahman Shimanto
Hello DENNIS
Thank you for staying with ExcelDemy and posting your queries. You previously asked to change the date format to dd-mm-yyyy. MUSIHA MAHFUZA MUKTA provided a solution regarding your date format problem. The solution is working perfectly at my end as well.
After reviewing your requirements, I assume you want to use the date picker to insert date values in a sheet.
I am delighted to inform you that I have developed an Excel VBA Event procedure. All you need to do is keep the event procedure in the sheet module. The process will format any date values into the dd-mm-yyyy format whether you insert date values manually or date picker.
Right-click on the sheet name tab >> click on View Code.
Due to this, the VBA Editor window will appear.
Paste the following code in the sheet module >> click the Save icon.
Choose any cell >> Input date values with any valid date format.
After pressing Enter, we see the date value converted into the dd-mm-yyyy format.
Hopefully, The idea will fulfil your requirements. Good luck!
Regards
Lutfor Rahman Shimanto
Hello JAMIE
Your appreciation means a lot to us. Thanks for your nice words!
You often click the stop button after you have already stopped the timer. That results in an Error. Thank you once again for noticing the issue. Your claim is correct.
However, I am delighted to inform you that the issue can be avoided with the help of an Error Handler called On Error Resume Next.
All you need to do is to modify the StopTime sub-procedure to fulfil your requirement.
Modified Excel VBA Code:
Good luck!
Regards
Lutfor Rahman Shimanto
Hello Hate
Thank you for reaching out and posting your comment. The issue you are mentioning is correct. Thanks once again for noticing the problem.
I am delighted to inform you that our team successfully developed a procedure to make a Pie Chart with subcategories. According to the idea, we have modified the article. I recommend you go through the article again and enjoy it.
Regards
Lutfor Rahman Shimanto
Team ExcelDemy
Dear Faisal
Thanks a ton. Your appreciations mean a lot to us. I am glad you have found the article very helpful.
You mentioned the user-defined functions only work for UTM Zone and UTM Easting on your end. The user-defined function for calculating UTM Northing is not working.
However, the issue you are addressing is unclear to us. On our end, it is working perfectly. I am using Microsoft 365. However, It should not make any difference. The code should work for all versions of Excel.
Please share your workbook containing a dataset with us. You can also post your query and attach the dataset in ExcelDemy Forum. We need more information about your problem, such as if it returns any error or wrong values.
The functions often may return values using scientific notation like 2.34344E+13. To avoid this type of notation, you may use an Event procedure.
Excel VBA Event Procedure:
I am looking forward to receiving your reply. Stay blessed and Good luck.
Regards
Lutfor Rahman Shimanto
Dear Amir
Sorry for the late reply. Thanks a lot for bringing the issue to us. I have developed some user-defined functions using Excel VBA. These functions take Lat and Long values and return UTM Easting, Northing and Zone. I also have modified the article accordingly. So, I recommend you to go through the article again. You will not be disappointed, I promise.
Regards
Lutfor Rahman Shimanto
Hello MOSES
Thanks for reaching out and posting your comment. You are absolutely right. However, I am delighted to inform you that I have modified the article. After going through the article, you must be able to convert Lat Long to UTM Easting, Northing and Zone. I have developed some user-defined functions using Excel VBA to achieve the goal.
Regards
Lutfor Rahman Shimanto
Hello Dr Timber
I went through this article and did not find any technical issues. In my view, you should reconsider what you have said.
This article is about finding an X intercept. To demonstrate this point, this context considers a practical dataset where the percentage of people still using masks is calculated when the number of Covid Cases becomes Zero. I think you expected the calculation to find X Intercept for Y = 0. That’s what is calculated in those methods. When the writer introduced us to the dataset, it was explicitly mentioned.
Anyway, I am introducing another method that may overcome your confusion. I have calculated the percentage of people who still use masks when the number of Covid Cases becomes Zero. I am using FORECAST.LINEAR Function to predict the percentage. And I get the same result shown in this article.
Excel Formula:
The data considered in this article is not Linear. That’s why you get confused. However, these methods must be able to find an X intercept for any Linear data.
Stay safe. And good luck!
Regards
Lutfor Rahman Shimanto
Hello ANAND,
Thanks for reaching out and posting your interesting issue. The requirement you mentioned can be done with the help of an Excel VBA code. The code contains two sub-procedures named ImportVCFRecordWise and ExtractData. All you have to do is to run the ImportVCFRecordWise procedure to achieve your goal.
Excel VBA Code:
Solution Workbook: Download the Workbook used to solve the issue.
DOWNLOAD WORKBOOK
VCF File: The VCF file contains some raw data. I am using the data described in this article to be more specific.
Steps:
Things to Keep in Mind:
This concept will assist you in reaching your goal. Good luck!
Regards
Lutfor Rahman Shimanto
Hello ASAD
Thanks for reaching out and posting your query. You mentioned using IBM Notes Version 10 as your email client. You must modify the code to work with IBM Notes instead of Outlook to achieve your goal. I am introducing you to an Excel VBA code that can send emails with the help of IBM Notes.
Excel VBA Code:
Things to Keep in Mind:
This idea helps you reach your goal. Don’t hesitate to contact us again if you have any more questions.
Regards
Lutfor Rahman Shimanto
Hello DeAnna
Thanks for reaching out. To overcome the situation, I will introduce another method. Here you must convert the locations into Geography data types. Later, you will find the Lat Long values for each location. Lastly, you have to call the below User-defined function in a cell.
Excel VBA Code:
API:
STEPS:
Select range B3:B4 >> go to Data tab >> click on Geography.
Choose cell C3 >> apply the below formula >> drag the Fill Handle to C4.
Press Alt+F11 >> go to Insert >> click on Module >> insert the mentioned code.
Choose cell C9 >> apply the below formula.
This concept will assist you in reaching your goal. I’ve also attached the Solution Workbook to help you understand it better. Best wishes.
Download Workbook
Regards
Lutfor Rahman Shimanto
Hi D KELLY,
Thanks for reaching out! I’d be happy to help you with your code and provide a solution. To solve this issue, we built a procedure called GetLatLong and a user-defined function named GetTotalDistance3Locations using VBA. You can input your desired locations in the GetLatLong procedure. The model calculates the total distance for three locations. Please click the link underneath this section to get a copy of the illustration workbook.
Download Workbook
Best regards,
Lutfor Rahman Shimanto
(ExcelDemy Team)
Hello PCLOUD
Greetings from our website! Thank you for sharing your question on the platform. There is a way to resolve your submitted query of finding the maximum value of the Close price in a defined period instead of the whole table. I can assist you with an Excel PowerQuery code to reach your goal.
PowerQuery Code:
The line MaxClosePrice = List.Max(#”Changed Type”[Close]) calculates the maximum value of the Close column in the transformed table. The result is assigned to the variable MaxClosePrice.
I hope this will achieve your goal. I am also giving you the Workbook used to investigate your issue to help you understand better. Feel free to contact us again with any other inquiries or concerns.
WORKBOOK:
DOWNLOAD WORKBOOK
Regards
Lutfor Rahman Shimanto
ExcelDemy Team
Hello SAMIR
Thanks for reaching out and posting your query. Regarding your question, I can assist you with a User-defined function programmed in Excel VBA that will take three arguments. Among these arguments, the first and second will be the Latitude and Longitude of the start and end location. And the third argument must be an API Key. I am giving you an API for demonstration which should work. However, you may use your API as well as the third argument. I am attaching the Workbook used to investigate the described issue.
Excel VBA Code:
OUTPUT:
WORKBOOK:
SOLUTION WORKBOOK
The User-defined function and API will meet your requirement. Don’t hesitate to contact us again with any other inquiries or concerns.
Regards
Lutfor Rahman Shimanto
Hello DREW7STER,
Greetings from our website! Thank you for posting your question on the platform. There is a way to solve your submitted problem of finding the unique values and their frequency. I am providing an Excel VBA code compatible with several unique values, as many as you want to be more specific.
Excel VBA Code:
I hope this will achieve your goal. Feel free to contact us again with any other inquiries or concerns.
Regards
Lutfor Rahman Shimanto
Hello CAR,
It is great to see you again. I hope this reply finds you well. As you requested, I have reviewed your code and found some Syntax errors. But overall, the algorithm was close to achieving your goal.
However, I am introducing a more efficient way of doing the same task with a better algorithm that should be compatible with large datasets.
EXCEL VBA CODE:
I hope this will achieve your goal. I am also giving you the Solution workbook to help you understand better.
DOWNLOAD WORKBOOK
Regards
Lutfor Rahman Shimanto
Thank you once again for your trust in us, CAR. Please do not hesitate to reach out if you need further assistance.
Regards
Team ExcelDemy
Hello DOUGLAS FORMAN,
Thanks for reaching out and sharing your exciting query. When a given condition is met, we can use data bars and conditional formatting to change the color of a cell at a time. I can help you with Excel VBA code that loops through the K column and applies your given formula. Latterly, it utilizes an If Conditional to format each cell. When iterating, if the cell contains 1, that means 100% “Yes” It formats the fill color of the cell as Green. On the other hand, it uses a Data Bars feature. To get a better understanding of the issue, I am going to share the Workbook used in exploring your problem.
Excel VBA Code:
INPUT:
OUTPUT:
WORKBOOK: Data Bars And Conditional Formatting
Feel free to contact us again with any other inquiries or concerns.
Regards
Lutfor Rahman Shimanto
ExcelDemy
Dear ABDUL KADAR
Greetings from our website! Thank you for posting your question on the platform. As requested, I can assist you with an Excel formula using the IF and COUNTIFS functions that determine how many products a Region makes even if regardless of the product name (sometimes the product column is blank against a Region).
FORMULA:
Regards
Lutfor Rahman Shimanto
Hello CAR
Thank you for reaching out to us on our website. You can use a single module to move rows between sheets based on various values. You may do this by using an IF statement inside a FOR loop that iterates through the rows in Sheet1, verifies the value, and then moves the entire row to the proper destination sheet based on the value. The intended code is given below.
Regards
Lutfor Rahman Shimanto
Hello Ruby,
Greetings from our website. You have encountered a warning message from the Microsoft Excel window. The error message “You’ve entered too few arguments for this function” means not providing enough arguments or inputs for a particular formula or function to work correctly. To resolve this error, check the formula or function you’re using and ensure you’ve provided all the required arguments.
This article explains how we can highlight cells with more than three duplicates. But, if you need help understanding this post, you can follow another article on the ExcelDemy website link given below.
How to Do Conditional Formatting in Excel [Ultimate Guide]
Later go through this article to better understand and highlight if there are more than three duplicates. Good luck.
Regards
Lutfor Rahman Shimanto
Hello PRACHI DABHADE
Greetings from our website! Thank you for posting your question on the platform. I can assist you with an Excel VBA code that creates new workbooks and allows you to specify names for those files. Furthermore, you can choose a location to store those files. Note that the user can cancel the folder selection dialogue. If they do, the code will end without making any new workbooks.
Download Workbook:
I am attaching the Workbook used to explore your issue. You can have it by clicking the link below.
https://www.exceldemy.com/wp-content/uploads/2023/03/PRACHI-DABHADE.xlsm
INPUT:
In the sheet named Sheet1, there is a list of names.
OUTPUT:
the desired workbooks are created utilizing the Excel VBA code mentioned below.
VBA CODE:
NOTE:
1) I am using the Application.FileDialog method, this code asks the user to choose a folder where the new workbooks will be saved. Then, it puts the path to the selected folder in the folderPath variable.
2) Using the folderPath variable and the Workbook’s name from the wbNames array, the code saves each new Workbook with its name in the chosen folder.
3) Lastly, remember to modify the VBA code when you want to work with the other ranges. Don’t hesitate to contact us if you face any other issues.
Regards
Lutfor Rahman Shimanto (ExcelDemy Team)
Hello Patrick,
Thank you for reaching out with your issue. You encountered a Run-time error. The “Run-time error ‘9’: Subscript out of range” error occurs because you did not modify the VBA code and forgot to insert the intended sheet name. You are misled somehow by the first step of the third method. The post says to go to the Insert tab and choose Headers and Footer from the Text group, and then execute the code mentioned in the third method; that’s all.
I can provide three Excel VBA codes to avoid the error you experienced unintentionally. All you need to do is choose the intended sheet as the active sheet and run a sub-procedure from the below list.
First Method: Insert Page Number in Footer Using Excel VBA
Second Method: VBA to Insert Total Page Number in Footer
Third Method: Insert Page Number in Selected Cell
Regards
Lutfor Rahman Shimanto (ExcelDemy Team)
Hello DYLAN,
Thank you for reaching out to us on our website. I understand that you have a value assigned to each member and are trying to split them into equal groups while ensuring each group’s total weight is within 1. Based on your requirements, I can assist you with this by applying an Excel VBA code.
The above VBA code splits a list of items into groups based on their assigned value and calculates the total weighted value for each unique group. It loops through each row of the worksheet, sets each item to a group based on the total weighted value of the group, and creates a new group if the current group's total weighted value plus the contemporary item's assigned value is greater than 1. The GetUniqueValues function calculates the unique groups and their total weighted values by using the Unique and SUMIFS functions to generate an array of unique values in column C and calculate the total weighted value for each group.
1) Inserting Required Values:

2) Displaying Final Output:

3) Download Practice Workbook
https://www.exceldemy.com/wp-content/uploads/2023/03/Dylans-Request.xlsm
Thank you again for reaching out to us, and I look forward to hearing from you soon.
Best regards,
Lutfor Rahman Shimanto
Hello KEVIN
Greetings from our website! Thank you for posting your question on the platform. I have yet to experience the issue you described personally. However, I can offer some insights to help you troubleshoot the problem.
The circular reference error may occur because the formula references the cell containing the procedure, creating an infinite loop. To avoid this, you can modify the system to refer to a different cell that doesn’t have the formula.
Excel’s built-in Error Checking feature can help identify and resolve circular references. To access this feature, go to the Formulas tab in the Excel ribbon, click Error Checking, and then click Circular References. Excel will then highlight any circular references in your workbook and suggest resolving them.
Providing any prominent solution without glancing at the workbook is challenging. You can share your workbook with us via Exceldemy Forum to better understand your situation.
Regards
Lutfor Rahman Shimanto (ExcelDemy Team)
Hello DAVID,
Thank you for reaching out with your comment. You encountered a different problem than what was described in the article. There could be several reasons why the problem is happening. Some of your add-ins may mess up the delete sheet feature, which may not work in your version of Microsoft Excel. To fix this, you should update your program.
You can use an Excel VBA code to delete a sheet by inputting the sheet name.
Excel VBA Code:
The VBA code and suggestions will solve your issue. Good luck.
Regards,
Lutfor Rahman Shimanto
Hello AYESHA
Thanks for your query. I have analyzed the workbook Rafiul Haq shared. A prominent approach to avoid data overlapping is to create the employee record separately for each month. So don’t hesitate to make the Employee Leave description for each month.
Regards
Lutfor Rahman Shimanto
Hello ERICH NAGY
I appreciate your time in reading the article. I’m delighted you found the information helpful and learnt something new. Your attention to detail is really appreciated, and you are correct. The appropriate range should be the C5:C20 range instead of D5:D20. An immediate correction will be made. Once again, We appreciate your comments and support. Continue reading our blog, and we aim to offer you further helpful information soon.
Regards
Lutfor Rahman Shimanto (ExcelDemy Team)
Hello ERIC SCHOENTHALER,
Thank you for your nice words! Great that you found our website and information helpful and relevant to your goal. I’m glad we could provide you with the information you were looking for. Regarding your question, you can use the following VBA code to insert a specific amount of page breaks starting at a particular line.
Regards
Lutfor Rahman Shimanto
Thank you so much for sharing this tip, Andy! I sincerely appreciate the time you took to share your expertise and assist others with this problem. Your suggestion proved effective, and We are genuinely grateful for it. Thank you again!
Regards
Lutfor Rahman Shimanto