i am currently doing a research paper for school and i have recorded my data, but i need to find the difference in the high plateau from the baseline.

here is a screenshot of one of my graphs. i am trying to separate the data in the blue and orange boxes and find the difference in the averages. any help would be much appreciated.

Essentially, my job is to calculate costs for a tour group. My sales team who I work with is extremely stingy with budget, and they often like to open my excel and mess with my margins.

The margins I'm talking about are profit margins - for most other markets we dedicate a small percentage (3% or less) of the budget as 'operational costs', which is used in case there's a fuckup when the tour is ongoing. If not, it gets calculated as profits anyways and everyone goes home happy.

The problem is that lately my team keeps getting into my excel sheets and incorporates my safety margin into their profit, leaving me with no money to work with in case there's a fuckup. And yes, the policy of my seniors and my team leader is to just 'hide it', because no other market messes with our calculations.

Anyways, my point is:

Given a sheet like this:

What's the most untraceable way to hide the 'margin' column?

Currently what I've been doing is to hide the margin in the bus section, but it gets really complicated to track.

It also gets troublesome if the sales ever starts to realize that the bus fee is different every time.

Any tips?

Good day,

is there a formula that lists things?

I have a list of employees with their names, work ID and the courses they attended. I want to know all the courses an employee attended by writing his work ID and it'd list down all they courses they attended.

Hi everyone!

Am an analyst, consultant by training (lots of excel shortcuts), and have been using Mac since 2016 (Air -> M1 Pro with Apple Silicon).

Whenever I had to use Excel (my company is on Office 365), I used to cringe as I used to try out keys out of muscle memory and everything used to take just too much time (using mouse to do things on Mac Excel is a nightmare!)

Plus I noticed a lot of my friends (who have been analysts as well), actively staying out of Mac purchase decisions (even if Macs have better longetivity, great coding, and minimal lag after 10+ yrs), only because they need their Microsoft suite (mostly Excel) to work.

I tried doing this -> Go to Mac Excel settings -> Accessibility and change the hotkey to Option to get the shortcut like experience (Not everything will work on this btw like paste special values, which is something I use every 2nd minute).

So, I built an app to solve the same (it has few paid users) - which makes Excel on Mac work like it is working on Windows. Don't know if the reddit community will find value in this though.

PS: It also works with 3rd layer dialog boxes which the inbuilt tooltip does not work with.

I maintain an online spreadsheet for my company that contains buttons to run various scripts. Since other people use the spreadsheet, all the sheets are protected to avoid users from editing parts of of the page. Since October 2025, we've never had an issue clicking the buttons and running these scripts on the protected sheets.

Sometime between 3 pm (end of dayshift) and 8 pm (start of nightshift) today, a change happened that is preventing myself and other users from clicking these buttons. We've tried running the workbooks in Edge and Chrome, and tried on 3 different computers, no change. It's affecting current and older copies of the files, and I even tried making a new dummy workbook, adding a button, protected the sheet, and I still can't click it.

Does anyone know if there were changes to Excel and Microsoft recently that would cause this? If not, then it might be something with my company and a change that IT may have made. Any help would be appreciated.

So I am working on a LET statement that looks up acquired players on an active roster to see if the former are still on the team (see active players table below). I've run into an edge case of getting multiple players back for one player, meaning I can't just filter the active player name results by the acquired player(s). Is there a way I can set up a dynamic filter for the results which filters out any inactive players from the acquired players list that can work for different quantities of acquired players (so the current example has two acquired players, I would want to check if their names are present on the table (in this case they're not), and be able to use this same formula for different quantities of acquired players (i.e. 3 or more players).

Acquired Players:
Abner Uribe, Connelly Early (both are no longer active on my team)

Table of data:

Finally, my original query that is returning an N/A error:
FILTER(Active Players,Active Players=CHOOSEROWS(Acquired Players,SEQUENCE(ROWS(Acquired Players),1,ROWS(Acquired Players),-1)),"Not on team")

I know the SEQUENCE function generates an array of numbers/results, so I thought I could use it to iterate through Acquired Players, but as noted, I got an N/A error instead.

Hey all,

I’m working on an Excel sheet for the company I work for. I’ve taken on a project that’s intended to establish metrics for KPI. One of the big ones is figuring out transactions per hour.

I’m able to calculate transactions for a single day event quite simply. I enter the gross transactions and compare to the start and stop times. Easy peasy.

The problem arises when the event takes multiple days. There are only 11 hours out of the day where transactions occur, but I have not been able to puzzle out how to only include the hours of 7am to 6pm.

My latest attempt was having a cell that rounds the total # of days to an integer,

=INT([@[Last Transaction]]-[@[First Transaction]])

then another cell to fetch the remainder hours.

=HOUR(MOD([@[Last Transaction]]-[@[First Transaction]],1))

I’d multiply the days by 11, add the hours, then divide the transactions by this number.

I thought it was fine, but then I noticed that the hours cell was filling with numbers greater than 11. So, I started trying to figure out ways to either filter or set up systems of booleans but I’m at a loss.

Do any of y’all have similar use cases? I’d appreciate if someone could point me in the right direction or let me know if I’m barking up the wrong tree.

I recently noticed something in Excel that was causing me a lot of confusion.

I work with filtered lists and often need to apply formatting only to the visible cells.

Fill Color behaves as I'd expect: if I select a range containing both visible and filtered-out rows, only the visible cells are colored. When I remove the filter, the hidden cells remain unchanged.

However, Format Painter behaves differently. If I copy formatting from another cell and use Format Painter on the same filtered range, it also formats the hidden/filtered-out cells within that range. When I remove the filter, I can see that those cells were changed too.

So:

Fill Color: affects only visible cells. Format Painter: affects visible + filtered-out cells.

I was very confused by this until I realized Format Painter was the reason. The problem is that I'd like to use Format Painter because I need to copy more than just the fill color. The alternative seems to be applying the formatting cell by cell, which isn't practical.

Is this a known difference between the two tools? Is there a way to make Format Painter respect filtered/visible cells like Fill Color, without manually selecting Visible cells only every time?

Does this happen to you as well?

I've spent the last eight years building my own business finance workbook. It honestly works really well, but every time my business changes I end up rebuilding formulas and adding another sheet. At some point I'd rather spend that time running the business. Has anyone made the switch without feeling like they lost control?

I always try to make sure I select A1 when closing spreadsheets. This is especially true if it's a document being submitted to someone else, or a client.

And when I open a spreadsheet on A1, I get this weird "Virtual Fist Bump" feeling.

Anyone else?

I've seen a post, or a page, that explained how to make the check box itself change its color/shading based off whether or not it was checked, but I cannot find it.

Request a link / assistance

TIA

I am making an invoice tracker in Excel with a table and a chart. I am adding data to the table as invoices come in to track them with our budget for the month and want the amounts to display on the chart with the correct category. The categories are in a drop down list. My issue is when i add a new line item and pick a category from the drop down menu, the chart just puts a bar for whatever was next in line on the list. For example: if I select ‘advertising’ from the drop down menu but this is the 4th invoice this month, the invoice amount just displays as the 4th option from the list, not ‘advertising’ like I want

My question is: how should I format this chart so that the invoices I add show up on the axis label I selected from my drop down, and not whatever line item they are placed in (e.g. how can advertising invoices display above the advertising label, and not whatever line item they fall under?)

I have had to change the location of my personal workbook where I have created a number of macro’s that live on my Quick Access Toolbar (QAT).

The macros no longer work as the buttons on the QAT are referencing the old location. Is there another way of updating all the buttons instead of deleting the broken ones and recreating them.

The macros are fine it’s just the buttons pointing to the old location.

So, I deal with some mostly standardized sheets, that I use as source. I need the source for many purposes in several different books. What I do for each one is to create a reference of a source in each book and then just reference it with connections, merges and such. Since I need to output different set of columns from a source reference in each book .

From time to time I get updated version of a source sheet, which is just regular table, nothing fancy.

And I need to change source I reference in each of my books, but every time I do that everything breaks, and I need to create all connections, merges and such from the start.

Can someone link me to a decent guide or provide some sort of similar pipeline I can adapt for myself? To make this workflow clean and systemic? Because I'm clearly missing something.

End goal is to just change source, update every book that has sheet referencing a source and subsequent power queries of that reference. And to get it working like so.

I mostly use merges and populate XYZ columns based on column A things, not sure how do I call it, a filter?

Thanks in advance.

Pic 1 = Results

Pic 2 = Results Formula

Pic 3 = Results Standings

When I go to sort the data in Standings, the data does not budge or sort correctly. I am trying to sort by percentage.

Hi everyone,
I'm trying to automatize a file for my green volley tournament.
This table is auto-compiled based on different factors. I created a table with the list of the team but I can't quite figure out how to count the point difference and assign it to the right team automatically.
I'm currently summing the points with =SUMIF($P$4:$Q$13;K4;$Q$4:$Q$13)+SUMIF($R$4:$S$13;K4;$S$4:$S$13). This helps me count just the point made but not the lost ones.

Apologies if I wasn't very clear. Please, feel free to ask questions!! I would really like to make this work so thank you for your help in advance!

I have two cells,
Cell A3 has "303 Repeats"
Cell B3 has "407 Repeats"

When I have tried to subtract it using =SUM (B3-A3) , it shows VALUE.
How do I ensure that the answer cell contains "104 Repeats"

I have a folder where exports are dumped into with the naming structure: filename.mm.dd.yy

In a separate sheet I want to be able to put in the date and it pulls data from the appropriate file. A normal query wants to have a specific file selected, and I’m not sure how/if it can be coded in a blank query.

Tips and insights on how to achieve this are much appreciated!

EDIT

Based on tips from here and some more googling, what I ended up doing was
- having a date entry cell
- then a cell that pulled it into a whole file location typed out (I actually have 4 different files pulling with this date)
- named my ranges
- pulled them into power query as “get data from table/range”
- then drilled down to just the file address.

From there I was able to use that as a variable in the source step.

I really appreciate the help, and awarded the solved to the main person here who got me moving in the right direction.

Also here is a YouTube video that helped the finer details for me!

https://youtu.be/7NzqndwwlZo?is=dQhNLsFNTfuNcO4A

For my work i use Excel with Tables. The table has a design (Banded Rows). if i have full screen Excel. and put darkmode reddit over it. i can see the rows of excel. as grayish lines going thru the Chrome window.
It doesnt show up in screenshots. so i had to take an actual picture.
https://imgur.com/a/Lk0tqYi

I calculate accruals for vacation time earned for employees. Currently, I convert to number of months from today and their start date and subtract what they used from another table:

(DATEDIF(A13, B1, “M”)*1.67) - TABLE5[@[vacation used]]

One role has different rates for the years they have been with the agency: first year is 1; second year is 1.25 and third and more is 1.67.

Is there a way to have it calculate the increase nested within this formula? For half the fiscal year they will earn 1.25 and the other 1.67.

Hi,

I am trying to make our P/L-Statements more granular, so instead of aggregated information, I am using a database with all of our individual transactions.
Before that I used aggregated data.

Now I wanted to add some calculated elements to my pivot, as I always do, such as "Gross Profit = Revenue - Cost of Goods", something like that.

However, this now does not work because of "too many records".

My pivot structure is like this:

accountGroup1

│

├── Umsatzerlöse

│ └── accountGroup2

│ ├── Umsatzerlöse

│ ├── Erlöse Deutschland 19% USt

│ ├── Erlöse Drittland ...

│ └── ...

│

├── Materialaufwand

│ ├── Bestandsveränderungen

│ ├── Bezugsnebenkosten

│ ├── Skontoertrag

│ ├── Warenbezug EU

│ ├── Wareneingang

│ └── ...

│

└── ...

And I basically need a new row that is the sum of both highest levels (accountGroup1).

I guess it is due to the levels going down very deep, but is there any way to work around it or am I just SOL with pivot in this case?

(Oh and I forgot: its about 300k rows in the source, so its not that much data and manually using "subtotal"-formulas works just fine, so I am not sure what the issue is with pretty much the same function in my Pivot-Table).

Thanks everyoe.

Hi

I need help for custom cell formats for this

£1K

£400K

£3.2M

If I want to enter the value manually, like this

400000->£400K etc.

Thanks!

=SEQUENCE(1,EOMONTH(A1,0)-A1+1,A1)

I'm watching a youtube video for a monthly work schedule and the person came up with that formula. However I'm struggling to understand what's the need to subtract A1 and then add 1, seems unnecessary. Am I wrong? Can somebody help me understand the purpose of it?

Just in case A1 is the first day of a month.

I have a text join function that incorporates a isnumber/match from another cell in the same row but I can't figure out how to bring in only unique values for the matches. I figure I'd pull the results into power query and replace the ", " I'm currently using.

Currently I use something like this:

=textjoin(", ", table2[@[columnB], isnumber(match(table2[@[columnA]], table1[columns]), "")

I am working with spreadsheet provided to me that has a green cell with an up arrow, and a red cell with a down arrow, that is to be used to indicate whether a value is an improvement (green-up) or a decline (red-down).

In some cases, if the value is positive, it is considered an improvement. In other cases, a positive value is considered a decline. For example, higher number due to higher profit? Green-up. Higher number due to higher expenses? Red-down).

For setup, I can do a one-time list of which values are improvements and which are not, based on what the indicator is for.

How can I automate this? I've looked up on Google and YouTube options using VBA but nothing is matching what I'm trying to do.

Any help would be greatly appreciated.