Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [spreadsheet]

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

3
votes
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
2answers
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
1answer
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
3answers
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
1answer
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
3answers
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
1answer
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
0answers
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
1answer
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
1answer
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
2answers
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
0answers
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
1answer
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
2answers
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
1answer
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
1answer
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
0answers
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
1answer
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
0answers
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
0answers
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
1answer
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 ...