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

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.

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 🥲

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!

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!

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!

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.

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.

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

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.

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.

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

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!!