Questions tagged [spreadsheet]
Questions about spreadsheet software in general and interoperability between spreadsheet programs.
700
questions
3
votes
2
answers
4k
views
Keyboard shortcut to add a worksheet to the right of the current sheet
Clicking on the + near the worksheets tab on Excel 2016 adds a new worksheet to the right of the current worksheet. However, hitting Shift+F11 adds a worksheet to left of the current worksheet. The ...
- 1,402
12
votes
5
answers
70k
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 ...
- 338
0
votes
1
answer
81
views
spreadsheet formula for returning parts of filtered cells in column
I use Libreoffice. (If Excel or GoogleSpreadsheets would be the only way to get a solution, I would be willing to switch. But the preferred platform is LibreOffice, I'm looking for a solution on this ...
- 157
0
votes
2
answers
1k
views
How exactly do I make multiple columns into one row to sort?
Currently trying to sort data into alphabetical order, however, I am running into a problem.
Every time I try to sort it the product number doesn't match up with the product.
I have been going on ...
0
votes
1
answer
63
views
Copying from a table row to spreadsheet with formula
I have this sample data from my HTML table row.
Tester [email protected] 2018-07-19 1000 1200 new
And I am copying this data to google spreadsheet with a formula ...
- 103
0
votes
2
answers
660
views
Lookup Function Giving Inconsistent Results in Excel & Google Spreadsheet
I am trying to create a column of looked-up values using the LOOKUP function. The goal is for the formula to look up the "rank" value of each "name" and print it in the "lookup" cell on the same row.
...
- 115
2
votes
1
answer
804
views
A single pound sign '#' to custom format excel cell?
I need to display a very big number within Excel, for example a following number: 174587584558159.
Excel displays it by using a scientific notation, like following: 1.74588E+14.
I tried to format ...
- 121
0
votes
1
answer
1k
views
Updating column values from one Excel spreadsheet to another
I had no idea what to Google for this. Basically I have 2 Excel files: File A and File B.
Here is File A:
And here is File B:
I am trying to loop through File B. I want to look at each row's first ...
0
votes
1
answer
51
views
two date cells - i want 3rd cell to some value
I have two cells with completed installation dates. I have a 3rd cell where I need to input outstanding installations.
If the 2 cells have dates, it means the installation is complete so I want the ...
- 1
1
vote
1
answer
63
views
how to save time with multiple COUNTIFS possibilities?
I am sure that there is a smarter approach then this brute one:
=IF(COUNTIFS(O40<>P40;O40<>Q40;O40<>R40;O40<>S40;O40<>T40;O40<>U40;
p40<>o40;p40&...
- 945
0
votes
1
answer
42
views
Excel - can you setup a certain worksheet to open when the spreadsheet is first opened?
Hoping someone can help, I have a spreadsheet that I need to open at a certain page, everytime when opened eg.. an index page.
I have the Vb code and works offline but I have since found out this ...
- 1
0
votes
2
answers
2k
views
DATEDIF() not returning expected value, how can I calculate age in months?
I'm trying to calculate the age of my credit card accounts in Excel. I've read online to use the DATEDIF() function, however, I'm not getting the expected result and I'm not sure where I screwed up.
...
- 370
1
vote
2
answers
93
views
Automate completion of spreadsheet for invoice
I need to invoice someone each week. I would like to create a template as 90% of the information on each invoice is the same, and the remaining 10% follows simple rules.
Each invoice has a number ...
- 1,493
-1
votes
1
answer
76
views
Copy cells from one excel spreadsheet to another based on a value in the row
I have a spreadsheet, Sheet 1, with data in it taken by the minute. Each line row has a timestamp and the corrosponding data follows.
I have a second spreadsheet/sheet, Sheet 2, with accompanying ...
- 1,361
1
vote
0
answers
115
views
In Excel, is there a way to autofil formulas that link to other files with information from other cells in the current spreadsheet?
I have a master spreadsheet that collates data from other spreadsheets, and shows this via linked cells - for example, I would have ten identically set out spreadsheets, each showing a different total ...
1
vote
0
answers
109
views
Show contiguous time periods without data in Libre Calc pivot table, pivot chart?
A minimal example: our table has sales in 2015, 2017, but not 2016.
Date Sales
01/02/15 $500
10/11/17 $1000
You make a Pivot Table:
Data > Pivot Table > Create
Row Fields: Date
Data ...
- 111
0
votes
1
answer
130
views
Generate cell pairs based on first row and column
Given a spreadsheet like this:
x | pressure | distance | height
pressure
distance
height
I would like an output like this:
pressure pressure
pressure distance
pressure height
distance pressure
...
-1
votes
1
answer
195
views
Return value pairs based on first row and column
Gnumeric has a nice tool for calculating correlations, but is limited in terms of specifying your output. It looks like this, Figure 8-7 :
Correlations | pressure | distance | height
pressure | ...
0
votes
1
answer
143
views
Spreadsheet: Split a column if it contains a certain string
Here is an extract from my spreadsheet:
Please note: this is just a simplified example. My full spreadsheet has many columns and over 10,000 rows.
In my spreadsheet, I frequently have rows that have ...
- 461
-2
votes
1
answer
964
views
How to update item quantity in excel?
I don't know much about Excel or spread sheets to be honest. But my girlfriend has been working on updating item quantities for the past two days. There are over 500 items.
Basically what she needs ...
0
votes
0
answers
160
views
How can I transfer data between spreadsheets using vlookup if spreadsheet changes each month? Can I set it to auto-update?
I have the above data in two spreadsheets.
I receive the top rows. (raw data)
Then total them using formulas. (middle rows)
Then need to move them into a separate spreadsheet. (bottom rows,
desired ...
2
votes
4
answers
5k
views
Is it possible to make the cell cursor blink in excel?
I have a large spreadsheet - with quite a bit of colour and bold font etc.
It's often hard to see the cursor - i.e. to find the cell that is selected. It just appears as a slightly thicker green line ...
- 413
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 ...
- 21
1
vote
1
answer
6k
views
Excel is changing my time input (32:00:00 becomes 8:00:00)
I am making a spreadsheet in Excel to keep track of time. When I enter 32:00:00 (hh:mm:ss) on the spreadsheet, it changes my time to 8:00:00. But when it adds up all the time at the bottom it still ...
- 13
8
votes
3
answers
9k
views
How to compare two Excel spreadsheets?
I would like to compare two Excel spreadsheets
Both have the same columns, in the same order, but not the same rows.
I have a spreadsheet with 1,000 rows and 50 columns including a unique ID for ...
- 83
0
votes
1
answer
53
views
COUNTIFS with dates
I'm trying to get the number of support tickets for each month. In my first tab "Tickets" I have:
The date values have the following characteristics:
In my second tab I have:
I want to know how ...
- 115
1
vote
1
answer
2k
views
Excel, update duplicates automatically
I have a table, something like this:
--------------------------------------------------------------------------------------
|Id | Name | Category | Sub-Category | Supplier
-----...
- 113
0
votes
1
answer
84
views
Spreadsheet: find a cell keyed by date
This is a question about Apple Numbers spreadsheet program.
Suppose I have two sheets: Benchmark and Training. In the Benchmark sheet, I track a benchmark value over time:
| Date | Benchmark |...
- 1,595
1
vote
2
answers
2k
views
Excel: using a formula to only sum from above row until next blank cell?
I'm a bit of a noob so bear with me. I'm making a spreadsheet of various products bought and sold at various prices. The table is sorted by product, with a blank row separating each complete buy-sell ...
- 11
8
votes
2
answers
8k
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
59
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'...
- 115
1
vote
1
answer
41
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/...
- 13
0
votes
1
answer
90
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 ...
- 1
0
votes
2
answers
181
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
1
vote
2
answers
77
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 ...
- 111
0
votes
1
answer
9k
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 ...
- 1
1
vote
1
answer
2k
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) ...
- 113
0
votes
1
answer
76
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*...
- 1
0
votes
2
answers
79
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.|
----------------------------...
- 3
0
votes
2
answers
7k
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
1
vote
1
answer
710
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 ...
- 29
0
votes
1
answer
1k
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
...
- 960
0
votes
1
answer
28
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? ...
- 247
0
votes
0
answers
871
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, ...
5
votes
2
answers
60k
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 ...
- 325
2
votes
1
answer
390
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?
- 325
3
votes
1
answer
1k
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
- 33
1
vote
3
answers
836
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 (...
- 11
0
votes
1
answer
119
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 ...
- 11
2
votes
1
answer
8k
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 ...
- 687