Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

User Posts: Fahim Shahriyar Dipto
0
How to Link Textbox to Multiple Cells in Excel (4 Easy Ways)
0

A textbox is a portable tool that is used to cover important text or information in Excel. You can drag the text box anywhere in the sheet. Also, it is ...

0
How to Do Linear Interpolation Excel VBA (with Easy Steps)
0

Sometimes, you may deal with a dataset that follows a linear regression. You want to know an unknown value from a known value of that dataset. In this case, ...

0
How to Use Wildcard with If Statement in Excel (5 Methods)
0

In Microsoft Excel, the IF function is used to check if a condition is met, and then the defined statements will be shown based on the given situation. On the ...

0
How to Show Labels Outside in Excel Doughnut Chart (2 Methods)
0

In Microsoft Excel, you can create a chart with your dataset. Different types of charts are available in Excel. The doughnut chart has a hole in the center of ...

0
How to Use Conditional Formatting to Compare Dates in Excel
0

Conditional formatting is a fundamental aspect of using Microsoft Excel. This enables us to locate certain values within a huge or random set of data. With ...

0
How to Resize Chart Plot Area Using VBA in Excel
0

You can create a chart through Excel's built-in chart features. While dragging the chart to any of the sides you can change the size and shape of the chart. ...

0
How to Turn Off AutoComplete in Excel (3 Easy Approaches)
0

Excel offers duplicate values from related cells when we start entering data into a cell, and we can take those identical values by pressing Enter at that time ...

0
Artificial Intelligence in Excel (4 Examples)
0

Excel is a very powerful software for calculating and evaluating data. You can input your data and calculate the results along with different functions in ...

0
How to Add Standard Deviation Error Bars in Excel
0

A graph's error bar is a line drawn through a point that is parallel to any of the axis that depicts the degree of uncertainty or variance in the point's ...

0
How to Create a Catalog in Excel (with Easy Steps)
0

The catalog means the record of the lists of products of a certain company, institution, or course. It is the overview of the list of products or objects that ...

0
[Fixed] QUADF Not Working in Excel
0

Sometimes, you may need to estimate Excel's proper or improper integrals. There is no built-in software in Excel for computing the integrals. However, you can ...

0
How to Count Alphabet in Excel Sheet (5 Easy Methods)
0

Whenever you work with a dataset that contains some sentences or words, you may need to count the alphabet in the Excel sheet. Excel shines here, providing ...

0
How to Make a Wind Rose in Excel (2 Suitable Methods)
0

The wind rose is a graphical interpretation of the wind direction and speed in a certain place. It represents the path of the wind along with its speed. This ...

0
How to Create Tooltip in Excel Chart (with Simple Steps)
0

Tooltip is a message or note that appears when the mouse pointer rests on an icon or any graphical interface. In Microsoft Excel, you can put a chart tooltip ...

1
How to Join Names in Excel (7 Quick Methods)
0

Sometimes, you may need to join names in Excel. Suppose you have a dataset where the first and last names have existed separately. You need to join the names ...

Browsing All Comments By: Fahim Shahriyar Dipto
  1. Reply
    Fahim Shahriyar Dipto Dec 1, 2022 at 2:28 PM

    Hello Ken K,
    Thanks for your feedback. The Print Title options including Rows to Repeat are always disabled in the Print Preview’s page setup. Display the spreadsheet in Normal view, go to the Page Layout tab and click on Print Titles. You may check whether your worksheet is protected or not. If you are facing the same problem then your problem may be quite exceptional. So, we can not solve the problem without your Excel file. You can share your Excel file via [email protected].
    Hopefully, we will be able to solve your problem. Keep supporting us.
    Regards,
    Fahim Shahriyar Dipto
    Excel & VBA Content Developer.

  2. Reply
    Fahim Shahriyar Dipto Jan 11, 2023 at 2:31 PM

    Hello Anwer,
    Thanks for your comment. It is the most common factor when you download a .xlsnm, the code doesn’t work. So, to avoid this consequence follow the steps stated below.
    Firstly, download the .xlsm file from the article and right-click on the file. Select Properties.

    Then, check the Unblock box and hit OK.

    Hopefully, this method solves your issue. If not then change the directory of the file and Rename it at your preference. then the VBA code of the source file will run in the existing worksheet.
    Again, if you are facing any obstacle then mail the Excel file to the address.
    Regards,
    Fahim Shahriyar Dipto
    Excel & VBA Content Developer

  3. Reply
    Fahim Shahriyar Dipto Jan 11, 2023 at 2:00 PM

    Hello Terry,
    It’s really nice to hear from you. In your query, you wanted to know about changing the plot area where the chart area will be the same. The VBA code needs to be modified a bit. I have attached the code below.
    Sub Resize_Chart_Plot_Area()
    Dim ch1 As Chart, plot_height As Double, plot_width As Double
    Set ch1 = ActiveChart
    chart_height = ch1.PlotArea.Height: chart_width = ch1.PlotArea.Width
    chart_height = ch1.PlotArea.Height: chart_width = ch1.PlotArea.Width
    ch1.PlotArea.Height = 150: ch1.PlotArea.Width = 400
    End Sub
    Run the code with the F5 key and it will change the plot area without changing the plot area.
    Have a great day.
    Regards,
    Fahim Shahriyar Dipto
    Excel & VBA Content Developer.

  4. Reply
    Fahim Shahriyar Dipto Dec 28, 2022 at 11:35 PM

    Hello Jim,
    Thanks for commenting. If I am not wrong, you want to sort dates that don’t have the dollar like the dataset below.

    Select the entire data range then navigate the Home tab >> choose Sort & Filter from #diting group >> pick Filter.

    Choose the Filter dropdown and uncheck the blanks from the column.

    Finally, the dates are sorted with the dollar.

    Regards
    Fahim Shahriyar Dipto
    Excel & VBA Content Developer

  5. Reply
    Fahim Shahriyar Dipto Dec 28, 2022 at 3:30 PM

    Hello Javate,
    Thanks for commenting. Your query is similar to reverse names. You can do that in one column. For this, go to cell C5 and insert the following formula.
    =MID(B5&","&B5,SEARCH(".",B5)+1,LEN(B5)+1)
    You have to use the MID, SEARCH, and LEN functions combinedly. You will get the output like the image below after pressing ENTER.

    You can follow the Reverse Names in Excel article to get the proper idea also.

  6. Reply
    Fahim Shahriyar Dipto Dec 28, 2022 at 2:16 PM

    Hi Nathan,
    Thanks for your valuable feedback. If I am not wrong, you want to count the characters without the symbols. For this factor, we have taken a sentence that contains commas. To remove the commas and count the characters insert the following formula in cell C5.
    =LEN(SUBSTITUTE(B5," ",""))-LEN(B5)+LEN(SUBSTITUTE(B5,",",""))
    Press ENTER and get the following output.

    It counts the total characters without commas.

  7. Reply
    Fahim Shahriyar Dipto Dec 20, 2022 at 5:27 PM

    Hi Quang,
    Thanks for your feedback. You are right on this occasion. The formula will be =(D15+E15)*F12 in G16. unfortunately, we have made a mistake here. Thanks to you that you corrected us. We have updated the article.
    Best wishes to you.
    Regards
    Fahim Shahriyar Dipto
    Excel & Content Developer.

  8. Reply
    Fahim Shahriyar Dipto Dec 20, 2022 at 5:21 PM

    Hi Anna,
    Thanks for commenting. There are 13 commands under the Interior application. When you put a dot (.) after the Interior application you will find the commands. there are Color, Colorindex, Pattern, ThemeColor, etc in the command section. But all the commands have the built-in color code that’s why you won’t be able to choose a specific color using this code. but while you working with blanks you can insert the RGB command and the Custom Color Code. In our case, it is not possible as we have to maintain the same color for the duplicates. Hope, you understand our answer.
    Regards,
    Fahim Shahriyar Dipto
    Excel & VBA Content Developer.

  9. Reply
    Fahim Shahriyar Dipto Dec 20, 2022 at 3:51 PM

    Hello Sumeet,
    Thanks for commenting. You can download the Excel file and also the Pdf file of all the formulas Cheat Sheet. For your betterment, we attached the Excel file and Pdf File links here also.

  10. Reply
    Fahim Shahriyar Dipto Dec 8, 2022 at 1:44 PM

    Hello Espen,
    First of all, thanks for commenting. it’s unfortunate that we don’t explain the line-shifting issues in this article. If I am not wrong, I think you are talking about the case like the image below.

    For resolving the issue you can follow the procedure described in the method of Converting Word to Excel without splitting cells.You will get the result like the image below.

    Hopefully, you will understand the steps mentioned in the link and solve your issue.
    Have a nice day!
    Regards
    Fahim Shahriyar Dipto
    Excel & VBA Content Developer

  11. Reply
    Fahim Shahriyar Dipto Dec 8, 2022 at 12:12 PM

    Hello Maurice,
    Thanks for your feedback. In your query, as you wanted to create the combination for 3 columns and five rows, you have to change the VBA code variables to X1, X2, and X3. Also, you need to change the row range. Don’t worry, We have added the code for your betterment. Follow the code and your work will be done.
    Sub CombinationsFor3Columns()
    Dim X1, X2, X3 As Range
    Dim RG As Range
    Dim xStr As String
    Dim FN1, FN2, FN3, FN4 As Integer
    Dim SV1, SV2, SV3, SV4 As String
    Set X1 = Range("B5:B9")
    Set X2 = Range("C5:C9")
    Set X3 = Range("D5:D9")
    xStr = "-"
    Set RG = Range("D5")
    For FN1 = 1 To X1.Count
    SV1 = X1.Item(FN1).Text
    For FN2 = 1 To X2.Count
    SV2 = X2.Item(FN2).Text
    For FN3 = 1 To X3.Count
    SV3 = X3.Item(FN3).Text
    RG.Value = SV1 & xStr & SV2 & xStr & SV3
    Set RG = RG.Offset(1, 0)
    Next
    Next
    Next
    End Sub
    Hope the above code solves your problem. Keep supporting us.
    Regards
    Fahim Shahriyar Dipto
    Excel & VBA Content Developer

  12. Reply
    Fahim Shahriyar Dipto Dec 1, 2022 at 10:04 AM

    Hello Fred,
    Thanks for your appreciation. You can also do it for 4 columns also. All you need to change is the variable, integer and string. Without these changes, all the other code will be the same as before. We also provide the VBA code for the 4 columns below.
    Sub CombinationsFor4Columns()
    Dim X1, X2, X3, X4 As Range
    Dim RG As Range
    Dim xStr As String
    Dim FN1, FN2, FN3, FN4 As Integer
    Dim SV1, SV2, SV3, SV4 As String
    Set X1 = Range("B5:B7")
    Set X2 = Range("C5:C7")
    Set X3 = Range("D5:D7")
    Set X4 = Range("E5:E7")
    xStr = "-"
    Set RG = Range("H5")
    For FN1 = 1 To X1.Count
    SV1 = X1.Item(FN1).Text
    For FN2 = 1 To X2.Count
    SV2 = X2.Item(FN2).Text
    For FN3 = 1 To X3.Count
    SV3 = X3.Item(FN3).Text
    For FN4 = 1 To X4.Count
    SV4 = X4.Item(FN4).Text
    RG.Value = SV1 & xStr & SV2 & xStr & SV3 & xStr & SV4
    Set RG = RG.Offset(1, 0)
    Next
    Next
    Next
    Next
    End Sub
    Hope the above code solves your problem. Keep supporting us.
    Regards
    Fahim Shahriyar Dipto
    Excel & VBA Content Developer

  13. Reply
    Fahim Shahriyar Dipto Nov 30, 2022 at 4:29 PM

    Hello Euliin,
    Thanks for commenting on our website. You can highlight a cell containing any random value with Conditional Formatting. Follow the steps.
    Firstly, select the entire row that you want to be highlighted. Then go to the Conditional Formatting and choose New Rule.

    Now, New Formatting Rule dialog box appears. Choose Use a formula which cells to format. Write the formula in the box in the Format values where this formula is true.
    =OR(ISTEXT(B5),ISNUMBER(B5))
    And click on Format.

    Now, Format Cells appears. Pick a color and hit OK.

    Now, again hit OK in the New Formatting Rule box.

    Finally, as you see you can highlight the row.

  14. Reply
    Fahim Shahriyar Dipto Nov 30, 2022 at 3:58 PM

    Hello Mr. Wilson,
    Thanks for your valuable comment. If you want to paste only the value you can do it through Paste Special feature.
    Firstly, create a dataset with the RANDBETWEEN function.

    Whenever you press anything opening the file the numbers refresh automatically.

    But, after copying the column with the CTRL + C key, right-click on the cell and choose Paste Special.

    Paste Special dialog box appears. Check the Valuebox there and hit OK.

    Finally, you have pasted the values without dragging anything.

    I hope you got the solution. keep supporting us.
    Regards,
    Fahim Shahriyar Dipto
    Excel & VBA content Developer.

  15. Reply
    Fahim Shahriyar Dipto Nov 30, 2022 at 3:38 PM

    Hello Siti,
    First of all thanks for your feedback. You can follow the below article to solve your problem. Go through it and you will get the solution hopefully.

    You can also use the following VBA code to merge multiple sheets into one and into separate sheets. But you have to put all the files into a specific folder and paste the folder path into the code. (mentioned in the article).
    Sub ConsolidateFiles()
    Dim FileList, CurrentF As Variant
    Dim nFiles, nSheets As Integer
    Dim cSh As Worksheet
    Dim cWB, sWB As Workbook
    FileList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (.xls;.xlsx;.xlsm),.xls;.xlsx;.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
    If (vbBoolean <> VarType(FileList)) Then
    If (UBound(FileList) > 0) Then
    nFiles = 0
    nSheets = 0
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set cWB = ActiveWorkbook
    For Each CurrentF In FileList
    nFiles = nFiles + 1
    Set sWB = Workbooks.Open(Filename:=CurrentF)
    For Each cSh In sWB.Sheets
    nSheets = nSheets + 1
    cSh.Copy after:=cWB.Sheets(cWB.Sheets.Count)
    Next
    sWB.Close SaveChanges:=False
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End If
    End If
    End Sub
    I hope you can get what you want after using the code. thanks again and keep supporting us.
    Regards,
    Fahim Shahriyar Dipto
    Excel & VBA content Developer.

  16. Reply
    Fahim Shahriyar Dipto Nov 24, 2022 at 2:00 PM

    Hello Edho,
    Thanks for your feedback. Though the question is quite unclear to us but we have tried another code for your working purpose.
    Sub X()
    Set DataSet = Range("B4:D13")
    Interval = 3
    N = 4
    FormulaColumn = 3
    R = DataSet.Rows.Count
    If R Mod Interval <> 0 Then
    Ending = R + (Int(R / Interval) * N)
    Else
    Ending = R + ((Int(R / Interval) - 1) * N)
    End If
    For i = (Interval + 1) To Ending Step Interval
    For j = 1 To N
    DataSet.Cells(i, FormulaColumn).EntireRow.Insert
    Next j
    i = i + N
    Next I
    For i = 1 To Ending
    If DataSet.Cells(i, FormulaColumn) = "" Then
    DataSet.Cells(i - 1, FormulaColumn).Copy
    DataSet.Cells(i, FormulaColumn).PasteSpecial xlPasteFormulas
    End If
    Next I
    Application.CutCopyMode = False
    End Sub

    Look at the code image carefully. We have changed the row number,N=4. It will insert 4 rows in after the active cell. We have put the FormulaColumn number 3 as our formula is existed in column 3. You can change the FormulaColumn number at your preference and the formula will be copied in your inserted rows for the exact formula column.

  17. Reply
    Fahim Shahriyar Dipto Nov 23, 2022 at 11:16 AM

    Hello Avery,
    First of all, we would like to thank you for your appreciation. Again, thanks for taking the time to leave a comment about a problem that you’re facing. In our Excel file, the formula is working smoothly. You can follow the step-wise procedure thoroughly. If the problem in your file remains the same, then send the file to us. It will be easier for us to pinpoint the problem. We, the Exceldemy team, are always ready to solve users’ issues.
    Regards,
    Fahim Shahriyar Dipto
    Excel and VBA Content Developer

  18. Reply
    Fahim Shahriyar Dipto Nov 23, 2022 at 10:32 AM

    Hello, Mrs. Thomas. Thanks for your feedback. You can easily show the day in the text by using the TEXT function. Follow the steps for better visualization.
    Firstly, go to cell E5 and insert the method.
    =TEXT(D5,"dddd")
    It will make the day in full form of the text.

    Then press ENTER and drag it down for other cells with the Fill Handle tool.

    Finally, you got your result.

    For displaying the month in text go to the cell F5 and write up the formula.
    =TEXT(D5,"mmmm")

    Drag down it and get the full result for the dates.

  19. Reply
    Fahim Shahriyar Dipto Nov 17, 2022 at 12:37 PM

    Hello Mr Martin. Thanks for your valuable feedback. We are grateful to you for informing us about the factor. According to your feedback, we cross-checked the issue. Unfortunately, the x and y values are switched unexpectedly somehow. we have updated the article. Thanks again.
    Regards,
    Fahim Shahriyar Dipto
    Excel and VBA Content Developer.

  20. Reply
    Fahim Shahriyar Dipto Nov 17, 2022 at 10:08 AM

    Hello Nicol,
    Here’s the solution. You can subtract two dates and can find out the days remaining in your hand. I have attached the step-by-step procedure for better understanding.
    Firstly, go to cell E5 and insert the formula.
    =C5-D5

    Secondly, press ENTER and drag down the Fill Handle tool.

    Finally, you will get the result like the image below.

  21. Reply
    Fahim Shahriyar Dipto Nov 7, 2022 at 5:13 PM

    Hello Mr. Clark. Thanks for your valuable comment. In your query, you wanted to know about category-wise idea sorting. If I misunderstood your problem then please let me know about this. I am adding the sorted image here. Then I have described how I have done that.

    Firstly, you need to set a list of ideas according to your category just like the image below.

    Then in cell B5 write up the following formula.
    =IF($B$4=J5,I5,"")

    Similarly, you have to put the same formula for the other columns but need to set the cell reference for the corresponding cells.
    Then drag down the Fill handle Tool for C5, D5, E5 ad F5 cells to get the same formula.

    Finally, you will get the result.

    Now, if you want to add any category-wise idea then it will automatically be sorted in your dataset list. See the image for a better clarification.

  22. Reply
    Fahim Shahriyar Dipto Nov 7, 2022 at 2:57 PM

    First of all, Thanks Mr. Smith for your valuable feedback. its means a lot to us. For your query, we have attached the steps with the image. Hopefully, you will understand this.
    To merge numerous Excel files into one, first, convert the XLSX files to CSV files. Navigate to the File Tab. Select the Save As option and then click the menu icon next to the Save Option. Then, in the list, you will find many file formats; explore them and select CSV UTF-8 (Comma delimited) from the list of other formats. Additionally, rename the file from Sales of January to 1-Sales of January (the serial numbers preceding the file name will arrange the files serially according to which serial we want to merge them). Finally, select the Save option.

    Then we’ll have a new file with the new format, 1-Sales of January.csv.

    To begin, Copy the path to the folder Multiple Files where we have saved our Excel files in CSV format to be combined.

    When you press the WINDOWS key + R, the Run window will appear. To launch the command prompt, type cmd in the Open box and hit OK.

    We’ve opened the CMD, or Command Prompt, as you can see.

    Enter cd and a space after it. To paste the copied path of our Multiple Files folder, press CTRL+V or right-click on your mouse.

    After clicking ENTER, you will be sent to the directory specified in the preceding line.
    Now, in the new line, put copy *.csv Consolidate.csv (where Merged Worksheet is our new filename), followed by

    After clicking ENTER, the following command lines will be created automatically, where we can see the two file names that we wish to combine.

    After you close the CMD window, navigate to the Multiple Files folder, where you will find the new merged file titled Consolidate.csv.

    When we view the Consolidate.csv file, we notice the Sales Record of January heading first, followed by the equivalent numbers from the February file.
    Hopefully, we think the solution is helpful for you

  23. Reply
    Fahim Shahriyar Dipto Oct 5, 2022 at 10:05 PM

    Thanks for your appreciation. It means a lot. You can explore our website to find out extraordinary techniques in Excel.

ExcelDemy
Logo