I have a very large excel spread sheet that has a column of time stamps. Does anyone know convert that over to a date? Is there a function I can use? I tried format cell date but that doesn&#39;t work. My file is 91,568 KB. If there is a simpler way to this that would be great. I&#39;m open to ideas.

Thank you in advance :)

P.S.
I don&#39;t know any programming languages

Converting time stamps in excel to dates

I use the `SUM(B1..B20)` formula to sum a column, but when I filter the data by an adjacent column, the sum doesn&#39;t update to reflect only those rows not filtered out.  How does one do this?

How does one sum only those rows in excel not filtered out?

Suppose I have: 

 1.  a value of 5 in `B1`
 2.  I want to pass the number (5) in `B1` as a row variable, which will be read in conjunction with column `A` into another cell (say `C1`) as &quot;=A(B1)&quot; i.e. &quot;=A5&quot;
   
How would I do this?

Pass row number as variable in excel sheet

It seem simple but I cannot find a way to define a range that goes up to the end of the column in an Excel formula.

For instance I can use this equation SUM(C:C) to sum all number found on the column C. However, given that the top of the page has titles and column headers, I would like to start my range at line 6. I thought SUM(C6:C) would do it but it does not work in Excel.

This is required so my SUM is always right no matter how many lines of data I add to my document in the future.

Thanks.

Sum up a column from a specific row down

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.

Check whether a cell contains a substring

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 like

    =IF(some_condition,EMPTY(),some_value)

I tried to do things such as

    =IF(some_condition,&quot;&quot;,some_value)

and

    =IF(some_condition,,some_value)

and assuming B1 is an empty cell

    =IF(some_condition,B1,some_value)

but none of these appear to be true empty cells, I&#39;m guessing because they are the result of a formula.  Is there any way to populate a cell if and only if some condition is met and otherwise keep the cell truly empty?

EDIT: as recommended, I tried to return NA(), but for my purposes this did not work either.  Is there a way to do this with VB?

EDIT: I am building a worksheet that pulls in data from other worksheets that is formatted to the very specific demands of an application that imports the data into a database.  I do not have access to change the implementation of this application, and it fails if the value is &quot;&quot; instead of actually empty.

Return empty cell from formula in Excel

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&#39;s last occurrence **in** a string.  `Search` and `find` both work left-to-right so I can&#39;t think how to apply without lengthy recursive algorithm.  And [this solution][1] now seems obsolete.


  [1]: http://www.mrexcel.com/archive/General/30895.html

Excel: last character/string match in a string

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 value changes, while A1 always references that cell, instead of going to A2, A3, etc.?

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

When I want to find the last used cell value, I use: 

    Dim LastRow As Long
    
    LastRow = Range(&quot;E4:E48&quot;).End(xlDown).Row
    
    Debug.Print LastRow

I&#39;m getting the wrong output when I put a single element into a cell. But when I put more than one value into the cell, the output is correct. 
What&#39;s the reason behind this?

Error in finding last used cell in Excel with 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 string?

Examples:
&lt;pre&gt;
  Are you classified as human? -&gt; human?
Negative, I am a meat popsicle -&gt; popsicle
                  Aziz! Light! -&gt; Light!&lt;/pre&gt;

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

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&#39;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 a pivot table that shows me this:

    ABC   1
    DEF   2

The simple pivot table that I create just gives me this (a count of how many rows):

    ABC   3
    DEF   4  

But I want the number of unique values instead.

What I&#39;m really trying to do is find out which values in the first column don&#39;t have the same value in the second column for all rows.  In other words, &quot;ABC&quot; is &quot;good&quot;, &quot;DEF&quot; is &quot;bad&quot;

I&#39;m sure there is an easier way to do it but thought I&#39;d give pivot table a try...

Simple Pivot Table to Count Unique Values

How do I remove leading or trailing spaces of all cells in an entire column?

The worksheet&#39;s conventional `Find and Replace` (aka &lt;kbd&gt;Ctrl&lt;/kbd&gt;+&lt;kbd&gt;H&lt;/kbd&gt;) dialog is not solving the problem.


Remove leading or trailing spaces in an entire column of data

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 it should be Text.

How can I do that in Excel?

Excel Date to String conversion

I&#39;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 prices over months.)

I can do it per cell and format-drag it, but I would like a general formula to apply to the whole worksheet.

Thanks!

Excel formula to reference &#39;CELL TO THE LEFT&#39;

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(&quot;contents&quot;,INDIRECT(ADDRESS(B1,B2)))

Sol 2

    =CELL(&quot;contents&quot;,OFFSET($A$1, B1-1,B2-1))

Is there no less verbose method? (like =CellValue(row,col) or whatever)?

Edit / Clarification:
I just want to use the excel worksheet formulas. No VBA. In short, I pretty much look for the equivalent of the VBA Cells() method as an excel Formula.

Get content of a cell given the row and column numbers

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 appear on a different sheet, within the same workbook, so I assume it will need to work with the `sheet2!A1` style format.

I have not had any luck with the Data/Filter menu options as this only seems to work on command. I need column B to update automatically whenever a new value is entered into column A. 

Ignore Duplicates and Create New List of Unique Values in Excel

Do you know a way in Excel to &quot;calculate&quot; by formula a list of unique values ? 
 
**E.g:** a vertical range contains values **`&quot;red&quot;`**, **`&quot;blue&quot;`**, **`&quot;red&quot;`**, **`&quot;green&quot;`**, **`&quot;blue&quot;`**, **`&quot;black&quot;`**  
and I want to have as result **`&quot;red`**, **`&quot;blue&quot;`**, **`&quot;green&quot;`**, **`&quot;black&quot;`** + eventually 2 other blank cells.  

I already found a way to get a calculated sorted list using SMALL or LARGE combined with INDEX, but I&#39;d like to have this calculated sort as well, WITHOUT USING VBA.

Getting unique values in Excel by using formulas only

I&#39;d like to write a formula such that if cell A1 displays `#VALUE!`, say `TRUE` in cell B1.

Here&#39;s my formula in cell B1:

    =IF(A1=&quot;#VALUE!&quot;, &quot;TRUE&quot;, &quot;FALSE&quot;)

I get `FALSE` when A1 does not say `#VALUE!` so that part is fine.  But, when it does say `#VALUE!`, I get a `#VALUE!` error in cell B1, when I want it to say `TRUE`.  How do I do this?

How do I recognize &quot;#VALUE!&quot; in Excel spreadsheets?

I have a set of data that shown below on excel.

    R/V(208,0,32)	YR/V(255,156,0)	        Y/V(255,217,0)
    R/S(184,28,16)	YR/S(216,128,0)	        Y/S(209,171,0)
    R/B(255,88,80)	YR/B(255,168,40)	Y/B(255,216,40)

And I want to separate the data in each cell look like this.

    R/V 208 0 32
    R/S 184 28 16
    R/B 255 88 80

what is the function in excel that I can use for this case.
Thank you in advance.



Substring in excel

I need help on my Excel sheet. How can I declare the following `IF` condition properly?


    if A1 = &quot;n/a&quot; then C1 = B1
    else if A1 != &quot;n/a&quot; or has value(int) then C1 = A1*B1

Using &quot;If cell contains #N/A&quot; as a formula condition.

How can I construct the following string in an Excel formula:

&gt;Maurice &quot;The Rocket&quot; Richard

If I&#39;m using single quotes, it&#39;s trivial = `&quot;Maurice &#39;The Rocket&#39; Richard&quot;` but what about double quotes?

How to create strings containing double quotes in Excel formulas?

Is it possible to declare variables in Excel cells and use them as parameters for formulas in other cells? 

For example I would declare `var1=10` in one of the cells.
In another cell I would use `var1` for calculation like: `=var1*20`.


Declaring variables in Excel Cells

Consider the following worksheet:

         A       B        C        D
    1 COMPANY  XVALUE   YVALUE   GROUP
    2 Apple     45       35       red
    3 Xerox     45       38       red
    4 KMart     63       50       orange
    5 Exxon     53       59       green

I have used the scatterplot function in Excel to create the following chart:

![enter image description here][1]


  [1]: http://i.stack.imgur.com/pHAEp.png

However, each point in the chart has an additional property: `GROUP`. There are four groups: `red`, `orange`, `black` and `green`. I would like to color each dot accordingly, so that I could perhaps see a pattern (group `green`being almost always on the left side of the chart, for instance). Because my list is 500 rows long, I cannot do this manually. How can I do this automatically?

How can I color dots in a xy scatterplot according to column value?

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 &lt;kbd&gt;CTRL&lt;/kbd&gt;+&lt;kbd&gt;SPACE&lt;/kbd&gt;, that will apply a formula to the entire column, or to a selected part of the column?

All Excel Formula Solutions on Gang of Coders

Total of 24 Excel Formula Solutions