Questions about using in-cell functions/formulas in spreadsheet software.

learn more… | top users | synonyms (5)

61
votes
2answers
3k views

Excel - can I set a cell to equal a certain value, no matter what is typed in it?

This is to play a joke on someone...not sure if it's possible to do what I want. We are circulating an Excel file among friends to select players for a golf tournament. I would like to set a cell (...
53
votes
7answers
423k views

In Excel, how do I check if a cell is in a list of values (a range of cells)

I've got a range (A3:A10) that contains names, and I'd like to check if the contents of another cell (D1) matches one of the names in my list. I've named the range A3:A10 'some_names', and I'd like ...
46
votes
7answers
134k views

How to create a formula for every row in a column in Google SpreadSheet?

I use three columns. A, B and C. In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2. How can I do so I don't have to type in the new formula in ...
45
votes
2answers
13k views

What does “A1:A4^{1,2,3}” mean in an Excel formula?

What does the caret operand in Excel do to a range when it is followed by some other numbers in brackets? For example lets say we have the following table: A B C 1 1.5 0 0 2 -0....
41
votes
4answers
114k views

Is it possible to sum an entire column without setting explicit cell boundaries in Excel?

I'd like to sum all the values in a column starting from a certain minimum value, but I don't want to give an upper bound because any value in the table will be appropriate for summing (minus the ...
38
votes
13answers
103k views

Format a number with optional decimal places in Excel

I have numbers in cells in Excel. I want the numbers formatted so that if they have decimal places they show to a maximum of two, and if they have no decimal places it doesn't show any. For example. ...
37
votes
10answers
108k views

Formula for updated currency exchange rates in Excel or other spreadsheets?

What kind of spreadsheets support a formula like the following: =exchangeCurrency("USD"; "EUR"; 3000) The result should be 3000 USD exchanged into Euros. Possibly a date could be provided for a ...
37
votes
2answers
236k views

What is the Excel hotkey to re-calculate all formula in sheet?

I have a sheet that contains many formulas. But they do not recalculate automatically when I change input. I'm looking for a hotkey, that can re-calculate the sheet. According to this page, F9 ...
32
votes
8answers
144k views

How do I get the day name into a cell in Excel?

Given a date, how do I get the day of the week (e.g. "Monday") into a cell in Excel?
32
votes
8answers
551k views

How to compare two columns and find differences in excel

I have two columns in Excel that I want to compare and find the differences between them. Suppose: Col A has 50 numbers i.e 0511234567 Col B has 100 numbers in the same format
30
votes
9answers
4k views

Limit Excel CPU Usage

Is there a way to limit how much CPU usage excel has access to when running? I have a VBA script that calculates a bunch of giant array formulas. The entire calculation takes maybe 20 minutes using ...
30
votes
7answers
145k views

How to count number of distinct values in a range?

I've got a large table that is already organized using filters etc. I'd like to add a summary underneath certain columns that contain the number of distinct values in that column. There's no ...
29
votes
7answers
61k views

Type math formulas in Microsoft Word the LaTeX way?

I wonder if there is some free solutions for typing math formulas the LaTeX way in Microsoft Word document (Office 2007)?
25
votes
3answers
4k views

How do I hide the #DIV/0! error while a referenced cell is blank?

In Column C I have Production. In column D I have Goal. In Column E I have variance %. My formula is =(D11-C11)/D11 However, how do you hide the cells down the sheet until you put something in D11 &...
25
votes
2answers
3k views

How to simulate a full outer join in Excel?

Let's assume I have some data in Excel (and not in a real database). In one sheet, I have data, where one column functions as the ID, and I have made sure that the values in this column are unique. In ...
21
votes
1answer
75k views

How do I put logical operators in an Excel =IF Formula?

I'm trying to enter a formula to display text according to an IF condition. The best I can manage is something like... =IF(myval>=minval & myval <= maxval, "OK", "Not OK") But this ...
20
votes
1answer
89k views

Create a pie chart from distinct values in one column by grouping data in Excel

I have an Excel document containing information from a survey. I want to create a pie chart over location (countries). How can I make Excel group all the distinct values together and then display ...
16
votes
16answers
153k views

How to get the current column name in Excel?

What is the function to get the current line number and the current column name for a cell in Excel?
16
votes
4answers
41k views

In Excel how can I sum all the numbers above the current cell?

I want to have a column in Excel that consists of a header, a bunch of numbers, and then have the sum of those numbers at the bottom. I'd like the sum to adapt to the insertion of new numbers above ...
14
votes
3answers
80k views

Excel: Replace occurences of one character with another in a String?

I want an excel formula that will change Haverford, PA to haverford,+pa. (Text is to lowercase, and spaces are replaced with plus characters.) The following will get the first space, but not all of ...
13
votes
4answers
55k views

Is there an Excel formula to identify special characters in a cell?

We have about 3500 documents whose filenames need to be manually scrubbed to remove special characters like brackets, colons, semicolons, commas, etc. I have a text file that I've dumped into excel, ...
13
votes
3answers
45k views

Excel function that evaluates a string as if it were a formula?

Suppose I have a text string like "11+5" or even "=11+5" stored in a cell. Is there a function in Excel that will allow me to actually evaluate that string as if it were a formula? This would be ...
12
votes
4answers
76k views

How to search for newline or linebreak characters in Excel?

I've imported some data into Excel (from a text file) and it contains some sort of newline characters. It looks like this initially: If I hit F2 (to edit) then Enter (to save changes) on each of the ...
12
votes
4answers
41k views

Can you do Regular Expressions in Excel without VBScript?

I'm looking for an Excel function that I can put in a cell to do a regex search of the contents of another cell. Is there anything available to do this? I'd prefer not to have to add VB Script to ...
12
votes
1answer
8k views

How do I copy the results of a calculation without copying the formula behind it? Open Office Calc [duplicate]

How do I copy and paste only the results of a calculation without copying the formula behind it? I'm learning to use Open Office Calc. I remember other spreadsheets that had a values function, or ...
11
votes
3answers
32k views

Excel formula to convert boolean values into {0, 1}

What is the Excel formula to convert boolean values {FALSE, TRUE} into {0, 1}? Supposing there is one shorter than =IF(cond,1,0).
11
votes
5answers
73k views

How to get time difference as minutes in Excel?

I have time values in two cells in an Excel sheet and I need the difference of the times in minutes. The cells in the Excel are like below: A B C 1 6:38 8:23 1:45:00 A is ...
11
votes
5answers
55k views

Is there an Excel function to create a hash value?

I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real ...
11
votes
2answers
11k views

In Excel, can I provide a “default value” in case my formula returns #NA?

For example, Say the value of a cell is: IF(ISNA(VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,FALSE)), 0, VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,...
11
votes
7answers
58k views

Change year in many date fields in excel?

So I've noticed in a worksheet I have many dates that are four years off, no doubt because of improper copy/paste from earlier question. Is there an easy way to convert a bunch of dates I select to a ...
11
votes
4answers
8k views

Is it possible to get the sheetname?

How can I get the sheet name in a cell? (I have more than one sheet, I want to get the current on each sheet)
10
votes
4answers
29k views

What is a formula to remove the whitespace from an Excel cell when trim doesn't work?

I have a table of Excel data that I copied from an html table in Internet Explorer. A column of table values all seem to have a space at the end of them, but Trim won't remove it. When I paste-special ...
10
votes
2answers
92k views

What is the difference between function and formula in Excel?

What is the difference between those two when speaking of Microsoft Excel?
9
votes
4answers
45k views

Check if Excel cell text is numeric using formula only

I have to find out if my cells text is a numeric value and wanted to use an elegant non VBA method that doesn't impede on its current state or value. What I've found is that the ISNUMBER() function ...
9
votes
4answers
56k views

Using find() in Excel to remove text from string

I'm using find to remove some of the test string in lookup cell value For instance I look-up the following in cell A2 Application: sales_rep_support and use find =RIGHT(a2,FIND(" ",a2)+4) to change ...
8
votes
5answers
44k views

Is there a function in Excel to find the maximum absolute value of a range?

I'm looking for a function in Excel that looks something like = MAX(ABS(A1:A10)) except ABS() doesn't take a range of numbers. The best that I can come up with is: = MAX(ABS(MIN(A1:A10)),ABS(MAX(...
8
votes
6answers
22k 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 ...
8
votes
1answer
9k views

Shortcut to accept suggested formula in Excel

I'm working with a lot of formulas in Excel 2007 and I would like to know what shortcut key you need to press to accept the suggested formula. When I type in =DEG the first suggested formula is =...
8
votes
5answers
99k views

How to combine values from multiple rows into a single row in Excel?

I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this: ...
8
votes
3answers
6k views

How do I download a stock price in Excel 2013? Is there an equivalent to Google Doc's GoogleFinance spreadsheet function?

In a Google Docs spreadsheet, I can use this cell formula: =GoogleFinance("GOOG", "price") to download the latest price of a stock. Is there an equivalent function in Excel 2013? Earlier versions ...
8
votes
1answer
3k views

How does the NOW() function get evaluated cross time zones in Excel Online?

How does EXCEL ONLINE evaluate the NOW() function when a spreadsheet is accessed from different time zones? For example, if user PAUL is in London, UK and accesses a sheet at 5PM GMT and user SUE is ...
7
votes
5answers
71k views

Best way to remove leading zeros from a non-numeric value in Excel

I have many cells in an Excel sheet, using 9 characters of 0-9 and A-Z, that have some number of prefixed zeros: 000000123 000001DA2 0000009Q5 0000L210A 0000014A0 0000A5500 00K002200 I'd like to ...
7
votes
4answers
55k views

Dynamically reference a Named Table Column (via cell content) in Excel

How do I reference an Excel table column dynamically in Excel 2007? I want to reference a named column of a named table and the referenced column will vary with the value of a cell. I have a table in ...
7
votes
1answer
36k views

Excel, how to find column number of min value?

I have a table in Excel. Can anyone help me find the column number of the cell where the min value of the row is? Example: 200 is the min value in row1 and has the column number 1. Is there a ...
7
votes
2answers
1k views

Conditional concatenate cell content across rows

I have a worksheet with thousands of rows which contain a unique ID A, and one 3 different error codes M. Each individual ID may have 1, 2 or 3 errors. They will always be in the same order. I have ...
7
votes
1answer
7k views

Get column number of an address in Excel

In cell LJ131 I have written the text string "$HG$86" (w/ out the quotes). I need to get the column number of the address value represented by that string, $HG$86. Everything I've tried thus far ...
7
votes
2answers
3k views

Formula for finding 'near perfect' matches [duplicate]

I have a list of +-8000 items which is the result after applying numerous techniques to remove duplicates (the list started at over 10000 items). I now have the following issue - Example 1. Jack ...
6
votes
2answers
1k views

How to generate random string from a set of available strings in Excel?

I want to generate a column which will have random string values in any order. Those string values have to be from ('New', 'Mint', 'Very Good', 'Good', 'Acceptable', 'Poor') strings? Can anyone let me ...
6
votes
4answers
33k views

Microsoft-excel newlines and tab

Current I use "CONCATENATE" to join string in Excel, but I want insert character newlines and tab in string joined by "CONCATENATE". Example: I've string below when I completed join string. "Today ...
6
votes
5answers
12k views

How to return everything between two characters in Excel?

I'm a programmer with lots of languages, but somehow I've never learned Excel macro or formula language. I'm sure there must be a simple way to do this, but I'm stumped. I have a column of file names ...