Last night, I looked up my book “Excel Reports and Dashboards” on Amazon only to find that my first reviewer gave me 3 out of 5 stars. He gives a bunch of reasons, mostly having to do with how advanced he is and how the book is too basic for his needs. But that’s not the interesting bit. In his review he says:
“My own favorite technique — setting all of a worksheet’s columns to be very narrow and then merging cells to position the information exactly where I want it — isn’t mentioned”
Interestingly enough, I’ve been using that trick for several years now. I’ve always found this trick difficult to explain verbally, let alone in print. But I’ll give it a shot here.
When building an Excel –based dashboard or report, the average analyst will use one cell per data item. This typically works well. However, a problem arises when you have data items of differing lengths in the same column. Take a look at this basic example I put together.
In this example, notice how label in D2 forces column D to be very wide. This makes the ‘Term’ column (D12) wider than the ‘Win %’ column. This is a typical problem in spreadsheet design – the top table has labels that dictate the column width for everything under them. This not only affects the symmetry and cosmetics of your reports, it also makes inefficient use of real-estate.
To help avoid these issues, I often adjust my spreadsheet so that all the columns are very narrow; basically giving me a workbench where I can have more control over the placement and size of each data item.
Here is the same report in the adjusted grid. As you can see, each data item spans a differing number of columns. This prevents the top-most data items from affecting the ones below.
Notice how the bottom table now has columns that are relatively uniform in size – despite the much wider columns in the top table.
Take away the gridlines and you get a clean looking report.
There you have it Alan Goodall (my 3-star reviewer). I’ve fulfilled my obligation to explain the ‘adjusted grid trick’.
By the way, if you have the book and liked it, I’d appreciate it if you would jump onto Amazon and review the book.
I’ve used this technique a couple times and felt really guilty. Too often, I read that we should stay away from merged cells. They cause grief. Now you’ve gone and confused me!
An approach I have used is to format the report for the second table, and then insert a linked picture of the first table – it doesn’t conform to column widths. (Of course if your user needs to edit contents of the first table, this won’t work)
I figured this out while building my first dashboard. The only drawback is adjusting the print size. Every extra column that is added affects your ability to adequately adjust the zoom level
One very major drawback to this approach is when you have to copy-paste data. Unless the merged cells are the same size, excel doesn’t want to paste it. You then have to unmerge the cells, paste the data, then merge them back together. I run into this one everyday when I try to paste data into an existing report. It gets even “funner” when you try to use the merged cell in an equation somewhere else.
I have a small macro called as “graph_paper”
Sub graph_paper()
Dim desired As Double, looper As Integer
Dim cm As Variant
Cells.ColumnWidth = 8.43
Cells.RowHeight = 12.75
cm = Application.InputBox(“Enter Square Length in Cm”, Type:=1)
If cm False Then
desired = cm * (0.393700787401575) * 72
Application.ScreenUpdating = False
For looper = 1 To 10
If [A1].Height = [A1].Width Then
Else
ActiveSheet.Columns.ColumnWidth = desired * ActiveSheet.Columns.ColumnWidth / [A1].Width
ActiveSheet.Columns.RowHeight = desired * ActiveSheet.Columns.RowHeight / [A1].Height
End If
0.5 as the input makes it look neat
Hi!
Took a look at amazon, your book looks interesting to me. “does it work” with excel 2007 and excel 2007 too?
thanks
G.
Gorky: Yes. The book applies to Excel 2007 as well.
I was fond of using the camera tool for this and dropping the pictures together on a third tab of a workbook. I would make a mental not to avoid deleting any columns on the first two tabs that destroyed the image page.
Mike, I can’t believe you are recommending we MERGE something. What about Center Across Selection? Isn’t that what you typically promote? Merge just seems to screw everything up – in vba as well as copy & paste as mentioned by Robert.
While you can use the camera tool as suggested above by several people, it doesn’t have very high resolution. So I prefer to make my cells really small and snap text boxes (often with dynamic content i.e. linked to named ranges) and graphs to the grid.
Here’s a screenshot of a dashboard I just put together at work using this approach:
http://screencast.com/t/ODVjZTI0MD
(Note I don’t advocate the use of dials, but will merrily implement them if my job depends on it).
One problem with text boxes is that if they are linked to a named range (i.e. if they serve up dynamic content) then you can only format the whole text box, not individual bits of the words/numbers inside. e.g. you can’t make say negative values red and positive values blue, and you can’t use TAB to space numbers.
My way around this is to use multiple text boxes where needed. Here’s an example:
http://screencast.com/t/NmRiNDA3M2Ut
…and here’s the same example with all the individual text boxes selected in the ‘Employment by industry’ box:
http://screencast.com/t/NTVkNjU5
Omar / Janice: Yes – it is quite hypocritical of me to suggest Merging cells after years of railing against Merge and Center.
I would say a couple of things:
1. ‘Center on Selection’ can be used to accomplish this trick (for the most part)
2. There’s the Ideal and then there is the Ordeal. Sometimes you have to Merge and Center dance to avoid issues with layout and real estate space.
3. The Camera tool is an option, but too many linked pictures can cause performance issues and wonky behavior.
I receive several automated reports each day that are built this way– and I have to say that they are a constant pain to use. This approach works if the end user is just glancing at the report, but if they ever want to do more this is not the way to go.
Most of the reports I’ve had to create are presented in PowerPoint. To do what you’ve got in the example, I’d have two different tables in Excel which I’d then embed via links into a slide. I have used this idea previously when I tried to only embed one table, but it is just easier with two in a PowerPoint world.
Re: “The Camera tool is an option, but too many linked pictures can cause performance issues and wonky behavior”
Jan Karel Pietrese posted somthing nice on DDoE regarding this. I’ve used it with success. http://www.dailydoseofexcel.com/archives/2010/05/12/performance-of-linked-pictures/
This, the use of Merge that is, seems fine in an end-product, like this. Like Justin, I’ve dealt with 3rd-party generated reports that used this technique without any visible improvement, just the seeming random insertion of merged rows and columns. I’ve written macros to comb through the sheet and turn it into a table that could be used in further procedures, only to have it fail a few month’s later with the addition of one more tiny column for no good reason I could see.
I use this approach a lot as well; only for an end product though not for data that I’ll need to copy often. I don’t use linked pictures as that adds another layer to your workbook that needs to be maintained and may cause issues.
I get frustrated with merging & unmerging; especially when there are MANY cells involved. (i.e. Your ref to Ordeal vs. Ideal). My preference would be to put the tables in seperate tabs if I can figure out a simple way to display the seperate tabs in a merged (1 screen) report form.
Merging is an evil thing. It should be avoided in worksheets which are used for analysis etc.
For reporting, merging is evil, but often a necessary evil. Since all data on the report worksheet should be linked to data on a real sheet (i.e., a data sheet or analysis sheet), you can avoid many of the problems with merged cells.
As a general rule, I don’t like to merge cells. Yet for a dashboard report that wont’ change I can see the need to design the report in this manner.
Now that I know it works for you I’ll be inclined to consider this technique in the future.