All articles with 'references' Tag

Excel table is a series of rows and columns with related data that is managed independently. Excel tables, (known as lists in excel 2003) is a very powerful and supercool feature that you must learn if your work involves handling tables of data.

What is an excel table?

Table is your way of telling excel, “look, all this data from A1 to E25 is related. The row 1 has table headers. Right now we just have 24 rows of data. But I can add more later!”

Excel Tables have been around for a decade now (they are introduced in Excel 2007), and yet, very few people use them. They are versatile, easy and elegant. At Chandoo.org, we celebrate Tables all the time. If you have never used them, start with below tuts.

• Introduction to Excel tables
• How to use structured referencing
• Tables and Relationships in Excel
• Using lookups and other formulas with Excel tables
• Simple way to get absolute references in Tables
• Customizing table styles for awesome usability

While tables are super helpful, they do come with some limitations. Today let’s examine one such unique problem and learn about an elegant solution.

Last time we had discussed the use of SumProduct() to ease your life for calculation of consolidated revenues and depreciation. This time we would be using the sum function! Yes you heard it right – The Sum function.

But we would use the Sum function with a small trick! We would use it to calculate running cumulative sum! And believe me, you would need this function so many times – to calculate accumulated depreciation, cumulative debt, Profits to Retained Earnings and almost all the accounts that would consolidate into the balance sheet.

With Excel 2007, Microsoft has introduced a powerful and useful feature called as Tables. One of the advantages of Tables is that you can write legible formulas by using structural references. That means, you can write easy to understand formulas like this,

But, there is a problem. When you write these formula and drag the formula cell sideways to fill remaining cells, Excel changes table column references and thus makes your formulas almost useless.

Well, there is a simple workaround for this problem

It is not too sunny here, but I am going to put on my business man hat. At the end of each month, I ask myself if my business (chandoo.org that is) has performed better or worse. One simple way is to look at previous month’s numbers and then I know how good the latest […]

Here is a little experiment to freak out excel.

Go to cell C3 and write =C3 and press Enter. Excel would throw up nasty message saying, “Microsoft did not know what to do. We have a sent a support engineer to your home, but he is stuck at the round-about near your house.”

Well, not really. But what you did when you wrote the formula =C3 in cell C3 was, you created a circular reference.

A circular reference is created when you refer to same cell either directly or indirectly.

See Mona Lisa, in circular reference style.

Learn more about Excel Circular References, how to use them, examples, how to avoid them, how to deal with them in this article.

Long time PHD reader and mother of a lovely kid, Michelle, sent me a question in email that provoked me to write this post, I was wondering how to tabulate large amount of information gathered through surveys. Where I work customers are constantly handed survey sheets in order for us to measure how the service […]

Do you know that while recording macros in excel you can use the relative reference based recording so that you can use your macro anywhere and the references would be adjusted automatically? Yes! I have learned this yesterday and I thought it is pretty cool. Here is how you can make your macro VBA code […]

This is the first installment of the spreadcheats series. I have used excel an year and half before I learned about the relative and absolute references. It was such a joyous feeling to find out that you can actually write one (or just a few) formulas and use the power of auto-fill to do the […]