While working with Excel, we often face #N/A error when the required value is missing from the dataset. But if you want to get rid of this visible error and ...

In banking or other money exchange fields, we often face the term Tiered Interest Rate. It means that, according to the policy of the specific country, there ...

In Microsoft Excel, the IF function is one of the widely used ones for conditional calculation. In Excel Data Validation, we can even use the IF function more ...

Microsoft Excel is a powerful tool to create a calendar. Even when it comes to making it dynamic with time slots, Excel is the best option. In this article, we ...

A decision tree is a kind of flow chart in Excel. It helps to visualize certain conditions clearly and take action according to them. There are various ways to ...

In the world of matrix problems, Eigenvalue and Eigenvector are one of the most important terms. They are widely used to solve linear equations reducing ...

For analyzing and comparing data, Goal Thermometer is a wonderful tool for that. It helps to get a clear visualization of risk and achievement. In this ...

The graph is one of the most widely used tools in Microsoft Excel. It helps to represent certain numeric values in a more presentable way. Along with the ...

Merging or joining columns is a very common requirement among Excel users. But things get really difficult when the dataset is large and has repetitive values. ...

In Microsoft Excel, we often need to run certain VBA codes that help to create a timer loop. This timer loop can be different based on the required output. But ...

When we work in Excel, we often need to change some values in an increasing or decreasing manner. But it gets very monotonous to type each time. Therefore, ...

In Microsoft Excel, there are several options to save a workbook. Each option has its own features. You may also find some files in Compatibility Mode and need ...

When we work with a large dataset, we often need to find some specific values under certain categories. But it gets really difficult to just scroll and find ...

Optimization analysis is a more complex part of goal-seeking analysis for one or more variables based on certain constraints. Even you can change the ...

In Microsoft Excel, we often use a Text Box for indicating certain instructions. It is a very handy tool with different types of elements. We can edit this ...

- 1
- 2
- 3
- …
- 7
- Next Page »

Hello Scott,

Thank you for sharing your problem. As per your query, you can simultaneously format specific text in

Bold,Italic,Underlineand even changeFont Sizewith the following code.`Sub Highlight_a_Single_Specific_Text_Case_Insensitive()`

`Text = InputBox("Enter the Specific Text: ")`

`Color_Code = Int(InputBox("Enter the Color Code: " + vbNewLine + "Enter 3 for Color Red." + vbNewLine + "Enter 5 for Color Blue." + vbNewLine + "Enter 6 for Color Yellow." + vbNewLine + "Enter 10 for Color Green."))`

`For i = 1 To Selection.Rows.Count`

`For j = 1 To Selection.Columns.Count`

`For k = 1 To Len(Selection.Cells(i, j))`

`If LCase(Mid(Selection.Cells(i, j), k, Len(Text))) = LCase(Text) Then`

`Selection.Cells(i, j).Characters(k, Len(Text)).Font.ColorIndex = Color_Code`

`Selection.Cells(i, j).Characters(k, Len(Text)).Font.Bold = True`

`Selection.Cells(i, j).Characters(k, Len(Text)).Font.Italic = True`

`Selection.Cells(i, j).Characters(k, Len(Text)).Font.Underline = True`

`Selection.Cells(i, j).Characters(k, Len(Text)).Font.Size = 16`

`End If`

`Next k`

`Next j`

`Next i`

`End Sub`

Apply this code to the selected cells of your dataset and you will get the desired output.

I hope this solution will help you. Waiting to get your feedback.

Regards,

Guria

ExcelDemy

Hello Nicola,

Thank you for sharing your problem. I assume you are getting difficulties to calculate interest rate as changed twice in a year. In that case, calculate first 6 months’ rate separately with this formula

=7.5/12and later 6 months’ with=8/12. As a result, you will get the values of monthly interests.I hope this solution will help you. Let us know your feedback.

Regards,

Guria

ExcelDemy

Hello Donald Duck,

Thank you for sharing your wonderful tip to hide the secondary axis in Excel. I hope it will profoundly help our users.

Regards,

Guria

ExcelDemy

Hello Naureen,

Thank you for sharing your problem. I am replying on behalf of ExcelDemy. Assuming the student names are in

Column Aand the scores are inColumn B, insert this formula to get the top10scorers with duplicate values.`=INDEX(SORT(A2:B16,2,-1),SEQUENCE(10),1)`

If you want to get the names in another sheet, then just add the worksheet names in the formula (as shown below) and you will get your required output.

`=INDEX(SORT(‘Semester 2’!A2:B16,2,-1),SEQUENCE(10),1)`

I hope this solution will help you. Otherwise, feel free to share your workbook with

[email protected]and we will look into this deeply.Regards,

Guria

ExcelDemy

Hello ExcelIsAbandonware,

Thank you for sharing this Excel bug issue with the solution. I hope it will profoundly help our users.

Regards,

Guria

ExcelDemy

Hello Arif,

Thank you for sharing your query with us. As per your question, there can be 2 solutions to the problem.

1. If your number is

100000, then convert it toOne Lacwith this formula whereThousandis kept blank.`=CHOOSE(LEFT(TEXT(H6,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &IF(--LEFT(TEXT(H6,"000000000.00"))=0,,IF(AND(--MID(TEXT(H6,"000000000.00"),2,1)=0,--MID(TEXT(H6,"000000000.00"),3,1)=0)," Lac"," Lac and ")) &CHOOSE(MID(TEXT(H6,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ") &IF(--MID(TEXT(H6,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(H6,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"), CHOOSE(MID(TEXT(H6,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")) &IF((--LEFT(TEXT(H6,"000000000.00"))+MID(TEXT(H6,"000000000.00"),2,1)+MID(TEXT(H6,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(H6,"000000000.00"),4,1)+MID(TEXT(H6,"000000000.00"),5,1)+MID(TEXT(H6,"000000000.00"),6,1)+MID(TEXT(H6,"000000000.00"),7,1))=0,(--MID(TEXT(H6,"000000000.00"),8,1)+RIGHT(TEXT(H6,"000000000.00")))>0)," Crore and "," Crore ")) &CHOOSE(MID(TEXT(H6,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &IF(--MID(TEXT(H6,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(H6,"000000000.00"),5,1)=0,--MID(TEXT(H6,"000000000.00"),6,1)=0)," Lac"," Lac")) &CHOOSE(MID(TEXT(H6,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety") &IF(--MID(TEXT(H6,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(H6,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(H6,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen")) &IF((--MID(TEXT(H6,"000000000.00"),4,1)+MID(TEXT(H6,"000000000.00"),5,1)+MID(TEXT(H6,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(H6,"000000000.00"),7,1)+MID(TEXT(H6,"000000000.00"),8,1)+MID(TEXT(H6,"000000000.00"),9,1))=0,--MID(TEXT(H6,"000000000.00"),7,1)<>0)," "," ")) &CHOOSE(MID(TEXT(H6,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &IF(--MID(TEXT(H6,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(H6,"000000000.00"),8,1)=0,--MID(TEXT(H6,"000000000.00"),9,1)=0)," Hundred "," Hundred and "))& CHOOSE(MID(TEXT(H6,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ") &IF(--MID(TEXT(H6,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(H6,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(H6,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))`

2. If your number is

123450, convert it into a word with this formula.`=CHOOSE(LEFT(TEXT(H6,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &IF(--LEFT(TEXT(H6,"000000000.00"))=0,,IF(AND(--MID(TEXT(H6,"000000000.00"),2,1)=0,--MID(TEXT(H6,"000000000.00"),3,1)=0)," Lac"," Lac and ")) &CHOOSE(MID(TEXT(H6,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ") &IF(--MID(TEXT(H6,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(H6,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"), CHOOSE(MID(TEXT(H6,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")) &IF((--LEFT(TEXT(H6,"000000000.00"))+MID(TEXT(H6,"000000000.00"),2,1)+MID(TEXT(H6,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(H6,"000000000.00"),4,1)+MID(TEXT(H6,"000000000.00"),5,1)+MID(TEXT(H6,"000000000.00"),6,1)+MID(TEXT(H6,"000000000.00"),7,1))=0,(--MID(TEXT(H6,"000000000.00"),8,1)+RIGHT(TEXT(H6,"000000000.00")))>0)," Crore and "," Crore ")) &CHOOSE(MID(TEXT(H6,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &IF(--MID(TEXT(H6,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(H6,"000000000.00"),5,1)=0,--MID(TEXT(H6,"000000000.00"),6,1)=0)," Lac"," Lac")) &CHOOSE(MID(TEXT(H6,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety") &IF(--MID(TEXT(H6,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(H6,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(H6,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen")) &IF((--MID(TEXT(H6,"000000000.00"),4,1)+MID(TEXT(H6,"000000000.00"),5,1)+MID(TEXT(H6,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(H6,"000000000.00"),7,1)+MID(TEXT(H6,"000000000.00"),8,1)+MID(TEXT(H6,"000000000.00"),9,1))=0,--MID(TEXT(H6,"000000000.00"),7,1)<>0)," "," ")) &CHOOSE(MID(TEXT(H6,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &IF(--MID(TEXT(H6,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(H6,"000000000.00"),8,1)=0,--MID(TEXT(H6,"000000000.00"),9,1)=0)," Hundred "," Hundred and "))& CHOOSE(MID(TEXT(H6,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ") &IF(--MID(TEXT(H6,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(H6,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(H6,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))`

I hope this solution will help you. Let us know your feedback.

Regards,

Guria

ExcelDemy

Hello Felix,

Thank you for sharing your query. Unfortunately, you cannot expressly create a hyperlink for a single word in a text string under a cell. By default, Excel creates a hyperlink for the whole cell. But you can change the cell appearance to show the hyperlink only to a single word. For this, do the following tasks.

• First, create a hyperlink for the whole text following any methods from this article.

• Then, select the words that you don’t want to be shown as hyperlinks.

• Now, remove

Underlinefrom the selected texts from theHometab or by pressingCtrl + Uon your keyboard.• Along with it, change the

Font ColortoAutomatic.• Finally, the output will look like this.

I hope this will help you with your problem. Eagerly waiting for your feedback.

Thank you.

Regards,

Guria

ExcelDemy

Hello Edmund Goh,

Thank you for sharing your problem with us. I assume you have properly gone through the steps to separate numbers with the

Text to Columnsfeature. The reason that any changes are not updating is because theText to Columnsfeature isstaticand it will not take any changes from the source cell. Yes, this is a limitation of this feature. You will also face the same if you apply theFlash Fillfeature. In this case, I will suggest you to apply any other methods from this article exceptMethod 4and5. Then you will be able to get changes on the separated numbers from the text.Let us know if it helps you.

Thank you.

Regards,

Guria

ExcelDemy.

Hello Theva,

Glad that you shared your query. As per your given example, if you want to simplify the first letter of each word along with numbers, apply this simple VBA code.

`Function ExtractFirstLetters(rng As Range) As String`

`Dim arry`

`Dim X As Long`

`arry = VBA.Split(rng, " ")`

`If IsArray(arry) Then`

`For X = LBound(arry) To UBound(arry)`

`ExtractFirstLetters = ExtractFirstLetters & Left(arry(X), 1)`

`Next X`

`Else`

`ExtractFirstLetters = Left(arry, 1)`

`End If`

`End Function`

Note that, if the words in your dataset are separated by

Hyphens(–) then insert this line in the code`arry = VBA.Split(rng, "-")`

Instead of this,

`arry = VBA.Split(rng, " ")`

I hope this will solve your problem. Let us know your feedback.

Regards,

Guria

ExcelDemy.

Hello Mike,

Thanks for sharing your problem. It seems fine for me when I tried to save the excel file shared in this article. The timestamp does not change in my case. Therefore, I will suggest you go through the steps carefully, no matter which method you prefer. Also, please check if the

Enable iterative calculationoption inFile>Options>Formulassection is turned on. If it still changes then try the VBA code shared in this article.https://www.exceldemy.com/timestamp-in-excel-when-cell-changes/#2_Apply_VBA_Code_to_Insert_Timestamp_in_Excel_When_Cell_Changes

Let us know if you can solve the issue. Otherwise, please send us the excel file that you are working on at this address [email protected].

Regards,

Guria

ExcelDemy.

Hello Violet,

Thanks for sharing your query. Yes, the

PMTdoes include tax or insurance. Therefore, you will provide $1308as thePMTin your spreadsheet.Let us know if you have more queries.

Regards,

Guria

ExcelDemy.

Hello Tony Woods,

Thank you for sharing your query. I have tried to change values in the

Levelcolumn and the timestamp changes according to the custom date format you shared. So it seems there is nothing wrong with your preferred custom format or the method that we described in the article. Therefore, I have come to the following solutions to your problem.Solution 1: Change theTextformat in theTimestampcolumnFor this, go to

Data>Textto Column.Then, in the second step, keep all the

Delimitersunchecked.Lastly, select

Dateas theColumn Data Format> choose your preferred format from the drop-down list > pressFinish.Solution 2: Check settings inFormulassectionFor this, go to

File>Optionsand then check if this setting is active in your workbook.Solution 3: Check settings inProofingsectionFor this, go to go to

File>Optionsand then check if the settings in theProofingsection are active in your workbook.I hope the solutions will help you to work efficiently. Let us know your feedback.

Regards,

Guria,

Exceldemy

Hello Allison,

Glad that you shared your problem. I have looked at it and therefore suggest these solutions.

If you want a drop-down list for some selected cells (

AG4:AG43) then replace`If Target.Address = "$AG$4" Then`

With

`If Target.Address = "$AG$4" Or Target.Address = "$AG$5" Or Target.Address = "$AG$6" …….. Or Target.Address = "$AG$43" Then`

If you want a drop-down list for the whole

AG Columnwhich is the33rd columnin the workbook then replace`If Target.Address = "$AG$4" Then`

With

`If Target.Column = 33 Then`

I hope the solutions will help you. Let us know your feedback.

Regards,

Guria,

Exceldemy

Hello Frank,

Thank you for sharing your query. I am replying to you on behalf of ExcelDemy. According to your question, I assume the dataset will look like this where the product “Ice Cream” is in two rows with different dates and prices.

Now, to solve your problem, you have to put a date in Criteria 2 that lies in between the dates you want a price from. Afterward, apply the same formula as we used earlier and it will show the accurate price.

`=INDEX($E$5:$E$16,MATCH(1,(($B$5:$B$16=G5)*($D$5:$D$16>=H5)*($C$5:$C$16<=H5)),0))`

I hope this solution will help you. Let us know your feedback.

Thanks!

Hello Tom,

I am replying to you on behalf of ExcelDemy. It is very unfortunate to know that, you are facing such difficulties. I have gone through your queries and thought of giving the following solutions.

1. First of all, please check this link in case you unintentionally protected your excel file through any of the methods described.

https://www.exceldemy.com/protect-excel-sheet-from-copy-paste/

2. When any worksheet or workbook is password protected, you cannot see them in actual letters, rather than some dots. Therefore, if you cannot unprotect the sheet with the help of this article, I am referring you to another one with some easy VBA codes to unprotect your file permanently without knowing the password.

https://www.exceldemy.com/unprotect-all-sheets-in-excel-vba/

3. You also mentioned that you are having trouble printing the worksheet. In this case, check this solution to solve this issue.

https://www.exceldemy.com/excel-margins-not-printing-correctly/

I hope the solutions will help you. Let us know your feedback.

Thank you!

Hello Mirela,

Thank you for sharing your query. It would be really great if you can send the workbook to [email protected] as It seems quite confusing to create a dataset with the information you shared. After that, I will try to solve your problem. Hope to hear from you soon.

Thanks!

Hello Russell,

Thank you for sharing your query. I hope the following solution will work for you.

First, click on the

Starticon and selectSettings.Then, select

Appsin the new window.Next, go to the

Default appssection on the left panel.Here, check if the application for

Emailis selected asMail.If not, then click on it and choose

Mailfrom the list of applications.After this, I recommended you restart your device once. I hope it will solve your problem. Please let me know your feedback.

Thanks!

Hi Jacek,

Thank you for sharing your query. To solve this, you can try another formula instead after copying the Code 128 Font in the

C:\Windows\Fontsfolder.First, insert this formula for the data

456987321596328and press Enter.`="*"&B5&"*"`

Then, change the Font to

Code 128and the Size to36for creating the barcode.Make sure, your input data is in Text format before applying the formula.

Moreover, VBA is not error-free. So I suggest you create a new Excel-Macro Enabled Workbook and copy-paste the VBA code to create your own barcode.

I hope this will help you to solve your problem. Please let us know if you have other queries.

Thanks!

Hi Muniru Tahiru,

Thank you for sharing your problem. To solve this you can go through this article. I hope it will help you.

https://www.exceldemy.com/automatically-update-one-excel-sheet-from-another-sheet/#top_ankor

Please let us know if you have other queries.

Thanks!