PDF Print E-mail
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...

Example of improving the readability of data in Excel
The original layout

 

Example of improving the readability of data in Excel
The new and improved layout ;-)

 

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 This e-mail address is being protected from spambots. You need JavaScript enabled to view it .

 

Sponsored Links