Custom Views in Excel - Save filter and header settings for quick reference

PHD reader and commenter Vishy contributed this post through e-mail. Thank you so much Vishy for this very useful tip.

The Problem:

You have created a specific view of your data in Excel (say by filtering, zooming out, changing column width, hiding specific rows, customizing window settings, print settings etc.). This is your reference point say [...]

Brick Charts in Excel - an Alternative to Gridlines

Grid lines provide great help in understanding values in a chart. Here is a handy trick you can use in the next bar chart to spice it up.

Here is how you can get this type of chart (we will call it a brick chart)

First we will make a regular bar chart

Now, let us assume we [...]

What is camera tool and how to use it? [spreadcheats]

Camera tool is your way of creating visual reference in an excel sheet. It is one of the useful and hidden features of excel. Here is how it works. You specify a rectangular area in your workbook and camera tool creates a mirror image of that area as a drawing object. You can move it or resize it. And whenever the contents of original rectangular area changes (charts, drawings or cell values) the mirror image changes too.

Excel Links of the Week [Dec 1]

Every Monday PHD shares some of the best excel related articles around the web so that you are up to date with discussions and new ways to be productive. Here is a list of links recommended for this week.

Advanced Data Validation Techniques in Excel [spreadcheats]

Data validation is a great way to keep your users informed about possible values in a cell and guide them to select something appropriate. As part of the spreadcheats series, in this post we will discuss 2 advanced data validation techniques that can help you when you are modeling a complex worksheet.

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 Dashboards in Excel - A Tutorial

One of my favorite cricket player, the GOD - Sachin Tendulkar has become highest test run scorer.
What do I get if Sachin becomes highest scorer, you may ask.
In order to celebrate this occasion I have created a cool sports dashboard in excel with some of the top test cricket players’ statistics. And, you get [...]