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.

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

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

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)

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

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

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.

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.

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.

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

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

Then, press ENTER to get the output.

I hope it helps.
Have a good day.

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!

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.

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.

The table was shifted in “sheet1”

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