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
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.
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 JohnThose '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
Outlook changing styles/colors of incoming email?
HI friends - I recently got an email from a research org named ORCid and I noted when I received the email that it was hard to read - the text was a lightt grey on white, or light grey on dark grey in dark mode: I emailed ORCid back and explained that this was going to fail any WCAG/Accessibility checks, and could they take a look. I spoke back and forth with their internal developer who insisted that it didnt look like that on being sent - he sent me examples from his own inbox as proof: And these look perfectly fine. Weirdly - when I forwarded this email to a colleague, they showed me on their screen it appeared as intended with the tetxt darker and more legible. They forwarded it back to me - and surely enough, it looks correct. This using the Outlook desktop app in both old and new mode - and dark or light profile - and also in the web outlook as well. Im really confused as to how/why when I receive it the first time it looks so unreadable? Any advice would be awesome.
Insights from Copilot have stopped working for us
We're all licensed (verified) with M365 Copilot, and for about the last week, one of our workhorse intake forms has stopped working with regards to the "Insights from Copilot" feature. When we attempt to refresh the insights, it presents the message, "Your form has XX responses. Copilot is analyzing the data to provide insights.", then spins for about 30 seconds, and then presents the error, "The insights haven't been generated successfully. Please try again." We're syncing responses to an Excel workbook, and that seems fine, but after about a week these "insights" just started failing in this fashion. We've done basic troubleshooting like trying InPrivate browsing, cache clearing, and since we are only at about 100 responses for this particular small form we're not hitting any limits. Regardless, it's wedged stuck in this state. I reported it via the M365 Admin portal and received the usual "No issues found" reply. 🙄 Anyone else seeing this problem? Does anyone actually use this feature? 😜What’s New in Microsoft EDU, Bett Edition January 2026
Welcome to our update for Microsoft Education and our special Bett 2026 edition! The Bett conference takes place in London during the week of January 21st - January 23rd, and Microsoft Education has 18 exciting updates to share! Check out the official Bett News blog here, and for our full Bett schedule and session times, be sure to check out our Microsoft EDU Bett 2026 guide. January 2026 topics: Microsoft 365 Updates for Educators Microsoft Learning Zone Microsoft 365 Updates for Students Teams EDU and OneNote EDU Updates Microsoft 365 LTI Updates Minecraft EDU 1. New Educator tools coming to the Teach Module in Microsoft 365 Unit Plans Soon educators will be able to create unit plans in Teach. Using a familiar interface, educators will be able to describe their unit, ground in existing content and educational standards, and attach any existing lesson plans. Unit plans will be created as Microsoft Word documents to facilitate easy edits and sharing. When: Preview in Spring 2026 Minecraft Lesson Plans Minecraft Education prepares students for the future workplace by helping build skills like collaboration, creative problem-solving, communication, and computational thinking. Coming soon, you will be able to create lesson plans in Teach that are fully teachable in Minecraft Education. And if you’re new to Minecraft Education, the lesson plan includes step-by-step instructions to get started. Just like the existing lesson plan tool in Teach, Minecraft Lessons can be grounded on your class details, existing content, and educational standards from 35+ countries. When: Preview in February 2026 Modify Content When: In Preview now Teach supports educators in modifying their existing teaching materials using AI-powered tools that save time and help meet the diverse needs of learners. With Modify existing content, educators can quickly adapt lessons they already use—without starting from scratch—by aligning materials to standards, differentiating instructions, adjusting reading levels, and enhancing text with supporting examples. Each modification tool accepts direct text input or file uploads from cloud storage, making it easy to transform current curriculum resources. These tools help educators maintain instructional intent while ensuring content is accessible, standards aligned, and effective for all learners. Align materials to standards Aligning instructional content to educational standards helps ensure lessons clearly support required learning goals and set the right expectations for learners. The Align to Standards tool rewrites existing lesson instructions so they reflect the intent of the selected standard—focusing on what learners should understand or be able to do—without copying the standard’s wording. Scenario: An educator has a lesson instruction for a reading activity on ecosystems. After selecting a state science standard, the educator uses Align to Standards to produce a revised instruction that emphasizes system interactions and evidence-based explanations while preserving the lesson’s original purpose. This allows the educator to strengthen alignment quickly without rewriting the lesson from scratch. Differentiate instructions Differentiation helps ensure every learner—regardless of readiness, background knowledge, or support needs—can access and engage with instructional tasks. The Differentiate Instructions tool adapts existing instructions based on specific supports an educator selects, such as adjusting reading level, including a single type of scaffold, or targeting a desired length. Because this tool is designed for single shot use, it produces a clear, accurate adaptation that adheres directly to the selected inputs. Scenario: A secondary biology educator has lab instructions written for general education learners but needs versions for learners requiring additional scaffolding. Using Differentiate Instructions, the educator quickly generates modified instructions that include step-by-step breakdowns, sentence starters, or graphic organizers—making the lab more accessible without changing the learning goal. Modify reading level Adjusting the reading level helps ensure instructional content remains accessible while preserving essential vocabulary and core concepts. The Modify reading level tool rewrites text to match a specified grade level, simplifying or increasing complexity as needed while maintaining meaning. Educators can also choose to generate a glossary with clear, age-appropriate definitions of key terms. Scenario: A social studies educator wants students to work with a primary source written at a university reading level. Using Modify reading level, the educator creates a version that maintains the document’s key ideas and important historical terms while simplifying sentence structure for lower secondary learners. By adding a glossary, students can access learner friendly definitions alongside the adapted text. Add supporting examples Concrete examples strengthen understanding by connecting abstract ideas to real world applications. The Add Supporting Examples tool enhances existing instructional content by appending relevant, accurate, and age-appropriate examples—without altering the original paragraph. Scenario: An educator teaching thermal energy transfer has a paragraph explaining that heat moves from warmer objects to cooler ones, but the concept feels abstract. Using Add Supporting Examples, the educator adds real world examples—such as a metal spoon warming in hot soup or an ice cube melting on a countertop—to help learners visualize how heat transfer works. These examples reinforce understanding and make the concept more accessible for secondary learners. Fill in the Blanks, Matching and Quizzing New Learning Activities are coming soon! We’re excited to introduce three new Learning Activities designed to make classroom experiences more dynamic and personalized: Fill in the Blanks, Matching, and Quizzes. Whether it’s completing paragraphs to strengthen comprehension, pairing terms with definitions in a timed matching game, or testing knowledge through quick self-assessments, these activities bring variety and fun to learning. Fill in the blanks creates paragraphs where learners can check their understanding by filling in missing terms. Matching is a game where learners can match terms and definitions while racing against the clock, aiming for fast completion and accuracy. And Quizzes allows students to quiz themselves and assess their comprehension. Learning Activities are available across our education products, in a standalone web app, in the Teach Module, in Teams for Education, in the Study and Learn agent and Study Guides. When: Spring 2026 Teach Module updates in Teams Classwork In Teams Classwork, you can already use Copilot to create Lesson Plans, Flashcards, and Fill in the Blank Activities. Coming this Spring, you will see the ability to create and modify more content, better matching the capabilities of Teach in the Microsoft 365 Copilot App. This includes modifying content with AI, Minecraft Lessons, and more! When: Coming soon Teach Module and Class Notebook integration We're bringing Copilot-powered creation tools directly into OneNote Class Notebook. Teachers will be able to generate Learning Activities and quizzes or modify existing content (like adjusting reading level or adding supporting examples) without leaving the page where they're already planning. When: Coming soon 2. Spark classroom engagement with Microsoft Learning Zone Educators worldwide are always looking for innovative ways to engage students, personalize learning, and support individual growth, yet limited time and resources often stand in their way. Microsoft Learning Zone, a new Windows app, empowers educators to transform any idea or resource into an interactive, personalized lesson using AI on Copilot+ PCs. The app also provides actionable insights to guide instruction and support every student’s progress. Learning Zone is now available to download from the Windows app store and included at no additional cost with all Microsoft Education licenses. Just in time for Bett 2026, Learning Zone has earned the prestigious ISTE Seal of Alignment - a recognized mark of quality, accessibility, and inclusive design. This recognition reflects our commitment to delivering meaningful, inclusive, and research-backed digital learning experiences for every learner. As noted by ISTE reviewers: "Microsoft Learning Zone saves educators valuable time while delivering personalized instruction that addresses individual learning needs." Getting started with Microsoft Learning Zone is simple. Educators begin by defining their lesson goals and preferences and can also choose to reference their teaching materials or trusted in-app resources by OpenStax. From there, AI does the heavy-lifting, generating a complete, interactive lesson with engaging content slides and a variety of practice activities. Educators can also quickly create Kahoot! quizzes using AI, bringing live classroom gamification into their lessons with just a few clicks. Learning Zone is more than content creation; it provides a full classroom-ready solution: from assignment to actionable insights. Once a lesson is created and reviewed, educators can assign it to students. Students complete lessons at their own pace, on any device, while the lesson flow adapts to their responses, helping reinforce understanding, revisit missed concepts, and build confidence over time. Educators, in turn, gain clear, actionable insights into student progress and mastery, enabling them to personalize instruction and better support every learner’s growth. Learning Zone is a classroom ready solution including management and actionable insights Learning Zone also includes an extensive library of ready-to-learn lessons developed in collaboration with leading global organizations, including the Nobel Peace Center, PBS NewsHour, the World Wildlife Fund (WWF), NASA, OpenStax, Figma, and Minecraft Education. Ready-to-learn lessons are available to educators and students on any Windows device and are a great way to inspire curiosity and bring meaningful learning of different subjects into the classroom. Ready-to-learn library in partnership with trusted global organizations Learning Zone is available today: Visit https://learningzone.microsoft.com to learn more and download the app. 3. New AI-powered tools for student learning in Microsoft 365 Study and Learn Agent Bring the interactive, conversational Study and Learn Agent in the Microsoft 365 Copilot App to your students. Available to all Microsoft EDU customers, the agent does not require an additional Copilot license. It is going into preview now, in January 2026. Join the Microsoft Education Insiders community at https://aka.ms/joinEIP and get information about getting access to the Preview. Study and Learn helps learners understand concepts, practice skills with activities like flashcards, and prepare for tests with study guides and quizzes. Additional activities including fill-in-the-blanks, matching, and others that will continue to be added. Purpose-built for learning in collaboration with learning science experts, Study and Learn aims to help foster reflective and critical thinking. Over time, it will provide a more personalized, adaptive, inclusive experience to make learning relevant and bolster motivation. When: January 2026 Preview Learning Activities app The Learning Activities Web App is now here! This web-based experience brings all your favorite activities together in one place, making it easier than ever to create, customize, and share engaging content. Whether you’re an educator designing lessons or a student building study sets, the web app offers a streamlined interface for finding or creating Flashcards and Fill in the Blanks with Matching, and Quizzes coming soon. You can easily access all your activities that you have created in other products from the web app, too. When: Available now! 4. Updates for your favorite teaching tools - Teams EDU and OneNote EDU Set AI Guidelines in Teams To help bring clarity to AI use in the classroom, AI Guidelines in Assignments allow educators to set clear expectations for when and how students can use AI—directly within the assignment experience. Educators start with a set of default, standardized AI use levels, and can apply them at the class or assignment level, with the ability to customize descriptions to reflect their school or district guidelines. These guidelines are clearly visible to students, reducing confusion and supporting responsible, transparent AI use, while also encouraging learners to use secure, education-ready Copilot. When: In Preview Q1 Add Learning Activities to Teams Assignments Learning Activities are coming to Teams Assignments and supported LMS platforms in preview, helping educators integrate interactive practice into the assignment workflows they already use. Educators can add activities such as Flashcards, Fill in the blanks, and Matching, and share resource documents that enable students to create their own learning activities within an assignment or the Classwork module. Students complete activities seamlessly within Assignments or their LMS, with progress captured as part of the assignment experience—supporting active, student driven learning while keeping setup, instruction and review in one familiar place. Students can create their own learning activities from educator-shared resources within an assignment or Classwork. When: In Preview Q1 New information literacy features in Search Progress in Teams Assignments Now students don't just gather sources—they investigate them. Four new research prompts (Source Reputation, Factual Importance, Cross-check, Source Purpose) make their thinking visible as they research. Read more about these new features in the preview blog here, and stay tuned for Microsoft Learn course updates to come. When: Available now Add Learning Zone lessons to Teams Assignments and LMS Learning Zone lessons are coming to Teams Assignments and Microsoft 365 LTI for LMS platforms in preview, allowing educators to bring interactive lessons directly into the assignments and grading workflows they already use. Educators can attach Learning Zone lessons during assignment creation, while students complete them fully embedded within Assignments or their LMS, with progress and scores automatically synchronized for review. This preview helps educators save time, reduce manual setup and grading steps, and confidently deliver interactive learning experiences—while keeping assignment creation, student work, and review all in one place. When: Preview in February Embed Learning Activities in OneNote You asked, we're building it. Soon, learners and educators alike will be able to copy a Learning Activity link, paste it into any OneNote classic page, and have it render inline – all to help folks engage without leaving the page. When: NH Spring 2026 5. Create with Copilot in your LMS In addition to supporting the new Learning Zone lessons in assignments, we are adding exciting new Create with Copilot options in Microsoft 365 LTI which bring the AI-powered capabilities of the Teach Module directly into LMS content creation workflows. From within their course, educators can use Copilot to draft lesson materials and other instructional content which is seamlessly published to the course using familiar Microsoft 365 tools. Create with Copilot is also available in LMS content editors to help educators compose content, discussion posts, and more. This includes the ability to modify existing content, if supported by the LMS platform. By embedding the creation experience where courses are designed and managed, Microsoft 365 LTI helps educators preserve instructional intent, reduce context switching, and move more quickly from planning to teaching. Microsoft 365 LTI is available to any Microsoft Education customer without additional licensing. LMS administrators can deploy the integration to an LTI 1.3 compatible LMS like Canvas, Blackboard, PowerSchool Schoology Learning, D2L/Brightspace and Moodle to get started! When: Preview in February 6. Dedicated servers coming to Minecraft Education Minecraft Education is launching a new feature that enables IT administrators and educators to run dedicated servers to host persistent worlds for use in classrooms and after-school programs, similar to Minecraft Bedrock’s dedicated servers (for the consumer version of the game). Dedicated servers enable cross-tenant gameplay, which is a gamechanger for expanding multiplayer experiences in the classroom or running Minecraft esports programs with other schools. This feature is currently in Beta to release in February for general availability for all Minecraft Education users. (Minecraft Education is available in Microsoft A3 and A5 software subscriptions for schools.) ___________________________________________________________________________________ And finally, just to recap all the news we have for you this month, here’s a quick review of all the features that are generally available or are rolling out soon: Teach Module Microsoft 365 Updates for Educators • Unit Plans – available in spring • Minecraft Lesson plans – preview in February • Modify content – align to standards. Private preview now • Modify content – modify reading level. Private preview now • Modify content – add supporting examples Private preview now • Modify content – differentiate instructions. Private preview now • Teach Module integration into OneNote Class Notebooks – preview in spring Microsoft Learning Zone • Available to download from the Windows store, at no additional cost • Provide full classroom ready solution including lesson management and insights • Teach Module, Teams Assignments and LMS integration in March Microsoft 365 Updates for Students • Study and Learn Agent – preview in late January • Learning Activities – Fill in the Blanks generally available • Learning Activities – Matching Activities in private preview now • Learning Activities – Self-quizzing available in private preview in February Teams and OneNote EDU Updates • Set expected AI use in Assignments – private preview end of January • Add Flashcards to Assignments – private preview in February • New information literacy features in Search Progress • Embed Learning Activities in OneNote – private preview in spring Copilot in your Learning Management System Dedicated Minecraft EDU servers Have any feedback to share with us? As always, we'd love to hear it! Mike Tholfsen Group Product Manager Microsoft Education
Database in an "inconsistent state" and other errors
Hello. I was directed here for help by Microsoft Tech Support because the problem is "file specific" and not at the app level. I am not computer savvy and have never used a forum like this. Tech support just kind of dumped me here and said "have at it," so any guidance, help, etc. would be appreciated. I use Microsoft Office 365 as a personal/individual subscriber and my Access program started issuing error message over the past week. The biggest concern and problem is around db called Comic Books and I've attached the error message. When I click OK, it attempts to repair, fails and issues the second message. Another database--an earlier copy of Comic Books from mid December-- opens OK, but has its own problems, such as when I attempt to simply copy a table (see third error message). I don't know if these are related. Can anyone help me? Is there any hope for me ever regaining access to the Comic Books database which was last accessible and working on January 4? Thank you.Help with Excel Sorting
Hello! I am looking for help with sorting data on a sheet with form responses to different excel sheets. I want to filter by column G to different sheets ("Near Miss", "Adverse Event", "Sentinel Event". The data automatically goes to sheet1 via form responses, but I am trying to streamline the data into specific types of events. I'm using office 365. TIA!Outlook for Mac missing ToDo
Since a recent update, our Mac clients don't have any way to view ToDo or Tasks in the New Outlook. The tasks don't show in the My Day/Task pane, nor do we have a ToDo Icon ion the left sidebar like users of New Outlook on Windows do. Accessing OWA is now the only way for users to see tasks/flagged emails, but very much not the preferred solution since there IS a desktop client which should do that. Microsoft® Outlook for Mac Version 16.105 (26011018) This is a serious usability issue, is this really a design decision or some sort of an issue?
