Excel on Mac
2690 TopicsHow to make excel add or remove rows to fit data and prevent spill error?
Hello! My question is: When using the =FILTER formula to copy cell data from one sheet to another, how can I make excel add or remove rows as needed to prevent a spill error? I am using the =FILTER formula to copy cell data from one sheet to another if a checkbox is checked. My formula uses the cells containing the checkbox (column A) as the reference for columns B, C, D, etc. to the right. I did this by selecting the cell that has the formula on the destination sheet, highlighting this cell and those to the right I wanted to transfer, and then control + r. My formula is: =FILTER('Source Sheet'!B5:B13,'Source Sheet'!$A5:$A13=TRUE) (If there is a way to copy an entire row from the source sheet instead of having to control+r to apply the original formula across select cells please also let me know, thank you!). However, I want the sheet to be able to automatically add rows to fit the data if the selected number of rows is greater than what I have left available on the sheet. Currently, if I leave 3 rows for data to fill and I select three checkboxes, this will fill the space with no problem. However, if I select 4+, I get a spill error (as there are not enough empty rows to put the data). Is there any easy way to have excel add rows as needed? Similarly, is there a way for excel to remove rows as needed? I am going to use this sheet to detail all medications being taken by family members (with medication name, concentration, dose, time to take each in their own column as column B, column C, column D, column E). The names will be listed in column A with ~3 rows below each name which will fill when the checkbox on the source sheet is clicked. I want to ideally fit this on one page when printing without major "fit to page" problems that come up during printing. The layout of the sheet will ideally look like this: The goal for the 'destination sheet' is to pull only the selected data I want to use from my master list on the 'source' sheet.' I want to avoid having a big empty space under each family member where they don't take many medications, and to also not have to manually add rows for family members that take a greater number of medications. Please let me know and thank you!86Views0likes3CommentsTitre axe secondaire
Bonjour, j’utilise Excel Microsoft 365 sur Mac et je rencontre un problème avec mon graphique de la loi de Pareto (voir capture d’écran jointe). Le graphique combine des colonnes pour le chiffre d’affaires et une courbe pour le pourcentage cumulé. Mon souci est que je n’arrive pas à ajouter un titre à l’axe vertical secondaire (celui de droite, qui affiche les pourcentages). L’option “Titre d’axe secondaire” n’apparaît ni dans le ruban, ni dans les paramètres du graphique, même quand j’active l’axe secondaire. J’ai déjà essayé de passer par les menus contextuels, les options de graphique, et même de modifier le type de graphique combiné, mais sans succès. Pouvez-vous m’aider à afficher ou activer le titre de l’axe secondaire ? Merci d’avance pour votre aide !33Views0likes1CommentPivot table
having an Excel workbook with multiple sheets. each contain certain data and a pivot table in each one. recently started having problem that is when I copy a pivot table from an existing data sheet to a new inserted sheet and then changing the source of data to the new sheet and then refresh the pivot table then save and close the file; then when I try to open the file again I got an error and the most recent pivot table get deleted. tried several ways to solve the issue with no avail noting I'm using Microsoft 365 for Mac. appreciate any help49Views0likes2CommentsGrouping Data
Hello. I have grouped some rows together, consisting of a top row, with a number of sub rows. The top row is the sum of the sub-rows. At present the icon to expand /close the sub rows is at the bottom. Is there anyway to have this icon at the top row? Many thanks56Views0likes2CommentsExcel for Mac Character entry into cell delayed til after all return entries processed.
MBA M2 Sequoia 15.6.1 (24G90) Excel Version 16.102 This issue started after recent update. When there is a big resource demand entries into cells used to be delayed, but would run correctly when resources were available. For example: Select cell A1 Type "A" Return Type "B" Return Type "C" Return This would produce A, B, and then C in three separate cells down the column, even when resource demand was high, the entries would just be delayed but entered correctly. Now, the process when delayed by resource demand, stores all the cell entries, first runs all return commands and then puts all entries into the last cell.87Views1like2CommentsToo thick/fat gridlines when printing from excel
When I print a spreadsheet where I have selected/marked for gridlines, the gridlines become much thicker than they should be. They have the same thickness as the cells I have given borders. On the screen everything looks correct. The problem is only on the printout. I get the same problem no matter which printer I choose (has nothing to do with the printer driver). I have the latest operating system and drivers.29Views0likes1CommentFinding a "2 cell" name in anther sheet and copying data
Hello- I'm looking for a formula to enter in column D of the Main Sheet. I would like to find the "Total" in sheet: 2023 for the name in cells A&B, and copy them into the Main Sheet. The names are in a different order in each sheet. I've tried XLOOKUP, but couldn't make it work. Any ideas?87Views0likes2CommentsCustom Sparkline Formula
I am in the process of moving to Excel from Google sheets. I am trying to build a project sheet and add a formula that will provide a "sparkline" for quick reference of the projected start and end dates, and then based on the status of the task (not started, in progress or complete) and today's date in relation to the start and end dates of the task, change color (Green, yellow, red, etc). Here is the formula I use in Google Sheets, but I cannot get it to work in Excel. =SPARKLINE(if(OR(today()<D5,today()>E5),{int(D5)-int($D$2),if(D5=E5,1,int(E5)-int(D5))},{int(D5)-int($D$2),today()-int(D5),1,int(E5)-today()}),{"charttype","bar";"color1","white";"color2",if(G5="Complete","grey",if(today()>E5,"red",if(AND(today()>D5,today()<E5,G5="Not Started Yet"),"darkred",if(AND(today()>D5,today()<E5),"orange",if(G5="In Progress","orange",)))));"max",int($E$2)-int($D$2)}) In this formula, Column D represents the start date and Column E represents the specific task end date. D2 and E2 represent the Project start and end date. Any Advice?Solved44Views0likes2CommentsBest way to get rolling 12-month totals
I am using Excel for Mac version 16.102. I have data in a Table which is basically date, category & value. There are many other columns I use for filtering. I pivot this data to get totals by month within year but I hide the yearly totals and just have the grand total. My pivot table is filtered to include\remove high-level category data. I have a Timeline which allows me to select the date range of my pivot. I have a number of slicers to facilitate filtering to get the dataset totals I need. There maybe a better way but the above works. My ask is how do I go from the above to easily get a rolling 12-month totals for say Jan 24 - Dec 24, Feb 24 - Jan 25, Mar 24 - Feb 25 etc etc? I can move the timeline which gives me the correct answer but I'd like to be able to see the rolling totals for the last 12-months at a glance without having to keep moving the timeline. Eventually I'd like to graph the last 12-months so I can see movement over time. Can I do want I want with a pivotable or do I need another approach? Thank you for your help.247Views0likes18Comments