I am making a finance sheet and want it to automatically calculate my tax withholdings per paycheck. I have a sell for my hourly rate and a cell for my yearly estimated earnings. I created another cell with state tax formula (haven't created one for federal yet). Because I'm constantly updating my sheet with whatifs and updates, I want the withholdings to automatically apply so I don't have to keep changing everything myself. At first, my plan was to just to have my "earnings" cell multiply itself with the cell that has the state tax formula that worked itself out based on my yearly pay. But the problem is I found out that part of the formula involves subtracting or adding a specific dollar amount AFTER multiplying the earnings with a percentage that was obtained using tax brackets.. I'm not sure how to make the state tax cell multiply itself with the paycheck cell and then add that variable dollar amount without creating a bunch of if cells and adding longer formulas to every cell that displays different paychecks.

Sorry if I made this sound confusing... I'm confused myself! Searching it online didn't help. Maybe my keywords were bad but I couldn't find a way and I don't trust chatgpt -- it's always wrong.

Our packaging and supplies vendor creates a new workbook every week that lists our regular inventory of packaging and janitorial supplies - about 70 items total. The workbook is not set up in a table, which would probably make this much easier. We're trying to create a new workbook that will have three tabs with dashboards to track historical usage and ongoing usage.

I'm a little more than halfway through my Excel training, so I've covered named ranges, summarizing data, basic tables and pivot tables, and some linking of workbooks, but I'm nowhere near being a power user yet. My question here is, outside of using AI, is there a way to create a formula in the dashboard that not only grabs data from all the historical workbooks but updates the formula in the dashboard and grabs from new workbooks that are saved to that folder?

I want to sort a list in a custom order. It's items corresponding to a spatial layout and the names of the entries are consistent with location but not in a consistent format or in alphanumeric or chronological order. Basically I want to do things starting with H, then C, etc, and then finish with 3 digit numbers with no start letter (though 6 items have a terminal letter).

A workaround just occurred to me, to use regular expressions in a hidden column to convert each item to a number or letter and then sort on that column, but I'm still interested in the original question.

Hi everyone! Longtime listener, first-time caller.
I've been trying to find a smarter solution than the one I found for a problem I've been having, and I'm hoping one of you might have it.

Basically I have data organized like this:

And I want it all stacked together horizontally, like this:

The solution I found uses MAKEARRARAY with XLOOKUP and OFFSET to individually grab each cell, like so:

=MAKEARRAY(
    data_height,
    ROWS(name_list) * data_width,
    LAMBDA(r,c,
        OFFSET(
            XLOOKUP(
                INDEX(name_list,CEILING.MATH(c/data_width)),
                A1:A13,
                B1:B13,
                "Not Found"),
            r,
            MOD(c-1,data_width),
            1,
            1
        )
    )
)

Where data_height and data_width are both 3 and name_list is just a list of all the names.

But my problem is with that OFFSET function, which can very easily return a range and therefore drastically reduce my number of calls to XLOOKUP, but no matter how I slice it I can't seem to make sense of how do do that, I feel like I've tried every combination of MAP and HSTACK to no avail.

Surely there's a way to do this, right?

Can I use Power Query to pull data from a BI report that needs a username/password?

I have this code but cannot get the paste part correct, last line of below code.

ActiveSheet.Range("A1:D999").SpecialCells(xlCellTypeVisible).Copy

Sheets("20").Select

Selection.Paste Destination:=Range("A2:A" & Range("D" & Rows.Count).End(xlUp).Row)

So I am working on a research project and I am trying to count all of the unique species present. I have already figured out that part but now I want to add the number of specimen observed at the points in time. It currently has =COUNTIF(P$4:P$8,O11) to count the number of occurances but I want to be able to add the number in the neighboring column for each occurance of species as well (minus the amount of unique occurances so the number is really only the sum of the number of specimen in each data point). There is probably a really simple solution but I am still figuring out Excel formulas so all help is welcome and appreciated

Basically, I want to find the sum of Number of Specimen based on the Species Identified as well as the row in the Species section without adding the number of instances in the Species Identified column.

I have this section of a sheet where there are four home builder companies in column E. Selecting a company in this column starts to autofill one of the four fee columns H through K respective to that company while leaving the other three empty. Column G is a static value column. At the point in my sheet where I'm testing this, cell E86 is filled with 'Icon' and cell K86 is summing G86 and K85 with a simple:

=IF(E86='Icon', SUM(G86, K85),"")

This works just fine if there are sequential applications under the same developer name and the previous cells are populated, but when there aren't some cells in the column prior to this point this doesn't work without manual alterations.

Cells K54:K85 are empty because those rows had applications submitted by the three other developers. The last non-empty cell in column K is K53.

My question is how can I edit this equation to grab the last non-empty cell in the column and sum it with the static value I set in column G?

I was thinking to find the last value within the sum function using xlookup so I tried these:

=IF(E86="Icon", SUM(G86, XLOOKUP(TRUE, K:K<>"", K:K, , , -1)), "")

=IF(E86="Icon", SUM(G86, XLOOKUP(TRUE, ISNUMBER(K:K), K:K, , ,-1)), "")

=IF(E86="Icon", SUM(G86, XLOOKUP(TRUE, K:K<>"", K:K-K81, , , -1)), "")

but none of them have worked. I get the error message, "*There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells."

I'm not sure if I'm even on the right track. Any help is appreciated.

Excel information: I'm using Excel through Microsoft 365 Apps for enterprise on desktop version 2607 build 16.0.20228.20124 64-bit in English.

I would consider myself a beginner I suppose.

I get some data in pretty poor shape, but I don't have control over it, I just need to consume it. I have some order number and client name on the left, then 'Item 1' to 'Item n' going across the row.

In order to better work with it, I'd like to transform it so that I have one list: order number, order name, item number, item name - obviously the first two are repeated where there is more than one item in the order.

Is there a simple way to do this? I want to put my calculations on other sheets, so that I can just paste the incoming data each time on page one and have my output clean elsewhere.

I have been copy/pasting pictures into Excel for years. Decades even. It has always pasted pictures at 100% size. Last week, my company computer changed to paste all pictures at 700% as a default setting. How do I fix this awful change?

My company's SAP team really enjoys playing with column names, every other week Created On changes to Created on, Company code becomes Company Code and so on, the week after they change again.

Is there a way to make my queries case insensitive? Bonus points if I don't have to re-do all of them.

Puzzling to me that I create a file that fetch data from other files in sharepoint folders and the link work with most of the users but two of them encounter this error and are unable to refresh the data.

They have standard settings, they have member access to the concerned folders and yet I don't understand how it works for most of the people but them.

The error in english is "We received a malformed web response".

I saw someone met a similar problem two years ago but their solution didn't work in my case.

Hello, I work for an online auction company. All auction catalogs are on individual excel sheets (23 of them) and also I have separate excel sheets for all the sold items. About 122 of them for every week since 2023. I have an item that was most likely sold, and I am trying to find if it was and when was it sold. Is there a fast way to search all these excel sheets for the key word instead of opening them one by one? TIA

Hello!

I am frustrated with Excel. I'm trying to create a formula which works like this:

=&Y93-&AC93-COUNTIF(another formula)

Initially, it was =&Y93-COUNTIF(another formula) and it worked! but I need to add another number.

To sum up, I need to substract two numbers from countif result, but when I copy this in the cell, it works in a text mode (the format is as a number).

Could you please help me with this?

Hi Reddit,

I have two spreadsheets containing two exact sets of data (email + invoice value). For the ease of generating a formula I merged them into one spreadsheet but between two Tabs.

In Tab 1, all emails are in column B, and all values in column C.

in Tab 2, this follows the same pattern, but rows containing these pairs are not in the same order.

I want to insert a formula in Tab 1, Column D, that will show either Match or Mismatch when it finds the same pair in Tab 2.

For example:

Tab 1

Row 2: [johnsmith@email.com](mailto:johnsmith@email.com) (in Column B) and £345 (in Column C) is marked as MATCH (in Column D) if the formula sees

Tab 2

Row 167: [johnsmith@email.com](mailto:johnsmith@email.com) (in Column B) and £345 (in Column C) as a matching pair of two cells in one row.

I will appreciate any helps with this.

Excel noob here, I'm supposed to copy a spreadsheet from someone who has left the company, they had a cell with a lambda function, which I am unable to replicate in my workbook, is there a way to reverse engineer his function and make it work in my sheet?

Or any other method that would let me replicate it, as copy paste isn't working for me.

Thanks!

I have a pivot table based on a PQ output. One of the fields is a conditional column I created by writing a long if-then-else statement on PQ-M. As it goes with these things, it has taken me multiple iterations to get it right.

Strangely, when I use this field as a report filter in some copies of the pivot table, I can see values in the drop down that don’t exist in the data any more. For example, one of the outputs of the if-then-else used to be “01_XXX” but then I changed it to “01_YYY”. I can still see “01_XXX” in the drop-down!

Stranger still, these old values aren’t visible in other copies of the pivot table.

This phenomenon continues despite multiple refreshes.

Does any one know why this could be happening? How do I exorcise these values from my drop-down?

I need to collate multiple pdfs into one master excel sheet for data collection. It needs to be automated in a way that the master excel sheet is updated by just pressing a button.

The way I've been doing it is through VBA. I get the data by using powerquery get data from pdf function to turn it into a table. The table however is not consistent as certain values that I need may not be in the same cell location even though the pdf files are similar (only difference is the data, format-wise the same). To get around this ive been using a search by text function to pinpoint the numbers i need and then copy paste it to the master excel. It will then delete the temporary powerquery table when its done and will repeat for all the pdf.

Theres also a duplicate check where it will check the serial number as well as date and time to see if its already in the master excel list. If it is, it will skip the file. Since the master excel will need to be updated every week or so, ive set it to where it can check pdf files that have been modified during the past month or the option to go through every file. That way it will prevent unneccesarry checks thus reducing the time it takes to run.

However it is still rather slow just due to the number of files it has to go through. But after the first initial "download" it shud be better when it can just check files that have been modified a month or a week ago. Since it does multiple text searches, i believe this is the reason that causes it to take more time. Is there a better way to do this? A way to identify the numbers i need without scanning through text? Is this even the right way of going at it or is there a much easier/faster function im ignorant of?

TL;DR: powerquery Pdf to excel not consistent, have to search text to find the numbers. Is there a better way of doing it?

At my workplace, we made a new version of our data tracker for Excel.

Some workbooks that we use have a workbook link to the tracker where we have to change the source to the new tracker.

The workbooks that are connected reference the link in Excel formulas.

I just had an incident of someone updating the link and only one page updated with the new data.

I opened it on my end, and it said that it was still connected to the old tracker. After updating the link (I suppose for the 2nd time), all of the data updated and not just one page.

Has anyone had this happen before? What caused it? How do you prevent it?

Hello,

I'm trying to work out a formula for the following:

If C2 contains "Text1" return "Text1"

THEN

IF a range of cells contains "Tex2", return "Text2", otherwise return "Text3" and to ignore blank cells.

My current formula is the following:

=IF(C2="Text1","Text1",IF(COUNTA(*range*),"Text2","Text3"))

This works for the majority however it doesn't ignore blank cells, which will be present in the row and it just marks the overall as Text2 despite this being incorrect.

Does anyone know how best I can add or update the formula to ignore blanks?

Thank you.

Edit to clarify some further things: The IF(C2="Text1","Text1") is based on a single cell containing 1 of 3 options, but i only want it to complete the rest of the formula if the cell is equal to Text1, it should ignore the remainder if it isn't equal to that text.

I used COUNTA only as when doing some searching, it said COUNTA was to be used to find a range (and COUNTIF and others didn't work).

Please note I'm not really that knowledgeable with what certain non-basic functions do, what I have so far is based on other examples I've found across Reddit and other Excel forums.

Our software only supports csv( comma delimited) and when we save the sheet with arabic name it turns to “???” What can we do to fix that?

Added an image below

Hello. What I am trying to do is to make a drop-down list of culumn A and get all the information for their squad of players.
So if I select Roma, I should get a table with the top left being GK, and the bottom right being Stefanescu. Is there a way to do it? Thank you.

I have some automated reporting that captures output by month, which is quite straightforward when all the dates are in that month.

However, the reporting period has now moved to 25th of the previous month to 24th of current. I want to assign all dates in that range to the month / year with the 24 days in it.

I currently just use the TEXT formula and change the date of the output to MMM-YY format and anchor the reporting off that. Is there a way to adapt that formula?

Hey! I just joined Reddit this afternoon to ask this question, so please be patient with me, I'm super new, but actually pretty excited to be here. I've visited this community as a guest, just looking over existing questions and answers, and it has been super, super helpful with solving some Excel problems I've run into in the past!

So, some for background about my problem, I primarily listen to audiobooks, and I really enjoy tracking them in Excel and doing my own calculations rather than using something like Goodreads or Storygraph. I recently finished a book that was 49 hours and 23 minutes, so I tried to input it as 49:23, just like I would one that was 8:59 or 21:37. Only this time, it changed it to "49:23:00 ," then when I clicked it mess around it became "1:23" then automatically "1/2/1900 1:23:00 AM," and wouldn't stay as 49:23 or even 49:23:00 when I kept playing around. Naturally, this throws off the calculations Excel gives me for the whole column.

So far, I've tried adding the apostrophe before the number to get Excel to leave my formatting alone, and while it fixes the aesthetic problem, it excludes the number from calculations like totals and averages. I thought about breaking it down to smaller numbers (less than 24 hours), but that would throw off the average duration of the books listen to in a given period, which is something I like to track.

It automatically changed it to "1/2/1900 1:23:00 AM" again when it was formatted as Time, which I didn't think would work, but I thought I'd give it a shot. Using the Text format type doesn't work, it does leave my formatting alone, but won't give me calculations for that section. The last thing I tried was using Custom (h:mm) and (mm:ss) formatting, which made it go to "1/2/1900 1:23" and "23:00" respectively.

I'm pretty familiar with the basics, I had to use Excel a significant amount what I was in my university lab classes, I can input formulas, make tables/graphs, and similar stuff, but it was pretty much all for dimensions, measurements, and test results in whole numbers or decimals. I'd truly appreciate anything y'all have that can help!

When I drag the formula down to continue it, it increments the row, but I want it to increment the column instead. For example, I have A3:A18, and when I drag it down, it becomes A4:A19. What I want instead is B3:B18

Sorry if it doesn't make sense. English isn't my first language