# Questions tagged [worksheet-function]

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

8,196
questions

124
votes

14
answers

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

120
votes

10
answers

483k
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)?

102
votes

9
answers

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

96
votes

14
answers

20k
views

### For x=3 in Excel, why does = - x^2 + x result in 12 instead of -6?

Suppose my cell A1 in an Excel spreadsheet holds the number 3.
If I enter the formula
= - A1^2 + A1
in A2, then A2 shows the number 12, when it should show -6 (or -9+3)
Why is that? How can I ...

81
votes

9
answers

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

71
votes

4
answers

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

69
votes

2
answers

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

61
votes

2
answers

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

60
votes

11
answers

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

59
votes

4
answers

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

54
votes

8
answers

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

52
votes

2
answers

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

49
votes

10
answers

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

47
votes

11
answers

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

46
votes

5
answers

11k
views

### Is there a concise Excel formula to calculate (A1*A6)+(B1*B6)+(C1*C6)...?

I want to know how to give a concise formula for the following long formula in Excel sheet:
= (A1*A6)+(B1*B6)+(C1*C6)...
Do I use a sum function or is there another function for this? I think using ...

46
votes

3
answers

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

45
votes

2
answers

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

8
answers

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

40
votes

17
answers

396k
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?

33
votes

9
answers

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

33
votes

10
answers

158k
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?

33
votes

4
answers

5k
views

### AND function gives TRUE result for empty, BLANK cell - why?

I have blank B2 cell.
=ISBLANK(B2) gives TRUE
A simple logical test on it returns FALSE
=IF(B2,TRUE,FALSE) gives FALSE
Yet when directly used in an AND statement, it returns TRUE
=AND(B2,TRUE) ...

31
votes

4
answers

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

27
votes

2
answers

67k
views

### I need to find the last Space in a XLS Cell

I have an Excel sheet with text values, e.g. in A1: "This is the text". I need to find the position of the last space character. Is there an excel formula that will give me the result I want? I have ...

27
votes

3
answers

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

27
votes

1
answer

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

26
votes

4
answers

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

26
votes

4
answers

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

25
votes

5
answers

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

23
votes

5
answers

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

21
votes

4
answers

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

21
votes

1
answer

88k
views

### What would be the the mathematical equivalent of this excel formula? =PMT()

I need to create a JavaScript form that does the same calculation as this =PMT() function.
mortgage = 220000
rate= 4.75%
term = 30
The example formula I have is =PMT(4.75%/12,30*12,220000*-1)
What ...

21
votes

6
answers

59k
views

### Sum contents in column from starting cell on down without setting an explicit last cell index

I have an Excel spreadsheet and would like to sum the numbers in a column starting at one cell (say, B5) and continuing down to the last number in that column.
I know I could just enter a large ending ...

20
votes

5
answers

18k
views

### How do I get the =LEFT function in excel, to also take the number zero as the first number?

My data contains a column of room numbers. Each room number's first 2 digits is the floor number where the room is located. The =LEFT function works well for data that does not start with zero. The =...

20
votes

6
answers

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

19
votes

10
answers

194k
views

### How to dynamically show the current time (and not date) in Excel?

I want to fill a cell with the current time, so it changes every time I open the file.
I currently use the NOW() function for that, but it returns both the date and the time.
I want to see only the ...

18
votes

7
answers

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

18
votes

3
answers

58k
views

### How do I create a data validation list WITHOUT using cells?

I have a simple data validation dropdown that I'm trying to do, but I DO NOT WANT to use cells to populate my list.
Something similar to what's below, but this obviously isn't working.
What ...

18
votes

1
answer

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

18
votes

9
answers

74k
views

### Excel function determine if a cell is visible

In conditional formatting I want to detect if the row above the current cell is hidden or visible. How can I detect if a cell is visible or not?
The only hack I can think of is:
Create a column with ...

17
votes

3
answers

73k
views

### Fill a large range with a formula in Excel, without mouse-dragging to extend

I have 5000 elements in column A and 5000 in column B
I want the element wise difference and stored in column C.
The subtraction command I know is =A1-B1 then drag all the way down with a mouse.
...

17
votes

9
answers

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

16
votes

10
answers

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

16
votes

4
answers

169k
views

### How do I reference an Excel sheet name in an Excel formula? [closed]

How do I reference an Excel sheet name in an Excel formula?

16
votes

4
answers

133k
views

### Excel-Overcoming #VALUE error with FIND function

In cell A1 I have entered "Apple". In B2 I enter the formula =FIND("Apple",A:A). However I keep getting #VALUE error. Can anyone explain this and how do I overcome it?

16
votes

7
answers

62k
views

### Custom Formula Not Updating

I followed this guide to create the following custom function which counts the number of cells of a certain colour in my spreadsheet:
Function ColorFunction(rColor As Range, rRange As Range, Optional ...

16
votes

1
answer

7k
views

### Reference Excel sheet with apostrophe in name

I have a large workbook with company names for each sheet. On some of the data sheets, I need to reference the other sheets. In doing so, I would start with an =sum( in the formula bar, then click on ...

16
votes

2
answers

4k
views

### In LibreOffice Calc is there a hotkey to edit the current cell?

I am wanting to edit a column of cells (for example, copy/pasted values have $0.005/hr and I want them to just be 0.005)
I can navigate with arrow keys, I can click to edit the existing value, but ...

16
votes

10
answers

206k
views

### How do I make Excel recognise TRUE or FALSE text as boolean values?

I have been using the INT formula to convert boolean values TRUE and FALSE into 1s and 0s. However, I have copied some data I have been given into my spreadsheet and Excel hasn't recognised the text ...

15
votes

5
answers

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