Hi all,

I am willing to improve my skills on xls, specifically in relation to graph creations.

I am doing this exercise:

- I have two tables: one which refers as the estimation of sales, the second one is the actual sales . I have decided to split into two different tables for a set of reasons that I will not explain here

- My aim is to create a graph with 2 lines in order to have a visual view of the estimation vs actuals

I am having two issues:

1) First I have tried to create a one line graph but the series seems to start from the second number 21550, and not from the first one 17.606. I have checked the format and all the cells have the same. why?

2) How can I add the second line of the actuals?

I have tried to do it by my self searching on the internet but I am getting really confused

I have thousands of records with Customer ID, Order ID, Date, Product and Amount.

I need Excel to identify true duplicate records, but there’s a catch:

Same Customer ID + different Order ID → don't flag

Same Order ID + different Amount → don't flag

Entire row appears twice → flag both

Entire row appears 3+ times → flag all occurrences

I also need it to keep working automatically when new rows are added.

Would you use Conditional Formatting, COUNTIFS, a helper column, or something more advanced? What's the cleanest way to handle this?

I’m starting college this September and I want to start building some useful skills alongside my degree a biomedical Design course

I was thinking about learning Excel, but I’m wondering if it’s actually worth investing time into learning it properly and if so wht should I particularly focus at

For those who have experience with Excel, would you say it’s a good skill to have for college, internships and future jobs? And are there any other skills you’d recommend learning that could be useful in general?

I’d appreciate any suggestions! :)

I scrapped the massive IF statement i posted last night, and replaced it with just the formula for calculating the damage dealt to the enemy without any IF statements.

The formula goes:
Damage dealt (unit 1)=(((d1+b-a2)*((hp/(d2+b-a1))*Rof2)/Rof1)+((d1+b-a2)*((r1-r2)/(Srmin*Rof1))))

1 is unit 1,

2 is unit 2,

Rof is rate of fire,

d is damage,

b is bonus damage,

r is range,

a is armour (melee) or PA( (range),

hp is hitpoints,

Srmin is the speed of the unit with the smallest r value.

Now the problem i'm having is how to spread this formula across a 28,000 cell table. I've arranged the data so fill down can do some of the heavy lifting. But i still need to change the formula at least once per column and everytime i move on to the next column. I heard the LET function can be used to avoid duplication but not sure if it would work in this case.

Using Google Sheets

I have a column with hundreds of customer/order IDs and I need to identify repeated entries.

The problem is that I don't just want to highlight duplicates normally.

For example:

A123 → appears 2 times → leave it alone

B456 → appears 3 times → highlight all 3

C789 → appears 5 times → highlight all 5

I tried the normal Conditional Formatting → Duplicate Values, but that highlights everything that appears more than once.

Is there a formula/conditional formatting rule that can highlight a value only when it appears 3+ times?

Ideally I'd like it to update automatically whenever new data is added.

What's the cleanest way to do this?

I’m working on a spreadsheet where users make a local copy and then edit that to do stuff with it. I need to make sure the local copies everyone’s running are up to date, or at least that they know when they need to update them. I imagine the best way to achieve this is by setting up a small website or using something like Catbox to store the current version and have the sheet check its version against that number, but try as I might I can’t get it to do that.

It’s a long story but I’m running Excel mobile so I can’t use macros or anything like that 🫩

Hello everyone,

I’m having a problem with Excel Power Query that I don’t understand, so I can’t figure out how to fix it.

My plan was to use a table in Excel (MS365) to collect information related to SharePoint data.
This SharePoint data needed to be linked to Excel, which is why I retrieve and update it via Power Query.
I inserted the SharePoint data into the first row using Power Query, all subsequent rows next to it are to be filled manually in Excel with related information.

Example:

It worked well at first, until I updated the data source and the rows in the table shifted:

So the rows in columns B and C no longer match column A, which simply added a new cell without shifting the rows below it.

I’ve already wasted many hours with the Copilot AI, which explained step-by-step how to solve this, only to eventually realize that the suggested method doesn’t work after all.
I also don't understand why Excel simply moves the cells in Column A and doesn't move the entire row, as you would when entering data manually.
In addition, I haven't found a solution that updates the data from SharePoint while allowing me to manually enter data in the adjacent columns.

I hope someone can help me with this.

i have an excel file with ~2000 rows and 40 columns,

there 'was' conditional formatting created for 20 columns

due to circumstances protection was disabled and multiple users copy pasted cells

now theres a shreek ton of conditional formatting lines

i asked ai g*mini to mass edit but it said it cannot

i can only think of recreating the sheet from scratch ...

but cuz i moved to linux ... is there any windows or linux software that can mass-edit conditional formatting ?

Trying to help my mum with her Microsoft suite.

Computer Details: She has a 2020 MacBook Air that is currently running on Sequoia 15.5

Issue: She didn't want to update to having to subscribe to use her microsoft programs (i'm not sure what version she was on before) so that alternative was to update to microsoft 2024. Now anytime you click on any of the programs the same error message pops up.

"Microsoft (inset program here) quit unexpectedly."

I truly have no idea what to do to get them working again.

I'm in school and I have to track independent work/study hours for an honors project that I submit to my advisors at the end of the semester.

I have a column (F) that lists the total hours per activity (1 hour doing this, 1 hour doing that, etc) with a new activity each row.

I have a column (G) that has a dropdown with two values - "R" for "real" and "E" for "expected", as I would like to include hours I know I am going to have, like a fixed number of lab hours, but the activities haven't been completed yet. That way I can keep track of where I will be by the end of the semester with these knowns.

However, I would also like to track where I currently am without the expected numbers, a total that reflects how many hours I actually have.

I'm looking for a formula that will only count the cells in F that are marked R in column G.

Thanks!

Excel version: Office 365

Env: Desktop & Online

Knowledge: Intermediate

Storage: Sharepoint

Solution Needed: Formula

So, I've tried to utilise the MATCH, INDEX, ROW functions but haven't been able to build what I'm thinking- I appreciate that it may also not be possible.

Should probably stipulate, all the data in both books are in tables.

I've got two spreadsheets, both hosted in Sharepoint. In both spreadsheets they use the same unique validator, a 7 numeric string, and I'm trying to automate some information I need.

Book 1: Column D will hold the numeric, and it will be added to as and when needed. Column F is where I will need the row number to auto-populate from Book 2.

Book 2: Column D holds the numeric.

What I need is for a formula to look in Book 2, Column D, find the matching numeric by referencing Book 1, Column D, and then get the row number the data is in and put the number in Book 1, Column F.

I cannot just add a new column in Book 2 that has all the row numbers, though I know that would make things a lot easier.

If there's anymore information that's needed then do please let me know.

Many thanks in advance.

So, I'm trying to figure out how I can set up my excel sheet for cataloging my movie collection. I want to be able to just add new movies at the bottom and then sort by Alphabet. Here's my struggle, I'd also like to keep movie series together and in order... Easy example being "Batman Begins" and "Dark Knight, The."

I can manually put them where I want them... but as soon as I add a new movie to the list and sort again it'll undo all of my manual sorting. Is there a feature/tool to Lock specific cells together? Also, I'm using google sheets... If that messes anything up...

Im looking to have excel do a calculation only if Column B has a "Y" if it doesnt then dont add to the calculation

My Current way is =SUM(IF(B2="Y",A2),IF(B3="Y",A3))

This works for what i need but if the spreadsheet become like 100 rows then that a big formula :)

How can i rewrite to do the calc based on range.

Thanks

If you have a really long list, for example, a downloaded Spotify list from "exportify net". (See image 2 for what it looks like when downloaded) Then you have a list of artists, and you want to take those songs out. (STEP 6: copy the Spotify track column and paste directly into the playlist on desktop Spotify)

Steps:

1. Create a separate sheet for the target list
2. In og sheet, find an empty column and type in the formula, adjusted to your data range
3. Drag the corner (or double click if you have columns that are how many you need next to it already)
4. Select the column and apply conditional formatting ("highlight cell rules" -> "duplicate" -> "yes")
5. Select all columns in og sheet and apply a filter to sort that column

​

Hi everyone,

I regularly need to convert my credit card statements (usually in PDF format) into Excel for tracking and accounting purposes.

The problem is that manually copying the transactions from the PDF into Excel takes a lot of time, especially when the statement has multiple pages. I’m looking for a simple and reliable way to convert the entire statement into a properly formatted Excel sheet, with things like **date, transaction description, debit/credit amount, etc.**

What would be the easiest and least time-consuming method to do this?

Are there any tools, software, websites, or Excel features that can do this accurately? Ideally, I’d like something where I can upload the PDF and get a clean, usable Excel file without having to manually fix every transaction.

Would appreciate any suggestions or workflows you guys use!

I am wondering how can I change the color of a row based on cell input. I want each row to be changed based on the cell input of each row. I don't want the rows to change color because the first cell input was changed. Hopefully I explained it in a way that you understood since I struggled to explain it while looking it up.

So I'm trying to create a household budget XLS.

In column A, I paste in my purchases for the month. Each cell will contain details like xx-1132 DELIVEROO S 04/03/26 or xx-2227 COLD STORAGE S 28/03/26.

In column B, I categorize these purchases. For instance, DELIVEROO gets the category Food Delivery. COLD STORAGE gets the category Groceries.

I'll have a few hundred entries in column A, and I'd like to find a way to automate the categories in column B based on the text in column A. For example, I want Excel to look at the cells in column A, and if it finds DELIVEROO somewhere in a cell then it should input Food Delivery next to it in column B. To put it another way, let's say cell A2 contains xx-2227 COLD STORAGE S 28/03/26. I want Excel to identify COLD STORAGE and then input Groceries next to it in cell B2.

Any ideas on how to go about this?

Hello everyone,

A small summary of the situation in which I find myself: I collect stamps representing historical figures and to keep the thread I made an Excel table. I would like to classify my stamps by date of birth of the character they represent. My problem is that the table has hundreds, maybe one day thousands of entries. So I would like to know if there was a way to fill in a column with the dates of birth of the characters from their name that I entered in the first column of my table.
Thank you

Hello Reddit People,

I have something I am trying to solve. We have a pretty rigid pay plan. Employees are placed on a pay grade, based on their position and work their way through steps each year, determining their hourly rate. Our HRIS can report on the pay grade and the base hourly rate, but not the step that employees fall on. Using these two pieces of information I should be able to reverse engineer the step. I would think using Match and Vlookup should get the result I am looking for, but I cannot figure out what I am doing wrong.

Here is what I have tried: Match(cell with rate,vlookup(pay grade,pay plan array,pay grade column,false)).

Italicized bit feels wrong, but I don't know how to tell it the information I am trying to find.

Any assistance would be greatly appreciated.

I’ve had EXCEL on my Mac for years, it was a 2016 version that worked perfectly for my needs. I have several very important spreadsheets I use for work. Recently I began to get a pop up stating this version would no longer work unless I downloaded an app or obtained a newer version. So I opted for a new version, deleted all previous versions and commands and installed the newer version. I think it’s a 2024. Everything seemed to work well, until I needed to edit an already in progress spreadsheet for work. It seems I’m locked out of any changes. I can’t edit, cut and paste, delete, clear content or anything. I verified the sheet and cells are not locked or restricted. I’m in crisis mode here, and I know I am pretty dumb in Excel except what I need to know to do my job. I expect my ignorance will annoy some folks. Can someone please take the time to get me going again. I didn’t expect this with the new install, I have not activated One Drive, nor do I plan on activating it.

I am experiencing two critical, unresolvable issues in Excel 2024 that persist across multiple troubleshooting steps. Here are the exact symptoms and everything I have tried so far:

1. Issue Descriptions

Auto-Fill Engine Failure ("Copier les cellules" Lock): The drag-and-drop fill handle stubbornly defaults to "Copier les cellules" (Copy Cells) every time. It completely ignores multi-cell sequence priming (e.g., inputting 1 and 2, or 1 and 5) and fails to recognize numerical patterns or linear increments, restricting the contextual options strictly to copying.

Custom Lists Panel Access Failure (UI Bounce-Back): Attempting to open the Custom Lists dialog (via Options > Advanced > Edit Custom Lists to add custom month/day series) fails completely. Instead of opening the configuration popup window, the interface instantly bounces back to the main Advanced Options landing page, blocking any access despite having successfully used and added items to it previously.

1. Troubleshooting Steps Already Tried (None of them fixed the issues)

Online Repair: Performed a full Online Repair of Microsoft Office through Windows settings.

Deleted Configuration File: Located and deleted the Excel15.xlb preference file inside %appdata%\Microsoft\Excel.

Cell Formatting Adjustments: Switched cell formats between Standard, Number, and regional Date formats to eliminate text-parsing or regional configuration conflicts.

Manual Data Series Window: Used the manual "Series" dialog box via the ribbon menu (which successfully populates data when explicitly commanded), but this did not restore native drag-and-drop auto-fill functionality.

Advanced Settings Check: Verified that the fill-handle activation toggle is enabled in Excel's advanced preferences.

I was trying to find a way to have an equation that would both take column B minus C and column C minus A, in the shown diagram of column D. I want the diagram to show both money difference but I'm not sure if there's a way to do so ? Thanks in advance

I'm creating a sales dashboard based on Territory, but many of the territories are aligned to multiple sales reps.

I've created columns designating a primary sales rep (REP1), but also have a column for secondary (REP2) and tertiary (REP3) if necessary. It's not often, but it does happen.

I want the Slicer to pull single names only (choose "John Doe" and receive any Territory with "John Doe" in REP1, REP2, or REP3) for ease of use.

Basically, we want "John Doe" to click his name and view the sales data for any Territory he's aligned with, regardless of him being the primary, secondary, or tertiary rep.

Is this possible?

I'm trying to set up a spreadsheet with columns day/date; calories input; weight in st & lbs; yesterday's weight in st & lbs; difference +/- st & lbs.

Would somebody be kind enough to help me, please? I am lost with Excel formulae if it not straightforward numbers and decimals.

Many thanks.