# Questions tagged [spreadsheet]

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

**3**

votes

**2**answers

374 views

### Handle pasting of decimal with dot (1.7) when locale is expecting comma (1,7)

I've a datasource where the dot is used as decimal separator, i.e. : 1.49 means "one and almost-half". Cannot change it.
This number must be copy-pasted to a numeric cell in Excel or Calc. The file ...

**0**

votes

**1**answer

44 views

### SUM depending on column value

I have a table that looks like this:
In cell F2 I would like to have the sum of all values in column B where the value in the cell next to it (C column) is equal to "Niels".
How could I do this? (I'...

**1**

vote

**1**answer

35 views

### Incrementing dates after a certain number of entries (in this case 9)

I would like for a date to increment after 9 entries like the below:
1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/2/2018
1/2/2018
1/2/2018
1/2/2018
1/2/2018
1/2/...

**0**

votes

**1**answer

49 views

### Count value pairs and get average of those pairs?

I have a spreadsheet with events that happened on certain dates. I created an extra column with week numbers based on those dates.
Now I got several events per week (number). What I want to find out ...

**0**

votes

**2**answers

53 views

### How can I make Excel filter each additional column as it's added?

If I highlight the entire first row and filter it, I shouldn't need to repeat the process every time I add a new column. Is there a way around that? I could run a macro, but that's still an extra step ...

**1**

vote

**2**answers

58 views

### Output matched value based on range

I have a spreadsheet with two tabs: one is a search term data from an Adwords campaign and another one is a list of text fragments.
I want to use a function to return check if the search terms ...

**0**

votes

**1**answer

2k views

### Making a cell mandatory based on previous cell entry

I am looking to achieve the following:
Column H has 5 potential inputs from a data validation list:
Pass, High, Medium, Low & N/A
If Column H is either High, Medium or Low, I need Column I to ...

**0**

votes

**1**answer

64 views

### Multiline cells are displayed as single line cells

Looks like the problem occurs when spreadsheet is automatically generated or converted to Excel format from something different.
I've saved spreadsheet from google drive as excel spreadsheet (.xlsx) ...

**0**

votes

**1**answer

57 views

### Spreedsheet IF statement being weird

So I got two equations:
=SUM(C1:INDIRECT("C"&H5))+(H1*H5)
And:
=SUM(C1:INDIRECT("C"&H3))+(H1*H5)+(K1*(H5-H3))
Now I want them in a IF statement like this:
=IF(A1>$H$3;($C$1:C1)+($H$1*...

**0**

votes

**2**answers

59 views

### MS Excel: How to insert words in cells using Find and Replace

I have several cells like this:
----------------------------------
|Test point 1 using a microscope.|
----------------------------------
|Test point 2 using a microscope.|
----------------------------...

**0**

votes

**2**answers

90 views

### How do I copy a value rather than a cell reference in a formula?

If I have the following cells
A1 | =A3 |
A3 | 8 |
So the cell at A1 outputs '8' as it simply references A3. But if I delete the 8 from A3, it deletes the 8 from A1 also. How do I 'copy' the 8 ...

**1**

vote

**1**answer

146 views

### golf statistics in excel

I this this is a very simple answer to a simple problem but I cant work this out. In my golf stats the cell contents in row 1 are the par of the hole, and row 2 has my score for each of the holes.
I ...

**0**

votes

**0**answers

70 views

### Excel conditional formatting copy in table

Is this known a issue? When I add a new row to my table by inserting email, my conditional formatting does not work properly. As you can see in the picture, only one format works, the other one does ...

**0**

votes

**1**answer

104 views

### Pivot table with multiple, non hierarchical, columns

Let's say my source data looks like this:
Brand Model Color Region Sales
---------------------------------------
Ford Focus Black N/A 23
Ford Focus White N/A 2
...

**0**

votes

**1**answer

23 views

### Separate content of a cell by spaces

I have a spreadsheet on Google Docs, in which are multiple columns. Each row contains all the data tags in one cell. So it is not separated under columns. How could I separate it into more cells? ...

**0**

votes

**0**answers

71 views

### How can I split cells to a new row? [duplicate]

I have products in a spreadsheet in which some have multiple categories. I need to split those categories in to a new row with the same product information. For example, I have the following:
Product ...

**0**

votes

**0**answers

682 views

### Splitting excel sheet into multiple sheets based on two columns

I have a table of 10000 rows X 50 columns. The table is all in one sheet and I am planning to split that sheet into multiple sheers based on values in two columns (columns number 5 and 1, ...

**2**

votes

**2**answers

6k views

### Highlight the cell if greater than value in the next column

So I tried experimenting with conditional formatting, but unable to find a way to highlight the cells in the column if their value is greater than the one in the next column under the same row.
For ...

**3**

votes

**1**answer

206 views

### Tracking the longest streak of 100+ points in a column?

I have a column of scores. Assume it is column B. How would I go about tracking the most consecutive 100+ point streak within the column?

**3**

votes

**1**answer

208 views

### Append three columns in LibreOffice

I have three columns in my Calc file
A B C
1 4 7
2 5 8
3 6 9
i want this as result, another column or A with B and C data
A
1
2
3
4
5
6
7
8
9

**1**

vote

**3**answers

65 views

### How can I copy the formula used in a cell to another cell as a literal formula

Cell A1 has the value 1.
Cell A2 has the value 2.
Cell A3 has the formula entered " = A1 + A2" and shows the value 3.
Similarly Columns B1,B2, B3, C1, C2, C3 etc.. etc..
I want to copy the formula (...

**0**

votes

**1**answer

63 views

### How to Sort unique Contacts?

I have a list of 35k contacts, I need to sort unique after unique
I demonstrated in this example how I want to sort contacts:
I tried by removing duplicates then to copy unique and to go back to ...

**0**

votes

**1**answer

274 views

### How to set the numeric format in Excel so that every three digits have a space for some arbitrary long numbers?

For example, if the format is like this: ### ##0,
the number 1283300 will be formatted like this: 1283 300 but I want to display it like this: 1 283 300
or number 75125690347812567 I want to ...

**3**

votes

**1**answer

122 views

### Excel: Based on value of cell in column A (three possible values), and cell in column B (integer), determine the value of cell C

I am trying to work out a problem as follows:
Column A: one of three possible values. {apples, oranges, pears}
Column B: an integer.
The value of column C should be calculated based on the previous ...

**7**

votes

**3**answers

1k views

### Spreadsheet (Excel, Google Docs etc), extracting value separated by comma

I have a value in one cell that looks like this
4,8,2
Lets say it's in the A1 cell
how I can get the inner 8 (the second comma separated value) from this cell into the cell B1 ?
=?
Tried with =...

**0**

votes

**1**answer

333 views

### Joining data from two Excel spreadsheets

I currently have two Excel spreadsheets. One displays a list of people, many of whom are part of a certain organization. There's a column for Person ID, which they all have, and another column for ...

**0**

votes

**3**answers

312 views

### How do I reorder cells in Excel?

I recently had to gather some data from a website for which I used a simple scraping script to collect and store the data in an excel file. The problem I'm facing now is that in the present form, I ...

**0**

votes

**1**answer

990 views

### Excel: Let Users Click, but Not Edit, Locked Cells

Is there a way to allow users to click on, but not edit, the contents of a cell? I know I can tinker in the settings in Review->Protect Sheet with the "Select Locked Cells" option. But here's the ...

**0**

votes

**0**answers

43 views

### Deleting rows in Excel based on individual date range

I have seven Excel files containing information about admissions of several hundred patients to various hospital departments, and data appertaining to these (length of stay, type of discharge etc.) ...

**0**

votes

**1**answer

309 views

### How to create an Excel chart of a subsection of a table that updates itself?

I'm working with a table that has a Day column followed by many others with different data. It looks like this:
I created a chart of this table, works fine and it updates itself if I add new values ...

**-1**

votes

**1**answer

67 views

### Sorting Excel Data

Right now the data looks like this:
COLUMN1 COLUMN2 COLUMN3
40 Broad Street Homeland Mechanical, LLC Owner
Quincy, MA ...

**1**

vote

**2**answers

173 views

### MS Excel: how to use COUNTIF() dynamically

I have a spreadsheet to keep a log of faulty items. Every item is logged onto a separate row and there is a "Usable?" column for each item. This is what and example row looks like:
Item | Usable?
...

**0**

votes

**0**answers

15 views

### I cannot modify a password-protected excel spreadsheet despite the only other user with the file open is viewing it as “Read Only”

Title. Even though another user does not have the password to modify the spreadsheet, their viewing of the file is preventing me from getting to the password screen and is forcing me into "Read Only" ...

**0**

votes

**1**answer

14k views

### How to combine multiple formulas in 1 cell

I am trying to combine the below two formulas in one cell, but no luck so far.
Both formulas work OK on their own, but I can not figure out how to combine them together to produce the result in same ...

**1**

vote

**2**answers

489 views

### Excel: Using nested IF statements containing wildcard characters

I'm currently working on a project where I have a list of patient visits to a clinic. Each patient is assigned a Primary Care Provider, but that's not always the Rendering Provider at a given visit. ...

**0**

votes

**1**answer

75 views

### OpenOffice calc colorize data points differently

I gathered some FTP file size statistics for my boss and I wondered if OpenOffice calc had the ability to colorize the data points differently. I want to color green the data points within one ...

**0**

votes

**1**answer

53 views

### Excel formula for comparing columns where text IS LIKE

I work as a data analyst in a clinic and we're currently working on determining how many patients saw their assigned provider at their last visit. I typically run the reports and then export them to ...

**1**

vote

**0**answers

56 views

### Excel on Mac - First worksheet overwritten with the second

So I'm new to Mac and I'm working with a spreadsheet in Microsoft Excel. It has one worksheet in it initially. While I'm working with it, I start putting things into a second worksheet. When I'm done, ...

**0**

votes

**1**answer

43 views

### Excel - Merging Spreadsheets

I am trying to merge two spreadsheets together through common data in the A column and make the two aligned. The first spreadsheet has more information in the A column then the second spreadsheet so i ...

**1**

vote

**0**answers

51 views

### Ignore the values in a row if a cell contains x [closed]

I have a Google Sheet with a filtered team roster. After a player leaves the team I'd like to preserve their points from a column sum ("Team points"), but remove other metrics of theirs that go toward ...

**0**

votes

**1**answer

36 views

### What's the correct way to run a monthly simulation from a table data?

I have different areas and want to see month by month how their expenses progress.
Let me give you an example:
Data:
+-------+----------+-----------------+--------+
| Area | Fix-cost | Per-person-...

**0**

votes

**2**answers

35 views

### Ordering Spreadsheet data cells by partial key

I have 4 columns in an excel file:
The two on the right have about 9000 rows.
The two on the left have about 14000.
The THER_CLASS column is the first 6 digits of the key column.
How would I ...

**0**

votes

**1**answer

319 views

### Excel Formula Query - How to auto copy data to specific sheets

Microsoft Excel question:
How can I auto copy certain data from a main/master sheet to other specified sheets?
E.G - Master sheet logs all the data. I would like each row of data to be copied to ...

**0**

votes

**1**answer

465 views

### Excel 2013: Extract multiple rows from spreadsheet based on column of unique IDs

I have two spreadsheets:
The first contains a large amount of data on research projects. Each project has its own unique ID, listed in the first column of the spreadsheet. However, these IDs are ...

**0**

votes

**1**answer

461 views

### Excel - Compare rows content and flag up differences in another cell

I'm trying to work this one out:
Shift content is the routes that must be taken on a particular shift
Driver knowledge is the routes that a particular driver knows
Each day they get allocated a shift....

**1**

vote

**0**answers

212 views

### Dynamic timeline spreadsheet with selective stickiness?

I'm trying to create my first spreadsheet ever, but there's one function that I'm finding very difficult to find guidance for from the internet, just like the title of this post felt a bit clumsy to ...

**0**

votes

**0**answers

54 views

### Pulling data from a range of spreadsheets into a single spreadsheet (where sheet names change)

At school we have various spreadsheets to track the progress of our students within their different subjects. To allow us to keep track of the progress we always have a sheet named after the current ...

**0**

votes

**1**answer

36 views

### How to repetitively apply formula to spreadsheets?

Every Monday, Freddy, "the numbers guy" of ACME Widgets Incorporated, downloads a spreadsheet of the widget production data from the past week. He opens it in Excel and writes some formulae to ...

**0**

votes

**0**answers

62 views

### Find first occurrence of a small sequence from a long list in Excel

I have a long list of words in separate cells in a row
in an Excel spreadsheet.
For example
A B C D E F G
blue red yellow blue green red purple ...
I ...

**1**

vote

**1**answer

3k views

### How do I pull data from one cell to another using matching info in excel? [duplicate]

So, let me try to explain what I need. I have two different workbooks of data. I need to match info from "WB1 Column B" to "WB2 Column A". If there is a match, I need to pull the info from the same ...