All articles with 'excel tables' Tag
We all know that area charts are great for understanding how a list of values have changed over time. Today, let’s learn how to create an area chart that shows different colors for upward & downward movements.
The inspiration for this came from a recent chart published in Wall Street Journal about Chinese stock markets.
We will try to create a similar chart using Excel. We are going to create the above chart in Excel.
Looks interesting? Read on…Continue »
Filtering is one of the most used feature in Excel. It is a quick way to take lots of data and narrow down to the subset we want.
But here is one common filtering scenario that is slow as snail.
Imagine you are looking at some sort of sales data (if you can’t imagine, look at the above demo).
Now, you want to filter this list for gender=male, profession=self-employed, product category = chocolates and quantity = 1.
If you use the right click, filter > filter by selected value approach, this will take several clicks.
Wouldn’t it be cool if you can select the entire combination and say filter?
Unfortunately, no such feature exists in Excel.
But you are not aiming to be ordinary in Excel. You are aiming to be awesome in Excel. That means, you don’t take no for answer.
Fortunately, we can quickly write a VBA macro that filters a list by selection. So let’s do that.Continue »
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.Continue »
Recently we asked you, what do you struggle doing in Excel? 170 people responded to this survey and shared their struggles. In this post, lets examine the top 10 struggles according to you and awesome remedies for them.Continue »
Imagine you are the head of Accounts Receivable department at a large company. Drab, I know, But humor me and imagine.
Now, every month you get a transaction report.
And you want to know which numbers are matching up.
i.e, if your company gave Vendor-0002 $872.34 on 1st of April, 2014 and your received below payments from them subsequently,
- $427.77 on 1st April
- $152.88 on 2nd April
- $291.69 on 2nd April
Then you consider the account matched since the total received is same as total payable.(427.77 + 152.88 + 291.69 = 872.34).Continue »
Anyone running a small business knows the oozing bits of joy when you hear a customer saying, “Can you send me an invoice?”
While creating an invoice is an easy task, if you want something that is professional looking, easy to manage and works well, then you are stuck.
That is where Excel really shines. By using an invoice template, you can quickly create and send invoices.
Today I want to share one such template with you all. Why? Because we are awesome like that.Continue »
Recently I posted about how you could construct dynamic (cascading) dropdowns that could easily handle multiple levels, like this: …and we saw that users could subsequently change upstream dropdowns in a way that would make downstream choices invalid, like this: In my previous post I used some VBA to clear out any ‘downstream’ choices if […]Continue »
Dynamic dropdowns are a handy way to get your users to make choices based on what they’ve previously chosen, while steering them away from making invalid choices. Today we’re going to look at one that easily handles multiple levels, and we’ll take a look at what could go wrong. Let’s see one in action, shall […]Continue »
What do you use Excel Tables for?
I will go first, I use them for,
1) Maintaining all my raw data, so that I can use structural references
2) Maintaining various trackers, for example I am tracking all our expenses in USA in a table.
3) For quick formatting of data (zebra lining, filters, sorting etc.)
What about you? What kinds of data are you holding in tables and how are you using them. Please share using commentsContinue »
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”
Last week, we had a lovely poll on what are your favorite features of Excel? More than 120 people responded to it with various answers. So I did what any data analyst worth his salt would do,
I analyzed the data and here are the top 10 features in Excel according to you.
Read on to learn more.Continue »
HR managers & department heads always ask, “So what is the vacation pattern of our employees? What is our average absent rate?”
Today lets tackle that question and learn how to create a dashboard to monitor employee vacations.
What do HR Managers need? (end user needs)
There are 2 aspects tracking vacations.
1. Data entry for vacations taken by employees
2. Status dashboard to summarize vacation data
When analyzing business data like sales, shop visits or productivity, one of the questions managers always ask is,
What is the best month / week for this data?
To answer this question, we need to make a chart that looks like above. Today, lets learn how to highlight portions of such charts that correspond to best week or best month.Continue »
Zebra lines, the dull highlighting applied to alternative rows is a very good way to make your tables readable & pretty.
We can use either conditional formatting or table formats to quickly add zebra lines to our data.
But what if you want a little more?
What if you want to highlight, lets say 3 rows in one color and 3 in another and repeat this …Continue »
Today, lets learn OFFSET formula.
OFFSET formula gives us reference to a range, from a given starting point with given height and width in cells.
OFFSET formula syntax
OFFSET formula looks like this:
=OFFSET(starting point, rows to move, columns to move, height, width)
Starting point: This is a cell or range from which you want to offset
Rows & columns to move: How many rows & columns you want to move the starting point. Both of these can be positive, negative or zero. More on this below.
Height & width: This is the size of range you want to return. For ex. 4,3 would give you a range with 4 cells tall & 3 cells wide.
Read on…,Continue »