Daily posts of Excel tips…and other stuff
Archive for the ‘For Normal People’ Category.
Hi there!
Many times it is a newsgroup post which triggers me to do a writeup on a specific subject. This time, a user asked how he could import a csv file every month, without having to go through the hassle of renaming the file and re-defining the import settings each time.
Opening the file in Excel [...]
Al sent me a formula to revise. Before:
=IF($B11<>0,IF(OR(ISNUMBER(FIND("-",J11))=TRUE,ISNUMBER(FIND(" ",J11))=TRUE,ISNUMBER(FIND("_",J11))=TRUE),
IF(ISNUMBER(FIND("-",J11))=TRUE,TRIM(SUBSTITUTE(J11,"-","")),IF(ISNUMBER(FIND(" ",J11))=TRUE,TRIM(SUBSTITUTE(J11,"
","")),IF(ISNUMBER(FIND("_",J11))=TRUE,TRIM(SUBSTITUTE(J11,"_","")),TRIM(J11)))),TRIM(J11)),"")
After:
=IF($B1<>0,TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J1,",","")," ",""),"-",""),"_","")))
What the formulas do and what they are supposed to do is not why I am sharing them with you. There are few techniques that can be demonstrated with these formulas that might help you become a better formula writer. This isn’t [...]
Hi all,
I decided I need a bit more content on my site for the average to intermediate Excel users:
Using controls on worksheets
Apart from input cells chained to cells with formulas, there are other options in Excel to make your spreadsheet model more interactive. You can add option buttons, check boxes and list boxes to your [...]
Excel has a Text format that can be applied to cells. When a cell is formatted as Text, anything you type in the cell will appears in the cell. That may seem obvious, but it applies to numbers and formulas too. For instance, if you type a formula in a cell formatted [...]
There are several ways to count cells in Excel. Here are a few.
1. Subtract rows
This is how our grandparents did it. If your data is in one column, you can compute in your head largest row - smallest row + 1. Watch out for those off by one errors.
2. Use QuickSum
On the [...]
Here are three ways to hide a column in Excel.
#1 Format Menu - select any cell in the column and choose Format - Column - Hide
#2 Right Click Menu - select the column header (which selects the whole column) and choose Hide from the right click menu
#3 Resize the Column to Zero - position your [...]
Recently I was asked how many customers made up 25% of my sales. I listed each customer and their sales for a specified period, then sorted descending on sales.
The formula in C2 is copied down for as far as the data goes. The secret is in the referencing.
=SUM($B$2:B2)/SUM($B$2:$B$51)
In the denominator, both B2 and [...]
