Eight ways to exploit COUNTIF() … and sumif() too [spreadcheats]
on 12 Nov, 2008 in Excel Tips | 3 Comments
![Eight ways to exploit COUNTIF() ... and sumif() too [spreadcheats]](http://chandoo.org/wp/wp-content/uploads/2008/11/using-countif-excel-help.png)
If for every countif() I write excel paid me a dollar, I would be a millionaire by now. It is such a versatile and fun formula to work with that I have decided to write about it as third post in our spreadcheats series.
Using COUNTIF() to replace pivot tables: We all know that you can [...]
Read More >>Excel Formula for Finding Repeated Words in a Cell
on 11 Nov, 2008 in Excel Tips | 5 Comments

If you ever need to know whether a particular word is repeated in a cell (for eg: a file extension or telephone number or name of the customer) here is how you can find it.
=if(lower(cell-with-text)<>SUBSTITUTE(lower(cell-with-text),lower(word-to-check),”",2),”Yes”,”No”)
We are using substitute() to replace the second occurance of a particular word and then compare this with original cell contents. [...]
Extracting Unique, Duplicate and Missing Items using Formulas [spreadcheats]
on 6 Nov, 2008 in Analytics, Excel Tips | 1 Comment
![Extracting Unique, Duplicate and Missing Items using Formulas [spreadcheats]](http://chandoo.org/wp/wp-content/uploads/2008/11/duplicate-items-excel-formula-find.gif)
Often I wish Microsoft had spent the effort and time on a data genie (and a set of powerful formulas) that can automate common data cleanup tasks like extracting duplicates, makings lists unique, find missing items, remove spaces etc. Alas, instead they have provided features like clippy which are intrusive to say the least.
So as [...]
Select & Show One Chart from Many
on 5 Nov, 2008 in Excel Tips, Featured, hacks, visualization | 39 Comments

Yesterday I have learned this cool excel charting trick and I cant wait to share it with you all.
The problem: I have too many charts & want to show one based on selection
You have made 3 charts to show your company performance in the last 8 years. But you don’t want to clutter the project [...]
Relative vs. Absolute References in Formulas [spreadcheats]
on 4 Nov, 2008 in Excel Tips | 5 Comments
![Relative vs. Absolute References in Formulas [spreadcheats]](http://chandoo.org/wp/wp-content/uploads/2008/11/excel-relative-formula-references.gif)
This is the first installment of the spreadcheats series.
I have used excel an year and half before I learned about the relative and absolute references. It was such a joyous feeling to find out that you can actually write one (or just a few) formulas and use the power of auto-fill to do the dirty [...]
Adding Box Plots to Show Data Distribution [Excel Dashboard Tutorials Part 2]
on 29 Oct, 2008 in Analytics, Excel Tips, visualization | 3 Comments
This is a Guest Post by Robert on Visualization Techniques for Excel Dashboards.
Background
With the dashboards using excel - 4 post series here at Pointy Haired Dilbert we developed a small excel based dashboard that enables the user to scroll within a larger set of data, to sort by a selected KPI with one click, to [...]
Sports Statistics Dashboard in Excel - Few More Alternatives
on 24 Oct, 2008 in Excel Tips, visualization | 0 Comments
First of all, thanks everyone for making the should you always start barcharts at zero? discussion lively. Almost everyone felt that we should start bar charts at zero.
After spending sometime with my initial test cricket statistics dashboard, I have created few alternatives. You can see them below.
But somehow I feel that I haven’t found very [...]


- Pankaj Verma: Know what - I wonder, perplexed and bewildered to think that...
- Bhars: Wonderful calendar!! Thanks a lot!...
- Stružák: Hi, would you mind publishing a calender template with weeks...
- Ketan: In any cell after writting the formula and bfore pressing th...
- Ketan: You may use any shape or text box too and link the data of d...
More Comments