User Posts: Akib
0
How to Insert Scrolling Text Box in Excel (2 Ideal Examples)
0

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes we need to ...

0
How to Add Prefix 0 in Excel (7 Easy Methods)
0

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes we need to put ...

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

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Though Excel is not a ...

0
How to Increase Character Limit in Excel Cell (2 Easy Methods)
0

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes, we set ...

0
How to Convert Binary to Decimal in Excel (2 Easy Methods)
0

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will ...

0
How to Create Dave Ramsey Debt Snowball Spreadsheet in Excel
0

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Dave Ramsey, a prominent ...

0
How to Exclude Data Points from Trendline in Excel
0

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will ...

0
How to Translate Multiple Cells in Excel (2 Easy Methods)
0

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will ...

0
[Fixed!]: Unable to Enable Macros in Excel (5 Quick Solutions)
0

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Visual Basic Application ...

0
[Solved:] Excel Not Filtering Entire Column (3 Quick Fixes)
0

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes we stumble in ...

0
How to Automate Copy and Paste from Website to Excel
0

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will ...

0
How to Insert Merge Fields in Word from Excel
0

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will ...

0
How to Merge Multiple CSV Files into One Workbook in Excel
0

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. This article will show ...

0
TDS Deduction on Salary Calculation in Excel Format
0

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will ...

0
How to Create Quarterly Comparison Chart in Excel
0

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I am ...

Browsing All Comments By: Akib
  1. Hi HAZEL,
    I am assuming that you want to transfer Gender and Language from one column to another without changing the row position.
    In that case, you can insert a new column to the left of the Gender and Language column.
    To do so,
    ● Select the Gender column.

    avbb

    ● Then, right-click your mouse to bring the context menu.
    ● After that, select Insert.

    b

    ● Excel will add a new column and transfer the Gender and Language column.\

    c

    There is another solution to this problem. You can use the OFFSET function. The advantage of this function is that you can transfer any portion of the columns to anywhere in the Excel sheet.

    Suppose, I want to transfer B8:C10 to E8:F10. To do so,
    ● Go to E8 and write down the following formula
    =OFFSET(B4,4,0,3,2)

    d
    ● Then, press ENTER. Excel will move the cells.

    e
    ● If you use earlier versions of Excel, you have to select E8:F10, then write down the formula and finally press CTRL+SHIFT+ENTER, since the OFFSET function is an array function.

    And lastly, you can copy the cells and paste them to transfer columns.
    I hope it helps. If it does not satisfy you, please let us know.
    Thank you. Have a great day.

  2. Hi LILIA,
    I hope you are doing well.
    Please use the conditions as per the image and see if it works.
    1\
    The range $E$4:$E$100 will allow you to have a list of 97 items. So, this can be used as a dynamic drop-down
    Thank you.

  3. Hi KALI,
    Normally when we write down a formula that has references from multiple sheets, the sheet name appears first. That’s why “Data range” appears. You can check the final image of step 3 to get the final formula.

    ‘Data range’!’Data range’!C5:G19 is corrected now. Thank you for your observation.

    ‘Data range’!$B$5:$B$10 refers to the range B5:B10. The selected range starts from the 5th row to the 10th row of column B.

    Thanks again. Have a good day.

  4. Hi DAVID,
    I hope you are doing well.
    In Excel, you cannot do so as far as I know. However, you can make the fill color same as the background color to technically avoid printing the row.
    And you can also hide specific rows and columns to not print them.
    Thank you. Have a good day.

  5. Hi HAIVH
    Let me first clear up your confusion.
    • Normally, when you pay a certain amount, you have cash outflow. Hence the amount you pay becomes negative.
    • On the other hand, when you receive a certain amount, you have cash inflow. Hence the amount you get becomes positive.
    Coming to your problem,
    • PMT function is used to calculate the monthly payment for a loan. This problem is not a case of loan payment. So, I think the PMT function is not suitable to be used here.

  6. Hello STEEN and DAV,
    I have checked the methods on several laptops. They are working well here.
    For your convenience, I have added a new Excel file with the VBA code.
    Please download it and check if the methods are working.
    Thank you. Have a good day.

  7. Hi DOMINIC,
    Yes, it’s possible to join non-consecutive cells using the TEXTJOIN Function in Excel.
    Let me show you how you can do it.
    Let’s assume this is your dataset.

    image-1

    Then, in A8, apply the following formula
    =TEXTJOIN(",",TRUE,A4:A6,A1)

    image-2

    The delimiter is a comma (,)
    TRUE indicates that you are going to ignore empty values.

    Then, press ENTER to get the output.

    image-3

    I hope it helps.
    Have a good day.

  8. Hi SANKARSHAN
    Generally, you can set one CRITERIA when you apply only COUNTIF Function.
    However, if you use a formula that has multiple functions, you can set multiple criteria.
    Let me show you an example.
    ● Suppose you have a set of fruits. You want to count the instances when the fruit is either Mango or Apple.

    image-1

    ● Go to D5 and write down the following formula
    =COUNTIF(B4:B15,B4)+COUNTIF(B4:B15,B5)

    image-2

    Here, Excel will count the instances when the criteria is Mango or Apple from the same range B4:B15.
    ● Now, press ENTER to get the output.

    imgae-3

    I hope it helps.
    You can also check the following articles to internalize the concept.
    COUNTIF with Multiple Criteria in Different Columns in Excel
    COUNTIFS with Multiple Criteria (5 Easy Methods)

    Thank you.

  9. Hi Casey,
    You may not get the desired output if you download the excel file attached to this article and perform operations.

    1. You must download the barcode font. The link is in the 1st step of this article
    2. Also, code 128 has 106 distinct characters. Πis not available in code 128.
    Maybe these are the 2 issues that need to be resolved.
    However, if these don’t satisfy you, please let us know.
    Have a great day!

  10. Hi Marko
    I hope you are doing well.
    After going through your problem, I will give you 2 possible solutions.
    Solution 1:
    This will require you to insert the number after “WA11” within parentheses.
    ● Add the parentheses.

    1

    ● Then, write down the following formula in B2.
    =MID(A1,SEARCH("(",A1)+1, SEARCH(")",A1)-SEARCH("(",A1)-1)+0

    2

    ● Then, press ENTER. Excel will extract the number inside the parentheses.

    3

    ● Then, AutoFill up to B3.

    4

    ● Finally, add the numbers.

    5

    Solution 2:
    ● This will require you to have a helping column with the numbers with “WA11”. But this is a bit tedious if you have a lot of data.

    6

    ● Now, add the numbers.

    7

    Hope this helps. Thank you.

  11. Hi David
    As far as I understand the case, you want to apply a filter to a row but keep the numbers in the cell permanent.
    Unfortunately, when Excel executes a filter, it hides the entire row. If any cell contains numbers, excel will hide them too. However, as this article explains, you can maintain the serial when applying a filter.
    In case I have misunderstood your requirement, please mail us the excel file at [email protected] and explain the problem a bit more.
    Thank you.

  12. Hi G0DWIN,
    Thank you for your kind words!

  13. Hi KRISB,
    I hope you are doing well.
    If you copy the table and paste it somewhere else, then Excel will not adjust cell references according to the move.
    The formula should work fine if you cut the table and paste it into another sheet.
    For instance, I have cut the table and pasted it into “sheet1”. Then Excel automatically modifies the formula. See the image for better clarification.
    1
    The table was shifted in “sheet1”
    2

  14. Hi NGÂN,
    The solution you want will require a combination of some functions like TODAY, COUNTIF, VLOOKUP, etc. Here is a post on our website that will help you.
    https://www.exceldemy.com/stock-ageing-analysis-formula-in-excel/
    We have several posts related to this topic too.
    https://www.exceldemy.com/make-inventory-aging-report-in-excel/
    https://www.exceldemy.com/excel-ageing-formula-for-30-60-90-days/
    https://www.exceldemy.com/aging-of-accounts-receivable-in-excel/
    I hope these articles will help get your job done. If not, please remember that we are just a text away!!
    Thank you. Have a good day.

  15. Thank you for your kind words. Have a good day!

  16. Hi JUNED SHAIKH
    I hope you are doing well. The term “Multi Validation” is a bit confusing. However, I would like to provide you with some information regarding Data Validation in Excel.
    1. You cannot have multiple columns or rows as your data source for data validation. The data source must be a single row or column.
    2. You can apply data validation to one specific cell once at a time. If you want to reapply data validation to one particular cell, you have to remove the previous data validation.
    3. You can apply data validation to the entire sheet keeping the procedure congruent with the above statements.
    I hope it helps. Have a great day.

ExcelDemy
Logo