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:

23604 questions
327
votes
12 answers

How to create strings containing double quotes in Excel formulas?

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
  • 85,857
  • 67
  • 175
  • 234
322
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
  • 6,950
  • 10
  • 34
  • 57
233
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
  • 19,464
  • 21
  • 95
  • 172
227
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
  • 5,743
  • 7
  • 31
  • 48
197
votes
12 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
  • 19,464
  • 21
  • 95
  • 172
194
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
  • 1,943
  • 2
  • 12
  • 4
185
votes
14 answers

Error in finding last used cell in Excel with 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…
james
  • 3,838
  • 11
  • 37
  • 57
173
votes
15 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
  • 109,080
  • 38
  • 170
  • 208
136
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,363
  • 2
  • 9
  • 4
130
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,188
  • 14
  • 53
  • 76
128
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,316
  • 11
  • 36
  • 44
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,301
  • 7
  • 37
  • 62
87
votes
15 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,458
  • 3
  • 14
  • 15
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
  • 1,925
  • 7
  • 26
  • 31
75
votes
21 answers

Getting unique values in Excel by using formulas only

Do you know a way in Excel to "calculate" by formula a list of unique values ? E.g: a vertical range contains values "red", "blue", "red", "green", "blue", "black" and I want to have as result "red, "blue", "green", "black" + eventually 2 other…
Patrick Honorez
  • 23,092
  • 8
  • 78
  • 133
1
2 3
…
99 100