How to Visualize Survey Results using Incell Panel Charts [case study]
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.
Data Validation using an Unsorted column with Duplicate Entries as a Source List
Here is a typical scenario: We want to allow only one of the pre-defined customer names in our spreadsheet. We have listed down all the customers in column B and want excel to check against this list and validate the data. But there are 3 problems. (1) Our list is not sorted alphabetically (2) It contains duplicates and (3) The list comes from external source, so we can not remove duplicates and sort the list every time.
Now how can we set up a simple data validation list that would not repeat customer names and shows them in sorted order like this.
Read the rest of this guest post by Hui to learn how to use data validation in creative new ways.
Top X chart – Show Top X values of a chart Interactively
Two charting principles we hear all the time are,
- Sort your data in a meaningful order before plotting it.
- Show only relevant information, not everything – because un-necessary information clutters the chart.
Today we will learn a dynamic charting technique that will mix these two ideas in a useful way. I call this a Top X chart.
Excel Links of the week [After a long time edition]
It has been a really long time since we have an excel links post here. That doesnt mean I am not reading anything exciting. It is just that I have gotten busy being a dad. But now that I am in to a saner schedule, we are bringing back the excel links. Here is a […]
Making a chart with dynamic range of values
We all know that to make a chart we must specify a range of values as input.
But what if our range is dynamic and keeps on growing or shrinking. You cant edit the chart input data ranges every time you add a row. Wouldn’t it be cool if the ranges were dynamic and charts get updated automatically when you add (or remove) rows?
Well, you can do it very easily using excel formulas and named ranges. It costs just $1 per each change. 😉
Ofcourse not, there are 2 ways to do this. One is to use Excel Tables and another is to use OFFSET formula.
29 Excel Formula Tips for all Occasions [and proof that PHD readers truly rock]
It is no exaggeration that knowing excel formulas can give you a career boost. From someone starting at the long list of numbers, you can suddenly become a data god who can lookup, manipulate and analyze any spreadsheet.
So when our little excel blog hit the 5000 RSS Subscriber milestone, I celebrated the occasion by asking you to share an excel formula through twitter or comments with rest of us. And boy, what an excellent list of formula tips you have shared with us all.
Here is the complete list of entries for the twitter formula contest.
Use ROWS() and COLUMNS() formulas to generate numbers in a sequence [quick tip]
Here is a quick excel formula tip to start your week. Use ROWS() and COLUMNS() formulas next time you need sequential numbers. What does ROWS() excel formula do? ROWS excel formula takes a range as an argument and tells you how many rows are there in that range. For. eg. ROWS(A1:A10) gives 10. How can you […]