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.
We'll use a sample dataset of dress stores that represents the order, size, and color information of a particular dress. The dataset contains 4 columns: Order ...
We'll use a sample dataset of sales information. The dataset contains two columns: Sales Person and Sales. Let's extract the unique values. How to ...
Dataset Overview To explain this process clearly, let’s use a sample dataset from an online fruit store. The dataset contains 5 columns representing fruit ...
For illustration, we will use the sample dataset below. Method 1 - Calculate Time Difference in Excel Using Operator ⏩ In cell E4, type the ...
In Excel, various people use different date formats so it is required to have any function to convert any formats into a standard date format. Whenever you ...
In Excel, we apply basic to complex formulas. Depending on the scenario use of the formula changes. In case you have any formula dependent on multiple ...
To explain the INDIRECT function, we'll use a dataset that represents sales information for 4 quarters. The dataset has 5 columns: Salesperson, Quarter-1, ...
Method 1 - Using the Excel SWITCH Function to Switch Corresponding Cell Values Steps: Enter the following formula in cell F4: ...
Basics of FREQUENCY Function: Summary & Syntax Summary The Excel FREQUENCY function returns how often numeric values occurred within the ranges you ...
Dataset Overview In large datasets, duplicate values or repeated occurrences of the same values can be a common issue. To address this, you can utilize the ...
The Excel EXP function is also known as exponential function. EXP Function in Excel: Syntax Summary The EXP function calculates and returns the value ...
In the Excel sheet, we store the progress of various work and projects. To track those projects or get any estimated time you can use the DAYS function. The ...
Although VLOOKUP has some limitations, the reason why VLOOKUP is not working is usually human error. To demonstrate the possible issues and their solutions, ...
Dataset Overview To make the explanation clear, I'll use a dataset representing product information from a fruit shop, but some information is condensed. The ...
Here's a sample dataset that will be used to demonstrate how to use the COUNTIF function. Download to Practice COUNTIF Excel Example.xlsx ...
ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.
Contact | Privacy Policy | TOS
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 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.
![](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20458%20529'%3E%3C/svg%3E)
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.
![](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20810%20405'%3E%3C/svg%3E)
USD to EURO:
USD to ARS:
![](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20811%20396'%3E%3C/svg%3E)
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:
![](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20681%20371'%3E%3C/svg%3E)
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.
![](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%201362%20619'%3E%3C/svg%3E)
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:
![](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%201219%20413'%3E%3C/svg%3E)
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:
![](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20353%20693'%3E%3C/svg%3E)
Before using the code make sure both of your sheets contains the same headings.
Sheet1 & Sheet2:
Output:
![](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20355%20489'%3E%3C/svg%3E)
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:
![Get-Data-from-Another-Worksheet](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20361%20188'%3E%3C/svg%3E)
=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.
![](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20491%20496'%3E%3C/svg%3E)
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.
![](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%201121%20333'%3E%3C/svg%3E)
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”
![DeepL Translator](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20870%20769'%3E%3C/svg%3E)
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.
![Answer sheet](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20880%20255'%3E%3C/svg%3E)
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:
![Mesaurements](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20474%2062'%3E%3C/svg%3E)
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
Hello Mohamed Sufi,
You are most welcome.
Regards
ExcelDemy
Hello Jude Ranby,
Here, the first methods Linking Existing Table on Another Sheet in Excel is duplicating the source table.
Here, I’m attaching a video for understanding.
Regards
ExcelDemy
Hello Drishti,
Here are some article you will find more exercises.
Excel Practice Exercises PDF with Answers
Sample Excel File with Employee Data for Practice
Advanced Excel Exercises with Solutions PDF
Regards
ExcelDemy
Hello Smn,
Formula of Exercise -5: =AND(G102=”Astro”,OR(C102=”Laptop”,C102=”Mobile Phone”)) is used to check multiple conditions using the AND and OR logical functions.
Here,
G102=”Astro”: It checks the value of G102 is equal to “Astro” or not.
OR(C102=”Laptop”,C102=”Mobile Phone”): It checks the value of cell C102 is equal to either “Laptop” or “Mobile Phone”. Here, the OR function will return TRUE if any of the conditions are TRUE.
AND(G102=”Astro”, OR(C102=”Laptop”, C102=”Mobile Phone”)): Here, the AND function combines the two conditions. It will return TRUE only if both/two conditions are TRUE.
Regards
ExcelDemy
Hello M-curious,
To do a conditional formatting follow the steps of method 1. Instead of using custom option from format cells, you can use the Alignment options.
Go to Format cells dialog box then from Alignment select any select any Alignments of your choice.
Regards
ExcelDemy
Hello Dear,
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
Dear Anthonia Aniekule,
You are most welcome.
Regards
ExcelDemy
Hello Bob Leslie,
You will get Break Even Analysis Template template in this article.
Regards
ExcelDemy
Hello Oscar,
Glad to hear that our blogs are helping professionals.
Regards
ExcelDemy
Hello IDD,
If you want real-time synchronization between the Excel file on your computer and Google Sheets, you can use the Google Drive desktop app.
1. Download and install the Google Drive app on your computer.
2. Sign in with the same Google account used to upload the Excel file.
3. Copy or move the Excel file to the Google Drive folder on your computer.
Open the Excel file from the Desktop App of Drive. Then, any changes made to the file on your computer will be automatically synced with Google Drive, and subsequently, with Google Sheets.
Finally, open the google sheets from the Drive. You will get the updated Excel file.
Regards
ExcelDemy
Dear Tony
You are most welcome.
Regards
ExcelDemy
Dear Yasin,
You are most welcome.
Regards
ExcelDemy
Dear OppaPanda,
You are most welcome.
Regards
ExcelDemy
Hello Thomas,
Thanks for your appreciation. We are working on it.
Regards
ExcelDemy
Dear AKhtar Khilji,
I hope this message finds you well too. Thank you very much for your kind words and your interest in using the training material from my blog for your nonprofit Excel training program. I’m thrilled to hear that you found the content helpful and that it will be used to empower the youth of marginalized communities in Pakistan.
I’m more than happy to grant you permission to utilize the exercises from the blog for your educational initiative. Please feel free to mention “ExcelDemy” along with the web address to provide proper attribution. It’s wonderful to know that the material will be shared with the trainees to enhance their learning experience.
If you have any specific requests or need further assistance with anything related to the training material, please don’t hesitate to reach out. I’m here to support your efforts in any way I can.
Wishing you great success with your Excel training program and the empowerment of the youth in marginalized communities.
Best regards,
ExcelDemy
Hello Rahul,
You are most welcome.
Regards
ExcelDemy
Hello AJ,
You are most welcome.
Regards
ExcelDemy
Hello Ankur Vaish,
Thanks for your suggestions.
Hello Sam Arthur,
You are most welcome.
Regards
ExcelDemy
Dear John Summers,
You are most welcome. Your appreciation means a lot to us to write such technical and informative article in future.
Regards
ExcelDemy
Hello Tiffany A Snyder,
You are most welcome.
Regards
ExcelDemy
Hello Earlington Etienne,
You are most welcome.
Regards
ExcelDemy
Hello Loni,
You are most welcome.
Regards
ExcelDemy
Hello,
You are most welcome.
Regards
ExcelDemy
Hello Jane Wanjiku,
You are most welcome.
Regards
ExcelDemy
Hello Sandra Merlo,
You are most welcome.
Regards
ExcelDemy
Hello Gallienus,
You are most welcome.
Regards
ExcelDemy
Hello John,
You are most welcome.
Regards
ExcelDemy
Dear Margaret,
You are welcome.
Regards
ExcelDemy
Hello Akram Siddique,
Here, in the formula : =WEEKDAY(B5,11)
11 means – Numbers 1 (Monday) through 7 (Sunday). Here the weeks starts from Monday. So it will count the days of week from Monday and the integer value will be 1, Tuesday (2) and so on.
Regards
ExcelDemy
Hello Noelle,
To highlight the entire row, insert the following formula: =$C5 < TODAY()
In applies to select the rows
![Applies to](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20855%20663'%3E%3C/svg%3E)
Regards
ExcelDemy
Dear Daniela Vognar,
You are most welcome.
Regards
ExcelDemy
Dear Radu Pescaru,
Sunteți foarte bineveniți.
Regards
ExcelDemy
Hello Patricia Pearson,
You are most welcome.
Regards
ExcelDemy
Hello Ehia,
You are most welcome.
Regards
ExcelDemy
Hello
You are most welcome.
Regards
ExcelDemy
Hello Chris,
You are most welcome.
Regards
ExcelDemy
Hello Sirajudeen,
You are most welcome.
Regards
ExcelDemy
Hello Patrick Opiola,
Thanks for your appreciation.
Regards
ExcelDemy
Hello Mardell Daubney,
Thanks for your appreciation.
Regards
ExcelDemy
Hello Dr.M. Veerendra Kumar,
You are most welcome.
Regards
ExcelDemy
Hello Hamed,
You are most welcome.
Regards
ExcelDemy
Hello AJ,
You are most welcome.
Regards
ExcelDemy
Hello Joedel,
You are most welcome.
Regards
ExcelDemy
Hello Abdul Kader Osman,
You are most welcome.
Regards
ExcelDemy
Dear Saiful Amin,
You are most welcome.
Regards
ExcelDemy
Hello Abdrman,
You are most welcome.
Regards
ExcelDemy
Hello Nick,
You are most welcome.
Regards
ExcelDemy
Hello Gianluigi,
You are most welcome.
Regards
ExcelDemy
Hello JC,
You are most welcome.
Regards
ExcelDemy
Hello JC,
You are most welcome.
Regards
ExcelDemy
Hello Andrii,
You are welcome.
Regards
ExcelDemy
Hello Syed Minhaj,
You are most welcome.
Regards
ExcelDemy
Hello Peter,
So kind of you.
Regards
ExcelDemy
Hello Nurit,
You are most welcome.
Regards
ExcelDemy
Hello Ava,
Thanks
Hello Tamiko,
You are most welcome.
Regards
ExcelDemy
Hello Huongpt,
You are welcome.
Regards
ExcelDemy
Hello Brenda Gale Austin-Sanders,
You are most welcome.
Regards
ExcelDemy
Dear Mario,
You are most welcome.
Regards
ExcelDemy
Hello Amanz,
We shared our ideas with you in your ExcelDemy Forum post.
Hello Adam Ingleton,
You are most welcome.
Regards
ExcelDemy
Hello Ani,
You are most welcome.
Regards
ExcelDemy
Hello Vijiaselvam Nayagam,
You are most welcome.
Regards
ExcelDemy
Hello Mohammad,
You are welcome.
Regards
ExcelDemy
Hello Suzie,
Glad to hear that our solution helped you.
Regards
ExcelDemy
Hello Romeo Nyi,
You are most welcome.
Regards
ExcelDemy
Dear Minzitia Lawrence,
You are welcome.
Regards
ExcelDemy
Hello Juan,
You are most welcome.
Regards
ExcelDemy
Dear Kagiso,
Thanks for your appreciation.
Regards
ExcelDemy
Hello Azreim,
You are most welcome.
Regards
ExcelDemy
Thanks for your appreciation.
Regards
ExcelDemy
Hello Lee G,
You are most welcome.
Regards
ExcelDemy
Hello Tanveer,
Thanks for your appreciation.
Regards
ExcelDemy
Hello Mian Muhammad Saleem,
You are most welcome.
Regards
ExcelDemy
Hello JD,
You are most welcome.
Regards
ExcelDemy
Hello Eduardo,
You are welcome.
Regards
ExcelDemy
Dear Sam,
Thanks for your appreciation.
Regards
ExcelDemy
Hello Mongwongprue Marma,
You are welcome.
Regards
ExcelDemy
Hello Calin,
You are most welcome.
Regards
ExcelDemy
Hello Adam,
Thanks for your appreciation. You are most welcome.
Regards
ExcelDemy
Hello Ali,
Thanks for your appreciation. You are most welcome.
Regards
ExcelDemy
Hello Dawood,
Thanks for your appreciation.
Regards
ExcelDemy
Hello Eduardo,
Kindly share your Excel File and images in ExcelDemy Forum.
Regards
ExcelDemy
Hello Daniel Woods,
To make problem sharing easier we launched ExcelDemy Forum, so that you can upload Excel Files and images of your problem. You can share your workbook in ExcelDemy Forum our expert team will reach out to you.
Regards
ExcelDemy
Hello Daniel Woods,
To make problem sharing easier we launched ExcelDemy Forum, so that you can upload Excel Files and images of your problem. You can share your workbook in ExcelDemy Forum our expert team will reach out to you.
Regards
ExcelDemy
Hello Abishek,
You are most welcome.
Regards
ExcelDemy
Hello David,
Thanks for your valuable suggestions.
Regards
ExcelDemy
Hello Mike Longoria,
You are most welcome.
Regards
ExcelDemy
Hello Z Thomas,
You are most welcome.
Regards
ExcelDemy
Dear Andrija,
You are most welcome.
Regards
ExcelDemy
Hello Mohammed,
Kindly share the issues you are facing while using the Excel file in the comment box. In case you need to share images, Excel file, you also can post your problem in ExcelDemy Forum.
Regards
ExcelDemy
Hello Syifaa’ Mustafa,
To get the Developer tab follow the steps mentioned in the article “Display the Developer tab in Excel“. Then you can use it to create Tooltip.
Regards
ExcelDemy
Hello Selam,
You are most welcome.
Regards
ExcelDemy
Hello DanJ,
You are most welcome.
Regards
ExcelDemy
Hello Matt S,
You are most welcome.
Regards
ExcelDemy
Hello Odedele Olufemi,
You are welcome.
Regards
ExcelDemy
Hello Joel,
You are most welcome.
Regards
ExcelDemy
Hello Chanyalew,
Thanks for your appreciation.
Regards
ExcelDemy
Hello Lynn,
You are most welcome.
Regards
ExcelDemy
Hello Nasir Ahmed,
Thanks for your appreciation.
Regards
ExcelDemy
Dear Anon,
You are most welcome.
Regards
ExcelDemy
Hello Vickie Addo,
We have given the answers of 100 questions in our Excel File, which is given in the Download Practice Workbook Section. As these questions are given to test your skills it won’t be helpful to check the answer immediately. A gif is given how you can test your skills at the end. Please download the Excel File and test you skills then see the answers.
Regards
ExcelDemy
Hello Venkatesh,
You are most welcome.
Regards
ExcelDemy
Dear Alexandar P,
You are most welcome.
Regards
ExcelDemy
Hello Naziom,
You are most welcome.
Regards
ExcelDemy
Hello Viraltecho,
You are most welcome.
Regards
ExcelDemy
Hello Joel,
You are most welcome.
Regards
ExcelDemy
Hello,
You are most welcome.
Regards
ExcelDemy
Hello Suhaili,
You don’t need to worry about payment. If you want any read-to-use template kindly send your requirements using [email protected] email.
Regards
ExcelDemy
Dear Dave,
You are most welcome.
Regards
ExcelDemy
Dear Komal Thakur,
You are most welcome.
Regards
ExcelDemy
Hello Samia,
In the Download Practice Workbook section, you will get the dataset used in the article.
Regards
ExcelDemy
Hello Nafez,
You are most welcome and thanks for you invitation to visit Qatar.
Regards
ExcelDemy
Hello May,
The “Developer Tab” option is missing in default Excel versions. You can enable it from Excel Options. By following this article Display the Developer Tab you can enable it easily.
Regards
ExcelDemy
Dear Laurie,
You are most welcome.
Regards
ExcelDemy
Dear Sovereign,
Thanks for your appreciation.
Regards
ExcelDemy
Hello Sagar,
You will get the solutions in the Excel file, which is given in the Download Practice workbook Section.
Regards
ExcelDemy
Dear Anusha Uppuganti,
You are most welcome. We are so glad that our article explanation helped you.
Regards
ExcelDemy
Dear PK,
Thanks for your appreciation. You are most welcome.
Regards
ExcelDemy
Hello Tsegay Gebreselasie,
Our aim is giving you the best Excel-related content like this.
Regards
ExcelDemy
Dear Mark,
Thanks for your appreciation.
Regards
ExcelDemy
Hello Rolan,
Thanks for your suggestion. Article is updated.
Hello Damien,
From our Download Practice Workbook section, you will get the Excel file.
If you face any difficulties after downloading the above file, follow this article to Enable Macros but Not Working
Regards
ExcelDemy
Hello Taiwo,
All the solution are available in the Excel Practice file section.
We used this formula to get discount amount: =F5*IF(D5<1,0.03,0.05)
Regards
ExcelDemy
Hi viralTecho
Thanks for your appreciation.
Regards
ExcelDemy
Hello James Ward,
To run the code from Excel you can follow these steps:
Developer tab >> Macros >> Select any Sub Procedure from the list
Regards
ExcelDemy
Hello Hugh Richards,
Thanks for your appreciation.
Regards
ExcelDemy
Dear Noe Albarran,
You are most welcome.
Regards
ExcelDEmy
Dear Yuan,
Thanks for the suggestions. The code is updated you can check it now.
Regards
ExcelDemy
Dear Amir
You have to use either the combined formula or the vba code. Because converting number to words maintaing the place vaule is not possible by simple formula.
Regards
ExcelDemy
Dear Ian Fisher,
You are most welcome.
Regards
ExcelDemy
Hello Viraltecho,
You are most welcome.
Regards
ExcelDemy
Hello Viraltecho,
You are most welcome.
Regards
ExcelDemy
Dear Roberto,
You are most welcome.
Regards
ExcelDemy
Dear Aubrey
You are most welcome. Your appreciation means a lot to us.
Regards
ExcelDemy
Dear Dilip Paunikar
You are most welcome.
Regards
ExcelDemy
Dear Soumendra
You are most welcome.
Regards
ExcelDemy
Hello Manwesh,
You can state your problem in the comment section or in our ExcelDemy Forum.
Regards
ExcelDemy
Dear Ashab,
We do have a YouTube channel named ExcelDemy
Regards
ExcelDemy
Dear Evangeline,
Thanks for your appreciation. Our experienced writers write these questions after doing a complete research about the topic.
Regards
ExcelDemy
Dear Jason,
You are most welcome.
Regards
ExcelDemy
Dear Ryan,
Glad to hear that our article helped you.
Regards
ExcelDemy
Dear Endashaw Balcha,
To know more about excel formula you can explore our Excel Formulas category. From here you will get practical use of different formulas in different fields.
Regards
ExcelDemy
Dear Raf,
You are most welcome.
Regards
ExcelDemy
Hello Muhammad Amin,
Good to hear that. To get more helpful contents explore our site.
Regards
ExcelDemy
Dear Jess,
Thanks for your appreciation.
Regards
ExcelDemy
Dear Jimmy,
You are most welcome.
Regards
ExcelDemy
Hello y2mate,
Thanks for your appreciation.
Regards
ExcelDemy
Dear ytMp3,
Thank you so much.
Regards
ExcelDemy
Dear Martin Carrion,
You are most welcome.
Regards
ExcelDemy
Dear Dina,
Thank you so much for your appreciation.
Regards
ExcelDemy
Dear Navini,
You are most welcome.
Regards
ExcelDemy
Dear Gopi Sahu,
Thanks for reaching out to us. You will get various types of WFM related articles here Excel Solver
If you need solution of any specific topic kindly comment down below.
Regards
ExcelDemy
Dear Justice,
You are most welcome.
Regards
ExcelDemy
Dear Merry,
You are most welcome.
Regards
ExcelDemy
Dear Karen,
You are most welcome.
Regards
ExcelDemy
Hi Putul ch. Borah,
Yes, you can do it by following the mentioned steps.
Dear Tiago,
You are most welcome.
Regards
ExcelDemy
Dera Ashok,
You are most welcome.
Regards
ExcelDemy
Dear Foysal,
You are most welcome.
Regards
ExcelDemy
Dear kinyapreen,
To get virtual lessons you can follow our youtube channel Youtube Channel
Regards
ExcelDemy
Hello BART,
It is possible to add a VB line of code to run the selection change code only when you click the tab of the worksheet called “index.” To achieve this, you can use the Worksheet_Activate event in the code module of the “index” worksheet. Here’s the code.
Regards
Alif Bin Hussain
Dear,
You are most welcome.
Regards
ExcelDemy
Dear Bart,
You are most welcome. Sure, you can inform us.
Regards
ExcelDemy
Dear Najeeburk,
You are most welcome.
Regards
ExcelDemy
Dear Danny Van Straten,
If you need any types of customized templates you may contact us through [email protected]
We have a expert team to create any types of professional templates.
Regards
ExcelDemy
Hello Yaojm,
Thanks for your suggestions. Here we focused on the comparison/difference of VLOOKUP and XLOOKUPp that’s why we didn’t improvised the use of VLOOKUP.
Regards
ExcelDemy
Hello Pankaj,
You are welcome.
Regards
ExcelDemy
Dear Charlotte,
You are most welcome.
Regards
ExcelDemy
Dear Mustafa,
You are most welcome.
Regards
ExcelDemy
Hello Abubakar,
You are most welcome. We hope so you will be able to create an interim certificate template following these steps.
Regards
ExcelDemy
Dear B,
You are welcome.
Regards
ExcelDemy
Dear Akash,
You are most welcome.
Regards
ExcelDemy
Dear Carl,
You are most welcome.
Regards
ExcelDemy
Hello Dear,
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
Dear Macel Williams,
To customize any templates you can contact with us via this mail problem @exceldemy.com
Regards
ExcelDemy
Dear Macel Williams,
To customize any templates you can contact with us via this mail problem @exceldemy.com
Regards
ExcelDemy
Dear Qasim,
Thanks for your suggestions we have updated our Exercise 3 you can check it now.
Regards
ExcelDemy
Dear Kenny H,
This Email address is valid but if you face any problem you can post your problem in our ExcelDemy Forum.
Regards
ExcelDemy
Dear Nikhil Pandey,
You will get all the solutions in the workbook’s Solution sheet. Here I’m giving you the query answer for your understanding.
The Formula is : =WEEKDAY(B5,11)
Regards
ExcelDemy
Dear Nikhil Pandey,
You will get the solution sheet in the Download Practice Files section. Download the workbook there in Solution sheet you will get all the solutions. You you can get the workbook from this link Solution Sheet
Regards
ExcelDemy
Dear Aiden,
Yes you can do that.
Regards
ExcelDemy
Dear Macel,
You are welcome.
Regards
ExcelDemy
Dear Marc,
You are welcome.
Regards
ExcelDemy
Dear Kim,
Thanks for your appreciation.
Regards
ExcelDemy
Dear Prakash Tulsiani,
You are most welcome.
Regards
ExcelDemy
Dear Kay,
To get a customized templates you can contact with us through this Email: [email protected]
Regards
ExcelDemy
Hello Dear,
You can download the Excel and PDF files free of cost just by providing your valid email address. To get the files go to the “Download Excel Formulas Cheat Sheet PDF & Excel Files” section of this post and enter your email address. Then check your email immediately after to get the download links.
Best Regards
ExcelDemy
Dear Nadew w/yesus,
You are most welcome.
Regards
ExcelDemy
Dear Dr,
You are most welcome.
Regards
ExcelDemy
Dear Adil Ahmed,
You are most welcome.
Regards
ExcelDemy
Dear Macel Williams,
You are most welcome.
Regards
ExcelDemy
Hello Dear,
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
Dear Isaac,
You are most welcome.
Regards
ExcelDemy
Dear MADI,
Thank you for following our article.
To split into sheets based on certain row conditions, we have to enter an IF condition that checks if the row is of a certain colour. Here, I have added the code to do this.
This code checks if the interior colour of the first cell in each row of the selected range is Green(RGB(0,255,0)). The entire row is copied and pasted into a new sheet if rows meet the condition. You can alter this colour condition according to your need.
Regards
Priti
Dear Sara,
You will find the solutions in the Excel file which is given in Download Practice Workbook section.
Regards
ExcelDemy
Dear Sakshi,
You are most welcome.
Regards
ExcelDemy
Dear Md. Morshed Alam,
You are most welcome.
Regards
ExcelDemy
Dear Alex,
Thanks for your feedback.
Regards
Exceldemy
Dear Paresh Kanti Paul,
We are glad to hear that.
Regards
ExcelDemy
Hello Dear,
You can download the Excel and PDF files free of cost just by providing your valid email address. To get the files go to the “Download Excel Formulas Cheat Sheet PDF & Excel Files” section of this post and enter your email address. Then check your email immediately after to get the download links.
Best Regards
ExcelDemy
Dear Sushil,
You are most welcome.
Regards
ExcelDemy
Dear Duncan R Walk,
You are most welcome.
Regards
ExcelDemy
Dear Bolajoko Olufemi,
You will get all resources related to Data Analysis here All About Data Analysis. We are planning to launch courses as soon as possible.
Regards
ExcelDemy
Dear Ronald,
You are most welcome.
Regards
ExcelDemy
Dear Ronhat Minmin,
If you want to get the Excel File, you can download it from Download Practice Workbook
Regards
ExcelDemy
Dear Ebrahim Ahmadi,
You are welcome.
Regards
ExcelDemy
Hello Dear,
You can download the Excel and PDF files free of cost just by providing your valid email address. To get the files go to the “Download Excel Formulas Cheat Sheet PDF & Excel Files” section of this post and enter your email address. Then check your email immediately after to get the download links.
Best Regards
ExcelDemy
Dear Lin Giralt,
You are most welcome.
Regards
ExcelDemy
Dear Khin Soe,
You are most welcome.
Regards
ExcelDemy
Dear Tha’ir,
You are most welcome.
Regards
ExcelDemy
Dear Mohamad,
Thanks for your appreciation
Regards
ExcelDemy
Dear Minhajul,
You are most welcome.
Regards
ExcelDemy
Dear Sandeep Bhattacharjee,
You are most welcome and thanks for your appreciation.
Regards
ExcelDemy
Hello Sujay Jain,
You can download the Excel and PDF files free of cost just by providing your valid email address. To get the files go to the 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.
Best Regards
ExcelDemy
Dear S Roy,
You are most welcome.
Regards
ExcelDemy
Hi Kazem,
You are welcome.
Regards
ExcelDemy
Dear Lana,
You are most welcome.
Regards
ExcelDemy
Dear Seamus Madden,
Thanks for your appreciation.
Regards
ExcelDemy
Hi Ndonwi,
Thanks
Dera Merc,
You are most welcome.
Regards
ExcelDemy
Dear Joanna,
You are most welcome.
Regards
ExcelDemy
Dear Richard,
You are most welcome.
Regards
ExcelDemy
Dear Andrei,
These functions are available in WPS office.
Regards
ExcelDemy
Dear Kapil,
You are most welcome.
Regards
ExcelDemy
Dear Tommy Fuifui,
You are most welcome.
Regards
ExcelDemy
Dear Excel Nob,
You are most welcome.
Regards
ExcelDemy
Dear Car,
You are most welcome.
Regards
ExcelDemy
Dear Jamie,
You are most welcome.
Regards
ExcelDemy
Dear Anitelleth,
You are most welcome.
Regards
ExcelDemy
Dear Anitelleth,
You are most welcome.
Regards
ExcelDemy
Dear Kay,
You are welcome.
Regards
ExcelDemy
Dear Pirooz Rahimi,
You are most welcome and thanks for your appreciation.
Regards
ExcelDemy
Dear Abel,
Thanks for your appreciation. You are most welcome.
Regards
ExcelDemy
Dear Kh Paydar,
You are most welcome.
Regards
ExcelDemy
Dear Joris,
You are most welcome. We are glad to hear that.
Regards
ExcelDemy
Dear Raja,
You are most welcome.
Regards
ExcelDemy
Dear Kapil,
Thanks for your appreciation.
Regards
ExcelDemy
Dear Polepeddi Sasidhar,
We are glad to hear that.
Regards
ExcelDemy
Hello Silpa,
You can download the Excel and PDF files free of cost just by providing your valid email address. To get the files go to the “Download Excel Formulas Cheat Sheet PDF & Excel Files” section of this post and enter your email address. Then check your email immediately after to get the download links.
Best Regards
ExcelDemy
Dear Jidith Savidge,
You are most welcome.
Regards
ExcelDemy
Dear Ajij Nadaf,
We will love to give you suggestions. kindly share your problem.
Regards
ExcelDemy
Good day, Daniel,
The issue you’re having might be because your workbook’s data range differs from the data range specified in our code.
Change the data range according to your worksheet.
This might be the solution to your issue. I’d appreciate it if you could send me the worksheet you’re working on so that I can better understand your issue and give you an exact solution.
Regards
Sakibul Hasan Nahid | ExcelDemy Team
Hey Philip,
Thanks for your response. You can use the SORT and FILTER functions along with the LARGE function to solve your problem.
Here’s the practice sheet we used. You can check it out for a better understanding.
SORT-FILTER.xlsx
You can also check out this article for more detailed explanations.
https://www.exceldemy.com/excel-top-10-list-with-duplicates/
Regards
Hassan Shuvo| ExcelDemy Team
Hello Chandan,
Thanks for commenting. If I’m not wrong, you want to match the dataset like the one below.
Select the entire dataset without the heading then go to conditional formatting >> New Rule
Then select “Use a formula to determine which cells to format.”
In the formula bar, enter the following formula:
=AND(A3<>B3,C3<>B3)
Press OK
Here, “A3” represents the cell containing the customer name, “B3” represents the cell containing the ID name, and “C3” represents the cell containing the real name. You can adjust the cell references based on your data.
Choose the formatting option that you want to use for the highlighted rows. For example, we took light green color here for highlighting.
The final outcome will look like the image below.
I hope this answer will help you to identify matched names. Please let us know if you have any other queries. Also, you can post your Excel-related problem in ExcelDemy Forum with images or Excel workbooks.
Regards
Mizbahul Abedin | ExcelDemy Team
Dear MD. KAMAL HOSSAIN,
To obtain your required format please follow the following simple steps:
From your Excel Workbook, press ALT + F11 to open Visual Basic Editor.
Insert a module.
Paste the following code:
Now, return to your worksheet. Type in the following formula in the cell where you require the output and press Enter key.
=NumberToWords(B5) & ” Taka Only”
Here, B5 indicates the reference of the cell where you have the input number.
I hope this solution will be sufficient for your requirements. Let us know your feedback.
Regards,
ExcelDemy
Dear Rich Saunders,
Query 1: The method 7 works just fine. If you write March in F4, you will get the values for that month as well.
Query 2: The formula is universal for any Excel version. If you write the formula in any cell the formula will get you the entire row value for the name you will write in the F4 cell. For example, if you write “Nathan” in F4 cell and paste the formula in the A1 cell then the formula will write the values of the entire row( B8:D8) in the A1:A3 range.
Yes, we should have mentioned the Ctrl+Shift+Enter in the article. We will make sure of that in the next articles. As we used Microsoft 365 so it works by pressing Enter. We are sorry for that.
Regards
ExcelDemy
Dear Tudor,
Hope you are doing well. Answer of your questions are given below with explanation.
1. How can I change the month and day from English to Romanian?
Select the cell with month and date, cell C7 in our case.
Right-click with the mouse and select Format Cells.
In the Format Cells window, select Romanian (Moldova) under the Locale menu.
Then, select any type under the Type menu and click the OK button.
The month and date are changed into Romanian from English. Do the same process for cell E7.
2. If an employee works on Saturday and/or Sunday, how can I collect those weekend hours separately, in a cell?
Let’s say the first employee works on Saturday and Sunday for 5 and 6 hours respectively. He works on 4 weekends which you can see in the picture. To sum these values we’ll use the SUMIFS function.
Apply the formula in any cell where you want the weekend hours, let’s say cell AM11-
=SUMIFS($F$11:$AJ$11,$F$11:$AJ$11,"<>P",$F$11:$AJ$11,"<>A")
After pressing Enter you’ll get the total weekend hours. You can use the Fill Handle tool to apply this formula to other employees.
Dear Aspen,
You are most welcome.
Regards
ExcelDemy
Thanks for your suggestions.
Dear Victor,
You are most welcome.
Regards
ExcelDemy
Dear Elizabeth,
You are most welcome. We are so glad that our article fulfilled your need.
Regards
ExcelDemy
Dear Anjli Gupta,
Answers are given in the Solution sheet of Excel File which is in Download Practice Workbook section.
![Answer sheet](data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%20880%20255'%3E%3C/svg%3E)
Regards
ExcelDemy
Dear HG,
You are most welcome.
Regards
ExcelDemy
Dear Seaunders,
Thanks for your appreciation. It means a lot to us.
Regards
ExcelDemy
Dear Gunawan,
Thanks for your suggestions. We updated the article. You can check it now.
Regards
ExcelDemy
Hi Fake Hidden,
Thanks for your suggestions. We updated our article so that it fulfills the need of all types of readers. You may check our article now and let us know any kinds of suggestions.
Regards
ExcelDemy
Dear Rafi,
Thanks for your appreciation.
Regards
ExcelDemy
Dear Elly,
Thanks for your appreciation. We re-explained our formulas to make them more understandable. If you find any difficulty now, let us know in the comment section below.
Regards
ExcelDemy
Dear Rashmi,
You are most welcome.
Regards
ExcelDemy
Dear Jay Dee,
That’s good to hear. We are interested to know more from you.
Regards
ExcelDemy
Dear Silver,
You are most welcome.
Regards
ExcelDemy
Dear William B,
You are most welcome.
Regards
ExcelDemy
Dear Eugen Safta,
You are most welcome.
Regards
Shamima Sultana | ExcelDemy
Dear Radu,
Thanks for your suggestion and we appreciate it. We updated the cell range.
Dear Sanket,
Thanks for your appreciation.
Regards
Shamima Sultana
Project Manager | ExcelDemy
Dear Yar Zar Htun,
You are most welcome. We are glad that through ExcelDemy you are improving your skill.
Regards
Shamima Sultana
Project Manager | ExcelDemy
Dear Vivasvan,
You are most welcome. It’s great to know that our article helped you to complete your project.
Regards
Shamima Sultana
Project Manager | ExcelDemy
Dear Bo Thibaut,
We are willing to do any Excel-related projects. Kindly share your requirements through this Email: [email protected]
Regards
Shamima Sultana
Project Manager | ExcelDemy
Hi Robert Bakinam,
Here, you will get the full copy in pdf Marking Result Sheet.
Regards
Shamima Sultana
Project Manager | ExcelDemy
Dear German,
You are most welcome. To get solutions with detailed explanations, follow ExcelDemy.
Regards
Shamima Sultana
Project Manager | ExcelDemy
Dear Kirsten Wheeler,
Thanks for your appreciation. To get solutions with detailed explanations, follow ExcelDemy.
Regards
Shamima Sultana
Project Manager | ExcelDemy
Dear NancyM,
Thanks for your appreciation. To get solutions with detailed explanations, follow ExcelDemy.
Regards
Shamima Sultana
Project Manager | ExcelDemy
Dear CJvW,
Thanks for your appreciation. To get more helpful content be with ExcelDemy.
Regards
Shamima Sultana
Project Manager | ExcelDemy
Dear Geogre,
Thanks for your kind attention and suggestion. We updated it.
Dear Michael Gamal Aziz,
You are most welcome.
Regards
Shamima Sultana
Project Manager | ExcelDemy
Dear Glenn,
We are glad to hear that. ExcelDemy is trying hard to give you quality content.
Regards
Shamima Sultana
Project Manager | ExcelDemy
Dear Uday Kumar,
We are working on your problem. We will appreciate you to post your problem in our ExcelDemy Forum.
Regards
Shamima Sultana
Project Manager | ExcelDemy
Dear Ramandeep Kaur,
Thanks for your appreciation. Sooner we will make another exercise based on Basic and Advanced Formulas.
Regards
Shamima Sultana
Project Manager | ExcelDemy
Dear Datla Srinivas,
We are working on your requirements. We hope we will come back to you with your expected solutions.
Regards
Shamima Sultana
Project Manager | ExcelDemy
Dear Mody,
Thanks for your appreciation.
Regards
Shamima Sultana | Project Manager | ExcelDemy
Hello Joanie,
You are most welcome.
Regards
Shamima Sultana | Project Manager | ExcelDemy
Dear Map,
Thanks for your appreciation and suggestion. Sooner we will write an article regarding this topic.
Regards
Shamima Sultana | Project Manager | ExcelDemy
Dear Ven Upekkha,
Thanks for your appreciation.
Regards
Shamima Sultana | Project Manager | ExcelDemy
Dear Rajesh,
You are welcome.
Regards
Shamima Sultana | Project Manager | ExcelDemy
Dear umit,
Thanks for your appreciation it means a lot to us.
Regards
Shamima Sultana | Project Manager | ExcelDemy
Hello Zuri,
Thanks for your suggestion.
Regards
Shamima Sultana | Project Manager | ExcelDemy
Dear Carl,
Thanks for your appreciation. ExcelDemy is glad to help you.
Regards
Shamima Sultana | Project Manager | ExcelDemy
Dear Sandra Aldridge,
Thanks for your appreciation it means a lot to us. ExcelDemy is glad to help you.
Regards
Shamima Sultana | Project Manager | ExcelDemy
Dear Micah,
Thanks for your kind words. ExcelDemy is dedicated to provide solutions to help you.
Regards
Shamima Sultana | Project Manager | ExcelDemy
Dear Mukibul Hasan,
Thanks for your appreciation. Stay in touch with ExcelDemy.
Regards
Shamima Sultana | Project Manager | ExcelDemy
Dear Shanmuga Raja V S,
Thanks for your appreciation. Stay in touch with ExcelDemy.
Regards
Shamima Sultana | Project Manager | ExcelDemy
Dear Adam,
Thanks for your appreciation.
Shamima | Project Manager | ExcelDemy
Dear Zidane,
Thanks for your appreciation.
Shamima | Project Manager | ExcelDemy
Dear Kayla,
Thanks for your appreciation and we are glad that our Dataset Template helped you.
Regards
Shamima | Project Manager | ExcelDemy
Hello Uday Kumar,
Kindly check the reply to your previous comment. We have given you a non-macro solution.
Dear Forte,
Thanks for your appreciation. Stay in touch with ExcelDemy to get more helpful content.
Regards
Shamima | Project Manager | ExcelDemy
Dear Gilson,
Thanks for your appreciation. Stay in touch with ExcelDemy to get more helpful content.
Regards
Shamima | Project Manager | ExcelDemy
Dear Gerry Conroy,
You are most welcome.
Regards
Shamima | Project Manager | ExcelDemy
Dear Syed Anwar Hussain,
Thanks for your appreciation.
Regards
Shamima | Project Manager | ExcelDemy
Hello, Xzavier!
For better understanding, we updated the article. Kindly check it now it would be helpful.
Regards
ExcelDemy
Hello, An!
You are most welcome. To get helpful content follow ExcelDemy.
Regards
ExcelDemy
Hello, James!
Your appreciation means a lot to us. To have a more wonderful experience follow ExcelDemy.
Regards
ExcelDemy
Hi Rassulsson!
Thanks for your appreciation.
Regards
ExcelDemy
Thanks, Michael, for your query. From your comment, it seems that you want to permanently assign the replacing dataset (denoted by Reprng in the code) instead of taking them in a prompted input box. You can do that by following the steps below.
1. First, set a Name to the range that contains the Find & Replace with columns (In this case E5:F6). To do that, select the range and type a suitable name on the Name Box. Suppose you give the name “Find_Replace_Array”
2. Now, open a new module and write the given code of method 1.
3. Then, on the code, replace the line :
Set Reprng = Application.InputBox(“Replace with: “, Title, Type:=8)
With the following line.
Set Reprng = Range(“Find_Replace_Array”)
Now, if you run the code, you will only need to select the range where you want to replace values and it will automatically replace the values for you. Here, you will not be required to manually input the address of Find_Replace_Array.
Hope it does the job for you. If you have any further queries, do let us know. Moreover, if your problem persists, you can post your problem in our Exceldemy Forum along with your Excel file.
Hello, Hamza!
Thanks for your appreciation. We are glad that our article helped you.
Regards
ExcelDemy
Hello, A!
If your file is protected by using Encrypt with Password feature then it won’t work. We mentioned it in the Things to Remember section.
Regards
ExcelDemy
Hello, Nabil sellami!
Thanks for your appreciation. To get more enjoyable articles stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello, JeteMc!
Welcome, JeteMc. To get more helpful information stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello, Helen!
Thanks for your appreciation. To get more helpful information stay in touch with ExcelDemy.
Regards
ExcelDemy
Hi, Irene!
Thanks for your appreciation. To get more helpful information stay in touch with ExcelDemy.
Regards
ExcelDemy
Hi, Elton!
Thanks for your appreciation. We will try to launch pdf files.
Regards
ExcelDemy
Hello, Muhammad Luqman!
You can download it from Download Excel Formulas Cheat Sheet PDF section.
Regards
ExcelDemy
Hi, Bosco!
Thanks for your appreciation. To get more informative contents stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello, Joe Smith!
The 64-bit version of Excel is not compatible with 32-bit active x controls. To know more about it you can visit this site
Regards
ExcelDemy
Hello, Aleksandra!
Thanks for your appreciation.
Regards
ExcelDemy
Hello, Bennie!
Thanks for your appreciation.
Regards
ExcelDemy
Hello, Harry!
You can download the copy from Download Sample Workbook
Regards
ExcelDemy
Hello, Emil Lazar!
Thanks for your appreciation. Stay in touch with ExcelDemy to get more useful articles.
Regards
ExcelDemy
Hi Abishek Sharma!
If you want the Excel file then you can download it from the Download Practice Workbook section. Or you need other assistance you can comment or send us mail at [email protected]
Hello, Nastaran!
Thanks for your appreciation. We are glad that our post helped you.
Regards
ExcelDemy
Hello, Ruben!
It’s glad to know that our content is helpful to you. To know more about Excel stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello, Tanael Wawe!
You are welcome. Stay in touch with ExcelDemy to get more helpful content.
Regards
ExcelDemy
Hello, Tanael Wawe!
Thanks for your appreciation. Stay in touch with ExcelDemy to get more helpful content.
Regards
ExcelDemy
Hello, Dan!
Thanks for your appreciation. It means a lot.
Regards
ExcelDemy
Hello, Ahnaf!
You are most welcome. stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello, Aleem!
Thanks for your appreciation. stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello, Mohan!
Thanks for your appreciation. Stay in touch with ExcelDemy for more helpful content.
Regards
ExcelDemy
Hello, Many!
You are most welcome. To get more helpful content with explanations stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello, ExcelAmateur!
Thanks for your appreciation. To get more helpful content with explanations stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello, Mubashir!
Thanks for your appreciation. To get more helpful content stay in touch with ExcelDemy.
Regards
ExcelDemy
Glad to hear that.
Regards
ExcelDemy
Thanks for your appreciation.
Regards
ExcelDemy
Hello!
Thanks for your appreciation.
Regards
ExcelDemy
Hello, Andy W!
Thanks for your appreciation.
Regards
ExcelDemy
Hi, Dhaval!
Thanks for your appreciation.
Regards
ExcelDemy
Hello, Pankaj!
We are glad to know that it worked for you. To get more useful content stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello, Suzette!
Thanks for your appreciation. We will try to use fonts that will be easier to read for old eyes.
Regards
ExcelDemy
Hello, Eric!
Thanks for your appreciation. To get more helpful content stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello, Instast!
Thanks for your appreciation. To get more helpful content stay in touch with ExcelDemy.
Regards
ExcelDemy
Greetings ASHISH PANT,
I appreciate you asking this question. We use the Microsoft Office 365 version here. In order to resolve your problem, you need to open the Excel workbook in Microsoft Office 365 version.
Regards
ExcelDemy
Hello, Andre!
Thanks for your appreciation. To get more helpful posts stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello, Michelet!
Thanks for your appreciation. Stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello, Im!
Hope you are doing well. Thanks for your appreciation.
Regards
ExcelDemy
Hello, Leslie!
Hope you are doing well. Thanks for your appreciation. Stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello, Aston!
Hope you are doing well. To show your data labels in millions kindly follow this article Millions in Data Labels
Regards
ExcelDemy
Hello, Chan!
Hope you are doing well. Glad to know that our content is useful for you.
Regards
ExcelDemy
Hello, Kim!
Thanks for your appreciation. For more useful content visit our site ExcelDemy.
Regards
ExcelDemy
Hello, Suki Nasoordeen!
Thanks for your appreciation. Stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello Gerald,
Do you need to know the conversion from Excel to PDF? Right? Because the original bank statement was in PDF format. So, here you can follow the steps below to convert your Excel file back to PDF file.
Firstly, you need to open your Excel file.
Secondly, click the File button at the top-left corner of the Excel file.
Thirdly, go to Save As and click it.
Fourthly, choose PDF from the Save option at the top-right corner by clicking the drop-down button.
Finally, choose a particular location to keep it by selecting Browse button.
Thanks
Regards
ExcelDemy
Hello, Chole Faulkner!
Thanks for your appreciation. Stay in touch with ExcelDemy.
Regards
ExcelDemy
Hello, SS!
Hope you are doing well. We are very sorry that you are facing some troubles with advertising. But will try our best to give you a nicer experience.
Regards
ExcelDemy
Hi Zidane
Hope you are doing well. Thanks for your appreciation.
Regards
ExcelDemy
Hi Peter
Thanks for your appreciation.
Regards
ExcelDemy
Hi Mohammed Hassen,
Thank you so much.
Thanks
Regards
ExcelDemy
Hi N,
Thanks for your appreciation. Please visit our site for more excel-related problems.
Thanks
Regards
ExcelDemy
Hi Bernadeth,
Thanks for your appreciation. To get more useful content kindly visit our site.
Thanks
Regards
ExcelDemy
Hi GIRIDAR N,
Thanks for your appreciation and we are glad to help you. For any types of Excel-related problems kindly reach out to us.
Regards
ExcelDemy
Hello Marian,
There are some unwanted issues that may arise while dealing with the VBA code. In that case, you need to utilize the following code.
Here, the Application path and pdf path need to be defined accurately. Then, when you run the code, you will find the copied items in the clipboard.
Then, click on Paste all to extract values.
Thanks
Author, ExcelDemy
Hi Exceler,
Here, I tried the first method for 50 sets of data and it worked but you have to change the cell references based on your dataset. For your better understanding, I am attaching the images along with the formula.
=INDEX($D$5:$D$30,MATCH(1,MMULT(--($B$5:$C$30=F5),TRANSPOSE(COLUMN($B$5:$C$30)^0)),0))
The images of datasets
Here, I used the formula for the entire dataset. I changed the references based on my dataset.
Output for 50 values:
Note: If your dataset is very large kindly send us your dataset
Thanks
Shamima Sultana
Hi Tom Lynham
Hope you are doing well. Here, I updated the article which implies the title and the explanation.
By following above explained methods it is possible to print colorful gridlines but you must use a color printer.
If this update doesn’t help you or you have further queries kindly let us know.
Thanks
Shamima Sultana
Hello Mark,
We uploaded the Excel files again, you can check these files also. If you find any difficulty opening the file let us know.
Thanks
Hello Mark,
Hope you are doing well.
Whenever I try to download this workbook it works fine without a password. To be reassured a couple of my teammates also downloaded this file they also didn’t face any difficulty.
As I haven’t used any password for this worksheet. I really want to know what caused such issues while you downloaded the file.
Here, I will show you what it looks like when I download the file again.
After downloading the file Excel shows a warning message. You have to click on Enable Editing.
Later, the downloaded file will be available to use or you can make any changes you want.
N.B. If this solution doesn’t work for you. Kindly sent me the screenshots of the problem.
Thank you.
Regards
Shamima Sultana
Hi Froggy,
Hope you are doing well. Thanks for reaching out to us with your issue.
As you intend to apply the above code to a workbook with multiple sheets so you need to add some extra lines in your code to define all available worksheets or selected worksheets.
Here, I will give you two solution
1. For selected sheets
2. For all the available sheets in a workbook
1. If you want to apply the same code in some selected sheets then use the code given below where I declared sheets names by using an Array.
Option Explicit
Sub AddSpaceBetweenRows()
Dim all_sheets As Worksheet
Dim rng As Range
Dim i As Long
Dim all As Variant
Set all = sheets(Array(“VBA1”, “VBA2”))
For Each all_sheets In all
all_sheets.Select
Set rng = Range(“B5:E9”)
For i = rng.Rows.Count To 2 Step -1
rng.Rows(i).EntireRow.Insert
Next i
Next
End Sub
2. To loop through all the available sheets of your workbook you will need to use the code given below where I declared all_sheets and used an extra For Loop.
For your better understanding, I’m providing the modified code here,
Option Explicit
Sub AddSpaceBetweenRows()
Dim all_sheets As Worksheet
Dim rng As Range
Dim i As Long
For Each all_sheets In Worksheets
all_sheets.Select
Set rng = Range(“B5:E9”)
For i = rng.Rows.Count To 2 Step -1
rng.Rows(i).EntireRow.Insert
Next i
Next
End Sub
Note: Based on your Excel file you will need to change the sheet name and the cell range selection.
For further queries comment down below.
Hello Naveed,
Hope you are doing well. Kindly send me the details of your problem including the Excel file via my Gmail account [email protected]
Thank you
Hi Helen,
I’m glad that our article helped you. For any types of Excel related problems kindly check out our ExcelDemy site.
Thanks
Shamima
Hello Michelle,
Hope you are doing well. If you need to run the macro after the first use it will create new sheets with the updates you made on your dataset.
It won’t automatically update the workbooks created previously (Book1, Book2, and Book3) rather it will create Book4, Book5, and Book6 with the updated dataset.
Thanks
Shamima Sultana
Hi A,
Thanks for your appreciation.
Hi Sikander,
Hope you are doing well.
To get the DateValue for February, click on the drop-down option of Month and then select February.
Then you will get the DateValue and the rest will be updated automatically.
Note: If you want to type the month name in that cell you have to be careful with the spelling of the month name.
Thanks
Regards
Shamima Sultana
Hi Deon Bailey,
Hope you are doing well. Thanks for reaching out to me with your issue.
As you didn’t share your Excel file that’s why it is hard to understand your sheet name and where is the problem occurring.
But you need not to worry, I’m giving you a possible solution so that you can add data to your selected sheet to the last row.
The reason for overwriting the added data is your code wasn’t finding the last row it was showing 1 as the last row number.
Here, I added data according to my dataset I used in this article. I commented out your code and added some required lines.
Sub Insert_Value_from_LastRow()
‘TargetSheet = Cmb_Months.Value
‘If TargetSheet = “” Then
‘Exit Sub
‘End If
Dim targetSheet As Worksheet
Set targetSheet = ThisWorkbook.Worksheets(“Dataset”)
targetSheet.Activate
‘lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
MsgBox LastRow
‘ActiveSheet.Cells(LastRow + 6, 2).Value = Cmb_Area.Value
‘ActiveSheet.Cells(LastRow + 6, 3).Value = Txt_Ln_Manager.Value
‘ActiveSheet.Cells(LastRow + 6, 4).Value = Txt_FName.Value
‘ActiveSheet.Cells(LastRow + 6, 5).Value = Txt_Surname.Value
‘ActiveSheet.Cells(LastRow + 6, 6).Value = Txt_S_Number.Value
ActiveSheet.Cells(LastRow + 3, 2).Value = “Rachel Ross”
ActiveSheet.Cells(LastRow + 3, 3).Value = “Germany”
ActiveSheet.Cells(LastRow + 3, 4).Value = “laptop”
ActiveSheet.Cells(LastRow + 3, 5).Value = 4567
End Sub
Note: Whenever you want to insert or add data after a particular row it is better to see the last row number by using MsgBox. It will help you to understand why data is overlapping.
I also added the images.
For further queries, you can send me your Excel file.
Thanks
Shamima Sultana