The word Backend is usually used to refer to the part of a system that the end users or clients don’t see. Take buying airline tickets online as an example. In this instance, the person buying tickets online only sees the web page. What they don’t see is the database behind the web pages and the services that get kicked off when they click “Buy”. This is what we refer to as the backend of the system.

Read the rest of this entry »

This particular tips comes from the fact that I’ve been asked this question several times now in the last few days alone, so I thought it would be a good idea to post it here: How do you put a Carriage Return in-between lines of text in a single cell? In other words, how do you use the Enter key like you would in Microsoft Word?

The answer to this is simple: Use Alt + Enter. When typing in your cell and you want to create a blank line before the next line of text starts, hold down the Alt key and press Enter. This will insert a blank line (like in Word) instead of shifting the focus to the next cell.

As I have talked about in previous articles, there are many areas where Microsoft Access and Microsoft Excel overlap. Because of this, there exists the ability to quickly interchange data between the two programs. In this article, I’m going to go over a few simple steps to transfer all of the data from a table in an Access database to an Excel spreadsheet.

Read the rest of this entry »

When working in a spreadsheet, nine times out of ten you’ll end up inserting a row. In fact, you’ll more than likely be inserting multiple rows at a time. However, if you just highlight where you want to insert and use the Insert command, only one row will be inserted. This can be annoying if you are trying to insert multiple rows at one time. Fortunately, you aren’t consigned to “one row at a time” hell just yet. Microsoft Excel does allow you to insert multiple rows in a single click.

It’s quite simple to accomplish this by following these steps:

Read the rest of this entry »

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 »

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 »

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.

What is that you say?  You already know how to create a Microsoft Excel spreadsheet?  Of course you do.  However, there is a big difference between “knowing how to create the spreadsheet that shows how much you spend on snickers each month” and creating a spreadsheet for work.  While the basic mechanics are the same, there are a few guidelines to follow that will make your Excel spreadsheets sharper and more professional looking to your audience.

Here’s a question I pose to you: What do you name your spreadsheet tabs in your workbook?  If your answer is either “?” or “I don’t name them”, then the following advice applies to you:  You should always name your spreadsheet tabs.  Even if they are going to be hidden, they should get a name.  Here are some suggestions on how to name your spreadsheets:

• Use descriptive names.  Don’t use numbers, letters, etc. A bad example would be “A”, “1″, “A2″.  Instead, make it meaningful to what the spreadsheet contains.  If your spreadsheet is an executive summary, label it “Executive Summary”.  One thing to remember is that while you want to make the names descriptive, you shouldn’t make them too elaborate.  Don’t go overboard, Excel has a limit on the number of characters you can use in a name, and this should cause no problem if you have an optimized (for both descriptiveness and length) name picked out.  This also makes it easier to reference cells in the sheet from another sheet, such as using “‘Executive Summary’!C2″.

• If you have to use abbreviations, make sure that they are common in general or common to your audience.  This is not the time to come up with your own unique abbreviation system.  A good way to test this is to bounce the names off of a co-worker who is not as familiar with your work and see if they can figure out what the abbreviations mean.

• Use proper grammar.  At least capitalize the first letter of the first word in the name.  Sometimes I will capitalize the first letter of each word, it really depends on what you’re working on and what your audience likes.

Another way to trim your presentation is to delete any tabs that are empty.  This is a very easy thing to do, but one that I’ve found most people don’t pay attention to.  You want all the focus to be on the tabs with content, you don’t want your audience having to search through empty tabs to get the spreadsheets that are relevant. 

While we’re on the subject of tab management, make sure that your tabs are in a logical order for the information you are presenting.  Don’t put a summary tab in the middle of all of the spreadsheets, nobody will find it and you will just get flooded with emails asking why it isn’t there.

Now that the spreadsheet tabs are straightened out, we can take a look at a few tips for formatting the contents of your spreadsheets.  Here are some useful tips to follow that really won’t take any more time to implement than your current process:

• Make sure that you use the same font type and size for each component of your spreadsheet.  This doesn’t mean that your headers have to be the same size as your data, but make sure that the font is consistent in both your headers and your data separately. Other formatting also applies here, make sure that it is generally consistent.  Obviously if you want to highlight some data with a different format that is fine, just don’t make a mess of all of your content.

• In line with above suggestion, across spreadsheets in a workbook, try to keep the formatting generally the same.  For example, if you use a certain font size, font style, and fill color for header information, try to use that for all header information you create in the workbook.  If you have three spreadsheets that are related because they show the results of a test against three different devices, try to make the formatting on these sheets the same.  A trick I use is to create the initial spreadsheet, and then just copy that sheet and change the relevant parts when you want to make the next one.

• Don’t go crazy with colors and font styles.  Accents are fine, but if you are using more than five colors in a spreadsheet, you are going overboard.

Remember, these are general suggestions, you should tailor them to fit the project you are working on.  However, I believe that if you implement these in your next project, not only will it cut down on the number of drafts that you have to create, but the overall work product created will be at a higher level of quality.