[DataFormat.Error] We received a malformed web response and credentials issue
Hi team :) Happy Friday !! There is an excel spreadsheet being connected with data sources and shared with users. Upon, hitting data refreshes it asks every user to edit credentials every time they hit data refresh or shows dataformat.error with malformed web response. There is no data format error though. Can anyone please assist here or know how to solve this issue ?? Thanks Happy
Excel 365 Sheet Views are not working
I have 7 different Sheet views in a shared document, they were working fine until about a month ago. Since then I have tried everything, I deleted them, created them again and they don't work. When I create them they look fine but once my collaborators enter to work on the spreadsheet all the views start showing the default view. The sheet has the black border and the eye in the tab. How can I solve this
combine multiple text into single line of text
Hi is there a way to combine all the cells into a single cell (of text) ignoring the duplicates/repeat values. i know concatenate but that function becomes very lengthy as ill have to define every cell into the function individually. is there any other function where i simply select the entire data (like an array) and it can do the required.Excel Pivot Table shows all records but one row is displaying blanks for some columns
Hello, My Excel pivot table returns all rows that should be returned according to filters applied directly to column filters in the pivot table itself, or via some Slicers that I've created off of this pivot table. For 2 particular Business Units, there is one row (again, that is properly returned as a row in the filtered pivot table - i.e., no 'missing' records) that shows values in the first 4 columns of the pivot table, but does not display the values in the subsequent 9 columns, despite the fact that the data is fully populated in the Source data. The one source field I've added to the 'Values' section of the pivot table (Report Count) does display values as expected. Troubleshooting tried so far: I've ensured that my Named Range data source is properly capturing the entire source dataset - i.e., no dataset rows or columns left off I've refreshed my pivot table numerous times after changing filters directly in the pivot table itself, as well as changing the filters in Slicers built off of that pivot table. In the Pivot Table Options, on the 'Data' tab: the 'Number of items to retain per field' is set to 'None'. And the 'Save source data with file' check box is not checked off. I've deleted/re-added the source data for the problematic, 'invisible' pivot table columns (not showing the data as expected) and refreshed the pivot table, but that did not correct the issue. I inserted a blank row adjacent to 1 of the 2 problematic records in the source data and copied the data from the problematic record into it, and deleted the original record and refreshed my pivot table, which also did not remediate the problem. I've also built another pivot table in the same workbook on a different Excel worksheet (using the same Named Range data source) and added all the same fields for one of the problematic records and they are all displaying normally. The only error message of any kind that is generated is when I go to one of the problematic records in the pivot table itself, select one of the cells in that row that is displaying a blank and then try to click on the Formula bar - the error message is, 'We can't change this part of the pivot table'. But, I believe this is a normal message if Excel senses that you're trying to update data in a pivot table. There are no error other messages or difficulty refreshing to report. Other relevant points: All told, I have 239 total rows in my source data, and all rows appear normally in the pivot table with the exception of these 2 problem records. There have been no other performance/display issues of concern outside of this one. This Excel workbook is mainly utilized on SharePoint by multiple Users, but for more complex updates/troubleshooting, I am easily able to bring it down to work in Excel Desktop as needed - and this problematic display with these 2 records is present regardless of whether I'm working with the file on SharePoint or with Desktop Excel. I can't think of what else to try at the moment - I've never seen anything like this in my 25+ years of working with Excel. I hope I'm missing something simple - any troubleshooting suggestions are greatly appreciated!Unwanted format changes on Excel worksheet in SharePoint
Hello - I've been working successfully with an Excel worksheet (.XLSX extension) that is published to SharePoint and has multiple Users viewing/updating data on this worksheet every day. Today, columns on this worksheet keep reverting back to varying widths (some way too wide and others way too narrow) despite going into the worksheet, re-formatting the column widths and saving the document. Other worksheets in the same workbook so far do not exhibit this behavior and are maintaining the desired column formatting (i.e., proper widths). I cannot figure out what is causing this unwanted formatting to happen and it has not happened before. I'm guessing this is an Excel issue, but might the SharePoint component be a factor as well? What are the potential causes of this, and what can I do to make the desired formatting remain and not revert back to the unwanted formatting? Thank you!
Sorting data on multiple sheets
Hi, I'm using Excel to track data from my team for analysis. I have 5 sheets in my workbook one for each week and a monthly overview. each week totals all data and totals are added to overview using SUM function, however when i sort a column, let's say i sort column a (Name) A-Z, all the data for each person on the monthly report changes. the same happens on the weekly sheets if i change the order of a column everything get mixed up so that each persons stats change. is there a way to stop this from happening? I want to be able to arrange it so that all the data in a row stays the same on each sheet
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.
