All articles with 'data validation' Tag

Do you want to create a dynamic dependent drop down list in Excel like below? You can use XLOOKUP and data validation to set this up quickly. It is fully dynamic and works across a full column too.

It is the Soccer WORLD CUP time. Let’s celebrate the season with an AWESOME Excel File to track game schedules, results & scores.

Ever wanted to create a two-level data validation list? You can use this simple trick to make two-level or cascading drop-down validation lists in Excel. You need some data, a pivot table and simple IF formulas to get this. Read on to understand the process and create your own two-level drop down lists in Excel.

Do you want to create a simple, elegant and useful tracker using Excel? You can make trackers with features like tables, data validation rules and conditional formatting. In this page, I will explain the process for creating an Excel tracker.

Validating your data as you type can prevent any surprises when you are doing analysis / follow-up on the data. Thankfully, excel has the right tools to do it. Excel drop down list can assist you in picking up a value from a valid list to enter in a cell. Here is a short how-to […]

Ever wanted to make a cool, snazzy interactive chart in Excel? Something like this:

In this tutorial, learn all about making your very first interactive chart. We use both formulas and pivot tables to build two versions of an awesome interactive chart in Excel.

Imagine you are head of human resources at Casual Contracting Co. Every month you hire a lot of temporary staff who spend 1-4 months with CCC before leaving. Sometimes you hire the same people again. Of late, you have noticed a strange process gap. You are paying same person two (or more) salaries.

This is because you are hiring a person for new temp role even before their current one ended. See above picture.

So how to avoid making such hiring boo-boos.

Simple, using Excel of course.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 53rd session of Chandoo.org podcast, let’s talk about data validation.

What is in this session?

In this podcast,

• What is data validation
• How Excel DV compares with database & software DV?
• Types of data validation rules
• List & custom rules explained
• Input & error messages
• Alternatives to data validation
• Enhancing data validation
• Removing data validation rules
• Homework problem for you
• Resources & show notes

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()

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.

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.

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.

Ctrl + Shift + Enter by Mike Girvin

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.

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

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

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