# Tagged Questions

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 (...
409k 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 ...
12k 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....
130k 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 ...
112k 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 ...
101k 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. ...
105k 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 ...
230k 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 ...
142k 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?
540k 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
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 ...
143k 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 ...
59k 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)?
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 &...
2k 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 ...
73k 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 ...
85k 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 ...
149k 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?
39k 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 ...
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 ...
53k 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, ...
44k 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 ...
74k 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 ...
31k 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).
53k 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 ...
40k 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 ...
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)
71k 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 ...
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 ...
56k 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 ...
90k views

### What is the difference between function and formula in Excel?

What is the difference between those two when speaking of Microsoft Excel?
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 ...
44k 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 ...
10k 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,...
54k 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 ...
43k 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(...
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 ...
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 =...
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 ...
69k 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 ...
54k 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 ...
35k 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 ...
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 ...
6k 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 ...
95k 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: ...
6k views

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 ...
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 ...
848 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 ...