Excel Map Chart is an effective tool for delivering insights and displaying data. However, occasionally customers encounter problems with the Map Chart not ...

Microsoft Excel is very useful to build a power energy cost calculator. We can use Excel formulas to create the calculator efficiently saving a lot of time and ...

Has your Microsoft Excel file suddenly ceased to function and begun to display the Excel Not Responding error message? Further, while attempting to operate on ...

Microsoft Excel computes costs associated with our mortgages, such as interest and monthly payments, which is one of its powerful features. If you are a little ...

If you are hoping to find the minimum value greater than zero in your large Excel dataset, you have come to the right place. As business analysts, we use Excel ...

Microsoft Excel is a powerful software. We use Excel formulas and functions in our education, business, and workplaces. Moreover, using Excel, HR can track ...

Microsoft Excel is a powerful tool. We use Excel Functions in our education, business, and financial analysis. Furthermore, as bookkeepers, we have to build ...

The entire Excel application is under VBA Application Object. Moreover, Excel provides different application methods like the VBA Intersect method to return a ...

By checking for a condition and delivering a result if it is TRUE or FALSE, the IF function enables us to logically compare a value to what we anticipate. What ...

Microsoft Excel is a handy software. We use Excel functions for our education, business, and financial analyses. Moreover, in light of the effective rate and ...

Microsoft Excel is a powerful software. We create different charts with leader lines in our business analyses and presentations. Unfortunately, Excel does not ...

Microsoft Excel is a powerful software. We use Excel functions and features for our educational, business, and other mathematical analyses such as stock ...

There are several distinct Microsoft Excel functions for determining the average of integer values. Additionally, there is a quick non-formula way. This ...

While generating a Pivot Table in Microsoft Excel, various Filed Buttons including Filter Arrows appear automatically at the table headers. As a user, we might ...

Businesses use Excel functions like EDATE for a variety of tasks, including tracking data records, scheduling, and producing analytical reports. If using Excel ...

Dear BZOIRO,

Yes, you can modify the SearchAll function in the given VBA code to search for each word in the cells separately. I have added the modified code in the Excel file below. This modification splits the search value into words using the Split function and loops through each word to find it in the range. If all words are found, the function merges the found ranges into a single range and thoroughly returns it. If any of the words are not found, the function exits and returns Nothing. Here’s how to use this for your case:

1. Initially, assign Macro “SearchMultipleSheetsIndividually” in the Search command box and click Run.

2. Afterward, type the word you wish to look for. Also, don’t forget to use the Wildcards (*, ? etc) while searching. For instance, if you wish to look for a cell of 3 words and has “Emily” in the middle, type *Emily* and then perform searching. Alternatively, if it happens to be the first word, try Emily*.

Download the Excel file to get the modified VBA code and practice by yourself. I hope it works for you.

https://www.exceldemy.com/wp-content/uploads/2023/01/Search-Box-for-Multiple-Sheets.xlsm

Best Regards,

Yousuf Khan (ExcelDemy Team)

Dear LUNDER,

My understanding of your statement implies that there is an issue with the calculations being performed in Microsoft Excel, specifically with the trigonometric functions cosine (cos), cosine (cos), and sine (sin) for certain angle values.

Further, I concur that the error seems to affect the precision of the results starting from the 17th decimal place, which makes the calculated values for the angles cos 90°; cos 270°; sin 180° to be inaccurate. However, it is important to note that the internal representation of floating-point numbers in computers can lead to inaccuracies, especially when working with very small or large numbers, or when performing multiple mathematical operations.

In these cases, you can take the following steps to resolve this issue.

1. Use the RADIANS function to convert the angle from degrees to radians before evaluating the trigonometric functions.

2. Increase the number of decimal places used in the calculations by using the ROUND or ROUNDUP functions.

Best Regards,

Yousuf Khan (ExcelDemy Team)

Dear TONY,

It’s possible that the delay is due to the amount of data being processed by the validation formula. To reduce the delay, you can try the following:

1. Turn off screen updating and calculation during the execution of the macro to reduce the time it takes to display the changes.

2. Consider using a different method for data validation, such as using a custom function or a Macro.

As requested, I have added a revised version of the VBA code with the optimizations under Private Sub Worksheet_Change in Module 3. I hope, these modifications will improve the performance of the code, as it minimizes the amount of visual updates that occur during the process. This will reduce the amount of time it takes for the changes to be made in the sheet as you type in. Download the Excel file and use the desired code.

https://www.exceldemy.com/wp-content/uploads/2023/01/Multiple_Selection_from_Drop-Down.xlsm

Best Regards,

Yousuf Khan (ExcelDemy Team)

Hello ASRA,

The Data Type option in the Data tab in Microsoft Excel is available in both the online and offline versions of Excel.

The offline version of Microsoft Excel is part of the Microsoft Office suite, which can be purchased and installed on your computer. Once you have installed Microsoft Office, you can access Microsoft Excel from the start menu or by double-clicking on an existing Excel file on your computer. The Data Type option in the Data tab will be available in the Excel application after installation. I hope this helps.

Regards,

Yousuf Khan (ExcelDemy Team)

Dear GORAN ZORIC,

Thank you for your comment! We are glad you took the time to review the article. Regarding the trade direction (BUY or SELL), it is actually represented in the Long / Short (L/S) column in the trading journal template. The trade direction entry can be either a “Buy” (long) or “Sell” (short) and the template will automatically add this field after you enter your entry and exit prices. We apologize for any confusion and hope this clarifies the matter. Thank you for bringing this to our attention.

Regards,

Yousuf Khan (ExcelDemy Team)

We apologize for the inconvenience. We are aware of the missing source reference and are working on updating the explanation. Further, we recommend using the code given before the explanation. Thank you for bringing this to our attention.

Hello FAHIM,

Yes, it is possible to highlight rows and columns of multiple active cells using conditional formatting in Microsoft Excel. To do so, you can use the following steps:

1. Initially, select the cells you want to apply the conditional formatting to.

2. Afterward, go to the Home tab and click the Conditional Formatting button in the Styles group.

3. Later, choose New Rule from the drop-down menu.

4. Next, select “Use a formula to determine which cells to format.”

5. Meanwhile in the formula box, enter the following formula:

`=ROW()>=MIN(ROW($C$4:$F$10))`

6. Further, click the Format button and select the fill color you want to use.

7. Lastly, click OK to close the Format Cells dialog box.

This approach uses a relative reference for the row and column in the formula, allowing the conditional formatting to adjust automatically as cells are selected.

Hello RUDOLF,

Thank you for bringing this to our attention! Yes, checking the Data Validation is a good suggestion to find and remove external links in Excel. Although, external links in Data Validation are not directly removable through Data Validation itself. However, by updating the source data for the Data Validation drop-down list, you can remove any external links that may be present.

Regards,

Yousuf Khan (ExcelDemy Team)

Hello Mike,

Thank you for sharing your experience with us!

I understand you are getting non-clickable link text when you are copying the URL from web browsers. Also, the

Paste Specialcommand is not responding which would solve the issue in your case. Unfortunately,Paste Specialcommand does not offer the Paste Link option while pasting web URLs.But Excel directly inserts a hyperlink to a copied URL when you:

1. Copy the URL from a web browser >> select the cell where you want to paste >> paste the URL in the

Formula Bar.2. Alternatively, you can directly paste the URL to a cell >> go to

Formula Bar>> pressEnter.This will convert the Text link to Hyperlink. You can also use the

HYPERLINKfunction to get clickable URL links(read the article).How to Use Excel HYPERLINK Function

Regards,

Yousuf Shovon

Hello MISLED READER,

Thank you for reading our article and your feedback. You have specified an incorrect use of the

IPMTfunction that does not consider the start and end dates. And, you are correct about the fact that the method calculates the interest payable in the 1st period of a 5 year note represented by the hardcoded “1” used in the “PER” section of the formula. TheIPMTfunction returns interest rates based on periods and not specific calendar dates. Thank you for pointing out the article gap to us. We will fix the issue with correct information. For now, we will show you another method here.You can use the other Excel basic formula to calculate the interest between two dates. Or, enter a custom VBA function as described below.

This function will take the loan details, including start and end dates, as input arguments and return the interest amount.

Here is the syntax and arguments of the

CalculateInterestfunction I have created:Here are the steps to implement the function:

1. Save the below VBA code to a Module.

2. Now, enter the VBA function in cell

C11>> pressEnterkey to get the interest.This formula calculates the interest between February 22, 2022, and March 24, 2022, based on the provided loan details.

Feel free to let us know your future queries and suggestions as we always appreciate them. Thank you.

Regards,

Yousuf Shovon

Hello Anthony,

Please, see the forum post to ignore cells when applying the

SORTfunction.Ignoring Null Cells In The Sort Function

Regards,

Yousuf Shovon

Hello Neil,

Yes, there is a way to name the resultant files using Excel VBA. I am modifying the VBA code to split the column into multiple workbooks given above. This code will rename the new files as month names using monthNames () array.

Here is the modified code:

Steps:1. Enter the code in a module >> close the

Visual Basicwindow.2. Go to the sheet you want to split >> Click on

Developer>>Macros.3. In

Macrodialog, selectSplitSheet_IntoMultipleWorkbooks_BasedOnColumn_withnames>>Run.4. Consequently, the new files appear with declared names.

Note:The new files are created in.xlsformat as you requested. You can change the format in the code if you want.I hope this was helpful to you. Let me know if you have any further queries.

Best Regards,

Yousuf Shovon

Dear Mike,

I understand you wish to insert a range to have the text as input. Here is the modified code to do so.

Also, you can insert a data range by adding variables. To know more about this, see this comment.

Hello PENELOPE JORDAN,

We are glad these methods were helpful to you. Though it seems, you are facing issues with the INDEX & MATCH method for multiple columns. If you are talking about the #NA! error using the given formula, it can be solved with an easy step. Just fill up the Product column with the proper value first and enter the given formula afterward. Thus, you will obtain the desired result.

Try this way and let us know if it works.

Regards,

Yousuf Khan Shovon

Hello JACK MACEY,

I understand you are facing issues while creating the weekly and monthly sheets using the same given code. The provided code works perfectly for

Dailyfrequency. But forWeeklyandMonthlyintervals, you have to adjust the parameter of the frequencies in the code accordingly.In this line below, we have declared the parameter of the frequency.

We have to add the

interval=1wkorinterval=1moparameters to the URL, which specify theWeeklyandMonthlyintervals.Here are the modified codes.

For

Weeklyfrequency,And for

Monthlyfrequency,Try this way. And, let me know if it works.

Regards,

Yousuf Khan Shovon

Thank you for your very useful suggestion, VAIBHAV SRIVASTAVA. I understand you suggested summing a text string which is much more relatable to this article. And to do so, you can merge the

SUM,VALUE, andTEXTSPLITfunctions. Here is the combined formula:`=SUM(VALUE(TEXTSPLIT(A1,"+")))`

Don’t hesitate if you have further suggestions for us. Thanks again.

Regards,

Yousuf Khan Shovon

Dear COLM,

Thank you for your very useful suggestion. I am including the note in the article with much appreciation.

Regards,

Yousuf Khan.

Hello NISHANTH VJ,

Thank you for your concern. Though I am afraid I am having trouble understanding the query clearly. Assuming you wish to modify the formula to account for the cost even in different forms every time you check in, you can use the weighted average cost of goods sold

(COGS)method. This method calculates the average cost of all goods sold during a period, taking into account the varying costs of different ingredients.The formula:

`Food Cost Percentage = (Total Cost of Goods Sold ÷ Total Revenue) x 100`

To calculate the total cost of goods sold, you can use the following formula:

`Total Cost of Goods Sold = (Beginning Inventory + Purchases - Ending Inventory) x Weighted Average Cost per Unit`

To calculate the weighted average cost per unit, you can use the following formula:

`Weighted Average Cost per Unit = Total Cost of Goods Available for Sale ÷ Total Units Available for Sale`

By using this modified formula, you can account for the fluctuations in the cost of food and beverage and calculate your food cost percentage accurately, even if the cost of ingredients varies over time.

Regards,

Yousuf Khan Shovon

Dear RICH SAUNDERS,

I understand your frustration with trying to find specific

INDEX/MATCHassistance for your Excel project. Here, I’ll create the Summary tab using INDEX/MATCH functions to connect the other four tabs.To begin, let’s assume that your four tabs are named

Dataset1,Dataset2,Dataset3, andSummary. To retrieve data from the Dataset1 tab, use the following formula:`=INDEX(Dataset1!$A$1:$O$40,MATCH($A2,Dataset1!$A$1:$A$40,0),MATCH(B$1,Dataset1!$A$1:$O$1,0))`

Be sure to update the tab name and range of cells to match the appropriate tab and data range.Note:I hope this helps! Let me know if you have any further questions.

Regards,

Yousuf Khan Shovon

Hello FRANK,

Glad to know the basic methods were useful to you. We further appreciate your valuable insights in addition to these methods. We will look into the advanced techniques and add them in the future. Thank you.

Regards,

Yousuf Khan

Hello ARCHIE,

I understand you wish to fix the #VALUE! error for VLOOKUP. In your case, the reason this is returning #VALUE! error for alpha-numeric values is that it is trying to convert the text values to numbers using the VALUE function. The VALUE function is designed to convert text that represents a number to an actual number. When it encounters alpha-numeric values, it cannot convert them to numbers, hence the error.

Fortunately, you can fix this by modifying the formula by replacing the VALUE function with the TEXT function. The TEXT function converts a value to text in a specific number format. Try the modified formula given below:

`=IF(ISNA(VLOOKUP(TEXT($A42,"0"),PROJECT_LIST!$A:$C,3,FALSE)),"",VLOOKUP(TEXT($A42,"0"),PROJECT_LIST!$A:$C,3,FALSE)&" ")`

Let me know with a demo dataset if you still face issues. Good luck!

Note:Make sure data are sorted in ascending to descending order in the lookup table.Regards,

Yousuf Khan

Hello MONTY,

Thank you so much for your useful suggestion. Our Exceldemy team will include the note in the article as soon as possible. We always appreciate any further concerns or suggestions you may have.

Hello ADITYA,

You’re welcome! We are glad to hear that you found the solution to your issue. Unprotecting the workbook can indeed resolve the issue of being unable to set the visible property of the Worksheet class. It’s great to see that your dynamic VBA code is working as expected. Let us know if there’s anything else we can help with.

Regards,

Yousuf Khan

Hi MICHAEL,

The errors you’ve mentioned could indicate that the workbook is either very hidden or corrupted. There are two levels of worksheet hiding: hidden and very hidden. From a user’s perspective, the difference is that a very hidden sheet cannot be made visible through the Excel user interface, and the only way to unhide it is with VBA.

Since you’ve used VBA to work with the worksheets and they still appear hidden, you can try a different VBA code to check if they’re very hidden and unhide them. Here’s the VBA code to unhide all very hidden sheets:

Note: This code only works for very hidden sheets, not worksheets that are hidden normally. If you want to display all hidden sheets, use the code below.

Give it a try and let me know if it works.

Best Regards,

Yousuf Khan