Have Excel duplicate labels
or numbers for you.
Example: You type Jan in the cell - press and hold the mouse button down over the AutoFill
handle and drag to the right 3 columns; you now have Jan, Feb, March and April; as your
headings.
If I have 100 in one cell and 101, one under the other, and I select the two cells. I can
press and hold on the AutoFill handle and drag to duplicate the pattern. If I drag 5 more
cell down, I will get 102, 103, 104, 105, and 106. Amazing isn't it?
Well try this the next time you want to fill in workdays (dates) on your spreadsheet,
because you are a time keeper maybe, try this.
Type in your date, example: 7/4/97
Press and hold the Right mouse down on the AutoFill handle and drag it over 10 cells. When
you let go you will get a menu that will give you the following selections.
Copy cells
Fill Series
Fill Formats
Fill Values
Fill Day
Fill weekdays
Fill months
Fill years
Linear Trend
Growth Trend
Series…
This of course gives you the ability to make a lot of different selection for your
AutoFill to do for you.
Would you like to add something more to the AutoFill? This is under the
Tools, Options… menu. When you click on the Custom Lists tab you will see the days of
the week and months of the year. This is how the AutoFill, fills in the months of
the year when we type Jan or we type Monday and then drag. So yes we can add in our own
list of labels.
Most of us when we are working with spreadsheets repeat the same labels from spreadsheet
to spreadsheet. You know like: Name - address - city - state - zip - phone; any type of
label. So the next time you start to type the same labels you have used before and you
know you are going to use them again do the following.
- Highlight your labels (after you have typed
them), Select Tools, click Options, click Custom Lists (tab)
down at the bottom of the dialog box click the import button. Click OK.
Now you can type the first word in your list, in a cell,
select the AutoFill handle, press and hold, then drag to fill in your labels.
Functions
for Duplicates (not AutoFill)
Counting Distinct Entries In A Range
The following formulas will return the number
of distinct items in the range B2:B11. Remember, all of these are
array formulas.
The following formula is the longest but most
flexible. It will properly count a list that contains a mix of
numbers, text strings, and blank cells.
=SUM(IF(FREQUENCY(IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""),
IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""))>0,1))
If your data does not have any blank entries,
you can use the simpler formula below.
=SUM(1/COUNTIF(B2:B11,B2:B11))
If your data has only numeric values or blank
cells (no string text entries), you can use the following formula:
=SUM(N(FREQUENCY(B2:B11,B2:B11)>0))
Let's look at how to
use SUMIF to directly apply the bonus.
Let's say that you have a column of names, total sales,
and standard
commissions. If the sales for the month exceed $10,000, you want to
add a 1 percent bonus to the standard commission.
For this example,
Person name's are in B2 to B5, Sales figures are in C2 to C5, Commission figures are in D2
to D5,
and we'll put the bonus values in E2 to E5.
Here's a formula for cell E2 that will add the bonus to the standard commission
provided the minimum sales condition is met:
=PRODUCT(SUMIF(C2,">=10000"),0.01)
As usual, you can select E2 and drag it through E5 to copy
the
formula to the remaining cells.
Excel 97/2000/2002/2003's comment notes are yellow fortunately, it's not a color
you're stuck with. To change the color of any comment note, follow
these steps:
1. Click the BORDER of the comment note.
2. On the Format toolbar, click the arrow next to the Fill Color
button.
3. Select a color from the palette.
If you like, you can have as many differently colored comments in the
same sheet as you want.
How to remove the extract character from a string of text.
Example: remove the parenthesis and dash from a
phone number. When you have the format (206) 345-5555 and you would like it to
look like:
2063455555
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2,"(",""),
")",""),
" ",""),
"-","")
Notice the 4 substitute functions all coming together.
Then look at the 4 parts that remove the characters.
You remove the ( from j2 the replace it with nothing.
You remove the ) from j2 the replace it with nothing.
You remove the "space" from j2 the replace it with nothing.
You remove the - from j2 the replace it with nothing.
Now you have all numbers and no characters in the middle.
How to add drop-down lists without VBA (macros) in your
workbook.
- Select a cell or a group of cells (ex:
A1:A12) go to the menu bar Data/Validation, In the "Allow" text
box select "List" .
- In the "Source" text box submit a list of
values separated by commas (John,Mark,Joe,Peter) or a range of
cells with names/values in it (ex: B1:B5). Click OK.
- Now when you select any of the cells A1 to
A12 a small arrow appears in the right part of the cell. Click on
it and you are offered a selection of the values that you have
submitted or of the values that you have entered in cells B1 to
B5.
| 
