# Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

## Keyboard Shortcuts in Excel (A Complete Guideline)

While working in Microsoft Excel, we often need to work with keyboard shortcuts to make things easier and more comfortable. But little do we know that it has ...

## How to Fix Compile Error in Hidden Module in Excel

Microsoft Excel users often encounter the "Compile Error in Hidden Module" issue. It generally occurs while running any code in Microsoft Visual Basic for ...

## How to Use IF with ISNA Function in Excel (3 Ideal Examples)

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 ...

## How to Use Tiered Interest Rate Calculator in Excel (3 Examples)

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 ...

## How to Use Multiple IF Statements in Excel Data Validation

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 ...

## How to Create Calendar with Time Slots in Excel (With Easy Steps)

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 ...

## Drawing Decision Trees in Excel (2 Easy Ways)

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 ...

## How to Calculate Eigenvalues and Eigenvectors in Excel

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 ...

## How to Create Goal Thermometer in Excel (3 Effective Ways)

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

## How to Create Full Outer Join in Excel (2 Ideal Examples)

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. ...

## Excel VBA to Create Timer Loop (3 Unique Examples)

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 ...

## How to Create Spin Button with VBA in Excel (3 Useful Methods)

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, ...

## How to Change Compatibility Mode in Excel (2 Easy Ways)

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 ...

## Excel VBA to Populate ComboBox from Dynamic Range

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 ...

## Excel Optimization with Constraints (3 Case Scenarios)

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 ...

Browsing All Comments By: Sanjida Mehrun Guria
1. Reply Sanjida Mehrun Guria Apr 13, 2023 at 12:30 PM

Hello Nah,
Thanks for your feedback. As mentioned in the article, you can try any of the methods for your preferred hours. Only the CONVERT function cannot do that because it is built to perform on standard time format. Therefore, please check the attached Excel file where the working day is considered 8 hours/day.
Thank you.

Regards,
Sanjida Mehrun Guria
Excel VBA & Content Developer
ExcelDemy

2. Reply Sanjida Mehrun Guria Apr 10, 2023 at 1:36 AM

Hello Jon,
Thank you for sharing your query. I have gone through your shared dataset but the values and their required output seem quite unclear in your question. Still, I have tried to solve it. You will find the solution in the Excel file below.
Please go through it and let us know if it is the solution that you are looking for. Otherwise, feel free to share your Excel file with [email protected] and we will look into this deeply.
Thank you.

Regards,
Sanjida Mehrun Guria
Excel VBA & Content Developer
ExcelDemy

3. Reply Sanjida Mehrun Guria Apr 10, 2023 at 1:11 AM

Hello Jake,
Thanks for sharing your query. Please find the attached Excel file with the solution. Let us know for more queries.
Thank you.

Regards,
Sanjida Mehrun Guria
Excel VBA & Content Developer
ExcelDemy

4. Reply Sanjida Mehrun Guria Apr 10, 2023 at 1:10 AM

Hello Luis,
Thank you for sharing your problem with us. As per your referred formula, I assume it is difficult to insert each product’s price in Column D for alignment. Because you need to have 2 separate datasets comprising Products and their Prices. Only after that, you can align them individually based on each category. For this, go through the following solutions that we described in this article.

Using VLOOKUP Function to Line up Two Sets of Data in Excel
https://www.exceldemy.com/align-two-sets-of-data-in-excel/#1_Using_VLOOKUP_Function_to_Line_up_Two_Sets_of_Data_in_Excel

Use of Consolidate Feature for Summing Up Values within Two Sets of Data in Excel
https://www.exceldemy.com/align-two-sets-of-data-in-excel/#5_Use_of_Consolidate_Feature_for_Summing_Up_Values_within_Two_Sets_of_Data_in_Excel

Thank you.

Regards,
Sanjida Mehrun Guria
Excel VBA & Content Developer
ExcelDemy

5. Reply Hello Scott,
Thank you for sharing your problem. As per your query, you can simultaneously format specific text in Bold, Italic, Underline and even change Font Size with 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.

Regards,
Guria
ExcelDemy

6. Reply 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/12 and later 6 months’ with =8/12. As a result, you will get the values of monthly interests.

Regards,
Guria
ExcelDemy

7. Reply 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

8. Reply Hello Naureen,
Thank you for sharing your problem. I am replying on behalf of ExcelDemy. Assuming the student names are in Column A and the scores are in Column B, insert this formula to get the top 10 scorers 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

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

Regards,
Guria
ExcelDemy

10. Reply 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 to One Lac with this formula where Thousand is 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"))``` Regards,
Guria
ExcelDemy

11. Reply 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.
• Then, select the words that you don’t want to be shown as hyperlinks.
• Now, remove Underline from the selected texts from the Home tab or by pressing Ctrl + U on your keyboard.
• Along with it, change the Font Color to Automatic.
• Finally, the output will look like this. Thank you.

Regards,
Guria
ExcelDemy

12. Reply 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 Columns feature. The reason that any changes are not updating is because the Text to Columns feature is static and 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 the Flash Fill feature. In this case, I will suggest you to apply any other methods from this article except Method 4 and 5. 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.

13. Reply 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, "-")`
`arry = VBA.Split(rng, " ")`

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

Regards,
Guria
ExcelDemy.

14. Reply 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 calculation option in File > Options > Formulas section 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.

15. Reply Hello Violet,
Thanks for sharing your query. Yes, the PMT does include tax or insurance. Therefore, you will provide \$1308 as the PMT in your spreadsheet.
Let us know if you have more queries.

Regards,
Guria
ExcelDemy.

16. Reply Hello Tony Woods,
Thank you for sharing your query. I have tried to change values in the Level column 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 the Text format in the Timestamp column
For this, go to Data > Text to Column.
Then, in the second step, keep all the Delimiters unchecked. Lastly, select Date as the Column Data Format > choose your preferred format from the drop-down list > press Finish. Solution 2: Check settings in Formulas section
For this, go to File > Options and then check if this setting is active in your workbook. Solution 3: Check settings in Proofing section
For this, go to go to File > Options and then check if the settings in the Proofing section are active in your workbook. Regards,
Guria,
Exceldemy

17. Reply 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 Column which is the 33rd column in the workbook then replace
`If Target.Address = "\$AG\$4" Then`
With
`If Target.Column = 33 Then`
Regards,
Guria,
Exceldemy

18. Reply 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))` Thanks!

19. Reply 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/
Thank you!

20. Reply 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!

21. Reply Hello Russell,
Thank you for sharing your query. I hope the following solution will work for you.
First, click on the Start icon and select Settings. Then, select Apps in the new window. Next, go to the Default apps section on the left panel.
Here, check if the application for Email is selected as Mail.
If not, then click on it and choose Mail from the list of applications. Thanks!

22. Reply 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\Fonts folder.
First, insert this formula for the data 456987321596328 and press Enter.
`="*"&B5&"*"` Then, change the Font to Code 128 and the Size to 36 for 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.
Thanks!

23. Reply Hi Muniru Tahiru,
https://www.exceldemy.com/automatically-update-one-excel-sheet-from-another-sheet/#top_ankor
Please let us know if you have other queries.
Thanks! Advanced Excel Exercises with Solutions PDF  