You Can't Manage What You Don't Measure!

Tips with MS-Excel

Need help with MS-Excel ? Looking for a particular tool or macro? We post some of our code here free to copy and past into your own MS-Excel workbooks. Insert (Copy and Paste) the code(s) into your macro module and you will have these handy tools.



Click here to visit Holy Macro Books.


Click here to visit Holy Macro Books.

Create Monthly Sheets with Summary YTD Sheet.

We need worksheets for each month and a summary page so often our clients have asked us for a way to do this with one click. We found ourselves using this little tidbit almost daily.

Price = Free

Sub DoMonths()

' Add sheets for each month Jan-Dec
' changes only Sheets whose names starts with "sheet"
' provided courtesy of J. Hunter and Associates

Dim J As Integer
Dim K As Integer
Dim sMo(13) As String

sMo(1) = "Jan"
sMo(2) = "Feb"
sMo(3) = "Mar"
sMo(4) = "Apr"
sMo(5) = "May"
sMo(6) = "Jun"
sMo(7) = "Jul"
sMo(8) = "Aug"
sMo(9) = "Sep"
sMo(10) = "Oct"
sMo(11) = "Nov"
sMo(12) = "Dec"
sMo(13) = "YTD"

For J = 1 To 13
If J <= Sheets.Count Then
If Sheets(J).Name = "Sheet*" Then
Sheets(J).Name = sMo(J)
Else
Sheets.Add.Move after:=Sheets(Sheets.Count)
ActiveSheet.Name = sMo(J)
End If
Else
Sheets.Add.Move after:=Sheets(Sheets.Count)
ActiveSheet.Name = sMo(J)
End If
Next J

For J = 1 To 12
If Sheets(J).Name <> sMo(J) Then
For K = J + 1 To Sheets.Count
If Sheets(K).Name = sMo(J) Then
Sheets(K).Move Before:=Sheets(J)
End If
Next K
End If
Next J

Sheets(1).Activate
End Sub

Excel Header & Footer Codes

When you need to spice up the headers or footer in MS-Excel, use these codes:

Price = Free

CodeMeaning

&D Current date

&T Current time

&F Workbook Name

&A Worksheet name (from the worksheet tab)

&P Current page number

&P+xCurrent page number plus x

&P-xCurrent page number minus x

&N Total pages in the workbook

&&Ampersand character

&L Start left alignment

&C Start center alignment

&R Start right alignment

&"fontname" Change to designated font (quotes are necessary)

&nn Two-digit font size to use, in points

&B Bold (toggle on or off)

&I Italics (toggle on or off)

&U Underline (toggle on or off)

&E Double underline (toggle on or off)

&S Strikethrough (toggle on or off)

&X Superscript (toggle on or off)

&Y Subscript (toggle on or off)

 

Saving Original Data

When importing data from other systems that has been exported to MS Excel format the first step I do is to copy the original data and labels the copy "work" and the original "Raw". I can then clean or format the data as needed. Most often the data is in "Sheet1" this seems to be the default many systems use (Crystal Reports etc.)

Price = Free

Sub Copy_Raw()
'
' Copy_Raw Macro 4/11/2007 by Jim Compton
'
' Stop screen updating or flickering until macro has finished
'
Application.ScreenUpdating = False
'
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Raw"
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "Work"
Range("A1").Select

Application.ScreenUpdating = True

End Sub

Dates & Times

Working with Dates and Times can be a little tricky in MS-Excel.
We can't cover it all here but we have a couple helpful tips. (More in our library.)

Price = Free

Return a value from date value, You can use:
Day()
Weeknum()
Month()
Year()
To display that information

in Cell a1 you have 11/8/2010 10:00:00 PM
in cell a2 = day(a1) result 8
in cell a3 = year(a1) result 2010
in cell a4 =A1-DATE(YEAR(A1),1,0) result 313
in cell a4 =TEXT($A1,"hh") result 22 (hour in 24 hour format)

Dates and times are stores as numbers in MS-Excel where:

Jan 1st 1900 = 1.0 all dates thereafter are integer values for example
Nov.11, 2010 = 40,490

60 Seconds = 1 Minute
60 Minutes = 1 Hour (3,600 seconds)
24 Hours = 1 day (86,400 seconds)

Time is the decimal portion of the number where 6:00AM = 0.25 (25% of the 24 hour day.

To enter the current date, press Ctrl + ;
(hold the Ctrl key while typing a semicolon.)
To enter the current time, press Ctrl + :
(hold the Ctrl key while typing a colon.)

Password Protection

Quick Password Protect (Unprotect) all worksheets within an open workbook

Price = Free

I have used PW as the password, change all the "pw" in the quotes to alter the password.

Sub ProtectAll_pw()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Protect Password:="pw"
Next
Application.ScreenUpdating = True
End Sub

Sub UnProtectAll_PW()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Unprotect Password:="pw"
Next
Application.ScreenUpdating = True
End Sub

Note: while it is very easy to break internal passwords in
MS-Excel, it is highly recommends that you have some method to recall or remember what ever you use as a password.

Using IF to assign letter grades

Assuming the numerical score is in column "B" enter this formula in the column where you want the letter grade then copy it down. Obviously the numeric values can be changed as desired.

Price = Free

=IF(B2<52,"F",IF(B2<64,"D",IF(B2<75,"C",IF(B2<85,"B","A"))))

Rounding Numbers By Fractions or Decimals

When rounding numbers to a  decimal factor or fraction, eighth  in a formula as 1/8 or by the decimal value of .125.

Price = Free

Rounding up
Expressing a fraction, the formula is
 
=CEILING(A1,1/8)
Expressing a decimal, the formula is
=CEILING(A1,0.125)
Notice the results are the same

Adding the Path and File name

Price = Free

To insert the file path and filename into a cell, you use the CELL worksheet function in the following manner:

=CELL("filename")

If you want to strip out the brackets and the sheet name, you can use the following variation on the formula:

=SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[","")

Highlight all Precedents

Need to highlight all the Precedents is a formula within a cell?

Price = Free

Use Ctrl [ (Control left square bracket)

Sort Birthdays by Month and Day (without Year)

Sorting birthdays by months and day we are using column C serves as a helper column, and assume full dates are in colum B
In cell C5 and copied down is the formula
=TEXT(B2,"MM DD").
After selecting the range, Press
Alt+D+S to call the Sort dialog box.
Sort by column C in ascending order.

Price = Free

Name

Description

Price = Free

Name

Description

Price = Free

 

Our Favorite MS-Excel Resources: