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

**9**

votes

**4**answers

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

**25**

votes

**2**answers

9k views

### Excel Conditional Formatting Escaping a Question Mark

I would like to use a conditional formatting rule in an excel file that would color any box with a question mark in it red. It seems that Excel is using a question mark as a wild card and will turn ...

**3**

votes

**2**answers

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

**3**

votes

**3**answers

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

**2**

votes

**2**answers

1k views

### How can I combine two lists into one long list?

I have two lists in coloumns 1 and 2. I need coloumn 3 to be of the format one dash two.
For example:
Column C will need to repeat all values of A and B.
I would prefer a formula for this is ...

**0**

votes

**0**answers

67 views

### Copying data from a mixture of protected and unprotected workbooks in Excel

I've been given 16 productivity-tracking spreadsheets from a team, and tasked with pulling the numbers from all the sheets into one, for a 'team average' type deal.
Two of the 16 workbooks are ...

**-1**

votes

**1**answer

393 views

### How to get data from separate worksheets displayed in a single separate worksheet

I am working on a spreadsheet in Microsoft excel 2010. In the spreadsheet I have 3 worksheets. These are known as:
1st
2nd
Cases
Now in the 1st and 2nd Worksheets, there is a table at the bottom ...

**9**

votes

**4**answers

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

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

**1**

vote

**1**answer

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

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

**21**

votes

**1**answer

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

**8**

votes

**6**answers

21k views

### Using excel, how can I count the number of cells in a column containing the text “true” or “false”?

I have a spreadsheet that has a column of cells where each cell contains a single word. I would like to count the occurrences of some words. I can use the COUNTIF function for most words, but if the ...

**6**

votes

**5**answers

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

**4**

votes

**2**answers

2k views

### Automatically filling colors as per the values in the cell in spreadsheet

Question : Is it possible to fill each cell in a spreadsheet with a color whose grayscale value is same as cell value?
Explanation :
Consider I have a spreadsheet as follows :
All values lie ...

**4**

votes

**4**answers

35k views

### Too much text for an Excel cell — how to make the cell scrollable

What do I do when I have more text in an Excel cell than I want to show in the allocated space?
Something making it "scrollable" would be preferred.

**3**

votes

**2**answers

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

**3**

votes

**1**answer

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

**3**answers

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

**2**

votes

**1**answer

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

**1**

vote

**2**answers

141 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

2k views

### How to use multiple criteria with COUNTIF?

INPUT:
This is the format of the OUTPUT desired:
OUTPUT for example INPUT:
The problem is, I don't know how to use COUNTIF with several criteria.
The "dream function" [that doesn't work] ...

**0**

votes

**1**answer

50 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. Please see the link for a screenshot of the spreadsheet I am ...

**0**

votes

**0**answers

131 views

### Don't want to include blank rows in Excel table

I have a table in excel which contains the following columns:
Brands: Cell B
Open Closed: Cell C
Case Number: Cell E
Case Summary: Cell G
At the moment these are the following formulas I am using ...

**0**

votes

**1**answer

850 views

### Copy distinct values in excel from one range to another

Suppose I have a table like this:
A B
food 12
cars 30
cars 40
food 10
food 14
living 100
fun 30
food 40
I'd like to copy all distinct values from column A to some other ...

**0**

votes

**2**answers

705 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

87 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

**3**answers

273 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

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