How To Use Labels In Microsoft Excel 2003 To Sum Cells

By:Chris Le Roy

Microsoft Excel is a fantastic tool but one of its downfalls is the use of cell addresses especially when you are trying to sum a series of numbers; however Microsoft has a really cool tool that allows you to add up a series of cells simply using the labels around your data. In this article we will investigate the steps you need to follow to be able to use Labels to Sum cells in Microsoft Excel.

Let us get started …

To show you how using labels works, the first step we need to undertake is to simply create a new set of data, so open a new Microsoft Excel workbook and click on Sheet 1. First off we are going to build the worksheet so in cell A1 I want you to type - Years, in cell B1 type the word Values and in cell C1 type the word Values1 and in cell D1 type the word Total. These four values we have typed into cell A1, B1, C1, D1 are labels. We are now going to put into the worksheet three more labels. In cell A2 type the value 1999, in cell A3 type the value 2000 and in cell A4 type the value 2001. The last three values entered will in fact become labels but we will convert them to labels a little latter on.

In the remaining cells simply put the following values:

B2 - 29 C2 - 32

B3 - 54 C3 - 99

B4 - 62 C4 - 72

Now that we have built a very simple spreadsheet and we have a few values to work with we have to tell Microsoft Excel to actually accept labels in our formulas in our spreadsheet. We do this by first going to the Tools menu and then choosing the Options command from the drop down menu. The Options dialog box will now be open in front of you. Simply choose the Calculation tab and in the bottom right hand corner you will see a check box that says Accept Labels in Formulas and you need to click on the check box so that it has a tick in it. Then to complete the process simply press the OK button.

Now we can use the labels we put in B1 and C1 to add up the values.

Lets try it out…

In cell B5, which is the Values column I want you to type the formula -

= Sum(Values)

The cell should return the total of 145. What you will notice though is that the formula looked up the column to where the label was and said everything in this column will be added together. We could have simply typed the following formula instead of using labels -

= Sum(B2:B3)

However, as I am sure you will agree, using labels makes your formulas a lot easier to read and much clearer to understand. Now it is your turn. In the cell C5 write the equivalent label formula for that cell. I will give you a hint if you are not sure -

= Sum(Values1)

How did that go?

Alright, the next issue we are going to visit is using numbers as formula labels. Now if we simply typed in cell D2 the formula -

= Sum(1999)

All that would be returned is the value 1999 so this will not work at all. In fact what we need to do is to tell Microsoft Excel that we want the cells in A2, A3 and A4 to be treated as labels. The first step we must do is to select cells A2,A3 and A4, then go to the Insert menu, choose Name from the drop down menu and then choose the Label command from the expanded menu. The Label Ranges dialog box will now be visible in front of you. All you simply have to do, to have the cells we chose before defined as Labels is to press the Add button and then press the OK button.

Click once on the cell D2. What we are going to do now is to type in the same formula I mentioned before and that is -

= Sum(1999)

By the way, press the Enter key if you have not done so already. As soon as you do that you should see that it adds up the values in cells B2 and C2 and you will see the value of 61 in your cell.

Try creating the formulas for the other two cells -

D3 = Sum(2000)

D4 = Sum(2001)

Finally, just to finish our spreadsheet off , we can total all of our total values in cells D2, D3 and D4 by typing the following formula in D4 -

= Sum(Total)

Using labels in Microsoft Excel is a very clean way of summing your values as it ensures that you do not miss a cell address and it makes the formulas a lot simpler. One issue that I do teach my students is not to put blank rows in your spreadsheets as I have seen cases where the blank rows actually affected the ability of the application to work out what was a label and what is not. By following the design process I outlined above you will find that you have absolutely no troubles in using Labels to sum cells in your spreadsheets.

Digg del.icio.us Blink Stumble Spurl Reddit Netscape Furl

Article keywords: excel labels, labels in excel, microsoft excel 2003, excel 2003

Article Source: http://www.articles2k.com

Chris Le Roy has written a number of Excel Tutorials, Excel Shortcuts and a sample Excel Invoice Template to help you learn more about Microsoft Excel 2003.

Top Software Articles
1). Track Your MySpace Profile Visitors By : PaulG I know that everyone who is on MySpace has had the same burning questions that I’ve had. How can I see who has viewed my MySpace profile? Where can I find a MySpace Tracker? Since MySpace has become so popular, so have the MySpace profile stalkers. Could be your friends, neighbors, ex-boyfriends or ex-girlfriends, or even someone you don’t even know.
2). 13 Comparisons of Vista vs Tiger By : paul wilson1 Competitive and fast are two terms that are applicable to the world of computers. Constantly changing and evolving computer systems bring many advantages to the users and techies alike. The race for supremacy between the yet to be launched Vista (scheduled for 2006) and Mac OSX Tiger began in 2003 with Microsoft’s announcements of plans for an integrated desktop search functionality in Windows Vista.
3). Using professional icons for a better communication between user and application interface By : Iconshock Professional Icons Today's applications - either for the Web or standalone - are starting to focus more and more on the user interface. The time when you had to write lines and lines of commands or go through several menus to execute a certain task has passed. Developers have now implemented simple wizards, with on-screen help near each important element of the application, which will make even the most complicated task look like a walk in the park.
4). The Top Five Spyware Issues Dealing With Internet Security By : Greg Lietz One of the main ways to compromise internet security on your PC is via a program called a Trojan Horse. A Trojan Horse is a program that quietly runs in the background, inviting the user to run it, while spreading its malicious code. This code can do any number of things. It can start right away or it may simply install a program that won't start for sometime.
5). The Benefits of Open Source By : Matthew C. Keegan If you are a building a website or a forum, chances are you are using a web editor or paying for message board services. The costs of using these "out of the box" products can add up, especially if you are trying to grow your business. Let's look at some good "open source" options for you to consider. You may be surprised at how much money open source can save you.
6). Virus Strains By : J Square Humboldt What we need is the Dewey Decimal System to go digital ... Specifically, someone needs to coax their keepers into putting some logical order into how computer viruses are sorted. Recently, warnings abounded about the Kama Sutra virus quickly proliferating cyberspace, joining the Grew.A and Nyxem.E as serious threats to computer file security. However, only those who took a closer look at these strains were able to discover that they all had something in common.
7). How To Install And Setup Noah's Classifieds By : Bedrich Omacka In this article I will explain the basic Noah's Classifieds features, show you how to install this script and how to setup it to run properly. First of all let's mention that Noah's Classifieds script is written in PHP can be used for free. Noah's Classifieds is one of the most popular classified ads tool on the Internet. Using this great script you can create unlimited categories and subcategories, upload images for the categories and the advertisements, define variable fields for the advertisements per categories etc.
8). Trojan Horse Delivered In Automatic Update By : Darren Miller Trojan Horse - One Mans “Worse Case Scenario” Prediction ---------------------------- This is a fictional article about a Trojan Horse Virus, or you could say it is one mans prediction of a “worse case scenario”. Because of the field I’m in, I maintain a personal list of my top 10 “worse case scenarios”. Every time I perform a security assessment I run into something new or identify a situation that is ripe for a potential vulnerability.
9). New to gaming, all you need to know about Flash gaming sites By : paul wilson1 Whew! According to Computer Gaming World, there are 80 gazillion free Flash games sites in the cyber world. Popular sites are: • www.games.yahoo.com -- this has card games, word games, arcade games, as well as puzzles. The number of violent games is limited so the site appeals to the “old-young” –those who are young at heart. • www.shockwave.com -- a popular site with original games as well as PopCap ones.
10). Improve PC Performance - 6 Tips You Must Know By : Michael Braid Are you frustrated with your PC? Is it feeling sluggish or crawling at a snail's pace? Are programs running slower than they used to? Are you just plain fed up with frequent slowdowns, freezes or crashes? Then it's time to stop what you're doing and optimize your system to improve PC performance! Follow these 6 simple sure-fire tips to help improve PC performance and you'll be surprised by the results! Each comes with an indication of how often you should do it.

New Software Articles
1). Lock Down the Personal Information on Your Computer By : Buzz Scott Securing and hiding personal information is more important now than ever. With identity theft on the rise and hacking technology improving you need to be safe.
2). Are Your Employees As Productive As They Would Have You Believe? By : Buzz Scott Productive employees can start with a good computer monitoring program.
3). Business Intelligence guide By : mansi gupta Business intelligence can be defined as a set of business processes designed to garner and analyze business information. It is a vast category of application of programs that includes providing access to data to help an entrepreneur in his business decisions, task of query and reporting, online analytical processing (OLAP), statistical analysis, forecasting and data mining.
4). PsP Software Downloads – Review of Software Sites By : pjs1965 PsP handhelds are one of the newest and most fun ways to play games, watch movies and listen to music. PsP software downloads are just some things that you can do to improve your psp. PsP software downloads are fun and easy. There are many places on the net were one can find psp software downloads. Some sites have you pay per download and sometimes charge up to a couple dollars for each download.
5). PsP Software – Review of Software Download Sites By : pjs1965 PsP handhelds are one of the newest and most fun ways to play games, watch movies and listen to music. PsP software downloads are just some things that you can do to improve your psp. PsP software downloads are fun and easy. There are many places on the net were one can find psp software downloads. Some sites have you pay per download and sometimes charge up to a couple dollars for each download.
6). Microsoft Has Stunted Innovation By : Chris Young Creativity is what spurred the growth of the computer industry and technology. Challenges were met and new frontiers conquered through sharp-edged innovations and competition. Sadly, over the years small companies like Microsoft grew into giants and then lost their thirst for adventure. They deliberately hid their proprietary source code and this stunted innovation.
7). Download Music, Movies and Games for Free - A Hot Issue By : Harry Rackers The Internet is used by a lot of people to download music, movies, games and various other soft wares for free. The legality of this practise is, and will be for a long time, a hot issue. Some countries want to totally stop the downloading of copy wright protected materials, like the U.S.A. Other countries, like France, want to allow this type of downloading, but only for personal use.
8). Expressing Love With Ecards - Is It Effective? By : CD Mohatta Can love be effectively expressed with ecards? With many mediums available today, which medium should be our medium of choice in expressing our feelings. We have choice of sending a voice mail, sending an email, a printed card, and ecards. Of course the first choice always remains - our own talk. How are ecards better than other mediums to express love? Let us look at that.
9). Learn Management With Desktop Wallpapers By : CD Mohatta Learning over the ages has undergone many changes. In recent years many rapid changes have taken place, from classroom teaching to online education. Could you ever think that management could be taught with the help of Desktop wallpapers? Here is how it is being done now days. Learning - Let us look at learning. How do we learn? By reading, reflecting and thinking and then remembering what we have been taught.
10). Adware is a Pain but you Can Deal with Fast By : Carol Hansonly Adware is one of those things that many people simply do not worry about until the ghastly stuff actually does some damage. Yes it can cause damage and sometimes this is too late to fix. The plain facts are that adware or spyware can number one effect your computers functionality and two this can lead to system failure and ultimately data loss. Now without putting the fear of god into you the data loss scenario is certainly a possibility but in the most extreme of cases.