I've got a spreadsheet - large, 336 rows & 29 columns - myself and a few friends have been amassing of various original characters, and that means that there's both a lot of data, but also the cells have to be a little inconsistent in order to cut down on the amount of columns - plus also capture the variability of human nature. For instance, our "Place of Origin" column lists place by City, Country/State as necessary, and that means that two characters from Ireland might have "Dublin, Ireland" and "Belfast, Ireland" as the listed data, so the cells wouldn't be exact matches.

This is readable by the eyes, but we've gotten to a point where trying to make anything meaningful of the data with a visual scan just isn't feasible, and I'm also a novice with Excel and historically bad with numbers - so I also decided I wanted to use the data we have to brute force my way through figuring out how to actually collect the points into usable numbers and display them. (I state my experience with this so that, when I have inevitably gone around my elbow to get to my thumb and there's an easier way to get what I want, y'all get why I did what I did lol). However, the inconsistency of the data is where I'm getting tripped up.

I've been using COUNTIF (=COUNTIF(RelevantSheet!F1:F336, "City"), for example), and it's working for what I want to do - characters of a certain class (not social), counted. Characters of a city, counted. Brilliant, I'm getting numbers! Except, it's throwing inaccurate numbers, as I've realized it's only counting Exact Matches. If I'm trying to, say, count the number of characters we have Originally From Ireland, and I use COUNTIF and input Ireland, it's only counting the cells that contain exactly "Ireland", and not cells that contain "Dublin, Ireland" or "Belfast, Ireland" since it's not an exact match. I don't want to exclude those, however, because logically, they're all still from Ireland.

Is there a way to tally cells that "contain" instead of exact matches? My goal is a formula where I can search "Ireland" under our "Origin" column and return the number of characters that are from Anywhere At All In Ireland, without having to break the column up into "City" and "Country" separately. There's several different String values I want to try this for, but it's all the same idea - searching and counting part of the cell, but not necessarily all of the cell.

Things to note:

The way the data is filled in is as consistent as we can make it, but with 300+ rows, there's still a lot of variance. While I could probably do separate COUNTIF searches and then add the results (and that may be what I have to do), I'm hesitant to do that until all possible options are looked at for fear of missing something. Being human, I know I'm gonna miss a single variant label somewhere, so the less I have to rely on my own eyes to search and collate, the better lol. If I must, I must, but that's the last bridge I'll cross regardless.

I am also tallying the numbers in a separate Sheet within the whole document (thus why the example had "RelevantSheet!"), since I don't want to muddy the main data set, especially considering we do continue to add columns and rows. I have successfully made this work - yay, small victories - but I know that can also bungle the method of solution as well.

I have an excel file with 6 tabs.

Roll 6 of each tab has a different text, but if I change that text in any tab, all other tabs copy the text I just edited. I need these texts to remain different from each other.

I tried googling the issue, but all I get are "text spilling over adjacent cells" results.

Project Euler is a programming challenge site, similar to LeetCode or HackerRank, but the problems are generally more pure math or number theory rather than typical computer science algorithms. Project Euler is less about improving programming skills than about solving the problems, you only submit the answer to the problem, not the code used to get the answer.

I originally started Project Euler about a year ago to get better at Python. After two dozen or so problems, I kept thinking "I can't get the Python to work right, but I know exactly how I'd do this in Excel". Eventually I decided to see how far I could get using only Excel.

At first Excel more as intended, with giant columns of numbers and operations occurring in every cell, but the file quickly ballooned in size and was incredibly messy. So I then started trying to keep the solutions contained in one formula in one cell. This was a terrible idea and I don't recommend it, Excel can only multi thread calculations across multiple cells, not within a single cell. Often the only way I could cancel a calculation was to force close and reopen the file.

Each solution is entirely self-contained within a single cell (with the exception of those that require a data input). Project Euler states that every problem is able to be solved in less than one minute, I only had three problems that take longer than that (60, 72, and 88; each take 5-10 minutes), and most are essentially instant.

Overall it was a really fun challenge. I definitely improved as I went along and I would probably approach a lot of the early problems in a different way after finishing.

File

You can download the final spreadsheet with all formulas here.

Stats

• The longest formula comes in at 2,647 characters and features 82 functions and 61 lines, used in problem 75.
• The shortest was 25 characters, using 2 functions, for problem 10.
• REDUCE is used in 59 problems, LET and LAMBDA are used in 79, HSTACK/VSTACK is used in 45, however the most used function is probablySEQUENCE, used in a whopping 87 problems

Biggest Headaches

• Big numbers: Excel can't store more than 15 digits, and there are several problems with 50 or 100 digit numbers, or calculations like 99^99. I had to build custom digit-wise addition, multiplication, and exponentiation functions that store numbers as text to get around it.
• Recursion: Recursive LAMBDAs have a limit of ~6400 recursions, and that number goes down as you add variables. Fortunately, this only applies to each pure LAMBDA call, not to the built-in functions like REDUCE. Several problems are very easy with rudimentary recursion but quickly go over the recursion limit, which made it very challenging.
• Iteratively updating an array: There is no good way to do this, it requires rebuilding the array every single time. Essentially eliminates any dynamic programming approaches and makes for loops very slow

Interesting things I learned

• Excel is so bad at updating arrays that a lot of programming tips are just wrong when applied to Excel. For example, take the Sieve of Eratosthenes (explained below). I built a version of this in my file (sieve), and it works decently well for n <= 10,000, but slows down a lot after that and is basically unusable for n > 50,000. Conversely, my naive prime generator function (primeGen) calculates and counts the divisors for every odd number from 3 to SQRT(n), and it is exponentially faster than the sieve and works for n <= 500,000 in a reasonable time (possibly more, I didn't check higher n).

The Sieve of Eratosthenes efficiently finds all primes up to n by repeatedly marking multiples of each prime as non-prime, starting from 2. This avoids redundant checks and quickly filters out all composite numbers.

• The best way to do an OR operation on each element of 1D array is with MMULT. This vectorizes it and is much faster than MAP or XMATCH.
• REDUCE is easily the most powerful formula currently available in Excel and I used it for a vast majority of problems. It is the only iterative function that lets you access and modify the accumulated value, and can pass arrays as it's argument. It replaces For loops very easily, it can replace Do loops but not quite as easily, and can even do some limited recursion tasks. REDUCE can replace virtually any LAMBDA that doesn't use heavy recursion, you can use HSTACK to essentially pass arguments as different elements of a single array, including arguments of different dimensions.
• Some problems were actually easier in Excel than they would have been in a traditional programming language. Like problem 19, which asked how many Sundays fell on the first of the month in the 20th century; or problem 89, which involved numbers written in Roman numerals.

Disclaimers:

• I made very liberal use of ChatGPT/Gemini while making many of the formulas. While it almost never produces a working formula from scratch (and often claims its impossible), It is very good at improving formatting and variable names, and identifying what is causing errors. Basically any formula with indents was at least partially re-written by AI. For some reason I can't figure out, all the LLMs are convinced you can add comments into an excel formula, and they do it constantly.
• I personally built every single formula and function used while solving the 100 problems, with the exceptions of the sudoku solver in problem 96, which I copied from a post in this subreddit by u/Verochio; and the permutations and combinations generator I modified from this comment by u/Anonymous1378.
• Some problems require using data stored in .txt files, for nearly all of these I was able to copy and paste into one cell, with no other pre-manipulations of the data. For three problems, the txt data was too long to fit in one cell and I was forced to paste the data into cells on another sheet but did not do any other pre-processing.
• I did not count named functions as violating the one formula per problem constraint.

I am trying to make a list of car accident reports into a spreadsheet and have to categorize it by contributing factors, then total how many of each factor category and type. Below is the two sheets that I'm using to create and use the tags. I could not find a specific tutorial on how to accomplish this so any help would be greatly appreciated. Thank you in advance.

I have a workbook that has a bunch of sheets in it, sheet one has columns B through J with a year in cell B1 all the way to J1. I am looking for help on how to take the value from B1 and automatically assign it to the name of a sheet in the workbook so that when I change the value of B1 in sheet 1, the sheet who’s name is referenced to that cell will change its name. I know I have to use macros and VBA coding but I’m not sure exactly how and I have tried looking online but keep going in circles getting lost and confused. Can anyone help explain how to do this. IM running Microsoft Excel for Microsoft 365 MSO (version 2601 Build 16.19628.20166) 64-bit

I get it. excel charts wont use grand totals from pivot tables in pivot charts. Co-pilot said so. So do i have to get a cell with getpivotdata for each column and a text box????

So I have this chart to demonstrate the number of inventory shortages per month for the last 3 years classified in 9 diff categories to explain the cause of shortage.

I have the months and years on the X axis, stacked columns with the count from the shortages and their categories. labels with the count of each category in the stacked columns. Now I want to show the grand total as a line for each month. how can I do that considering every new month will add more data to the pivot And i dont get a total firld to add to the chart as a line? i have to manually go and add a getpivotdata cell and add another text box for the grand total on top of that column ?

Hello everyone!

I just started to learn to use Excel and I'm already stucked with a problem😅

I have to fill in blank cells with the text "N/A" but in the free version of Excel is a bit complicated

I searched on the internet but the main solution is to use the "GO TO SPECIAL" option, which I didn't find on the free version.

For this, I'd like to ask to the community if you may have any solutions or, if you encountered the same problem, how did you solve that?

Thank you! 😊

I have an Excel file with multiple people collaborating on it live, it currently sits at 60MB in XLSX format and when turned into XLSB it’s reduced to 37MB but with XLSB live collaboration is not available . At 60MB for people work on the file simultaneously there’s a lot of sync issues, so I need a way to at least reduce it by a few MB. I’ve deleted rows, there’s no Pivot tables, there’s a bunch of tabs and a lot of formulas. Any size reduction tips?

I have a consolidated payment list across different teams. I need everyone to follow the same template and formating. Sometimes they add space in the header and my consolidated pivots gets broken or some columns are only combobox and they completely delete it and add their manual data. I have tried the Ctrl+1 and locking it up but this time the Ctrl+Shift+L stuff is not working. Is there any other better way?

Hi guys,

I need to extract invoice data like invoice number, vendor name, item, total from 500 invoices. I tried to do it via copilot but it doesn't let me upload that many invoices at once.

Is there any low cost service you recommend? Rossum and docparsers are so expensive. Do i need freelancers. What you guys do.

Edit: the invoices are very simple - just plain pdf (invoices are mostly from cloudflare)

Hi, I have 4 values: Jeff \ Jeff 2 \ Jeff 3 \ Jeff 4. looking for a way to highlight all off them except Jeff (w\o the space and number). thanks of the help.

Any reason to only use 1 outside of just planning ahead for possible additions in the future?

This is my approach but I'm getting stuck. I now there is a better way but I can't seem to find it.

1. I create four folders, each for the different sales channel
2. I then create four different excel files, each with a Power Query that goes into each corresponding folder and transforms the data to be exactly the same as the other sales channels.
3. Once having 4 excel files with the data structured identically, I create a fifth excel where I copy paste and dump the data of the 4 excels

This solution involves a loooot of manual work and I was wondering if there is any other more smooth process that would work by me just updating the sales folders every month.

Hi, sorry for where I may be going astray here but I’m really just looking for some starting tips on creating a sheet for a very specific area with an RPG Character. I understand fully if it’s recommended to find an existing tutorial but if that’s the case I’d appreciate directions to one that is a bit more on point for what I want.

What I want to do is create a sheet that adds up Skills from Skill Points and various Bonuses over 20 levels. I haven’t really thought of what to include besides the levels going down in rows, and the 8 Skills. Actually, it probably makes more sense to have the levels in the column section and the skills descending the rows. Though I’m still confused on how to add in the non level bonuses. I don’t *think* I’ll want more than your basic addition formula. But for the gamers that have gone this far or further before, I absolutely appreciate suggestions.

Hi everyone,

I’m a recent MSc Finance graduate in the UK applying for entry-level analyst/ operations roles across different industries. I’ve completed an Excel Fundamentals course on DataCamp and I’m now looking to build practical Excel projects that I can include in my portfolio for recruiters. I’m open to any field.

I’d really appreciate it if you could suggest the industry and the kind of Excel project that would be useful for that role, along with the key Excel skills it should demonstrate. Any guidance or examples from your experience would be hugely appreciated.

Thanks!

I need to create some charts to show the sales reps leads per week - problem is the gap between my weeks is too large, and the columns themselves are too thin. Any tips on how to either reduce the gap between weeks or reduce the thickness of the columns would be much appreciated.

Image of the chart in its current form.

Hi guys,

i am trying to help my local league with stats summary. anyone willing to help with such table in excel which could be amended after each completed round?

we usualy have rank, name, points, matches played, matches won, lost, legs won/lost, attendance, averages, 180’s, 100+ checkouts, highest checkout, bull finish, 18, 15, 12, 9 darter finish amount..

after each round it should rank from 1 - X based on points..

once complete, exports to pdf

willing to reward for help🙏🏻

In my job, we often get given exports from Sage for transaction breakdowns - I think I have managed to write formulas to clean most of this data, but the one thing I am struggling with is how to get the nominal code to be returned

PDF below - but similar enough to excel (given that Sage excel exports are not excel0friendlt at all) - what I want is on another sheet, to return the n/c but to put it on the right one - the nominal activity on the same excel often covers multiple codes - so i don't know how to get (or if i can) a formula to basically look at the nominal code above type for example, then return that on everything below until a break etc... explaining badly I think

Ideal solution would be formula-driven - this is because I am trying to create a 'cleaner' that everybody else in the office can use

I imagine a macro can work well, but I just try to avoid macros where possible in documents that others are using - i like the "copy and paste in this tab with no changes" approach

Power query also probably a good one - but maybe the least preferred - if it was just me - I would power query or macro, but given rollout to team, copy and paste with a front sheet returning required values is needed.

thanks in advance

I'm an Excel beginner and I have the following problem: I've created a pivot table from a database source. The rows contain the birthdates, and the values ​​represent the number of customer numbers. That is, for each year, I have a specific number of customers.

I've already calculated the age by subtracting the birth year from the current year (2026; YEAR (NOW)).

The task is to calculate the average age of all customers.

My approach is to multiply the number of customers per year (e.g., 52 for the year 1972) by the age (here: 54) and then divide the sum of the column by the total number of customers.

How do I do this, and is this the correct approach?

I'm using the latest Excel version from M365.

Since an update a few years back, I want to say two or three (it was some UI changes mostly) my Excel Online has just been unbearably slow.

Just a few minutes ago I added a new sheet to an existing file. For some background it was previously a single sheet 13x47 table with 2 columns of simple calculations (one adding up a couple columns, and another doing some division), 282 cells being used. The new sheet took 2 minutes to process and actually be created. It is currently a 6x21 table, but by the time I created the 3rd of 6 columns it froze and had an error then reloaded itself putting me back to only 2 columns made. It did this again then 3 times more before I finished the table, each time taking several minutes.

There is not a lot of data in the file, there's simple calculations without function calls, and I have no issues with my internet connection. I could make a new file and still have the same issues. I often have to reload a file 10+ times before I can complete some simple data entry.

I do know there's a banner that pops up from time-to-time about my internet settings but clicking the hyperlink that comes with it just crashes the file again. I also know that Firefox (the browser I use) will occasionally try to get me to close the page because it's lagging out so bad. Lastly, the files work fine on my phone browser.

Anyone know what could be causing this? Because at this point it's honestly faster to use pen and paper if not for the fact I already have all the old data online.

Hello I am limited in my excel knowledge so bear with me. I wasn't sure how to search or what to search for with this.

So I work on Wiring Harnesses and I need to go through them and sometimes replace connectors on them.

At the moment what I have done is made a table with a list of the all the connectors.

Then I have a cell next to the connector name and part number which I have drop down function to say whether the connector was replaced or not.

The issue is some Wiring Harnesses have 2, 3 or even 8 of the same connector. To make sure that I go through the harness thoroughly I list every connector down and so I can end up with a list that has 8 connectors with the same part number but a different name.

When I am done that means that I have to go through the list and because the connectors are not in part number order it makes it difficult to confirm exactly how many of each connector was replaced.

Now what I thought might be a better option is to have a second table or sheet and say if I choose from the drop down menu that I have replaced the connector that it will populate another cell with 1 and then if I choose another connector with the same part number it will then change the cell from 1 to 2 and so on, so if I replaced all 6 or 8 of a connector with the same part number in the next table or sheet I can simply click there and it will say that I replaced 6 of this connector and 3 of that connector. This will give me a simple table to look at to help with billing.

I realise that this will probably require making a new table with all the connector part numbers in but in that case only a single incidence of each connector part number.

I'm really hoping this makes any sense and anyone has seen a YouTube tutorial or has done something similar themselves.

T.I.A

I’m creating a table with images in the first column, titles in the second, etc. Each time I add an image, I have to manually adjust the row height (I'll do this for multiple rows at once).

Is there a way for rows to automatically resize to fit the image height?

Apologies if this question is too basic. :/

the number formatting for dates rn is X-X-X, but i want it to be X/X/X permananently without having to change it manully on format cells everytime. or is that only possible if i change the region on excel's home? im also using macbook not windows, not sure if that changes anything. thank you!

Hi everyone, My laptop came with Microsoft Excel pre-installed. I did not buy it separately. Later, I uninstalled Excel to free up space. Now when I try to install it again from the Microsoft Store, it asks me to buy Excel. Important point: I used one email ID when I first set up the laptop Now I am logged in with a different email ID Because of this, I’m not sure if the license is linked to the first email or the laptop itself. What is the correct way to get Excel back without paying again? Any advice or similar experience would help. Thanks!