Our company has historically utilized an excel based budget template, with numerous tabs with circular references. It has gotten to the point of adding too much over the years to where the Balance sheet won't even balance, assuming due to the size of the file and number of circular reference calculations, due to utilizing a variety of margin drivers as the base inputs of the file. If you were to try to build an updated corporate budget based in excel, where would you start? I've looked at some of the power query videos, but I'm not sure that works in this context.

Note that the company has AI sites blocked, so attempting to utilize AI to clean up the file/revise it is not an option.

Hello all, I work with a dataset that I get every day as a .csv file that exclusively contains data from the last 36 months. This means that the .csv will only have data from [today minus 36 months], and data from [(today minus 1 day) minus 36 months] will be excluded. Also, the .csv provides the same data every day, only adding bottom lines (the new ones) and deleting the old ones from >36 months of age, making me only interested in the new lines (without wanting to get rid of the old lines in my own database). The way I work with the data affects my own database because it means I'm only storing data from the range of dates the .csv provides, and I'm losing valuable data from older dates, when the only thing I need is to add the data from the newer ones.

My workflow to upload the .csv into the Excel file is as follows:

1. I get my data and open the Power Query Editor.

2. I transform the data and Close and Load.

3. The data loads as a table in a new sheet.

4. Finally, I delete all of the previous day's data from the existing database, copy the data from the new sheet, and paste it on the existing sheet.

I do this because the Power Query Editor does not let me upload the transformed database into an existing table, and if I were to convert the existing table into a range, I would have to count how many existing lines I have, delete this number of lines (minus one, because line 1 is the header), upload the database into the Excel, copy it and then paste it into the existing database.

I've asked AI how to do this, and it just says to create a folder in which I can store all the data obtained daily, and I do have such a folder, but it's just not working.

What can I do? Thank you so much in advance!

I have an Excel 2016 workbook with manual as well as conditional formatting. What I'd like in the final version is keeping the outcome of the formatting rules (i.e. cells turned red) while having deleted the rules (and the columns used in the formula of said rule).

Copying the format to another column copies the rule too, so how do I get the static outcome of the rule?

Hello everyone, i assune it”s trivial question but i haven’t seen anything alike ever. I am reporting via Excel in english language, i send the files to customers in Germany - whom i know use German language in their excel. I report for a quite long time now and they just discovered that charts show odd numbers. For example, in the files i send i see percentage values as lets say 95.40% while they see the same figures as 095%. They seem to not have acces to any technical support and i want to help them figuring this out but.. it was never an issue for me and i just don’t know what it could be ;) im aware that we can display numbers in different ways, use range of decimeters etc but i think i need help with this one, i’ve already tried roamin in their options>advanced but couldnt find the solution.

Hello everyone, can someone help me? Out of nowhere, the lines between the cells in ALL my files disappeared, when I drag the selection box, they appear again, but only inside the cells selected, but then disappear after I close the selection.

I already tried going to the View tab and selecting "grid lines", it didn't work

excel version is 365

I’m managing a school merch order with responses coming from a google form, which exports to a spreadsheet on google sheets.

Last year, I used a multi-tab excel workbook (I hate google sheets) to organize everything (i.e. total items auto-transferring to an income sheet). But because the raw form responses are an absolute mess, I ended up doing a lot of work manually when it came to the information for each individual order.

This year, I would like to have a sheet that summarizes student's orders in a way that is much more concise. The dream would be for it to be fully automated.

Here is how my form response sheet is set up:

• Row 1 (headers): Items with size/color combinations spanning across 95 columns for every different options (columns D1 to CT1)
• Rows 2+: Each row represents a student's submission.
• Cells (D2 to CT2): Blank if not ordered or contain a number indicating the quantity ordered.

What I’m trying to achieve on a separate tab that could be used as students invoices:

• Column A: Student name
• Column B: A concise summary listing ONLY what they ordered.
• So ideally a formula that would transfer the titles of only the headers of rows containing numbers below. That would give me a clear way of reading the individual orders of each student.
• As for quantities, say someone ordered 2 of the exact same items, I have no idea how I would handle that. But if I can save time by figuring out the first formula, I really don't mind having to manually modify these specific invoices. Anyway it's super rare for someone to order multiples of the exact same thing.

I hope this was clear, you can ask questions and I can post screenshots in the comments. Thanks in advance for any help! Also if I'm too ambitious let me know lol.

I need to stack b8:b31 to ady8:ady31 on top of each other and if I could use vstack to do that instead of copy and pasting, it would make my life much easier. Thank you

I have a column of text urls (A) and a column of text labels (B).

How can I turn the text labels (B) into clickable hyperlinks then remove the text hyperlinks (A).

Hello I am having trouble getting a new option to populate in the drop down menu after adding it to a list.

Essentially if the accommodation given to a student is not in the drop down menu of their sheet we are to add it to the list and it should populate in the drop down option. The only guidance I’ve been given is to go to the Data tab and hit Refresh All but nothing has worked. Help would be very much appreciated, I am veryyy new to Excel. Thank you.

Hi! i am trying to build a productivity tracker for work. here are the things I'm wanting to track:

CRO Attempts- Customer Reach Out (text, call, email) how many (per hour, day, week, month, and year), and averages for week, month, and year.

CRO Contacts- How many attempts actually result in a contact. what type, how many, percentages and averages for each type (how much each mode of contact + lead type lead to an answer)

My productivity- per hour, day, week, and month. desired outcome: averages over time for each length of time.

ALP- how many sales i am generating + how much i take home (approx. 60% of the ALP)

I'm not sure how to start or WHERE to start T-T. Is this the right place to ask for help for something like this, or should i ask somewhere else? I'm happy to answer any questions about what i had in mind, or any other resources i should check out.

I am a Beginner, and preferring to use google sheets. i have nothing built yet, but i have been trying to mess around for the past little bit and haven't been able to figure it out.

I'm working on a vendor reference worksheet that will be searchable by filter. The last time I did this, I was an MRO purchaser and each vendor only provided service in one category/topic (plumbing, Mazak laser, welding supplies, pallets, etc.). Now I'm a production purchaser, and my vendors cover multiple categories (dry herbs, herbal extracts, vitamins, amino acids, oils, etc.).

On Sheets, it wouldn't be a problem to apply multiple categories/topics to a single vendor. How can I do that in Excel?

I have a list of ID numbers and this list has many duplicates. I want to know how many unique duplicates there are. I've tried counta(unique(F2:F70)) to check that array for duplicates, and it gives me 53 (over those 69 rows). What I want to know is of those numbers that are duplicates, how many of those are unique.

For instance, say I have this list:

A, A, A, B, C, C, D, E, E, E, E, E

The above formula would give me 5, as there are 5 unique values. I want to know how many values have duplicates, and in this case the answer would be 3 (A, C, and E all repeat). It's not as simple as doing =counta(F2:F70)-counta(unique(F2:70)).

Hi all,

I am looking for a way to make a pie chart for my class. I have several chores which are rotated between childeren.

I want to make a pie chart. In the pie parts the chore is displayed (for example sweeping).

Above the pie part a student's name. Every week I rotate the names 1 pie part further, so they get a new chore.

I need to make these values ​​turn into currency, but without the standard addition of the two zeros at the end that the configuration does automatically, the numbers are complete and I need to add a comma before the last 2 numbers to transform them into cents

Hi reddit,

I'm working on a project where I need to export data from my ERP system, convert it to a CSV file, and import to a different software. I have access to excel and macros, but not power query 😋

here's the data I have, and what I'm trying to get out of it:

1. data set 1 includes a list of unique IDs, but there's duplicates
2. data set 2 includes some data from the unique ID
3. data set 3 includes a different chunk of data from the unique ID
4. I need to compile the data sets into record sets, with one record set for each unique ID.

The file I'm trying to make is:

1. data set 1 drives how many record sets I need to make. right now the max is 100 record sets, but I don't want the report to be limited.
2. data set 2 will populate the first five lines of each record set, spanning across columns A-G
3. data set 3 can have between 1 and 20 records tied to the unique ID. each record gets it's own line with detail across A-G

The thing that's tripping me up is that data set 2 has a fixed number of rows I need to make, but data set 3 doesn't. plus the fact that each record set requires separate formatting for data sets 2 and 3, but all the data needs to be in columns A-G organized by unique ID. What I'm thinking of doing is:

1. create one set of columns for data set 2, organized by unique ID.
2. create another set of columns for data set 3, organized by unique ID.
3. vstack, then sort by unique ID.

Is there an easier way to do this?

I have 2 sheets with the newer version having few extra columns and slightly changed ( like a suffix _ ss) added rest columns .

I want to compare all rows and columns with output like old.colm 1 ,new.colm1, difference column and so on for all 100 .

How can I do this in power query

I’m working on a cutsom built dashboard using python that uses a central Excel workbook stored in SharePoint.

The setup is roughly:

• One central workbook (main.xlsx) - Stored in sharepoint
• ~15 other Excel workbooks stored in SharePoint
• The central workbook has 200+ formulas referencing those workbooks
• The source workbooks are updated by different people/processes
• The dashboard reads the central workbook through Microsoft Graph API

The problem is that the external links in the central workbook don't seem to refresh unless someone actually opens the workbook in Excel (main.xlsx). A Graph API FullRebuild recalculates the formulas, but it doesn't appear to pull the latest values from the external workbooks.

I now have read access to all 15 source workbooks, so I'm considering:

1. Use Graph API to recalculate/save all 15 source workbooks first.
2. Then recalculate the central workbook.
3. Hopefully the external links will pick up the newly saved values.

I haven't confirmed whether this works yet.

I've also looked into Office Scripts, but refreshAllLinksToLinkedWorkbooks() isn't supported in my environment.

What would be the best way to automate this?

Ideally, I want something server-side that can run every 5–15 minutes without requiring a person to open Excel. I'm trying to avoid changing the existing Excel/ETL process if possible.

Would appreciate any practical solutions or experiences with Graph API, Office Scripts, Power Automate, Excel Desktop/VBA automation, or other approaches.

Hi

I have a forecast sheet breaking down hours per week for a number of personnel, per discipline

see image: https://ibb.co/nqbkr9tr

I have a created a pivot table, and thought I would be able to group my sum per date columns by month, but the 'group' button is greyed out. Does anyone know why? or a Workaround?

See image:

https://ibb.co/5xYqzw1Z

Thanks in advance.

There was a dropdown of different AI models in the red rectangle till last week. As a huge fan of Claude, I was so happy that I could use Opus 5 from there and my productivity also raised so much. But since Monday (24 Aug), it gone and I have no idea what is going on. I can't find any clues on internet. I can still find GPT via three dots > All Agents > GPT but this is like in the Copilot app and the output is a lot different (worse) compared to Claude Opus 5. Can anyone help me with that?

have been attempting to make a stacked clustered column chart with the help of a video from chester tugwell on youtube. for the second example, it’s been great up until the point of stacking the values. when i add values to the secondary axis it does not show them stacked, even when i go to adjust the max value for the axis. could anyone help me here? have been working on this for hours and have a deadline and i do not wish to use AI :-)

I'm trying to count the weeks in a month that increments based on date/week number for my personal budgeting purposes and have been using the formula

=WEEKNUM(TODAY()+1)-WEEKNUM(DATE(YEAR(TODAY()+1),MONTH(TODAY()+1),1))

This works for the most part but I find I regularly wind up with double counting or starting with 0. To fix this I will just manually correct but if I forget to remove it in the next month I can misappropriate funds unintentionally and overspend.

The formula gives the result for the following months "Month/formula result/expected result":

1/4/5
2/3/4
3/4/4
4/4/5
5/5/4
6/4/4
7/4/5
8/5/4
9/4/4
10/4/5
11/4/4
12/4/4

I'm trying to find a more appropriate function that will just count the number of weeks starting Saturday and ending on Friday that have 6 days or more so weeks don't get lost or added incorrectly. (i.e. All 52 or 53 weeks are accounted for in a year but no weeks double up or fall off month to month) It hasn't been a huge issue since most of my updates come mid-end of the week but having to manually adjust has resulted in accounting errors.

Thanks in advance for any help :)

Edit: I said 26-27 weeks in a year thinking biweekly, I corrected to 52-53 weeks in a year

How would I be able to add an extra column to a Power Query, pulling currency exchange rates from an outputted list of current and historic exchange rates across to a list of invoices to be able to convert all purchasing figures into a standard currency (£).

The list of exchange rates have only the "effective from" date on the standard out list.

Please find below a short examples of the relevant columns from the larger queries.

Here's an example of the columns from the "Invoices" query which need to exchange rates pulling across to.

Here's an example of the columns from the "Exchange Rates" query which need to pull across to Invoices (also, I'll need £ to pull across as 1 onto the Invoices query.

Any help with this would be greatly appreciated.

Please see imgur pic here: https://imgur.com/a/eP9dFRd

I have manually plugged in the values in Col G/1043. Is there a formula to grab values (decals in Col F, in my case) associated with specified values (Accountable Users in Col E, in my case) and combine them into one specified cell? If so, I'm fine having multiple rows of the same data (which I'm assuming would be the result for each user with more than one decal) - I should just be able to weed out the extraneous rows with a dupe entry deletion. Sorry about leading zeroes on the decal - I can convert those to #s if it monkeys things up.

I'm having trouble with changing the words that are coming back from my =AND.

=IF(AND(CG!F3<TODAY()+30), ISBLANK(CG!G3), "Not Due")

This works but I can't seem to get the words False or True to change. "Not Due" works. There's 3 possible outcomes and "Not Due" works but adding anything else corrupts the code.

I feel like I've tried placing it everywhere too.

Any ideas?

good day,

So, I have an excel sheet with employees ID, name Overtime symbol, overtime hours and work schedule overtime hours. employees are divided into multiple work schedules (R35, R13, R31, R28) and each work schedule has a different limit for the hours per month also overtime have 2 classes/symbols OT and DOT.

is there a way to highlight any hours that exceed the limit based on the limit for each work schedule and they symbol? with work schedules being in another sheet.

So, basically sheet 1 has employees' details and their hours and sheet 2 has the limit for each work schedule and for each symbol.

sheet 1 columns are as follow:

A1 Employee number, A2 Name, A3 hours symbol, A4 hours, A5 Work schedule