All articles with 'references' Tag

Relative References in Excel Tables

Published on Apr 21, 2017 in Excel Howtos, Learn Excel
Relative References in Excel Tables

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.

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.

Continue »

Accumulated Depreciation using Mixed References

Published on Jul 19, 2011 in Excel Howtos, Financial Modeling
Accumulated Depreciation using Mixed References

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.

Continue »

Use Copy & Paste to Preserve References to Tables [Quick Tip]

Published on May 23, 2011 in Learn Excel
Use Copy & Paste to Preserve References to Tables [Quick Tip]

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

Continue »

Simple Excel Formula to Calculate All-time High, Trailing 12 Month High Values [Quick Tip]

Published on Feb 10, 2011 in Learn Excel
Simple Excel Formula to Calculate All-time High, Trailing 12 Month High Values [Quick Tip]

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 […]

Continue »

A round-up on Circular References

Published on Sep 16, 2010 in Learn Excel
A round-up on Circular References

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.

Continue »

How do you consolidate data from multiple sheets in to one? [open thread]

Published on Feb 19, 2010 in Excel Howtos
How do you consolidate data from multiple sheets in to one? [open thread]

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 […]

Continue »

Want to become a Data God? Learn Excel Data Tables

Published on Sep 10, 2009 in Excel Howtos, Featured, Learn Excel
Want to become a Data God? Learn Excel Data Tables

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!”

Continue »

Make your VBA macros work anywhere: Enable Relative References

Published on Nov 18, 2008 in Learn Excel
Make your VBA macros work anywhere: Enable Relative References

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 […]

Continue »

Relative vs. Absolute References in Formulas [spreadcheats]

Published on Nov 4, 2008 in Learn Excel
Relative vs. Absolute References in Formulas [spreadcheats]

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 […]

Continue »