13 Oct 09 Giant chart with conditional formatting
Sometimes I meet demonstrative tasks where the great number of data makes impossible to show it in even a full monitor/paper sized chart.
A few days ago, there was such a task, where I needed to illustrate the harness of hundreds of birds. Those being less adaptable and/or not being a Excel expert, might consider this impossible.
I’ve got a trick to solve it: I don’t use a chart.
Conditional formatting is a perfect tool to do this. Using it, colored cells will compose the diagram. It is possible to create multiple-monitor charts with it. Of course this technique has its own limits too: for an example the number of disposable columns, and the fact, that not all types of diagrams can be created with it. (It can be surely used to create a: bar or a column chart, a scatter plot or a Gantt chart).
This is how a giant chart looks like:
I put some small things on it for better perspicuity and legibility:
- Aid-lines each 10 row and 10 column.
- A tricky headpiece to ensure the legibility of each piece of data in contempt of downsizing.
What do you think about it?
I would appreciate if you would write it between the comments.
16 Feb 09 Relative range names in practice
With range names you can do big tricks with excel.
I will show you one of these tricks today:
When you define a range name, then excel will propose the absolute cell reference as default.
But you can use a relative or a mixed reference too.
The active cell is the starting point.
You have to create your reference like you would write into that cell.
My trick is:
- Select A2 cell.
- Choose Insert –> Name –> Define
- Type a name for the range: “Last”
- Write in the refers to box “=A1″ without quotes and without dollar signs.
- Give a name to this range, for example “last”
This range name will refer to the upper cell to the current position.
It can be used like this:
=SUM(B2:Last)
will give you a dynamic range that will grow or shrink when you insert or delete some rows, even if the deleted row is the last row from the range.
This is a perfect solution.
If you insert multiple rows and don’t fill every cell, the normal reference will not upgrade, the relative name will always work.
28 Jan 09 VBA Error Handling with line numbers
Sometimes it is easier to find an error if we know the code line where the error occurred. I have a method to show this in an error message.
This is a light version of my error handler and focuses only to the line number of the error.
There is a variable called “erl” which contains the line number of the error, so we can use it in the error message:
MsgBox Err.Number & " - " & Erl & " - " Err.Description
But this will show 0 instead of the line number if we didn’t put numbers at the beginning of the rows.
Dick Kusleika wrote about the MZ Tools, and MZTools is a great tool for this too.
There is an “add line number” icon on the toolbar which made the line numbering in my stead.
Add Line Number Icon
Voila, it is done, and we can use it.
Subscribe!
