Tech Tips Home
The Best Tech Tips And Daily Deals

WorldStart Tech Tip And Store Search

Like what you see here? Subscribe to the Tech Tips newsletter!   Email: Subscribe

# Calculations in Excel Using Whole Row or Column

Monday, June 14th, 2010 by | Filed Under: MS Excel

Ever have an Excel column or row that is designed to continually expand with additional data?

Do you have calculations – may a sum or average or whatever – from the set of data?

And when the amount of data increases what do you do with your formulas?

If you’re changing the cell range each time then I know that you’re frustrated.

Or maybe you wrote the formula by using the address of the first and last cells in the row or column?

And while once you know the address of the last cell it certainly makes for some long formula strings – which can be difficult to edit and handle later on.

So the question must be asked … is there a simpler way? Maybe something that doesn’t require continual formula editing or obnoxiously long formulas?

A simple way you ask – no problem.

When writing a formula that should use the data in an entire column/row, use its letter/number as both the beginning and ending locations.

Sounds confusing I know but wait – a quick example will clarify things immensely.

Let’s say that I need the average of all data in column A. In the location I want the average placed use this:

=AVERAGE(A:A)

Need a calculation from an entire row? =AVERAGE(1:1) will average all data in row one.

Well, you get the idea…

I’m not sure we could write a cleaner formula for any calculation and the happy news is that this one replaces others that had the potential to be messy and annoying!

~April

### One Response to “Calculations in Excel Using Whole Row or Column”

1. Lora says:

I have a question. What if you are regularly adding columns to your spread sheet and you only want to include ONLY the two most recent columns in your average? Today will be A & B, next week will be B & C….. Can this be done without having to change the fomula every week?

• Tech Tips Daily - Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

• WorldStart's Daily Deals - Every week, we send out great deals in our Daily Deals newsletter. Many of these deals are exclusively for our Daily Deals newsletter subscribers and can't be found with our regular specials.

• Just For Grins - Each issue includes a couple clean jokes, some funny quotes, and a hilarious reader's story. Newsletter is sent five days a week.

Subscribe

We only use it to send you the newsletters you request. It is NEVER disclosed to a third party for any reason, ever! Plus, if you decided you don't like our newsletters (don't worry, you'll love them), unsubscribing is fast and easy.

Tech Tips Daily

Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

Tech Tips Weekly

The week's best in tech and computer help. Get your issue sent to your email every Friday!

WorldStart's Daily Deals

The very best deals on the Internet! Get a new set of incredible sales every day of the week!

Just For Grins

Clean jokes, funny quotes, and hilarious comics. Sent 5 times a week straight to your email.

Subscribe

Categories:
Archives: