Wonders of Excel: FILL HANDLE

As you probably know, Excel Fill Handle is located in the bottom right of any active cell or the bottom right cell of a selected range. It's a quite small black square visible in this snip:

Small it is, but it's very important to know how to utilize it, as it can be really helpful in eliminating lots of manual typing and saving time in working with data entry... and much more than this. It can be used for filling any range of cells with increments of any choice you want to select.

To utilize the Fill Handle, first you need to fill one or more cells with values (numbers or text, incl. dates and other custom lists). Hover your mouse pointer over the small square until it changes to a small black cross, then you have couple of choices: you can left-click, right-click or double-click (in some situations); click and drag the mouse in the direction you want to fill up the range of cells with series or custom lists that follow your pre-defined pattern.

Counting unique cell entries in a worksheet

Sometimes you may want to be sure that your large Excel dataset contains unique entries only, i. e. none of the entries appears more than once. How would you check it?

There are some array formulas and advanced filtering methods available for such occasion, but the most convenient way is probably using UDF (User defined function) I'm presenting here:

Function Count1time(cRng As Range) As Variant
'Counts each cell with a value in the selected range
'Excludes any repeats!
'example of usage: Count1time(A1:C100)
Dim cVal As Variant
Dim uniq As New Collection
Application.Volatile
On Error Resume Next
For Each cVal In cRng
    uniq.Add cVal, CStr(cVal)
Next
Count1time = "Found " & uniq.Count & " unique entries"
End Function

To use it - in your workbook - open the Visual Basic Editor (VBE) by pressing ALT+F11 shortcut, right-click within Project Explorer, select Insert > Module (if not inserted there yet). Copy and paste the function code in the Module window.

Switch back to your Excel worksheet and test the function on any selected set of data. If the function counting result is equal to the number of cell entries within the tested range, it confirms that all data in your set are de facto unique. If not - some values are entered more than once.