Score of 16
Accepted
Prevent Excel to automatically "Wrap text" in a cell
Set Fixed Row Height(s)
In order to stop row heights from increasing to accommodate content containing ALTENTER line breaks you can set the row height(s) to a fixed value.
Row Height: Fixed Value ...
- reputation score 176
Score of 10
In Excel, how can I round or display numbers to 2 significant figures (the two leading non-zero digits of a decimal number)?
If rounding is required, you can also try this formula =ROUND(A1,INT(2-LOG(A1))).
- reputation score 4165
Score of 7
In Excel, how can I round or display numbers to 2 significant figures (the two leading non-zero digits of a decimal number)?
Try,
In B2 formula copied down :
=0+LEFT(A2,MIN(FIND({1,2,3,4,5,6,7,8,9},A2&1/17))+1)
Edit :
1] 1/17 =0.0588235294117647 which is called Pandigital number that contains digits from 0 to 9
2] A2&...
- reputation score 841
Score of 4
VBA to check if all cells in a range are empty before proceeding further
No loop needed:
Sub Check_and_execute()
If Application.WorksheetFunction.CountA(ThisWorkbook.Sheets("Sheet1").Range("A1:A10")) > 0 Then
MsgBox "Not all cells are empty."
Exit ...
- reputation score 23948
Score of 4
excel formula of sum with error and cells not in range
You can use the AGGREGATE function to SUM and ignore errors:
=AGGREGATE(9,6,c3,f3,h3,j3,n3)
- reputation score 9959
Score of 3
Excel: How do I reference an entire row except for a couple of cells?
Unfortunately Excel does not have the nifty google sheets feature to write a formula as A3:A (for rows) or C3:3 (for columns). While most of these answers listed will work, I would argue my below ...
- reputation score 175
Score of 3
Accepted
Two-way connection between multiple cells and across multiple worksheets
In the module "sheet 1" put this code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo eh
If Not Intersect(Target, Me.Range("F9,F12")) Is Nothing Then
Application....
- reputation score 2597
Score of 3
How do I turn my CSV formatting into a automatic "drag & drop" BAT file?
Ctrl+H
Find what: ^(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+\R\*FROM CLIP NAME:\h*(.+?)\h*\R.+
Replace with: $1,$2,$4,$3,$5,$6,$7,$8,"$9"
check Wrap around
check Regular ...
- reputation score 19684
Score of 3
Accepted
Libre Office - Single Column Pie Chart
You can do it by creating a pivot table, then using the data from the pivot table to create a chart. Here are the steps:
Select you data. Then on the menu, select Insert -> Pivot table. On the ...
- reputation score 21097
Score of 3
Why does Excel add leading apostrophes to some of my text cells?
Leading apostrophes force excel to treat the cell’s contents as a
text value.
Even if the cell contains a number or date, Excel will treat it as
text.
The apostrophe can only be seen in the Formula ...
- reputation score 503653
Score of 2
Cell reference in formula
It's a little inelegant, but use the following formula part where you have M:M:
INDIRECT(SUBSTITUTE(ADDRESS(1,10+ROW(),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,10+ROW()...
- reputation score 21
Score of 2
Increment number with letter in cell
If your B***** cell is at B1 cell,
Left: ="B"&TEXT(INT(RIGHT(B1,LEN(B1)-1))-1,"00000")
Right: ="B"&TEXT(INT(RIGHT(B1,LEN(B1)-1))+1,"00000")
If you have 6 digits after "B", use "000000".
- reputation score 483
Score of 2
Looking for the same value in two cells
What's neede is an exclusive or (XOR):
=IF(XOR(JANUARY!D6="Higgenbotham", JANUARY!E6="Higgenbotham"),JANUARY!B6,"")
A XOR is essentially two ANDs wrapped in an OR
=IF(OR(AND(JANUARY!D6="Higgenbotham"...
- reputation score 6059
Score of 2
Sum cells based on the value of other cell
You need SUMIF() formula.
In your specific case =SUMIF(B$2:B$9;D4;A$2:A$9) will give you the sum for Name1 then paste it down for Name2 and Name3
- reputation score 633
Score of 2
Accepted
convert Date to Julian day in Excel
You can use this formula: =DATE(A2,B2,C2)-DATE(A2,1,0)
- reputation score 3081
Score of 2
Accepted
Excel: How to create a list of values in one cell for curent and previous values?
You can do it with a slight change in your table, 2 different formulas, and an understanding of anchoring.
Table changes: you don't need column C and column E become really stock/usage.
So, if you are ...
- reputation score 1301
Score of 2
Accepted
VBA to check if all cells in a range are empty before proceeding further
Something like this?
Sub Check_and_execute
Dim Cell As Range
Dim CellsEmpty as boolean
CellsEmpty = True
For Each Cell In ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
If Cell.Value <> "" ...
- reputation score 6059
Score of 2
excel formula of sum with error and cells not in range
Is the formula you're looking for something like this?
= SUMIF (A1: A14; "<> # N / A")
= SUM (IF(ISERROR(A1: D2), "", A1: D2))
SUM on its own does not ignore nested subtotals,...
- reputation score 56
Score of 2
Excel Conditional Formatting - Using Formula as Part of a Cell Reference
You don't need to employ complex, indirect methods using ROW, INDIRECT and ADDRESS.
Simply use the following for row 4:
=AND(D4="",$A4<>"")
and set the Applies to range to ...
- reputation score 541
Score of 1
Add a text comment in a numeric cell with Excel
Seems to me your problem could be solved simply using the Comment tool already in Excel (Review > New Comment):
This has the benefit of being "hidden" (aside from the indicator in the ...
- reputation score 96
Score of 1
Accepted
Changing VALUE in a cell on entering specific text with VBA
You are creating a circular reference by putting a formula in D3. Do the lookup in vba and return just the value:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me....
- reputation score 23948
Score of 1
VBA to check if all cells in a range are empty before proceeding further
I'm not sure if this is faster, but it is shorter. You can check for a range being blank (including counting null strings as blank) with a single line of code:
rg.Cells.Count = WorksheetFunction....
- reputation score 9959
Score of 1
How to set value of height or width of cell precisely? - ms excel Office 365
Excel is not a precise desktop publishing tool. Units of measure for row height will be rounded up or down to the next possible measure.
You have more control if you set your unit of measure for the ...
- reputation score 23735
Score of 1
Match 2 sets of numbers in 2 cells
In excel, in the "formula" ribbon, in the "formula auditing" section, there is a tool called "Evaluate Formula. It will allow you to step through the formula in a cell and see how it is calculating.
...
- reputation score 1544
Score of 1
Sum cells based on the value of other cell
SUMPRODUCT function can help you get the result:
=SUMPRODUCT(($B$2:$B$9=E4)*($A$2:$A$9))
- reputation score 3081
Score of 1
Accepted
Not using a cell as variable, whilst inserting a (excel-)formula in several cells
Use $ in front of each part of the cell to keep it static.
For example: =$P$1/12
- reputation score 277
Score of 1
Accepted
How to step up cell reference in function by numeric values based on known number of rows
Adapting my answer to Conditional concatenate cell content across rows,
enter the following formulas:
F2 → =IF(AND(A1<>A2,A2<>""), G2, "")
G2 → =IF(A2<>A3, B2, B2 & ", " & G3)...
- reputation score 22601
Score of 1
How to step up cell reference in function by numeric values based on known number of rows
Use an extra column.. then hide it. ( :
F2 ----> =IF(A2="","",IF(A1=A2,"",G3&", "&B2))
G2 ----> =IF(A2="","",IF(A2=A3,A2&", "&G3,B2))
and drag downwards... please share if it ...
- reputation score 1293
Score of 1
How to move partial text from one cell to another
In Excel 2013:
Copy text (from PDF file).
Click in the spreadsheet in the upper left corner
of where you want the data to go.
Paste.
You may want to do “Paste” → ”Match Destination Formatting (M)”.
...
- reputation score 22601
Only top scored, non community-wiki answers of a minimum length are eligible