All articles with 'form controls' Tag
Imagine you run an office furniture company. You want to stop reordering two brands of furniture – Relaxer (a type of chair) and Boca Top (a type of table). You currently have 20,000 Relaxer chairs and 5,000 Boca Tops. These are valued at $200,000 and $100,000 respectively. When sold, they will yield $100,000 and $25,000 gross profit. You are hoping to sell them off in 2 or 3 years. You forecast that we can sell off these as per some yearly schedule.
You need to analyze this and prepare a cash flow model.
Let’s learn how to answer such open ended questions using various analysis techniques in Excel.Continue »
Here is a quick & awesome way to make your dashboards sexy. Add an on / off switch to your conditional formats.
Take a look at above demo to understand what I mean.Continue »
During last week, an alert reader of our blog, Jørgen emailed me a link to “How Trump happened“. It is an interactive visualization by Wall Street Journal. Jørgen asked me if we could replicate the visualization in Excel. My response: “Making a new chart in Excel? Hell yeah!”
Read on for awesome visualizations and full explanation.Continue »
Most advanced Excel users know that slicers are cool. Today, let’s learn how to use slicers to create an awesome selection mechanism for your dashboards and forms.
First see a quick demo
Looks slick, eh? Read on.Continue »
Hello all, prepare to be amazed! Here are 43 creative, fun & informative ways to visualize KPI data.
About a month ago, I asked you to visualize KPI data. We received 65 entries for this contest. After carefully reviewing the entries, our panel of judges have discarded 22 of them due to poor charting choices, errors or just plain data dumps. We are left with 43 amazing entries, each creatively analyzed the data and presented results in a powerful way.
How to read this post?
This is a fairly large post. If you are reading this in email or news-reader, it may not look properly. Click here to read it on chandoo.org.
- Each entry is shown in a box with the contestant’s name on top. Entries are shown in alphabetical order of contestant’s name.
- You can see a snapshot of the entry and more thumbnails below.
- The thumb-nails are click-able, so that you can enlarge and see the details.
- You can download the contest entry workbook, see & play with the files.
- You can read my comments at the bottom.
- At the bottom of this post, you can find a list of key charting & dashboard design techniques. Go thru them to learn how to create similar reports at work.
Thank you very much for all the participants in this contest. I have thoroughly enjoyed exploring your work & learned a lot from them. I am sure you had fun creating these too.
So go ahead and enjoy the entries.Continue »
Cascading drop downs enhance usability of your dashboards & interactive workbooks. A cascading drop-down is a 2 or more level selection mechanism. When you have 100s of selection choices, instead of creating one massive drop down or combo-box, you can set up multiple levels of drop downs, so that users can narrow down their selection. For example, users can select Country, State and then City using cascading drop downs.
There are many ways to setup cascading drop downs. You can use formulas coupled with either data validation or form controls. You can also use Slicers. In this video we will review these techniques.Continue »
Whenever you have a dashboard that is quite long or spans across multiple worksheet tabs, it can be hard to use. Here is a simple trick to make your dashboards user friendly. If your dashboard has form controls, create duplicate sets of them and place them in locations where users are looking. For example, If your […]Continue »
We, adults can’t escape three things:
- Demanding bosses (replace with customers or nagging spouses or naughty kids)
While I can’t help you with demanding bosses or taxes, when it comes to deadlines, I have the right tool for you.
A tracker that highlights all overdue items so that you know where to focus your attention.
Let’s learn how to use awesome powers of Excel to find-out which items are due. You can apply these concepts to nail down over due invoices, pending project tasks or scheduling workforce.Continue »
Analyst’s life is busy. We have to gather data, clean it up, analyze it, dig the stories buried in it, present them, convince our bosses about the truth, gather more evidence, run tests, simulations or scenarios, share more insights, grab a cup of coffee and start all over again with a different problem.
So today let me share with you 25 shortcuts, productivity hacks and tricks to help you be even more awesome.Continue »
Imagine you are the in-charge of finance department at Hogwarts. So one fine day, while you are practicing the spells, Dumbledore walks in to your office and says, “Our electricity bills are way too high. As the muggles don’t accept wizard money, we have to find a way to reduce our power consumption.”
So you summoned the previous 12 month utility bills to examine energy consumption patterns, and pretty soon you realized that most of the electricity consumption is due to the light bulbs. You suddenly have a brilliant idea. Why not replace the light bulbs with a variety that consumes low power? A light bulb moment indeed.
Your next step is to figure out what varieties of light bulbs are out there. Fortunately this is easier than catching a snitch in a game of quidditch. A quick search revealed that there are 3 types of light bulbs:
- Regular incandescent bulbs (the kind Hogwarts currently uses)
- Compact Fluorescent Light bulbs (CFL)
- Light Emitting Diode bulbs (LED)
Now your job is to do a cost benefit analysis of these options and pick one.Continue »
Recently, I saw this chart on Economist website.
It is trying to depict how various cities rank on livability index and how they compare to previous ranking (2014 vs 2009).
As you can see, this chart is not the best way to visualize “Best places to live”.
Few reasons why,
- The segregated views (blue, gray & red) make it hard to look for a specific city or region
- The zig-zag lines look good, but incredibly hard to understand % changes (or absolute changes)
- Labels are all over the place, thus making data interpretation hard.
- Some points have no labels (or ambiguous labels) leading to further confusion.
After examining the chart long & hard, I got thinking.
Its no fun criticizing someones work. Creating a better chart from this data, now thats awesome.Continue »
Ok people. Let me tell you that this post is almost not about Excel. It is about how one Excel blogger’s (yours truly) dream of long distance cycling came true. So sit back, grab your favorite drink and read between sips.
So what is this all about?
Last Sunday (27th July) & Monday (28th), I finished my first ever 200KM bicycle ride. I rode for a little more than 12 hours, burned 5,179 calories & rode 206 kilometers.
It is definitely one of the most memorable, tiresome & uplifting experiences in my life. So naturally, I want to share the story with you.Continue »
If you want to create magical effect with your Excel workbook (or report, dashboard, model), then hear no further. In this episode, we explore 5 very powerful magic tricks you can apply to get jaw dropping reactions from your bosses, clients & colleagues.
In this podcast, you will learn,
- Why magic
- 5 Excel Magic Tricks
- 1: Conditional formatting
- 2: Form controls + Charts
- 3: Pivot tables + Slicers
- 4: Macros + Automation
- 5: Using right feature @ right time
- How to learn these magic tricks
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 »
Last week we learned how to answer questions like, “How many tiles in a room?” using Excel. We learned about CONVERT function and fraction number format settings in Excel.
But why stop at calculation? We can even model a room full of tiles, thanks to Excel’s grid nature.
So today, we will learn how to create a room layout as shown above, using Excel.Continue »