I have about 350 data points for values of stress and corresponding values of strain. Strain is in column A, stress is in column B.

I want to simplify this data to about 50 data points. I picked the stress values manually and they are stored in column E. I want to get the corresponding values of strain in column D.

I'm utilizing XLOOKUP function, which is working correctly if I have the data in a google sheet. Will link it in comments.

The same thing formula/function does not work in Excel. I would like to understand what I'm doing wrong.

I posted a similar query before that was deleted by the mods. The problem was related to floating point errors. I used ROUND(column E values, 1) but this does not seem to solve the problem.

TIA

Hi everyone,
I have a question about Excel and Power Query.

Imagine you are given a task at work to analyse an Excel dataset containing hundreds of rows. You need to calculate the total sales amount and the number of customers who purchased a particular product at a specific branch, then summarise the results in a single table.

The dataset contains the following columns:
- Branch (UK city names)
- Product
- Customer ID
- Purchase Date
- Amount

For example, I need to filter the data to show purchases of laptops at the New York branch, then calculate the total sales amount and the number of customers who purchased them.

Some customers may have purchased the same product multiple times. Each customer should only be counted once in the customer count, but all their purchases must be included in the total sales amount.

Ideally, I would like the final result to appear in Excel as a summary table like this:
Branch | Product | Number of Customers | Total Sales
New York | Laptop | 3 | $2,100

What would be the easiest way to do this in Power Query?
I would prefer to use the Power Query interface without writing any M code, if possible.

Thank you 🥲

Hi Everyone,

Currently attempting to write a formula but several iterations are throwing errors.

Trting to fit the following into one cell:

1. If A1 is less than 50 or more than 100, execute formula 1, else execute formula 2

2. Formula 1 and 2 include B1*C1, but also rounded to two digits and with a maximum value

Finally got an iteration that didn’t throw errors, but the current version’s formula doesn’t function properly

=(IF(AND(A1>=100,A1<=50), (MAX(3000,ROUND(B2C2,2),(MAX(6000,ROUND(B2C2,2))))

Error is that the formula doesn’t change to max -3000 if A1 is outside the parameters(A1<=50 or >=100)

Thank you

As a professor in the humanities at a large university, I often have to handle large spreadsheets of data to report grades in multiple sections of my classes. I know the basics of how to use Excel when I download grades from a third party (the "Top Hat" in-class response system) and then upload the resulting .csv file to my university's LMS (Moodle). The problem is that when I upload the grade files (more than 300 students' scores on Attendance and clicker-system responses), certain errors are popping up: For instance, some students have been marked absent for dates on which the original grade record from Top Hat clearly shows they were present. I suspect that the error is appearing in the final stage of the process: i.e., after I have downloaded the grades from Top Hat and modified the contents of the .csv file (editing out irrelevant fields, making sure all students' IDs are correct, etc.), and then uploaded them using Moodle's Import function.

The university's IT people have tried to help me (they say they can see nothing wrong with my process for downloading/uploading); but they haven't yet pinpointed the source of the errors. I can easily change incorrect grades manually within Moodle, but to do that I need a way to hunt them down across hundreds of fields of data. It has occurred to me that, as a stopgap measure, I can download a .csv file containing the grades that I have just uploaded, and then compare it to the the relevant columns of grades in the original spreadsheet (before the errors were introduced). This could help me track down wrongly-recorded grades and correct them, at least until my IT folks can fix the upload issue.

Hence my question: I imagine there must be an easy function within Excel to make sure the contents of a specified column in one spreadsheet match those of a corresponding column in a different one. I am not good at reading software manuals, which is why I lay this before this helpful community. Thank you for reading this!

I have a directory with over a thousand image files in it. They all share the same dimensions and use the .jpeg extension. I would like to import the images in alphabetical order into an Excel sheet starting with image 1 in row 1, column A, image 2 in row 1, column B, image 3 in row 1, column C... until it gets to image 8 in row 1, column H. Then move to image 9 in row 2, column A, image 10 in row 2 column B and so on until all the images are in the Excel sheet. I want to limit the width to 8 columns because that is the maximum number that will fit on 1 printed page with the box sizes I have chosen (112 pixels wide x 166 pixels tall).
I have used Excel's built in Insert Illustrations option and it works great if I was to import 1 image at a time, but that would take days to import all the images manually. I have attempted to import multiple images with Insert Illustrations but Excel imports them into columns instead of rows. If I could tell Excel to import the selected images into a row instead of a column, I would be willing to do that.
Obviously, my top choice would be a macro that would import all the images in the directory into Excel going from A1-A8, then B1-B8, then C1-C8... until all the images in the directory are in the Excel file. Any assistance would be appreciated. Thank you.

Suppose I have this data in A1

Data String Here

I want to convert it to

="Data String Here"

How do I do that? Is there a quicker way that putting =" and " on the end manually? I am currently crunching 500 row data, maybe there's a shortcut you guys are familiar of.

EDIT: The main problem is that I want to preserve the original text and its original cell position. I literally just want the row cell data to be a Text String Formula. I instead created a powershell script to automate this process. Thanks guys.

I am at a loss as to how to do this. I'm trying to capture the number of cases of a disease at a given point (per elapsed week since an outbreak was declared), and paste that number into another table.

My data source is cases per day:

And I want to copy only the data that falls on a completed week (second column) into a new table which captures only weekly cases. As below (currently done manually):

Many thanks!

I’m trying to maintain an Excel file using the Google Drive for desktop app so multiple coworkers can access and edit the same file, including people who prefer Excel and people who prefer Google Sheets.

Here’s what’s happening:

• I originally created the file in Excel on my desktop.
• I saved the .xlsx file in Google Drive for desktop so it would sync and be accessible from other computers.
• When I open the file through Google Drive in a web browser, it remains an .xlsx file and opens in Google Sheets. The formulas work, and the formatting appears correctly in the browser.
• However, after the file has been opened or edited online, when I go back to the synced .xlsx file and open it in desktop Excel, much of the formatting is gone or changed.
• I then have to manually reformat the Excel version.
• This seems to happen repeatedly after the file is opened or edited in Google Sheets.

The goal is to keep one .xlsx file synced in Google Drive while allowing some users to work in desktop Excel and others to work through Google Sheets in the browser.

Is there a reliable way to do this without Google Sheets altering the Excel formatting? Is Google Sheets actually modifying the .xlsx file when it is opened or edited, even though the file is not being converted to a native Google Sheet?

I’m trying to determine whether this is an Excel/Google Sheets compatibility issue, a Google Drive syncing issue, or whether this workflow simply is not supported reliably long term.

Any guidance or alternative workflows would be appreciated.

Afternoon all,
I'm trying to use SUMIFS to pull data from one big sheet with all the info, in to more manageable bites for a reviewer. Problem is, I want them to be able to sort the bite-sized work plans, but when I test that by simply sorting, the actual cells I'm referencing jumble; they don't move with their respective rows. Locking doesn't really fix anything, because obviously if I sort, it's all different now. The data in the reference sheet can change and be added to, and I would prefer these work plans auto-update, so Ctrl+Shift+V can't help me here. I've included some screen caps to explain better.

So im trying to make an ongoing changing table. For the table info I need file folder names, not the info in the folder just the folder name. The drive current looks like.

D drive

-main file

--sub file

--sub file

--sub file

So I need to make a table of the sub file name. Any help on how to do it. There are over 3000 files so I dont want to type each name individually.

Thanks

I have a pivot table which has grouped rows. When new daily data is added to the backing table, and the pivot table is updated, the group for the newly created date becomes expanded. Only the row with the newly added data. I can collapse the row and the next day upon refresh it will not expand...only the next newly created row. Any suggestions to stop this from happening? It's not the expand columns option.

So I was analyzing some data in my Excel (2019) Professional Plus/Win10, and naturally setting up equations and then copy/paste to apply to the entire data set. Then, suddenly, the copy paste does not work!

Usually when you select a cell and ctrl+c to copy, the selected cell will have dashed boundaries to indicate it was copied, and then you would ctrl + v to paste it to your target cell. But now my file is glitched in a way that when I select a cell and ctrl +c to copy, its cell boundaries will still become dashed, but nothing happens when I ctrl + v to paste it another cell. If I open a new file, the copy/paste works normally, but my original file is still glitched. If I copy and open the glitched file with another computer (Excel 2019 Home version/Win10), the copy/paste will work normally.

Has anyone encountered this before and know how to solve this? Thanks in advanced!

Hello!

I used Microsoft forms to do quizzes. I have 5 quizzes and thus 5 separate response spreadsheets - I would like to add up everyone's cumulative score across all 5 quizzes. Is there a function to do this and keep each score tied to the individual participant? The participants aren't in the same order and some don't participate in all 5 quizzes, so I can't sort by name then copy/paste the data into one spreadsheet. Let me know if I can clarify the request further!!

Hello, I have used excel daily for years and consider myself to be proficient. I’m always looking for ways to expand my skills taking on projects that will let me learn new functions and formulas. As I’ve learned more, I’ve found myself encountering “INDIRECT” and despite the googling and AI summaries, it still doesn’t make total sense to me. Can someone explain it like I’m 5? This seems like an important bridge to a new type of formula building, but I just can’t seem to get my head around it. Thanks!

Say I have an XML string formatted as below:

<DrugList>
  <Drug>
    <DrugName>Placeholder</DrugName>
  </Drug>
</DrugList>

The XML gets pasted into a cell in one large string, and can't be broken out into rows beforehand.

Each DrugList element can be parent to multiple Drug elements. I can use FILTERXML to grab every DrugName element from the DrugList quite easily, and return them as a spill array.

However, when trying to run FILTERXML on more than one cell at once, it only returns the first result for each cell.

So if A1 has XML with two DrugName elements, and A2 has three, the formula =FILTERXML(A1:A2,"//DrugName") will only return the first DrugName Element from each cell's XML string as a spill array.

DrugList is also a child element within a broader hierarchy, so simply concatenating everything into one big XML string is not possible.

What would be the most effective way of extracting all the DrugName elements from multiple cells in this case?

EDIT:
Here's some more detailed sample XML that better represents the actual hierarchy I'm working with:

<DrugRequest>
  <RequestorInfo>
    <RequestorName>Timothy</RequestorName>
    <RequestorID>123456789</RequestorID>
  </RequestorInfo>
  <DrugList>
    <Drug>
      <DrugName>Gabapentin</DrugName>
      <Pharmacy>
        <Name>CVS Pharmacy</Name>
      </Pharmacy>
    </Drug>
    <Drug>
      <DrugName>OxyContin</DrugName>
      <Pharmacy>
        <Name>CVS Pharmacy</Name>
      </Pharmacy>
    </Drug>
  </DrugList>
</DrugRequest>

i have to pull zip codes out of a column that has full client contact data in each cell and cannot figure out how to use the delimited tool or formulas to get the correct data. each cell is arranged like this:

Client Name
Address Line 1
City, State Zip
Phone Number
Email

I have several hundred rows in this column and need just the zip code separated. i've tried several methods and cannot find a solution.

EDIT: when i try to use the delimited tool, it only pulls the first line of text, client name, and not any of the other lines. i cannot understand why

I started a spreadsheet with a template but then modified the cells to change the formatting. When I add a new column, it adds it in the style of the original template, and clearing the formatting does not seem to erase this even though it should. How can I clear these rows (the magenta Column, the brown lines, and the brown shading)?

I got an error after transforming and loading my data into Excel using Power Query. I will detail all the steps below before I get the error.

-Transform the data into a table

l-Load it into Power Query

-Highlight some of the columns and select Unpivot Other Columns

-Rename the new column

-Close and Load

And after doing those steps, my excel will freeze for a while and then I will get greeted by the error. I'm currently using Excel 2016. I tried googling the problem online but I couldn't find a solution to my problem.

Hi all!

Are there any ways in which R coding, specifically a background using tidyverse coding, could help improve my Excel workflow? My background is in research and synthesis science, so I use tidyverse in R to clean/filter/manage data quite often. I've hung up my "lab coat" for now, and have transitioned to data management in Higher ed. So much of what I do is now using Pivot Tables in particular, and helping summarize data to put into reports. I know how to code this in R, and have begun exploring options using Power Query so that I'm not manually clicking through so many filters and manually adjusting Pivots. I think this would also help improve reproducibility in our workflows as well. In a perfect world I could just do everything in R, but I work on a very collaborative team that uses Excel for everything.

Is Power Query the way to go if I don't plan on using any Power BI for visualizations? Are there ways that I could use VBA or something similar to clean or summarize data? Any others experience a similar sort of "backwards" transition from SQL or R into Excel? Curious to hear about anyone else's experiences!

Attempting to make a function that will Index lookup by specified date offset but I keep running into Value error. My employees are to add new rows to table each day to do data entry. I need this function in order to scrape specific rolling date windows for averages. t

Public Function DEXDOFF(array1 As ListObjects, arraydc As ListObjects, offset As Double, column As Double) As Double

'Function to index table by specified number of days offeset from present

DEXDOFF = Application.WorksheetFunction.Index(array1, Application.WorksheetFunction.Match(offset, arraydc, 0), column)

End Function

Typically input looks like =DEXDOFF('Table1[#All],'Table1[[#All],[Date]],(Today()-Day(offset),'column')

Hey guys,

I use a lot of VLOOKUPs for my job, when I paste in a CSV export from a central dashboard. This works fine, except when they change the dashboard's column order unexpectedly, which obviously scrambles the VLOOKUPs and forces me to modify them.

I realize the standard solution to this problem is to switch to INDEX and MATCH, but the formulas are always so bloated and it's a bit more mental effort to double check that the rows and columns are accurate.

So with the help of Copilot, I decided to make my own version of VLOOKUP that accepts a column name instead of a fixed number. I call it TLOOKUP.

Usage: =TLOOKUP(RowName, Table, ColumnName)

Example

To get the price of an Apple, you'd just use =TLOOKUP("Apple",A1:C6,"Price")

To me this just seems like a more elegant solution than INDEX and MATCH, or nested XLOOKUPs.

INDEX and MATCH: =INDEX(A2:C6, MATCH("Apple", A2:A6, 0), MATCH("Price", A1:C1, 0))

Nested XLOOKUPs: =XLOOKUP("Apple", A2:A6, XLOOKUP("Price", A1:C1, A2:C6))

VLOOKUP and MATCH: =VLOOKUP("Apple", A1:C6, MATCH("Price", A1:C1, 0), FALSE)

The LAMBDA is here if you want to add TLOOKUP to your own spreadsheet:

=LAMBDA(rowName,tbl,colName,
    INDEX(
        tbl,
        MATCH(rowName,INDEX(tbl,0,1),0),
        MATCH(colName,INDEX(tbl,1,0),0)
    )
)

I've started using this on my spreadsheets and work and it appears to be returning the same correct values as VLOOKUP. I chose not to include error handling because VLOOKUP returns an error if a lookup fails, so I wanted the same behavior from TLOOKUP. I don't see any obvious downsides but I could be missing something.

Any theories on why a formula like this wouldn't be implemented natively in Excel?

Edit: I know that this is effectively just INDEX and MATCH. But isn't =TLOOKUP("Apple",A1:C6,"Price") more simple and readable than =INDEX(A2:C6, MATCH("Apple", A2:A6, 0), MATCH("Price", A1:C1, 0))?

Edit 2: My main motivation for wanting a custom function is that my data source is a CSV export (that I don't have control over) with columns that often change order. If the columns were consistent this wouldn't be needed.

Apologies for the incoming text wall but I'm looking for advice on 2 issues I'm having w a table with ~12k rows (each of which with a unique ID) as I feel like there has to be an easier solution for these that I can't figure. I've managed to teach myself a decent amount since our data analyst left for a new position last month but would still say I'm a beginner w/ excel so ELI5 would be greatly appreciated!

1. Using power query, I merged 2 sheets that had the same columns in each but the data was pulled at different time points. I made a merged table with say columns A-C from sheet 1 and columns D-F from sheet 2. The new merged table is now the "source of truth" and the only table I'll be updating. I want to be able to refresh the data for pivot tables etc., but I only want it to refresh from the new merged table and not pull in the original 2 sheets the merged table was made from. Is there a way to stop the communication between the original sheets and the new merged table?

2. I have a column where each cell has text of all of the applicable diagnoses for that ID# separated by semicolons. In the screenshot is a quick example table I made, where columns A&B are what's presently in the excel table, and from this I would be creating basic diagnosis summary/category columns C&D. The issue is each diagnosis can be written in about 50 different ways, there are endless combinations of diagnoses, and there are a few diagnoses where the way it is written determines its category (i.e. row 5 in example- so I can't just assume that any cell with the word "breast" belongs in the "breast" category).

The only way I can think to do this is click the filter button for column B and search "breast," uncheck any where that diagnosis doesn't fit in the breast category, filter, and then apply "breast" to all filtered rows in column C. However, I can't help but think there has to be an easier way to do this??

Any advice ya'll have would be amazing--thanks in advance!!

hey guys! how do i edit each individual error bar to input my values. whenever i edit one, all of them change to that. all my points have different error values but it wont let me individually edit. Pls help!

Hi all, I have been making a spreadsheet to help with production workload planning for a manufacturing plant I work for. I’ll start by saying I’m no expert, I’m fully self taught.

I have used SQL to bring through data of our workflow from our manufacturing system into excel. The idea behind this sheet is to have it on a big screen in the warehouse as kinda like a flow tracker showing what Works orders are due on what days for dispatch.

I would like for the supervisors to add comments next to Works order lines to say if it won’t be done on time (if material arrives late for example) but since it’s an ever changing list that items get removed/added when they get completed or new ones are added (it’s sorted by dispatch date)

The issue is if they add a comments next onto let’s say line 3, but then line 2 gets removed line 3 becomes line 2 but the comment will still be on line 3. Is there an easy fix to this that I’m just not seeing?

Apologies if I haven’t explained this properly.

At the moment I have a spreadsheet that looks like the following

Staff Name - Training - Column 3,4,5 and 6.

When i setup the spreadsheet I wanted to make it easy to indicate what training staff had attended so I made the Training cells a drop down list with multiple selections as an option using a VBA setup that allows multiple training options to be selected and these populate in the one cell. Column 3-6 are text entry columns that are unrelated but still on the spreadsheet.

We have over 150 staff so as you can imagine this is quite a substansial spreadsheet. I've now been asked if it's possible to add a date and potentail a note, such as DNA (Did Not Attend) to the training options and I'm a bit stuck as to how to add this.

I can't think of a way of adding it to the drop down options because the would be so many potential dates, I could remove the drop down and just ask everyone to type that information but kind of goes against the ease of use of using a drop down. I really need the info to remain in that one cell, I could potentially add additional columns to the sheet to include this info but again unsure on how that will look.

So yeah, just after some general advice, is the an obvious solution I'm missing? I;ve attached a screenshot of a dummy version of what it looks like currently although I have changed headings and removed all the info but just imagine it duplicated over multiple rows for multiple staff