# Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her professional endeavors.

## Designation

Junior Software Analyst in SOFTEKO.

## Education

B.sc in Industrial and Production Engineering, Bangladesh University of Engineering and Technology

## Expertise

Content Writing, Dashboard, Software Analysis, VBA, Microsoft Office.

## Experience

• Software Analysis
• Technical Content Writing
• Team Management

## Summary

• Currently working as Junior Software Analyst in SOFTEKO
• Started technical content writing of Excel & VBA in June 2022

## How to Create a Heatmap in Excel (Static, Dynamic and Geographic)

What Is a Heatmap? A heat map visually represents data using colors in a two-dimensional or geographic area. Each data point corresponds to a color ...

## How to Calculate Ratio in Excel: 4 Methods

Method 1 - Use Simple Division to Calculate Ratio Select the cell where you want to calculate the ratio >> Write the following formula: ...

## Confidence Interval in Excel: Upper & Lower Limit

Method 1 - CONFIDENCE Function It returns the confidence value for the population mean using the normal distribution. The syntax for this function is. ...

## Rows and Columns in Excel (All You Need to Know)

In Microsoft Excel, rows and columns form a grid structure in the spreadsheet. Each cell in the grid is identified by a unique combination of its row number ...

## Break Even Analysis in Excel (Calculations and Template)

Let's use the following dataset of sales and costs for a product to conduct a break even analysis. Download Practice Workbook You can download ...

## Excel VBA to Break Links – 4 Examples

The following GIF provides an overview. The dataset below contains a sales overview. B5:C11 is linked to a different workbook:  Sales January. ...

## How to Create Cross Join in Excel (3 Simple Ways)

The sample dataset contains the Color and Sizes of a t-shirt. We will Cross Join these tables to get all the available variations of that t-shirt. ...

## How to Write X Bar or Hat in Excel (4 Methods)

The X Bar symbol is used in statistics frequently. Method 1 - Use Symbol Command to Write X Bar in Excel Steps:  Select the cells where ...

## How to Find the Slope of a Regression Line in Excel (3 Easy Ways)

What Is the Slope of a Regression Line? A regression line generally shows the connection between some scatter data points from a dataset. The equation for a ...

## How to Calculate 2 Sigma in Excel – 2 Methods

What Is the 2 Sigma? Sigma refers to the Population Standard Deviation. The formula is: Sigma = √(x-µ)2/N N = Population Size µ = Population Mean ...

## Excel VBA: Web Scraping with Chrome (With Easy Steps)

To demonstrate how to use Excel VBA to scrape data from a website using the Chrome browser, we'll use this article from ExcelDemy as the dataset, and scrape ...

## How to Decrypt Excel File Without Password (2 Easy Ways)

We'll the following Excel sheet to showcase decryption. It contains a sales overview. If I try to edit something in this Excel sheet it shows a Warning ...

## How to Calculate NPV for Monthly Cash Flows with a Formula in Excel (2 Methods)

What Is NPV? NPV, or Net Present Value, is a fundamental component of financial analysis. It helps determine whether a project will be profitable. The NPV ...

## How to Sort Excel Sunburst Chart Order (2 Ideal Examples)

Example 1 - Sort Sunburst Chart Order Alphabetically in Excel Step 1 - Insert Sunburst Chart Select the data range for the chart. Go to the Insert tab ...

## Excel VBA to Translate Language with Formula (With Easy Steps)

Codes for Different Languages Here's a short list of language codes for some languages. You will find the detailed list on the Google Cloud web link. ...

Browsing All Comments By: Mashhura Jahan
1. Hi DOUG KIMZEY,
Thank you for your comment. I am replying on behalf of ExcelDemy. Power Query sometimes shows grayed-out menus if you try to perform mathematical operations in multiple columns at the same time.

• To solve that problem, you can select the column >> select Standard >> select Divide (or whatever operation you want).

• Select the Value according to your preference. Here, I want to divide by 200 because these scores are in 200 >> select OK.

• Now, you can see the values in the column are divided by 200.

• In the formula bar, add your preferred operations >> click on the Tick mark.

• Finally, you will see that you have performed the grayed-out features without using them.

If this tip doesn’t work for you then please share a screenshot of your problem or the Excel file with us so that we can see what is causing the problem and solve it.
Regards,
Mashhura Jahan
ExcelDemy.

2. Hi DANIEL,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. You can apply the following formula to insert the first date in the ‘daily’ section to get the correct weekday name.

`=TEXT(AM11,"DD")`

Use the following formula for the second date.

`=TEXT(K4+1,"DD")`

Also, modify the rest of the dates accordingly to get the correct result. I hope this will help you to solve your problem. Please let us know if you have other queries.
Regards,
Mashhura Jahan
ExcelDemy.

3. Hi PIET,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. You can apply the following code. By using this code, you will be able to double-click on any cell from columns C, G, or L and the entire range (A4:L60) will be sorted based on the column of the selected cell.

``````Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim srt_rng As Range
Dim srt_col As Range
Set srt_rng = Range("A4:L60")
If Not Intersect(Target, srt_rng) Is Nothing Then
If Target.Column = 3 Or Target.Column = 7 Or Target.Column = 12 Then
Set srt_col = Target.EntireColumn
With srt_rng
End With
Cancel = True
Else
Exit Sub
End If
End If
End Sub``````

Regards
Mashhura Jahan
ExcelDemy.

4. Hi OMAR DEL VALLE,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. You can apply the following formula for finding duplicate barcodes.

`=IF(COUNTIF(\$C\$5:C5,C5)>1,"Duplicate",Code128(C5))`

But, here the duplicates are also in Code 128 font to change that follow these steps.
• Right-click on the sheet name >> select View Code.

• Write the following code.

``````Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For row_no = 1 To 100
If Range("D5").Cells(row_no, 1).Value = "Duplicate" Then
Range("D5").Cells(row_no, 1).Font.Name = "Calibri"
Range("D5").Cells(row_no, 1).Font.Size = 11
End If
Next
End Sub``````

• Now, just drag the Fill Handle and you will get your desired result.

Regards
Mashhura Jahan
ExcelDemy.

5. Hi RICHARD O’CONNOR,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. You can apply the following formula to calculate working days until a future date. And then, drag the fill handle down to copy the formula in other cells.

`=NETWORKDAYS.INTL(C5,D5,1,\$G\$5:\$G\$9)`

Here, Cell C5 is start_date, D5 is end_date, 1 refers to Saturday and Sunday as the weekend, and range \$G\$5:\$G\$9 refers to the holidays.
Note: If you skip weekend and holidays arguments in the formula then it will take Saturday and Sunday as weekends and no holidays.
Regards
Mashhura Jahan
ExcelDemy

6. Hi JHON,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. If you insert pictures with text in the same cell like the following dataset, you will be able to search the pictures easily by following the first method from this article.

In the following image, you can see that the results for the router are displayed with pictures.

For this case, you will have to set the Properties for the pictures as Move and size with cells from the Format Graphic task pane.

Regards
Mashhura Jahan
ExcelDemy.

7. Hey NICO,
Thank you for your comment. I am replying on behalf of ExcelDemy. You can use the IF function to check through every error group. The formula will be something like this:

`ID=IFERROR(IF(error group = 1,TEXTJOIN(“, “,TRUE,FILTER(ID array ,error group 1 array=”x”,””)),IF(error group = 2,TEXTJOIN(“, “,TRUE,FILTER(ID array ,error group 2 array=”x”,””)),IF(error group = 10,TEXTJOIN(“, “,TRUE,FILTER(ID array ,error group 10 array=”x”,””)),””))) ,””)`

Regards
Mashhura Jahan
ExcelDemy.

8. Hey EXC,
Thank you for your comment. I am replying on behalf of ExcelDemy. If you are facing this problem with Method 1 then you will have to select the reference cell in the formula from the first row of the range you are selecting. You can see that range C5:D12 is selected in this article. And, cells C5 and D5 were used in the formula. These cells are in the first row of the selected range. You will have to maintain this rule while writing the formula.
I hope this will help you to solve your problem. And, if it doesn’t, let us know in which method you are facing the problem.
Regards
Mashhura Jahan
ExcelDemy.

9. Hi BIANKA,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. If you want to get the distance in KILOMETERS then you can use the following VBA code and the rest of the procedures will be the same.

``````Option Explicit

Public Function Driving_Distance(startlocation As String, destination As String, keyvalue As String)

Dim First_Value As String, Second_Value As String, Last_Value As String, mitHTTP As Object, mitUrl As String

First_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
Second_Value = "&destinations="
Last_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi"

Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")

mitUrl = First_Value & startlocation & Second_Value & destination & Last_Value
mitHTTP.Open "GET", mitUrl, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")
Driving_Distance = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDistance"), 3) * 1.60934, 0)

End Function``````

Regards
Mashhura Jahan
ExcelDemy.

10. Hello ZAIN,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. For Example 5, you will have to keep 2 things in mind.
1. You will have to change the ColumnCount in the ListBox Properties according to your dataset.
2. In the VBA code, you will have to change Row and Column numbers according to your dataset.
Regards
Mashhura,
ExcelDemy.

11. Hi XIONG YANG,
Thanks for your comment. I am replying on behalf of ExcelDemy. You can not add a label directly in the QR Code. But, you can add a label to the cell where you are inserting the QR Code by following these simple steps.
Step-01: Write the following VBA code in the module instead of the code that is provided in this article.
`Function GenerateQR(qrcode_value As String)`
`Dim URL As String`
`Dim My_Cell As Range`
`Set My_Cell = Application.Caller`
`URL = "https://chart.googleapis.com/chart?chs=100x100&&cht=qr&chl=" & qrcode_value`
`On Error Resume Next`
`ActiveSheet.Pictures("My_QR_CODE_" & My_Cell.Address(False, False)).Delete`
`On Error GoTo 0`
`ActiveSheet.Pictures.Insert(URL).Select`
`With Selection.ShapeRange(1)`
`.Name = "My_QR_CODE_" & My_Cell.Address(False, False)`
`.Left = My_Cell.Left + 5`
`.Top = My_Cell.Top + 5`
`End With`
`GenerateQR = ActiveCell.Offset(0, -1).Value`
`End Function`

Now, Save the code and go back to your worksheet.
Step-02: Select the cells where you want the QR Codes >> go to the Home tab >> select Bottom Align.

Step-03: Select the cell where you want the QR Code >> write the following formula in that cell.
`=GenerateQR(B5)`

Next, press Enter and you will get the QR Code with the Label.

Step-04: Finally, get the QR Codes for other data in the same way.

Regards
Mashhura,
ExcelDemy.

12. Hey ERIK NIELSEN,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. If you want to get metadata from subfolders of the specified source folder then you can use the jamsoftware that is used in the “How to Export File Metadata to Excel” section of this article. Let’s see the steps.
• Firstly, download and then copy and paste the FileList.exe file to the folder from where you want to get the metadata by following the steps from the “How to Export File Metadata to Excel” section of this article.

• Secondly, press Windows Key + R.
• Thirdly, write cmd in the Open section.
• Then, select OK.

• After that, write `cd C:\Images`. Here, `C:\Images` is the source folder path.
• Then, press Enter.

• Next, write the command below.
`FileList.exe /USECOLUMNSNAME,FULLPATH,LASTCHANGE,EXTENSION,SIZE, AUTHORS,System.Photo.DateTaken > output.txt`
• Then, press Enter.

• After that, minimize the window.

• Now, you will see a txt file is created in your selected location. It contains the metadata from all the subfolders.

• Next, open an Excel file >> go to Data tab >> select From Text/CSV.

• Afterward, select the txt file >> select Import.

• Now, you will see the metadata are imported to a table.
• Then, select Comma as Delimiter >> select Load.

• Finally, you will see all the metadata from the subfolders is loaded to an Excel sheet.

Regards
Mashhura,
ExcelDemy.

13. Hey MARK,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. For the provided practice workbook, I used the following VBA code and it solved this problem for me.

`Function QR_Generator(qrcodes_values As String)`

` Dim Site_URL As String`
` Dim Cell_Values As Range`

` Set Cell_Values = Application.Caller`
` Site_URL = "https://chart.googleapis.com/chart?chs=100x100&&cht=qr&chl=" & qrcodes_values`
` On Error Resume Next`
` Worksheets("Using User Defined Function").Pictures("Generated_QR_CODES_" & Cell_Values.Address(False, False)).Delete`
` On Error GoTo 0`
` Worksheets("Using User Defined Function").Pictures.Insert(Site_URL).Select`
` With Selection.ShapeRange(1)`
` .Name = "Generated_QR_CODES_" & Cell_Values.Address(False, False)`
` .Left = Cell_Values.Left + 2`
` .Top = Cell_Values.Top + 2`
` End With`
` QR_Generator = ""`

`End Function`

Now, Save the code and go back to your worksheet. Let’s see the steps of using the function.
Step-01: Select the cells where you want the QR Codes. Here, I selected cell range D5:D7 >> write the following formula.
`=QR_Generator(C5)`

Step-02: Press Ctrl + Enter and you will get your desired output.

Regards
Mashhura,
ExcelDemy.

14. Hi TIAGO,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. You can get the correct value by using the COUNTA function instead of the SUM function. Let’s see the steps.
Step-01: Write the following formula in the selected cell.
`=E5/COUNTA(\$C\$5:\$C\$16)`

Step-02: Press Enter to get the Average.

Step-03: Drag the Fill Handle down to copy the formula to the other cells.

Regards
Mashhura,
ExcelDemy.

15. Hello Alun Vaughan-Evans,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. There is a way to keep the formatting and also values. You can follow these steps to do that.
Step-01: Select the range you want to copy and then copy the range by pressing Ctrl + C on your keyboard.

Step-02: Select the cell where you want to paste the range. And then go to Home > Paste > Values & Source Formatting (like the following image).

Finally, you will see that you have copied the values with formatting.

Regards
Mashhura
ExcelDemy

16. Hello JC,
You can follow the steps from the Feature of Eisenhower Matrix Template in Excel section. If it still doesn’t work then check if all cells contain the formula in the Eisenhower Matrix.
And, while making your own Eisenhower Matrix, check if you are applying for the same range as in this article. If not, change your ranges accordingly in the formula.
Lastly, if you are using an older version of Microsoft Excel then press Ctrl + Shift + Enter while entering the formula.
I hope this will help you to solve your problem. If none of these works then you can let us know at [email protected] with your Excel file and problem details. We will try our best to solve your problem.
Regards
Mashhura
ExcelDemy

17. Hi SEM,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. If you want to filter dates older than a number of days from today, you can follow the steps from Method-2. But, instead of the formula used here, write the following formula.
`=C5<(TODAY()-30)`

Here, I wrote the formula for dates older than 30 days from today. You can change the formula according to your preference.
Regards
Mashhura
ExcelDemy

18. Hi Penny,
If you check to see if the cells are blank or not then they will be evaluated as not blank because the cells contain formula. If you need blank cells then you can follow these steps.
Step-01: In a Helper Column use any of the formulas from this article. But instead of an empty string (`""`) write a letter or a special character (`"#"`) in the formula.
`=IF((D5*E5)>2000,D5*E5,”#”)`

Step-02: Copy the values from the Helper Column and Paste them as Values where you want the final output.

Step-03: Press Ctrl + H and the Find and Replace dialog box will appear. Replace # with nothing and you will get your blank cells.

Finally, if you check the cells, then you will see the cells are blank.

I have shared the necessary images for your convenience. I hope this will solve your problem. Please let me know if you have other queries.
Thanks!

19. Hello DQNOK,

20. Hello Frank,
This article contains different methods for different situations. Method 1.3 hides the numbers that are less than Zero. But, if you want to hide zeros only then you can try the following code.

`Option Explicit`
`Sub Hide_Rows_With_Zero()`
`Dim row As Long`
`Dim col As Long`
`Dim qq As Boolean`
`For row = 6 To 14`
` qq = True`
` For col = 2 To 4`
` If Cells(row, col).Value <> 0 Then`
` qq = False`
` Exit For`
` End If`
` Next col`
` Rows(row).Hidden = qq`
`Next row`
`End Sub`

Thanks!

Advanced Excel Exercises with Solutions PDF