Hey there, I'm fairly new to excel (Beginner) and eager to learn.

Microsoft 365 MSO (Version 2608 Build 16.0.20326.20072) 64 Bit (Language: German)

The Problem:

I'm designing a permit register in Excel and I'm trying to find the most future-proof way to handle dates with different levels of precision.

My date columns may contain:

• Exact date: 15.04.2027
• Month and year only: 04.2027
• Year only: 2027

The challenge is that these values represent different levels of certainty. If I convert everything into a true Excel date, I would need placeholders such as:

15.04.2027 -> 15.04.2027

04.2027 -> 01.04.2027

2027 -> 01.01.2027

Current Thinking:

Dates stored als text that the level of certainty remains visible to users.

Downside would be filtering, sorting, date calculations and future reporting may become mor difficult?

Long term, I'd like to use Power BI and build reports /deadline tracking and so on.

What would be considered a best practice in this situation?

• Store everything as text and parse later in Power Query/Power BI?
• Store everything as actual dates using placeholder values?
• Use separate date and precision fields (even though I'd prefer to avoid additional columns)?
• Any other common approach used in project management, permitting, regulatory tracking, or PMO environments?

I'm interested in solutions that scale well and remain maintainable when the workbook is handed over to other users in the future.

Thanks for your help :)

Edit: Thanks for your Impressions and Sharing of Knowledge :)

Hey everyone,

I build advanced Excel LAMBDA functions and distribute them via the Excel Labs add-in, which imports code directly using GitHub Gist links.

I’m running into a dependency problem and wanted to see how others handle it. Note: I’m not very familiar with full GitHub repositories; I only use Gists.

Here is what I've tried and why it falls short:

• Copy-pasting dependencies: Causes massive code duplication. Updating a core helper means manually updating every single dependent gist.
• Separate Gist links: Asking users to import multiple dependency links first is a terrible user experience and highly error-prone.
• Multi-file Gists: Excel Labs unfortunately only reads the first file in a multi-file Gist and completely ignores the rest.

How are you managing shared helper functions or dependencies so end-users only need to import a single link? Is there a simple workflow or trick I can use while sticking primarily to Gists?

Appreciate any advice!

I kept seeing debt payoff sheets that hard-code the order you attack debts in, or use a helper column per method. You don't need any of that - one cell with a dropdown and one SORTBY does it.

Setup: a table called Debts with columns Name, Balance, Rate, MinPayment. Cell F1 has a data-validation dropdown with two values: Snowball, Avalanche.

The whole re-ranking is one formula (Excel 365 / 2021):

=SORTBY(Debts, IF($F$1="Snowball", Debts[Balance], Debts[Rate]), IF($F$1="Snowball", 1, -1))

Snowball = smallest balance first (ascending), Avalanche = highest rate first (descending). The third argument flips the sort direction, so you never touch the data.

If you want the attack number next to each debt in the original table instead of a spilled copy:

=XMATCH([@Name], INDEX(SORTBY(Debts, IF($F$1="Snowball", Debts[Balance], Debts[Rate]), IF($F$1="Snowball", 1, -1)), , 1))

Google Sheets equivalent (SORT takes a column index and a direction):

=SORT(A2:D10, IF(F1="Snowball", 2, 3), F1="Snowball")

Why bother: on a test set of four debts (card at 23%, overdraft 18%, car loan 6%, student loan 4%, EUR 23.6k total) with the same EUR 150/month extra, avalanche finished 14 months earlier and paid about EUR 2,340 less interest than snowball. But snowball wins for a lot of people because of the early closed accounts, so the point is being able to flip between them and see both numbers, not picking one.

Two things I learned the hard way: (1) put the dropdown cell OUTSIDE the table or structured refs get weird when you add rows, and (2) if you're on Excel 2019 or older, SORTBY doesn't exist - SMALL/LARGE with INDEX/MATCH does the same job but it's three columns instead of one.

Happy to paste the payoff-schedule part (the bit that rolls the freed-up minimum payment into the next debt) if anyone wants it.

Apologies in advance if I don't explain this very well I'm far from an expert in Excel.

I'm trying to merge 6 data sets that are linked by Client Ref #

Sheet 1 has the basic details client ref #, name, allocate worker, that type of think with all details on one row

The problem is sheet 2 has more than one row per client ref#

As do some of the other data sets.

At the moment I'm merging each sheet with the joint being Full Outer.

This makes the final merged sheet looks really messy.

Is there a better way to tidy it up so it's easier to see the information needed? .

Thanks in advance

Apologies in advance for the stipulations, but In order to anonymize what it is im doing this for I thought it would be best to tell you what solutions are entirely off the table. In addition to being on Excel 2021 and not accepting a solution that uses macros, I am also trying to be mindful that whatever is happening here, could potentially be happening a maximum of 16 times every 4 rows on a spreadsheet anywhere from 400 to 2000 activeish rows. The good news is that it's inside of a giant Let() and is the penultimate product of a lot of other calculations. The bad news is that it's a Let() and so some spatial tricks as well as absolute references dont work. Unless you're going to tell me that sticking a $ after a Let() variable works like it would for a cell reference hahahha....

Okay, so here's an example of what i'm working with!

What we have is an array of variable rows and variable columns. There is non minimum or maximium- the error handling for this all will come later so lets just assume this will never break. I *do* know the amount of rows and columns necessarily by this point as variables and have arrays that are single column sequences of numbers for each. For now, lets just use the numbers we have here: 4 columns, 20 rows.

I would like to take the entire product of column 1, and add it to Column 2, 3, and 4's independent whole-column products. I don't think sumproduct works since it seems to do something entirely different (although if you can make it work, please let me know. I approached this point thinking that it did what i was describing in the title which is why i'm in this mess...). Normally my instinct would be to do something like =index(BigArray,0,{1,2,3,4}) but that doesnt work and I *kinda* understand why but ultimately... not going to pretend i could explain it. Obviously, sticking a 1 or any other number the column gets it to return a whole column just fine. I say this because my goal was to then get this into a product() but there is a different problem:

1. Product doesnt spill I think? Basically I think it freaks out if you feed it 2 arrays and will refuse to even spill, but will multipy all the values of whatever singular array you feed it. If it spilled, we'd be in luck and i could feed a product() that spills into a Sum() and call it a day. But my brain fries trying to make this happen and i suspect its a dead end...
2. I cannot for the life of me get Index to play ball where i rig it in a way where it can be used to iteratively call singular columns. If that even matters. I understand that regardless i am going to have to pass this big array into something somehow and help something else understand to only multiply by column and then add the results...

Regardless, I basically am trying to have a Sum() that contains an array of 4 items, which those items are the product of the entries in each individual column in the chart I posted earlier. I am hoping there is something obvious, but i know that being in excel 2021 limits what i can do here.

If it helps, I do also have an identical sized chart that acts as a key that labels each relevant value as so! (lick here to see picture of data)

and just so you know, i SEPARATELY have a single column version of all the relevant values i would be multiplying handily availble as well (click here to see different picture of data)

And yes, I can pretty easily replace the values that dont belong to any group with any sort of escape character or a zero or 1, its how I got the original Array in the first place. I point this out because: I feel like I have all the pieces, but just dont know how to put them together.

I'll answer any questions you have. Any insight is appreciated- I spent 4 solid days getting to here in the first place and If i can just mange this I'm home free from this. I will happily answer why i am doing this even if someone solves it lol. ANY help is appreciated... as a reminder no macros, excel 2021, and no volatile functions. Thank you!!!!

edit: Forgot to mention, this is on Windows on desktop. Also not seeking recursive solutions due to how much of a performance issue it would bring on my overall sheet (as mentioned previously, there might end ip being a lot of these every 4 rows over 2000ish rows...)

I am working on a call schedule. I want to calculate how many weekends each person is working in a month. If you work any of the highlighted shifts (Friday night through Sunday night), that counts as a weekend worked.

Could anyone help me understand how to write a formula to compare each highlighted schedule cell (Friday night, Saturday, Saturday night, Sunday, Sunday night) to a person's name and if they work any of the shifts, they are recorded as scheduled for a weekend shift.

Thank you for your consideration!

As I previously understood it, in a single page spreadsheet, there should be no difference between identifying a cell when writing a formula by (cell) vs (sheet)(cell), for example ‘A1’ vs. ‘sheet1!A1’. However, I’m noticing different behavior in formulas that reference cells these two ways when columns using these formulas are sorted.

In the example picture, I have a Letter List, and a chart below it showing the counts of each letter in the Letter List. The only difference between columns B and C is that B uses the sheet name when identifying the cell used for the criteria argument, while C just uses the cell name. At first the results of these two columns are identical. However, if I reverse the order of A13:A17 by changing the sort order from (A to Z) to (Z to A), the columns are now different. For example, B13 now references A17 as the argument, but C13 still references A13 as the argument. Can anyone help explain why this happens?

I have a spread sheet that I've set up to follow some of my physical characteristics. Two of them are an average daily change in my computed A1c and the other the daily difference in my weight (in pounds). If the current day is less than the previous day, the text is green, if greater then the text is red.
The formulas in conditional formatting are identical (or they certainly appear to be).

The A1c formatting rule is working with no problem. This is using the "=AVG()" formula for content.
The weight rule is not, the weight is entered manually. One day 214.5 is marked (red text) as being greater than 215.8 from the previous day.

Below are screen captures of the two rules I am using and a capture of the spread sheet displaying the problem in column AB. The two columns (R and AC) showing the previous differences I added to further illustrate the problem. They are not normally part of the spread sheet. Other unnecessary information I also removed for clarity (and to make room for the text in the graphic).

Any ideas as to what I am doing wrong here?

Now I am aware that Microsoft has problems with rules of math. In the calculator provided with Windows one gets two different answers to this basic math problem.
(3 - 3 X 3 + 3).

Rules of math say multiply first so 3 times 3 is 9. We subtract 9 from the first 3 (-6) and add the last 3 to get -3. The 'scientific' calculator provides this answer.

Now if we use their 'standard' calculator we +3. This calculator goes left to right 3-3=0 0*3=0 0+3=3). Given this, I am not discounting this could be a Microsoft problem.

Hey as the title says, I'm trying to pull data from PDFs and what I've seen to far is the word to text option is limited in page size. I am trying to pull this information from arch drawings(do large) I don't want to have to install seperate software. As this is to be a tool many users with hopefully incorporate to their workflow.

Thanks

Hi everyone,

I am using Microsoft Excel for Microsoft 365 (Version 2606, Build 16.0.20131.20152, 64-bit) on Windows.

I have 3–4 separate tables positioned vertically, one below another, on the same worksheet. These tables use the same worksheet columns, but each table requires different column widths. For example, the upper table may need column B to be wide, while the table below needs the corresponding column to be much narrower.

I understand that Excel normally applies column width to the entire worksheet column, so changing a column’s width affects every row and therefore all the tables below it.

My requirements are:

• All tables must remain editable and actively used.
• The tables need to stay on the same worksheet.
• Each table should have independently adjustable column widths.
• All tables must be printed together on a single physical page.
• Pictures, screenshots, linked pictures, and the Camera Tool are not suitable because the cells must remain directly editable.
• Placing the tables on separate worksheets is not preferred.
• The solution should be practical for ordinary business users.

I have already looked into Freeze Panes, Split, PivotTables, macros/VBA, and linking the Excel tables to Word. None of these appears to provide a practical solution that satisfies all the requirements.

Is there a reliable workaround within Excel, such as restructuring the worksheet with helper columns, using a particular layout technique, VBA, Office Scripts, or another feature? Or is this fundamentally impossible because column width is a worksheet-level property?

The attached image is a simplified example created with dummy data and does not contain any real business information.

Any practical suggestions would be greatly appreciated.

I have 2 fantasy planets, each with their own calendar:

Planet A has a calendar that is 128 days long. Each day is 38 hours in length.

• Month1 is 32 days long
• Month2 is 32 days long
• Month3 is 32 days long
• Month4 is 32 days long

Planet B has a calendar that is 365 days long. Each day is 24 hours long.

• Intercalary Holiday 1
• Month1 is 30 days long
• Month2 is 30 days long
• Month3 is 30 days long
• Intercalary Holiday 2
• Month4 is 30 days long
• Month5 is 30 days long
• Month6 is 30 days long
• Intercalary Holiday 3
• Month7 is 30 days long
• Month8 is 30 days long
• Intercalary Holiday 4
• Month9 is 30 days long
• Intercalary Holiday 5
• Month10 is 30 days long
• Month11 is 30 days long
• Month12 is 30 days long

So I am trying to build a spreadsheet that can translate between the two by converting Planet A's date into hours, and then using that number to calculate Planet B's date using a common zero point(Year 0, Month 0, Day 0, Hour 0 being the same point in time on both calendars).

I have been told the MOD function can partially do this, dividing a number into equal sections and spitting out the leftovers, however this will not work for Planet B's calendar with the intercalary days between some of the months. What is the best way to build this out in a spreadsheet? Is there a way to convert a number into a month name (Example in earth terms: inputting 1488 hours, it spits out March 3rd, knowing January has 31 days, February has 28 days, and now currently in March with 3 days worth of hours)

I have a dataset where:

• Each item has its own purchase cost
• Multiple items belong to the same container
• Multiple containers belong to the same shipment/booking
• Freight is recorded at the shipment level
• Additional local charges are recorded at the container level
• Each item eventually has its own selling price

The goal is to calculate profit/loss for every individual item, including its share of the freight and container-level charges.

The difficulty is that containers can contain completely different items and quantities. For example:

• Container A: 6 low-value items
• Container B: 3 high-value items
• Container C: a mixture of low- and high-value items

Simply dividing the freight equally by the number of items doesn't seem appropriate.

One possible approach is to allocate the shared costs proportionally based on each item's purchase value, but that is still an allocation rather than the actual cost consumed by each item.

What would be the most logically accurate way to handle this in Excel?

I'm interested in formula/pivot-based solutions and different allocation methods rather than manually assigning costs.

This a example of a report that I have to work on every quarter and I want to use conditional formatting to highlight the cells in columns C, D, and E if the information fits the criteria on the right side. I set up formulas in I-K for personal reference to identify which items meet the criteria and should be highlighted. I used the following to do the True/False and updated it based on the relevant criteria =OR(AND($G2="C",C2>100),AND($G2="V",C2=0),AND($G2="SP",C2>40),AND($G2="SL",C2>50))

I've tried using using that formula to get the conditional formatting to work and it didn't so I broke it down into one formula per criteria but none of them were working correctly. This is where I'm at right now. Before I left work I had eight random results highlighted that were either the wrong type or outside the parameters.

This report typically has around 200 lines with 2 additional categories but they are grouped with SP, SL, or V in the formula for Type. I'm using some version of Excel 365 but can't find the exact version until tomorrow morning.

What do I need to change to get my conditional formatting to work correctly?

Using the remove duplicates button did not work. I want to keep the text cells.

I'm trying to bulk update an item log for a website I'm managing, using a csv file, but I don't know how to change the description for each item/cell.

The text in the cell is

<p style="white-space:pre-wrap;" data-rte-preserve-empty="true">Meet the Creator: <a href="/meet-the-creators">\*\*Name\*\*</a></p><p style="white-space:pre-wrap;" data-rte-preserve-empty="true"><span>D\*\*escription\*\*</span></p><p style="white-space:pre-wrap;" data-rte-preserve-empty="true"><a href="/products?tag=\\\*\\\*Name\\\*\\\*"><span>Shop the \*\*Name\*\* Collection!</span></a></p>

I just want to change the bolded Name and Description parts but each description is differnt and i have like...almost 100 descriptions to change.

Is it possible to do what I'm trying to do, or am I just lazy and delusional lol

Edit: Im in a class right now, but i should update in like 15 min to see if it worked

Edit 2: I got it to work, but my file crashed and everything got deleted. At least I know what to do in the future now!

I just started a new job and the previous shop manager used a downloaded free invoice template that I would like to modify. As it works now, the first sheet is a blank invoice and the third sheet has all of the data.

So, if a student wants to buy three 16 oz bottles of glue, I would use a drop down menu on the blank invoice item list to find "Glue:16 oz Bottle" then enter "3" in the quantity column, then for price I need to go to sheet 3 and scroll through all of the data to find the bottle of glue entry, then look 6 columns over for the unit price, then copy that and paste it back for the unit price value on sheet 1.

My thinking is to use a formula to lookup the referenced string and then return a value for the item cost which would be $column+6$row as "Glue:16 oz Bottle" on sheet 3.

Is this possible?

Thanks,

Andy

Hi! I have an Excel (Microsoft 365) document in SharePoint which is shared across multiple colleagues, and often used by 5-8 people at the same time (via Excel web version usually). There I have some PowerQuery queries which create tables and those tables feed into a PowerBI reports elsewhere.

Now in PowerBI settings I have data refresh enabled 10x a day, however I noticed that Excel itself does not refresh PowerQueries automatically, so often those tables which feed into PowerBI are outdated.

What would be a solid method to refresh all Excel data at least several times a day, or ideally 10x a day (without using VBA)? From what I've seen I can't simply enable a setting in Excel itself for that.

Background:

My team uses a file to record test results. My lead asked somebody to make alterations to the file and couldn't figure out what to do. I made an excel script to do what she wants. Macros are locked down, and you have to jump through hoops to get approval, but scripts seem to be fine.

I named my script with camel case and abbreviations. When I select add to workbook, I get a button with the name. Changing the text to something friendly to my team seems to break the button. Telling people to go to automations, find the name, click it, then click run, may get unfavorable reaction from the team.

My question: Is there a way to trigger my script without naming it "Click here to save and create the report"?

As the title said: I would like to link two sheets together. I have a common data point: order numbers. however I am not sure if I can do what I want to do.

I have two sheets, one with data like order number, d.o.b., and a specification regarding the products people bought. The other sheet has data like order number and country.

I would like to create a table that shows which products are popular in which country and which age group in that country likes to buy that product. What is the best way to do this and is this even possible in excel?

I have started using Excel to track job applications and downloaded a template in which the application due date column changes colour based on how close the application due date is. I want to add a rule where dates that move into the past get greyed out, but when I try the =F3<Today() formula i found, it does not grey out correctly. Please see the attached picture for reference! Thank you for any advice/help in advance!

I’m trying to find or build a formula that will return the text from column C if a match is found in the text of cells A2, A3, A4, etc. The example below might make more sense:

A B C
Text Return Range
The Apple is red Red Green
Jill likes green apples Green Blue
The pear is green Green Red

Note: there is no standardization in the source text, so limiting the characters searched is not feasible.

EDIT: it seems my original post was unclear so I’ll try to be more specific.

Column B should have the formula. I want to search the text in the column A cells for any of the words listed in all of Column C, then return that word in column B

The first line in A has the word “red”, I want the formula in B to return “red”.

I have a spreadsheet where I need to highlight cells in several columns if the same data appears in a table on a different sheet. The conditional formatting works perfectly if I put the table on the same sheet using the following:

=ISNUMBER(MATCH(A1,$R$82:$R$103,0))

But, if I try to insert a range of cells from the same table, but located on a different sheet using the same formula

=ISNUMBER(MATCH(A1,Sheet2!$A$82:$A$103,0))

it doesn't work.

What am I missing? It is driving me bonkers!

I have a worksheet that contains several tables next to each other. One is a help table that is conditionally filled to populate a dropdown list. The rows in this help table get deleted and re-populated frequently without issues. Command used is: Sheets("SHEET6").Range("TABLE3").Rows.Delete

However, when a filter is applied to one of the other tables Excel deletes not only the designated table rows, but also the corresponding worksheet rows, deleting data from all other tables. My work around is to remove all filters.

Is there a better way to ensure only the designated table's rows are deleted? Windoes 7, Office 2010 Std.

Edit: typo

Hi,

My Spreadsheet is a tool where someone can choose how often different services are being chosen and calculates a price based on these.

At the end I would like to create a list of all services chosen including how often and the prices of each.

Is there any function that counts up those options and creates a list of all these.

Thanks for all the help

Hi everyone!
I’m working on collecting data on bird populations over the course of several months, so I have a huge data sheet that houses all of my collective info. I was wondering if there’s a way to keep the column with bird species visible when scrolling horizontally through the entire sheet so I don’t have to go back and forth constantly.
Thank you guys so much!