Quick Excel Charts

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”

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

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:

Negative_Vlookup

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:

Negative_Vlookup2

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

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.

Sharing_Workbook1

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

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:

FillDownBlanks1

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:

FillDownBlanks2

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

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:

WEEKDAY1

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”.

WEEKDAY2

This is how it will look like after the formatting:

WEEKDAY3

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

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

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:

Sheetstab1

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:

Sheetstab2

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:

Sheetstab3

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

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…
 
Gridline_Color1

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

Gridline_Color2
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

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:

FILL_RANGE

Count characters on a range of cells

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.

COUNTCHARS

ERROR.TYPE() Function

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:

ERROR_TYPE1

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:

ERROR_TYPE2

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:

ERROR_TYPE3

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

Calculating the difference between two dates

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.

Datedif1

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.

Networkdays

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

Excel2010_FavoriteTipsThis 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”

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:
 
SecretMenus1
 
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:
 
SecretMenus2

Excel Financial Functions

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.

FinancialFunctions1

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:

FinancialFunctions2

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:

FinancialFunctions3

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)

FinancialFunctions4

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.

FinancialFunctions5

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

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:
LARGE1
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:
LARGE2
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:
LARGE3
Hope that this article can be useful for your work.

SUMIF() and SUMIFS() Functions

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”):

SUMIF1

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”:

SUMIFS1

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

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:
Cascading-Validation-Lists1

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.

Cascading-Validation-Lists2

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.

Cascading-Validation-Lists3

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

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:

Cascading-Validation-Lists5

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.

Cascading-Validation-Lists6

This will open the Data Validation dialog box.

Cascading-Validation-Lists7

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:

Cascading-Validation-Lists8

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.

Cascading-Validation-Lists9

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

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:

NumberingRowsTable1

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:

NumberingRowsTable2

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

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. Now click on the Add Watch button in the Watch Window dialog.
WatchWindow1
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.
WatchWindow2
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.
WatchWindow3