Video Based Training

Formulas

Capitalize the First Letter in a Cell
Use this formula to capitalize the first letter of a cell:
=CONCATENATE(UPPER(LEFT(A1,1)),RIGHT(A1,LEN(A1)-1))

Checking for Errors
The IF(ISERROR) function can be used to check for errors produced by a formula. In the example below, 0 is returned if an error is found. If no error is found, then the calculation is performed.
=IF(ISERROR(B4/C4),0,B4/C4)

Formula Comment
You can add a comment to an Excel formula. This is different than adding a comment to a cell.
=A1+A2+N("comment here")

Link to External Workbook
='drive:path[filename.xls]worksheet name'!cell reference
=c:my documents[monthly.xls]Jan'!A1

Convert Name from 'Last, First' to 'First Last'
Use this formula to convert a cell's value for a person's name from 'LastName, FirstName' to 'FirstName LastName'. The formula works by extracting information before and after a special character; in this case a comma.
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)&" "&LEFT(A1,FIND(",",A1)-1)
Old Value: Smith, John
New Value: John Smith

Random Selection from List:
=INDEX(A1:A10,ROUNDUP(RAND()*COUNTA(A1:A10),0))

Find a Value in a Series of Cells
The formula below was used to determine if the word No was detected in the range D8:J8. If the word No was detected, then Fail would display. If the word No was not detected, then Pass would be displayed instead. If the word No was not detected within the range, then an #N/A error would be returned. The IF(ISERROR) function must be used to prevent the error from displaying.
=IF(ISERROR(MATCH("No",D8:J8,0))=TRUE,"Pass","Fail")

vLookup

• lookup_value The cell you'd like to match in a separate worksheet
• table_array The table you'd like to look in. *matching data must appears in left most column
• col_index_num The column number to search for a matching record. Enter '2' for column 'B'
• range_lookup Type 'FALSE' for an exact match, and 'TRUE' to find the closest match

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
=VLOOKUP(C2, Sheet2!A:G, 7, FALSE)

External Links in 2007 & 2003
http://support.microsoft.com/kb/925893
[James] I had a problem when I had all xlsm books and linked to the xlsx master sheet. Had to convert all xlsm to xls, and converted master sheet to xls. The vlookup would no longer function unless the workbook was open. I found if the workbook was closed and the book was unprotected it would work. Whereas xlsm could be closed and protected. Not sure how to fix.

Count If NOT Blank
Use the "<>" condition below to exclude blank cells from a formula.
=COUNTIF(A1:A20,"<>")

If Statements for Blank Cells
Either of the two options below can be used to test for blank cells:
=IF(ISBLANK(A1),"blank","notblank")
=IF(A1="","blank","notblank")

Find Text Inside a Cell
Use this formula to search inside a cell and return a TRUE value if the text is found. The search is case insensitive.
A1="Grand Total"
=if(ISNUMBER(SEARCH("Total",A1)),"Match","No Match")

Multiple IF/AND Conditions
A series of AND conditions can be added to an IF formula. Replace AND with OR to check for only one of a series of conditions.
=IF(AND(B1="",B1=A1),"ConditionsMet","ConditionsNotMet")
// if B1 is blank and B1 equals A1, then display ConditionsMet. Else, display ConditionsNotMet

Multiple IF (Nested IF) Conditions
An alternate way to use multiple IF conditions is the method shown below. It works by testing the first condition and returning a value if the test returns TRUE. If the test returns false, then the next condition is tested. If none of the tests return TRUE, then a final value is returned.
=IF(A1>89,"A",IF(A1>79,"B", IF(A1>69,"C",IF(A1>59,"D","F"))))
// if score is greater than 89 grade equals A, if score greater than 79 grade equals B… else grade equals F

Extract Date Using Formula
Use this formula to convert Excel's serial-date format into a standard date format. In the example below, A1 = 07/11/2011. Additional text function formats can be found here.
=TEXT(A1, "mm") => 07
=TEXT(A1, "dd") => 11

Keyboard Shortcuts

Copy Selected Cells
Use this keyboard shortcut to only copy unhidden, grouped, or filtered lists.
alt + ;
ctrl + c
ctrl + v

Visual Basic

To Install a Function
Follow these steps to install a custom function inside an Excel workbook:
Alt + F11 to open the VB editor
Insert > Module

Comments
Use the single apostrophe to add a comment to Visual Basic
' this is a comment

Turn Off Screen Flicker
Turn off screen flicker to increase the speed of a macro.
' disable screen flicker
Application.ScreenUpdating = False
' YOUR CODE
 
' re-enable screen flicker
Application.ScreenUpdating = True

Run a Macro from Within a Procedure
Similar to an include, a macro can be called from another procedure or subroutine using the code below. This is also helpful if you have a procedure too large to run and need to break down into smaller modules that can be called from a new module.
Application.Run ("MacroName")

Loop
Use this code to loop a subroutine the number of times you define. The example below is repeated 100 times.
For x = 1 To 100
  VB Code Here
  VB Code Here
Next x

Retrieve XML string from API site:
Sub RetrieveData()
 
Workbooks.OpenXML Filename:= _
  "http://api.careerbuilder.com/v1/jobsearch?&DeveloperKey=WDH259673&Location=Phoenix&CoBrand=cbga_tap&SiteID=cbga_tap_api&UrlCompression
Service=tinyURL&Category=JN045&perpage=100&pagenumber=1" _
    , LoadOption:=xlXmlLoadImportToList
  ActiveWorkbook.SaveAs Filename:= _
    "C:RetrieveData.xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
  ActiveWorkbook.Close
 
End Sub

Open Workbook and Copy Info Into Active Workbook
This macro can be used to export data from an external workbook and copy information from the selected sheet into the active sheet within the workbook running the macro.
Workbooks.Open Filename:="C:directoryworkbook_to_copy_from.xls"
Rows("2:101").Select
Selection.Copy
Windows("workbook_to_paste_into.xls").Activate
ActiveSheet.Paste
Windows("workbook_to_copy_from.xls").Activate
ActiveWorkbook.Close

Delete Carriage Returns, Line Feeds, and Tabs
Run the macro below to remove carriage returns and line feeds from an excel file.
Sub remove_line_feeds()
 
' find and replace carriage returns with a semicolon followed by a space
  Cells.Replace What:=Chr(13), Replacement:="; ", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 
' delete all line feeds. do not replace with another character
  Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 
' delete all tabs. do not replace with another character
  Cells.Replace What:=Chr(9), Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 
End Sub

Print Worksheet
Assign the following code to a button to print the active worksheet.
Sub print_sheet()
  ActiveSheet.PrintOut Copies:=1
End Sub

Find Text in a Range of Cells
A custom function must be created to search for a value within a range of cells.
' this is the custom function
Function MyFind(rng As Range, target As Range) As String
MyFind = "No"
For Each cell In rng
If InStr(cell.Value, target) Then
MyFind = "Yes"
Exit For
End If
Next cell
End Function
 
' paste this code into the active cell.
' the first value represents the range, the second value is the value being searched.
' note that searches are case sensitive
=MyFind(B1:B20,A1)

Show Tab Name in a Cell
Use this function to insert the tab name into a cell within a workbook. To run the function, insert =TabName() into any cell.
Bug Found: last tab opened appears on every tab function is called. Only use this function for workbooks with one tab.
Function TabName()
' use this macro to insert tab name into cell
  TabName = ActiveSheet.Name
End Function

Extract URL from Hyperlink

1. Create the VB function below
2. Use the function in a formula to extract the url from a hyperlink: =getURL(A1)

Function getURL(pRange As Range) As String
 
   Dim ST1 As String
   Dim ST2 As String
 
   If pRange.Hyperlinks.Count = 0 Then
      Exit Function
   End If
 
   ST1 = pRange.Hyperlinks(1).Address
   ST2 = pRange.Hyperlinks(1).SubAddress
 
   If ST2 <> "" Then
      ST1 = "[" & ST1 & "]" & ST2
   End If
 
   getURL = ST1
 
End Function

Helpful URL's

Related Files