4 Excel Formulas Every Blogger Should Know

Posted: 06 Nov 2010 12:04 PM PDT

This is a guest post by Joseph Reese. If you want to guest post on this blog, check out the guidelines here.

As a blogger, whether you plan to make a living from advertising or are into affiliate marketing, or even if you are selling (or plan to sell) your own info-products, Excel can be a tremendously useful tool. So, if you are serious about treating your blog as a business, then learning how to use Excel is a smart move.

However, a large percentage of the hundreds of bloggers I’ve worked with at my consulting firm were frustrated with Excel to the point that they were ready to ‘tear their hair out’.

That’s why today, I am going to teach you how to overcome the 4 hurdles that baffle most Excel users. These techniques will allow you to get a lot more value from your Excel workbooks and avoid the Excel-blues.

1. Using the Fill handle

Every day I get the same question: “How do I subtract one column from another” or “How do I multiply two columns”

Well, this is most easily done by creating a simple formula that operates on two cells and then applying this formula to an entire column by using the “Fill handle”, as shown in this 50 second video:

The “fill handle” is amazingly useful tool and can save you a lot of time when entering formulas. What might you use this for? I have used it for taking affiliate commissions from information product sales to find the profit, calculating effective conversion rates, working out my CPM … there are thousands of uses of this neat little trick for bloggers that will save you time. Try it and see!

2. Absolute and Relative Cell References

In the previous video, you saw how to use the fill handle to copy a formula for an entire column. When you use the Fill handle, Excel changes the formula for each row so that it references the correct cells.

This is extremely useful, but in some cases, you want to use the fill handle (or copy the formula) and have some of the cell references change and other references to remain constant. As you can see in the following video, this is extremely easy, you just add a ‘$’ sign before the column and the row:

How might you use this? Well if you are a freelance blogger you might have your hourly or per-post rate in a cell and use this rate for quotes or invoices. You want to use that figure in all your calculations. Same if you sell advertising, you would base your quote off of a price-list where the prices are constant.

3. Linking Data from Other Sheets/Files

Many people struggle with linking data between sheets, but in the following 60 seconds video, you'll see how easy it is:

Using this technique you could take your price list or rate-card from the previous example and make it far more powerful by having one sheet contain your prices and the other sheet be your quote or invoice!

4. The Formula Evaluation tool

Most formulas, especially complex ones, won’t work right off the bat. You need to know how to easily trace which part of your formula isn’t working and fix it.

There’s a little-known tool that allows you to see how Excel evaluates your formula and pinpoint exactly where a problem stems from.

It’s called the “Evaluate Formula” tool. And it is hidden in the “formula auditing” section on formulas ribbon. In the following video, you can see how to use the “Evaluate formula” tool to find out why your formula isn’t working:

You can use the “Evaluate Formula” tool to:

Find why a formula returns an error
Discover why your formula does not return the value you expected it to return
As a learning tool while you get up to speed with developing complex formulas

I use the “Evaluate Formula” tool on a daily basis to debug formulas and I find it invaluable.

Over to the readers

Do you use other Excel techniques/tools on a regular basis in your blogging business? I’d love to hear from you in the comments.

About the Author: Joseph Reese is the founder and chief formulas expert at Excel-Formulas.com an Excel consulting firm. Head over to his website if you want to improve your performance with Excel.

Original Post: 4 Excel Formulas Every Blogger Should Know

Helmi Kimsui Menulis..