# Questions tagged [spreadsheet]

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

560
questions

**0**

votes

**0**answers

5 views

### Listing and formatting tagged data in Google Sheets / Excel [migrated]

I'm creating a list of texts with some tags associated to them in Google Sheets, each text has a "tags" column where the tags are separated by commas.
I want to have a second sheet where the ...

**0**

votes

**1**answer

26 views

### How to swap rows by just swapping the first cells?

Say I have this table:
Occasionally I need to swap and add rows to reorganize my table. In this example, I put the row Cat at the bottom, and insert row Horse:
And then later decide to move row ...

**3**

votes

**2**answers

138 views

### How to combine two rows in a spreadsheet into a single row?

I want the third row to be a combination of the first two row. The first two rows can be of varying length and will be different length from each other.
How can this be done?

**0**

votes

**4**answers

117 views

### Add a text comment in a numeric cell with Excel

Is there a way with Excel, to put a comment with a numeric cell, like the cell A3 here:
such that formulas like =SUM(A1:A5) still work?
Here the wanted result would be 19.
Here it doesn't work ...

**0**

votes

**1**answer

32 views

### Convert csv-like file (with custom separators) to ods, odf, xls, or xlsx

I have a csv-like file with complicated data and would like to open it in LibreOffice Calc, Microsoft Excel, or another spreadsheet program.
What I mean by csv-like:
Fields are separated by the ...

**0**

votes

**1**answer

32 views

### How would I copy an Excel sheet that has formulas with “Table2[Date]..” to a new sheet?

I created a sheet that uses several tables. There are many formulas that reference different rows of different tables on the same sheet. The sheet was for the month of Feb. I want to copy the format ...

**0**

votes

**1**answer

31 views

### Trying to find the largest value from the given data, per day and then try and map the largest data with date and time onto a graph

Scenario:
A machine is processing a number of items randomly throughout the day, every day i.e. 54x items are produced at 17:00 and then 207x items are processed at 18:00 etc (Screenshot provided)
...

**-7**

votes

**1**answer

48 views

### URGENT =IF(C3>37.6,“high”), IF(C3<36.6,“low”),IF(AND(C3>36.7,C3<37.5,“normal”))) is this formula right

The second half of the formula doesn't work If body temperature of patient (t) < 36,6, it is low, from 36,7 to
37,5 - normal, greater than 37,6 - high.
This is the information to create the ...

**0**

votes

**0**answers

25 views

### How can I Insert new columns without my formula changing?

In cell B2 I have a formula that states =SUM(E2:AAC2). And In B3 I have =SUM(E3:AAC3) (AAC is simply to indicate that I'm trying to sum along the row indefinitely) and this repeats many subsequent ...

**1**

vote

**0**answers

19 views

### How to quickly select items that meet a criterion on excel

I have two excel spreadsheets. One has hundreds of names and emails (the names aren't sorted properly and include people who will not be receiving an email), and another with hundreds of names that ...

**2**

votes

**1**answer

225 views

### Both '.' and ',' for decimal separator in LibreOffice Calc?

I have read How to set decimal separator in Libre Office Calc? but I haven't found an option for this:
Is it possible to set both . and , as decimal separator in LibreOffice Calc?
In France it's , ...

**0**

votes

**0**answers

10 views

### IF used to search a Range in SUMIFS in Google Sheets

This is my first post so.. Let's begin.
I am trying to total salaries contingent on data in two ranges.
My first formula used 3 SUMIFS to check the range of pay, salary/hourly, full-time/part-time ...

**1**

vote

**0**answers

10 views

### finding difference in two columns of a spread sheet?

lets say I have 3 columns
A B C
1 2
2 3
4 5
and i will go on entering numbers in A and B, in C I need to have the difference between col B and col A, can I write a single formula for ...

**0**

votes

**1**answer

21 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

25 views

### How do i make adjacent cell as dropdown with values from a defined named range? Please refer image

I have a Dropdown on cell C11 (options - Google, Samsung, Apple, iPad).
If I select let say 'Samsung' my expected output on D11 is Dropdown with values from 'Samsung Category' named range
Please ...

**0**

votes

**2**answers

69 views

### Data validation based on another cell and two column

I have a list of Food and each food has a color. Food may have multiple colors. For example, below, Banana can be Blue, Green, and Yellow,
Orange can only be Green
On another cell, the user can ...

**0**

votes

**2**answers

60 views

### Multiplying Arrays of Uneven Length based on criteria and Summing the result

I have two tables in my spreadsheet, a Name Table:
and a Category Table:
From the above two, how do I create a formula that lets me calculate cost by category in a single cell as shown below:

**1**

vote

**0**answers

8 views

### How do I turn off autoformat OpenCalc / OpenOffice Spreadsheets

Without context and background: Read the title, How do I turn off AutoCorrect in OpenCalc, specifically time, preferably turn off all AutoFormat.
I am using a spreadsheet to track how fast my online ...

**0**

votes

**4**answers

158 views

### Display column header containing highest value in a spreadsheet

So, I have a list of names and values, linked here:
Ideally, I want to locate the highest value in the spreadsheet, and then display the name of the person with the highest value overall.
So far, ...

**0**

votes

**0**answers

100 views

### How to sync two columns and update the following cells in the rows?

Say I have two files, which have first columns are identical:
Note, that only the first columns are synced. The second columns are different.
Is there a way to make a rule between two files that,
...

**0**

votes

**0**answers

22 views

### Wps spreadsheet function issues

I tried to convert from a kelvin temperature in celsius with this function:
=CONVERT(L3;"kel";"cel")
and works well only with this type of example numbers: 300 or 300.7888436480150.
But if i ...

**0**

votes

**1**answer

39 views

### What should I do if I have to match one column with another column in excel in the same spreadsheet?

I need to find out how many rows in column B match with rows of column A.

**1**

vote

**1**answer

27 views

### How to fit a polynomial trendline such that only the leading two coefficients are non-zero in Excel?

I have data on the displacement of an object over time and a cubic trendline fits it very well.
I know that the initial displacement is 0 so I can tick the "set intercept" box.
However, I also know ...

**0**

votes

**1**answer

36 views

### Excel spreadsheet is showing all 65536 rows?

There's an important spreadsheet that I use in work with around 5000 rows of data, and is updated daily.
Someone has formatted the entire columns to have full borders, thus extending the spreadsheet ...

**0**

votes

**2**answers

52 views

### MS Excel - Add / Subtracting time in 0000 format

I am trying to subtract time on Excel.
My company uses a 24hr time format (0500,0600, 1250, 1300 etc.)
I'm trying to calculate the time difference between 2 cells:
| A1 | B1 |
|------|------|
| ...

**-1**

votes

**1**answer

36 views

### How to automatically update all the relevant cells in Excel when a new row is added

So I have a spreadsheet used for calculating let's say the taxes on something which I purchased. It has a column with the sales tax calculation, state tax, etc. and all each of these columns reference ...

**1**

vote

**0**answers

34 views

### What is the VBA equivalent of using an array formula to calculate the max non-blank value of a range?

I have the following array formula in Excel: {=MAX((C9:C44<>"")*(ROW(C9:C44)))} that calculates and returns the row number of the last non-blank cell in a range.
I could reference the result in ...

**0**

votes

**1**answer

44 views

### How to automatically multiple match/dropdown on Excel

I am trying to generate a report for the assets within the company. I am essentially trying to create an automatic drop down list. I have done some research on using INDEX and AGGREGATE methods but I ...

**-1**

votes

**1**answer

39 views

### How can I OCR the text from specific regions of a screenshot, then output that to a usable format?

I'm trying to automate logging of uh, ranked games in Street Fighter of all things - Yes, nerdy, I know. I have a spreadsheet
In that I manually put in the following for each ranked game:
The ...

**0**

votes

**1**answer

68 views

### How to sum values in a spreadsheet based on matching criteria in another sheet?

I have a spreadsheet where I track income and expenses. The spreadsheet has two different sheets which I am trying to craft a query between (example tables below).
Transactions:
Categories:
The ...

**0**

votes

**2**answers

24 views

### How do I apply =If to an existing formula?

I have this formula currently:
=INDEX(C2:C6,MATCH(MAX(B2:B6),B2:B6,0))
I want to apply this formula if the criteria is something specific in column A. Any ideas how to do it?
Perhaps my initial ...

**0**

votes

**0**answers

20 views

### Filtering long lists in OpenOffice Calc

I have 3 keyword lists separated into columns: Column A, Column B, and Column C
Column A is the primary list, containing 100s of keywords. Columns B and C are secondary lists, containing many, but ...

**2**

votes

**0**answers

37 views

### Chromebook - Sort and Filter in Google Sheets while Offline

I have spreadsheets stored offline on a Chromebook that I would like to sort and filter without needing to go online.
They were created by downloading from Google Sheets, but when I open them locally ...

**0**

votes

**1**answer

53 views

### Adding Values of a specific label/tag in a spreadsheet

I am designing a spreadsheet for an accounting framework where I record all the transactions. Say there are 3 categories of transactions: A, B, C.
I want to record the transaction, label my ...

**0**

votes

**0**answers

11 views

### Using Apple Numbers for iOS, how can I get the daily average with some dates have muliple entries?

Date Company Earnings Hours *Earn/Hr Mls *Earn/Ml Runs
11/23 Quicky $102 4hr 0m $25.50 53 $1.92 6
11/23 Slowboys $20 1hr 0m $20.00 9 $2.22 1
11/22 Quicky ...

**0**

votes

**0**answers

26 views

### Can't replace filtered rows/constants

I have a table which has Questions (Column A) and then multiple possible answers associated with each question (Column B). This means that for most questions there are a few blank rows in Column A as ...

**0**

votes

**3**answers

657 views

### Merge multiple columns into one

In Excel, I'd like to merge/combine the data of multiple, nonconsecutive columns into one (excluding the headers, preferably on another sheet).
Example: I have a sheet containing rows of names which ...

**0**

votes

**0**answers

36 views

### Preserving columns when converting PDF to Excel Sheet

I have a PDF of data which I receive on a monthly basis. The PDF is about 100 pages. The problem is when I convert the PDF to Excel, I lose the formatting of the columns and the data becomes too messy ...

**1**

vote

**1**answer

23 views

### Blank function bar

I used a function to show the result of a calculation, but is it possible to hide such function from the function bar?
Example:
I used C4 and D4 to get the result on E4, when I click on E4, I see ...

**0**

votes

**1**answer

25 views

### How to calculate number of paychecks in a semi-monthly basis?

I know I can easily count it using a calendar, still I wonder, how would a formula tell me the answer if usually people are paid by the 5th and 20th of the month.
Example:
Date of hire: 02/06/19
Last ...

**0**

votes

**0**answers

109 views

### Why don't Excel and Google Sheets allow filtering a non-contiguous column range?

A quick search around the web shows that there is a quite common question about both Excel and Google Sheets: is it possible to create filters for a non-contiguous range of columns? Suppose you have ...

**0**

votes

**0**answers

42 views

### MS Excel - Separate texts by delimiter into specific columns

In MS Excel, I need to separate text by comma, extract three things from right and put them in three different columns i.e. city, State and Country.
Problem:
In the original text which is the full ...

**1**

vote

**2**answers

204 views

### MS Excel - Separate text based on keyword

I need to separate text based on a keyword to different columns. the original text is in one column but I want to split the intended subtext into different columns. The problem in this case is I get ...

**0**

votes

**1**answer

26 views

### Convert Multiple Cell Rows Into Single Cells

I am merging two Excel databases and need to ensure that some prices are the same, however, they are stored in two different ways.
To facilitate easy comparison of the tables I need to convert a ...

**0**

votes

**1**answer

103 views

### Sorting a list of electronic components?

In a spreadsheet the bill of materials of electronic components lists the components by their values, for example, 1R0 (for 1 Ohm), 1k0 (for 1 kilo-Ohm), or 22p (for 22 pico-Farad), 1n0 (for 1 nano-...

**1**

vote

**1**answer

42 views

### Open a spreadsheet with broken formulas

I am using code the generate a spreadsheet, including a formula for a cell on certain rows. When I do this, there is something wrong with the formula, and when I try and open the spreadsheet, Excel ...

**2**

votes

**1**answer

120 views

### Devising Excel Spreadsheet Formulas

I'm using Excel for the accounting purposes of a group venture, to keep track of our expenses, costs, payments, and balances.
Details of each expense are stored in columns A:J
Total costs and ...

**1**

vote

**2**answers

42 views

### How can I sort out a list of numbers according to certain characters in the middle of these numbers?

So, at my job, we want to divide the work of checking certain items according to their registered numbers. Here's an example of these numbers:
0022499-80.2017
0200781-98.2016
0021322-13.2016
Items ...

**0**

votes

**0**answers

64 views

### How Do I repeat a calculation until I cross a threshold in Google Sheets

I have a Google Sheet I'm using to evaluate financial goals. In this sheet, I have 8 cells. The first two cells are used to enter parameters that are used by the formulas in the other cells.
The ...

**0**

votes

**1**answer

26 views

### How to sum a column UP TO “this” cell?

I'm using Google Sheets but hoping for others' sake a general answer might work here. See the screenshot below. When I insert a row below the column containing "$37.50" and I enter a number in the new ...