25
votes
How to delete MS Word table cell contents but not cells
Using the delete key instead of backspace should do it. On a PC, anyway.
If you're on a Mac, and therefore don't have a real delete key (the delete key on the Mac's keyboard is functionally equivalent ...
- 351
9
votes
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))).
- 3,692
9
votes
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 ...
- 91
7
votes
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&...
- 841
4
votes
Add drop down list / combo box into excel cell
Found this answer when looking to do the same thing myself, while experimenting discovered, there's a second option which means you don't need to use a defined cell range. Entries can be added ...
- 63
4
votes
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)
- 8,931
3
votes
Limit Excel cell entry to a certain number of digits
You can use the data validation (Ribbon > Data > Data Validation) to control it.
Allowing a 9 digit number only, is equivalent to allowing numbers between 100,000,000 and 999,999,999.
If you want to ...
- 1,020
3
votes
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 ...
- 175
3
votes
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 ...
- 18.3k
3
votes
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 ...
- 23.4k
3
votes
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....
- 2,487
2
votes
Hide cell from being computed in the formula
If you know that you might want to exclude certain items from the formula, you can prepare for this as follows:
in R1 through R10 enter 1
use the formula: =SUMPRODUCT((R1:R10)*(Q1:Q10))
Now if you ...
- 19.5k
2
votes
Hide cell from being computed in the formula
You can't omit these cells from being included in the formula without making changes in the formula.
You can't have your cake and eat it.
I suggest you change the formula to exclude the cell ...
- 23k
2
votes
Accepted
Excel merge cells, keep values, collapsing chart
Using data from Sheet1,
create a new sheet as Sheet2
For Row 1 in cell A1 insert
=Sheet1!A1 & CHAR(10) & Sheet1!A2 & CHAR(10) & Sheet1!A3
Using CHAR(10) is the equivalent of ...
- 1,523
2
votes
How to link cells from access to excel?
One way:
In Excel, go to the Data tab > Get External Data > From Other Sources > From Microsoft Query
From there, uncheck "Use the Query Wizard to create/edit queries" (this is because the query ...
- 31
2
votes
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()...
- 21
2
votes
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".
- 473
2
votes
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="...
- 5,556
2
votes
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
2
votes
Accepted
2
votes
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 <> "" ...
- 5,556
2
votes
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 ...
- 1,126
2
votes
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 ...
- 19.1k
2
votes
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 ...
- 56
2
votes
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 ...
- 541
1
vote
How to move partial text from one cell to another
Try this:
Copy from PDF
Paste in text file (I use Notepad)
Copy from text file
Paste in excel
Select "Use Text Import Wizard…" in the paste icon
Select Next 3 times and see if the results are correct
...
1
vote
Accepted
How do I create a ''droplet'' script in Win7 that runs a Notepad++ task on a CSV file
Windows 7 comes with PowerShell v2 which supports Regular Expressions with negative look behinds.
If I interpret your RegEx correctly you want to apply the two digits 99_ leading the 2nd field to ...
- 7,271
1
vote
Accepted
How do I copy specific selected data between cells in a CSV file in Excel or Notepad++?
With Notepad++, you can do:
Ctrl+H
Find what: ^([^,]*,(\d\d)_[^,]*,[^,]*)(?<!_\d\d),
Replace with: $1_$2,
check Wrap around
check Regular expression
Replace all
Explanation:
^ : ...
- 18.3k
1
vote
Accepted
Robust linking of cells Excel
As other Users have explained, the only way to have a "linked" cell that auto-updates its format when the source cell's format changes is to use VBA.
However, if the only thing you wish to do is ...
- 4,387
Only top scored, non community-wiki answers of a minimum length are eligible