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.

Welcome to another edition of excel links.
During the last week, I have revised excel downloads page on PHD and now it contains over 75 excel templates, tutorial downloads. I am not sure if you dig the new format of the page though. So go take a look at it and let me know what you [...]

Sometimes we get values in our Excel sheets in such a way that the % sign is omitted. So instead of the value being 23%, it is 23. Now, you can very easily correct this by editing the cell and adding a % sign at the end. But what if you have 100s of rows of data. You can’t do this to every cell. (You can not just format the cells to % format either, excel shows 23 as 2300% then). There must be some simple and intuitive solution for this … umm.

We, data junkies, love pivot tables. We think pivot tables are solution for everything (except for may be global warming and that broken espresso machine down stairs).

Today, we are going to learn 5 awesome pivot table tricks that will make you a star.

Blank rows or Blank cells is a problem we all inherit one time or another. This is very common when you try to import data from somewhere else (like a text file or a CSV file). Today we will learn a very simple trick to delete blank rows from excel spreadsheets. Read this post to findout how to delete blank rows / cells from your excel data in a snap.

Last week I asked you “would you be interested in an online excel training program?” and quite a few of you have responded with “YES”.

So I decided to start excel school. This is a 12 week online training program that will explain various MS Excel concepts to you in an easy to understand format. I am calling it “school” because the program will be fun, exciting and playful – just like school. We will learn from each other as much as we learn from the course itself. Read the rest of the post to see “Excel School Intro” video.

Last week I have reviewed Google’s flu trends chart and told you that is it is very well made. Out of curiosity I made a similar chart in Excel. In this post, I am going to share the experience and results with you. Interested? Read on…