This is the last part in a series describing how to use the Hyperlink tool in Microsoft Excel (see the bottom of this post for links to the first three parts). In this part, I will be showing you how to create a link to an email address in your spreadsheet.

When would this be useful? A good example where I’ve seen this used is for contact information. If you have a user working on a spreadsheet you created (say an order tracking form), and they have a question or problem, they can click on the email link and a new message window will pop-up (in their default email application) with the To address and Subject pre-populated.

Once again, I’m going to make the assumption that you know how to open the Hyperlink tool dialog (as discussed in Part I of this series). From this dialog, select the E-mail Address option in the Link to: box. The result is shown below:

Read the rest of this entry »

This is the third part in a series describing how to use the Hyperlink tool in Microsoft Excel (see the bottom of this post for links to the first two parts). In this part, I will be showing you how to create a new document and put a link to it in your spreadsheets.

Personally, I haven’t used this function of the Hyperlink tool that much. However, that doesn’t mean that you won’t ever have a use for it. For example, if you are creating a spreadsheet where you want the user to be able to create a new document and then save it off, you would use this function.

I’m going to skip how to open the dialog, as that was discussed in the first article. Assuming the dialog is open, select the Create New Document option in the Link to: box. The result is shown below:

Read the rest of this entry »

This is the second part in a series describing how to use the Hyperlink tool in Microsoft Excel. Check out the first part in the series here. In this part, we will be exploring how to insert an internal link into your spreadsheets.

Read the rest of this entry »

This is the first part in a series that describes how to use the Hyperlink tool in Microsoft Excel. In this part, we will be focusing on how to insert a link to a web page or file into a spreadsheet.

It can pretty much be assumed that 99% of the people you are working with have access to the internet at work. Because of this, linking to web page content has become a very common practice when creating almost any kind of work product. Also, with the advent of the LAN (Local Area Network), linking to files such as Microsoft Word documents has also become common. In Microsoft Excel, both of these tasks can be accomplished using the Hyperlink tool.

To open this tool, click on the icon circled in red below:

Read the rest of this entry »

The If Conditional is a decision structure that is used not only in Microsoft Excel, but in many other applications as well. It’s pretty much THE basic universal decision structure. The concept is simple: A logical condition is tested, if the condition is satisfied (a True result) then one branch is followed. If the condition is not satisfied (a False result) then another branch is followed. The question is, how to use it in Excel?

Read the rest of this entry »

One of the great things about Excel is that it allows you store multiple rows and columns of data with many different relationships. Take your classic customer information database as an example. You can store all of your customer’s information and the relationships between different pieces of this information (such as all the customers who ship to a certain city, order a certain item, etc.). However, even though the relationships are stored, it may not be readily apparent what they are. For example, say you want to see all of the customers who are based in Denver. If your data has more than a few rows, it could take a lot of time to do this manually. This is obviously not practical. What’s the solution? The answer is simple: The AutoFilter.

The AutoFilter option is quick and simple to use. It allows you to sort through your data by applying drop-down lists to your columns. These drop-down lists are populated with the unique values for that column. Let’s take a look at the following example:

*Note: If this was a real life example, filtering would be overkill as the data set is so small.

Using the above data, how would we filter on the City field to get only the companies that are based in Denver? Follow the steps below:

1. Select the column that contains the City field.
2. Click on the Data menu.
3. Select Filter.
4. Select AutoFilter.

The City field should now have a drop-down arrow on the right hand side of the cell. If you click on this arrow, the following should be displayed:

This drop-down list will be populated with all of the unique values from the selected column. As shown in the above screenshot, the two values displayed are “Denver” and “Miami”, even though there are three rows with data. This is because two of these rows contain “Denver” in the City field. These unique values are the values that you can filter on.

Finally, to show only those companies based in Denver, click on “Denver” in the drop-down list. The following is the result:

As you can see, only the rows that have a value of “Denver” in the City field are now displayed. To undo this, select the All option from the drop down list. All of the rows will now be displayed.

To remove the filter, just click off the AutoFilter option from the Filter menu (basically the same way you turn the AutoFilter on as described above). This will also undo any filter selections made.

You can also use this same technique to filter on multiple rows All you have to do is select the columns you want to filter on when applying the AutoFilter. Using our example, if I wanted to filter on both the City field and the Phone Number field, I would just select both columns before turning on the AutoFilter.

*Note: In the drop-down, there are also options for “Top 10″ and “Custom”. These are a little more advanced than your basic filter, play around with them to see what works for you. I also plan on writing another article on using these options, as this article is just for the basic use of the AutoFilter.

The AutoFilter is a great option because it is so simple to use, and it allows you to sort through large amounts of data very quickly. I use this feature often, especially when I need to get results fast on a large set of Excel data.

This is the second part in my two part Access Vs. Excel series, where I’m basically trying to differentiate when you would want to use one of these tools over the other for a project. This question comes about because of the many similarities that these two tools share. You can see the first article here: Access: Vs. Excel: When to Use Access.

Here are three example cases where I think you would want to use Excel over Access:

• You need to create a one-time, formatted report - If you’re creating a one-time report based on a set of data, then Excel is really the way to go. On the average, setting up a new report model in Access takes longer than creating a new report model in Excel. This is because in Access, you have to setup the database and other backend structures, while in Excel you can pretty much start plugging away from the get-go. If it’s only a one or two time report, you don’t need to take the time to setup a whole database for it. Plug the data into Excel, throw a format at it, and you should be good to go.
  
  
• The relationships/fields won’t change - This basically means that once you put the report together, the structure of your data won’t change, and neither will the relationships. For example, say you are creating a spreadsheet that shows all of your customers and key parts of their company info. This is pulled from a hidden spreadsheet that contains all the info from each company. This is fine, however, if you want to add new fields to the company info, this could cause cascading cell reference problems. Also, if you have some complex relationships between data fields, it’s much harder to check to make sure that they are still intact and correct. As long as your backend is pretty set (relationships and fields), Excel can be a good tool because of the shorter setup time involved.
  
  
• The report format won’t change - Don’t get me wrong, I think that Excel does have some flexibility when it comes to changing an existing spreadsheet. However, once you get past a certain point of complexity, it gets very difficult to make changes that don’t break other parts of the spreadsheet. For example, if you have a bunch of different formulas with cascading dependencies, changing just one requires you to check in multiple different places to make sure that nothing broke. I prefer Access for these kinds of reports. However, if you don’t have to change your format too drastically, I recommend Excel.

It’s critical to understand that both Access and Excel have their uses. Sometimes these overlap, most of the time you can clearly see which one would be better to use. It may not seem like it now, but as you get more familiar with both tools, you’ll see which one is better to use in certain situations. Also, there are always exceptions to the rules. Maybe your client doesn’t have Access, maybe they don’t like to use Excel. You might be forced to use one, all you can do in that situation is roll with the punches.

Another extremely useful Excel function is the AVERAGE function. As I’m sure you can guess, this function will return the average of the values in the cells that you pass into it.

Here is the example I’ll use:

Using this example, here’s the usage for this function:

• AVERAGE(A1,A2) - This will return the average of the values in cells A1 and A2. The returned value would be 13.5.

• AVERAGE(A1,A2,5) - This will return the average of the values in cells A1 and A2, and the number “5″. The returned value would be approximately 10.7.

• AVERAGE(A1:A4) - This will return the average of the values in the range of cells between and including A1 and A4. The returned value would be 12. You’ll see that there is no value in cell A3. That’s ok, because the AVERAGE function will ignore this and just calculate the average based on the populated cells. This is also true any time that a cell in a given range would have a non-numeric value, such as text.

• AVERAGE(A1:A4,5) - This will return the average of the values in the range of cells between and including A1 and A4, and the number “5″. The returned value would be 10.25.

I find the AVERAGE function is nice to use when you don’t know exactly how many cells in a range you’ll want to average (as in the case of possible empty cells, etc.). It’s also convenient when you don’t want to type long formulas with a ton of cell references, and then manually figure out the denominator. I use it on a pretty regular basis, and it saves me a good amount of time and cuts down on the mistakes that manual calculations can cause.

To many people, there is some confusion as to what the difference is between Microsoft Access and Microsoft Excel. To try and clear some of the confusion up, I’m starting to write a series on Access Vs. Excel. In this part, I will be examining example cases where it’s better to use Access rather than Excel.

Here are the three example cases:

• You need to store complex dynamic data and report on it - In this case, you have data that is going to change over time and it’s not straightforward (think many interrelationships). Not only that, but you will need to be able to produce reports on this data even after it changes. While this can be done in Excel, it is usually easier to do this in Access. One reason for this is because it is generally easier to dynamically alter/add/subtract data in Access once you have the database setup. Consequently, because your reports in Access will be based off of database queries, you shouldn’t even have to update the reports to get the new data, you should just have to run the queries. In Excel, you will spend hours deleting rows, adding rows, reformatting your report, and all the other fun tasks that follow, and you’ll have to do this each time you change the data.

• You will need to be able to create custom reports on the fly - This is another huge advantage for Access. Once again, you can design new queries easily in order to create a custom report. All you then have to do is run the queries to get your new report. In Excel, this would be a much more arduous process, as you would probably have to create a new spreadsheet, link the cells, transfer the data, format it, etc.

• You need to store your data for a period of time - If you need to store your data for the long run, Access is the hands down choice. It is much easier to take this data later and put it into other databases or manipulate it in your own database rather than digging through an excel workbook trying to cut and paste what you need, or change it.

Even though Access is the better choice in the example cases above, there are times when Excel is the better option. The next article in this series will deal with these example cases.

While this is a basic Excel function, I haven’t come across a lot of people who know about it. It’s a shame, because it’s very useful. Basically, the COUNTA function will return the number of non-blank cells in a list or array that you pass into it.

Let’s use the following sample data:

Using the above data, here’s the usage for this function:

• COUNTA(A1,A2) - This will return the count of the non-blank cells when looking at A1 and A2. In the above example, this would return two.

• COUNTA(A1:A3) - This will return the count of all the non-blank cells between and including A1 and A3. In the above example, this would return two.

• COUNTA(A1:A5,”Seven”) - This will return the count of all the non-blank cells between and including A1 and A5, and then will add one to this count for the value “Seven”. In the above example, this would return five. You can also use this syntax with specific cells instead of a range, as in the case of COUNTA(A1,A5,”Seven”).

• COUNTA(A1:A3,2) - This is very similar to the example usage above. It will return the count of all the non-blank cells between and including A1 and A3, and then will add one to this count for the value “2″. In the above example, this would return three. You can also use this syntax with specific cells instead of a range, as in the case of COUNTA(A1,A2,2).

I’ve found COUNTA to be a very useful function, especially when you have a lot of data where not all the cells are necessarily populated, and you only care about how many populated ones there are. For example, if you are calculating the average score for each question on a survey, but only want to divide by the number of surveys where this question was answered, then you would use this function to get the number to divide by.