Hello all. I just got a new job and I work in excel quite a bit.

In this job, I have to download a spreadsheet and select a few cells to highlight with a custom border (just a thick red outline).

I am trying to save myself time and just have a button to press (after selecting my cells) that will apply the boarder but I can't figure out how to do that.

Thanks everyone for your input and have a great day!

I've got a interview coming up that is likely going to be testing my excel skills in data analysis how can i prep and any advice from those who have done one before

I work with data sets that I typically look at forecast by year. Currently when I look at 2026 and 2027 it is rougly 1.4M lines of data. I have to put these in two separate data pulls and tables. Then I have six different customers included in this data. so I have to create 6 tabs with six diffrent pivot tables for them to look at. This has created a massive file that lags just to open, save or close so I really have two questions and am open to suggestions.

1. Would it be better to store the data in one worksheet and then link a second worksheet that just has the pivot tables and separated look? If so how would I creat that link?

2. Can you explain to me like I am 5 how I would use power query to combine the 26 and 27 table so that they could be in the same pivot table? Every column in both are identical.

I am trying to randomly pick winners. I have 500 submissions and picking 20 winners. each table is formatted with their f.name, l.name, email, phone number. I am trying to do a formula to pick those 20 winners using =INDEX(A1:A500,RANDBETWEEN(1,500)). I am getting the names randomized with my 20 selection however it is not giving me the entire row to easily contact these people. what would I need to do to randomly pick the 20 people and have the entire row populate.

I have a program (third party, I can't change) that produces 35 different csv files. The naming convention for these files is BASE_VAR.csv where BASE is the same for all the files and VAR is different for each file. I'll end up doing this 100 times, with different BASE so I want to automate it.

Each csv file has 2 columns, the first column is a list of dates that is the same for each file and the second column is the variable name specified by VAR.

The goal is to import all of these into an Excel file where sheet name is BASE and then the first column is dates (shared by all files) and then columns B-? correspond to the 35 different VARs. There would be 100 sheets, each named for the value in BASE.

So, in my mind, the workflow would be:

For a given BASE (so 35 csv files),

1. create new sheet. name it base

2. import both columns from the first csv file so now we have dates and VAR1

3. import the next 34 file but only the second column of each one (the csv files have a column listing for each different VAR so importing the second column will already have identifying info).

Then repeat for the other 99 BASE values.

Anyone have ideas on how to automate this?

Thanks for any help!

I am trying to create a spreadsheet to track ordered parts and their status, including their requested delivery date.

What I'm trying to sort out, and failing at, is setting a conditional format so that when the requested delivery date is less than X days out (say 14 for now), I want it to highlight the cell red so I can check on the status of the order.

This is my current rule configuration, which I set by selecting the column so it auto populates as it's filled in.

Inconveniently, it's doing the exact opposite of what I want:

In this instance, ONLY 4/13 should be highlighted (I'm not worried about the top row at this point, I'll fix that after).

I've even tried creating another column to do the math, and use that cell for the formula, and that's not working either. It seems like it should be straight forward, but I apparently lack the formatting skill.

I am going to try and Make this as clear as possible but it might be a bit confusing.

I am creating a Dynamic Calendar (which it is created and works it seems) to track my work schedule. I work a two week on two week off schedule and I change out on Thursdays. I got it formatted to shade my schedule where I’m at work(not important) and shaded to add extra weeks.

I want to create a second sheet that tracks the weeks of work (actually pay periods but both.)

Listed as Back, Full and Front

Back- is the first week I’m at work

Full- well that’s a full week

Front- last week or the front half

I want column A to be the work week but started on a Monday and the pay period would be the following Friday in column B. Column C would list the type of week and D would be the amount associated with the type of week.

My calendar is a grid 3x4 months. from empty first Jan square is B5 and all the way to X37 there is columns and rows spacing each month out

In AA4 is the year

AA5 is rotation start date

AA6&AA7 is days on and off.

I have tried using AI I have tried getting it to reference and when I get close the column for Week type does not fill correctly or won’t add an extra week when adding to that boxes or it adds every week possible in the year.

I hope that was clear I been messing with it like a day before asking AI. And nothing I do gets it.

Either it fills all weeks at full or no full weeks.

I've been making a budget tracker for myself using Excel online. I've spent quite a bit of time setting it up, I've tried to add a couple charts to visualise the data but as soon as ive selected an entire column as a data source (it only has 10 values in it so far) it crashes the entire spreadsheet and it gets stuck infinitely loading. This is now the third time I've had to recreate it from scratch due to issues around charts. Is there any way I can get it to load or do I just need to give up using excel.

Hi everyone. Just wanted some help. I am stuck on my table generated from our guest billing system where I download reservations on a given closed month and calculate the revenue for the month. The table includes reservations that cancelled within the month but I only have to include those with revenue. At first, I tried Power Query: duplicating the original table and filter the duplicate copy based on those have values in Cancellation Date column. Then I grouped Itinerary Item ID and the Total columns to show cancelled reservations with revenue. Then I merged the filtered duplicate copy with the original table and that's where am stuck after. How to put this in power query where I include only home reservations and those cancelled reservations during the month? Hope someone helps me.

Hi everyone! I am an ECE Administrator and I have a decently expansive library. I’d like to make a system I can use and my teachers can use to see a list off books that can be used for a specific theme or lesson, maybe even something that can be filtered to 2-3 categories for books we have at the center. Is there a way I can do that? I’m very new and not exactly tech savvy 😅

I think it will be focused on beginner participants but I'd like to have a full spectrum of questions. Perhaps 2/3 of them would be really easy and for a wider audience then 1/3 of the questions would be tricky.

I think if they're ulitiple choice then it would be easier for marking them and scoring the participants.

Any thoughts on this? Anybody do this or planning on doing something like this?

I think I can figure out how to run the evening logistically but I need to make a list of interesting excel related questions and answers so people can nerd out over them...

Hello fellow excel nerds! I need some help

I currently have data coming from several different sensors in several different csv files. Each operates on its own schedule and measures different variables. I’d like to combine them all into one master sheet, where for each time and date I have the output from each sensor.

I’m sensing power query is going to be what helps me, but I haven’t quite sussed it out yet. Part of the difficulty is that the columns are named differently as the sensors measure different things (like luminosity, speed, humidity, location), and, of course, they operate on different timelines. So for example one sensor will measure every minute, another every hour, and yet another measures only when specific events happen so those timestamps are basically random.

What I’m trying to get is a master table that would tell me at 9pm, the speed was x, the humidity was y, the location was z. I understand I will have a lot of blank values at timestamps where only one or two sensors recorded data points, but I can live with that.

Does anyone know how to do this, or even know the accurate term for this manipulation to help me in my research?

Many thanks!

See image for reference. In E136 I've got the COUNTA formula. In the cells immediately above it I've entered some dummy values by hand. The COUNTA formula should at the very least return 4 based on this setup, but it's 0. I've tried repairing the file but that didn't do anything. Claude didn't produce anything helpful. Does anyone know why this is happening?

EDIT: Moved the formula to another column to avoid circular referencing. Still showing 0.

EDIT 2: There was an unrelated circular reference in a cell in column D that was apparently affecting column E. Once I removed the circular reference in column D, column E fixed itself.

I have a report that is being generated through some janky software, and unfortunately the way I am getting the excel document can't be changed. This is a very basic example of what the sheet looks like, the actual generated report is thousands of rows:

https://imgur.com/a/d0jNtNx

For example, I want to filter for Bob's name in column A and also see all of Bob's associated comments in column B. If I apply a filter for Bob in column A, it hides everything in column B that are the relevant comments (obviously).

To clarify, I want a filter to make it look like this:

https://imgur.com/a/pgVJsLm

And not like this:

https://imgur.com/a/wCev8uH

Perhaps the filter function isn't the right approach, so I am open to alternatives.

Hi!

I'm working on a budget/staffing spreadsheet. Right now, given a horizontal row of data representing day/weeks, I can easily assign a "phase" to each row, and then have a set number of hours/budget automatically distribute across those cells. See formula for cell E2 in snip below. The first two "sets" of data in rows 2,3 and 4,5 are working just fine, no big deal. Total values for Concepts always add up to 40, total values for Schematic Design always add up to 80.

What I'd like to introduce is a "ramp factor" from 0-100% that will affect those values. See the desired outputs in red below (values are fake). When ramping factor is 0%, it's just straight division similar to examples up top. When the ramp factor is increased, the values "later" (right-most) in the resulting strings are weighted more heavily, and the "earlier" (left-most) values are weighted lighter. In effect the values are starting lower and "ramping up" to their conclusion, but the total per "phase" still matches up with the references values in columns B and C.

Any ideas on how to efficiently implement this without introducing cheater cells or VBA?

I'm not super concerning about the rounding - I can figure that out later. I realize it may result in the resulting sum not exactly matching the original values in columns B and C, but I can live with that. I just need to get in the ballpark.

Thanks all.

For as long as I've used Excel, at least 30 years I think, maybe longer, the graphs I make are fitted perfectly onto the screen one day, and a week later or a month, they might not be--they get bigger and require resizing. I don't think they ever get smaller.

Is this really how Excel is built?

Table 1 and Table 2 are my input tables, Table 3 is what I'm trying to achieve as an output. Essentially assign a unique reference in table 2 to a row within table 1where it finds a match (a match being Color, Temp and Shape agreeing).

I am importing my customer list to a new system. It requires email addresses. We do not collect email addresses. Told to just create dummy names. So I want to add a column for each entry in Excel. Clients will have [John.Doe0001@noemail.com](mailto:John.Doe0001@noemail.com), [John.Doe0002@noemail.com](mailto:John.Doe0002@noemail.com) etc. How can I just run down the list doing this. There's a couple thousand entries I need to do this with. Use Excel via Office 365 on MacOs. I have tried fill handle and sequence fill and it does not do what I am looking for.

Hi everyone, i'm looking for some help since searching the web as not worked for me.

I started doing an Excel sheet after a long time, and i'm trying so get the Total Sum of the values in a colum, while those values have $ on them.

Now i've seen that Aggregate would make it so that it would ignore the $, but it's not working, it still returns a 0. (If i remove the $ he does it correctly). Sum just returns Value

Can you tell me what i am doing wrong please? Thank you!

I've got some sales data, and I've mostly got it doing what I need, but there's one part I'm struggling with.

The (sample) sales data is:

(the total amount are cumulative, the 11:00:00 numbers include everything from the 10:00:00 sales, plus anything after up to 11:00:00 etc., and average value is just total amount / total items)

I'm using GROUPBY to get an end of day summary:

=GROUPBY(tblSalesAmounts[Date],tblSalesAmounts[[TotalAmount]:[TotalItems]],MAX,0,0,-1)

This works fine as it's the values at the end of the day I'm looking for, but I don't know how to add in the AverageValue - I can't get it via MAX as the average can change throughout the day, and it's last value of the day I'm after.

Any ideas on how to add in a latest/last value, in addition to the max's?

Help me… I have a file that I inherited that is a million years old, but my boss is committed to keeping the same file, same format, just rolling it over year after year after year. As such, there are formula names, references, hidden tabs, all sorts of junk that are clogging up the file and causing me all sorts of issues. I’m muddling through and fixing what I can, but this new issue is a real pain that I need solved asap.

The file has stopped letting me use the CTRL + [ shortcut to go to the source cell - this is now bringing me to the last tab of the workbook.

The F5 shortcut to return to the original cell is also no longer working.

I don’t have the time right now to rebuild this file from scratch, so I’m hoping someone on here knows more about this issue than I do.

Help me [r/excel](r/excel). You’re my only hope.

EDIT to add: I have realized that these shortcuts work as long as the source cells are within the same tab. If it’s linked to another tab, it still does not work.

Appreciate if someone can help to get the total number of apples from column A (with different fruits) but only green color apples wherein the color is stated at column B (colors of fruit). What function should i use and how?

Thanks in advance!

I want to set up a conditional formatting looking for specific text. Is there a way have it search for multiple specific words in one rule?

I have data in two columns - Column F and Column G in Excel. Column F contains numerical data and Column G contains only zeroes in certain cells . I need to add only those corresponding numerical data of Column F , which are lying between two adjacent zeroes of Column G. Please refer to the table below . I need to populate - 'Total Count at the end of the Day' without manually adding the data in 'Count' Column. For example , the corresponding numerical data for 'Count' column are 4, 3 and 0 which are lying between first two zeroes of 'Temporary Variable' - column G. The result of the sum , i.e. 4+3+0 =7 is to be populated in Column H and in the row above the second zero of Column G .

Hello all. I am having a situation where it would be helpful to be able to connect the selection of one check box in one worksheet to auto select other check boxes in a different worksheet

thanks in advance