Excel Tips & Tricks

Excel Tips and Macros

A compilation of Excel Tips over a period of 6-8 years. This is not an exhaustive list, just one that I have built over time. When I learn a new tip or process, I try to record that in this document.

I hope this helps other people as it has helped me over the years.

As always, any contributions are welcome!

Table of Contents

Excel Tips

Find Specific Text in a Cell

How to Extract the First Word of a String in Microsoft Excel

How to Extract the Last Word of a String in Microsoft Excel

How to Extract the First 2 Words of a String in Microsoft Excel

Remove First Word in a String

Remove Last Word in a String

Pull String before a Certain Character

Using a Formula to Fill Blank Cells with Value Above

Add Hyperlink to Text Only and Not the Entire Cell in Excel 2010

How to Create a Hyperlink Inside of Excel Using the Value in another Cell

How to Extract a File Name from a Path in Microsoft Excel

Count the Number of Words in a Cell Or a Range Cells in Excel

Counting the number of occurrences of a character in one cell

Count the number of cells that contain text.

Count the number of cells that contain star in any way. No matter what is before or after star, this function finds all the cells that contain star in any way.

Count the number of cells that contain exactly star + a series of zero or more characters. An asterisk (*) matches a series of zero or more characters.

Count the number of cells that contain exactly star + 1 character. A question mark (?) matches exactly one character.

Count the number of cells that contain exactly star.

How to capitalize the first letter in a cell in Excel

Retrieve weekday from date

Sort cells by font color in Excel

How to Color Alternate Rows in a MS Excel 2010 Table

Calculate the number of days between two dates

Days between the two dates:

Weekdays between the two dates:

Excel Options

Custom Lists

Macros

HyperAdd

HyperAdd Hotkey Solution

FormatTL

FormatWrapTxt

How to Use the Vlookup Function in Excel

VLOOKUP

LOOKUP_VALUE

TABLE_ARRAY

COL_INDEX_NUM

[Range_Lookup])

Count the Number of Cells with Text Formatted Italic

Glossary

Search & Find

Left, Right, Mid

Custom Number Format

Leading Zeros

Decimal Places

Add Text

Large Numbers

Repeat Characters

Colors

Dates and Times

How to Export Comments in Excel to a Word Doc

Automated File Names

Question:

Advice

Padded CELL

Step 1: FINDing the Beginning and the End

Step 2: LEFT a bit, RIGHT a bit, Aim for the MID Section

Step 3: Error Trapping

Final Note

Vlookup Multiple Criteria Tutorial in Excel 2013

Understanding the Vlookup Function

Concatenate to Create Unique Identifier

Adding the Vlookup Formula

Add the Lookup Value

Multiple String Search Criteria

Macros – Creating Macros

Record a Macro

Create a Macro Using Microsoft Visual Basic

Create a Startup Macro

Copy Part of a Macro to Create another Macro

Troubleshoot Macros

When I click the Refresh button, a message tells me that my macro changes will be lost.

While recording a macro, I recorded an action I didn't want.

A macro I recorded sometimes produces an error message.

Excel Tips

Find Specific Text in a Cell

=ISNUMBER(SEARCH("clarity",E2))

How to Extract the First Word of a String in Microsoft Excel

=LEFT(C2,FIND(" ",C2)-1)

How to Extract the Last Word of a String in Microsoft Excel

=RIGHT(C2,LEN(C2)-FIND("*",SUBSTITUTE(C2," ","*",LEN(C2)-LEN(SUBSTITUTE(C2," ","")))))

How to Extract the First 2 Words of a String in Microsoft Excel

=LEFT(C2,FIND(" ",C2,FIND(" ",C2,1)+1)-1)

Remove First Word in a String

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Remove Last Word in a String

=LEFT(M2,LEN(M2)-FIND(" ",M2))

Pull String before a Certain Character

The following formula will perform the extract you need:
=LEFT(F2,FIND(";",F2,1)-1)

Using a Formula to Fill Blank Cells with Value Above

With this method, please do as the following steps:

Select the range that contains blank cells you need to fill.
ClickHomeFind & SelectGo To Special…, and aGo To Specialdialog box will appear, then checkBlanks option. See screenshot:
ClickOK, and all of the blank cells have been selected. Then input the formula “=A2” into active cell A3 without changing the selection. This cell reference can be changed as you need.
PressCtrl + Enter, Excel will copy the respective formula to all blank cells.
At this point, the filled contents are formulas, and we need to convert the formals to values. Then select the whole range, right-click to chooseCopy, and then pressCtrl + Alt + Vto active thePaste Special…dialog box. And select Valuesoption fromPaste, and selectNoneoption fromOperation.
Then clickOK. And all of the formulas have been converted to values.

Add Hyperlink to Text Only and Not the Entire Cell in Excel 2010

Adding a hyper reference (although you can still select the whitespace of a cell (after the text has finished) and it won't follow the link)

=CONCATENATE("This is your ", HYPERLINK(" " link")

FYI, you can hold ALT key and click on a cell without it opening the URL

How to Create a Hyperlink Inside of Excel Using the Value in another Cell

How to create a hyperlink inside of excel using the value in another cell to as part of the link. The Formula looks like this:

HYPERLINK(link_location,friendly_name)

…Wherelink_locationis where you want the hyperlink to go to andfriendly_nameis how the link will appear on your sheet.

For example, if you have a file on your desktop it would look something like this:

Hyperlink(“C:\Documents and Settings\Admin1\Desktop\Example.xls”,”Example Link”)

You would just need to change the exact path to the file on your computer. You can also make the file name a cell reference that contains the name of the file you want to point to, as in this example:

Hyperlink(“C:\Documents and Settings\Admin1\Desktop\” & A1 & “.xls”,A1)

…In this example, A1 could equal “Example” and “Example will also be displayed for the hyperlink text.

How to Extract a File Name from a Path in Microsoft Excel

Path & File name: c:\My Documents\ExcelTip.xls

The Function:

=MID(A25,FIND(“*”,SUBSTITUTE(A25,”\”,”*”,LEN(A25)-LEN(SUBSTITUTE(A25,”\”,”"))))+1,LEN(A25))

Result: ExcelTip.xls

Count the Number of Words in a Cell Or a Range Cells in Excel

=IF(LEN(TRIM(C2))=0,0,LEN(TRIM(C2))-LEN(SUBSTITUTE(C2," ",""))+1)

Counting the number of occurrences of a character in one cell

=LEN(E2)-LEN(SUBSTITUTE(E2,";",""))

Count the number of cells that contain text.

=COUNTIF(A1:A7,"*")

Count the number of cells that contain star in any way. No matter what is before or after star, this function finds all the cells that contain star in any way.

=COUNTIF(F:F,"*Title*")

Count the number of cells that contain exactly star + a series of zero or more characters. An asterisk (*) matches a series of zero or more characters.

=COUNTIF(A1:A7,"star*")

Count the number of cells that contain exactly star + 1 character. A question mark (?) matches exactly one character.

=COUNTIF(A1:A7,"star?")

Count the number of cells that contain exactly star.

=COUNTIF(A1:A7,"star")

How to capitalize the first letter in a cell in Excel

To capitalize “How” in cell “A1″, use the following formula:

=REPLACE(A1,1,1,UPPER(LEFT(A1,1)))

Retrieve weekday from date

=TEXT(B2,"dddd")

Sort cells by font color in Excel

In fact, sorting by font colour in Excel is absolutely the same assorting by background color. You use theCustom Sortfeature again (HomeSort & FilterCustom Sort...),but this time chooseFont Colorunder "Sort on", as shown in the screenshot below.

If you want to sort by just one font color, then Excel's AutoFilter option will work for you too:

Apart from arranging your cells by background color and font color, there may a few more scenarios when sorting by color comes in very handy.

How to Color Alternate Rows in a MS Excel 2010 Table

If you used to know how to color the alternate table rows in earlier versions of MS Excel you need to learn this useful skill again because the method has changed.

There are several methods to do this. Here I’ll cover one such method.

Here is oursample table, without any colors at all:

(1) Select the cellsyou’d like to fill with alternating bands of color:

(2)Go to theHOMEtab on your ribbon.

(3)SelectConditional Formatting > New Ruleto display theNewFormattingRuledialog box.

(4)Select “Use a formula to determinewhichcells to format” forRule Type.

(5)Enter this formula forRule Description:=MOD(ROW(),2)=0

(6)ClickFormatto display theFormat Cellsdialog box. Select theFilltab:

(7)Select the color you like and clickOK. Back at theNewFormattingRuledialog box, clickOKagain.

Now the 1st, 3rd, and all the otherODD NUMBERED alternating rowsof your table are colored:

(8)If in the formula you change 0 with 1 (=MOD(ROW(),2)=1), EVEN NUMBERED alternating rowswill be colored:

(9)If you’d like to haveTWO DIFFERENT COLORS alternating, first color the whole background solid in the color you like and then follow all the above steps:

By applying both methodsto coloralternate rowsandalternate columns, you can create unique and interesting color schemes for your table:

Calculate the number of days between two dates

To do this task, use the subtraction (-) operator or theNETWORKDAYSfunction as shown in the following example.

A2 / 6/8/2007
A3 / 6/20/2007

Days between the two dates:

=A3-A2

Weekdays between the two dates:

=NETWORKDAYS(A2,A3)

Note: use DATE(2012,5,23) for the 23rd day of May, 2012

Excel Options

Custom Lists

Go to Excel Options > Advanced under the “General” section

Macros

HyperAdd

On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module. Copy and paste this code into the code window of the module. It will automatically name itself HyperAdd.

Sub HyperAdd()

'Converts each text hyperlink selected into a working hyperlink

For Each xCell In Selection

ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula

Next xCell

End Sub

When you're finished pasting your macro, click Close and Return to Microsoft Excel on the File menu.

Then select the required cells and click macro and click run.

NOTEDo NOT select the whole column! Select ONLY the cells you wish to be changed to clickable links else you will end up in a neverending loop and have to restart Excel! Done!

HyperAdd Hotkey Solution

Here’s a hotkey solution, but I just can't figure out how to get Excel to evaluate a column of URLs as hyperlinks in bulk.

Create a formula,="=hyperlink(""" & A1 & """)"
Drag down
Copy new formula column
Paste Special Values-only over the original column
Highlight column, clickCtrl-H(to replace), finding and replacing=with=(somehow forces re-evaluation of cells).
Cells should now be clickable as hyperlinks. If you want the blue/underline style, then just highlight all cells and choose the Hyperlink style.

The hyperlink style alone won't convert to clickable links, and the "Insert Hyperlink" dialog can't seem to use the text as the address for a bunch of cells in bulk. Aside from that,F2andEnterthrough all cells would do it, but that's tedious for a lot of cells.

FormatTL

Sub FormatTL()

' FormatTL Macro

' Keyboard Shortcut: Ctrl+u

Cells.Select

With Selection

.HorizontalAlignment = xlLeft

.VerticalAlignment = xlTop

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlInsideVertical)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlInsideHorizontal)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

Range("G4").Select

End Sub

FormatWrapTxt

Sub FormatWrapText()

' FormatWrapText Macro

' Keyboard Shortcut: Ctrl+i

With Selection

.HorizontalAlignment = xlLeft

.VerticalAlignment = xlTop

.WrapText = True

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

End Sub

How to Use the Vlookup Function in Excel

VLOOKUP

The Vlookup function for Excel is a great function that allows you to search through a list of data within Excel. This allows you to do things like use the ID number of an item to locate other data for that item without having to manually go through a list of data.

Though the function seems complex at first, it is quite easy to use once you become comfortable with the functions arguments.

Here is the function listed with all of its arguments:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Below, each argument for this function is explained.

LOOKUP_VALUE

This is the actual thing/item/value/etc. that you want to use to locate the desired data contained within your Excel spreadsheet. This could be a unique ID number that attaches to a specific record; it could be a name; it could be a number that is contained within a range (explained in the range_lookup argument). Simply put, this is the value that is used to locate the data that you want to return. Once the Vlookup function finds this value, it will return data that is contained within the exact same row as where this value is found. The col_index_num argument below is where you tell the Vlookup function from which column you would like to return the data.

Note that this value is searched for within the left-most column of data that is contained within the reference stated in the next argument, table_array. Also, you may use a cell reference for the lookup_value instead of an actual value; this is a common technique that is used so that you can return different data without having to change the actual Vlookup function.

TABLE_ARRAY

This argument tells the Vlookup function from which table of data you need to retrieve your values. For this argument, you will select a range of cells, for instance: A1:D10. In this example, the left most column is A and this is where the Vlookup function will try to find the lookup_value argument in order to return data from the corresponding row in which it is found.

Make sure that you select a table_array that contains all of the data that you would like to return using the Vlookup function because you cannot return any data that is outside of this table array.

COL_INDEX_NUM

This is where you tell the Vlookup function which column contains the data that you would like to return. Following the example used in the table_array argument explanation above, if you want to return data from column C then you would put 3 for this argument. The columns are numbered starting with 1 for the left-most column referenced in the table_array argument.

[Range_Lookup])

This is the only optional argument and its value can only be either TRUE or FALSE. This argument tells the Vlookup function if you want it to find an exact or approximate match. If you input FALSE, which is the most commonly used value for this argument, Excel will only return an exact match where the lookup_value exactly equals a value found in the left-most column of the table stated in the table_array argument. If this value is not found, it will return an error. If multiple values are found, the Vlookup function will return the first value that it finds.

If you leave the value for this argument empty, since it is an optional argument, it will default to TRUE, which you can also manually enter yourself. In this case, the Vlookup function will return an exact match if one is found, BUT, if an exact match is not found, it will return the next largest value that is less than the lookup_value. Also, in order for this to work, the first column of the table_array needs to be sorted in ascending order (the one that will be used to find the lookup_value).

This TRUE value for the range_lookup argument is most often used when you want to return something that can be within a range, such as school grades, where an A or a B is not just a single number but, instead, can be achieved by having a grade that is within a range of numbers such as 80-89 for a B and 90-100 for an A.

The Vlookup function is one of the most helpful functions that you will use within Excel. Though it may seem scary and confusing at first, just stick with it and you will be able to input this function in seconds and save yourself hours of sifting through data.

Count the Number of Cells with Text Formatted Italic

The only way you can do this is by creating a UDF:

Alt+F11 to get to the VBA editor
Insert a module (right click on your sheet name and choose <Insert<Module>)
Paste this code:

Function CountItalic(rng As Range)

Count = 0

For Each rng In rng

If rng.Font.Italic = True Then

Count = Count + 1

End If

CountItalic = Count

End Function

Then you will be able to use the function CountItalic() in your worksheet.

To make sure I did not count blank italic-formatted cells, I did have to add a nested if with the resultant formula:

Function CountItalic(rng As Range)
Count = 0
For Each rng In rng
If rng.Font.Italic = True Then
If rng.Value > "" Then
Count = Count + 1
End If
End If
Next
CountItalic = Count
End Function