How to Be an Excel Detective: Finding and Highlighting Formulas
Hi everyone, I recently wrote a blog post on some simple, yet powerful, techniques for anyone who works with Excel spreadsheets, especially those with complex data. I wanted to share a summary of it with this community, as it might be helpful to others who are looking to understand and protect their work. The post covers two main things: Quickly Revealing All Formulas: A simple keyboard shortcut (Ctrl + `) or the "Show Formulas" option can instantly reveal all formulas in a worksheet. This is a great way to quickly see how a spreadsheet is structured. Permanently Highlighting Formulas: The article shows how to use the "Find & Select" > "Formulas" feature to select all cells containing formulas and then permanently highlight them with a fill color. This visual cue can help prevent accidental edits and protect your data. Watch This in Action: For a step-by-step guide on how to use these techniques, you can watch the video on my https://www.youtube.com/@BIGurus. 🔗 https://youtu.be/1x-1dbqlWXk You can also read the full article here: https://medium.com/@anandsharad/how-to-be-an-excel-detective-finding-and-highlighting-formulas-fe9d4fdbc1b1 I'd be happy to answer any questions you have or discuss other Excel tips and tricks in the comments!
OLAP connection Open XML adds x: namespace which is preventing slicer to work
I serialise the excel workbook with C# OpenXml to dynamically change the connection.xml file so users can connect to olap cube tabular in pbi. So I programmatically set a connection string for the user this way. All was working fine until the user started to insert slicers to the pivot table. I noticed in the connection.xml the connections xml tag has x: namespace added by OpenXml. eg <x:connection></x:connection> According to doco "When the object is serialized out as xml, it's qualified name is x:connection." https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.connection?view=openxml-3.0.1 Once I removed these x: namespace from the connection xml tag slicers worked. <connections><connection></connection></connections> Is this a bug in excel ? Are there plans to fix this? OpenXml is a lib provided by Microsoft and adds the x: namespace and excel cant handle the namespace, Reporting this bug and to see when a bug fix will be release for excel to use slicers when you dynamically change the connection string using openxml. Thanks
Downloading multiple files from excel hyperlinks
Dear experts, Could you please share any Excel formula or PQ solution to download multiple files from excel hyperlinks. Each cell have one hyperlink and there are total > 100 hyperlinks, it's difficult to download each file one-by-one. Thanks in Advance, Br, Anupam
Power BI DAX measurements
Hello Power BI users. I am trying to create a measurement that gives me the top 10 providers experiencing patient's leaving against staff advice (ASA). The date that I am trying to create this measure lives in two separate tables from two different sources. I've connected these two tables using a date table to connect the two tables. In the ASA_Ali_1 table there are multiple programs with dates listed within each row. These dates include program capacity which will need to be used in the calculation. (of note, the ASA_Ali_1 table does not contain case by case basis information but a summation of ASAs that have occurred for each date corresponding to a specific program). In the CLEAN_ASA table, contains case by case i.e., client leaving ASA and the provider that was working with said client at the time of their departure. I need to use the program capacity to generate a relative number of ASAs of a specific provider (contained in CLEAN_ASA) against the average program capacity (contained in ASA_Ali_1). I would like the output to show me the top 10 providers experiencing ASAs for the most recent month. Here is an image of the two data sources being used so you can get an idea of the structure. To summarize, my outputs columns in report view should show the provider, the number of ASAs they have experienced in the most recent month, the number of ASAs in the most recent month, and the percentage of ASAs relative to that program that provider was working in relative to their respective program's capacity for the most recent month. (please note that the providers program they are assigned to work [within the CLEAN_ASA table] in is titled , "Housing location," and in Ali_ASA_1 this is identified as "Program"). Please let me know if I can create DAX measurements within both tables to generate this output. Any help would be greatly appreciated 🙂Shift cells up
Dear Experts, I have a data like below:- and want to delete all the nulls,( shift) all the empty cells up, I can do this using F5-> special , select blank and delete , but with huge data it Crash:- Any solution which is less heavy and not cpu hungry that can do this job? any M code , or PQ UI steps? I tried converting each row in to list and then List.RemoveNulls etc but didn't help.. Attached excel for Reference Thanks in Advance, Br, Anupam
Table from JSON object
I get the following JSON response from an API call [ [ { "field": "ID", "value": 29 }, { "field": "Created at", "value": "06/08/2024 15:18" }, { "field": "Created by", "value": "Amanda" }, { "field": "Job Card Status", "value": "Final" }, { "field": "Sales Amount", "value": "2500" } ], [ { "field": "ID", "value": 28 }, { "field": "Created at", "value": "06/08/2024 15:16" }, { "field": "Created by", "value": "Amanda" }, { "field": "Job Card Status", "value": "Final" }, { "field": "Sales Amount", "value": "15400" } ] ] and need to create a table in Power Query to create the bar chart in Power BI. The bar chart shows 'Sales Amount' by week. The x-axis represents weeks and the y-axis aggregates 'Sales Amount' for a given week. Using the API call I'm getting this 2 column table and with my limited Power Query skills I can't transform this table to the target table with the columns ID, Start Date and Sales Amount which I then can use to create the bar chart. How do I approach this?A Little Help from my Friends
I am an advanced (expert) in Excel. I've used it for over 20 years everyday and have thousands of education hours under my belt. I would like to get some ideas on how some of you would approach this complex situation I have. I have already created three different methods but each have too many draw backs or failure points. With that said, I'll try to be brief but detailed enough for your thoughts. I have a file for which I'll call the Master-File it has a list of parts, suppliers, prices by month with Forecast and Actual. Current and Future months are forecast, previous months are actuals. I have 6 people that send me, what I'll refer to as Sub-Files. These subfiles contain the same headers as the Master-File. In the Master-File previous months data cannot change. (Ex. If reporting was done today 7/14 Actuals for June would be imported as Actuals and the remaining July thru December would be Forecast updated with the latest forecast data, which is already included in the sub-files). Months are listed left to right in columns not rows. Also, those sub-files can contain new parts that are required to be imported into the Master-File. My first solution was to use Power Query and import the Sub-files each month from a folder into the Master-file. Problem with that was, data from previous months would change in some of the Sub-Files and would over-write the data. (This data can't change once locked in). My second solution was to utilize a Unique ID for each record where I could easily identify new parts first and import that data by manually copying and pasting the data. Then sort the data using a match formula so the records in the Sub-file matched the Master-file and then manually copy and paste in the Actual and Forecast data. Some data was not correctly matched, so I started using XLOOKUP to import the data in the Actual and Forecast. Then, copy and paste as values. With 6 different files the table would at times over-write the data in the Actual forecast fields causing errors. Even after turning off auto table formula update option. Plus, this was very time consuming. Now I am starting over. I have some other ideas but if two heads are better than one then many heads are even better. I would love to hear your thoughts on how you would go about handling this process.Excel Not Refreshing Data from Power BI Semantic Model (Live Connection via XLMA)
Hi everyone, I'm trying to create a fully automated reporting process by connecting an Excel workbook directly to a Power BI semantic model using the XLMA endpoint (live connection). The goal is to have Excel always reflect the latest data from Power BI, without needing to manually export reports or paste tables. I’ve written a script that opens and refreshes the Excel workbook each morning to ensure everything stays up to date. The problem: Even after running the refresh (via script or manually in Excel), the data doesn't actually update. It continues to show outdated values, even though Excel successfully connects to the latest version of the semantic model in Power BI. I've already confirmed with my Power BI team that the semantic model is working correctly and refreshing as expected. Their suggestion is that it's an Excel-side issue. Details: I'm using Excel for desktop (latest version) with a Power BI Pro license. The data is pulled via "Get Data, From Analysis Services" using the workspace connection URL. There are no errors during refresh, but the values don't update. Later on, I sometimes do get error messages but not always and never during the initial refresh. The message is along the lines of: "We couldn’t get data from the external data source. The (blank) session ID cannot be found. Either the session does not exist or has already expired." If I manually rebuild the pivot table, it does pull the correct current data, but that defeats the purpose of automation. Has anyone encountered this issue? Is there a known limitation or workaround for ensuring Excel truly refreshes the live connection to the semantic model? Appreciate any insight or suggestions! Has anyone encountered this issue? Is there a known limitation or workaround for ensuring Excel truly refreshes the live connection to the semantic model? Appreciate any insight or suggestions!
How to automate a login and refresh of a spreadsheet via an Excel plug-in?
Hi. I have a requirement to automatically refresh a spreadsheet's data. I'm an Excel novice and have no idea how to do this automatically. To refresh the data in a manual fashion, I perform the following steps: 1) Open the Excel file. 2) Click on the iLEVEL menu option (it's on the top amongst other menu options such as 'Data', 'Review', Automate', etc..). This will present an iLEVEL login icon. Click on this icon and then enter my email address and password. In fact, once I've entered my work email, it uses SSO to gain access instead of entering a password. I do have a password should I need it (needs to be entered somewhere). 3) On the toolbar ribbon, under the iLEVEL menu option, there is the 'Refresh All' option. I click on this, wait a few minutes, and the spreadsheet has its data refreshed. iLEVEL is a third-party software solution. What I need to do is to achieve the above result automatically. At, say, 3am each morning, I would like automatically refresh the data. The updated spreadsheet will then act as the data source for our reporting. In case it's asked, at this time, we cannot look to hook into the view/table that feeds this spreadsheet. Therefore, the spreadsheet is the endpoint. I will investigate this but if you are aware if this can be done or not, and point me in the right direction, it will be appreciated. I'm wondering if Power Query, or something similar, could achieve this. Thanks.
