# Questions tagged [spreadsheet]

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

41
questions

**3**

votes

**2**answers

313 views

### Why is this simple Excel calculation not netting Zero?

It's supposed to be zero but for some reason the result produces a peculiar series of digits at the end.
Also, here's the .xlsx of it: http://83.212.111.36/Book1.xlsx

**18**

votes

**5**answers

76k views

### How to Label / Tag Items in a Spreadsheet

I'm a novice at spreadsheets, so I hope you'll forgive me if I'm asking a super obvious question.
Is it possible to use tags in Excel/Google Spreadsheet? I'm creating a spreadsheet to log all the ...

**22**

votes

**1**answer

64k views

### How to sort an Excel spreadsheet columns by row contents?

How do I sort the columns of an Excel spreadsheet? The Sort command from the Data menu only allows me to sort rows by the value of a column. I want to sort the columns alphabetically by the contents ...

**15**

votes

**6**answers

139k views

### Select each nth row in Excel

I have an Excel spreadsheet with thousands of rows. I want to select every 7th row from that spreadsheet. (By "select," I mean delete all of the other rows, or copy the selected rows into a new ...

**8**

votes

**3**answers

7k 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 ...

**6**

votes

**5**answers

41k 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 ...

**14**

votes

**1**answer

25k views

### How to scale a long LibreOffice Calc sheet to fit on two pages when exporting to PDF?

I have Libreoffice and I want to export a long calc sheet to a pdf document with the fit-to-page function from the page preview in the file menu but it doesn't seems to work. I want to fit my sheet ...

**9**

votes

**10**answers

104k 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 ...

**22**

votes

**1**answer

20k views

### What is in LibreOffice Calc the equivalent to Excel's “Paste Special > Value”?

In my professional life, I'm limited to use Microsoft Office. In Excel there is an option ows to render the result of a formula to be pasted as a "fixed value".
For instance A1 = "I",B1 = "ride", C1 =...

**6**

votes

**2**answers

23k views

### How can I print Excel spreadsheet files automatically?

My company has computer-generated reports that come out as Excel spreadsheets which are emailed to the Billing Department. They would like these reports to be automatically printed as they are ...

**4**

votes

**4**answers

1k views

### Spreadsheet forumula questions to pick a random DVD

I have a list of my (279) DVD’s in an OpenOffice Spreadsheet (version 3.3.0) on my Windows 7 laptop and I want to create a formula to randomly pick a film to watch (for occasions when I can’t make up ...

**3**

votes

**4**answers

9k views

### Adding a constant in LibreOffice Calc

How do you add a constant to a group of cells in LibreOffice Calc?
For example, here are four cells:
47 28 35 62
Now add 3 to each cell:
50 31 38 65

**1**

vote

**2**answers

4k views

### Microsoft Excel: display realtime filtering data from another worksheet

I want to display all rows that has Gender column equal to M inside sheet2. The data is retrieved from sheet1. The filtered data also has to be real time, meaning whenever I make change in gender ...

**0**

votes

**2**answers

4k views

### Referencing a single row to multiple rows in Excel

I have a worksheet with a list of sales, one sale per row. In order to import this data into my accounting software, I need to create a separate worksheet that formats the data differently. Each row ...

**0**

votes

**2**answers

2k views

### Make comma separated list from column with blanks, fed by checkboxes

I want to make a spreadsheet where user can check boxes on one worksheet, and have those values then be brought over, comma separated, into one cell, on another worksheet. The values of the checkboxes ...

**7**

votes

**5**answers

3k views

### Excel Formula to Calculate Duration of Time Based on a String of Text Such As 9:00am — 12:30pm

I'm trying to develop an Excel formula that would calculate the actual time duration for a string of a time range of the following format: 1:00am – 3:00am
According to this article it seems it should ...

**3**

votes

**2**answers

9k views

### Split Text into Columns Function in Excel

I have an excel spreadsheet with rows of data as shown below. There is a code with the product name and then a number.
QA32 Product One (Large) 23
AZ63 Product Two 96
PR65 Product Three 149
How do I ...

**3**

votes

**1**answer

2k views

### Sum the first N elements in a spreadsheet column

I'm trying to sum the first N elements in a column, where N is a value in another cell.
I've come up with this:
=sum(B1:indirect(concatenate("B", A10)))
Where A10 contains N. But I don't want to ...

**3**

votes

**6**answers

47k views

### In Excel, copying cell *values* (not formulas)… yet keeping the cell formats

I have an Excel spreadsheet with multiple worksheet tabs for collecting data... and a final tab with a lot of formulas, to summarize the other tabs for a given date range.
The goal is for the ...

**3**

votes

**1**answer

6k views

### In Excel, doing a “Freeze Panes” on the right-most column instead of the left-most?

My understanding of Excel's "Freeze Panes" feature is that you can only freeze the left-most column(s), or top-most row(s).
However, I have a table where a very important column needs to be located ...

**3**

votes

**4**answers

3k views

### How do you find the maximum value in a column, skipping to every nth value?

Using LibreOffice's Calc program (nearly the same functionality as Microsoft Excel) I have a set of numbers in column T, which represent numbers for the week (with labels in S). Each week's worth of ...

**3**

votes

**1**answer

677 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

**2**answers

408 views

### How do I copy specific selected data between cells in a CSV file in Excel or Notepad++?

I have to download media file databases on a daily basis and modify metadata from a csv file so that the unique numerical prefix in column B is added as a suffix to the cells in column C.
Is there a ...

**1**

vote

**1**answer

518 views

### Find highest number of consecutive outcomes for each person in list

I've got an Excel list that looks like this (with the name in column A and the score in column B):
N. Nam Score
1. Jim 7
2. Jim 4
3. Jim 4
4. Jim 7
5. Jim 7
6. Jim ...

**1**

vote

**1**answer

58 views

### Counting from & to specific dates & times on a spreadsheet - mostly done, but some bugs

I wanted to make a spreadsheet that I could enter a date & time and calculate how much time (in years/dates/months/weeks/hours/minutes/seconds...)
I kept expanding on it. it serves very little ...

**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

**3**answers

1k views

### How do I read old star office 5.x spreadcheet documents today? (*.sdc)

I found a couple of old spreadsheet documents that I created a couple of years ago that I would like to read again.
I think I used the old Star Office 5.2 to create those Spreadsheets.
They all ...

**0**

votes

**2**answers

823 views

### Unique string values in range

I have some spreadsheets where there are large number of cells that have essentially been used for free text.
There is a finite set of values for this free text and most, if not all repeat.
eg.
...

**0**

votes

**1**answer

29 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

67 views

### How do I create a ''droplet'' script in Win7 that runs a Notepad++ task on a CSV file

I have recently asked a question on how to perform a neat little 'Find & Replace' task on my CSV media database files (a task i have to do at work on a daily basis). The question was answered here ...

**0**

votes

**3**answers

310 views

### Apply a formula to particular cells in Microsoft Excel

Say I have the following sheet:
| A | B | C |
1 | 5 | 1 | 5 |
2 | 8 | 8 | 99 |
3 | 77 | 3 | 3 |
4 | 2 | 7 | 4 |
5 | | | |
6 | Base:| 2 | |
...

**0**

votes

**1**answer

43k 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 ...

**0**

votes

**1**answer

11k views

### Excel formula to find frequency of occurrence of unique words in a column

I have a spreadsheet with a column with repeating number of different kinds of words like
A1
A2
A3
A1
AA
A2
A7
A8
A1
Now I want to plot a graph showing the frequency of occurrences of a word. The ...

**0**

votes

**1**answer

9k views

### Excel - creating a master template?

Sorry for my second Excel question in as many days! Almost there now though :)
I've created a template for an invoice for my mum... is there anyway I can sort of lock the file, and create sub ...

**0**

votes

**1**answer

102 views

### How do I select a set of specific cells and then copy their entire row?

Okay this is hard to explain.
I have a massive spreadsheet with sets of data in it. Each set has 'type' column (eg: system, memory, video card, etc). I need to be able to search within this column ...

**0**

votes

**1**answer

1k views

### Excel spreadsheet formula to sum a column

I'm trying to create a formula that sums a total of only the trailers that are in the yard, but don't have a Date Unloaded next to them.
(Click image to enlarge)

**0**

votes

**2**answers

162 views

### How to merge Excel worksheets by stacking columns, using an ID column?

I have a Worksheet #1 containing:
ID VALUE1
----------------
ABC 8274
DEF 613
GHI 236
and a Worksheet #2 containing:
ID VALUE2
----------------
ABC 11.13
XYZ ...

**0**

votes

**1**answer

123 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
...

**0**

votes

**1**answer

227 views

### how to pass a string value into COUNTIF?

For this formula:
=COUNTIF(A1:A10,"jim")
how can I pass in a variable? That is, let's say that cell C11 has the value "jim"; can I then do something like:
=COUNTIF(A1:A10,TEXT_VALUE_OF_CELL(C11)) ...

**0**

votes

**4**answers

167 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

**2**answers

209 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 ...