All articles with 'data validation' Tag
Pivot tables are very powerful analysis tools. They can summarize vast amounts of data with just few clicks. But they are lousy when it comes to output. Imagine the horror of putting a pivot table right inside your beautiful dashboard. One refresh could ruin the layout and create half-an-hour extra work for you.
How to combine the power of pivot tables with elegance of your dashboards?
The answer is: GETPIVOTDATA()Continue »
Hello everyone. Stop reading further and go fetch your helmet. Because what lies ahead is mind-blowingly awesome.
About a month and half ago, we held our annual dashboard contest. This time the theme is to visualize state to state migration in USA. You can find the contest data-set & details here.
We received 49 outstanding entries for this. Most of the entries are truly inspiring. They are loaded with powerful analysis, stunning visualizations, amazing display of Excel skill and design finesse. It took me almost 2 weeks to process the results and present them here.
Click on the image to see the entries.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 »
Outside of a dog, a book is a man’s best friend. Inside of a dog it’s too dark to read. -Groucho Marx
We don’t have a dog (yet), but we have a lot of books. Anytime I am in a bookshop, I feel like a mosquito in fat-camp. So you can imagine me smacking my lips when I saw your suggestions for favorite Excel books recently. There were more than 50 different books recommended by our readers. Of course I cannot buy all the 50 in one go. That would make Jo (my wife) angry. So I bought one that most of you recommended.
It is a book about array formulas, advanced formulas and data analysis techniques. I am eagerly waiting for the delivery to devour it. Once I am done with it, I will post a review here.
What about you? What Excel book are you reading now? Please share in comments.Continue »
A while ago, we published a new year resolution template. This was a hit with our reader with thousands of you downloading it. During last week, Peppe, one of our readers from Italy, took this template and made it even more awesome.
The original template had tasks and completion check mark. As you finish each task, you can see the progress bar moving.
Peppe added priorities to this. With his new version, progress is measured based on how much priority we assigned that particular task. Pretty neat eh?!?Continue »
Here is a quick tip on removing data validation rules. We all know that data validation is a powerful feature in Excel to control what data is entered. But, sometimes, you may want to remove the rules from a set of cells. How would you do it? The normal approach is, Select the cells with […]Continue »
Last week we saw a really cool holiday request form made by Theodor. This week, we will learn how to combine conditional formatting and data validation to create an awesome data entry form. First see a demo to understand what I mean: How to create such a data entry form? Very simple, just grab a […]Continue »
This is a guest article by Matt, who works as a Business Analyst with allrecipes.com. He shares with us how he is using Excel to become a rockstar business analyst. In his own words,
“At Allrecipes.com we use excel for a variety of purposes. Analyzing site trends, forecasting traffic, charts, dashboards, and slide shows; you name it, we use excel for it. That’s why Chandoo’s tips have been so helpful – because we use excel every day. Thanks to chandoo.org, I’ve developed a reputation as an “excel wizard” and even taught a 4 week excel training class!“Continue »
Sometimes we don’t know what we want. If this happens when I am in a bar, I usually order a cocktail. Just a mix of everything. The same will work in Excel too.
For eg. If you have lots of data, but the value you want to look up needs to change based on whims and fancies of your users, then you can resort to a cocktail. A mix of VLOOKUP with Drop down lists (Data validation)
Read more to find how to solve this.Continue »
This is a guest post written by Paramdeep from Pristine. Chandoo.org is partnering with Pristine to bring an excel financial modeling online training program for you. This is Part 6 of 6 on Financial Modeling using Excel In this tutorial we are going to learn how to build assumptions & input sheets in our excel […]Continue »
Ok, since excel school 3rd batch is going to open on 15th, I wasnt going to write anything today. I have slept just 4 hours last night, blame it on work (and that funny video on youtube). But I found 30 minutes free time, so here you go, a quick but delicious tip on making your data validation dynamic.
Dynamic Data Validation?!? What in the name of slice bread and peanut butter is that?
See the demo aside to understand and read on to master the trick.Continue »
While I was working Denmark, there is one thing I noticed. Danes are one hell of football lovers. The football (soccer) enthusiasm is over the top when there is a match between Denmark and Sweden. A common practice in many offices is a football pool. This is how it works: When there is a match […]Continue »
In a recent consulting assignment I had a tricky data validation problem. The customer wanted to have an either-or condition in the data validation, like this: My initial reaction to this requirement was “hmm… that is not possible“. But before shooting the email back to client, I got curious and checked if excel data validation […]Continue »
Often when we are making spreadsheets for forecasting or planning we would like to keep the starting month dynamic so that rest of the months in the plan can automatically rolled. Don’t understand? See this example: This type of setup is quite useful as it lets us change the starting month very easily. We can […]Continue »
A panel chart (often called as trellis display or small-multiples) shows data for multiple variables in an easy to digest format. It lets users compare in any way and draw conclusions with ease.
Today, I want to discuss how the principles of panel chart can be applied to visualize a complex set of survey results. For this we will use the recent survey conducted by Gartner on how various customers use BI (Business Intelligence) tools.Continue »