Whenever working with Excel, one recurring activity you might have to complete is deleting an entire row from your worksheet. You can do it manually. But if ...

Excel provides you with a various range of borders that you can add to format the cells according to your preference. You can also apply these borders using ...

Whenever working with Excel you may face a situation where you will need to link data from other workbooks to get a large amount of data. But this data changes ...

A Cross Join combines each row from the first table with each row of the second table. Cross Join returns all the possible combinations of two tables. The ...

The X Bar symbol is used in statistics frequently. Whenever working with Excel, you may face a situation where you will need to write X Bar. The focus of this ...

A Spin Button helps to increase or decrease values easily by clicking on the button easily. But, you can not get negative values directly by using the Spin ...

Whenever working with a set of data a regression line helps you to see the connection between the scatter data points. If you are looking for a way to find the ...

When you divide a number by another number it returns a quotient and a reminder. If you are looking for a way to separate these two in Excel then you are in ...

If you are looking for a way to calculate 2 sigma then you have come to the right place. The main objective of this article is to explain how to calculate 2 ...

If you want to calculate payroll for overtime then this article will be beneficial for you. The focus of this article is to explain how to calculate payroll ...

If you are looking for a way to scrape data from a website, this article will be helpful for you. The focus of this article is to explain how you can use Excel ...

If you are stuck with an Excel file that is password protected and you don’t know the password then this article will be beneficial to you. The objective of ...

There are many ways of calculating Depreciation. Units of Production is one of them. The main objective of this article is to explain how to calculate the ...

If you are looking for a way to calculate NPV for monthly cash flows then you have come to the right place. The focus of this article is to explain how to ...

A data table helps to represent data more clearly in an Excel chart. If you are looking for a way of editing a data table in an chart then you have come to the ...

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

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>> selectDivide(or whatever operation you want).• Select the

Valueaccording to your preference. Here, I want to divide by200because these scores are in200>> selectOK.• Now, you can see the values in the column are divided by

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

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

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.

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, orLand the entire range (A4:L60) will be sorted based on the column of the selected cell.I hope this will help you to solve your problem. Please let us know if you have other queries.

Regards

Mashhura Jahan

ExcelDemy.

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 128font to change that follow these steps.• Right-click on the sheet name >> select

View Code.• Write the following code.

• Now, just drag the

Fill Handleand you will get your desired result.I hope this will help you to solve your problem. Please let us know if you have other queries.

Regards

Mashhura Jahan

ExcelDemy.

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 C5isstart_date,D5isend_date,1refers toSaturdayandSundayas theweekend, and range$G$5:$G$9refers to theholidays.If you skipNote:weekendandholidaysarguments in the formula then it will take Saturday and Sunday as weekends and no holidays.I hope this will help you to solve your problem. Please let us know if you have other queries.

Regards

Mashhura Jahan

ExcelDemy

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

Propertiesfor the pictures asMove and size with cellsfrom theFormat Graphictask pane.I hope this will help you to solve your problem. Please let us know if you have other queries.

Regards

Mashhura Jahan

ExcelDemy.

Hey NICO,

Thank you for your comment. I am replying on behalf of ExcelDemy. You can use the

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

Hey EXC,

Thank you for your comment. I am replying on behalf of ExcelDemy. If you are facing this problem with

Method 1then 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 rangeC5:D12is selected in this article. And, cellsC5andD5were 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.

Hi BIANKA,

Thanks for your comment. I am replying to you on behalf of ExcelDemy. If you want to get the distance in

KILOMETERSthen you can use the followingVBAcode and the rest of the procedures will be the same.Regards

Mashhura Jahan

ExcelDemy.

Hello ZAIN,

Thanks for your comment. I am replying to you on behalf of ExcelDemy. For

Example 5, you will have to keep2things in mind.1. You will have to change the

ColumnCountin theListBoxPropertiesaccording to your dataset.2. In the

VBAcode, you will have to changeRowandColumnnumbers according to your dataset.I hope this will help you to solve your problem. If it fails to solve your problem, then please specify where are you facing the problem.

Regards

Mashhura,

ExcelDemy.

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 theQR Codeby following these simple steps.Step-01:Write the followingVBAcode 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,

Savethe code and go back to your worksheet.Step-02:Select the cells where you want theQR Codes>> go to theHometab >> selectBottom Align.Step-03:Select the cell where you want theQR Code>> write the following formula in that cell.`=GenerateQR(B5)`

Next, press

Enterand you will get theQR Codewith the Label.Step-04:Finally, get theQR Codesfor other data in the same way.I hope this will help you to solve your problem. Please let me know if you have other queries.

Regards

Mashhura,

ExcelDemy.

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

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

cmdin theOpensection.• Then, select

OK.• After that, write

. Here,`cd C:\Images`

is the source folder path.`C:\Images`

• 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

txtfile is created in your selected location. It contains the metadata from all the subfolders.• Next, open an Excel file >> go to

Datatab >> selectFrom Text/CSV.• Afterward, select the

txtfile >> selectImport.• Now, you will see the metadata are imported to a table.

• Then, select

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

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

Regards

Mashhura,

ExcelDemy.

Hey MARK,

Thanks for your comment. I am replying to you on behalf of ExcelDemy. For the provided practice workbook, I used the following

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

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

Step-02:PressCtrl + Enterand you will get your desired output.I hope this will help you to solve your problem. Please let me know if you have other queries.

Regards

Mashhura,

ExcelDemy.

Hi TIAGO,

Thanks for your comment. I am replying to you on behalf of ExcelDemy. You can get the correct value by using the

COUNTAfunction instead of theSUMfunction. Let’s see the steps.Step-01:Write the following formula in the selected cell.`=E5/COUNTA($C$5:$C$16)`

Step-02:PressEnterto get theAverage.Step-03:Drag theFill Handledown to copy the formula to the other cells.I hope this will help you to solve your problem. Please let me know if you have other queries.

Regards

Mashhura,

ExcelDemy.

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 pressingCtrl + Con your keyboard.Step-02:Select the cell where you want to paste the range. And then go toHome > Paste > Values & Source Formatting(like the following image).Finally, you will see that you have copied the values with formatting.

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

Regards

Mashhura

ExcelDemy

Hello JC,

Thanks for your comment. I am replying to you on behalf of ExcelDemy. The downloaded file works for me.

You can follow the steps from the Feature of

Eisenhower Matrix Template in Excelsection. If it still doesn’t work then check if all cells contain the formula in theEisenhower 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 Excelthen pressCtrl + Shift + Enterwhile 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

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 fromMethod-2. But, instead of the formula used here, write the following formula.`=C5<(TODAY()-30)`

Here, I wrote the formula for dates older than

30days from today. You can change the formula according to your preference.I hope this will help you to solve your problem. Please let me know if you have other queries.

Regards

Mashhura

ExcelDemy

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 aHelper Columnuse 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 theHelper Columnand Paste them asValueswhere you want the final output.Step-03:PressCtrl+Hand theFind and Replacedialog 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!

Hello DQNOK,

Thank you for your feedback.

Hello Frank,

This article contains different methods for different situations.

Method 1.3hides 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`

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

Thanks!