All articles with 'structured references' Tag

Excel has hundreds of formulas. But as a new learner or user, you may want to just focus on top 10 formulas to get the most out of it. Assuming you already know the basics (check out Beginner Excel page if you are complete newbie), here is a list of top 10 Excel formulas for you.

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

Let’s start the new year with a bang.

Excel Tables were introduced more than a decade ago, but a lot of people don’t know them or under utilize them. So start this year by becoming a very table genius.

What is Excel Table?

Excel tables are a simple and elegant way to structure and store your data. Let’s say you have staff details like below. Instead of calling it like A1:E72, you can convert this data in to a table and call it, you guessed it right, covfefe (or more coherent option like – staff).

Time for some good, old fashioned VLOOKUP love. Let’s say you are writing VLOOKUP()s to get data from an unusually fat table, ie one with heaps of columns. You want to get to lookup ID in first column and get thingamajig in what is that column number. Well, better get counting from 1 and after 19 seconds and lots of squinting you arrive at column number 53 – which has thingamajig. 

If this sounds like your VLOOKUP routine, check out these three amazingly simple tips to save some time and effort with your lookups.

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.

My mom will be very unhappy with this post. She always told me to focus on one thing at a time. But in this post we are talking about 3 things, not one. Sorry mom.

1. Thank you

I want to thank you for visiting chandoo.org & supporting us.

As I am about to leave to USA for attending Excelapalooza conference, I couldn’t help but be amazed at how much you have given me & my family. Almost 4.5 years ago, when I left my plush corporate job to work full time on Chandoo.org, I had no clue how the future will unfold. Today my heart is full of happiness, my family is secure, my site has grown by heaps and our community (especially you) is awesome.

Without your enthusiasm to learn and keen desire to become awesome, I would not have a job (of running this website). You inspire me to learn new things everyday so that I can share them with you.

Thank you for all the visits, clicks, comments, emails, tweets, likes, signups, purchases & love.

Thank you.

Ever seen a formula like =SUMIFS(Sheet1!B2:B3923, Sheet1!C2:C3923, A1, Sheet1!D2:D3923, A2) and wondered what it is really doing?!?

If so, you are not alone.

Formulas written with cell references tend to look complicated and clunky. What if we could write formulas in plain English?

That is what Structural References do. When using structural references in formulas, your focus will be on your data, not on which cell ranges the data takes up.

For example, you can write formulas like these:
1) SUM(mySales[no. of customers]) to find how many customers we had.
2) SUMIFS(mySales[no. of customers], mySales[product], “FastCar”) to find how many customers bought “FastCar”

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

Yesterday, we have seen a beautiful example of how showing details (like distribution) on-demand can increase the effectiveness of your reports. Today, we will learn how to do the same in Excel.

We, data junkies, love pivot tables. We think pivot tables are solution for everything (except for may be global warming and that broken espresso machine down stairs).

Today, we are going to learn 5 awesome pivot table tricks that will make you a star.