We have a staff roster in excel.
I am wanting to create “buttons” or a “macros” (unsure which it is), that will allow me to click a button which will change the colour of the cell and remove the data.

For example.
5 cells will contain a name, that person is going on leave for 5 days. I want to be able to select all days they are away, and click a button that removes their name from the cell and changes the colour to green.

I'm trying to create a sheet that has a running total of my hours, but is able to deal with negative time, ie when i've left early or whatever, though i'm finding it hard to have excel represent negative time.

Currently i have my expected time on site subtracting from my actual hours worked, which is most of the time giving me a positive number but it's hard to convert the numbers from hours to get an accurate calculation.

For an assignment, I was tasked to create an excel scatterplot of some sample data. When I try to create a scatterplot using multiple cells as if to distribute data points for two subjects, I get an error. In addition, if I limit the graph to just one subject I get a weird series of labels on the x-axis and no points actually appear on the graph. How can I fix this problem so that I can properly graph the data distribution for one subject and create a graph so that the data distribution for subject one and two are shown.

Hello!

I have a spreadsheet tracking information with 5 different status.

I want to write a formula on another sheet in the same workbook that will return a true or false (doesn't necessarily need to be true or false) on whether a collection of those cells are one specific status.

Essentially the status could be hold, terminated, ongoing, and complete. I wanted the formula to tell me true if all cells are complete. The other status don't really factor in except that they are not complete.

I will have probably 50 batches of cells like this from 4-50 status.

Can anyone point me in the right direction on how to write a formula?

EDIT BELOW

For the advanced ask.

I'm trying to track the status for unique identifier numbers.

Is there anyway to change the formula provided below by excelevator to look for all of a certain number in a designated column, then check the status and return the true/false.

TL;DR

I have 580 sheets, spanning since 2015. how much more can my poor little excel file handle? The file is about 3.5mb in size 😯.

So, i work in architecture, and this is generally how i track my time throughout the day. i will NEVER remember what i do when it comes time to doing my timesheets and this makes it way easier. i also have a place at the bottom that tracks my architectural licensure hours. i have to manually input that, and there are two sheets at the end that summarize this data (none of that is relevant i'm just including the explanation for context).

i've used this excel since 2015 and there is a "sheet" for every single week since about july of 2015.. so there are about 580 weeks/sheets... when will my excel explode and refuse to open? I've started saving a copy and backing up the file each week.

I'm open to a more robust system, but needs to generally have the UX (or similar) of this workflow. I have gone as far as vibe coding prototypes but i dont have the confidence that AI will be more stable than what i'm currently doing, and i'm not a coder so i can only take it so far.

anyway, input would be appreciated.

I have a spreadsheet that details a list of all transactions within two particular account types, and each transaction has a name & user ID associated with it. Nearly every user ID is associated with more than one transaction, and there are 1000+ users in the full list. I am looking for a way to combine all transactions attached to each individual user ID, while also designating the two different account types, and that can be automated to apply to the full sheet. I know how to do this using SUMIF but I don't know how to achieve this without having to enter every name/ID manually.

Eg:

Ultimately what I need to end up with is the maximum, minimum, and average total transactions per unique user ID, separated by type A and type B transactions.

I've been working as an engineer for a couple years and every once in a while, I get asked to format some data or calcs to 3 sig figs. You would think this is no big deal, but the data I'm usually dealing with is not uniform, and there are usually thousands of data points I have to review manually. Here is a quick example of the variety I'm referring to with the intended output format:

123,456 -> 123,000

0.00000012 -> 1.20E-7

12.3456 -> 12.3

1 -> 1.00

0.12345 -> 0.123

12,345 -> 12,300

0.000912 -> 0.001

These numbers would be in subsequent cells. For decimals, I've been using the inc and dec decimal buttons. For extremely small decimal values, I have been changing the formatting to scientific notation. For numbers >999, I've been using the round function. It takes hours to do all of this manually for the thousands of cells I have to edit.

I would like to make a macro to read the way the number is displayed and change it to 3 sig figs, but I am not the best at coding and the VBA coding forums are largely unhelpful. I was wondering if anyone on here would be able to help me create a macro for this purpose?

Hi all. I work as a service engineer for laboratory equipment. I cover a range of different products and I am trying to 'simplify' the way I build lists of parts that need ordering. I have a list of products similar to the example in the first picture. In the second picture I have an example of what I want to happen. I want to be able to choose the product type, eg Pump A, from the dropdown list (I can set up the dropdown list), and have this then show me all the parts needed for that pump. This would then be multiplied so I can have multiple drop down lists to choose Pump 1, Pump 2 etc, then Doodah 1, all from drop down lists which then show the list of all parts needed from the 1st sheet for the particular configuration I have set up.

I appreciate I probably haven't explained this in the best way possible, so please let me know if you have any questions. Is this possible in excel? How would you suggest going about it?

Thanks for your help!

Hi,

I am trying to do a very basic visualization on a map chart and I'm running into an issue.

I have 5 different cities in the US with potential candidates in each city (based on information from a 3rd party). It all works until I look at the map and it just highlights the individual cities and it's way too small. Is there a way to change the highlighted cities to a bubble plot that's reasonable to see?

I can do it on 3d maps, but I'm going to be asking others to enter their own 5 cities with corresponding numbers and 3D maps are too difficult for them

I'm making an Excel spreadsheet to keep track of lecture videos.

My basic columns are:

Series → Teacher → Video Name → Time

Most videos have one teacher, but sometimes a single video has two teachers.

For example:

Series Teacher

Anatomy Dr. Rajesh Dr. Jay Mr. Imran

I'd like to merge/group the Series cell so it looks like one video/group, while keeping the teachers on separate rows.

The problem is filtering. I want to be able to select Dr. Rajesh from a filter and see every lecture he's involved in, without having "Dr. Rajesh, Dr. Jay, Mr Imran" treated as one filter option.

Is there a clean way to do this in one Excel sheet?

I have a Power query that is connected to a sharepoint folder. Whenever I try to refresh the file it would take around 10-40 mins of load time before it loads into the Data Model. Granted that the major reason for the loading time is due to the size of the data (estimating somewhere between 10-12M rows after filtering), but what are some ways that I can speed up the loading.

So multiple people need to work on excel files stored on a Diskstation. Unfortunately Synology Office messes up the formats a lot. Is there a better tool or a way to use proper Excel without having to store the files in the MS cloud?

So I have a spreadsheet (Spreadsheet A) with 200+ rows. One of the columns is a list of serial numbers (we'll say H2-H250). I have another column in another spreadsheet (Spreadsheet B) with more serial numbers that need to be highlighted in Spreadsheet A H column. I have been going line by line doing a Ctrl F to highlight the rows containing matching serial numbers in Spreadsheet A. I feel there has to be an easier way to do this. Any advice?

In File-Options-General under When creating new workbooks I see my preferred font and font size. However each time I create a new workbook to test this, it reverts to Calibri 11, even though it still has my preferred font and size listed as what it should use on a new worksheet. Does anyone know how to change this permanently?

I want to make a range of cells (G30:G508) automatically reference the value of a single cell (B3).

I’m working on a spreadsheet that displays energy consumption of a group of machines across an 8-hour shift. I have columns with the raw data from various machines, and a column with the totals. These are referenced in a graph that shows energy consumption as a fluctuating line with time as the x axis and kilowatts as the y axis. I also have a single cell (B3) that displays the average of all the values in the totals column. I would like to make a new column that shows the value in B3 across the entire range (G30:G508) so that the average energy use for the shift appears as a straight line across the graph.

I know I can do this by manually copying the data from B3 and pasting it to G30:G508. But I would like to make the new range automatically update if the value of B3 changes. If I put =B3 in the formula bar, it works for the first cell, but if I drag it down the rest of the range populates as =B4, =B5, etc which is not what I want. I essentially want every cell in the G30:G508 range to have the =B3 formula without having to type “=B3” 478 times.

Can anybody help me with this?

searched "power query sharepoint" and "power query web online" and didn't find relevant discussion or same question

I have a single query that reads a SharePoint folder, filters for CSV files containing a specified substring, opens the most recent one, and then does some simple transformations and loads it into the workbook. The query works without issue when refreshing from Excel desktop app. But if the workbook is open in Excel online and I try refreshing the query, I get error:

Credentials are required to connect to the SharePoint source.

Something went wrong while loading required resources. Please try refreshing the page or come back later to try again. (APIs: connections, connectionsInScope)

...which makes absolutely no sense because I opened the workbook in SharePoint. The workbook and the CSV files are on the same SP site. Is there anything I can do to make it refreshable in Excel online, or is it simply a problem that Microsoft devs have not ironed out yet for PQ in Excel for web?

I’m having a strange issue in Excel where the Down Arrow key doesn’t always work.

The Left, Right, and Up Arrow keys work perfectly, but the Down Arrow sometimes works and most of the time it doesn’t.

Has anyone else experienced something similar? If so, how did you fix it?

Cheers!

I spent the last month building spreadsheet templates and kept getting caught out by the same thing...,a formula works perfectly in desktop Excel, then someone opens the file in Sheets and it returns #NAME?, or it opens in Excel for the web and quietly gives the wrong number.

The ones that got me: XLOOKUP doesn't exist in Sheets, ARRAYFORMULA and QUERY don't exist in Excel, TEXTJOIN wrapped around IF silently stops working without array entry, and a date typed as "2026-11-01" is text, not a date, so sorting breaks.

So I made a small thing to check for it. Paste a formula, it tells you what breaks, on which platform, and what to use instead. It also catches misspelled function names, unbalanced brackets, whole-column ranges, and volatile functions.

https://rithvik697.github.io/formula-checker/

Runs entirely in your browser. Nothing gets uploaded, no signup, no ads.
It's rough and definitely incomplete. If it gets something wrong on a formula you actually use, reply with it and I'll fix the rule.

Code is open if anyone wants to look: https://github.com/rithvik697/formula-checker

I have been trying to do this without a macro. While I manage to do it normally, it is not dynamic i.e. if I change value from 50% (Amber) to 70% (Green), it does not change the colour automatically. Copilot was no help. I want the progress bar to fill the same cell as the cell which has the value. Take any arbitrary thresholds for Red, Amber, and Green. Thanks in Advance!

I need help on an excel sheet that features these:

1. Column A is a pre-numbered cell count from 1 until 10,000.

2. Column B is a cell that functions like a start timer button bearing any color.

3. Column C is the time (start).

4. Column D is a space. (no data or characters here)

5. Column E is another button to be clicked by the end user to end the time. (end)

6. Column F is the total time consumed from Column C to Column E.

This goes on for the next rows until the 10,000th row.

Functions:

Everytime you click the button in Column B, the timer in Column C starts. Then when you click Column E button, Column F displays the end timer. Then Column H displays the total time consumed up to the last second.

I've been struggling to get my head around how to work out how to do this feasibly so any help would be appreciated!

I've got a large set of data with multiple drop downs, which will be updated on a daily basis. Each row will be a separate task which will be allocated to a member of the team - name listed in the row.

Some of my team are not so computer literate so we are trying to make it as easy for them as possible - they have refused to read through the cells to find their names. We are hoping to create sheets for each team member so that they can just see their own tasks as they come in.

As we enter the data in the Master Sheet, is there a possibility of the sheets auto populating the data with the corresponding team member? i.e. under Member A's sheet will only be tasks allocated to member a etc.

We could just manually do data entry but thought there may be a faster way

I have a complex workbook with named tables. I want to use a named formula in an XLOOKUP to identify the array I want to search. Essentially, instead using TableName[Header], I want to use TableName[formulaResult] for the return result.

Table1:

Table2:

I want to find the Event in Table2 based on the Date and Organizer.

I'm using an INDEX formula to find the column header of the column the formula sits in. I've named the INDEX formula "getColumnName" in the Name Manager.

If I put =getColumnName in Table2[B2], I get the result "Event." This works just fine.

I'd like to use this result in my XLOOKUP to make this search as dynamic as possible, but I'm striking out. Essentially, I want:

XLOOKUP(Table2[Organizer]&Table2[Date],Table1[Organizer]&Table2[Date],Table1[getColumnName])

My expectation is that the formula in Table3[C1], would translate as:

XLOOKUP(Table2[Organizer]&Table2[Date],Table1[Organizer]&Table2[Date],Table1[Event])

Excel isn't recognizing the return array Table1[getColumnName].

I thought the solution might be to place the XLOOKUP inside a Let(), e.g.:

Let(colName,getColumnName,XLOOKUP(Table2[Organizer]&Table2[Date],Table1[Organizer]&Table2[Date],Table1[colName]))

But that also throws an error. I'm new to using Named formulas and Let(). I think the issue is that Excel doesn't allow a formula as a value to identify the Named Range.

Does Excel allow for this option?

**

In case it's needed, this is the INDEX function:

INDEX(Table2[#Headers],COLUMN()-MIN(COLUMN(Table2))+1)

Looking at Microsoft's list of Excel updates for August, the only things they mention relate to AI features:

• Copilot in Excel: change history skill #FIA
• Copilot in Excel: chat history #FIA
• Copilot in Excel: chart and PivotTable skills
• Copilot in Excel: Python skill (Insiders)

https://techcommunity.microsoft.com/blog/excelblog/whats-new-in-excel-august-2026/4527283

But, they also made an update to the creation of Tables. That is, when creating a Table, you can now give it a name in the same dialog.

I don't particularly care about the AI features, but the small change to the Table creation dialog makes me happy.

I know this is pretty niche but I am a vb coach and want to build out a way to autofill court diagrams with any roster I might need. I am a beginner at excel and don’t know if excel is even the best place to do it. I think the biggest issue I will have is having an image or graphic, that the excel data is then filling in on. I am just looking for advice on how to do this. I can comment or send pictures of the diagrams I am talking about to anyone who can help!

Also not sure if this is the right place for this, if not please tell me where to go!

I'm working on a sheet that I'd like people to be able to create new rows but for most of the cells to contain a complex formula. It's an IF statement. I though excel would automatically just insert them into the cells of the new row but it doesnt.

I have made sure the check box in the options section is checked to allow this. I also have nearly 60 rows so minimal rows isn't the issue.

Any ideas?

Separately I'd love to protect those auto filled cells by column but that prevents people from deleting rows. Any thoughts on this one? The first question is my primary concern though.

Thanks all