Questions tagged [spreadsheet]

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

Filter by
Sorted by
Tagged with
3
votes
2answers
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
5answers
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
1answer
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
6answers
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
3answers
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
5answers
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
1answer
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
10answers
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
1answer
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
2answers
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
4answers
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
4answers
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
2answers
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
2answers
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
2answers
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
5answers
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
2answers
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
1answer
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
6answers
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
1answer
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
4answers
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
3answers
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
2answers
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
1answer
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
4answers
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
2answers
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 ...