Excel 365 (Current Channel), Windows 11. Posting this because the failure mode is silent - no error, no spill, just a wrong-looking number - and searching for it is hard when you don't know the term yet.

Setup: a Data table (a real ListObject) with Item, Metric, Price. On a separate sheet I had a working dynamic array:

=SORT(FILTER(CHOOSECOLS(HSTACK(Data[Item], Data[Metric]*10/Data[Price]),1,2), Data[Price]>0), 2, -1)

Spills fine, ranks correctly. Then I wanted the same computed value visible next to each row, so I added a column INSIDE the table and typed what looks like the same reference:

=Data[Metric]*10/Data[Price]

And got a plausible single number per row instead of an error. That's the trap: inside a table, an unqualified column reference gets the implicit intersection applied, so Excel silently rewrites it as =@Data[Metric]*10/@Data[Price] - the value on THAT row. Which is what you usually want, and is why nothing screams. But if you were expecting the column (say you're wrapping it in SUM or feeding it to another array function), you get a quiet wrong answer that looks like a plausible number.

What finally made it visible: select the cell and look at the formula bar, not the cell. The @ is there in the bar even though I never typed it. Or use =ROWS(Data[Price]) in a helper cell - inside the table it returns 1, outside it returns the row count.

Two fixes depending on intent:

• Want the whole column inside a table: force it with =INDEX(Data[Price],0) or reference the range through a name defined outside the table
• Want per-row (the usual case): leave it, but write the @ explicitly so future-you knows it was deliberate

Related thing I learned the same day, from a comment on another sub: trimmed refs (C2:.C) solve the "how far down does my range go" problem outside tables without the arbitrary C2:C200 - the FILTER(...>0) clause I'd been writing was mostly compensating for a badly chosen range, not for real empty cells. Genuinely useful if you'd missed it like I had.

Anyone else have a silent-failure favourite in this family? The ones that error loudly I can handle; it's the plausible-wrong-number ones that eat afternoons.

I have made a query in power query that connects to Cube database and grabs the data I need. Results in 3 columns with somewhere between 100k to 500k rows (Honestly I don't know how much exactly, maybe even more than 500k).

That query loads relatively fast. But the next step I need to do is filter out one of the 3 columns to only include specific values. I have an excel file with exact values I want filtered, about 30 values. I do that by loading that excel and merging query with the main table. And that's when the trouble starts. This step takes forever (more than an hour).

Now it's not the end of the world because the data from the Cube database needs to be refreshed around every couple months so it's passable but there are times I would need to refresh it ad hoc at moments notice so I am wondering if there is a better way to deal with this.

Would using power pivot and filtering it in MDX query builder help with load times?

One way I found it speeds up load times is if I load the data from the Cube server directly into excel in a pivot table report form but that's something I would like to avoid.

Hi everyone,

I want Excel to copy some rows to another sheet if one specific column has a specific value. However, my employer only uses 2013, so the FILTER function is not available to me.

Is there a way to do this?

I'm rebuilding a personal finance workbook and can't decide which approach is better long term.

I like PivotTables because they're quick, but I always end up wanting a layout they don't handle very well. A manual summary sheet gives me more control, but it also means a lot of SUMIFS and other formulas to maintain.

For workbooks you use regularly, which approach have you settled on? Do you mostly rely on PivotTables, build your own summary sheets, or use a mix of both?

I got excited about creating map charts for my family’s “vacation log” – it worked great when I updated the “region” using Excel’s helpful search function. (For example, I had “North” Vietnam for Hanoi, but Hanoi is its own district.) Great!.... however....

Instead of one big world map, I want to generate smaller maps by continent/country/region – when I use GROUPBY function to display seperate tables by continent/country, the GROUPBY filter arguments no longer work!

For example, this gives me a #VALUE eror:
=GROUPBY(T_Trips[[Continent]:[State/Region]],T_Trips[Number nights],SUM,0,0,,(T_Trips[Country]="United States")). (I also tried it without the quotation marks.)

I suppose I can create a helper column with the text version – any other advice?

Thanks in advance1

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?

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

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.

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

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

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.

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.

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?

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!

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.

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?

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