/r/excel
A vibrant community of Excel enthusiasts. Get expert tips, ask questions, and share your love for all things Excel. Elevate your spreadsheet skills with us!
OPs may (and should) reply to any solutions saying:
This awards the user a ClippyPoint and changes the post flair to solved.
The best way to display code on Reddit is to
put it in a code-block
Tip: For VBA or Power Query, select the code, press Tab, then copy/paste the selection into your post or comment.
It's more helpful to show us.
Add screenshots in your text post, or provide links to uploaded images at an image-hosting site.
Or use tableit, or the ExcelToReddit converter (thanks to u/tirlibibi17), to convert your data into a Reddit table.
Congratulations and thank you to these contributors
Date | User | CP |
---|---|---|
2024.04.26 | Simplifkndo | 10 |
2024.04.24 | Gabo-0704 | 10 |
2024.04.23 | BackgroundCold5307 | 300 |
2024.04.14 | MayukhBhattacharya | 25 |
2024.04.11 | Agnol_ | 10 |
2024.04.11 | MayukhBhattacharya | 10 |
2024.04.09 | xFLGT | 25 |
2024.04.09 | Flamekorn | 10 |
2024.04.08 | posaune76 | 10 |
2024.04.06 | Alabama_Wins | 500 |
2024.04.04 | delightfulsorrow | 10 |
2024.04.03 | xFLGT | 10 |
Occasionally Microsoft developers will post or comment. They are identifiable with a special user flair.
A community since March 11, 2009
/r/excel
I have a word document that I'd love to have as an Excel table, but I'm hoping there's an easier way than copying and pasting everything over. The Word doc is about 300 entires in the below format. I was playing around with power query but not getting far.
Category name Description -actual description- Example phrases -example 1 -example 2 -example 3
Any help is appreciated
Hello!
I am trying to make a custom number format for percentages. I am trying to make it so that percents with only 1 digit before the decimal point are displayed with a single decimal (X.X%) and those with 2 digits are shown with no decimal point (XX%).
More examples: 9.27% is shown as 9.3% 12.58% is shown as 13%
I have this as my custom number format currently: [<0.095]0.0%;[<1]0%;
The problem I’m running into is negatives… I still want negatives to follow the same rules as positives. So if a number is -32.44% I want it to show as -32% not -32.4% as my number format currently dictates.
Any ideas?
I really don’t know how to word this so bear with me.
We have a document at work that gets updated every month with new data. This data is always different (we’re in accounting)
She wants me to create on a separate worksheet a way for this data to be added as soon as the new data has arrived.
We currently have the April data on one worksheet and she wants me to create a dynamic formula/way to just simply copy and paste it or very easily add the new months data.
Can anyone provide any assistance?
I need to change my x-axis to say the days, instead of specific timestamps.
I'm completely new to Excel so I'll need some handholding, thank you
Hello, I need to create a rent schedule for a 99 year lease where the annual rent for the first 7 years is 100,000/ year and increases by 10% every 7 years. Can anyone advise how to best set this up?
Hi!! I’m an excel newbie (heavy emphasis on the newbie) and I’m wondering if someone can help me out. For each person, I’m expecting a standard set of Visits (under List), and I’m trying to see if there’s a way to identify ones that are missing. I’ve been told that the FILTER function would be helpful, but unfortunately I only have access to Excel 2016. Any help would be greatly appreciated!! I’m also able to get my data on to gSheets if that’s of any help!
https://imgur.com/a/bNpPSTQ (simplified version of my data)
Hi everyone. Apologies to post the same question again. But I am here with better explanation for my problem. I have attached two sheets of demo data to explain the problem.
First sheet: Companies A-J ; second sheet: Companies F-O
How do I validate data based on sales value for companies A-O?
(i.e. it should check for sales values and give 'correct' or 'incorrect' based on matching sales values like 'correct' for company G and 'incorrect' for 'F', and also tell me the company names whose sales value are missing from either of the sheets like A-E and K-O here).
Does anyone else struggle to work with or understand prebuilt spreadsheet models? I recently started a finance job and find myself spending too much time trying to understand the structure and calculation flow of these spreadsheets before effectively using them for my usecase.
Does anyone else find this challenging? Curious to know what you do and if you have any tips or useful resources.
Hi, I want to filter my date column so that it dynamically only shows dates from the start of our financial year (April 1st). So only dates from April 1st 2024 should be included up to end of March 2025 and then restart to only show dates from April 1st 2025 etc. Any ideas?
I am playing BitLife, and I decided to make this spreadsheet because I now have the Investor DLC along with the Time Machine DLC.
I made this to keep track of the in-game crypto market. I write down the current cost of each crypto based on my current age which I can edit the first cell, and the rest of the cells will soon follow as they are each set to =Previous Cell +1. The yearly return calculates the cost row as =(Current Cost-Previous Cost)/Previous Cost, and the total return is =(Current Cost-Year 1 Cost)/Year 1 Cost.
This spreadsheet is clearly not visually appealing. The colors are based on the crypto icon in the game, and as I am writing this, I am thinking of switching the two colors of the cells and outlines, or just changing to green or something. Also, I'm not entirely sure how to make the cells of the following years empty without taking out the formulas. I was thinking about using conditional formatting to make it invisible until I add something to the Cost rows, but I don't think it'll work since it's a formula and not actual text.
Another issue are the percentages. I get somewhat crossed eyed looking at them, and I can't pull too much information at a glance. I ended up changing each yearly return row to a darker color because of this issue. Right now, I am thinking of adding a conditional formatting rule to look at each total return row, and separate the columns into 5-year intervals, and highlight those with highest return in green, and those with the lowest in red. That's going to be a tedious task, but if there is no better option then I'll do it. I'm not sure what else I can add aside from that.
Hi, I was hoping someone who knows about Power Query can help me.
The scenario is that I'm getting a table from a web source (web API) using an Excel table column. So far this worked (although took me a few days to get it to work.
But the issue I have is that the value I want to put in the query comes from a field inside a record (record "B") and this record "B" is also inside record "A" which is the result from the web query.
You can see in the screenshot how I'm trying to get the final value into a new column based on each record from "Column1".
https://drive.google.com/file/d/11_-yxMgfkGsBvnrlb1TGo_PShuSWrMz-/view?usp=sharing
https://drive.google.com/file/d/11UaDMQ7u6HUdjkjlxhR1kVkxQpsWcsRT/view?usp=sharing
Here you can also see that the first record has another record in the field "buys".
https://drive.google.com/file/d/11OJW7KgdwyAp-eLKywFCxhGa6kI-ighb/view?usp=sharing
And here is the error I'm getting:
https://drive.google.com/file/d/11NoczqPnOT03QEEiOFMloylgI1KWmUZE/view?usp=sharing
I managed before to get the first record "A" into the column "Buying Price" and also know I could create another column using the same formula to get the value from record "B" but I was wondering if I could do it all in one step.
Here is the formula I used to get the record "A" into the table before:
https://drive.google.com/file/d/11S5JhgUy2E5wXOKEBAe_HtdLljdcX-TD/view?usp=sharing
Thanks in advance for the help.
Is there another formula I can use instead of sumif/sumifs when the data I'm searching/summing is in a table format? The data has headers in row 1 in column A, B, C etc and there is a filter at the top. I frequently need to insert rows and then sort all the columns according to Column A from smallest to largest account number (example). However if there are sumif formulas going to these columns, me sorting the data screws up the sumif formulas.
So is there another formula I can use instead of sumif where it won't get messed up? Or is the only solution to change the format of my data?
Thanks
Okay so I have a list of vendors that an employee is responsible for paying invoices.
I have a monthly excel sheet of a large data set of vendors (that multiple people are responsible for). I need to figure out a way to have excel pull out the data for the 1st list.
I'm not sure how to do this.
My first guess was to do some sort of highlight rule and have excel highlight just the names on the 1st list and then filter by color. But I'm not sure how I would even do this and there might be a better way.
How do I make a formula that says the second or any business day of the current month? I tried everything but nothing works and i'm feeling dumb.
I have a large data set that each line is a specific date and then an action that was taken on that date. The actions are often the same across several dates. Is there anyway I can consolidate the mutiple lines of dates with the same action into one line with the range of dates?
Current Example w/ fake Data:
How I would like it to look:
Hi All,
I am trying to figure out a formula that outputs the remaining number of pay periods in the year (as of today) when the pay schedule is semi-monthly and pay is given out on the 10th and 25th of each month. I figured out a simple formula if the pay was bi-weekly but I can't figure out the solution for a semi-monthly pay schedule. Please let me know if you have any solutions!
Example: Today is April 29th, 2024 so there are 16 pay periods remaining. The # remaining should decrease on the 10th and the 25th of each month until there are 0 pay periods remaining on Dec 25th.
Thanks!
Hi all, I'm a recruiter and looking to build a spreadsheet with info about our clients that updates in real time, including company headcount. This information is publicly available on sites such as LinkedIn but it's not in a table or anything so I can't figure out how to get Excel to pull that number specifically from the webpage. Here is an example of a page I'd be pulling from, any thoughts?
Have spreadsheets with a column of cells which contain one or more domain host names, each separated by a line feed.
Example:
--Cell A1--------------------
--Cell A2--------------------
Want to plug in a formula (or column of formulas) that will return a count of how many names exists in an entire column of cells of this type.
as of now i can insert a row above or below where ever but as soon as i go to sort the columns, that new row i put in gets pushed to the bottom like it doesnt "stick" or "bind" to the row i want it to. assistance requested.
When looking at a simple chart of data that shows monthly changes, how do I convert that into annual percentage change? For example looking at BLS PCE data, 12 months of MoM change, does not equal the YoY change.
Does anyone know the calculation for that?
Sample data below. This continues on for roughly 60 years.
Thank you.
Does anyone have any good templates I can use for a project management and capacity tracking in excel. Ideally one where I can build snapshot visuals of individual team member capacity. I've been looking at Monday and asana so something similar would be great. Thanks
Hello,
I've searched high and low, consulted with Chat GPT, and am striking out. I am working on a form in Excel that my team uses as a coversheet for cases. I have another file that we use as a log that contains information that I would like to have automated on our coversheet. The coversheet has a field for the account number which would be a cell that is manually entered, once the account number is entered I have other fields that populate. The field that I am running into the issue is a field that references what medical carrier the account has. I need a formula that when the account number is found on the reference log, it needs to match the word Medical in column K. If medical is found in column K, I want it to return the value in Column L of the carrier name.
I'm redoing one of my old spreadsheets and using tables this time around.
A formula in one of my columns references the previous row. It works fine on 2-whatever, but obviously the first row can't and I want to use a slightly different formula there. How would I go about doing that?
I've encountered a weird behaviour and only found garbage googling this issue.
The workbook I'm working on acts as an entry mask for laboratory data. All cells except those that serve as the data entry masks and settings are locked by a macro (one of the dropdowns: YES/NO). The macro only locks the interface and still allows other macros to change the sheet with the data table.
When I lock the workbook with my macro, and test the other stuff, everything works fine. When I open an unlocked workbook, lock it with my macro, everything works fine. But when I save the workbook in a locked state (stored in a MSTeams channel), close it, re-open it, all my other macros crash with the "you are working in a locked sheet" message. If I unlock and lock it, everything works again. It seems, like the information, that only the interface is to be locked gets lost somehow. Is there a way to preserve this information?
It's really irritating to the users in the lab and I don't want to give them the option to unlock everything, that would defeat the purpose.
Excel version: Microsoft 365 Apps for business version 2403
I am trying to set up a data validation for a cell that is only suppose to have a number between -14.00 to 25.00 or if there no data it will have a hyphen.
I keep getting stuck on trying to brain storm how to start this. Any help is appreciated
I'm trying to collect the letters that have a value over a certain amount, e.g. over 50, and then returning them in rows. The cells in red are the desired outcome. Currently I'm using the filter function on each column, and then using Vstack to put them together. I'd also like to do a lookup on the final column against a range, and return Y or N depending on if the letter is in that range.
The issue is I have thousands of rows and dozens of columns, and my current solution is slowing down the workbook considerably. Any help is appreciated.
I constantly have to check on vendors and make sure the orders arrive on time. We already have a log sheet for order placed and date it was requested..
Column A2 has the dates. If 3 days has passed, i want excel to send an email (found in Column E2) Hello, I would like to know the status of this order.
and add text "requested update" on G2.
Don't send if "requested update" on G2
Greatly appreciated
Hello!
First time posting here and very new to macros and VBA. I am trying to create an automated system for data analysis. Depending on the project, the number of rows and columns in a data table changes, but the formulas within the table would be consistent. Is there a way for a user to input information to generate a table that includes the correct rows/columns and fill in a formula in specific columns? Happy to provide more information - just not sure if this is even feasible to do.
Thank you!
Built a chart as described above with this guys technique (https://m.youtube.com/watch?v=bQs0p3VxmZQ). For some reason an extra ticker section is appearing at the end which is ruining the formatting. Only goes away if I add a random piece of text which then ofc appears on the graph. Any help appreciated.
We have 3 cells, validation dropdowns as per the following table.
Cell1 / Cell2 / Cell3
General / Team Materials / Equipment
General / Team Materials / Office supplies
General / Team Materials / Infrastructure
General / Review / Job descriptions
General / Review / Job interviews
Payroll / Absence / Permission
Payroll / Absence / Disciplinary
Payroll / Days off / Vacation
Payroll / Days off / Holiday
So for example, dropdown for Cell 1 shows General and Payroll.
If payroll is selected, dropdown for Cell 2 will be Absence and Days off.
If Days Off is selected, Cell3 will have a dropdown with Vacation and Holiday.
I need this to be done using a single table or range as shown above.