### Convert numbers to minutes

12.5

9.3

10.7

and want to convert this numbers to a minutes/seconds format you can use this formula:

=A1/(24*60)

This assumes that the values below are on cells A1:A3 and this formula is placed on cell B1 and copied down. You need to format the cells B1:B3 as Custom mm:ss to see this:

12:30

09:18

10:42

### Multiply range of values by a value

One of the most common tasks that we need to do in Excel is multiply a range of value on a sheet by a specific value, say, multiply a range of values by 100. This can be done very easily by following this steps. Say you have this sheet:

In cell A5 you should put the value that you want to use to multiply the values on cells A1:D3. Now on cell A5 make a Ctrl+C (Copy) and select the cells A1:D3. Right-click on top of the range and select Paste Special, on Paste options select the Values and on Operation select Multiply. Click OK. Now your sheet should look like this:

If instead of multipying the values by 100, you Add, Substract or Divide the values by the value that you select on another cell (in this case I used the cell A5).

### Get row number from a match

Excel has a function to locate a value on a range of cells and return the number of the row where the value is found, it’s the MATCH() function. The syntax is like this:

**MATCH**(**lookup_value**,**lookup_array**,match_type)

*lookup_value* is the value that we want to look for and *lookup_array* is the range of cells where we want to search. *match_type* can have 3 values:

1 - finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order;

0 - finds the first value that is exactly equal to lookup_value

-1 - finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order.

If match_type is omitted, it will assume to be 1.

Here’s an example. We have the following table:

We want to find the name Mary on the range of A2:A6. So we should use this formula:

=MATCH("Mary",A2:A6,0)

This will return 2 because the name Mary can be found on cell A2.

### Excel Charts - Add totals labels to Stacked Column chart

I can make a Stacked Column chart with 3 series, one for each Vendor. The chart will look like this:

Select this new series on the chart, right click and choose Format Data Series. On the Data Labels tab, Label Contains mark Value. Then on the Patterns tab put Border-None and Area-None. Click OK. Now select the labels, right click on them, choose Format Data Labels and on the Alignment tab, Label Position choose Inside Base. Click OK. You need to adjust the scale of your chart so that it looks like the original one. In this case, I changed Maximum value to 450 instead of Automatic. As you can see, the “Total” series name still shows up on the Legend. You need to select the Total legend and delete. The final result will be this something like this:

### VBA – Define dynamic ranges

In VBA code we can define a dynamic range by checking the last row that has data on it. this can be defined in various ways. Here is a simple way of doing it:

Dim rng As Range

rng = Range("A12:C" & Worksheets("Sheet1").Range("C65535").End(xlUp).Row + 1)

This will get us the range A12:C200 if C200 is the last used cell in column C.

You can also define a range that gets all of the columns in row 1 that are used as column headers, like this:

Dim ws As Worksheet

Dim rng As RangeSet

ws = ActiveSheetSet

rng = Intersect(ws.Rows(1), ws.UsedRange)

This will get us the range of A1:D4 if columns A, B, C and D have data on it.

### VBA – Get font color Function

When we have colors on our sheets data and want, for instance, to count how many “red” words we have on our sheet, that is not possible because there is no formula in Excel to check for font colors. Instead we can create our own VBA Function to get the font color. It’s a very simple code. You have to insert it on a VBA module on your sheet.

Function GetFontColor(ByVal Target As Range) As Object

GetFontColor = Target.Font.ColorIndex

End Function

Then you can use it on your sheet like this:

GETFONTCOLOR(A2)

Below is an example on how you can use this function. In column C we put the font color of text in column A.

COUNTIF(C2:C9,3)

“3” in the formula refers to the color red.

### Add-in – Save Excel 2007 file as Pdf

Many times we need to send our Excel files to someone but we don’t want them to be able to edit our files. One of the ways that we can use to do that is to save the Excel sheet as a PDF file. With Office 2007 we don’t need a third party tool to convert our sheets, we can install an Add-in from Microsoft that can be found on the link below:

This Add-in allows you to export not only Excel sheets but also other Office 2007 programs. The sheets can be exported either to PDF or XPS format. It also allows to send as an email attachment in this formats.

### VBA – Combine sheets data into one sheet

I have a Workbook with multiple sheets, all with the same configuration, with headers on row 1 and data starting from row 2.

I want to combine the data from all the sheets into one single sheet called “Target”. I have to go through all the sheets and check what is the last row and the last column with data to define the range that I’m going to copy to the Target sheet. I think that the code is all well commented for you to understand how this is done. This is a simple example how to combine data from multiple sheets into one single sheet. This can be done other ways or can be more complex with sheets with different columns or so. This will be handled in future articles.

Sub CombineSheets()

'This macro will copy all rows from the first sheet

'(including headers)

'and on the next sheets will copy only the data

'(starting on row 2)

Dim i As Integer

Dim j As Long

Dim SheetCnt As Integer

Dim lstRow1 As Long

Dim lstRow2 As Long

Dim lstCol As Integer

Dim ws1 As Worksheet

With Application

.DisplayAlerts = False

.EnableEvents = False

.ScreenUpdating = False

End With

On Error Resume Next

'Delete the Target Sheet on the document (in case it exists)

Sheets("Target").Delete

'Count the number of sheets on the Workbook

SheetCnt = Worksheets.Count

'Add the Target Sheet

Sheets.Add after:=Worksheets(SheetCnt)

ActiveSheet.Name = "Target"

Set ws1 = Sheets("Target")

lstRow2 = 1

'Define the row where to start copying

'(first sheet will be row 1 to include headers)

j = 1

'Combine the sheets

For i = 1 To SheetCnt

Worksheets(i).Select

'check what is the last column with data

lstCol = ActiveSheet.Cells(1, Activesheet.Columns.Count).End(xlToLeft).Column

'check what is the last row with data

lstRow1 = ActiveSheet.Cells(activesheet.rows.count, "A").End(xlUp).Row

'Define the range to copy

Range("A" & j, Cells(lstRow1, lstCol)).Select

'Copy the data

Selection.Copy

ws1.Range("A" & lstRow2).PasteSpecial

Application.CutCopyMode = False

'Define the new last row on the Target sheet

lstRow2 = ws1.Cells(65536, "A").End(xlUp).Row + 1

'Define the row where to start copying

'(2nd sheet onwards will be row 2 to only get data)

j = 2

Next

With Application

.DisplayAlerts = True

.EnableEvents = True

.ScreenUpdating = True

End With

Sheets("Target").Select

Cells.EntireColumn.AutoFit

Range("A1").Select

End Sub

### Excel Charts – Do not show empty dates

As you can see, Excel has added the dates that where missing between 2 and 5 of October. This dates are not a part of our original table and don’t have value to be charted so I don’t want to display them. This happens because Excel assumes that this is a Time-scale category chart. To remove this dates, go to Chart Options and set the Primary Axis-Category X axis to “Category”. This way we get a chart like Chart 2:

### Splitting a string

There are numerous occasions when we need to split a string into pieces. Depending on the type of string that we are handling we can do it using many different techniques. Here I’m going to show you one way of doing a simple string split. On column A we have the strings that we want to split. We want to get the text that is before the “,” character into column B.

So in column B we can put the following formula:

=TRIM(LEFT(A1,FIND(",",A1)-1))

Here I use 3 Excel functions: TRIM(), to remove the spaces on the beginning and on the end of the string that we get; LEFT() to get the string to the left of the “splitter”, in this case is the , character and FIND() to find the position of the “splitter” character. I remove 1 from the position that FIND() returns so that I don’t get the “,” character.

In column C we can put the following formula to the the text that is on the right of the “,” character:

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

Here, instead of the LEFT() function, I use the RIGHT() because I want to get the text on the right of the “splitter” character. I use the LEN() function to get the number of character that the string on column A has so that I can subtract the position where the FIND() function finds the “splitter” character. This way I get the number of characters for the function RIGHT() to return, counting from the end of the string in column A. Example: the string in cell A1 has a LEN(A1) of 25 characters. The “splitter” character is found on position 9 of the string. So my formula will return on cell C1 (25-9)=16 characters counting from the right of string in cell A1, the result will be “2nd Street 2009”.

### VBA - Convert emails to hyperlinks

On a sheet column I have email addresses and I want to convert them to hyperlinks so that when I click them, I open my email client and send and email to that address. I can use a macro to change the content of that cell to a hyperlink. Here’s the code for that:

Sub Convert_to_Hyperlink()

Dim rng As Range

Dim cell As Range

'Define here the range where you have your emails values

rng = Range("B2:B100")

For Each cell In rng

'If the cell is blank, ignore it

If cell.Value <> "" Then

cell.Hyperlinks.Add ANCHOR:=cell, Address:="mailto:" & cell.Value, TextToDisplay:=cell.Value

End If

Next cell

End Sub

### Create random combinations in Excel

Imagine that you need to create random combinations of letters in Excel. In this example I want to create a 3 letters random combinations list. I want the result to be like this:

TEX |

JYY |

QCX |

CDH |

NTW |

To get this kind of combinations I used the following formula:

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))

I’ve used the RANDBETWEEN() function to generate values between 65 and 90 because they are the ANSI codes of letters A to Z. The RANDBETWEEM() function has the following arguments:

**RANDBETWEEN(bottom,top)**

*bottom* is the smallest integer that the function will return and *top* is the highest.

After the generation of the random number, I use CHAR() function to return the corresponding character from the ANSI table of characters. This function has the following syntax:

**CHAR**(**number**)

*number* is a number between 1 and 255 that specifies which character we want to return.

On my formula, combining 3 times the formula CHAR(RANDBETWEEN(65,90) I get a combination of 3 letters.

If I wanted to get a random list of numbers between 100 and 1000, I could use the following formula:

=RANDBETWEEN(100,1000)

This will give me a list of number like the one below:

941 |

486 |

970 |

952 |

376 |

### VBA – Delete rows based on condition

One of the most common questions I get is how to delete rows based on one or more conditions. For instance, you want to delete all rows in your sheets that have the name “John” in column A. Here’s the code to do that:

Sub Delete()

Dim startrow As Long

'starting row number here

startrow = 1

' Assuming data to check is in A Column

Do Until startrow > Cells(Cells.Rows.Count, "A").End(xlUp).Row

If Cells(startrow, 1).Value = "John" Then

Rows(startrow).Delete

Else

startrow = startrow + 1

End If

Loop

End Sub

### Highlight duplicates

One of the most common tasks that an Excel user needs to do is to find duplicates on a list of data. There are several ways to do this, depending on the needs. One of the ways is to highlight the values that are duplicated on a column. This can be done using the Format-Conditional Formatting option on the menu bar. Here's an example:

On cell A2, I created a Conditional Formatting with a condition "Formula is" and putted the function =COUNTIF(A:A,A2)>1. The COUNTIF function has the following syntax:**COUNTIF(range,criteria)***range* is the area where you you want to check if it finds the value specified by *criteria*.

This will return TRUE if the value from cell A2 is found more than 1 time in column A. If this is TRUE it will apply the Format that I defined. I then Copy and Paste Special-Format to the rest of the cells in column A.

In Excel 2007 and 2010, you can use the a new option on the Conditional Formatting menu that is the Duplicate Values.

Just select the range where you want to highlight the duplicate and go to the menu bar and choose Highlight Cells Rules-Duplicate Values. A Duplicate Values dialog box will open. You can choose to highlight the Duplicate or the Unique values and you can set the color that you want to use to highlight the values.

The result will be the same as using the COUNTIF solution. On Excel 2003 you don’t have this Duplicate Values option on the Conditional Formatting menu.

### IF() – Checking conditions

The IF() function is one of the most used in Excel. It’s very simple to use. The syntax for this function is:

**IF(logical_test,value_if_true,value_if_false)**

*logical_test* is the value or expression that we want to evaluate. *value_if_true* is the result if the condition of *logical_test* is true and *value_if_false* is the result if the condition is false.

Examples:

=IF(A2=100,"True","False") will check if there is a value 100 in cell A2. If true, will put on the cell where we have the formula, the value “True”. If the value in cell A2 it’s not 100 the formula will return “False” on the cell.

=IF(AND(A2=100,A3=200),"True","False") this is the same as the last example with the exception that in this case we are checking two condition and only if both are true, the result will be “True”.

### VBA – Copy all Worksheets to another Workbook

If you want to copy all of your worksheets from the actual workbook to another workbook, you can use this simple VBA code to do it.

Sub copy_sheets()

Dim wb1 As Workbook

Dim wb2 As Workbook

wb1 = ActiveWorkbook

'Change the name of the destination workbook here

wb2 = Workbooks("Destination.xls")

For Each Sheet In wb1.Sheets

If Sheet.Visible = True Then

'copy the sheets after the last

'sheet of the destination workbook

Sheet.Copy After:=wb2.Sheets(wb2.Sheets.Count)

End If

Next Sheet

End Sub

### DATEVALUE() - Convert text values to dates

Sometimes we have values on our sheets that are stored as text (strings) and we need to convert them to date format. For this we can use the DATEVALUE() function. The syntax for this function is:

**DATEVALUE(date_text)**

*date_text* argument is a text string.

This function can convert various types of strings. Here are some samples:

DATEVALUE("15MAR") will return 15-03-2009

DATEVALUE("MAR09") will return 01-03-2009

DATEVALUE("15/3") will return 15-03-2009

DATEVALUE("1-MAR") will return 01-03-2009

### Replace value on several Excel files

I’ve written this code for a user that wanted to change a value in hundreds of Excel files that where in several sub folders under a main folder. This code will go through the main folder C:\Test and will replace the value on cell A1 on Sheet1 of every Excel file it will find. You can adapt this code by changing the folder name, cell destination or sheet name.

Sub Change_Value_On_Files()

Dim fso, folder, files, NewsFile, sFolderSet

objExcel = CreateObject("Excel.Application")

objExcel.Visible = TrueSet

fso = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\Test"

Set folder = fso.GetFolder(sFolder)

Set files = folder.files

For Each SubFolder In folder.SubFolders

For Each folderIdx In SubFolder.files

If Right(folderIdx, 4) = ".xls" Then

Set objWorkbook = objExcel.Workbooks.Open(folderIdx)

Set objWorksheet = objWorkbook.Worksheets(1)

Set objRange = objWorksheet.Range("A1")

objRange.Range("A1").Value = "YOUR VALUE HERE!"

objWorkbook.Save

objWorkbook.Close True

End If

Next

Next

End Sub

### VLOOKUP() - Get value based on another value

If we have a table of data, like the one shown bellow, and based on a value of that table want to return the value from another column we can use the VLOOKUP() function.

The syntax for this function is:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

In our example, on cell B10 I've putted this formula:

=VLOOKUP(A10,$A$2:$B$7,2,FALSE)

First we have the *lookup value* that is the value that we want to check is it's on the table. In this example is the value on cell A10.

Then we have the *table array*. This is the range of cells where we want to check for the value on cell A10. In this case, we want to lookup the value on range A2:B7. If it finds the value of A10, it will the return the value referred by the *col_index_num* argument of the function. In this case we want to return the value from column 2 (the employee name).

The *range_lookup* argument is optional and it can have TRUE or FALSE value. If you put TRUE or omit this argument, it will return an approximated match. If it doesn't find the value of the lookup_value it will return the next largest value that is less than the lookup_value. If you put FALSE, it will find an exact match and if it doesn't find one it will return the error value #N/A.

So, on this example, we want to find the value 32893 and get the corresponding value from column 2, in this case the returned value will be James Watson.

If the value specified on the lookup_value is not found, and the range_lookup is FALSE, then the formula will return a #N/A error value. We can change our formula so that it doesn't display this error with this formula:

=IF(ISNA(VLOOKUP(A10,$A$2:$B$7,2,FALSE)),"",VLOOKUP(A10,$A$2:$B$7,2,FALSE))

In this case we use two more functions, the IF and ISNA. I will not explain the IF function in this article, will handle this function on a separated article on the future.

The ISNA() function checks if the VLOOKUP() function returns a #N/A error value. So this formula will check if the VLOOKUP() function returns #N/A. If it does, the cell will be empty. If it doesn't it will show the result of the VLOOKUP function.

### SUMPRODUCT() - Sum values based on conditions

Sometimes we want to sum values from a table based on conditions that we input into another cell. Take a look at the table below. Here we have a table of weekly values for each vendor.

In this case we want to input on cell B7 the week value and get the total for this week on cell C7. This can be done using the SUMPRODUCT() function. The syntax for this function is like this:

SUMPRODUCT(array1,array2,…) you can have up to 30 arrays.

The formula in C7 for this will:

=SUMPRODUCT((B1:E1=B7)*(B2:E4))

Checks on range B1:E1 for the value in cell B7 and sums the values under that value. On this example the value "Week 2" can be found on C1 so the sum will be done with the values from C2:C4 and the result will be 338.

Another example is putting in B9 the vendor name and get the total for this vendor on cell C9. The formula will be like this:

=SUMPRODUCT((A2:A4=B9)*(B2:E4))

Checks on range A2:A4 for the value in cell B9 and sums the values for that row where it finds the vendor. On this example the vendor "Vendor 3" can be found on row 4 so the sum will be done with the range B4:E4 and the result will be 492.

Finally we want to get the value for Week 2 for Vendor 3. We can use also the SUMPRODUCT function like this:

=SUMPRODUCT((B1:E1=B7)*(A2:A4=B9)*(B2:E4))

It will return the value 114 that corresponds to Vendor 3 (row 4) and Week 2 (column C).

These are simple examples of how you can use the SUMPRODUCT function. It can be used in more complex formulas that I will address later on another article.

### About me

I started writing this blog to share with others what I've learned over the years that I use Excel and with the experience in problem solving that I've got from my participation on Excel forums. Hope that you recognize the work I've done here.

**Microsoft Excel Blog:**

**Pplware**(Tech page from SAPO Portuguese portal – articles in PT):

**VLOOKUP Week**:

**Programar Magazine**(article in PT):

**120segundos.net**(Videos in PT): http://120segundos.net/videos/pesquisa?q=excel

### Contacts

Would like to hear from you. If you have some comments or sugestions about this blog, feel free to send me an email. I'll try to answer as soon as possible.

### Consulting

I'm available to do consultant work. Depending on your project, I can take a look and provide a custom quote for it. Each project is a challenge, so I don't work with a fixed fee. I can handle, for instance:

- Worksheets/ workbooks improvement;

- Custom applications development;

- User forms development;

- Excel Integration with other Office programs (Outlook, Powerpoint, etc)

- VBA features to automate your work;

- Worksheet problem solving;

- Excel Training.

You can contact me by email at: jpgpinto (at) gmail (dot) com. Drop me an email and I will find the solution for your problem.