Eight ways to exploit COUNTIF() … and sumif() too [spreadcheats]

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

Excel Formula for Finding Repeated Words in a Cell

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]

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

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]

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]

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

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