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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.
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.· 3 months agoTHIS SAVED MY LIFE. THANKS
To post as a guest, your comment is unpublished.· 5 months agoThanks, I was looking for this!
To post as a guest, your comment is unpublished.· 1 years 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.· 2 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.· 2 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.· 2 years agoThanks a lot!