•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Issue with transpose function when switching from sheets to excel
Our take
Transitioning from Google Sheets to Excel can present a learning curve, especially with functions like TRANSPOSE. Your formula, `=TRANSPOSE(FILTER($IJ$60:$IJ$503, $IH$60:$IH$503=AI64))`, is valid, but Excel may behave differently, producing zeroes due to cell formatting or other nuances. Additionally, Excel's handling of merged cells can lead to #SPILL errors, requiring unmerged cells for the output. To resolve these issues, consider checking your data range and formatting, and remember that Excel often requires adjustments in how functions are
I'm new to excel and am finding out there are MANY small differences with sheets. I have a table that uses the transpose function just fine in sheets, but in excel it produces a bunch of zeroes.
the formula I'm using is:
=transpose(filter($IJ$60:$IJ$503,$IH$60:$IH$503=AI64))
Do I need to add or remove anything to make it work the same as sheets?
Also, (this one's minor) it worked fine with merged cells for a cleaner look in sheets, but excel made me unmerge the cells where the results go. It kept giving me a #spill error with the merged cells. Any ideas on this?
Thanks for the help
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Transfer Data from One Workbook to Another (SPILL Issue)Hey! I’m working on an Excel setup where the workbook on the left is my source and the one on the right is my destination. My goal is to automatically transfer the “Given IDs” from the source to the destination based on their assigned codes. I fixed the one‑to‑many lookup issue, but now I have another problem — when I type an existing code again in the destination (highlighted in green), it shows #REF! instead of its IDs. Is there any way to fix this without losing the automatic transfer I already have? + A user suggested to the COUNTIF into a MOD. I'm not sure how to operate this so I hope anyone could teach me. + "FILTER('[book 2.xlsx]Sheet2'!B:B,ISNUMBER(XMATCH('[book 2.xlsx]Sheet2'!A:A,B:B)))" = this also helps but I also encounter the aforementioned problem (in my real workbooks, there will be times when I need to enter the same codes directly in the destination, and I don’t want to keep going back to the source workbook to re‑enter them all over again.) Thank you so much in advance! https://preview.redd.it/cb9jv0h57ryg1.png?width=1314&format=png&auto=webp&s=b26b1b2886d53f8fe94b76735e4de21ea9a3b866 submitted by /u/jeankrstein [link] [comments]
- I want to transpose 78 equal columns of data into one long stack with a space in between.I have a set of data that looks like this (78 sets): https://preview.redd.it/he8sfifg7ypg1.png?width=1851&format=png&auto=webp&s=20ddb7e3a59df52c0c21358fc6ec010142c49724 I would like it to look like this: https://preview.redd.it/c40z0rfj7ypg1.png?width=240&format=png&auto=webp&s=39856a57079147b8d345268a1d0959908b718655 I have tried variations of INDEX and OFFSET but can't seem to get it to work smoothly and automatically for 78 sets of this data. I am currently using Google Sheets (for ease of portability) but I also have Excel so could switch it across if the functionality is not there in Google. Any help would be greatly appreciated. submitted by /u/boring-goldfish [link] [comments]
- How to prevent !SPILL error?Hi all, I'm making a spreadsheet and I keep getting a SPILL error in one of my boxes. I'm taking some of my data from one tab on another excel to calculate a % change but each time I do it, the tab spills over one extra cell for no apparent reason? Annoyingly it is just returning the same value also so there's no reason I can see for it to spill? As I move through the months the amount of cells it spills into increases. It's infuriating, can anybody help at all? Thanks https://preview.redd.it/rplx7e6i79vg1.png?width=1657&format=png&auto=webp&s=eea0d48c6edecb7a8699aa4e057ed47fc0bbca99 submitted by /u/Ok-Let-5155 [link] [comments]
- Using the #spill error as the resultI'm trying to use the filter function to generate some tables Now my one table has multiple rows that correlate to just one row in the Table I want to generate. If there is only one row that fulfills the criteria, I want the result to be one thing, and if I get a #spill error I would like it to say another thing (but not #spill). Is there any way to do this? Is there an easier way to do it? There is a column in the original data that contains the answer I want, but since it's in multiple rows I can't figure out how to not get the #spill result. I can't share any data as it is for work and English is not my native language so I'm sorry if I didn't explain properly. submitted by /u/gaensefuesschen [link] [comments]
Tagged with
#Excel alternatives for data analysis#Excel compatibility#google sheets#Excel alternatives#financial modeling with spreadsheets#natural language processing for spreadsheets#generative AI for data analysis#rows.com#AI formula generation techniques#formula generator#excel#transpose#sheets#formula#function##spill error#filter#merged cells#$IJ$60:$IJ$503#$IH$60:$IH$503