4 Excel Formulas Every Blogger Should Know

By Guest Author

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:

  1. Find why a formula returns an error
  2. Discover why your formula does not return the value you expected it to return
  3. 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.



Share

14 Responses to “4 Excel Formulas Every Blogger Should Know”

  • Dinesh @ DailyBlogMoney

    I like your 2nd point, which is useful while converting the earnings from one currency to local currency.

    I appreciate your help to bloggers.

    • Web Marketing Tips

      excel is really a powerful tool.

      This one have huge options and i am sure every time you able to learn new things.

  • Web Marketing Tips

    nice to see that you have expanded blogging tips to next levelby adding these kind of tips.

  • Destination Infinity

    Quite useful tips, especially the second one (I never knew the use of $ sign for having fixed values).

    Though I admit that excel is the most common version of the spread sheet that is in use, I request you to create similar video’s with Openoffice.org Calc (spread sheet) as well. Open Standards are very important, particularly on the long run.

    Destination Infinity

    • Joe Reese

      Hi Destination Infinity,

      That’s a great idea. Most of the techniques I’ve shown also work for OpenOffice. But I’ll definitely make videos dedicated to OpenOffice.

      I’ll also do a couple for Google docs.

    • Web Marketing Tips

      yes these are usedul specially for blogger like us. Will add few tips of mine as well.

  • Mani Viswanathan

    You’ve linked well between an excel sheet & the real deal 🙂

    • Web Marketing Tips

      yes this one is simply great and useful as well.

      Would love’to see your tips on word as well.

  • Joshu Thomas

    Thats a nice post and a quite different one from the usual blog tips. Thanks again !!

    cheers, Joshu

    • Web Marketing Tips

      yes we never expcted these kind of technical tips from a hard core blogger.

  • BlogTipss

    Valuable information about Excel you got here! I would like to thank you for sharing your thoughts and time into the stuff you post!! Thank you for so useful excel formulas

    • Web Marketing Tips

      yes excel wad nightmare to many bloggers and i am sure this is going to help them as well.

  • Sheila @ Avaguide

    Great tips, this will lessen time in an excel spreadsheet at the same time it will be easier in handling computations.

  • Spreadsheet Guy

    This is a great job. I always use Format As Table so I can easily sort and filter different columns. It is great for searching the results of a Google Keyword Tool session so I can find the right keywords to target. I’ll put something up on my blog about it.
    -Rick

Comments are closed.