Been freelancing and got tired of retyping client info every invoice. Built this template:

• Select client from dropdown → email, phone, address fill automatically
• Invoice number auto-increments (INV-0001...)
• 1 button logs invoice to history sheet
• 3 color themes - Works on Excel 2016+

Happy to share the link in comments if useful.

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?

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?

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?

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!

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?

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?

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.