How to transpose reference while auto fill down/right in Excel?
In general, when we drag auto fill handle right from a cell, the column reference will be changed horizontally, on the other hand, the row reference will change vertically while dragging fill handle down. However, in this article, I introduce a formula which can help you transpose the reference while dragging fill handle down and right in Excel as below screenshot shown.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
To transpose the reference while filling right or down, you can apply a simple Transpose function.
Select the cells you want to fill formula, and enter this formula =TRANSPOSE(Sheet2!$B$1:B12) in the formula bar, press Shift + Ctrl + Enter key. See screenshot:
In the formula, B1:B12 is the range you want to transpose, you can change it as you need.
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 11 months agoThis didn't work for me. When I hit shift+cntrl+enter I see the brackets appear around my transpose command but nothing happens in the cells I highlighted.
To post as a guest, your comment is unpublished.· 1 years agoHow to copy a raw [put in mind the row is formula and not a value] and paste it in another sheet as a column ?
Since it's a formula it won't work, Please support on this as it's making me crazy :)
Thanks in advance :)
To post as a guest, your comment is unpublished.· 1 years agoI do not understand clearly about your question, but if you just want to transpose the row formulas into a column formulas, you just need to use Past as Transpose function in right click context menu.
To post as a guest, your comment is unpublished.· 1 years agoThanks a lot!