Improving readability of worksheet data in Excel

It can be a big help if your data in Excel is easy to read (avoiding errors in calculations and or data analysis). I know there are more ways than one to achieve that goal, in this article I’ll show you some code that handles the solution without using conditional formatting. It’s a rough and simple solution…

To address the problem visually look at the differences between the images below…

The original layout above.

The new and improved layout below 😉

 

For those of you that are not familiar with VBA / macro’s use the steps below…

  • If you use the latest version of Excel make sure you’ve got the “Developer” tab in Excel
  • Next press the button “Record Macro”
  • Then press the button “Stop Recording”
  • Press ALT + F8
  • Choose “Edit”
  • Copy and paste the code below (or download a text file containing the code here…)
  • Save your Excel !!!
  • Press ALT + F8 and Run the Macro

 

Sub ImproveReadability()

‘ ImproveReadability Macro

‘ Developer: Winko Erades van den Berg
‘ E-mail : winko at winko-erades.nl
‘ Developed: 10-10-2011
‘ Modified: 10-10-2011
‘ Version: 1.0

‘ Description: Improve the readability of your Excel worksheet data by using colors’Declare variables
Dim iRow As Long
Dim iCol As Long
Dim HeaderRow as Integer’Constant values
HeaderRow = InputBox(“Specify the row that contains the header information:”) ‘Create Input Box to ask the user where the header starts
iCol = ActiveSheet.UsedRange.Columns.Count ‘Determine how many columns are in use

‘Change de header lay-out
ActiveSheet.Range(ActiveSheet.Cells(HeaderRow, 1), ActiveSheet.Cells(HeaderRow, iCol)).Select
With Selection.Interior ‘Fill Color
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0
.PatternTintAndShade = 0
End With

With Selection.Font ‘Font Color
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True ‘Font Bold

‘Start highlighting alternate rows
For iRow = HeaderRow + 2 To ActiveSheet.UsedRange.Rows.Count Step 2
ActiveSheet.Range(ActiveSheet.Cells(iRow, 1), ActiveSheet.Cells(iRow, iCol)).Select
With Selection.Interior ‘Fill Color
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.75
.PatternTintAndShade = 0
End With
Next iRow

End Sub

 

Suggestions for improving this article are welcome, please let me know and drop me a line.

Prevent RSI and automate mouse clicks by using excel

Sometimes one has to do a lot of mouse clicking… By repetitive clicking and small movements of your hands and other joints you could end up with some nasty RSI (Repetitive Strain Injury) and that ain’t worth it!!! So beat RSI (or your enemy while gaming) by using VBA and Excel to automate some excessive mouse clicks!

As mentioned in the intro, this article is about automating mouse clicks in Excel. Therefor I wrote a macro in Excel as you can see in the image below…

Look at the three big buttons that enable you to Left Click, Middle Click or Right Click.
Also noteworthy are the adjustable “Start Delay” and “Click Interval”.

 

For those of you that are not familiar with VBA / macro’s use the steps below…

  • If you use the latest version of Excel make sure you’ve got the “Developer” tab in Excel
  • Next press the button “Record Macro”
  • Then press the button “Stop Recording”
  • Press ALT + F8
  • Choose “Edit”
  • Copy and paste the code below (or download a text file containing the code here…)
  • Save your Excel !!!
  • Press ALT + F8 and Run the Macro

Download the Excelsheet (as can be seen above) here

Private Declare Sub mouse_event Lib “user32” (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Const MOUSEEVENTF_LEFTDOWN = &H2
Const MOUSEEVENTF_LEFTUP = &H4
Const MOUSEEVENTF_MIDDLEDOWN = &H20
Const MOUSEEVENTF_MIDDLEUP = &H40
Const MOUSEEVENTF_MOVE = &H1
Const MOUSEEVENTF_ABSOLUTE = &H8000
Const MOUSEEVENTF_RIGHTDOWN = &H8
Const MOUSEEVENTF_RIGHTUP = &H10

 

Sub AutomaticMouseClickLeft()
‘ Automatic Mouse Click Left Macro

‘ Developer: Winko Erades van den Berg
‘ E-mail : winko@winko-erades.nl
‘ Developed: 27-03-2012
‘ Modified: 28-03-2012
‘ Version: 1.0

‘ Description: Automatic Mouse Click Left

AantalKlikken = InputBox(“Enter the number of Clicks to be executed :”, “KlikkerDieKlikBox”) ‘Ask the number of Clicks to be executedApplication.Wait (Now + Sheets(“Sheet1”).Cells(5, 2)) ‘Get the delay from the sheet before starting to click – hh:mm:ss
For Klik = 1 To AantalKlikken ‘Start counting the Clicks
‘Do mouse click on cursor position
mouse_event MOUSEEVENTF_LEFTDOWN Or MOUSEEVENTF_LEFTUP, 0&, 0&, cButt, dwEI
DoEvents
Application.Wait (Now + Sheets(“Sheet1”).Cells(5, 3)) ‘Delay interval between Clicks – hh:mm:ss
Next Klik ‘Next Click
‘End
End Sub

 

Sub AutomaticMouseMiddleClick()
‘ Automatic Mouse Middle Click Macro

‘ Developer: Winko Erades van den Berg
‘ E-mail : winko@winko-erades.nl
‘ Developed: 27-03-2012
‘ Modified: 28-03-2012
‘ Version: 1.0

‘ Description: Automatic Mouse Click Middle

AantalKlikken = InputBox(“Enter the number of Clicks to be executed :”, “KlikkerDieKlikBox”) ‘Ask the number of Clicks to be executed
Application.Wait (Now + Sheets(“Sheet1”).Cells(5, 2)) ‘Get the delay from the sheet before starting to click – hh:mm:ss
For Klik = 1 To AantalKlikken ‘Start counting the Clicks
‘Do mouse click on cursor position
mouse_event MOUSEEVENTF_MIDDLEDOWN Or MOUSEEVENTF_MIDDLEUP, 0&, 0&, cButt, dwEI
DoEvents
Application.Wait (Now + Sheets(“Sheet1”).Cells(5, 3)) ‘Delay interval between Clicks – hh:mm:ss
Next Klik ‘Next Click
‘End
End Sub

 

 

Sub AutomaticMouseClickRight()
‘ Automatic Mouse Click Right Macro

‘ Developer: Winko Erades van den Berg
‘ E-mail : winko@winko-erades.nl
‘ Developed: 27-03-2012
‘ Modified: 28-03-2012
‘ Version: 1.0

‘ Description: Automatic Mouse Click Right

AantalKlikken = InputBox(“Enter the number of Clicks to be executed :”, “KlikkerDieKlikBox”) ‘Ask the number of Clicks to be executed
Application.Wait (Now + Sheets(“Sheet1”).Cells(5, 2)) ‘Get the delay from the sheet before starting to click – hh:mm:ss
For Klik = 1 To AantalKlikken ‘Start counting the Clicks
‘Do mouse click on cursor position
mouse_event MOUSEEVENTF_RIGHTDOWN Or MOUSEEVENTF_RIGHTUP, 0&, 0&, cButt, dwEI
DoEvents
Application.Wait (Now + Sheets(“Sheet1”).Cells(5, 3)) ‘Delay interval between Clicks – hh:mm:ss
Next Klik ‘Next Click
‘End
End Sub

 

Suggestions for improving this article are welcome, please let me know and drop me a line.

Cleaning phone numbers in Excel or OO Spreadsheet

Personally I’ve been using Excel for storing phone numbers for almost twenty years now. Every now and then I don’t recognize the phone number so I need to search in my Excel sheet looking up who called me. After ten years and 500 phone numbers plus, the information stored in the Excel sheet is ‘poluted’ with hyphens and spaces. So in this article I’ll try to explain how to clean up messy telephone numbers by removing spaces and separators in Excel?

The following example uses the nesting of formulas by removing the hyphens first, followed removing the spaces.

=SUBSTITUTE(SUBSTITUTE(A2,”-“,””),” “,””)

 

Suggestions for improving this article are welcome, please let me know and drop me a line.

 

Using Excel’s conditional formatting to colorize the weekends

In this period of the year most of us are starting to make plans for next year, Excel is an incedibly powerfull tool that can help you visualize your plans. Using Excel’s conditional formatting you can colorize the weekends and make them standout more than the other days of the week or vice versa. This article is an How to colorize the weekends in Excel using conditional formatting.

To colorize the weekends in Excel using conditional formatting, based upon the date in column B, you can use the following formula:

=IF(OR(WEEKDAY($B2)=1;WEEKDAY($B2)=7);1;0)

 

To create a rule using conditional formatting:

  • Select the cells that you want to apply the conditional formatting to.
  • Click “Conditional Formatting”.
  • Choose “New Rule”.
  • In the “New Formatting Rule” dialog box, choose “Use a formula”.
  • Under “Format values”, type the formula: =IF(OR(WEEKDAY($B2)=1;WEEKDAY($B2)=7);1;0)
  • The formula uses the dates in column B (You can select your own column with dates, by replacing the $B2 part in the formula with the column letter of your choice).
  • Click “Format”.
  • In the “Color” box, select your favourite color.
  • Click “OK” until all dialog boxes are closed.

 

Suggestions for improving this article are welcome, please let me know and drop me a line.

How to split numbers and text from a string in Excel

I received an e-mail from Marc, in which he asked me whether it is posible to split numbers and text in Excel. The answer of the solution is based upon determining the first position of a number in a string, you can do that by using the following formula.

=MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))

 

The next trick is to determine the text within a string:
A101 will become A when using the following formula

=LEFT(A1;(MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))-1))

101A will become A when using the following formula

=RIGHT(A2;(MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9}))))

 

 

To determine the numbers within a string:
A101 will become 101 when using the following formula

=RIGHT(A1;LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))+1)

101A will become 101 when using the following formula

=LEFT(A2;LEN(A2)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9})))

 

 

You might want to convert the text string to numbers by using the following formula.

=VALUE()

A101 will become numeric value 101 when using the following formula

=VALUE(RIGHT(A1;LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))+1))

101A will become numeric value 101 when using the following formula

=VALUE(LEFT(A2;LEN(A2)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9}))))

 

Suggestions for improving this article are welcome, please let me know and drop me a line.

Rearrange columns in Excel using VBA

Did you ever need to rearrange or reorganize columns across multiple sheets in a certain order based on column headers? In this article I’ll try to explain how to rearrange columns in Excel based on column header information by using Visual Basic for Applications (VBA) .

As mentioned in the intro, this article is about rearranging columns in Excel using column header information.

To make it more visible see the images below…

In this example the headers are in alpabetical order:
Address, City, Country, Date of Birth, First Name, Last Name, Middle Name, Phone Number, Postal (ZIP) Code, State.

 

And let’s say you want to change the column order to:
First Name,Last Name, Middle Name, Date of Birth, Phone Number,Address, City, State, Postal (ZIP) Code, Country (see the image below)

 

For those of you that are not familiar with VBA / macro’s use the steps below…

  • First make sure you’ve got the “Developer” tab in Excel
  • Next press the button “Record Macro”
  • Then press the button “Stop Recording”
  • Press ALT + F8
  • Choose “Edit”
  • Copy and paste the code below (or download a text file containing the code here…)
  • You might want to use your own headers and ordering, so change the code there 😉
  • Save your Excel !!!
  • Press ALT + F8 and Run the Macro

 

Sub MoveColumns()
‘ MoveColumns Macro

‘ Developer: Winko Erades van den Berg
‘ E-mail : winko at winko-erades.nl
‘ Developed: 03-10-2011
‘ Modified: 03-10-2011
‘ Version: 1.0

‘ Description: Rearrange columns in Excel based on column headerDim iRow As Long
Dim iCol As Long’Constant values
data_sheet1 = InputBox(“Specify the name of the Sheet that needs to be reorganised:”) ‘Create Input Box to ask the user which sheet needs to be reorganised
target_sheet = “Final Report” ‘Specify the sheet to store the results
iRow = Sheets(data_sheet1).UsedRange.Rows.Count ‘Determine how many rows are in use’Create a new sheet to store the results
Worksheets.Add.Name = “Final Report”

‘Start organizing columns
For iCol = 1 To Sheets(data_sheet1).UsedRange.Columns.Count

‘Sets the TargetCol to zero in order to prevent overwriting existing targetcolumns
TargetCol = 0

‘Read the header of the original sheet to determine the column order
If Sheets(data_sheet1).Cells(1, iCol).Value = “First Name” Then TargetCol = 1
If Sheets(data_sheet1).Cells(1, iCol).Value = “Middle Name” Then TargetCol = 2
If Sheets(data_sheet1).Cells(1, iCol).Value = “Last Name” Then TargetCol = 3
If Sheets(data_sheet1).Cells(1, iCol).Value = “Date of Birth” Then TargetCol = 4
If Sheets(data_sheet1).Cells(1, iCol).Value = “Phone Number” Then TargetCol = 5
If Sheets(data_sheet1).Cells(1, iCol).Value = “Address” Then TargetCol = 6
If Sheets(data_sheet1).Cells(1, iCol).Value = “City” Then TargetCol = 7
If Sheets(data_sheet1).Cells(1, iCol).Value = “State” Then TargetCol = 8
If Sheets(data_sheet1).Cells(1, iCol).Value = “Postal (ZIP) Code” Then TargetCol = 9
If Sheets(data_sheet1).Cells(1, iCol).Value = “Country” Then TargetCol = 10

‘If a TargetColumn was determined (based upon the header information) then copy the column to the right spot
If TargetCol <> 0 Then
‘Select the column and copy it
Sheets(data_sheet1).Range(Sheets(data_sheet1).Cells(1, iCol), Sheets(data_sheet1).Cells(iRow, iCol)).Copy Destination:=Sheets(target_sheet).Cells(1, TargetCol)
End If

Next iCol ‘Move to the next column until all columns are read

End Sub

 

Additional information 1

Someone sent me an alternative solution for reorganizing columns in Excel. The script makes use of the array function in Excel. It does a really nice job but beware, the  code handles your data in a way that it does keep your original data structure.

Sub Reorganize_columns()
‘ Reorganize Columns Macro

‘ Developer: If you want to know, please contact Winko Erades van den Berg
‘ E-mail : winko at winko-erades.nl
‘ Developed: 11-11-2013
‘ Modified: 11-11-2013
‘ Version: 1.0

‘ Description: Reorganize columns in Excel based on column headerDim v As Variant, x As Variant, findfield As Variant
Dim oCell As Range
Dim iNum As Long
v = Array(“First Name”, “Middle Name”, “Last Name”, “Date of Birth”, “Phone Number”, “Address”, “City”, “State”, “Postal (ZIP) Code”, “Country”)
For x = LBound(v) To UBound(v)
findfield = v(x)
iNum = iNum + 1
Set oCell = ActiveSheet.Rows(1).Find(What:=findfield, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)If Not oCell.Column = iNum Then
Columns(oCell.Column).Cut
Columns(iNum).Insert Shift:=xlToRight
End If
Next x
End Sub

 

Additional information 2

Dennis Klenner from D&R Design Ltd wanted you to know “Header names are case sensitive”! Thank you for the remark Dennis 🙂

 

Suggestions for improving this article are welcome, please let me know and drop me a line .

 

 

Convert MAC address in MS Excel or OO Spreadsheet

As I’m currently working a lot with Excel, I get a lot of questions like “How do I do this?” or “How do I do that?”. Yesterday someone asked me how to convert a data string to a MAC address. The standard format for printing MAC addresses in human-friendly form is six groups of two hexadecimal digits, separated by hyphens (-) or colons (:), in transmission order, e.g. 01-23-45-67-89-ab, 01:23:45:67:89:ab. In this article I’ll give you examples of how to add, remove or change the separator for MAC addresses in MS Excel or OO Spreadsheet.

 

Adding a seperator (change 0123456789ab into 01-23-45-67-89-ab)

=CONCATENATE(MID(A2;1;2);”-“;MID(A2;3;2);”-“;MID(A2;5;2);”-“;MID(A2;7;2);”-“;MID(A2;9;2);”-“;MID(A2;11;2))
=CONCATENATE(MID(A2;1;2);”:”;MID(A2;3;2);”:”;MID(A2;5;2);”:”;MID(A2;7;2);”:”;MID(A2;9;2);”:”;MID(A2;11;2))

 

An alternate solution was send in by Franco Magliaro see below:

=MID(A2,1,2)&”-“&MID(A2,3,2)&”-“&MID(A2,5,2)&”-“&MID(A2,7,2)&”-“&MID(A2,9,2)&”-“&MID(A2,11,2)
=MID(A2,1,2)&”:”&MID(A2,3,2)&”:”&MID(A2,5,2)&”:”&MID(A2,7,2)&”:”&MID(A2,9,2)&”:”&MID(A2,11,2)

 

Removing a separator (change 01-23-45-67-89-ab into 0123456789ab)

=CONCATENATE(MID(A2;1;2);MID(A2;4;2);MID(A2;7;2);MID(A2;10;2);MID(A2;13; 2);MID(A2;16;2))

or the easy way

=SUBSTITUTE(A2;”-“;””)
=SUBSTITUTE(A2;”:”;””)

 

Changing a separator (change 01-23-45-67-89-ab into 01:23:45:67:89:ab)

=CONCATENATE(MID(A2;1;2);”:”;MID(A2;4;2);”:”;MID(A2;7;2);”:”;MID(A2;10;2 );”:”;MID(A2;13;2);”:”;MID(A2;16;2))
=CONCATENATE(MID(A3;1;2);”-“;MID(A3;4;2);”-“;MID(A3;7;2);”-“;MID(A3;10;2 );”-“;MID(A3;13;2);”-“;MID(A3;16;2))

or the easy way 🙂

=SUBSTITUTE(A2;”-“;”:”)
=SUBSTITUTE(A2;”:”;”-“)

 

An alternate solution, to convert the Cisco 0123.4567.89ab MAC format to the F5 load balancer MAC format of 01:23:45:67:89:ab, was send in by Mike Wintrode. To add an extra twist, the F5 load balancer also removes the leading “0” in any pair, so for instance 00:14:0F:80:a3:01 would show as 0:14:f:80:a3:1.

So Mike created the following:

=CONCATENATE(IF(MID($A2,1,1)=”0″,MID($A2,2,1),MID($A2,1,2)),”:”,IF(MID($A2,3,1)=”0″,MID($A2,4,1),MID($A2,3,2)),”:”,IF(MID($A2,6,1)=”0″,MID($A2,7,1),MID($A2,6,2)),”:”,IF(MID($A2,8,1)=”0″,MID($A2,9,1),MID($A2,8,2)),”:”,IF(MID($A2,11,1)=”0″,MID($A2,12,1),MID($A2,11,2)),”:”,IF(MID($A2,13,1)=”0″,MID($A2,14,1),MID($A2,13,2)))

The basic premise utilizes an “IF” statement to check for a preceding “0” and if present to return the next character, and if not to return both characters.

 

Adam Heath was so kind to send me an Exel template which uses most of the above formulas
(thanks Adam!), which you can download here.

 

Suggestions for improving this article are welcome, please let me know and drop me a line.