Recent Discussions
Excel Challenge - Pivoting poorly structured data
This is from an ExcelBI challenge. I thought it may be worth while posting my solution here as a demonstration of modern Excel methods. Challenge Like many of such challenges, the natural solution approach is to use BYROW but that creates the usual 'array of arrays' error. Solution: Gradually I am moving to a point at which I have no formulas showing in the workbook other than calls to Lambda functions. In this case, the worksheet formuloa is = PIVOTBYCATEGORYλ(OrderTbl) The function works row by row apportioning the amounts against the listed categories PIVOTBYCATEGORYλ // Groups and pivots table by category = LAMBDA(table, LET( normalised, BYROWλ(table, APPORTIONλ), // Identify fields from normalised table dimension, TAKE(DROP(normalised,,1),,2), category, TAKE(normalised,,1), partCost, TAKE(normalised,,-1), // Pivot by category return, PIVOTBY(dimension, category, partCost, SUM,,0,,0), return ) ); The function APPORTIONλ divides the amount between categories so each record within the source data returns a number of rows APPORTIONλ // Splits by category and assigns costs = LAMBDA(record, LET( category, TOCOL(REGEXEXTRACT(INDEX(record,4),"\w+",1)), amount, INDEX(record,3) / COUNTA(category), year, YEAR(INDEX(record,1)), region, IF(LEN(INDEX(record, 2)), INDEX(record, 2), "Unknown"), broadcast, B∕CASTλ(HSTACK(region, year, amount), category), return, HSTACK(category, broadcast), return ) ); /* FUNCTION NAME: B∕CASTλ DESCRIPTION: Broadcasts the terms of a vector over the shape of a second array */ B∕CASTλ = LAMBDA(vector, array, IF({1}, vector, array)); The key to making the formula work is the function BYROWλ that I wrote to generalise the inbuilt but over-restrictive BYROW function. The PIVOTBY function returned the required crosstab from the normalised data array
Those 'annoying' page breaks
I use Word, as well as other programs, to put the final polish on my manuscripts. I am considering changing publishers, and one of the candidates wants me to use page breaks only on chapter ends. Is there any way that I can tell Word 365 (Win 11 Pro) to let me put the breaks in where I want them? This would also mean finding a way to suppress the automatic page breaks. Is this even possible, and if so, how do you do it? Regards Jo
Excel crashes whenever file tab is clicked
I have been having a lot of issues with Excel on a vm that we use, and I cannot try and fix these issues due to not being able to click on the file tab. Whenever the file tab is clicked, excel freezes and if anything clicked again goes to not responding. I do not get anything from windows diagnostics. I have also reinstalled Office 365 several times and excel always has this issue. the VM is a 2019 data Center and excel is 32 bit. running in safe mode does nothing. Book.xltx or Sheet.xltx is not in the XLSTART folder, if it was, I would have to wait for plant downtime to restart the vm. Deleted the excel key in HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel, restarted excel and issue still persists.
Quiz scores and review scores are not equal. Why?
Hi maybe someone can help. Why is my actual test score 100% when I view my results, but if I check my score with the "review scores" option from admin screen, it only comes out to 67%.....? How do I get both to reflect 100%. I am looking to use the auto grade function. The drop down answers is not important as it's just gathering info. If I manually bump the score +1, that 67% won't increase (even with a refresh) to 100% but the answer gets a correct flag and I can see the "extra credit". This is very confusing for such a simple app. I get the same issue when I tried to use a branching question on another quiz. It's not being scored in one scenario (person doing the test so they get 100%), but in review scores, it's counted as a question and it comes across as a missed answer or something, so the percent is off. Thanks.
Microsoft Edge Webview icon
I'm I in the right community? My Widgets Icon on my toolbar doesn't open even with the Widgets turned on in the settings. Could you help me? I am the administrator. I have the Edition Windows 11 Home Version, 23H2, with a Windows Feature Experience Pack. I have Windows 11 software and has been updated recently. Everything under Task Manager, details, mswebview.exe is running, however, the window doesn't appear on the home screen. Could this be an easy fix? TIA JosephCurrent Date and time per cell
I have a table with one column listing rows with a dynamic text box (open; closed) in the next column I would like to display the date and time when the status changes. The purpose is to show when a task was completed, I have used the now() function but it keeps refreshing, I need it stay the date The snip of the sheet below shows how is is currently working, so can any one help with some code for column J please. thank you LeeLoss carry forward for limited years
Hello Excel Community, Excel Version: O365 (Enterprise) I’m running into a mental block trying to model loss carryforward with limited years using a spill formula. Below is a simplified example of what I’m trying to build a formula for. Rules: • A loss from any year can be carried forward for N years, after which it expires and can no longer be used. • In any year with a positive tax liability, if there is any remaining loss from the past N years, you deduct using a FIFO approach (oldest losses used first). • Any unclaimed balance from year N+1 expires, even if it hasn’t been fully used. I’ve found plenty of examples showing loss carryforward without expiration, but I’m struggling to build something that handles both FIFO and expiration in a dynamic array formula. I feel like this must be a solved problem and I’m just missing something obvious. Any help would be greatly appreciated! ------------------------------------------------------------------------------------------Change in return of AVERAGE function - Mac Excel
I was puzzled that a spreadsheet I use daily generated an error today that had not existed in earlier versions. Attempts to confirm the validity of the spreadsheet functions by running prior versions that had previously run error free resulted in the same error. Eventually, it seems that the operation of the AVERAGE function has been changed in an Excel program update that was installed yesterday. Previously, if an AVERAGE function addressed a range of empty cells, it would return a zero value. Now it is returning the error #DIV/0! This is strange because the AVERAGE function will now return zero if the addressed range contains zeros rather than just being empty. Not sure if Microsoft intended this change or if the change might also apply to other functions. In muy case, I'm able to change my spreadsheet to provide for this but it would have been nice to have some warning.Random Contractor Selection List
Hi, I would like to set up an automated random contractor list for tender purpose - is this possible? For example, I have Contractor 1 - Contractor 10, I would like to choose 2 contractors for each tender/quotation request randomly. Currently, it is done manually which is time consuming and could not ensure impartiality in selection - if I can setup the contractor rota to generate random selection, it would be wonderful. Please can anyone advise and help with the correct way to setup my excel sheet? Many thanks
Shared OneDrive for Charity Management Team
We are a small charity running a Village Hall in the UK. A few of the trustees form a Business Team that run the hall day to day. All of these have Office 365 personal or family subscriptions to use word outlook etc on their own laptops. The charity itself has Office 365 Business Basic Grant subscription. This give us Exchange email, OneDrive, Teams and SharePoint plus a few other bits. We currently store all our charity documentation on a free 15Gb OneDrive dating back to SkyDrive when first adopted. Each member of the team has their own charity Microsoft account and email e.g. email address removed for privacy reasons or email address removed for privacy reasons etc, etc. I want to migrate this data onto an Office 365 Business OneDrive that is shared with this Business Team Here's the rub. As most users have a personal subscription and data saved on their own OneDrive keeping the two separate is problematic. Because Microsoft credentials are so tightly integrated between the OS and Office if they try and sign in to the existing OneDrive it takes them to their own private OneDrive. To work around this they have to use an Incognito browser session in order to log in. I am aware you can create a shared OneDrive for teams as described https://support.microsoft.com/en-gb/office/create-a-new-shared-library-from-onedrive-for-work-or-school-345c8599-05d8-4bf8-9355-2b5cfabe04d0 which should allow them to access this OneDrive by using their own credentials as above (email address removed for privacy reasons etc) but if they log in to this shared OneDrive once, and on the prompt that comes up at log in select "Stay logged in", can they keep visiting the site without logging in again and will they then have to use an incognito browser session to access their own personal OneDrive? Sorry this is so complicated but its been a nightmare trying to keep this working Many thanks JohnMultiple Inventory entries on the same item
Okay so I'm wondering if there is a way for excel to automatically advance to the next column when entering inventory data so if I need to make another entry on the same item I don't have to advance to the next cell myself. This would be helpful in streamlining inventory and making it foolproof when adding inventory to a specific item (without having to worry about deleting previously entered data). I'm hoping there is a way to do this so I can take a tablet when completing my inventory and avoid the old clipboard and paper.
Bank recon matching
Happy New Year everyone! Can someone please assist me with this to a matching exercise for a bank reconciliation. Tried to do XLOOKUP with REGEXTEST but no luck. Below is the link to the excel file. https://1drv.ms/x/c/80b0e5c8d40a83ef/IQAn2IxiOBDpSJBIIZCfPj3TAexy2EEPYT5VjpDkcpTzpB4?e=S4NEuo
Duplicate Values that are not exact match
Hello, I have an excel file with names I need to clean up. I started with using conditional formatting to highlight all duplicate values and deleted the duplicates. There are still duplicates in the file that don't share the exact name. For example, there's one cell called "Blackline Safety", and another called "Blackline Safety Corp". These are duplicates, but I don't know how to do it without looking manually. Is there any way to get excel to find the duplicates like that? Thanks
Events
You’re passionate about what you do, and we’re passionate about empowering your potential. Microsoft 365 Community Conference - is your chance to keep up with AI, build game-changing skills, and take...
In-Person
Recent Blogs
- 4 MIN READClipchamp reached new levels of innovation and enabled increased productivity in 2025!Jan 16, 2026
- There are now two faster, more intuitive ways to bring text-based data directly into Excel.Jan 15, 2026
