Conditional formatting
I'm making a rule to Highlight cells in Column AZ when the value is lower than Adjacent cells in Column P. For the most part it works except for two cells in AZ (1 shown as an example) that are higher than P but are being picked up by the conditional formatting. The two cells I'm focusing on here are P14 (£0.27) and AZ14 (£0.43). £0.43 should not be being highlighted pink as it isn't lower than £0.27. This has to be something obvious but I have looked at it too long. Please help if you can :)Halloween 2026 Scheduling Challenge 🎃
Happy Halloween! You'll find the rules in the screen capture below and in the attached workbook. Creative solutions are encouraged. Feel free to use any functions available in the Beta channel. I won't post my solution immediately so submissions aren't influenced. Enjoy!
Find mismatches
I want a way to flag or highlight a cell in Column B if the content does not exactly match the content on the same row in Column A. My spreadsheet has hundreds of rows of data, and just a few mismatches, and I'd like an easy way to find them. Seems like Conditional Formatting would offer an option, but I don't see one. Is there a way to do this?
Excel 2016 copy/paste issue is back after KB5002934 update
Is anyone else experiencing copy/paste issues in Excel 2016 after the KB5002934 update? We're seeing this issue on workstations across our organisation. What's frustrating is that this is the same issue we encountered before, which was supposedly fixed in a previous update. Now it's back, barely a few weeks later. For now, we're using an uninstall script to temporarily remove the update from affected workstations. However, this isn't a reliable long-term solution because Windows Update will push the update again. Is anyone else experiencing the same thing? Has Microsoft provided any explanation for why this issue keeps coming back? Has anyone found a more permanent solution that doesn't involve repeatedly uninstalling the update? I'd like to know if other organisations are dealing with this and how they're handling it.Excel external workbook links break across users (using cloud storage)
We use Excel desktop on Windows computers with Microsoft 365. Our office uses Sync.com to synchronize shared client files across multiple employees' computers. Our current setup: We have a Master Excel workbook linked to approximately 11 individual client workbooks. We create new workbooks and establish the links twice a year, in June and December. We create links by opening both workbooks, selecting a cell in the Master, typing =, switching to the individual workbook, clicking the source cell, and pressing Enter. We need linked values to update automatically when both workbooks are open. Our folder structure is: - Master: Sync\1 - CLIENT FILES\Equitable Block\Block Name\Renewals\YYYY - Individual workbooks: Sync\1 - CLIENT FILES\Client Folder\Renewals\YYYY The files are synchronized through Sync.com, and everyone opens them from shortcuts to the shared Sync folder. The problem: Excel stores an absolute local path in the formula. For example: C:\Users\paige_lnsp4p\Documents\Sync\1 - CLIENT FILES\...\[Individual Workbook.xlsx]Myers Position'!$F$16 Another employee's Sync folder is located at: C:\Users\chris\Sync\1 - CLIENT FILES\... As a result, links created by one employee work on that employee's computer but fail on others. Changing the source to the local path on another computer repairs the link there, but that is not a practical permanent solution for our team. Sync.com support confirmed that its desktop app cannot enforce a consistent local path across different Windows usernames. They suggested investigating relative Excel links. My questions: 1. Can Excel desktop create and maintain relative external workbook links that work across multiple Windows computers when the shared folder structure is identical but the local root paths differ? 2. Given that the Master and individual workbooks are stored in different branches under Sync\1 - CLIENT FILES, is there a supported way to achieve this without moving all workbooks into one folder? 3. If relative links are not supported for this arrangement, is there another reliable way to maintain these links without requiring each employee to change the source paths? 4. Would VBA or another Excel-based solution be appropriate, or would it introduce further reliability or security issues? We want to preserve our existing workbook design and continue using desktop Excel. We are looking for a permanent solution that works for all employees, not a process that requires manually repairing links on each computer.Excel “Trace Dependents” stops working in XLSX files generated by ChatGPT
Excel “Trace Dependents” stops working in XLSX files generated by ChatGPT – Cause and tested solution Problem When opening Microsoft Excel workbooks (.xlsx) generated by ChatGPT, the Trace Dependents feature may stop working, reporting that a cell has no dependents even when other cells explicitly reference it. The Trace Precedents feature may continue to function normally. In our testing, the problem was particularly difficult to diagnose because: It occurred repeatedly with Excel workbooks generated by ChatGPT. Opening an affected workbook caused Trace Dependents to stop working even in other workbooks that had previously worked correctly. The malfunction persisted after closing the affected workbook. Recalculating formulas, rebuilding the calculation chain and reconstructing the workbook did not consistently resolve the issue. Root cause identified The affected XLSX files generated by ChatGPT contained the following setting in xl/workbook.xml: forceFullCalc="1" This setting enables forced full calculation, bypassing Excel's normal dependency-based calculation mechanism. Tested solution Close Excel completely, including any remaining EXCEL.EXE processes. Open the affected XLSX file as a ZIP archive. Locate xl/workbook.xml. Find the <calcPr> element. Change forceFullCalc="1" to forceFullCalc="0", or remove the attribute. Save the corrected XLSX file. Restart Excel in a clean session. Open only the corrected workbook and test Formulas → Trace Dependents. Experimental confirmation We verified that: An original ChatGPT-generated workbook containing forceFullCalc="1" triggered the malfunction. The problem affected the entire Excel session and persisted after closing the original workbook. A corrected version containing forceFullCalc="0" worked when opened in a clean Excel session. The same issue was reproduced through multiple tests, including Excel Safe Mode. Recommendation for ChatGPT and XLSX-generation developers ChatGPT's spreadsheet-generation workflows should explicitly prevent the unnecessary activation of forceFullCalc=True. Before delivering generated XLSX files, the system should inspect xl/workbook.xml and ensure that persistent forced full calculation is disabled unless specifically required. This check is particularly important because the problem may affect not only the generated workbook but also other Excel workbooks opened in the same session. Scope and limitation This issue was repeatedly observed in workbooks generated through ChatGPT in our testing. The results do not establish that all ChatGPT-generated Excel files are affected, nor that this setting is responsible for every Trace Dependents malfunction. Nevertheless, the relationship between forceFullCalc="1" and the observed behavior was confirmed experimentally in the tested environment. Conclusion A ChatGPT-generated XLSX workbook with forceFullCalc="1" can trigger a persistent malfunction of Excel's Trace Dependents feature. Disabling this setting and restarting Excel restored the functionality in our tests.Put multiple values in one cell with lists and arrays in Excel
Throughout Excel's 40-year history, you've only been able to put one value per cell. In this announcement, we're excited to share how that's changing with the release of lists, arrays in cells, and nested arrays, initially to Microsoft Excel for Windows and Mac Beta Channels. Many workbooks already try to pack multiple values into one cell. A project might list "Carlos, Henrietta, Jacob" as three owners, or a Forms survey might return "2:00 PM; 2:30 PM; 3:00 PM" as one response. With lists, you can keep those values in one cell, while also keeping them separate for filtering, calculation and more. Later in this post, we'll explore arrays in cells and nested arrays in more depth. NOTE: These are preview features. Their behavior may change before general release based on your feedback. We don't recommend using them in important workbooks until they're generally available. Lists Lists let you put multiple values into one cell. You can create a list by selecting Insert > List or pressing Ctrl+J, then typing or pasting items separated by commas or semicolons, depending on your regional settings. Selecting the icon in the cell shows the individual values. You can add, remove, or edit list items by double-clicking the cell or pressing F2, just like other values. With lists, you can filter by one or more individual items instead of whole text entries. Referencing a list returns all its values for calculations. For example, =B2 spills those values into separate cells. Arrays in cells Lists are useful on their own, but they're part of a much broader change to Excel. For the first time in Excel, arrays can exist natively in cells as values or as formula results. They can be any size or shape and can even contain other arrays. You can now keep the result of any spilling formula in a single cell by "wrapping" the formula body with braces { }. Since the introduction of dynamic arrays, array results have spilled across cells – for example ={1;2;3}. Wrapping the original array with braces creates a 1x1 array around it, so instead of spilling to multiple cells, the array stays in a single cell. Braces have long been used to describe arrays in Excel and this extends that behavior by allowing multiple layers of braces. This gives you more flexibility when building spreadsheets. Instead of leaving room for a formula to spill, you can keep the result in one cell. Arrays inside arrays, or nested arrays Arrays can now also "nest" inside other arrays. For example: ={{1,2,3};{4,5,6}} Previously, a formula that produced an array of arrays would return a truncated result or #CALC! error. Now, supported formulas return the complete nested result. In the example below, you can see how TEXTSPLIT behaves with and without nested arrays. Without nested arrays, Excel only returns the first item for each row. With nested arrays, the result spills, one array per row. The arrays in each row can then be used in further calculations. Four new functions: FLATTEN, HAS, HASANY, HASALL To help you work with arrays more easily, we've added four functions. FLATTEN(array, [pad_value], [levels]) simplifies nested arrays by removing one or more levels of nesting. Continuing from the prior example, FLATTEN lets you simplify the nested array output, spilling the individual results into the grid. We used an empty string ("") for pad_value so rows with fewer items show blanks in the remaining columns. Three HAS functions check whether values are in an array: HAS(array, value) returns TRUE if value appears anywhere in array, and FALSE otherwise. HASANY(array, values) returns TRUE if any of the values appear anywhere in array, and FALSE otherwise. HASALL(array, values) returns TRUE if all of the values appear anywhere in array, and FALSE otherwise. Do more with spreadsheets using arrays in cells and nested arrays Arrays in cells open up spreadsheet designs that weren't practical before. The run tracker below captures split times (how long it takes to run each kilometer) in a table with one run per row. The number of splits depends on the length of the run. Stats for each run are calculated right in the same table. For more examples, I recommend looking to your favorite Excel communities on LinkedIn, YouTube, Reddit, or elsewhere. Enabling nested array calculations in a workbook Compatibility Version 3 will be released alongside arrays in cells and is required for most calculations involving nested arrays. You can set Compatibility Version for each workbook in by selecting Formula > Calculation Options. See compatibility versions for more information. Some existing formulas return different results in Compatibility Version 3. If your workbook doesn't behave as expected, you can keep it set to Compatibility Version 1 or 2. Known limitations As this feature rolls out to Beta Channel, the following limitations apply: Conditional formatting doesn't inspect array contents unless you use a formula Data validation can't use a list or array as dropdown items Charts don't expand an array into data points PivotTables don't read array values as source data Power Query doesn't load or emit array-valued columns Find & Replace can't replace list/array items Availability These improvements are rolling out to Beta Channel users running: Windows: Version 2610 (Build 20520.20000) or later Mac: Version 16.114 (Build 26092111) or later Features covered on this blog roll out over time to enable us to monitor quality and performance, so some preview features may not be available to you right away. Also note that features may be paused, adjusted, or removed as part of that process. Feedback Click Help > Feedback in Excel to tell us what you think.Question about categorizing transactions from bank statement in EXCEL
I have a problem of categorizing every transaction in the bank statement for bookkeeping purposes. As shown below, I have to input the Description for every item, and every item has to refer to different columns. For example, if we see "CHECK NO" in REf 2, we have to input "*Look up in other excel file*". If "John" appears in REF2, we have to input "JOHN CO. -BC". If "BOOKTRANSFER" appears in "BANK TRAN. DESCRIPTION", we have to input "BOOKTRANSFER DR. ABC CR. Def". If 360.9(DEBIT) and PAY TO CAR. CO(REF4) appears at the same time, than "VEHICLE LOAN - TOYOTA 123" (I still cannot figure out how to write this rule). There are hundreds of these rules. I wrote a function, like =IFS(ISNUMBER(SEARCH(("BOOKTRANSFER"),CONCAT(B6,F6:K6),1)),"BOOKTRANSFER DR.ABC CR. Def", ISNUMBER(SEARCH(("John"),CONCAT(B6,F6:K6),1)),"JOHN CO. - BC", ISNUMBER(SEARCH(("CHECK NO"),CONCAT(B6,F6:K6),1)),"*Look up in other excel file*"), and drag it down in cell E, to facilitate my job. However, I cannot enter every single rules into a cell, and it would be very long and complicated. It is also difficult to modify. I just wonder whether I can do it with a table or not, like this: so I can input hundreds of rules. But I still cannot figure out how to do it with a table. Please advise if there is a smarter way to do the job. Thank you. (P.S. Reply in Chinese and English are welcomed)
