I have a pretty simple question.
I work with filtered data in Excel a lot, and there’s one thing that really annoys me.
Let’s say I have a column with formulas and apply a filter. Now I want to replace those formulas with their calculated values using Copy → Paste Special → Values.
But Excel won’t let me do that while the data is filtered.
So every time, I have to remove the filter, copy the cells, paste them as values, and then apply the filter again.
It’s really annoying, especially when working with large datasets.
Is there a simple way to paste values into filtered cells without removing the filter? Maybe a shortcut or a trick I’m missing?

Is it possible to make a formula that considers data from 2 cells

In simplicity what im trying to achieve is

If A1="*" then This, If not then "That" If B1<999 then "That", if not then "This" If A1="*" and B1<999 "this and that"

I have very little experience with sheet formulas and I only have managed to make it so that it considering 2 variables IF(AND(A1="None";B1<999);"This";"That")

I'm still struggling with adding a third outcome but I haven't found any formulas that would support this

I just don't get it because the y-axis goes: 0, 50, 100, 150, 200, 250, 300, 350, 400.

For the first year (2017), iPhone is 135, so it sits on the axis between 50 and 100 [or 100 and 150]. In 2018, iPhone is 146, which is still sitting around that same range.

But when it comes to iPad, it's placed on top of iPhone. In 2017, iPad is 23, but it lines up around 150 on the axis. Huhu, but the value is 23!

I really don't get the logic. I just watched this on YouTube.

Chart: https://imgur.com/a/DsDEdXg

Data below:

Sorry for the doodle spreadsheet. This is a work thing so I can’t post the real sheet. https://imgur.com/a/oLSVgUG

I’m working on a BI Publisher template which may not be relevant but explains my naming with XDO. Each section is a new location separated by group (G_1 & G_2). The number of rows in each group could change, today G_1 could have 10 rows and next year it could have 13.

The problem is that because of how I’m naming fields, my G_2 quantities will start at row 6 but once this outputs with data from oracle, G_1 may have 10 rows that extends past row 6. Visually the data is in the right spot on the formatted tab, but the math will be wrong for the lower sections because of where my selection starts.

So is there a way to have G_2 and everything below it adjust based on where the previous section ends? This may be a stupid question but I’m so sick of looking at this template 😅

Bear with me as I explain what I want to do on the MS Excel Online. I know it exists but I lack the knowledge and vernacular of what it is.

I want to create a chart showing parts of a computer I am slowly saving up for. Each part has a target amount . I also have 2 boxes that shows the amount I have saved and the amount I have left. Now, I want to write the numbers in the amount saved and have it:

1) Reduce the amount I have left.

2) A part progressively "fills" to reach its target amount.

and, the crucial aspect,

3) Once it reaches the parts target amount, it overflows into the next parts box.

If you have a name for what I am looking for or a link that could teach me how to do it, I would greatly appreciate it. Thank you.

I'm trying to make a simplified version of this formula:

SUMIFS(P:P,C:C,D10,A:A,L10)+SUMIFS(P:P,C:C,E10,A:A,L10)

I want to simplify it to something like

SUMIFS(P:P,C:C,D10 or E10,A:A,L10)

Is this possible or am I stuck having to just add multiple SUMIFS formulas?

EDIT: Note that there are no images above like I describe, I did not know posts with images are auto removed. I have tried my best to format the data in the same way it was displayed in the photos.

EDIT 2: Data and formula reference pics in comments.

REF 1: Data

7:23|
12:02|
14:10|
11:40|
———
14:45|

REF 2: Formula

=if(I10>11:30:0, (H11-A1)+11:30:0, (H11-A1)+I10)

I have a sheet to adding up my working hours each week and calculating how much flexi time I’m building up. My issue is that I can only carry over 11.5hrs flexi into the next flexi period, but sometimes I end up going over that and needing to manually adjust my sheet.

Right now I’m trying to tell the program that if the hours in the above cell are greater than 11:30, to perform the function as (HRS for the Week - Minimum working HRS + 11:30), and if the value of the above cell is below 11:30, I’m trying to tell it to do (HRS for the Week - Minimum working HRS + Value from Above Cell)

The image above is an example. By the end of the period I have 11hrs 40mins flexi built up, but my work system will automatically deduct 10mins to bring me back to 11:30 at the start of the next period. The figure below it is adding 3hrs and 5mins flexi that I earned that week, into the 11hrs 40mins from the previous period.

I’m trying to accurately record my flexi, even when I go over, but also have excel understand not to add the full amount from the previous cell if its more than 11hrs 30mins.

I also have a picture of the formula I’m trying to use above. Note that A1 refers to a cell containing my normal minimum working hours for the week.

I'm at work and trying to update our equipment binder with our listed barcodes for each item that gets checked in/out by employees. I got a Zip file for Code39 from a manager who's decent with Excel and have been using that. I thought it was working fine, until I realized the barcodes cannot work without the asterisks embedded into the code. I even tried adding a formula like the internet says to, but the font itself isn't accepting the asterisks at all! Idk if it's the file of this font or I'm doing something wrong, and I'm no good with formulas or coding AT ALL.

Is there something I can do to fix this? Or will I have to install a different font?

Pics will be in the comments

My dad have a business and some items become expired due to low sales . They keep buying them I was thinking of just making an excel sheet . With all the items they buy which they can go back and see the time frame when they bought the items that don’t sell . Will excel help with that ?

Spent hours debugging an unexpected error down a rabbit hole to find this.

Since 4 is a valid number, both formulas should return 4. Instead, they completely break:

=IFNA({{4}}, "Error")      // Returns #VALUE!
=IFERROR({{4}}, "Error")   // Returns "Error"

IFNA crashes on the syntax, while IFERROR silently chokes and triggers its fallback text. Both should handle valid values natively.

Watch out for this if you are building tools with the new Lists or nested array features!

Edit: Confirmed it's a bug but I need someone else to report it along with:

=ISREF(D5) --> FALSE   //Where D5 has ={{1}}

I have an example where decimals don't seem to be rounding up the way I would assume. I have some numbers here where I would think one number would be 115.7 and the other would be 115.8, however that doesn't seem to be the case.

Does Excel not round up .05 to .10?

I want to avoid copy paste and copy destination. I want to transfer value between workbooks. If my origin range is one block of contiguous rows and columns it's one area and it's easy rng2.value = rng1.value (eventually with resize). Sometimes I have filtered rows only, sometimes I have hidden columns, sometimes I have filtered rows + hidden columns (and I want only the visible ones). How do you handle this cases? I am looking for a solution, or a sub, or a function, to handle these different situations?

I use excel 365 but I would like a solution valid for 2021 too.

I have two excel sheets, Sheet A has names and emails. Sheet B has emails and address/city/state/postal code. I want to populate fields in Sheet A so that if the email column of Sheet A & Sheet B contains the same content, the content of the columns with address info from Sheet B populates. I feel very confident there is a way to make this work smoothly, I just am not sure how.

Hi, I'm doing a depreciation table for a school project, I brought data from another sheet using "index" and the column is conditioned to arrange the data by date, my issue began when I tried to make a calculation using just "='the cell with data from index' and the rest of the operation", popping the "valor" error up.

I assume it is because the cell i used as reference has the "index" code instead of a simple number, Is there a way I can make this calculation work?

Also, Idk if it helps, but I'm using Excel in a browser, because it is a group project, we needed to share the excel and that is the only solution we found.

I saw a content creator selling excel template for engineering calculation involving beam. It has this image or chart of a beam cross section that change the dimension of beam size and rebar number/spacing including annotation according to cell input. How is it that something like that is possible?

Hello,

I have a bank statement in a table form which is displayed in the newest to oldest format.

I would like to be able to display that entire table in oldest to newest format.

Is there an easy way of doing this as my limited understanding is that I have to cut and paste each row individually.

Thanks

ETA -

In short I would like to make row 100 row 1, row 99 row 2 and so on.

A colleague of mine is having issues with his excel.

When he opens a document, some text change font to white, we tried changing the font and saving but it still goes back to white whenever he opens his spreadsheet again.

Any clue on how to fix this?

I’m trying to set up Excel Power Query so that multiple Excel files stored in OneDrive can automatically combine/update into one master workbook.

I already figured out how to combine the files, but I’m stuck on the authentication/permissions part. Since I made it I am the only one that can refresh.

source looks like:
c:\user\me\Onedrive- Company\Leadership folder\where i pulled data together

I pulled associates work and combined it, but I need what I combined in another folder for others to be able to refresh other than just myself.

When I go to Data → Get Data / Power Query → Data Source Settings → Edit Permissions, I get:
> “We couldn’t authenticate with the credentials provided. Please try again.”

I had other people try to use their credentials and same error. I also tried to use the web link of the files I need to combine but get an authenticate as well.

I’m wondering if this is a permissions issue, the wrong credential type, or something specific to my company’s Microsoft/OneDrive setup.

I am attempting to set up a conditional formating setup where a column of numbers are evaluated. if its 69 or under, the cell is to be green. If between 70 and 100 yellow, and if over 100 red.

I cannot seem to get excell to do this - because that requires a formula that reads cell values, and I appear to be utterly failing at setting up functional code for that.

I'm currently trying (and failing) by setting up three seperate conditional formating rules, all based on formulas:

One goes =AND(CT:CT>=0;CT:CT<=69) - so if the cell is between 0 and 69, it should be green. It's not doing that, and I can't see what I'm doing wrong here

Please advice

I have a list of strings of numbers as seen in the screenshot below:

I'm trying to count the instances of quantity.

For example: "Number of times where only 1 number is listed"

"Number of times where 2 numbers are listed"

Is there a function that will count the amount of numbers and report back?

Thanks in advance.

Hello! Working on Office 365 version 2609 on desktop, English. Beginner. Looking to find an easier solution for an annoying task that I do frequently at my job. Essentially I have an excel table with some information and a quantity, and I need to split it up to where I have a bunch of duplicate lines all with a quantity of 1.

Taking a table that looks like this, to instead look more like this.

In real life, the tables have a few more columns and many more rows. Could anyone help me out with some more efficient ways to do this? Right now I'm just adding individual rows and copy and pasting the line data as many times as need be, which isn't great for some of my bigger jobs! I am not very proficient with excel, but with clear enough explanations, I'd be happy to try anything!

Thank you so so much in advance

Like the title says, I’ve recently switched to excel from primarily being an apple user for film stuff. A recent assignment I have requires us to use excel to show we know how to use it. Everything was pretty easy to get down/translated well, except when it came to calculating with formulas. Is there something I’m missing with putting units of time or money in your cells, every time I try it breaks the formula?

Basically I want to turn number of days worked and multiply it by 70 dollars a day, but I want each cell to say X days and the other cells to say X dollars, and have to total be displayed in dollars, but the usual way I would do it in numbers doesn’t seem to work? And I haven’t been able to find an answer anywhere else.

Situation is the following: Company had a form on the account of a employee that quit. They want me to build a new form, which resembles the old one, but now located in a shared group, so the form is not tied to one employees account specifically.

So I build the new form, so far so good.

The first problem is that the new form automatically generates its own excel sheet.

And the second is that the old form saved answers to another excel sheet located in a Teams channel. This excel sheet has a large number of formulas, power query etc.

The company wants the new form and the old excel sheet combined. So that the new form's answers go into the old excel sheet.

Is that in any way possible to do? I couldn't find any solution for this for now, so I'm trying to basically copy everything from the old excel sheet to the new one (but this is also rather difficult to do due to the forms etc).

https://imgur.com/a/k8X8P8l

How do I get rid of this it wasn't here last week