Incapable of Creating a Manual Line (Threshold) on a Box and Whisker Chart
I am seeking information on how to manually add a line (a visual threshold line for my need) to a box and whisker charts. To summarize my data, I have 4 independent variables with 2 metrics that are measured on a percentage scale (Figure 1 below). I am trying to add threshold lines to visualize a pass/fail (Figure 2). I have used several suggestions about combining charts and copying and pasting scatter plots. Nothing is working as intended and the simplest solution is simply to insert a line on top of the chart. However, this does not integrate it into the chart and I cannot add it to the legend. Is this realistically the only way to get what I want? I am not familiar with macros or VBA, but this seems to be a function that other users could use. I would use this function on other box and whisker plots with varying number of variables, too. This chart style was chosen for the most efficient way to present the data. I included a glimpse of how the data is setup (Table 1). Any ideas are welcomed.Default blocking of macros ???
According to this flowchart: Macros will be enabled IF the document is located in a trusted location ! But it's NOT the case ! I have set my Onedrive as a trusted location and I still have my macros blocked ! My OneDrive have a COPY of a shared folder from other person and this setup WORKS nicely under Windows 10 but NOT under Windows 11 ! It's as though the new default CAN'T be overwritten ! I cant figure out what I have to do from the article where the flowchart is taken from ! What do I have to do more under Windows 11 ?New in Excel for the web: Power Query Refresh & Data Source Settings for authenticated data sources
We’ve reached yet another milestone in Excel for the web: Power Query Refresh is now generally available for queries sourcing data from selected authenticated data sources. As we released the ability to refresh Power Query data from anonymous data sources (link), it was only a matter of time until we added the ability to refresh Power Query data from authenticated data sources, which are the majority of data sources used, and require users to enter credentials. This milestone also enables us to release Import with Copilot to Excel for the Web (following Win32 and Mac), as it relies on Power Query for refreshing data. Getting started These new functionalities are available to all users on Excel for the Web. See this support article for more information on Power Query data sources in Excel versions. efresh a data source in Excel for the web using Power Query Refreshing Power Query queries You can now refresh the Power Query queries in your workbook that source data from a selection of authenticated data sources: Select the Data tab > then choose Refresh All Open the Queries Pane > then select Refresh When you refresh a query, if authentication is needed, you can select the relevant method – anonymous, user and password, or your organizational account. For example, to refresh organizational data, select the respective method: Your user will be automatically identified (you can also switch it, if needed), so you can easily click “Connect” to continue the refresh process. The list of supported connectors includes: SharePoint* files (Excel workbooks, TXT, CSV, XML, JSON, PDF) SharePoint* folders SharePoint Online List SharePoint List SQL Server Database OData Feed Web API IBM Db2 Database PostgreSQL Database Azure SQL Database Azure Synapse Analytics Azure HDInsight (HDFS) Azure Blob Azure Table Azure Data Lake Storage Gen 1 Azure Data Lake Storage Gen 2 Azure Data Explorer Dataflows Dataverse Microsoft Exchange Online Dynamics 365 (Online) Salesforce Objects Salesforce Reports *SharePoint/OneDrive for work or school Note: Refresh is limited to 1000 connections per user. The refresh happens behind the scenes so you can keep editing the workbook while refreshing. Managing queries using Data Source Settings You can now view and manage data source credentials for the Power Query queries in your workbook using Data Source Settings: Select the Data tab > then choose 'Data Source Settings’. Choose between ‘Current Workbook’ and ‘Global Permissions’ to view and manage data sources credentials in the current workbook or across all workbooks, respectively. To delete the credentials stored for a data source, click on the ‘Delete’ button. To edit the credentials stored for a data source, click on the ‘Edit credentials’ button. In addition, we’re introducing a new functionality in Data Source Settings – authenticating to a data source that exists in the workbook from within the dialog: Select the Data tab > then choose ‘Data Source Settings’. Navigate to ‘Current Workbook’. Click on the ‘Add credentials’ button: What’s next? Future plans include releasing the full Power Query Editor experience to Excel for the Web. Feedback We hope you like this new addition to Excel and we’d love to hear what you think about it! Let us know by using the Feedback button in the top right corner in Excel - add #PowerQuery in your feedback so that we can find it easily. Want to know more about Excel for the web? See What's new in Excel for the web and subscribe to our Excel Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on Twitter. Jonathan Kahati, Gal Zivoni ~ Excel Team
best way of returning data from a table
Hi I have a worksheet showing a materials list column headers include Job Type, Item number, description, purchase cost, quantity, price, vat Would like to be able to have a separate worksheet where you can enter a 'job type' and have all the materials that are related to that job type pulled through and displayed. Some materials are used for multiple job types though. What would be the best way of doing this? Have tried the FILTER function which works to display the correct materials but requires changing the formula and can't work out how to use this from data entered in a cell. Can anyone help please?
Dynamic arrays across workbooks
Hello everyone, I’m new to dynamic arrays and I find them incredibly powerful! I noticed a note saying that dynamic arrays are only supported when both workbooks are open. However, I’ve found that the data still appears even when the source workbook is closed. For example, I have dynamic arrays in Workbook A, and I use an XLOOKUP in Workbook B to retrieve data from A. When Workbook A is closed, nothing seems to break. So, in what situations are dynamic arrays not supported across workbooks? Thank you very much!
Progressbar in Excel (VBA)
Maybe I'm trying to do the impossible. Who knows ? I've made a vba uerform to do several things (too long to explain) but what I would like to do is while the progressbar is running to have a small text appear from under the (now) dark blue progress. So it runs from left to right, at a certain point I would like some text to appear letter by letter as the bar gets longer. I tried right clicking on the progress bar and moved it to the back and moved the text to the front. Result is that text stays behing progress bar. Hope this is clear, and hope to see if anyone comes up with a solution. Thanks in advance, Andy
How to turn one cell red if there is a red cell in another cell - but for an entire column of rows.
Hi there, I am trying to determine how to turn one cell red in a column, if there is another red cell in that same row, but for a series of columns. Here's more context to better explain. I am doing chart audits for healthcare. I have one column (A) with patient names list on separate rows, and then a series of columns I-P) with items that need to be completed in the chart - columns I through P. I have already set up conditional formatting for the series of columns where if a value of the cell says "No" (aka the item is not complete in the chart) that cell turns red. I would like the patient name (a single cell in column A) to turn red if any of the other cells in that row are red. I believe I can create this using conditional formatting rules. But there will be up to 100 rows and it seems there must be a better way than setting up the rules row by row which would require me to create this rule 100 times. Is excel able to do this? Thanks in advance!
