unpivot data and handle merged cells without using Power Query (Unpivot_Toolkit)
Hey, guys!! I’ve been working on a set of functional Excel Lambdas to solve a common headache: transforming "Wide" human-readable data into "Long" database formats without having to open Power Query every time. =UNPIVOT_PLUS(table, [no_of_cols], [no_of_headers], [attribute_names], [value_name], [remove_errors], [remove_blanks],[pad_blanks_with],[pad_errors_with]) Don’t worry about the full list, most use cases only require 5 arguments. I've included a table of default values at the end. Merged Cell Support: Automatically handles fill-down/fill-right logic for merged headers/columns. Bonus Helper: SPLIT_INJECT =SPLIT_INJECT(array, target_indices, delimiter, [ignore_empty], [match_mode], [pad_with]) It targets specific columns, splits them by a delimiter (like TEXTSPLIT), and expands the entire table horizontally while keeping all other columns perfectly stable. Optional arguments match TEXTSPLIT defaults. Feel free to tear this apart or adapt it for your own edge cases. I’d love to hear how you end up using it! You can grab both functions from my GitHub Gist https://gist.github.com/Medohh2120/f8553c149684e39bb499249e39f01017 File with use cases https://1drv.ms/x/c/6c310c8fd1669a94/IQCscpo3yh7jR5XdGZe2AQGyAf4-vCd8K6BvLZQgrP2V8Oo?e=gPdbOd Argument Description Default Behavior table The array or range of data to unpivot. Required argument (no default) [no_of_cols] Fixed left columns to keep as identifiers. 1 [no_of_headers] Top rows used as headers, handling merged cells. 1 [attribute_names] Header name for the unpivoted attributes . "Attribute" [value_name] Header name for the unpivoted values. "Value" [remove_errors] Excludes grid rows with formula errors. FALSE [remove_blanks] Removes grid empty cells and empty strings. TRUE [pad_blanks_with] Value to substitute for empty cells. Leaves cell blank [pad_errors_with] Value to substitute for errors. Leaves error as-is
Sheet View Issues with Excel Web Browser
Hi, I have created a Excel document for my department to use, I tried Excel App but due to the size of the Spreadsheet it kept freezing, as a result I switched it to use Excel through Web Browser. The sheet no longer freezes which is good. I do have another issue though.....I wanted multiple users to be able to access, edit the sheet at the same time so I did some research and Sheet View seemed to be the way forward. I created a Sheet view for each employee to use so when you go to View, Sheet View and click on the relevant person their work is updated, saved etc and this shouldn't affect others view. This is working in terms of people editing. The main issue now though is that despite following advice sometimes when someone changes a filter on their own sheet view it seems to change others view. My understanding was you can hide, filter etc within your own Sheet view but this doesn't seem to be the case.......PLEASE HELP!!!! My excel and computer Skills aren't excellent to please any simple advise would be great.
Disable Auto-Fit/Auto-Wrap on Web?
About a week ago Excel rolled out a new feature to me that offered to auto-fit my cells whenever needed. Not knowing what it was, I accepted it and turned it on. Now, I want to turn it off and cannot find it anywhere. I've tried following https://support.microsoft.com/en-us/office/undo-automatic-formatting-in-excel-54eba206-110c-445a-89f1-c4eb67a36bd4?showContactUsNav=False&ns=EXCEL&version=90instructions, but these instructions are for the app, not web, and the issue doesn't replicate on the app. Below in E46 is an example of the issue. I want to paste links without them auto-wrapping my text or auto-fitting the column. I have tried turning wrapping on and off again in this column, but it doesn't work. I could manually disable the wrapping every time I enter a link, but I would much rather just turn this feature off.
Revenue structure
File Structure Reference Column A contains Client Names (e.g., A, B, etc.). Row 2 (starting Column B onward) contains month-end dates from Jan 2026 through Dec 2027. Columns B through the last month column contain monthly revenue values. The final column is the Total column (annual total calculation). 1. Termination Logic If a client terminates in a specific month: Revenue should appear normally up to the month prior to termination. In the termination month, revenue should become zero (or stop, based on effective date assumption). All future months (including months in the following year) must remain zero automatically. The annual Total column must update correctly based only on active months. The model must not require manual zeroing of future months — this should flow automatically once the termination month is specified. 2. Fee Update Logic If a client’s fee changes in a specific month: The revised fee should apply only from that month forward. All prior months must remain unchanged. The increase should not assume a uniform annual escalation. The updated fee must flow correctly into the next year without altering historical data. For example: If the fee increases in July 2026, then: Jan–Jun 2026 remain at old rate. Jul 2026 onward reflects the new rate. 2027 months continue with the revised rate unless changed again. It will be great if the formula works on Google Sheet as well.
Validating & Populating Cells with dates based on a Date Picker
Hello, we are working on a really simple delivery schedule for Monday thru Friday. The top row has a cell that is formatted as a short date picker. In choosing a date in that cell, I would like the dates of the 5 numbered cells below for each day of that week to be updated based on the date chosen. For instance, I chose 2/1/26 in the date picker. I would like cells A2, H2, O2, V2, and AC2 to correlate to that date number and automatically populate the proper day of the month. If possible, it would be great if the month names that are in cells D3, K3, R3, Y3, and AF3 would also correlate and update properly. Any help would be most appreciated, I attached a screenshot to show the layout. Thank you very much in advance.Excel sheets that populate a drop down menu
Hello Is there a way to have a drop-down list of all the sheets in a workbook in a selected cell and the selected sheet from the list's data populates other cells in the first sheet? My goal : To easily see what machines are connected to a list of RJ45 wall points. In the workbook, there is 1 sheet that has a list of wall points . Each machine has its own sheet. Each machine sheet has lots of corresponding data that is not pertinent to the RJ45 wall point sheet. My goal is to select a cell next to a given wall point row in the RJ45 wall point sheet, that drops down a list of machines (that are all the machine sheets in the workbook). The selected dropdown list machine, will populate pre-defined adjacent cells with corresponding data from the selected machine sheet. Each cell in the MACHINE NAME column has a drop down list of the sheet names to select from. Once a machine is selected, the hardware names and model columns that are on the same row are populated with the corresponding data on the machine sheet. I have asked ChatGTP and Gemini AI to help ... without any success. Is this possible to do? Thank you
Help needed with IF and COUNTIFS Formulas
Is anyone able to advise the following formula: =COUNTIFS($B$5:$B$15,$R$4,$C5:$C15,"<=" & V3,$D5:$D15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$G5:$G15,"<=" & V3,$H5:$H15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$K5:$K15,"<=" & V3,$L5:$L15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$O5:$O15,"<=" & V3,$P5:$P15, ">" & V3) Is there a way to simplify this? Is there a way to make this more accurate? Cells in column G & H, I & J, O & P are using the following format: =IF(C6="","",C6+E6) Cells in U4:CC4 are using the following format: =COUNTIFS($B$5:$B$15,$R$4,$C5:$C15,"<=" & U3,$D5:$D15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$G5:$G15,"<=" & U3,$H5:$H15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$K5:$K15,"<=" & U3,$L5:$L15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$O5:$O15,"<=" & U3,$P5:$P15, ">" & U3) Cells in U5:CC15 are using the following format: =IF(U$4>=$T5,1,"") My issue is is when I put in the three break times, the mid break comes out at a shorter time. My other issue is is that when I put in the times in row 5,6and 11, the data is coming up as a combined data in rows 5, 6 and seven on the page two. Just for reference, "page two" is the same spreadsheet. What I need to happen is that I enter in the shift start time and finish time. This then populates through to Break 1, 2 and 3. The Time entry is the time the break starts. ie: 1 hour after start of shift, 1 hour after coming back from break, etc. The break entry is the duration of the break taken. ie: 30 minutes. Once all the info is put in, the relevant "Time Block" on "Page 2" shows a 1. What is happening at the moment is that when I enter all the time data, the time blocks are not populating correctly in accordance to the entry. Basically, If I have numerous people on shiftI need the time blocks to show where I have shortfalls in shift cover and not having too many people on break at the same time. IE: Link to Live Copy: https://www.dropbox.com/scl/fi/eur1j526htu1j8a4d4290/Staff-Breaks.xlsx?rlkey=r4tm9xts4tonofpa2th2cusfw&st=nueyk0d7&dl=0 Any ideas would be greatly appreciated.Cell drop list of workbook sheets
Hello Is there a way to have a drop list of all the sheets in a workbook in selected cells and the selected sheet from the list, populates other cells in the first sheet? I want to easily see what machines are connected to a list of RJ45 wall points. In the workbook, there is 1 sheet that has a list of wall point and each machine has its own sheet. Each machine sheet has lots of corresponding data that is not pertinent to the RJ45 wall point sheet. My goal is to select a cell next to a given wall point row in the RJ45 wall point sheet, that drops down a list of machines (that are all the machine sheets in the workbook). The dropdown list selection will populate pre-defined adjacent cells with corresponding data from the selected machine sheet. Each cell in the MACHINE NAME column has a drop down list of the sheet names to select from. Once a machine is selected, the hardware names and model columns that are on the same row are populated with the corresponding data on the machine sheet.
