Questions tagged [spreadsheet]
Questions about spreadsheet software in general and interoperability between spreadsheet programs.
700
questions
5
votes
8
answers
7k
views
Excel spreadsheet bogged down, Is there an alternative software? [closed]
I work for an organization with a yearly budget that is an Excel spreadsheet consisting of about 25 large sheets that are linked to one another. There is a big summary sheet that draws it's numbers ...
0
votes
1
answer
72
views
Excel TODAY() function
I have two tables in excel
The first table is called Oranges, it shows where the oranges were produced and where they were exported to:
The second table is supposed to show the total oranges shipped ...
2
votes
2
answers
219
views
Creating an Invoice in Excel
I have only dabbled with Excel and not much in quite some time now.
I have an Excel Invoice laid out so Column A is a name of a job and each row in column A can be a different job.
Now column B ...
10
votes
10
answers
120k
views
Can't insert cells in Excel 2010 - "operation not allowed" error message
I was working on a spreadsheet in Excel 2010, and all of a sudden when I attempted to insert a new row of cells, I saw that the insert and delete options were grayed out.
I attempted to copy a ...
2
votes
2
answers
2k
views
Copy paste from spreadsheet to browser list
I have a browser based form like the one shown below.
The Student No and Name of the Student fields are not editable.
I need to key-in numbers to the Total field.
The Student No field is numeric. ...
1
vote
1
answer
780
views
Merge 2 excel spreadsheets both containing updates in rows and columns
I have 2 spreadsheets and need to merge and update the data combination of both.
Spreadsheet A
userid, name, phone1, phone2, city
1, mister T, 123 123, 123 124, paris
2, mister A, , 123 124, tanger
3,...
4
votes
1
answer
7k
views
OpenOffice Calc - how to insert BLANK in a formula?
In Calc, as in most spreadsheet applications, an IF() function exists that can be used to control behaviour, for example inserting this formula in cell C1.
=IF(A1>B1;"A";"B")
will display A in C1 ...
1
vote
1
answer
2k
views
Is it possible to automatically separate line/scatter plot data into different series based on an additional column?
My data looks something like this:
H, 2015-12-11T19:44:34, 3270
W, 2015-12-11T19:44:34, 2150
H, 2015-12-11T19:49:35, 3370
W, 2015-12-11T19:49:35, 2290
...
As a one-off, I can get ...
3
votes
1
answer
3k
views
Assigning rank in OpenOffice (or LibreOffice)
In OpenOffice (or LibreOffice), is there a way to rank rows?
For example, let's say you have a spreadsheet where cell A1=20, A2=30, and A3=10.
I want to B1 to get a value of 2, B2 to get a value of ...
3
votes
1
answer
484
views
How to delete multiple rows in Gnumeric
When I select one row in Gnumeric I get the option to Delete 1 Row:
When I select more than one in Gnumeric, I get a grayed out (non-functional) option to Delete 3 Rows:
Clearly Gnumeric knows what ...
0
votes
0
answers
50
views
Map 2 spreadsheets
I have two spreadsheet.
Both spredsheets have an email field. Spreadsheet 1 has 400 email fields and the other has 1000 email fields. The 400 emails are in the 1000 email spreadsheet. The 1000 email ...
1
vote
1
answer
974
views
OpenOffice Calc "if" formula for currencies
I have a spreadsheet that has many different currencies in it ($, euro, shekel). I want to display the actual price in the original currency and then show the converted price (in this case to shekels)...
-2
votes
1
answer
63k
views
Find duplicates in two different tabs [duplicate]
This is what I would like to do: Tab A Column A has a mass of numbers, Tab B Column B will have numbers inputted into it. I need to have the Tab B Column B highlight showing a match to a number in Tab ...
-1
votes
1
answer
100
views
Excel Budget Spreadsheet
I am trying to create a very nuanced budget in Excel. I have a lot of rules about budgeting and thought it would be easier to create a spreadsheet. When a certain line item exceeds budget it is to ...
1
vote
0
answers
28k
views
How can I create sub-sheets in Microsoft Excel?
I have a workbook in excel that displays information from multiple processes and each of these processes has multiple sub-processes.
I can display information for each process easily using one sheet ...
2
votes
3
answers
1k
views
How do you properly align average and standard deviation outputs in a spreadsheet?
I am not sure of the best way of formulating this question right now so I will use an example using random numbers. I start with values assigned to IDs, so that each ID can be n=1, n=2, ... etc
ID ...
0
votes
1
answer
731
views
Reference a cell with input from another cell
The final objective is to reference the last populated (higher row number) cell from a column (its value).
I found a function that returns the row number of the last populated (higher row number) ...
-2
votes
1
answer
175
views
How do I add a button to a spreadsheet and remove borders from a particular cell?
I have two queries regarding the following Excel file:
I would like a button on this excel sheet whose name should be “Save as PDF”.
When I press this button it should save the excel sheet’s all ...
0
votes
2
answers
2k
views
Entering notes into LibreOffice Calc and OpenOffice Calc
In LibreOffice Calc and OpenOffice Calc, there is often a need to quickly enter several rows of text that will not be part of any calculation area. This is often done to write notes relevant to the ...
0
votes
1
answer
2k
views
How to remove the title from a data table in excel?
Is there a way to keep the data table but remove the values' title (highlighted in red)?
I would like to keep the legend however..
3
votes
1
answer
757
views
Sum values in specific rows based on whether the row has a value that belongs to a range
Here's what I'm trying to do:
I have a table that has several metrics (example: revenue, cost, etc.) for about a hundred different accounts. I can't modify this sheet because it gets auto-updated by a ...
0
votes
1
answer
66
views
Excel string to date format
I tried to convert string date value with format cell option but I failed. Tried search over and over again on web but didnt get better solution.
I want to convert following
23rd July 2015
to ...
1
vote
1
answer
7k
views
How do I add values in column B if column A contains the same employee surname more than once?
State of Data: I have column A which has names of employes and column B has number of hours. How do I add the hours if column A contains same employee surname more than once?
I have been using what I ...
1
vote
1
answer
55
views
How do I take the information from 1 spreadsheet and count it on another spreadsheet based on a set range of values?
I have 2 spreadsheets in the same work book. On the first, I have dates names and others values (y/n, amounts). I need to be able to put in a date range on the second spreadsheet and have it take data ...
4
votes
2
answers
3k
views
Edit spreadsheet object in Word document with protection
I am trying to create a template in Word to be used on a SharePoint site. Using Word 2003, is there a way to enable protection on a document that has an Excel Spreadsheet (97-2003) object on it but ...
1
vote
1
answer
1k
views
How can I convert this date format to the ISO date format?
I have a spreadsheet with a list of dates in the format 25th Jan. I'd like to convert these to ISO format, ie YYYY-MM-DD. The year will in all cases be 2016. Is there some magic equation-foo that will ...
1
vote
1
answer
82
views
How to determine largest individual value in excel in a group of identical cells?
I am trying to figure out Column D Which SKU has highest total? how do I do that in excel with a formula? In this case I determined it manually, I tried IF=IF(A2=A3,IF(C2>C3,"Largest","NOT Largest")) ...
0
votes
4
answers
4k
views
Format percentages in Microsoft Excel
I am trying to format the following column in Microsoft Excel to add the percentage sign after the second decimal:
5.8394
3.2922
3.2
4.461
1
I highlight the column and choose Format cells > ...
0
votes
1
answer
584
views
filter excel list by formula instead of values
If I have a column in excel, with the following formulas, would it be possible to filter it? In this case, the values are all 7, but could I filter by the explicit formula?
=2+7
=3+4
=2+7
7
=1+6
7
=...
2
votes
1
answer
149
views
Excel autonumber range
Most of you know about this little feature of Excel, as well as almost any other spreadsheet software like Google Spreadsheets called Autonumbers. Example.
What I'd like to know if it's possible to ...
1
vote
1
answer
445
views
Count number of cells that match certain criteria with formulae
Suppose I have column with dates:
2015-01-10
2015-08-12
2014-01-02
2015-01-28
2016-10-14
I want to count how many dates fall to January. I would supopse that formulae is like this COUNTIF(A1:A20,"...
0
votes
2
answers
1k
views
Spreadsheet finding and sorting most frequent appearance of names
I have a spreadsheet that looks like the following:
https://docs.google.com/spreadsheets/d/1BN3GNRFCsBeHu9gQaKzIo7bfFRkcDqMJ6VMtidEwPD4/edit?usp=sharing
There is a set list of names in Column A. ...
0
votes
1
answer
118
views
How does the user make a selection in MSExcel that contains only those cells that contain data?
Got an MSExcel question for you.
he specs for my computer are: Intel Inspiron laptop i3-2.20ghz , 8GB DDR3, 400GB HDD, Win 7 Prem.
And the version I am using is MSExcel 2003.
My question has to do ...
2
votes
2
answers
59
views
How to sum two categories within a single column and show the totals to the side of the column?
I have a list of trailers with a column to the side that says if the trailer is full or a pup trailer. I can't figure out how to make the column total how many of each type of trailer and show the ...
0
votes
1
answer
394
views
How can I update running total of money owed month on month automatically?
I have a spreadsheet of a monthly coffee fund that all my staff pay in to.
Is it possible in Excel to have a running total of what everybody owes that increases month on month automatically?
And ...
1
vote
3
answers
515
views
Spreadsheets for Matlab
I was wondering if there was a way to directly write a spreadsheets in Matlab, or if someone had published a Matlab add-on that specifically introduced spreadsheets?
0
votes
0
answers
57
views
Which versions of MSExcel support the 'WEBSERVICE' function?
Pursuant to Excel formula to take out quotations ,
I saw that it's possible to use a form of web-querying in certain versions of MSExcel. Incidentally, what I saw was:
=WEBSERVICE("http://finance....
0
votes
2
answers
11k
views
Complex rota hours calculation in excel
OK so there are a number of different shifts in operation here and I want a total hours formula.
problem is the rota is written using a number of different notations for different shifts
some are 2-...
1
vote
2
answers
1k
views
openoffice calc: sum of a function of elements of a row
If I want to sum the elements of the first row (from the column a to x) of a calc document I can use =sum(a1:x1).
My question is about the possibility to calculate something like this
=sum(a1^2/(1+...
0
votes
2
answers
560
views
Prevent formula input in all cells in a column, but allow whole number input
I am in a bit of a sticky situation. There is a public spreadsheet that some of my coworkers are required to use for their job. They all collectively use it during their shifts to track product ...
1
vote
2
answers
1k
views
Counter in Excel
My file has 50 sheet inside, which each sheet is a standard form. Lets name as Form1, Form2, Form3.
I want to create a print sheet, where I select which form I want to see, so that I don't need to ...
1
vote
1
answer
6k
views
Filtering by currency in Excel
I have this situation in Microsoft Excel:
Cells are formatted by using "Format Cells" and setting the Currency manually for each cell. So, they are not simple strings. I would to sum filtering by ...
-1
votes
2
answers
29k
views
How do I split single cell contents into multiple columns without seperators?
I have an excel spreadsheet that has the following:
Cell A1 ABCD 1234
I would like to split this to be
CELL A1: ABCD
CELL B1: A
CELL C1: B
CELL D1: C
CELL E1: D
I tried using the text ...
0
votes
2
answers
50
views
need help in editing data in csv file
I am in need of editing the date data in csv.
I will explain with an example.
I am using WPS presentation software for reading and editing csv files. If the above is not possible in that, i can also ...
3
votes
2
answers
5k
views
How do I make a random selection in a spreadsheet using more than one criteria
I have a spreadsheet created in OpenOffice (V3.3.0 in Windows 7 32 bit) that I use as a database of my DVD collection. After asking a question in SuperUser, I have it so it can pop up a random title ...
3
votes
0
answers
3k
views
Histogram of dates in Google Sheets or OpenOffice
I have a list of dates that I would like create a histogram of.
date
5/21/2014
5/20/2014
9/16/2014
5/28/2014
6/8/2014
6/8/2014
5/28/2014
5/20/2014
8/11/2014
7/19/2014
8/11/2014
The chart's x-axis ...
0
votes
1
answer
23
views
Calculating non-duplicates
I have below data in three columns.
A B C
07002 72J217.4 72J217
07002 72J229.5 72J129
07002 72J229.6 72J129
07002 72J229.7 72J129
07006 61E001.5 61E001
...
1
vote
2
answers
5k
views
Is there a tool to put a phone number into E.164 format?
I'm looking for both a quick and dirty, as well as easily repeatable, solution to strip non-numeric data from a column in a spreadsheet for import to a MySQL database. Example:
(123)456-7890 fred ...
9
votes
2
answers
2k
views
Is it possible to access and edit Google documents directly from Libreoffice?
I'd like to work with a Google spreadsheet directly inside Libreoffice, without having to export and import it, in order to automatically reflect the changes on the online file.
Basically, I'm looking ...
2
votes
1
answer
2k
views
Excel: how to illustrate a budget constraint that depends on more than two factors in a table? Calculate how much of a house you can afford
Imagine that you want to make calculation of how expensive flat/house you can afford to buy. You know how much you can spend on housing per month (100). You also know that there are three factors that ...