I have a list of data of Vendors, Bill IDs and Due Dates that all add up to the amount this Vendor is due.

However, if I add ”Due date” to the rows, it repeats the values 3 times, because now it gives the Value to Due Date, Vendor, and Subtotal, which is all the same.

Is it possible to merge them, so it would just be Due Date - Vendor - Amount without the Values repeating for every row?

https://preview.redd.it/alumnhy434he1.png?width=534&format=png&auto=webp&s=4d4cad6100c7a5477fe7d6005548e39f2ac9de9f

i have problems with putting the number automatically, any idea for the formula for this?

I am currently using the substitute and text join function to be able to put all information from one row into a merged cell but the row I am using has a lot of blank cells.

My current formula is =SUBSTITUTE(SUBSTITUTE(TEXTJOIN(" ",TRUE,'Yard Turnover'!$B6:$G6),"0",""),"0","")

The problem that I am having is that the zeros are being replaced with blanks but between each blank there is still spaces so it is adding multiple spaces before and after the text that I want to keep. Is there anyway to remove the spaces that are coming from the blanks but keep the spaces between each word that I actually want to have spaces between?

I am trying to use conditional formatting to change the color of cells in column (I) based on the risk rating. The cell fill colors are different in case the risk is a "Threat" or an "Opportunity" which is mentioned in another cell (C5). The formula I'm using only works for the first cell and highlights all the rows in the same column (column I). I want to conditionally format the cell colors of column I (I6, I7...) depending on the values in column C (Threat/Opportunity) of the same row (C6, C7) etc.

This is the conditional formatting formula which I'm currently using:

=AND($C$5="Threat",$I$5>=20,$I$5<=25)

=AND($C$5="Opportunity",$I$5>=1,$I$5<=3)

Type Units Rolling Sum (A) Rolling Sum (B) A 1 0 A 3 1 B 2 0 A 4 4 A 6 8 A 0 14 B 1 2 A 3 14 A 1 16 A 2 14 A 2 12 B 2 3 B 1 5

What's the excel function that would give me the rolling sum totals as shown for A and B (these are done manually 🙊). The rolling sum is the sum of the most 5 recent observations for each type at each row from zero observation to the last which is the most recent

How do i link data with specific remarks. I have multiple columns and only need data from two of them in another excel sheet. I have remarked the data i need as "IPC-13"

Hi, is there a way to make the active rows in sheet 2 match the number of rows from sheet 1? right now i paste a table in sheet 1 where it is then referenced in sheet 2 where i make calculations according to the values from sheet 1 imports. my problem is, everytime i paste a new table in sheet 1 (which has sometimes more and sometimes less rows) i would have to adjust the number of active cells/rows manually in sheet 2. is there a way to make it auto adjust? because in my case its not only sheet 2 but many many sheets and it is time consuming to do all manually.

So lets say I have a colomn of every day of the month, is there a way to insert a checkbox only next to sundays? As I want to re-use the same set up but have to change the dates for every month. I'm looking for a command that checks which day it is (WEEKDAY i think should work) but then if it matches to a sunday it inserts a checkbox.

I have a spreadsheet for recording vehicle movements, which works as follows:

• Column F = Number of hours (period) the vehicle was used
• Column D = Vehicles
• Column B = Start time of vehicle use
• Column C = End time of vehicle use

I would like to know if it is possible to count only the hours within the business hours period (from 7:30 AM to 5:30 PM on business days).

Hello! I am super new to excel so I apologize for possibly not using the correct terms or explaining correctly.

I have the 2019 version.

For background: After each shift we tally up everything we did, (for example phone calls) and those numbers get put into daily spreadsheets that then gets put into a folder for that month that feeds into an overall spreadsheet that shows each month and work action being counted for both day and night shift.

I have two excel spreadsheets (they are completely identical other than one counting night shift and one day shift) and was wondering how I could take the numbers for example of phones counted in january for both night shift and day shift and put them into our main excel that houses all the numbers for years past.

I have the running number of phones answered within the night and day shift excels but i’m having trouble coming up with the correct way to ask this question if that makes sense. I don’t know which formula to use or if it’s not even a formula. I’m trying to make this a little bit faster than plugging each number in by itself (which is what was done before)

Hi everyone I currently need some assistance with a pie chart formatting query. Or atleast I think it has something to do with formatting. I'm creating a financial tracker for myself to track my income, spending and savings. I got this pie chart on my spreadsheet that shows me how much of my total expenditure is distributed across my many different expenses.

I want to know. When I add an expense twice into my table I want the pie chart to show the total expense amount for that specific expense (like if I have 2 entries for Netflix for 20usd and 15usd, it shows both seperate entries on the chart when I want it to be just one 35 USD)

Can this be done or not? Or do I require a another step?

Thank you.

I feel like this is a very dumb question but I’ve been at it for an hour and a half now..

So I have 500 individuals with data that shows who likes horror movies and who does not like. (1 = yes 0 = no) I’d like to make a pie chart that shows the percentage of each.

When I select that column for the pie chart, all it shows is a legend with the 500 individuals and no pie chart what-so-ever.

I’m new to power query.

I have two seperate excel tables with overlapping and also different columns.

I’m trying to make a hybrid and add to one report the most useful columns from the second report that are unique to the second report.

I’m doing this in excel by just adding the column names on the first file and doing an xlookup against the second file.

These lookups are all tied to part numbers.

When adding both files to powerquery and then trying to merge it is asking me to do a 1:1 and pick matching columns between the two only (or so it appears) or else it wont let me.

The whole point is to add the useful unique half from the second report to the first and have my part numbers do lookups against that data. The part numbers exist in both querys.

Sorry if this does not make much sense. I’m new but this can save me tons of time and help me automate it.

https://preview.redd.it/eajqgxgbu1he1.png?width=1893&format=png&auto=webp&s=09ee8d21992a7f680ff7284ca9cf97f588f1ba5f

Hi, would need some help on formulas or ways to move the text in brackets from column X to column Y. thank you

Is Data Model unavailable on Excel 2024 for Mac?

I just bought Microsoft Office 2024 and installed it on my MBP but can’t find the Data Model. Am I missing something?

I have three line charts on three different sheets. The later two sheets are just copies of sheet 1, and are in the same location on each sheet, so that when I switch between sheets, I can easily see any differences in the chart. There are visual differences in the chart in that the slopes of the lines do not look the same.

The data on all charts is exactly the same except for start date. So, for example, my first sheet has day 1,11,21,31 on the horizontal axis, and sheet two has day 31,41,51,61 and sheet three has 101,111,121,131, etc.. The intervals between the dates are the same, and the vertical data is the same (and the vertical axis data all seems correctly positioned, I'm only having irregularities with the horizontal data).

Since I was having this problem, and the sheets are all just copies of each other with the only difference being start date, of course I figured the problem was that the start date was different. So I copied the data from sheet 1 into sheet 2 and 3 so that everything should be exactly the same (including the start date), but to my surprise, the visual oddities (different slopes of lines) remained.

Worried I had somehow altered sheet 2 and 3 without knowing it, I again made a copy of sheet 1 (now called sheet 4) and pasted in data from sheet 2, and again the slopes of the lines changed. Lastly, I repasted date from sheet 1 into sheet 4 and it did return to how it used to look (so that when I switch between sheet 1 and 4, there is no difference).

I'd like to get the line chart on sheet 2 and 3 to look exactly the same, any ideas?

I know most Excel giga users work on a PC, and I’m training to improve my Excel skills. Unfortunately, I only have access to a MacBook. Would using a Windows keyboard plugged into my Mac give me access to all the Windows Excel shortcuts? If anyone has experience with this, I’d really appreciate the help

Hi! I'm currently stuck on this math problem where I have 2 years and 9 months worth of sales data.

How should I be factoring in the last 3 months (e.g. Oct-Dec 2023) when I only have 2 points of data (2021 and 2022) whereas all other months (e.g. Jan-Sept) all have 3 points of data (2021, 2022, 2023).

Please help... feeling very puzzled on how I should be calculating the averages for a monthly seasonal index and if any weighting should be applied...

After that, how should I be using the seasonal index to forecast demand for the last 3 months of 2023 and then for all of 2024?

Any specific step-by-step guidance in excel would be helpful. Thanks!

https://preview.redd.it/5qn2hycgc1he1.png?width=115&format=png&auto=webp&s=178145e4cd6b6dc9e330182b28dd41e0d27a4bf7

OK, this is now getting weird. In this question, which I asked only the other day, I described how on Excel on MacOS, Cmd-V (Paste) had suddenly changed such that it only ever pasted by value. I eventually fixed it but only by completely uninstalling and then reinstalling Office 365.

Well it has just started to happen again! And that's after a few days of everything having been fine. I cannot for the life of me think of what I could have done to cause it; so much so -- and especially given that it has now happened twice -- I'm of the view that I am not the cause. Ot at least, me doing something unusual is not the cause. By most people's standards, I am an advanced Excel user, but by the standards of the folk on here I am barely out of the rookie level and don't do anything particularly funky. So I'm pretty sure it's not me doing something exotic!

I'm posting about it this time merely to record it for posterity, in case anyone else stumbles on the problem. I am not asking for help, mainly because I'm not going to spend any more time trying to fix it. Fortunately, although I usually work on Excel MacOS, I also have it in Windows on Parallels and the Windows version of Excel does not appear to be having this past problem. So, off to Windows I go.†

Finally, in the spirit of leaving clues for others who come after me, here are two things I noticed that seemed to be correlated with the primary paste issue:

1. If I Right-clicked on a column header -- e.g. to insert a new column, or to find out what the column width was -- there was a very noticeable delay before the context menu popped up. This had actually begun to re-occur about a day after I had done my uninstall/reinstall, but since the main paste problem hadn't re-emerged, I put up with it
2. As the paste problem did re-appear -- in fact, this is what alerted me to the fact that it had happened -- I noticed a weird behavior that I had seen previously. If after copying (Cmd-c) a cell I then attempted to paste it into a range of cells, I would get the popup alert that says "The data you're pasting isn't the same size as your selection...". Weird. And, as with the main paste problem, this seems to be specific to using the Apple command shortcuts for copy and paste: Cmd-c (⌘-c) and Cmd-v (⌘-v). If instead I use the Windows shortcuts -- Ctrl-v and Ctrl-v -- everything is fine.

† Well, that's Plan A. That may quickly be switched for Plan B, which is to use all this hassle as an excuse to ditch this 2020 8/8-core M1 MacBook Air, and go buy a brand new 16/40-core M4 Max MacBook Pro. Every cloud an' all that. 😇

So, I've got a spreadsheet that I use to keep track of my uber stats (I'm an uber driver). Out of curiosity, I want to try and find a way to have it automatically sum up or average certain stats based on the month. Like, say, for example, I wanted to sum up the amount earned in each month. I copied a sample of the data and would like to know if there's a way to do this without having to completely rework the formatting of what I've already got

https://preview.redd.it/rfolqei251he1.png?width=286&format=png&auto=webp&s=dff36e5d4a157780d5718c96c33c6b380576fdaf

Someone told me to enter registration name it's very repetitive but the end name is different like Qwe-R92 Qwe-Psf Qwe-scr I want to do it like i only type their end name then the first name appear after i leave the cell/press ENTER/Press TAB like i type "R92" then when i changing cells it correct automatically to "QWE-R92" i want to be efficient as possible but i don't know how to do it

Hello Excel community!

At work I’ve been constructed to Validate multiple columns from multiple excel sheets. The first thing I saw was my columns from both files had different amount of rows. i.e from pre-test to post-test.

My question is will the IF () function accurately display the values needed if returned “yes” or “no” - despite the difference in column rows.

Cheers!

Hi all,

I'm taking a Excel data management class and got super stumped on a portion of my last assignment. I had a data set and am creating a table describing characteristics of moms based on their positive or negative disease test. I have added rows showing average number of children, average maternal age, and average risk index. However, I have to add a column for proportion of those families who are living with a partner.

My living status variable is binary, 1 lives with partner, 0 lives without partner. When I add the variable to my pivot table and filter by just 1, it still combines both the lives with and without partner variables and shows them as a percent of 100, even though not all the women live with partners. I tried adding it as both a filter and a column but I can't get it to show up as the actual proportion rather than just from 100%. Can anyone help me here at all?

The y axis labels on my bar graph are 1, 2, and 3, but I need them to be AR, TX, and MO to represent what states the people in the study were born in. I can’t figure out how to change the labels.

Hi all, very novice Excel user here so appreciate the advice. When I export a file from a web-based platform all of the data appears in one column. Is there a way to move each individual variable into its own separate column?

Hi there! I am failing at describing this well enough for a google search, so I hope some humans can understand what I need. I have a table of 4 columns (Type, Rarity, Variant, Value). In this table, there are no rows that are complete duplicates of any other, and the Value is filled in.

In another table, I will be filling in the Type, Rarity, and Variant, but I want the Value to automatically populate based on those other 3 pieces of info. Any sort of working function that I can just paste down that column will work for me.

Currently, my completed Value table is J2:M14 (including headers), and my Type/Rarity/Variant columns are B, C, and D. So B50, C50, and D50 would be referenced to pull the correct Value from J2:M14 and output it in E50.

Let me know if anything doesn't make sense, and thank you for your help!

=IF(OR(C252>=E252, C252<=F252), 1, IF(G253=0, 0, G253+1))

I have a formula that checks cell C252 to see if it breaks the threshold in cells E252 or F252. It then puts a value of 0 in column G if no breach occurs or the number of rows that have passed previously if a breach has occured. The formula works well for all that has been explained.

However, I need column G to reset to 1 in the next row if a breach has occured. Since column G reports the amount of rows previously before the breach once a breach occurs it should start counting fresh again.

Hello!

I have been trying to copy and paste values filtered data, but I am finding it very difficult.

Trying to copy the filtered values on Column BC to Column F.

In the picture above COLUMN BC are extracted values from a formula. Now, I am trying to copy and paste the values only on COLUMN F.

I have tried various methods by selecting the visible filtered data by using: "CTRL + G" and/or "ALT +; "

The outcomes are,

1. I get an error, "This action won't work on multiple selections."
2. It gets pasted on the first cell only.

Is there a solution for this, or am I just tackling it wrong?

Hello! I’m trying to cross reference two spreadsheets linked by phone numbers.

On one spreadsheet the phone number format is 1234567890 (fine)

On the other it’s 123 456-7890 (bad)

Can I easily reformat the bad one into the good one? Thank you!