Right-click in drag

You obviously know that you copy / paste a cell by clicking on its lower right corner, and dragging it down or across while holding the left mouse button down.
For some cell values, e.g. dates or numbers, you can even switch between an incremented copy and a straight copy by holding or not the Ctrl key. By default, dates will increment and numbers won’t, and holding Ctrl will switch the behavior.
NOW, if you perform this dragging with the right-click instead of the left-click, you’re getting a lot more options (see screenshot below), like filling in weekdays, or months. This is quite the time saver.

Options stemming from a right-click drag

Try it, you might like it.

Thomas

PS: Try selecting multiple cells before dragging and see how that affects the results you’re getting.

Find the text cells masquerading as numbers.

From time to time, an import gone awry will leave you with values that look like numbers but are actually text. They won’t total, they won’t compute in formulas and won’t be picked up by a pivot table.

If you have a whole column of them, usually a Data \ Text to columns will take care of the issue, but sometimes that’s not your best tool.

A quick way to find out which cells are not really numbers (and a quick way to find lots of other types of cell) is to use the Goto…Special function. Select the range containing the culprits, press F5 and you have the Go to menu, press the Special button and you will get all the Special Cells option. Select the appropriate one, in this case, Constants \ Text and voilà, the text cells are selected. You can now manually address the issues.

Try it, you might like it.

Thomas

Quick Balance Sheet Account Matching

Hi all,

If your accounting package doesn’t let you apply g/l entries to other g/l entries, or if you just haven’t been doing it, reconciliation of balance sheet can often be an arduous task, with dozens, hundreds or thousands of line to match before you can find out your outstanding items.

The following formulas will help you make a first pass of reconciliation before you dig in further. The goal is to take care of all the one-for-one reversals. To find them, we check the amount column, and for each line, we check if the sum of amounts matching that line’s amount, either with a negative or positive sign, is equal to zero.

First, the two step method, using table references rather than range references. If the [ ] and [@..] confuse you, check out this tutorial on office.microsoft.com.

  • Create a column with the absolute value of the line’s amount, using the ABS() function: =ABS([@Amount])
  • Create a column with a formula summing the Amount column if the Absolute values match: =SUMIF([ABS],[@ABS],[Amount])=0

Second the one step method, using sumproduct:

  • =SUMPRODUCT((ABS([Amount])=ABS([@Amount]))*[Amount])=0

Both of these methods will return a list of TRUE / FALSE values: TRUE if the amount is matched, FALSE if it isn’t. You can filter out the TRUE value and focus your attention on the FALSE. Your work isn’t done yet, but it got started faster. Check out the example file (quick balance sheet matching) to see it in action.

Try it, you might like it,

Thomas

Quick True / False formula

Want a True / False result on your formula (which could be pretty helpful on a quick autofilter analysis)?
The long way?

=IF(MyFormula=0, TRUE, FALSE)

The quick way?

=MyFormula=0

You can use operators directly without an IF statement, and mix and match just like you would in the first part of an IF statement, e.g.
=AND(SUM(A1:A5)>5,SUM(B1:B5)<12)

Try it, you might like it.

Thomas

The Match Columns macro – Absolutely useless until you need it.

You have two ranges with headers. Some of the headers match, some don’t. In addition, they’re not in the same order.

Select the first range, hold Ctrl before selection the second range and run the below code. All the columns of the second range are now matched to the columns of the first, and all unmatched columns of the second range are now at the end. Tada!

Try it, you might like it,
Thomas

Sub MatchColumns()
‘Matches two ranges by inserting columns in both ranges so each value is on the same column

‘you first need to
‘ select the two data blocks you want to split and match

Dim rg1 As range, rg2 As range
Dim firstMatch As Boolean
Dim i As Long, j As Long, foundCol As Long

application.ScreenUpdating = False

If selection.Areas.Count 2 Then
MsgBox “Select two areas”
Exit Sub
End If

Set rg1 = selection.Areas(1)
Set rg2 = selection.Areas(2)

‘gets the number of unique values in the first rows of range 1 and 2, to be able to run the loop all the way
Dim cUnique As New Collection

On Error Resume Next

With rg1
For i = 1 To .Rows(1).Cells.Count
cUnique.Add .Cells(1, i), CStr(.Cells(1, i))
Next
End With

With rg2
For i = 1 To .Rows(1).Cells.Count
cUnique.Add .Cells(1, i), CStr(.Cells(1, i))
Next
End With

On Error GoTo 0

‘boolean needed to be able to resize range 2 if required
firstMatch = True

For i = 1 To cUnique.Count
If Len(rg1.Cells(1, i)) = 0 Or rg2.Cells(1, i) = rg1.Cells(1, i) Then
firstMatch = False
GoTo nxt_i:
End If

On Error Resume Next
foundCol = rg2.Rows(1).Find(What:=rg1.Cells(1, i), LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False).Column

If Err 0 Then
Err.Clear
rg2.Offset(, i – 1).Resize(, 1).Insert Shift:=xlToRight
If firstMatch Then Set rg2 = rg2.Offset(, -1).Resize(, rg2.Columns.Count + 1)
Else
rg2.Columns(foundCol – rg2.Column + 1).Cut
rg2.Columns(i + rg2.Column – 1).Insert Shift:=xlToRight
firstMatch = False
End If

nxt_i:

Next

application.ScreenUpdating = True
End Sub

Put borders around separate values using Conditional formatting

Just a quick one to show you that conditional formatting is not only about coloring cells. Assuming you have a table with distinct values in column A, e.g. a customer code, a blend numbers, or anything else (see screenshot 1). You’d like to have borders between each category, just to make it neat. Just select the whole table, add a new rule for conditional formatting (see screenshot 2), use formula =$A2<>$A1 ($ are important), set a top border, validate. You’re done, see screenshot 3.

Screenshot 1, before:

Image

Screenshot 2, Conditional formatting with formula:

Image

Screenshot 3, after:

Image

Try it, you might like it.

Thomas

Special Cells – They’re special (at least that’s what Barney said)!

Special cells will make your interaction with Excel much more enjoyable, and they can be used both in worksheets and VBA, making them a wise investment.
1. From the worksheet:

The F5 key will take you to the Go To Box. There, the Special button awaits you. Once you press it, you’re just one step away from the magic:

Choose any of the options, press OK and two things can happen. If you’ve selected a range, the Special Cells will be selected within that range. If you’ve just selected one cell, the Special Cells on the whole worksheet will be selected.

The possibilities are endless. One example: if you have a table with row headers that you’d like to copy down into the empty rows below:

  • Select the range
  • F5 \ Special \ Blanks \ OK  (or the shortcut version F5 \ Alt+S \ k \ Enter
  • =B2 (or the cell right above your first selected blank
  • Ctrl + Enter
  • All the cells are now updated.
  • You can now select the whole column and do a copy \ paste values to get rid of the formulas.

 

There are of course many more uses for Special Cells, but this will get you going.

 

2. in VBA

You can access the Special Cells through the SpecialCells member of the Range Class, as in:

Range(“A1:L200″).SpecialCells(xlCellTypeBlanks).value=”Not Blank Anymore”

to fill in the blank cells of a range, or, much more useful, to copy only the visible cells of a range that’s been filtered (by the way, if you can avoid loops and use autofilter instead, your macros will thank you):

Sheets(“Sheet1”).Range(“A1:L200”).SpecialCells(xlCellTypeVisible).copy Sheets(“Sheet2”).cells(1,1)

 

You can play around with the Macro Recorder to get the different options, but here’s the list from the MSDN site:

  • XlCellType constants
  • xlCellTypeAllFormatConditions. Cells of any format
  • xlCellTypeAllValidation. Cells having validation criteria
  • xlCellTypeBlanks. Empty cells
  • xlCellTypeComments. Cells containing notes
  • xlCellTypeConstants. Cells containing constants
  • xlCellTypeFormulas. Cells containing formulas
  • xlCellTypeLastCell. The last cell in the used range
  • xlCellTypeSameFormatConditions. Cells having the same format
  • xlCellTypeSameValidation. Cells having the same validation criteria
  • xlCellTypeVisible. All visible cells
  • XlSpecialCellsValue constants:

– xlErrors
– xlLogical
– xlNumbers
– xlTextValues

 

Try it, you might like it.

Thomas