Excel User

Custom Lists

2012-01-24T18:49:00.001Z

Excel custom lists can be used to fill out cells or to sort data in a user-defined order. Excel comes with some pre-defined custom list that can be viewed by going to the File-Options-Advanced menu. Scroll down until you find the Edit Custom Lists button.

When you click the button, a new dialog window will open where you can see the pre-defined custom lists:

This custom list can be anything from the months, to colors, brands, etc. To create a new custom list, just select the NEW LIST on the “Custom lists” field and start typing the entries on the “List entries” window, pressing Enter on each entry to move to the next line of the list. When you’re finished, press the Add button and your new list will be created and will appear on the “Custom lists” window, like on this example, where I’ve created a colors custom list:

You can also also write on a range of cells the values that you want to use to create your custom list and use the “Import list from cells” option where you specify the range of cells that have your data, click on the Import button and your custom list will appear on the “Custom lists” window, like on this example:

Now, let’s see how to use this custom lists. For example, on cell A1, type “Jan”. Then select the cell and drag to the right, by pushing the right bottom little black square that you see on the cell to see what happens.

You will get something like this, depending how far did you pushed the cell to the right:

You can use this method to fill out cells with data from other custom lists that you have on your Excel. You don’t need to start with the first value on your custom list. For instance, instead of inserting “Jan” on cell A1, if I insert “Mar” and push the cell to the right, I would get a list of “Mar, Apr, May…”.

You can use your Custom List to sort data using the order that you defined when you created the list. For instance, I’ve this table of data:

I want to sort the columns by months. I already have a Custom List build with the correct order (“Jan”, “Feb”, “Mar”…) that I can use. Just select the range A1:C2, go to the Data-Sort option on the menu bar and when the Sort dialog box opens, on Row, select “Row 1”, because we want to sort by this row, and on Order select “Custom List…”. This will open the Custom Lists dialog where you need to choose the right custom list to use.

Click OK on both dialog boxed and you should get you table sorted on the correct order: “Jan”, “Feb” and “Apr”.

Avoid Merged cells in Excel

2012-01-20T11:14:00.001Z

This can be a surprise to you, but it’s true! You should avoid using merged cells in a Excel worksheet because it limits it’s future use. Here’s an example. This is how my table originally looks:

My table title is not centered across the table. I can simple select cells B2:C2 and click on the Merge & Center on my Home tab. I can also align “Missed both tests” across C2:D2. My table would look like this:

Definitely better, in terms of look, but if, for instance, you want to sort the test results by the results of Test 1 column, by selecting the range B3:D7 and going to Data-Sort and try to sort by Test 1 column, you will get an error message, like this:

Let’s get back to our original table, without merged cells. To get the same look result, you should consider using “Center Across Selection” instead. Please follow this steps:

1 - Select the cells that you want to “merge”, in this case, cells C2:D2;
2 – Open the Format Cells dialog box (Home Ribbon, Font group, little button in bottom right or right click and select Format Cells from the list)
3 – On the Format Cells dialog box, go to the Alignment tab and on the “Text alignment”-Horizontal, select the “Center Across Selection” option and click OK, like this:

4 – Select cells C6:D6 and repeat the process.

Your table will look like the one with merged cells but now, let’s try to do the sort again by the results of Test 1 column, like you tried before. You don’t get any error now, do you?

This is to show to you that you can get the same look on your worksheets using merged cells and Center Across Selection methods but if you need to use your data to further operations, like sorting, using merged cells, you will be restricted. With Center Across Selection you will not have this kind of problems.

Excel Mashups

2012-01-16T12:19:00.001Z

This is for advanced users, more for web developers that want to create spreadsheet-based mashups. A mashup is just a web page that takes data from existing sources and combines it into something new.
You create an Excel mashup by uploading a workbook to SkyDrive and then embedding it on a web page. Then, you use JavaScript to programmatically interact with that workbook.
I’m not going to explain here how to do this but just point you to the new site from the Excel team (http://www.excelmashup.com/) where you can learn all about it.

Sum every x Row

2012-01-16T11:23:00.001Z

This can be very handy. Assume that you have your data on cells A1:A12 and you want to sum only, say, the values on each 3rd row. Using a SUMPRODUCT() formula like this:

=SUMPRODUCT(--(MOD(ROW(A1:A100),3)=0),A1:A100)

you can sum every 3rd row. You can adjust the 0 like this:

0 will sum every 3rd row
1 will sum every 3rd row starting with row 1
2 will sum every 3rd row starting with row 2

You can also adjust the 3 to change the number of rows to be summed. You can see here an example on the use of the multiple variations of this formula:

Quick Excel Charts

2011-10-24T13:16:00.001+01:00

This is a simple trick. If you have a series of data on your Excel sheet and want to make a quick chart with it, the fastest way is to select your range of cells that have the data and just press F11. This will create a new Chart sheet on your workbook. All you have to do next is to change the type of chart that is displayed and format it to suit your needs.

uCertify–“The fastest way to IT certification”

2011-08-30T11:24:00.001+01:00

I’ve been using the 77-882 Microsoft Excel 2010 exam prep kit from uCertify, a company that provides training exams for IT certifications. uCertify has hundreds of exams covering areas like Microsoft, Adobe, Oracle-Sun, etc. This particular exam is the one to use if you’re trying to get a certification as a MOS – Microsoft Office Specialist on Excel 2010.

To start, I’ve to say that this package has a list price of USD 139.99 but is been sold now for USD 111.98. You can get it from here. After you purchase the kit, you have to install it on your computer. The program comes with a series of Practice Tests, from 15 questions to 40 questions. The questions are multiple choices or for you to pinpoint the location of menu options on a picture. You can set a test duration and a passing score for each test you take. There is also a Study Helper that helps you learn some features of Excel. At the bottom you will also find the Exam Objectives with a list of the topics covered by this exams.

Overall I found it very easy to use. The program as a very nice presentation with all things on the right place. At the end of the exams you can check the questions where you failed and an explanation of the right answer. This exams covers almost all aspects of Excel 2010 and the questions are well written. This is clearly worth the money that is costs and is, in my opinion, the best way to study and prepare yourself for a certification on this area.

Negative VLOOKUP()–return the value to the left of the key field

2011-07-19T18:43:00.001+01:00

VLOOKUP() function only returns values from the columns to the left of the key field. You can’t build the formula and give it a column index number of –1, for instance. This is will result on a #VALUE error, like on the following example:

On this example, we want to retrieve the value on the Date column that matches with the ID on cell B14 so we putted a –1 column index number on the function that didn’t worked.
A simple way of going around this problem is to build our own VLOOKUPNEG (as from negative) function that works with negative column index numbers and can return values to the left of the key field, in this case, the Date value that is on the left of the ID column. So, this is the UDF (User Defined Function) that you can create:

Function VLOOKUPNEG(lookup_value, table_array As Range, col_index_num As Integer, CloseMatch As Boolean)

Dim RowNr As Long

RowNr = Application.WorksheetFunction.Match(lookup_value, table_array.Resize(, 1), CloseMatch)
VLOOKUPNEG = table_array(RowNr, 1).Offset(0, col_index_num)

End Function

Using this UDF, on the same example, we will get this result:

Now, on cell B15, we are already getting the Date that matches the 1002 ID that was on cell B14: 14-05-11!

Sharing a Workbook

2011-07-06T17:14:00.001+01:00

Excel 2010 has a feature for Share Workbook, under the Review tab, on the Changes group but Excel wasn’t designed for having multiple people accessing the same workbook at the same time so, when you share a workbook, it becomes very limited on the things that you can do with it.

For instance, on shared workbooks you can’t have tables. You can’t delete sheets. You cannot merge cells, add conditional formatting to cells, add Data Validation’s, add charts, add pictures, add pivot tables, etc. You will end up with a very basic worksheet to work with.

On top of all of this, shared workbooks tend to became corrupted! It’s only a matter of time. So, my advice is to stay away from shared workbooks. If you want to have multiple people inputting information on the same file, you can create a workbook for each of them and then have a “master” workbook where you link to all of those individual files and compile the information on that “master”.

VBA – Fill Down Blanks

2011-06-15T11:15:00.001+01:00

This is a common problem that many users have: they got a list of data, that has some blank cells in the middle, and they want to fill the blanks with the value from the last cell above. Here’s an example on how your sheet could look like:

As you can see, we have some blank cells that we need to fill with the value from the last filled row above on the Company and Country columns. We would like to obtain this:

For that, we can use this code:

Sub FillSelectionBlanksBelow()
    Dim Cell As Range
    For Each Cell In Selection
        If Cell.Value = "" Then Cell.Value = Cell.Offset(-1, 0).Value
    Next Cell
End Sub

Then you just need to select the range where you want to apply it (in this case A2:D11) and run the macro and you will get the blanks cells filled down.

Display Weekdays names

2011-05-01T17:00:00.001+01:00

This is a common task. We have a list of dates and we want to get the corresponding weekdays. We can use the WEEKDAY() function to get it and the result will be like this:

On column A we have our dates and on column B, using the WEEKDAY() function we get the number corresponding to the weekday. But this is not a good way of displaying the weekday, we would like it to display it as the name of the weekday, not a number, right? There are numerous ways that we can change that. I’m going to show you two of them.

The first, and simplest way, is to format column B with a custom format. For that, just select the values on column B and right-click to select Format Cells and on the Number tab, choose the Custom Category and insert Type as dddd. This will give the names of the weekdays like “Sunday”. If you insert a type ddd it will show you the names like “Sun”.

This is how it will look like after the formatting:

Another way you could do this is using the CHOOSE() function with a formula like this on cell B2 and then copy down:

=CHOOSE(WEEKDAY(A2),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

The result will be the same. This last method is just to show you that there are lots of ways of doing the same thing in Excel.

Change font on all cell comments

2011-04-18T10:08:00.001+01:00

This is a simple code that is very useful if you want to change the font size on all comments that you have on your Excel sheet.

Sub ChangeCommentsFont()
    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
        With cmt.Shape.TextFrame.Characters.Font
            .Size = 11
        End With
    Next cmt
End Sub

You can change this code for setting a new font name, font color, etc.

Moving around Excel sheets

2011-04-13T18:44:00.001+01:00

Excel lists all of your sheet names on the Sheets Tab, on the bottom of your page, all except the ones that are hidden. For example, you can have your sheets tab like this:

You can change your sheet just by clicking your sheet name on the sheets tab bar and if the sheet name isn’t visible, you can scroll using the little arrows located on the left side of your sheets tab. You can also more around your sheets by using Ctrl+Page Up or Ctrl+Page Down. On this example, we have a sheet for each month (January to December) but it only displays January to May. That is because there isn’t enough space to display all sheets names. You can adjust the space my moving the separator to the right to increase the space for the sheets names but it can happen that even so, there isn’t space for all of your sheets, as you can see on this example:

On this case, you can right click on one one the navigation arrows that are on the left side of your sheets tab and it will display a pop-up window with all of your sheets names where you can select the sheet to where you want to go. It will show up like this:

I bet that this little trick will save precious time when you’re working with large workbooks that contains dozens or even hundreds of sheets!

Change Worksheet Gridlines Color

2011-04-08T11:43:00.002+01:00

By default, gridlines on Excel sheets are black but you can change this. So instead of having your sheet display black gridlines, like the ones on this image…

…you can have your gridlines with another color, like this example:

To change the gridlines color, just go to your File tab and select Options. Then on your Excel Options dialog window, select the Advanced tab and scroll down until you find Gridline color option. Just select the color you want and click the OK button and you’re done.

Quickly fill a range of cells with a value or a formula

2011-04-05T10:53:00.001+01:00

If you want to fill a range of cells with a value, a formula or random numbers, there is a faster way of doing it using Ctrl+Enter instead of just hitting the Enter key. To fill a range of cells with a value, follow this steps:

1. Select the range of cells that you want to fill
2. Enter the value or formula
3. Instead of hitting Enter, press Ctrl+Enter

Here’s an example on how to fill a range of cells with random numbers using this tip:

1. Select cells A1:E10
2. Enter this formula: =RANDBETWEEN(1,10)
3. Press Ctrl+Enter

This will fill the range A1:E10 with random values between 1 and 10, like this:

Count characters on a range of cells

2011-04-05T10:03:00.001+01:00

You can use the LEN() function to get a count of characters from a value on a cell like this:

=LEN(A1)

But if you want to get a count of characters from a range of cells, if you try to do something like this =LEN(A1:B6) you will get an #VALUE error. The trick here is to use SUMPRODUCT() function to perform the count of characters using a formula like this:

=SUMPRODUCT(LEN(A1:B6))

You can see here, on this example, the result of this formula on a sample sheet.

ERROR.TYPE() Function

2011-04-04T11:28:00.001+01:00

This function returns a value corresponding to the type of Excel error. This is the syntax:

ERROR.TYPE(error_val)

error_val is a reference to a cell that has a formula that you want to check if it returns an error value. Here are the possible return values:

This function can be very useful to check if a certain formula returns an error, so that you can display a custom error message to the user. For that you can use the IF() function combined with the CHOOSE() function, like this:

=IF(ERROR.TYPE(A2)<=8,CHOOSE(ERROR.TYPE(A2),"Ranges not intersecting","0 Divisor","Wrong data type","Invalid cell reference", "Unrecognized range or function name","Number error","Inappropriate function argument","Waiting for query data"),"")

This formula will check if there is an error on cell A2 formula and if it returns a ERROR.TYPE value will choose the appropriate error message to display on the cell where this formula is placed. If there is no error, it will return nothing. Here are some examples of the use of it:

You can improve this formula to give you the cell address where you have the error by using the CELL() function, like this:

=IF(ERROR.TYPE(A3)<=8,CHOOSE(ERROR.TYPE(A3),"Ranges not intersecting","0 Divisor","Wrong data type","Invalid cell reference", "Unrecognized range or function name","Number error","Inappropriate function argument","Waiting for query data")& " on cell " & CELL("address",A3),"")

This will return a message like this instead:

Hope this is useful to get your worksheets more user friendly.

Calculating the difference between two dates

2011-03-22T10:49:00.001Z

The easiest way to calculate the difference between two dates in Excel is to use the DATEDIF() function. The syntax is like this:

DATEDIF(start_date, end_date,unit)

start_date and end_date are just the starting date and finishing date from where you want to calculate the difference. unit is the date unit to be used on the result. It can be:

y – number of years between start_date and end_date
m – number of months between start_date and end_date
d – number of days between start_date and end_date
md – difference in the day component between start_date and end_date. This means that the years and months are not included in the calculation
ym – difference in the month component between start_date and end_date. This means that the years and days are not included in the calculation
yd – number of days between start_date and end_date, with the year components excluded from the calculation

So, to give you an example of this function works, lets calculate how many days from now until Christmas.

We are calculation the difference between today (cell D2) and Christmas day (cell E2). This tell us that there are 278 days until Christmas. We could calculate how many working days are left until Christmas. For that we could use the NETWORKDAYS() function:

NETWORKDAYS(start_date, end_date, holidays)

start_date and end_date are just the starting date and finishing date from where you want to calculate the difference. holidays are the dates to exclude from this because they are not working days, they are public holidays. You can set a range where you put this holidays to be excluded. On this next example we have on cell B2 a formula to calculate just the working days until Christmas that gives us 199 days.

On cell B3 we have the formula:

=NETWORKDAYS(D2, E2, E4:E6)

This will also calculate the number of working days between today value on cell D2 and Christmas day on cell E2 but excluding the dates that you put on range E4:E6 that can be public holidays or days where you don’t work for some reason. On this case, as 1 May is a Sunday, it is already excluded from the NETWORKDAYS calculation, the formula will only exclude the other 2 public holidays giving us a result of 197 working days until Christmas.

Recommended Book - John Walkenbach's Favorite Excel 2010 Tips and Tricks

2011-03-11T11:18:00.001Z

This is a great book from John Walkenbach, also known as “Mr. Spreadsheet”, that will teach you some tricks and will give you great tips on how to do things in Excel 2010. With John Walkenbach's Favorite Excel 2010 Tips and Tricks book you'll get a jump start on mastering the extensive changes to the 2010 version of Excel. This is in my opinion ones of the top books that you should have if you want to master Excel, specially, the 2010 version.

Excel “Secret Menus”

2011-03-07T10:47:00.002Z

There are two “secret menus” that I bet almost all of you never saw on an Excel Sheet!
To see the first one, let’s click on a sheet cell and then move the mouse over any border of the cell until the mouse pointer changes to a set of arrows. Then right click it and drag to the destination cell and release the mouse. A set of options like this will appear:

To see the second “secret menu”, put a date on a cell and move the mouse over the bottom right corner of the cell until the mouse pointer changes to a small filled black cross. Now you can right click it and drag to the destination cell. A menu with a set of options will appear:

Excel Financial Functions

2011-02-21T11:38:00.002Z

Excel has a powerful pack of financial functions that you can use. Most of them are only used by people with specific needs, but some can be useful for the majority of people to make calculations on loans for instance. One of the most useful functions is PMT(). It can be used to calculate the monthly payment on a loan. Here’s the syntax:

PMT(rate, nper, pv, fv, type)

rate is the interest rate for the loan

nper is the number of payments for the loan

pv is the present value, is the loan amount

fv is an optional value. It can be the final payment for the loan. If omitted, it will assume 0

type can be either 0 or 1. It indicates when the payments are due. Default value is 0 assuming that the first payment is due after a month or you can set this to 1 if the first payment is to be done when the loan is issued.

Let see a practical example how we can use this function to calculate the monthly payment on a $30,000 loan, with 5% annual interest rate and 72 monthly payments.

As you can see on the image, on the formula bar, we have our annual rate divided by 12 because we want the monthly payment. The loan value need to be negative because this is a value that we are going to take “out of the bank”, we are spending it. This formula will result on a $483.15 monthly payment witch will give us a total payment of $34,786.65 over the 6 year period of the loan. Don’t forget that you have to add taxes and insurances values to this monthly payment.

We can use another version of our formula. Imagine that we want to leave $10,000 for the end of the loan period. We can put this value on the pv argument of our formula and we will have this result:

If you check the formula on the formula bar, you will see that I’ve added the B7 cell as fv argument. So now we have a $363.77 monthly payment with a final value of $10,000.

No let’s do another kind of exercise. We know that we are making a monthly payment of $500 on a $30,000 loan amount and for 72 months. We want to figure out what is our annual interest rate. For that we need to use the RATE() function. Here’s the syntax:

RATE(nper, pmt, pv, fv, type, guess)

pv is the present value, is the loan amount

guess is your guess of what the rate will be. If omitted, it will assume that the rate is 10%.

Here’s how example would look like:

Again, if you check our formula, we are multiplying the rate value for 12 months to get the annual rate. This formula will give us an annual interest rate of 6,2%.

But if we needed to calculate how much we could buy with a $500 monthly payment over a 72 months period at a 5% interest rate? We need to calculate our “present value” (pv), right? For that we have the PV() function:

PV(rate, nper, pmt, fv, type)

As you can see, we could afford to spend as much as $31,046.39 with those conditions.

Another useful financial function is FV(). It can be used to calculate a future value of an investment. Here’s the syntax:

FV(rate, nper, pmt, pv, type)

pv is the present value, or the money that you already have on the investment

Here’s an example of a retirement plan for a person that is 30 years now, is planning to retire at 65. This will give us a total of 420 monthly payments (35 years * 12 months). He plans to save $100 a month and he will have a 3% annual interest rate on this financial product. He started with a $5,000 deposit.

As you can see, he will have, when he will retire at 65 years of age, $88,425.91 on he’s savings account. If he started without making a deposit, you could omit the pv argument of the function.

Excel has much more financial functions but most of them are aimed to professionals that need to calculate investments analysis, depreciation calculations, etc, they are not aimed for the general Excel user. Maybe later we can see in detail some of them and how you can use them. For now, I hope that you find this basic financial functions useful.

LARGE() Function–Get the top N values from a list

2011-02-15T20:53:00.002Z

This is a real situation that many of us already experienced! We have a list of data and we need to get the top N values from that list. Of course we can sort descending the list and get the top values, but many times we can’t sort the source data (it may be used on another report, for instance). We can use the LARGE() function to get the nth largest value from a range of values. The syntax is like this:

LARGE(array, k)

array is the range of data from where we want to get our top values
k is the position on the range of data to return

So, let’s see an example on how to apply this function to get our top values. Our source data is like this:

In this example we want to get the Top 5 companies by Invoices value. We will be putting the values on cells B17:C21.

To start, let’s put the numbers 1 to 5 on cells A17:A21. Now, on cell C17 let’s put our formula, like this:

=LARGE($C$3:$C$15, A17)

We will be looking on cells C3:C15, where we have our Invoices values, for the first largest value, that why we are using cell A17 that has the number 1 on it. Use the dollar signs on the array argument so that when we copy the formula to the next rows it will keep the reference to our Invoices values that are on C3:C15. Now copy the formula on cell C17 to the rest of the cells (C18:C21). This is how our sheet should look like:

So, now we have the top 5 Invoices values but wee need to fill the companies names. For that we will use the MATCH() function to get the row number within range C3:C15 that has the value that matches C17. The MATCH formula will be like this:

=MATCH(C17,$C$3:$C$15,0)

For the value in C17, this formula would return 10 because the value on this cell is coming from row 10 of our range source. This is not row 10 of our sheet but row 10 of our source! Now with this information we can get the company name that is on this row. For that we will use the INDEX() function like this:

=INDEX($B$3:$ B$15,10)

This would get the company’s name on row 10 of our source data.

Combining both formulas, it will look like this:

=INDEX($B$3:$B$15,MATCH(C17,$C$3:$C$15,0))

Put this formula on cell B17 and copy to the other cells on B18:B21.

The result will be like this, where we get on cells B17:C21 the top 5 values of Invoices from our source data on B3:C15:

Hope that this article can be useful for your work.

SUMIF() and SUMIFS() Functions

2011-02-12T11:03:00.002Z

To get the sum of values in a range of cells, we can use the SUMIF() function. The syntax is like this:

SUMIF(range, criteria, sum_range)

In this example we want to get the sum of sales from a region (“North”):

As you can see on the formula bar, on cell E10 we have the following formula:

=SUMIF(B3:B8,”North”,E3:E8)

This will check on cells B3:B8 for the North region and will sum the values that correspond on cells E3:E8.

But if we wanted to get the sum of the North region for only a customer? Before Excel 2007, we needed to use the SUMPRODUCT() function. Now we have a new function in Excel that is called SUMIFS(). With this new function, we can have 2 or more conditions. Here’s the syntax:

SUMIFS(sum_range, criteria_range1, criteria1 [,criteria_range2, criteria2…])

The first argument is the range of cells to be summed. Then you should specify pairs of arguments that consist on the range to be check and the value to be match on that range. So, here’s an example of the sum of sales for the North region for customer “TMA, Inc”:

If you check the formula bar, our formula will sum the values on cells E3:E8 that have the region North on cells B3:B8 and the customer “TMA, Inc” on cells C3:C8.

When moving from SUMIF to SUMIFS, you should pay attention because the syntax is in a different order.

Cascading Validation Lists

2011-02-02T20:34:00.002Z

Here’s the scenario: on our worksheet, we want to choose a Country and the corresponding City. We want to limit the countries available to the user as well as the cities that they can choose. For start, we want to choose the country and on the second cell we can only put cities that correspond to that country. It makes sense, right?
So, let’s start we our simple example on how we can make cascading (or dependent) Validation Lists. In cell A2 we want to put the country and on cell B2 we want to put the city, like this example:

To start, we have to build our “database”. On a second sheet, I’ve made a list of countries that I want to have available for the user to choose on my validation list and I putted and index number that will correspond to the right cities list. So, for instance, for France, I used the index 2 that will be corresponding to the cities list 2. You will see after how this index number will be used.

Now, if you’re using Excel 2010, you can have the “source” lists on a separated sheet but if you’re using Excel 2003 for instance, you can only have the values on a second sheet if you use named ranges. We will use the name ranges so that this cascading validation lists work on both versions. So, the next step is to create the named ranges for our lists. On your Formulas tab, we have the Name Manager button. When you click it, it will open the Name Manager dialog box.

Click on the New button to add a new named range and it will open the New Name dialog box.

On the Name field you can put, for instance, “Country” for our first named range and on the “Refers to” field we will select, in this case, =Sheet3!$A$3:$A$5 to get the list of countries. Click OK to finish. Then we need to create the named ranges for the rest of the lists. Click again on the New button on the Name Manager dialog box and repeat the process to create three more named ranges: England, France and Portugal, selecting for the “Refers to” field the corresponding range of cities names. This will leaves with 4 named ranges, like this:

Now we can build our validations lists on our original sheet where we want to select the data from our “database”. With cell B1 selected (where we want to select our country), go to the Data tab and click on Data Validation and choose Data Validation.

This will open the Data Validation dialog box.

Now we want to use a list of options for the user to select so go ahead and on “Allow” change the value to “List”. This will open the Source field that is where we will put our Country named range. So put =Country and click OK. Now we want to make the second Validation List for the user to choose the city. Select, in this case, cell B2 that is where we will put this second validation list. Now comes the trick. We need to check the value of the country cell (cell B1) to get the index of the corresponding value on our “database”. If the user selected Portugal we need to get the index “3” that will tell us to get the list of cities that correspond to Portugal. For that we will make a VLOOKUP of the value in cell B1 in the Countries table. After we get the index value, we will “choose” the list to use for the Source of our second validation list. To make this we will use this formula:

=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)

So, what does this formula do? It will start for looking up the value on cell B1 against the values on our countries table and getting the corresponding index that it will use on the CHOOSE() function to get either the 1st, 2nd or 3rd named range.

This is how our second validation list will look:

The result will be two cascading or dependent Validation Lists. If we choose country France, we will have on the cities Validation List the cities from France only.

This article demonstrates how you can make basic cascading Validation Lists to use on your sheets. You can take this example and use it on your own real life situations.

Numbering rows in a Table

2011-01-27T11:22:00.002Z

When you insert a table on your sheet (by going to Insert-Tables-Table), you may need to number the rows. When you create your table, it will look like this:

In this case we want to put and order number on column B. For that we just need to add the following formula into cell B5:

=SUBTOTAL(3,C$2:C4)

The result will be like this:

The formula uses the SUBTOTAL function with the argument of 3 to COUNTA. This will give the count of all cells in column C, starting to count from the header (C2) and ending on the row that is one cell above the one with the formula. For instance, on cell B5, if you check the formula, it is counting the number of rows from C2 to C4, thus returning the result of 3.

This formula will be updated even if you sort or filter your table data.

Excel 2010–Watch Window

2011-01-24T11:35:00.002Z

Ever needed to keep an eye on a single result of a formula on one sheet while working on multiple sheets? You needed to be constantly switching from sheet to sheet to check the result every time you changed a value, right? No need for that anymore because Excel 2010 introduces the new Watch Window.
The Watch Window button can be found on the Formula tab, Formula Audition group. Click it and a Watch Window dialog will open. No click on the Add Watch button in the Watch Window dialog.

A new dialog window will open for you to select the cell that you want to watch. After you select the cell, click on the Add button to close the dialog window.

You can repeat theses steps for all the cells that you want to check. After that you position you watch window on top of your sheet or you can position it where it doesn’t cover your sheet, on the top of it, bottom, left or right.
This way you will have a Watch Window that will show you the result of the cells that you’re tracking, without having to switch to the sheet where the formula is to check is result.

PDF to Excel

2010-11-05T10:53:00.004Z

I’m using a new application called PDF2XL from Cogniview to convert data from a pdf file to an Excel file. The PDF2XL OCR version that I’m using can also use scanned PDF files as well as native ones. You can convert the data from the pdf file into Excel or even into Word or Powerpoint.

The software is very easy to install and to use. When you open the program, to convert a part of your pdf file, you just need to open your pdf file inside the application, select the area that you want to convert and hit the Convert option on your menu bar. In seconds your data will open inside Excel. That’s it! With just a few mouse clicks your data will be converted into the application that you’ve chosen. Nothing is easier that this!

There are 3 versions of the software to choose from:

With prices starting at $97 USD for a single license I think that this is the best and most popular software on the market to convert data from pdf files into Excel.

Excel 2010 Multithreaded Calculation

2010-10-28T19:25:00.002+01:00

Since Excel 2007 version that Excel is prepared to take advantage of dual-core (or more) machines. With Multithreaded Calculation, Excel finds formulas that can be calculated simultaneous and calculates them on multiple processors at the same time.
To activate this function, you need to go to File-Tools and on the Advanced category scroll down until you find the Formulas section. Check the “Enable multi-threaded calculation” and select the “Use all processors on this computer”, as shown in this picture.

Excel Charts – Use an Image as Line Chart Markers

2010-10-23T17:02:00.002+01:00

Excel line charts can have some different line markers but you can also use images to personalize your charts and give them a different look. When you create a line chart, it will look something like this simple example:

Here we have some diamond type line markers but we want to change this for a dollar sign marker. To do this we start by adding an image to our worksheet by going to Insert tab on our menu, then on the Illustrations group we can choose to add a Shape, Clipart or a Picture. Then format the image to a size that is suitable to the chart where you want to place it. Select the image and press Ctrl+C (Copy). On your chart, click on the line series to activate it. The line markers on your chart will be selected. Press Ctrl+V (Paste) to copy the image to your line markers and that’s it. Your chart will look something like this:

You can also have different images for each line marker. For instance, I could put green dollar signs on the months where the sales values increased and red dollar signs where the sales values decreased. When you Paste your image to your line markers, you can choose only the ones that you want to change the images instead of all the line markers.

Evaluate Formulas in Excel 2010

2010-10-19T11:19:00.002+01:00

Formulas in Excel calculate in a blink of an eye. Sometimes you have the needs to see what is happening in your formula calculation. For that you can use the Formula Evaluate function in Excel 2010. On the following example we have a simple formula on cell C1, has you can see on the formula bar.

If you select the cell where the formula is and go to the Formulas tab on your menu bar, go to the Formula Audition group and select the Evaluate Formula option, has shown on the image below. This will open a Evaluate Formula dialog box where you can evaluate your formula.

You have an Evaluate button on the bottom that you can click to calculate the underlined portion of your formula (in this case A1). You can use the Step In and Step Out button to go in detail of the underlined portion of your formula.

You can also evaluate just a portion of your formula instead of the entire formula. For that, on the formula bar, use the mouse to select the portion of the formula that you want to evaluate. In this example, we just want to evaluate the A1+A2 portion of our simple formula.

Then press F9 and Excel will just calculate the portion of the formula that you’ve selected, as shown on the next image.

This will display 257 on the formula bar that corresponds to the sum of A1+A2 cell values. Don’t forget to press ESC to exit the formula after you use this method because if you press Enter, that portion of the formula will stay as the calculated value, in this case, it will keep the 257 value instead of the A1+A2 formula.

Get First and Last Word from a String

2010-10-16T18:45:00.004+01:00

Maybe you already had the needs to extract the first and last name from a string containing the complete name of a customer. I know I did! This is a good example of the application of this article.
To get the first word from a string, we need to find the first space on the string. For that we can use the FIND() function. This function works from left to right so is perfect for finding the first space on the string. Then we want to retrieve the word to the left of the fist space. For that we will use the LEFT() function. Here’s how we can build our formula:

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

Because we can be dealing with different strings and some may not have a space, we need to check for errors on our formula so we should use the IFERROR() function on Excel 2007 and 2010 like this:

=IFERROR(LEFT(A2,FIND(" ",A2)-1),"")

This way, if we find any error on our formula, instead of showing #VALUE on the cell, we just leave it blank when this happens. In previous versions of Excel the IFERROR() function doesn’t exists so we need to use the ISERR() function that is compatible with all Excel versions. Our formula needs to change to this:

=IF(ISERR(FIND(" ",A2)),"",LEFT(A2,FIND(" ",A2)-1))

To get the last word from a string is not so easy because what I’ve mentioned earlier, the FIND() function works from left to right so we need to find the last space on the string and get the text to the right of it. Here’s the formula:

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

As in the previous formula, we need to check for errors on our formula to avoid the #VALUE error message, so our formula turns to this:

=IFERROR(RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),A2)

To have a formula that is compatible to all Excel versions, we need to change our formula to this:

=IF(ISERR(FIND(" ",A2)),"",RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

This will produce something like this example:

An alternative to Cell Comments

2010-10-16T11:48:00.002+01:00

You can put a comment in any Excel cell by right clicking on the cell and selecting the Insert Comment option or by going to the Review menu tab and on the Comments group, select the New Comment.

Here you can also choose Show All Comments to show or hide the comments on your sheet. On the File-Options-Advanced under the Display group, you have also some options to change the way your comments appear (or not) on your sheet. As we saw on the Change the Shape of a Cell Comment article, comments can have many different looks.

Excel provides an alternative to cell comments. You can use Excel’s Data Validation feature to display a pop-up message when a cell is selected. This way your message will always show up to the user even if the cell comments are disabled.
Do put a message on a cell using Data Validation, just select the cell where you want to put it and go to the Data menu tab, then Data Validation and select Data Validation option.

This will open the Data Validation dialog box. Under the Input Message tab you can fill the Title for your pop-up message (optional) and fill the Input Message field. This is the message that will appear when you select the cell. Don’t forget to leave the “Show input message when cell is selected” check box selected.

As you can see on the image above, the result will be a pop-up message when the cell (in this case cell A1) is selected. The pop-up message can be dragged to a different location.

Recommended Book - Excel 2010 in Depth

2010-10-10T22:28:00.000+01:00

The book that I'm actually reading is Microsoft Excel 2010 In Depth from Bill Jelen. It's a +1,100 pages book that is well written and covers all of the main features of the new Excel 2010 version. For a big book I found it very easy to read because the book is visually attractive, with good and simple examples.
The book is divided under 5 main categories:

Changes in User Interface
Calculating with Excel
Business Intelligence
Visual Presentation of Data
Sharing

I definitely recommend that you read this book to get to know Excel 2010 in depth because there are so many new things to discover that you will need a big book like this one to discover them all.

Excel 2010 – Introduction to PowerPivot

2010-10-10T16:37:00.002+01:00

PowerPivot is a new free add-in for Excel 2010. It’s probably the most expected feature in the new version of Excel because it turns it into a powerful Business Intelligence tool. These are some of the major benefits of PowerPivot:

While Excel grid goes until row 1,048,576, has we’ve seen on the Excel 2010 Grid post, with PowerPivot you can handle millions of rows of data. You can sort, filter, scroll and pivot that amount of data using PowerPivot.
You can create pivot tables with data from multiple tables.
Import data from different sources like Access, RSS, SQL Server and show the data into a single pivot table.
DAX – Data Analysis Expressions is the new formula language in PowerPivot. DAX has 117 functions for two types of calculations: 81 normal Excel functions and 54 new functions mainly for data analysis.
Excel workbooks with PowerPivot are smaller than the ones that use traditional pivot tables.

There are also some negative aspects on using PowerPivot on your workbook:

You can’t Group on pivot tables.
You can’t use VBA on PowerPivot as you could with pivot tables.
PowerPivot can’t be used with other versions of Excel, only the 2010 version.

Excel 2010 – Handling Duplicates

2010-10-06T12:30:00.002+01:00

In previous versions of Excel, removing or highlighting duplicates was not a natural thing of Excel, you would need some tricks to do it. Excel 2010 now provides a tool to handle this easily. Take a look at this sample data:

As you can see, there are some Customers and Products duplicated on our table. We want to get a list of unique customers. For that we need to copy the data from column A to another section of our worksheet (in this case the range A1:A7). Then select a single cell within the data set. Go to your menu bar and on the Data tab click on the Remove Duplicates button. A Remove Duplicates dialog box will open like this:

Excel will assume that you have headers on your data and will check the “My data has headers” check box and the name of your columns will appear on the list below. Here is where you will select the fields that you want to make unique records. In this case it’s just the Customer field. Click OK to close the dialog box and you will get a message with the number of duplicates found and remove and the number of unique records that remained.

In this example we used just a single column to check for duplicates but we could check for duplicates using more fields. We could check for unique combination of Customers and Product. For that, on the Remove Duplicates dialog box, just select both columns from the list.
Instead of removing your duplicate records, you may want to just highlight them on your sheet so that you can see witch are duplicated. You can do that by using Conditional Formatting option under Home tab. Please check the Highlight Duplicates article for that.

Excel 2010 Grid

2010-10-06T11:42:00.002+01:00

In Excel 2003 we had only 65,536 rows and 256 columns. This would gives us 16.7 million cells to work.
In Excel 2010 we now have 1,048,576 rows and 16,384 columns. This more or less 17.1 billion cells on each worksheet witch represents a 102% increase over the old version.
Columns in Excel 2003 where labeled from A to IV. In Excel 2010 they are labeled from A to XFD. This change means that some old range names such as YTD2005 or TAX2006 cannot be used anymore because now they are valid cell addresses. Excel automatically changes this name ranges during conversion. For example, a range name such as YTD2005 will be changed to _YTD2005.

Delete all pictures or charts on Worksheet

2010-10-04T11:52:00.002+01:00

This is a tip on how you can delete all pictures and/or charts on a worksheet without having to manually select each one and delete them.
So, you can use the F5 shortcut key on Excel to open the Go To dialog box.

Then click on the Special button on the bottom of the dialog box to open the Go To Special dialog box.

Select the Objects option and click the OK button to close the dialog box. Now you have all of the objects on your worksheet selected. Check if you don’t have any more objects on your sheet that you don’t want to delete, like a company logo or something, before you press Delete key to delete all of them at the same time.

Excel Shortcut Keys – Part I

2010-10-04T11:36:00.002+01:00

Excel has very useful shortcut keys that helps you save time while inserting data on your worksheet. This is the first of a series of posts about Excel Shortcut Keys.

Ctrl + Shift + ; Enter the current date on a cell
Ctrl + B Bolds highlighted selection on sheet
Ctrl + I Italics highlighted selection on sheet
Ctrl + U Underlines highlighted selection on sheet
Ctrl + Z Undo last action
Alt + = Create a formula to sum all of the above cells
Ctrl + Shift + $ Formats the selected cells as currency format
Ctrl + Shift + # Formats the selected cells as date format
Ctrl + Shift + % Formats the selected cells as percentage format
Ctrl + Space Selects the entire column where the cursor is placed
Shift + Space Selects the entire row where the cursor is placed

Change the Shape of a Cell Comment

2010-10-01T19:08:00.002+01:00

Cell comments is used very often on worksheets but they look always the same way because most users don’t know that they can change the shape of the comment. Instead of being this normal yellow rectangle, it can have lots shapes and colors.

To change the comment shape, make sure that the comment is visible by right-clicking the cell where the comment is placed and choosing Show/Hide Comments options. Then just select the comment border and, in Excel 2003, go to the Draw menu and choose the Change AutoShape option and select the shape you for your comment.

For Excel 2010, you have to add the Change Shape button to your Quick Access Toolbar (QAT). Click on the right arrow on your QAT to open the Customize Quick Access Toolbar, like shown on the picture below. Click on the More Commands option to open the Excel Options dialog window.

On the “Choose commands from” dropdown, select the Drawing Tools Format Tab. On the bottom list, select the Change Shape button and click the Add button. Click OK to close the Excel Options dialog box. Your Change Shape button will appear on your QAT like this:

Now that you have your button, you can select your comment border, click on the Change Shape button and select the shape that you want for your comment. the result can be something like this:

Don’t forget that you can also change the fill color of your comments, the color and weight of your comments border, as well as many other customizations that you can do to your comments for them to stand out on your worksheet.

Excel 2010 – Sparkline Charts

2010-09-25T22:17:00.002+01:00

Excel 2010 has a new feature called Sparkline charts that allows you to place a mini-chart inside a cell on your sheet. There are 3 type of sparkline charts: line, column and win/loss. The line and bar charts are very useful to show you a trend about your information while win/loss charts show you positive or negative numbers across a horizontal axis, represented by squares that show positive values if they are above the horizontal axis or negative if they appear below.
To create a sparkline chart, just click on the cell where you want your chart to appear. Click on the Insert tab on your menu bar and click on the type of sparkline chart that you want. A “Create Sparklines” dialog window will appear. The cell that you’ve selected on your sheet will already appear on the field “Location Range” of the dialog window. Click on the “Data Range” field and select on your sheet the range that you want to use on your chart. The range will appear on the “Data Range” field. Click OK and you’re done.
On this picture, you can see on cell F3 a line sparkline chart and on cell G3 a bar sparkline chart.

On the next picture, you can see on cell F3 an example of the win/loss sparkline chart.

Create a Self-Expanding Chart

2010-09-20T11:20:00.002+01:00

This happens very often with most of the Excel users. You have a set of data that you use to create a chart. If you latter want to add more data to the chart, you need to redefine your chart data series, unless if you didn’t add rows or columns in the middle of your original data where in this case, Excel will assume them on your chart.
To avoid this, just create your chart as usual. Then select any cell inside the range of data that you used to create the chart. Go to Insert-Tables to convert the range of data to a table. This is all you need to do for Excel to recognize the data as a table and the data added to the table can be reflected on the chart without the need to redefine your source.

REPT() - Repeat Function

2010-09-16T08:42:00.006+01:00

The REPT() function is unknown for most of Excel users. This function repeats the given text of characters a given number of times. The syntax is like this:

REPT(text,number_times)

text is the text string that you want to repeat. number_times is the number of times you want to repeat the text on the cell.

Example:

=REPT("*",3) repeats the "*" character 3 times on the cell. The result will be "***".

A good use of this function is to make cell "bar" charts that will illustrate the dimension of the number on an adjacent value. Here's how it would look:

On this example, I use the character "■" from the Character Map and used the function like this:
=REPT("■",C3/1000)

I divided the value by 1000 so that the size of the "bar" stays at a reasonable size.

INDIRECT() Function

2010-06-18T21:29:00.004+01:00

The INDIRECT() function returns the value of the cell referenced by a text string. The syntax for this function is:

INDIRECT(ref_text, A1)

ref_text is a reference to a cell in an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If it's not a valid cell reference, it will return #REF!.

A1 is a logical value that indicates what type of reference is on cell ref_text. If A1 is true or omitted, ref_text will be interpreted as an A1-style reference; if A1 is FALSE, ref_text will be interpreted as an R1C1-style reference.

Let's see a simple example of the use of this function. Let's say that we have this workbook that has a sheet for each month of sales.

On cell B3 we want to sum the sales values of the range A1:A10 for the month that we specify on cell B1, in this case "Feb" that corresponds to the Feb sheet on our workbook. For this we can put a formula using INDIRECT() function on cell B3 like this:

=SUM(INDIRECT(B1&"!A1:A10"))

Hide Cell content

2010-04-10T17:17:00.002+01:00

Excel doesn’t have any options to hide the content of a cell, not even the 2007 version, without hiding the row or column where that cell is. There are several ways to bypass this limitation:

Use a special custom number format by right-clicking the cell that you want to hide and open the Format Cells dialog box and on the Number tab select Custom on the Category list. Then on the Type field enter ;;; (three semicolons). Click OK.
Change the font of the cell to the same as the background color.
Add a shape that has the same color as the background on top of the cell that you want to hide the content.

This methods don’t really hide the content of the cell because if you click on top of the cell you can still see the content on the formula bar. You can always hide the formula bar also to avoid this or you can do it like this:

Select the cells that you want to hide the content
Right-click to go to Format Cells or press Ctrl+1 to open the dialog box. Click on the Protection tab
Select Hidden check box and click OK
In the menu bar, go to Review-Protect Sheet
Add a password or press only the OK button

This way the sheet will be protected and the cells are locked. You can’t make changes to the cells. By default, Excel makes all cells “locked”.

Convert Formulas to Values

2010-03-29T20:51:00.002+01:00

One of the most common tasks in Excel is converting formulas to values. In Excel 2003, you could do a Copy, Paste Special-Values. In 2007 version, this operation is easier than ever. You just need to Ctrl+C to copy the range you want to paste the values and, without opening the Paste Special dialog box, you can go to Home, Clipboard, Paste-Paste Values. Then just press ESC to cancel the Copy mode. This way you will be replacing the formulas on your range by the current value that they return.
Instead of copying the values on top of your formulas, you can copy them to another location, leaving the formulas intact.

VBA - Create folders based on names on a column

2010-03-23T11:02:00.003Z

This is a simple thing that sometimes we need to use. We have an Excel sheet that has a list of, for example, names in column A and we want to create folders for each of them on a disk. For that we can make a small VBA code to go through each of the rows on column A and create a folder with that name on the path that we specify on our code. To do that, I came up with this code:

Sub MakeFolders()
    Dim xdir As String
    Dim fso
    Dim lstrow As Long
    Dim i As Long
    Set fso = CreateObject("Scripting.FileSystemObject")
    lstrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lstrow
        'change the path on the next line where you want to create the folders
        xdir = "C:\" & Range("A" & i).Value
        If Not fso.FolderExists(xdir) Then
            fso.CreateFolder (xdir)
        End If
    Next
    Application.ScreenUpdating = True
End Sub

On this example, I will create the folders under C:\ If you want a different place, just change it on the code.

Excel Camera Tool

2010-03-17T22:13:00.004Z

Excel has a hidden feature that most of the users don’t know. It’s the Camera Tool. For Excel 2003, you can get the button for the Camera Tool by going to Tools-Customize, on the Commands tab, find the category Tools. Scroll down until you find the Camera button. Drag and drop the button into one of your toolbars. Close the customize dialog window.

For Excel 2010, you need to make the Camera button appear on the Excel Quick Access Toolbar. Go to Quick Access Toolbar, click on the drop down button and select More Commands, like on the picture below:

This will open Excel Options dialog box. On the “Choose commands from” drop down select “All Commands” to see all Excel commands. Scroll down the list until you see the Camera command. Select it from the list and click on the Add button on the right. Once it appears on the right box, click OK button from the dialog box.

Your Camera button will appear now near the drop down button on your Excel Quick Access Toolbar:
Now that you have the Camera Tool button available, let’s see what you can do with it. This tool can be very useful if you want to display a part of your sheet on another location, without affecting the formatting of that area. Let’s see a practical example. Suppose I have the following table:

I want to show this table on a chart. For that, I will “take a picture” of the table from one of the sheets and place the “picture” on top of the chart that I have on another sheet. Just select the table cells and click on the Camera tool button on your toolbar. Then on the sheet that has the chart just click on one cell and the “picture” of your table will be displayed like if you have inserted a picture on your sheet. You can now work this “picture” the way you want it to look and place it on top of your chart. The final result will be something like this:

This tool is very useful if you want to display tables from different sheets without having to adjust the formatting of the target sheet.

VBA - Put borders around all used cells on a sheet

2010-02-10T10:52:00.002Z

This code is used to put borders around every used cell on a sheet.

Sub DrawBorders()
   With Cells.SpecialCells(xlCellTypeConstants, 23)
       .BorderAround xlContinuous, xlThin, xlColorIndexAutomatic
        On Error Resume Next 'used in case there are no inside borders
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
    End With
End Sub

VBA - How to Autofilter all of your sheets

2010-02-08T09:32:00.001Z

If you want to put Autofilter on all of the sheets on a workbook, you can't do it by select all of the sheets on the sheets tabs. Instead you can use this simple VBA code:

Sub Autofilterall()

    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        Range("A1").Activate
        Selection.AutoFilter
    Next ws
    Application.ScreenUpdating = True

End Sub

VBA - Get Sheets names and Rows count

2010-02-07T16:02:00.007Z

This article is based on a specific question that I was asked. "How to get all of the sheets names and number of filled rows in column A from a workbook in a new sheet?". This is the code I came up to solve the problem:

Sub GetSheetNames_andRowCounts()

    Dim i As Integer
    Dim j As Integer
    Dim SheetCnt As Integer
    Dim lstRow As Long
    Dim ws1 As Worksheet
    Dim SheetName As String
    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
    SheetCnt = Worksheets.Count
    'Add the Target Sheet
    Sheets.Add after:=Worksheets(SheetCnt)
    ActiveSheet.Name = "Target"
    Set ws1 = Worksheets("Target")
    j = 1
    'Combine the sheets
    For i = 1 To SheetCnt
        Worksheets(i).Select
        'check what is the last row with data using column A as a reference
        lstRow = ActiveSheet.Cells(65536, "A").End(xlUp).Row
        'get the name of the sheet
        SheetName = Worksheets(i).Name
        'assign the values to the Target sheet
        ws1.Cells(j, 1).Value = SheetName
        ws1.Cells(j, 2).Value = lstRow1
        j = j + 1
    Next
    With Application
        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    Sheets("Target").Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select

End Sub

VBA - Add photos to cells inside comments

2010-01-25T20:32:00.005Z

I have a sheet with employees numbers and names and I wanted to see the corresponding photo of each employee. I have a folder with the photos of each employee. Each filename is the employee's number. To be able to see the photos, I decided to put each photo inside a comment on each employee number cell. This way, when I pass over the employee number, a comment window will show up with the employee's photo. It will look like this:

For this, I have the following code:

Sub InsertPictures()
    Dim cll As Range
    Dim rng As Range
    Dim strPath As String
    strPath = "D:\Photo Folder"
    With Sheets("Sheet1")
        Set rng = Range("A2:A416")
    End With
    For Each cll In rng
        If Dir$(strPath & "\" & cll.Value & ".jpg") <> "" Then
            With cll
                .ClearComments
                .AddComment ("")
                .Comment.Shape.Fill.UserPicture (strPath & "\" & cll.Value & ".jpg")
                .Comment.Shape.Height = 160
                .Comment.Shape.Width = 120
                .Comment.Shape.LockAspectRatio = msoTrue
                End With
        End If
    Next cll

End Sub

You can adjust the size of your photos by changing the Height (160) and Width (120).

Excel Charts - Hide Zeros

2010-01-14T22:33:00.006Z

If you have a series of data to display on an Excel Chart, like this one:

It will look something like this:

As you can see, the chart will display the zero values. If you want to hide the zeros, you have two ways of doing:

Method 1:
1) Select the range of cells where you have the values for your chart
2) Right click and select Format Cells
3) On the Number tab, on Category, select Custom
4) In the "Type" box you will see the current format for the range you selected
5) Put the cursor on the end of the content of the Type box and enter 0,0;;; it it will look something like this picture:

6) Your zero values will be hiden now

Method 2:
Go to Tools, Options and on the View tab, under Window option, uncheck the Zero values options, like this:

Convert numbers to minutes

2009-11-16T23:08:00.003Z

If you have a series of values like this:

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

2009-11-13T11:08:00.005Z

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

2009-11-05T09:53:00.003Z

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

2009-11-02T22:07:00.002Z

Let's say that I have the following table of data:

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

But I want to display on the top of each bar, the total for the Week (the sum of each Vendor). To do that, I need to add a 4th series to the chart. For that I select the chart and right-click on top of it and select Source Data. On the Series tab, on the Series, click on the Add button to add a new series. For the Values range, select the cells where the values of Total are on our table. For the Name, select the cell where we have "Total". Click OK and you will get a chart 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:

Hope this helps you create better charts in Excel!

VBA – Define dynamic ranges

2009-11-02T11:07:00.003Z

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

2009-11-02T10:20:00.025Z

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.

To count the number of “red” words in column A we can simple to this:

COUNTIF(C2:C9,3)

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

Add-in – Save Excel 2007 file as Pdf

2009-10-31T14:19:00.003Z

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:

http://www.microsoft.com/downloads/details.aspx?FamilyId=4D951911-3E7E-4AE6-B059-A2E79ED87041&displaylang=en

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

2009-10-31T11:27:00.003Z

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

2009-10-28T18:24:00.002Z

When we create a new chart in Excel, if the X axis labels are dates, Excel will assume “Automatic” on the Primary axis Category. Please check image below.

On this example I have dates 1, 2, 5, 6 and 7 of October. Only this dates have data on it.

With the option Category X axis set to Automatic, Excel will produce a chart like Chart 1:

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

2009-10-25T17:35:00.004Z

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

2009-10-25T16:54:00.003Z

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

2009-10-25T16:44:00.002Z

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

2009-10-24T10:58:00.002+01:00

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

2009-10-23T19:02:00.006+01:00

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

2009-10-23T10:32:00.002+01:00

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

2009-10-23T09:29:00.002+01:00

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

2009-10-22T22:18:00.003+01:00

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

2009-10-22T10:58:00.005+01:00

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

2009-10-21T20:16:00.008+01:00

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

2009-10-20T21:35:00.016+01:00

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

2009-10-20T21:14:00.003+01:00

I live in Setúbal, Portugal. I work with Excel for more than 10 years. This tool is so powerful and complete that I'm still learning to work with it. Everyday I discover new fixtures of this tool and ways of doing things in Excel. Just started using Office 2010 and I'm loving!
I'm an active member of the Experts Exchange Comunity. Currently, I'm a Sage in Excel (+600,000 points ). I try to help others solve their problems in Excel and also in other tools, mainly Microsoft tools (Access, Outlook and Visual Basic .Net).

Contacts

2009-10-20T21:03:00.001+01:00

You can contact me by email at: jpgpinto (at) gmail (dot) com

Would like to ear 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.