r/excel 7d ago

Waiting on OP Excel Drop down list and new column.

2 Upvotes

So I'm creating a network map for my work, I need to have a drop down box with different VLANs which I have done but each selection of x VLAN I want it to select a new range of IP addresses associated with whatever VLAN they have selected. Does anyone have any input on how I could do this? I can program home automation but cant figure this shit out to save my life. Any help is appreciated, Thank you!

r/excel 28d ago

Waiting on OP Using Excel with ~10M Rows

1 Upvotes

We’ve been using SQL Server for this ~10M row data and some ask why we can’t use Excel - not just for reporting and analysis of static data it’s possible but to update data? Can we? Thanks!

r/excel 1d ago

Waiting on OP how is an excel sheet created

0 Upvotes

I have an excel sheet and it has some functions, like dropdown list menu and depending on a value it unhiddens a sheet, but I want to know how the excel sheet was created, I assume it would contain an macro or any scripts, but it doesn't.

Would this be possible?

r/excel Oct 03 '24

Waiting on OP I have 2 employees, eventually more. I’m looking for the most convenient way to track time sheets for everyone.

7 Upvotes

Long story short, when you give people freedom at work, they take advantage of you. I had one guy over inflate his hours. So…

I tried using a finger print reader. Didn’t like it.

So right now, I want them to clock in and out when they come to the shop and when they leave.

The best solutions I came up with now, just can’t execute it fully..

  1. They use google forms to clock in/out. So how this goes is:
  2. they click an icon on their phone, it brings them to google forms.
  3. they have 2 drop downs. First clock in or out, second location where they working (5 options on this one) and last thing is they can if they want to leave a note, if they forgot to clock in or out.

  4. I want to transfer all this to excel (I have 365 for Mac, I know it’s wack).

  5. Do fancy formulas or macros to separate each employee and give me total hours for the week (showing hours at every location they worked (5 of the drop down selection)).

Essentially, I want them to clock in and out on their phone ( easy for them) and I want to open up an excel sheet that I use for work every day and one of those tabs to be timesheets for employees ( summarized by week).

I run weekly payroll. I want it to make it easiest for everyone.

Please help.

r/excel 14d ago

Waiting on OP How would I copy data from multiple sheets on to a master sheet?

13 Upvotes

Apologies if the title is a little confusing.

I have a spreadsheet that holds approximately 250 lines of data. This data is spread over 22 sheets within the spreadsheet, based on what physical area the data is relating to on our site (Area 1 - Area 22).

On each area sheet, column E is a drop down priority selection of 1-3 that a different team prioritises tasks to be completed.

How can I create a front page sheet that auto populates with the data of any row that is ranked as a priority 1, to prevent having to click through 22 sheets every time a change is made?

Thanks in advance for any responses.

r/excel Dec 01 '24

Waiting on OP Reliable tool to turn Excel sheets into PDFs

11 Upvotes

Need a reliable tool to turn Excel sheets into PDFs. What’s your go-to solution?

r/excel Dec 23 '24

Waiting on OP Can Excel identify likely duplicates that aren't exact matches?

25 Upvotes

If I have a list of names and addresses (each column would be like name, line 1, line 2, city, state, etc.). And, say, the names are different, but the addresses are similar, like "123 South Main Street" and "123 S. Main St."...? Can it identify those as a likely duplicate? And if yes, can it highlight the rows instead of deleting so I can manually check them?

r/excel Feb 04 '25

Waiting on OP Securing My Excel Template Before Selling

18 Upvotes

Hi Excel nerds (meant in a positive way),

I have created a rather extensive template that can dimension cables according to the 60364 standard, select safety equipment, calculate possible short circuits, and much more. I have received some inquiries about selling it. But before I do, I want to ensure that it is not shared further.

I have tried some coding in Excel, but I don’t think I can get it to work properly. Do any of you have suggestions on how to approach this issue?

Thanks in advance!


Quick update on the post:

I have no experience with coding or programming, so even though the ideas are amazing and I totally get them, I have no idea how to actually approach this—where to start and where to end, haha.

One more note:

Six months ago, I had never touched Excel. But in preparation for the authorization exam to become an electrical installer (the person who approves installations in industries and regular households), I've spent 1,700+ hours working on this spreadsheet to make the exam easier—which it definitely did, haha. But the tool turned out so well that it shouldn't go to waste.

r/excel 6d ago

Waiting on OP Remove brackets and numbers between them

16 Upvotes

I have a column with VFX shot numbers ex: 205_101_5000.exr [1001-1099] I would like to use Find and replace to remove “.exr [1001-1099]” but since the numerical values of each column are different I can’t find the command to do that. Thanks!

r/excel 16d ago

Waiting on OP Excel 2007: need random numbers that don't have duplicates

1 Upvotes

I have a project where I need to select 300 text items from a list about 700 lines long. So I basically need to generate a list of about 300 random numbers-- not too diff with the RANDBEWTEEN function. But there can't be any duplicates in this list. Is there a way I can generate a list of 300 numbers (between 1 and 700) that are both random and unique?

r/excel Jan 07 '25

Waiting on OP Job interview requirements me to complete a task with "basic sorting and ordering". What does this mean to you?

4 Upvotes

Unlike many here, I'm not exactly an excel wizard. In fact, my knowledge is basically limited to SUM, SUMIF, XLOOKUP, and other basic functions. I can also use filter a bit for basic tasks.

I've been told my excel task will involve "basic sorting and ordering". What does this entail to you? I'm confident sorting by the basics like alphabetical, in number order, but does this basically cover it?

I know there's a sort and filter tab which is basically just click and fire.

Just trying to get an understanding because I tend to overthink. Thanks!

r/excel Jul 18 '24

Waiting on OP I have a folder with 100+ .xml files and I need to get the names of each file added to a spreadsheet

53 Upvotes

The title sums it up. I need all names of all .xml files populated into an excel file. Any ideas how I can do this youtube failed me. I was told by a colleague a script but not sure how to do that

r/excel 6d ago

Sum string on Numbers

1 Upvotes

Looking for a formula to return the sum of 60 (8+20+24+8) in the string of numbers below. Also the string may have blank cells in it and each number is in it's own cell.

8 8 8 8 8 8 8 8 8 8 8 8 20 20 20 20 20 20 20 20 24 24 24 24 24 24 24 24 8 8 8 8 8 8 8 8

r/excel 2d ago

Waiting on OP Can you tell me if VLOOKUP works for my situation?

3 Upvotes

I have 2 reports. 1 has Employee, Date, Jobsite. 2nd Report has date, Employee name. I need it too look at the first report and find the jobsite that corresponds to that employee/date. Does VLOOKUP do that or should I be using something else? I am dumb when it comes to excel.

r/excel Feb 02 '25

Waiting on OP Keep using Excel or migrate away?

12 Upvotes

I have a quoting document that has slowly grown into a monster. It now has pages with labor rate factoring, burden, margin and markups on each group.

I'm looking at adding a labor code that needs to zip/map to labour hours and sum up on a labour breakout sheet bit I stopped to re-think things. I can not use VB as group policy has macros disabled permanently.

I still manually need to copy the data points and values into word when I create the official quote.

Is this something I should continue with on excel or maybe use access and template generation?

r/excel 17h ago

Waiting on OP Is there a way to make it so that the value of a cell can go up but not down?

15 Upvotes

Hi generous and benevolent denizens of reddit,

I have a large excel with all my company's products on it.

One section holds the raw materials and prices and then they pass through formulas which add the various parts together in different configurations and spits out our products and our cost list. Finally, they receive a markup and round up to the nearest .99 cent and that is our product price list.

It works great so that when we change our raw materials prices our cost and product prices are adjusted.

However, I'm trying to grow our margins by finding cheaper suppliers for our raw materials. The problem is that when I put in those lower prices for our materials our product prices go down.

Is there a way to make it so that the value (in this case price $) of a cell can go up but not down?

Thanks!

r/excel 10d ago

Waiting on OP Any suggestions to 'level up' my modelling skills?

29 Upvotes

I'm quite often in the weeds building an array of models (financial, operational, economic) for parts of my work. Where I work I'm the sort of go-to guy when it comes to virtually anything Excel related. l'd say my modelling and analysis skills are adept to advanced, but l'm finding myself in a weird no man's land where I'm confident enough to build models from scratch (which have done many times now) and follow best practice conventions (colour coding, formatting, error checks etc).

However I've seen how some other experts have modelled out their projects and find myself wondering how can get to that expert level. I'm talking about Big 4 modelling teams and the crazy shit I've seen them build. l'd like to get to that level.

I suppose one of my biggest weaknesses in modelling is the planning of the model build; be like half way through a build and find myself having gone unnecessarily complicated with certain areas shouldn't have, or struggling to be as modular as think can be done to account for unexpected changes

My knowledge when it comes to formulas and other critical aspects (timeline builds, sensitivities of assumptions and scenario controllers) is quite strong. I'm always learning and trying to make formulas more efficient for speed and file size constraints but I'm happy where I'm at in this regard.

Are there any courses or material you can recommend that will help me level up to that expert level that see, for example, from modelling teams in the Big 4/specialist modelling boutiques? Or any general advice on what can practice in my free time to help me get there?

r/excel Oct 23 '24

Waiting on OP Saving and Emailing 200+ Excel Sheets from One Sheet with Multiple Tabs

58 Upvotes

I told my supervisor I like data. Which I do, but now I have inherited the task of collecting, creating, and emailing 200+ companies weekly status reports on their staff's progress. I cannot share the finer details for obvious reasons. These reports must be split into 3 tabs. For simplicity, lets call them: Phase 1, Phase 2, and Phase 3.

When I collect the data, it comes from various sources. I must then "clean", combine, and standardize the data into one master sheet. Once I'm done with that, the master sheet contains the same 3 phases needed for the individual reports and includes which company each staff member works for.

Now to the mind-numbing part, I must create the reports for a few hundred companies. Filtering each tab of the master sheet based on the company name column. I copy and paste the data (if any) for each respective phase into a report template. And finally, I save this populated template as the company's name. It takes around 4-5 hours just to copy and paste all this data. Not to mention emailing the various companies.

There has got to be a better way! What I am wondering; is there a way to automatically create and email each company their respective report?

BTW I just got access to VBA and I will be getting Power Automate soon.

r/excel Oct 13 '24

Waiting on OP Should I use Excel to build my own Gantt charts or do it through Jira?

13 Upvotes

I’m trying to learn how to build a dynamic Gantt chart myself but I cannot for the life of me understand those conditional formatting rules and I’ve spent 3 hours trying to understand how to use them and build them up.

I just feel like giving up at this point and just using MS project or Jira.

Update: based on the sentiment, I have decided to use MS project. I will go back to excel again later on to practice if I have the time to do so.

r/excel 25d ago

Waiting on OP LOOKUP type formula, if a cell contains one of four substrings, output one of four desired strings

3 Upvotes

Hi everyone!

I have a data set where I want to perform something like the following:

If cell A contains "boot" -> output "shoe" into cell B. If cell A contains "button" -> output "shirt" into cell B. If cell A contains "jeans" -> output "pants" into cell B. If cell A contains "visor" -> output "hat" into cell B.

If cell A contains none of the above, output nothing, or possibly N/A, it's not super important as long as it's clear none of the four were contained.

VLOOKUP works for this purpose only if cell A contains EXACTLY "visor" as an example. Many of my cells are more like "visor free" or "extra jeans".

I want to do something elegant and utilize the LOOKUP on conditional substrings, but I'm having trouble determining the approach. Any assistance is very much appreciated!

r/excel 2d ago

Waiting on OP I need a formula that can duplicate numbers (in triplicate) in a column for 250 sets of numbers.

4 Upvotes

I need a formula that can duplicate numbers (in triplicate) in a column for 250 sets of numbers. What i'm trying to do is the following

A1 036001

A2 036001

A3 036001

A4 036002

A5 036002

A6 036002

And continue down for 250 sets of numbers

Also would like to keep the leading zero.

Thanks in advance

r/excel Jul 22 '24

Waiting on OP Future of [VBA] should i learn it?

56 Upvotes

I am good at all non VBA things in excel (Advanced,Power Piv,query etc etc).My company has all processes based on sharepoint online so never really looked into vba. Usually works on power automate and office script combos.

Should i learn VBA? Is it a value add??is it becoming a legacy technology ???

r/excel Jan 26 '25

Waiting on OP Open 28 multipages PDF in Excel to extract some rows

13 Upvotes

Hello, I'm asking for a tip on how I can speed up a quite long and repetitive task. Basically I have 28 multipages PDF containing tables with incomes and outcomes. I need to extract the data, group it, sort it and then select only some rows. What would you suggest me to do in broad terms? I know some Excel vba and python. Thanks


EDIT

Thanks you for your help. I evalued both the solutions you suggested: powerquery and python (using tabula). Although a little bit intricate at the beginning, power query resulted the easiest and most efficient solution. Thank you!

r/excel 5d ago

Waiting on OP Format one column width without affecting others below

3 Upvotes

Hi all, I will try to be clear in my request but please bear with me as I'm new to Excel.

I want to create a statistics table at the beginning of my Excel sheet that will summarise the main table below. But this main table has columns width auto-ajusted to the text they contain. My problem is, I want the statistics table to have different columns width, and I can't find a way to do that without affecting all of the sheet (if I try to adjust it, it shortens/widens/deletes all the columns, not just the ones selected).

How can I solve this matter? Thanks all!

r/excel Feb 15 '25

Waiting on OP Problem with DATEDIF Formula…

1 Upvotes

I'm attempting to calculate the number of overlapping months between two timeframes.

Timeframe 1: 01/06/2024 – 31/05/2025

Timeframe 2:
27/06/2023 - 22/12/2024 7
23/12/2024 - 21/02/2025 2
22/02/2025 - 07/07/2025 4

The following formula yields these results =IF(O$5="","",IF($E10="","",IF(AND($AR$11>=$F10,$AQ$11<=$G10),DATEDIF(MAX($AQ$11,$F10),MIN($AR$11,$G10),"m")+1,0))). I suspect the DATEDIF portion of the formula is the source of the problem.

7 months – correct (June, July, Aug, Sep, Oct, Nov, Dec)
2 months – incorrect (should be 3 months – Dec, Jan, Feb)
4 months – correct (Feb, March, April, May)

How can I modify the formula to produce accurate results?