User Posts: Md. Sourov Hossain Mithun
0

## How to Highlight Highest Value in Excel (3 Quick Ways)

When we are working in Excel, it will be easy to find the maximum value if we highlight it. This article will guide you on how to highlight the highest value ...

0

## How to Calculate Average, Minimum And Maximum in Excel

Calculating Average, Minimum and Maximum is a common and simple task in Excel. There are several methods to do it. In this article, you will learn how to ...

0

## How to Remove White Space in Excel (6 Easy Ways)

In this article, youâ€™ll learn how to remove white space in excel with some easy and useful methods. While working in Excel, the extra space problem is a pretty ...

0

## How to Count Occurrences Per Day in Excel (4 Quick Ways)

Counting the occurrences per day is a pretty common task while working in Excel. In this article, youâ€™ll learn some useful methods to count occurrences of a ...

0

## How to Remove Text from a Cell in Excel (9 Easy Ways)

There are several methods to remove text from an Excel cell. In this article, Iâ€™ll show 9 methods including VBA to remove text from Excel cells. Just have a ...

0

## How to Count Filled Cells in Excel (5 Quick Ways)

While using Excel we need to count filled cells for different calculations. In this article, Iâ€™m going to show how to count filled cells in Excel with 5 quick ...

0

## Excel Count Number of Cells in Range (6 Easy Ways)

Counting the number of cells is a basic task in MS Excel. There are a lot of methods to do it. In this article, Iâ€™ll show 6 easy methods including VBA to count ...

0

## Excel VLOOKUP to Find Last Value in Column (with Alternatives)

VLOOKUP is a very popular function in Excel which refers to the vertical lookup. We can use the built-in VLOOKUP function or we can even make our own formulas ...

0

## Excel Find Duplicates in Column and Delete Row (4 Quick Ways)

In a short dataset, we can easily remove duplicates manually but if the dataset becomes large then we need some other quick methods to do it. So today Iâ€™ll ...

0

## How to Autofit in Excel (7 Easy Ways)

While using Excel itâ€™s a very common task to use AutoFit option for rows and columns. So Iâ€™ll show in this article how to autofit text in Excel with 7 easy ...

0

## How to Create a Range of Numbers in Excel (3 Easy Methods)

To perform many tasks with a dataset, sometimes we need to create a range of numbers in Excel. So today Iâ€™ll show 3 easy ways how to create a range of numbers ...

0

## How to Delete Specific Rows in Excel (8 Quick Ways)

To delete specific rows in excel is a too common task in our day-to-day life while using Excel. In this article, Iâ€™ll show 8 quick methods to delete specific ...

0

## How to Create a Formula in Excel for Multiple Cells (9 Easy Ways)

Applying a formula for multiple cells is a too common task in Excel. Thatâ€™s why we should have proper knowledge of this operation. So here Iâ€™ll show 9 easy ...

0

## How to Multiply a Column by a Number in Excel (3 Easy Methods)

We often use multiplication in Excel. There are several ways for performing this task. In this article, Iâ€™ll show 3 easy methods to multiply a column by a ...

0

## How to Copy Excel Sheet to Another Sheet with Same Format

In Microsoft Excel, there are several methods to copy anÂ Excel sheet to another sheet with the same format. In this article, Iâ€™ll show 4 quick methods to do ...

Browsing All Comments By: Md. Sourov Hossain Mithun
1. Hello CJ, thanks for your feedback.

Just skip the percentage if it doesn’t get relevant, the formula and procedures are the same.

2. Hello JEMAIMAH OMAKEN, thanks for your feedback.
Visit our site to explore more articles that will work on Excel 2013. As 2013 is not so older version so you will find no major differences.

3. Hello Jane, thanks for your feedback.
Yes, it’s possible, just add the column on the left and apply the commands as I applied.

4. Hello ROY, thanks for your feedback. You have got a nice trick. I hope, it will help others.
But if the reverse order affects the other calculation of any user then maybe the alternative methods are more feasible.

5. Hello ANDY S, thanks for your feedback.

Sub Print_Button_for_DropDown()

Sheets(“Data”).Range(“\$B\$4:\$D\$11”).AutoFilter Field:=2, Criteria1:=Range(“F4”).Value
Sheets(“Data”).Select
Sheets(“Data”).PrintOut

End Sub

Here, I have made a drop-down list in Cell F4 for the locations. Keep this cell in that sheet where the print button is located, that means the active sheet. You can change the reference and range in the codes according to your dataset.

Md. Sourov Hossain Mithun Mar 28, 2023 at 4:25 PM

Hello MISTI,
Thanks for your feedback. I hope you will be glad to know that, we have updated our methods according to related examples. Now it will help you to understand the specific use of every method.

7. Hello WILL,
Thanks for your feedback. There are some reasons that are why you may have faced the problem. You can solve it by following the steps:

1. Maybe your Fill Handle tool is deactivated. To activate it, Click File > Options > Advanced > Enable fill handle and cell drag and drop.

2. The AGGREGATE function can work only for vertical ranges, not for horizontal ranges. So always apply it for vertical ranges and then the Fill Handle should work.

3. The AGGREGATE function is available since 2010, so if you are using an older version of Excel then it won’t work.

If the above solutions fail to rescue you then your issue is quite particular and that is difficult to find out without the file. So if you share your file with us then we hope, we could provide you with the exact solution.

Thanks for your feedback. It’s possible to combine 3 columns using the 2nd method after a little bit modification of the formula.

I added more 4 products in column D and then applied this formula in Cell E5:
`=IF(B5<>"",INDEX(\$B\$5:\$B\$1048576,ROW()-COUNTA(\$B\$5:\$B\$8)),IF(C1<>"",INDEX(\$C\$5:\$C\$1048576,ROW()-ROW(\$E\$8)),INDEX(\$D\$5:\$D\$1048576,ROW()-11)))`

*INDEX(\$D\$5:\$D\$1048576,ROW()-11)
Here, 11 is used based on the length of the second column.

9. Hello ROXY,
Thanks for your feedback. The above three issues are all the most common and possible issues that we have recognized till now. Would you please check whether your worksheet is protected or not? If not then maybe your problem is quite particular and that’s quite difficult to find without the file. So if you would share your file with us then hope, we could find out the reason and give a proper solution.

10. Hello DILEKA,
Thanks for your feedback. There are some possible reasons for why the sort command may not work:
1. Remaining blank rows, cells, or blank columns in the selected range.
3. Mixed Data Type in the Same Column.
4. Selecting multiple worksheets before sorting.

https://www.exceldemy.com/sort-and-filter-in-excel-not-working/#Sort_and_Filter_are_Greyed_out_in_Excel

We hope the above solutions will rescue you. If not, then your problem is quite particular. In that case, if you share your worksheet with us then hope, we will be able to find out the issue and give a proper solution.

11. Hello DANIEL,
Yes, itâ€™s possible to do that using the COUNTA function based on the first column. For that, use the following formula-
`=IF(B5<>"",INDEX(\$B\$5:\$B\$1048576,ROW()-COUNTA(\$B\$5:\$B\$8)),INDEX(\$C\$5:\$C\$1048576,ROW()-COUNTA(\$B\$5:\$B\$8)-4))`

âžĄ `ROW()-COUNTA(\$B\$5:\$B\$8)-4`
Here, 4 is subtracted based on the length of the first column to return 1 as the output of this portion. So for your own dataset, modify the value according to the length of your first column.

12. Hello TAB,
Thanks for your feedback. You can easily do that by using a simple formula.
1. Select the range of dates.
2. Click on the Conditional Formatting command from the Home tab.
3. Then select New Rule.
4. Select “Use a formula to determine which cells to format”.
5. After that, insert the formula in the “Format values where this formula is true box”-
`=AND(D1<=TODAY(),F1<>"Complete")`
6. Choose the Red fill color from the Format command.
7. Finally, hit the OK button.

*To gray out the dates with complete status, use the following rule and Gray fill color:
`=AND(D1<=TODAY(),F1="Complete")`

13. Hello JULIE, thanks for your feedback. Use the below code to fix that-

Sub Worksheet_SelectionChange(ByVal Target As Range)
Static xRow
Cells.Interior.ColorIndex = 0
If xRow “” Then
With Rows(xRow).Interior
.ColorIndex = xlNone
End With
End If
Active_Row = Selection.Row
xRow = Active_Row
With Rows(Active_Row).Interior
.ColorIndex = 7
.Pattern = xlSolid
End With
End Sub

*Or you can use an alternative way with the previous code, after opening the file, click on any cell on the previously highlighted row, and then only the active row will be highlighted.

14. Hello JK,
Thanks for your feedback. Your problem is quite rare and unique. So it’s difficult to detect this type of problem without the user’s Excel file. If you would share your file with us, then hopefully we could detect the issue and could give you the exact solution. But temporarily we are suggesting you use the SUM function within the TRIM function, we are showing you a sample formula:
=TRIM(SUM(C5:C9))

The TRIM function will remove all extra spaces. I hope, it will help you.

15. Hello HERMAN,
Thanks for your feedback. You can follow the articles given below to create a payroll format based on 15 days. The steps and format will be pretty same, hope it will help you.

https://www.exceldemy.com/daily-wages-sheet-format-in-excel/#Step_1_Calculate_Total_Daily_Working_Time_in_Daily_Wages_Sheet_Format_in_Excel

https://www.exceldemy.com/calculate-hours-and-minutes-for-payroll-in-excel/

16. Hello KATHY,
Thanks for your feedback. Would you please check whether your worksheet is protected or not? If not then your problem is quite specific. So if you would share your file with us then hope, we could find out the reason and provide a solution.

17. Hi MICHAEL,

To count the number of items associated with each title (according to to catalog id), use this formula: =COUNTIF(\$B\$2:\$B\$27,B2)

And to sum the total number of uses of each item associated with that same title, use this formula: =SUMIF(\$B\$2:\$B\$27,B2,\$D\$2:\$D\$27)

18. Hello Mat, thanks for your feedback. The problem you mentioned will need a complex formula. You will have to apply a formula like this:
=IF(SUM(–(MAX(AC2:AC12)=AC2:AC12))=1,INDEX(T2:AC12,MATCH(MAX(AC2:AC12),AC2:AC12,0),1)).

19. Hello HOPE, thanks for your feedback. To do that, place Private Sub Workbook_open() in a new module and then call the previous Sub within it. I hope, it will work.

21. Hello TONIA.
Thanks for your feedback. Autofit doesn’t work in a protected sheet, so please check it. If it remains unprotected then your problem is a quite particular type. So if you would share your workbook with us, we hope to find out the problem and give you a possible solution.

22. Hello, HPOTTER.
Thanks for your feedback. We think your problem is very specific which is difficult to identify without the file. So, if you would share your Excel file with us then we could find out the issue and hope, we could give you a solution.

23. You are welcome đź™‚ Glad to know that it helped you.

Advanced Excel Exercises with Solutions PDF