Stack Overflow
Stack Overflow

Questions tagged [excel-formula]

This tag is for Microsoft Excel questions where the question or answers are specifically about the structure, syntax, or output of an Excel formula.

A formula is a way for you to make calculations based upon data written in the spreadsheet.

The best part about formulas is that they are linked to the data in such a way that if the data changes, the formula gives an updated result, reflecting the change immediately (as long as calculation mode is set to "Automatic").

In other words, formulas are mathematical equations which are used to CALCULATE a value. They begin with an equal sign (=).

There is a list of the functions/formulas available within Excel:

  • In Excel 2003, go to Insert » Function, or press Shift+F3
  • In Excel 2007, 2010, 2013 or 2016, go to Formulas Tab » Insert Function

The tag (and the related tag) are used when asking questions about the use of specific formulas or worksheet functions in Excel.

Links:

30321 questions
352
votes
13 answers

How to create a string or formula containing double quotes in Excel?

How can I construct the following string in an Excel formula: Maurice "The Rocket" Richard If I'm using single quotes, it's trivial: ="Maurice 'The Rocket' Richard" but what about double quotes?
Allain Lalonde
  • 91,574
  • 70
  • 187
  • 238
320
votes
3 answers

Shortcut to Apply a Formula to an Entire Column in Excel

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is there a shortcut, similar to…
John Shedletsky
  • 7,110
  • 12
  • 38
  • 63
262
votes
20 answers

Return empty cell from formula in Excel

I need to return an empty cell from an Excel formula, but it appears that Excel treats an empty string or a reference to an empty cell differently than a true empty cell. So essentially I need something…
Bryan Ward
  • 6,443
  • 8
  • 37
  • 48
259
votes
11 answers

Check whether a cell contains a substring

Is there an in-built function to check if a cell contains a given character/substring? It would mean you can apply textual functions like Left/Right/Mid on a conditional basis without throwing errors when delimiting characters are absent.
geotheory
  • 22,624
  • 29
  • 119
  • 196
222
votes
14 answers

Excel: last character/string match in a string

Is there an efficient way to identify the last character/string match in a string using base functions? I.e. not the last character/string of the string, but the position of a character/string's last occurrence in a string. Search and find…
geotheory
  • 22,624
  • 29
  • 119
  • 196
201
votes
8 answers

How to keep one variable constant with other one changing with row in excel

Lets say I have one cell A1, which I want to keep constant in a calculation. For example, I want to calculate a value like this: =(B1+4)/(A1) How do I make it so that if I drag that cell to make a calculation across cells in many rows, only the B1…
dude
  • 2,013
  • 2
  • 13
  • 4
201
votes
14 answers

Find last used cell in Excel VBA

When I want to find the last used cell value, I use: Dim LastRow As Long LastRow = Range("E4:E48").End(xlDown).Row Debug.Print LastRow I'm getting the wrong output when I put a single element into a cell. But when I put more than one value into…
Mushahid Hussain
  • 4,052
  • 11
  • 43
  • 62
189
votes
16 answers

How can I perform a reverse string search in Excel without using VBA?

I have an Excel spreadsheet containing a list of strings. Each string is made up of several words, but the number of words in each string is different. Using built in Excel functions (no VBA), is there a way to isolate the last word in each…
e.James
  • 116,942
  • 41
  • 177
  • 214
139
votes
16 answers

Simple Pivot Table to Count Unique Values

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on. For instance, I have this: ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456 What I want is…
user1586422
  • 1,393
  • 2
  • 9
  • 4
133
votes
6 answers

Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.
venkat
  • 5,648
  • 16
  • 58
  • 83
130
votes
10 answers

Excel Date to String conversion

In a cell in Excel sheet I have a Date value like: 01/01/2010 14:30:00 I want to convert that Date to Text and also want the Text to look exactly like Date. So a Date value of 01/01/2010 14:30:00 should look like 01/01/2010 14:30:00 but internally…
Chaitanya MSV
  • 6,706
  • 12
  • 40
  • 46
105
votes
14 answers

Excel formula to reference 'CELL TO THE LEFT'

I'm trying to do conditional formatting so that the cell color will change if the value is different from the value in the cell left of it (each column is a month, in each row are the expenses on certain object. I want to monitor easily changes in…
mik
  • 1,668
  • 4
  • 15
  • 15
96
votes
3 answers

Get content of a cell given the row and column numbers

I want to get the content of a cell given its row and column number. The row and column number are stored in cells (here B1,B2). I know the following solutions work, but they feel a bit hacky. Sol 1 =CELL("contents",INDIRECT(ADDRESS(B1,B2))) Sol…
Philipp
  • 4,659
  • 9
  • 48
  • 69
78
votes
3 answers

How do I recognize "#VALUE!" in Excel spreadsheets?

I'd like to write a formula such that if cell A1 displays #VALUE!, say TRUE in cell B1. Here's my formula in cell B1: =IF(A1="#VALUE!", "TRUE", "FALSE") I get FALSE when A1 does not say #VALUE! so that part is fine. But, when it does say #VALUE!,…
MrPatterns
  • 4,184
  • 27
  • 65
  • 85
77
votes
14 answers

Ignore Duplicates and Create New List of Unique Values in Excel

I have a column of values that often appear as duplicates. I need to create a new column, of unique values based on the first column, as follows: Column A Column B a a a b b c c c This Column B will actually need to…
tob88
  • 2,151
  • 8
  • 30
  • 33
1
2 3
…
99 100