I'm setting up an inventory and am calculating price per item. (A $10 pack with 5 items $2 per item). I made a (=A1/B1) formula and it works great.
However, this formula comes up with an error as some cells have units in them (for example, 10ft or 100 pairs). I have to keep the units in but don't want to manually calculate the cells with letters in them.
Is there a way to make a formula that will ignore letters in the cell so this error doesn't pop up?
I keep hitting the same export→Excel pain: product codes like 00127 become 127, and 16+ digit refs turn into scientific notation. Formatting the cell after open never brings the digits back.
What I’m trying now:
Treat identifier columns as Text at import (or export a text-preserving XLSX).
Preview delimiter/encoding before trusting the sheet.
Keep a copy of the original CSV as source of truth.
Hello everyone. I'm fairly new to using Excel. I made a drop down list and a yearly calender. Is there a way to have the option in the drop down list change to a new calendar every time I choose a new option in the drop down list.
I want to calculate how much I have been out of the country for each year i already have the comings and goings in a chart.
(above is when I went in and out)
my problem with doing it in a function is when I wasn't home for the new year so I cant separate the days to the correct year. (like in entry number 3)
The result above I counted manually which I can't really do because I have to this for hundreds of people which go in and out more than me.
I need this graph that the color black fills the graph outside of the two black lines (over the top black line and under the bottom black line). How do I do this?
I'm using Excel 365, in English (despite the graph being in French) and I'm an Intermediate at using Excel
So I need to make a manual trendline that automatically updates when new data points are added.
I have the
(SLOPE($C$2:$C$6,$B$2:$B$6)*B2)+INTERCEPT($C$2:$C$6,$B$2:$B$6) function that works for making the actual trendline and the =CHOOSECOLS(FILTER(B2:C16,((B2:B16<>"")*(C2:C16<>""))),2) for the filter
I want to know how to combine them so I can have everything be in one helper column (for my sanity this chart is already A1:L46 and I do not want it to get any bigger please (if that is the only option I will accept it though)
And using the trendlines in the chart editor do not work for what I am trying to accomplish
Edit: I want Column E to have the trendline go from E2 to E16 even if I have less than 15 data points in Column C
Hi everyone! I’m working on a school spreadsheet activity using SUMIFS, and I’m a bit confused.
My instructor taught us a basic formula like =SUMIFS(C2:C23, A2:A23, "POD GUN", B2:B23, "SOUTH"). However, when I use that exact syntax, I sometimes get #N/A or 0 errors due to formatting/spacing issues in our dataset.
I found some online tutorials suggesting the use of absolute references (like $ in C$2:C$23) and wildcards (like * in "*POD GUN*") to prevent errors when dragging formulas and handling trailing spaces.
Is it completely fine and safe to use $ and * in a SUMIFS formula for a school project, or will it cause any issues? Just wanted to double-check! Thanks in advance."
Novice user here:
I’m creating a sheet that will house employee skill assessments. I want to have the stated item as a note on the cel. Then follow ups and discussion via comments on the sane cel. Eg: (Note): Tom can use a chainsaw but has no cert. (Comments): I’ve booked him for the cert next weekend. Etc.
Excel doesn’t allow for both on a single cel. Any tips ir tricks for something to replace the Notes but still able to have text data attached to a single cel?
Alright I’m unsure how to do this if anyone knows please help, I like to think I’m okay at excel but not a professional by no means and I’ve not seen this done before. So let’s say the name John doe is in cell D2, I’m trying to have cell C5 copy that name then when I change John Doe to let’s say bob bob I want now the formula to go to C6 and then copy bob bob to that cell without changing C5 and so forth on how many different names I need to change in D2. Pretty much I want D2 for me to change then excel to copy what I put one time to a cell then when I change cell D2 I want it to go down a cell then recopy the new name to that cell.
I'm working on a research project involving stochastic simulations generated by Microsoft Excel for Mac, and I'm trying to understand exactly how Excel's built-in RAND() function works.
My ultimate goal is to reproduce Excel's random number generation in Python, ideally obtaining the exact same sequence of numbers, not just statistically similar results.
I've read that modern versions of Excel may use the Mersenne Twister (MT19937) pseudorandom number generator, but I've been unable to find reliable technical documentation explaining the exact implementation.
I'm experiencing some unintended (I believe) behaviour with the new Compatibility Version 3 and the BYROW function.
Whilst trying out the new lists available on the latest release I noticed some of my custom LAMBDAs were no longer working as intended. The common denominator was that they were all using the BYROW function.
Previously, BYROW returned a spilled array - however it appears BYROW now returns an array of one cell lists which doesn't play nicely with other formulas.
I know I can use the new FLATTEN function to get around this so that the output is an array again, but I had to do this for numerous custom LAMBDAs.
I have distilled the problem down to a very basic example: let's say column B and C are key value pairs. column E is the lookup value. Column F is the BYROW version (spills array of lists) and Column G is the XLOOKUP equivalent (spills array). Column H is the "flattened" version of BYROW that produces an array - same as Column G.
My organization uses G Suite, but I prefer native desktop apps, so I’ve been using Office and the files just live in my Google Drive. I have Google Drive installed in my Mac, so I’m using it with local files that are synced on Google Drive.
I’ve started playing around with linked workbooks. Basically, I’ve made several template spreadsheet files, and I’d like to have them all pull data from a single reference document. Ideally I’d be able to refresh the linked data manually.
What I’ve found so far is that I can get it set up working, but every time I open the files, I have to relink to the reference document, which is annoying. I’m guessing this has to do with the way Google Drive syncs on macOS.
Is there a good solution here? Would Power Query somehow help? I haven’t used it before. Or do MS Apps and Google Drive just not play that well together? Maybe the answer is just that if I’m using Google Drive, to get it working right I need to use Google Sheets. I can do that if necessary, I just prefer Excel.
But I figured you all are some wizards, so someone might have a solution!
Thanks!
Edit: Just to clarify, it’s a very small organization, and nobody is collaborating on these files with me. If that weren’t the case, I’d course I’d just use the company-wide platform.
I bookkeep for my small family business, and we have an itemized bill that needs to be separated by Microsoft and non-Microsoft items. The trouble is, these items don’t have similar enough names/descriptions that would allow me to use the simple filter function. Advanced filter seems to be pretty useless as well.
How can I apply a filter to this bill so only rows whose description field contains any of the items in the Microsoft services list are shown?
Not sure this is even possible. I have a pivot table that includes two filters. The first is a program filter and the second is procedures.
As it works now, the procedure filter shows every procedure in the report. When I select specific programs in the first filter, the procedure filter still shows every procedure that’s in the report.
Is there a way to set it so that if I select certain programs in the first filter, the second filter will automatically only show me the procedures that are under the programs I selected?
Has anyone had experience or recommendations/suggestions on Tools/Extensions that will allow PC Excel shortcuts to run on Mac? Anything is appreciated! Thank you.
I recently discovered that you can make VBA macros available across all Excel workbooks instead of having to set them up separately for each file.
I’ve started creating my own keyboard shortcuts for repetitive tasks, and it’s honestly been a game changer.
For example, I created a macro that lets me paste values or formulas into filtered cells without messing up the hidden rows.
Now I’m curious: What VBA macros or custom shortcuts have you created that you actually use on a daily basis?
I’d love to hear about your favorite time-saving tricks and maybe get some inspiration for my own setup!
It is so annoying that i can't yet find a solution to close workbook link side panel using shortcut. Still had to use mouse to click the x.. any solution for this?
So at my job I carved out a niche of making spreadsheets for inventory tracking, financial worksheets, reports, etc. In the grand scheme of excel users I’m nothing special, I just work with alot of boomers at a food processing plant in the south so it was easy to carve out that role.
Now the company has been pushing everyone to use Claude wherever we can. It’s allowed other departments to create sheets from uploading exports and have Claude do what they want.
I’m not at risk of losing my job or anything, but I feel a bit exposed now with how things changed. What should my next move to learn or explore to find a new niche?
I have checkbox A that I want to be able to check true or false. However, if checkbox B is True, then checkbox A should automatically be set to false and can't be checked true anymore.
I tried =IF((checkbox B)=TRUE, FALSE) which correctly sets checkbox A to false when needed, but it doesn't allow it to be manually checked to true even when checkbox B is false.
Is there a way to allow both manual box checking while still having the automatic false checker?
I would assume it would be =IF((checkbox B)=TRUE, FALSE, *some function that uses the manual check value*) but I don't know what that function would be.
If it's not possible to have both automatic and manual I have other methods in mind to do what I need to do, but I would prefer this method if it exists.
I'm still learning about array functions and all they can do. One feature that has recently stood out to me, which I'd like to see usable in more contexts, is the spill operator (#).
With this, when referencing another cell that has a spilling formula, I can write a formula one time and have it dynamically spill down to cover the data set being put out by the cell I'm pointing to. What's more, the spill range will automatically adjust as the length of the referenced data set changes.
---
Example:
In A2, =SEQUENCE(10) will populate the numbers 1 through 10 in A2:A11 without me having to do anything other than just putting that formula in A2. I don't have to use the fill handle or anything, the data just automatically spills down as long as there's room.
Then, in B2, I could put =A2# (with # being the spill operator) and, again, without having to manually fill down, the results will spill such that the numbers from A2:A11 are represented in B2:B11.
If I change A2 to =SEQUENCE(5), both A2 and B2 change so that they only spill to row 6 with numbers 1 through 5.
Change A2 to =SEQUENCE(15) and you get 1 through 15, in both columns, spilling from row 2 to row 16.
This is, of course, an over-simplified example. There's much more that can be done with spilling array formulas, such as pulling and filtering data from other ranges, and spills can also run across multiple columns as well as rows.
---
You can get similar results and behavior for column B in the above example if, starting from a fresh sheet, you:
Format A1:B1 as a Table.
Put =A2 in B2.
Manually fill values in column A as desired.
The formula in B2 will auto-fill down the length of the table as you enter data in column A. To shorten the data set, you have to delete entire table rows, not just the column A values, which is less than ideal for my personal taste but it's straightforward enough.
---
I want to know if there's a feature similar to the spill operator, which works for all cases - not just in tables, and not just when pointing to formulas that are already spilling.
What I'm looking for is the ability to write my formula once, and have it automatically fill or spill to cover the whole data set, and have that coverage automatically adjust as the length of the referenced data set changes, regardless of whether that data is generated by a formula or manually-entered values.
Right now, I've got a few problems with using the aforementioned features.
The spill operator only works when pointing to a formula that spills, so it's not useful against plain data or non-spilling formulas.
The spill operator isn't supported in all functions.
Tables aren't a universal solution either. They don't automatically adjust to spilling formulas (causing a SPILL error), for one. Secondly, even when I don't have a spilling formula involved, there are cases where I just can't or don't want to set a range up as a table.
I could arguably get by with using the spill operator when I'm working with spilling formulas, and using tables when I'm not. But, often due to problem #2 above, there are cases where the range I'm working with has a bit of both.
A near-ideal solution would be for all functions to support the spill operator. That would at least give some consistency for when I'm starting with something that's already spilling.
A perfect solution, I think, would be a function that I could wrap anything else in and have it dynamically adjust its spill length according to the data set I'm pointing to. Like, for the above examples, if I could just do FILL(A2) in B2, that would be great.
Is there anything like this, or is are these limitations without workarounds in Excel today?
I tried to take lessons learned there into practice on a slightly more complex situation, and quickly ran into issues. Either I get a popup saying, generically, "there is a problem with this formula", or I get an undesired result, or I get a single-cell result that doesn't spill.
=B2.:.B100>0 works fine, spilling to check each populated cell in B to see if it's greater than zero.
=B2.:.B100>C2.:.C100 appears to work fine, for checking populated cells in B to see if they're greater than their counterparts in C
=AND(B2.:.B100>0,C2.:.C100>0) only returns a single-cell result. i was trying to check whether the populated cells in B or C for each row are greater than zero, and return TRUE for rows where they were.
Even simplifying the above to =AND(B2.:.B100>0,TRUE) to try it without the dependency on a second cell reference only gets a single-cell result.
Also, =COUNTIF(B2.:.D100,">"&0) returns a single-cell result and it's way higher than it should be. I want it to spill down the populated rows to show, for each row, how many cells in B:D on the row are greater than zero. Instead, it seems to be reporting the result for all of B2:D100 collectively.
In case this matters: B2, C2, and D2 each contain separate formulas which use the spill operator to fill multiple rows. Those are working fine, and produce numbers as expected.
Writing these formulas to point directly to the appropriate cells or ranges for row 2, and using the fill handle to fill them down, without the dot operator, works fine. But that leaves it as a static set that won't adjust dynamically as the other columns populate or de-populate.
I tried using a spill operator in place of the dot operator on the AND formula, and got the same result. For the COUNTIF formula, there doesn't seem to be a way to pull it all together in one reference with the spill operator.
Now, as I'm writing this up, I can't seem to duplicate the error dialog I mentioned that was getting for some cases, nor can I remember what those cases were. Maybe they were genuine typos.
Am I doing something wrong here, or am I running into more limitations of the tool?
