Questions tagged [spreadsheet]

Questions about spreadsheet software in general and interoperability between spreadsheet programs.

Filter by
Sorted by
Tagged with
2 votes
3 answers
14k views

remove duplicate values from two spreadsheets in excel

I have got two different spreadsheets with some duplicate emails in it but maybe with different information in other cells of the same line. Both of the spreadsheets contain more than 10k lines. I ...
0 votes
2 answers
71 views

Why some cells can not be sorted in Excel

I am met with very strange things with spreadsheets in Excel now. Some cells with a date like 2023-10-22 can not be sorted with other cells in the same format in a column. I changed the format of the ...
0 votes
1 answer
8k views

matching names from one Excel Spreadsheet to another

I have two separate spreadsheets, both with several hundreds of employees names. My task at the moment is to go through one of the spreadsheets to see if any employees are matching on both, which will ...
0 votes
0 answers
26 views

Importing a range of formulas from csv or txt file starts with ' in excel and sheets [duplicate]

I am generating some image URLs with a python script and converting them to a formula. I have tried saving it to both a CSV and a TXT file as others suggested, but both lead to the same problem: After ...
5 votes
3 answers
98k views

How do I calculate the total difference between two columns of figures in Google spreadsheet/Excel?

Usually I would just calculate the difference of each row like this: =B1-A1 Then I would sum up the values of the column like this =SUM(C1:C10) and get my desired value. I don't want the C-column. ...
9 votes
1 answer
18k views

Apple numbers: How can I create a filtered column (B) with only unique values from column (A)?

I'm using Apple Numbers. I have a table with data like: A | B ---------+--------- Spain | U.K | Spain | Germany | U.K | Italy | And I would like to set table B to include ...
0 votes
1 answer
14 views

How to formulate reference another sheets if i have list of sheets name in sheets1? On sheets

How to formulate reference another sheets if i have list of sheets name in sheets1? I want the value in cell b2 in each sheets.
0 votes
0 answers
24 views

Find a chart by his title in Excel spreadsheet

I have an Excel document with numerous charts on one of its sheets. I need to locate a specific chart by its title. Is there a method, similar to using the search and replace tool, to easily find the ...
1 vote
1 answer
68 views

Coloring cells across multiple excel sheets in an xls file based on a condition

I am new to excel. I have an xls file with 10 sheets. In each sheet I have two tables of size 10 x 10, one starting at position A4 and the other starting at N4. All the entries are numbers. (Note that ...
1 vote
2 answers
10k views

How can I pull specific rows in Excel into a new spreadsheet

I have several spreadsheets containing data I need to analyse. I have a randomized list of numbers that dictate which rows in each spreadsheet I need to code for my data sample. Is there a formula I ...
0 votes
2 answers
85 views

How to shorten Excel OR function for many conditions?

As shown in the data, I want to determine whether a student passed (failing if s/he gets less than 40% in any subject). I can do this using many OR's inside IF. My formula: =if(or(b3/b$2<0.4,c3/c$...
0 votes
1 answer
17 views

Clear direct formatting does not work, how do I treat the cell with a date in it as date and no as text

A cell that contains a date, 02/30/2020, is treated as text by my formula datedif from another cell whereas other cells with similar dates are treated as dates. Deleting the content of the cell does ...
1 vote
3 answers
16k views

Display real-time filtering data from another worksheet in Microsoft Excel

I want to display all rows that have a "Gender" column equal to "M" inside "sheet2", where the data is retrieved from "sheet1". The filtered data also has to be ...
2 votes
3 answers
5k views

How to fix a corrupted LibreOffice spreadsheet

I have a substantial multi-sheet LibreOffice worksheet that seems to be throwing a lot of errors in LibreOffice, including crashes. Any idea of how to get it to work as expected? I'm thinking ...
0 votes
2 answers
276 views

How do I create a formula that will count all the "x" in a list, then give me a total for amount paid based on amount of "x"?

I have a list aside for a staff party where we have a running total of amount paid at the bottom, and are indicating who is attending by placing an X next to their name. I would like it to be so that ...
0 votes
2 answers
185 views

Get unique IDs with date 1 or date 2. For IDs with both date, obtain row with most recent of the 2 dates

I have been trying to retrieve the row in the following sheet (sheet1) where the dates are matching dates in sheet2 without duplicating the IDs in Sheet1 (Column B) Sheet1 Sheet2 In other words, I ...
0 votes
1 answer
871 views

How can I evenly auto-distribute values across 12 columns in a spreadsheet so that their sums are as close to equal as possible?

I'm trying to distribute a bunch of annual recurring business costs across the span of 12 months so as to make each month's total expenditure as close to each other as possible. Doing this manually is ...
0 votes
1 answer
50 views

Is there a formula for retrieving data from multiple cells on a different spreadsheet and adding to one single cell on another spreadsheet?

I am working on a project in which I need to retrieve the Parent Items for a list of scrap material. The problem is that most of this material is used in multiple Items. When I run a separate report ...
1 vote
1 answer
766 views

Turn cell references in Excel hyperlinks into the text in the referenced cells

I have a column of bare URLs in Excel. Say it's Column A. I want to replace each URL with a friendly text link, like "here." Suppose A1 contains the link https://www.website.com/content/...
1 vote
1 answer
95 views

replace comma separated values in a sheet uses SORT formula to get sorted results from another sheet

I have a sheet called "Form Responses 1" connected with a Google Form, and I made another sheet/tab and used this formula: =SORT('Form Responses 1'!A2:M, 4, TRUE) to sort the responses with ...
0 votes
3 answers
109 views

Return First Column Header of Instance of String

I have a collection of speakers who have spoken various dates. Date,1/2/2023,1/2/2022,1/2/2021,1/2/2020,1/2/2019,1/2/2018 1st-Speaker,Jim,John,Fred,Sam,Scott,Riley 2nd-Speaker,...
0 votes
1 answer
120 views

Can I have excel automatically swap text in a column from one word to another without using another column?

I'm fairly new to Excel so excuse my noobish question. I am working on a roster spreadsheet and have a bunch of shop codes, essentially abbreviations of the shop name in a column, and I have been ...
1 vote
1 answer
226 views

How can I stop libre office sheets jumping when scrolling wide columns?

Long time sufferer of Windows, moved to Linux Mint around a month ago and I am still getting used to the changes. One very annoying thing that I have Googled, looked at the questions here marked libre ...
0 votes
0 answers
558 views

Copy output from Google Colaboratory and Paste in Excel

Given a Colab notebook with the following cell url = 'https://files.grouplens.org/datasets/movielens/ml-latest-small.zip' extract_zip(download_url(url, '.'), '.') movies_path = './ml-latest-small/...
0 votes
2 answers
13k views

How to add multiple digital signatures to an Excel spreadsheet?

I have an Excel spreadsheet that lives on a shared folder, that everybody can access. On that spreadsheet, I have a worksheet with a list of tasks that have to be completed, but not necessarily by the ...
0 votes
1 answer
50 views

Find stdev of values in col B where col A matches criteria

I have a sheet where column A is dates and column B is values. I want to find the stdev of values in B where the date in A is within the last 30 days. I am able to do sums with a sumifs which works on ...
2 votes
1 answer
497 views

How to set a whole row equal to a value, using ARRAYFORMULA() ? (in Google Sheets)

I would like to set the contents of all cells in a particular row to have the same value, sort of like having a banner that runs across the entire width of the spreadsheet. I'm working in Google ...
0 votes
1 answer
144 views

Can I use Excel to solve a quadratic based on a table of x and y values? (without using a graph)

I have the following data sets where the top bold row are my y-values and the following rows (%) are my x-values. Each row is a different set of x-values. I'd like to plug in 80% for x for each set ...
2 votes
1 answer
2k views

convert excel spreadsheet with format to markdown

I want to convert a spreadsheet table with basic formatting (bold, script) to MD (markdown) format. I know howto convert a table to Markdown but it losses the basic format and i need to preserve it. ...
0 votes
2 answers
82 views

Excel opens at top of sheet only

How can I get Excel (using Office 365) to open where I left off (usually the bottom) and not the top of the sheet? I have a large spreadsheet and usually need to add new information at the bottom. ...
12 votes
5 answers
69k views

How to fix Excel and prevent it from converting copy-pasted or imported strings to dates even when cells are formatted as text

Every time I copy-paste some numerical data written as fractions or separated by hyhens into Excel, it always converts numbers that could possibly be dates into dates. There's no way of getting them ...
0 votes
0 answers
38 views

How can I extract two or three "parallel" XPaths on a web page to/in a format that can be imported into a spreadsheet?

How can I extract two or three "parallel" XPaths on a web page to/in a format that can be imported into a spreadsheet? Here is an example: on https://www.amazon.co.uk/s?rh=n%3A20606777031&...
2 votes
1 answer
238 views

Spreadsheet: View the entire (long) text of selected cell

I am not using spreadsheets very often and I am not sure whether this is a feature of common software (I'm using libreoffice calc). I have a spreadsheet in which 50 to 200 word texts are entered in ...
0 votes
1 answer
115 views

Sequential numbering of codes

I have a sheet where as I enter details of a product, an internal 4 character code is generated...typically 4 letters (e.g. ABCD or BADC). This is just based on simple VLOOKUP and CONCAT functions. ...
1 vote
1 answer
157 views

reference range in different sheet with Indirect column and row

I'd like to reference a range in a separate worksheet using indirect relative row and column notation, something like this: =INDIRECT("'dataset'!R[0]C1:R[0]C4", FALSE) instead of =dataset!a1:...
4 votes
1 answer
171 views

How to stop Excel from combining Conditional Formatting rules into 1 broken rule

I understand that Conditional Formatting is a horrendous tool, much like Excel itself, but it's the only realistic option so I'm stuck with using it. However, I'm unable to grasp how painfully stupid ...
1 vote
1 answer
96 views

What's the mathematical, =SUM() equivalent of the =NPV() formula, in Excel or Google Sheets?

Is there an Excel/Google Sheet SUM formula equivalent to the NPV formula, ie. one that produces an exact same answer given the same input figures? The question might be similar to these ones but the ...
0 votes
1 answer
52 views

Concatenate data (matrices) in separate sheets (Gnumeric)

I need to "concatenate" data from two sheets into a third sheet. I need to do this in Gnumeric, but presumably it should be doable with other spreadsheet programs as well. Sheet 1: | A ...
1 vote
1 answer
153 views

Drag to fill duplicates values

I want to create a sequence of dates that populate a column. A 2023-12-31 2023-12-30 2023-12-29 ... Format Cell is set to Date. Calculation Options is set to Automatic Enable fill handle and ...
0 votes
1 answer
772 views

Find next cell within column that is a number, starting from reference cell in another sheet

I think this sounds simple but I'm having a bit of trouble figuring it out. I have 2 sheets, the Sample Sheet attached and an Input Sheet. In the Input Sheet, I input one of the green shaded values (...
4 votes
6 answers
962 views

keyboard equivalent of dragging the little black square to generate sequence

What is the keyboard way of generating a sequence? The pic below shows libre office calc. In order to generate the sequence of numbers 1 to 10. I simply enter few numbers of the sequence (in ...
0 votes
1 answer
11k views

Linking rows in Excel without merging

I would like to know if it is possible to link rows together in Excel without merging. For example, I would like A1 tied to B1 so when I move A1 around to different positions in Column A, B1 will ...
0 votes
3 answers
6k views

Merging multiple versions of same excel spreadsheet

So here's the situation: I have multiple versions of the same spreadsheet-- each one has the exact same row and column labels. The difference between any two given spreadsheets is that data in one ...
0 votes
1 answer
86 views

Prepend text for spreadsheet

I want to have a string value prepended to a XL-style formating in a spreadsheet program (gnumeric). The current formatting is [$-f4f2]hh:mm which should take 1:00 PM as input and convert/display it ...
1 vote
4 answers
8k views

How to filter a spreadsheet on multiple filter values

I've got a spreadsheet of addresses with the following columns: First Name, Last Name, Street Address, City, State, and zip code. I'd like to be able to filter or otherwise extract a subset of these ...
1 vote
1 answer
67 views

Custom Information Message for user based on another cell value

I am busy with an optimization spreadsheet which needs to display an information message, maybe similar to the Input Message for data validation (but I am open to suggestions). This message will be ...
0 votes
0 answers
16 views

Dynamically changing content of a cell dropdown for validation because of a huge list

I have a huge lookup list of items, each with a conversion factor that I wish to look up. The lookup list is too big to be practical for a validation dropdown - this is how it looks: Here's the ...
-1 votes
1 answer
43 views

Spreadsheet: store multiple entries for one "field" per row

I have a spreadsheet that inventories our sheet music collection with multiple fields like title, composer, etc: Octavo # Title - original language Composer Arranger Editor Poet Translator Voicing ...
0 votes
1 answer
68 views

Print Excel Spreadsheet using Template?

So Let's say I have a spreadsheet No Name Class Gender 1 Jack Science Male 2 Rose Science Female 3 Kevin Social Male 4 Olive Social Female and I have template No Name Gender 1 2 3 4 How to ...
1 vote
0 answers
68 views

How to draw empty cell values in LibreOffice Calc

my problem is the following (didn't find info how to do it in the official documentation) - recorded ~108k ping results to LibreOffice table with columns DATE (letter A), LATENCY (letter B), STATUS (...

1
2 3 4 5
14