All Questions
Tagged with microsoft-excel worksheet-function
7,327
questions
20
votes
6
answers
109k
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 ...
- 523
20
votes
10
answers
195k
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 ...
- 934
19
votes
3
answers
59k
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 ...
- 26.4k
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 ...
- 181
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 =...
- 275
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 ...
- 1,030
17
votes
3
answers
74k
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.
...
- 275
17
votes
9
answers
196k
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 ...
- 277
16
votes
10
answers
118k
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(...
- 327
16
votes
4
answers
171k
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?
- 179
16
votes
4
answers
134k
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?
- 469
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 ...
- 163
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 ...
- 24.4k
16
votes
10
answers
208k
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 ...
- 405
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 ...
- 956