Alternatives to merged cells?

Posted on 2012-09-06
Last Modified: 2012-09-07
Hello - can somebody point me to any article or reference that provides ways of arranging different column widths on a spreadsheet without resorting to merged cells?

I am working on a spreadsheet (Excel 2010) that has to provide input areas for different catagories of information, arranged in bands down the sheet, and the user req's are very specific about the order of entry fields in each band.

I am not lucky; the widths of the entry fields can vary widely between bands. I was provided with mock-up's by the designers where they lined everything out by using merged cells, but I would like to avoid this if at all possible.

Question by:mlagrange
    LVL 92

    Expert Comment

    by:Patrick Matthews
    It would be helpful if you could post an illustrative example.

    Author Comment

    ok, say the first band, "Category 1 Stuff", has 2 columns: 1st column is 60 points wide, 2nd is 140 points wide
    The 2nd band, "Category 2 Stuff"  has 4 col's: 160, 20, 20
    The 3rd band, "Category 3 Stuff", has 12 col's: 20, 10, 30, 20, 20, 60, 20, 50, 10, 10, 20, 20

    Like I said, the designers laid this out in the default layout of a new worksheet as 14 col's of 20 points wide, and merged cells as needed.  

    fwiw, this spreadsheet has to pull data from an Access database, and so I'm looping through recordset rows, and writing out the spreadsheet rows in VBA.

    If I have to merge cells while I'm churning through this, I guess that's what I have to do, but I was hoping there was a better way.

    Author Comment

    In the example above, "Category 1 Stuff" has 12 rows, fixed
    The row counts of the "Category 2 Stuff" and "Category 3 Stuff" bands can vary
    LVL 92

    Expert Comment

    by:Patrick Matthews
    If you're already pulling at least some of this stuff from Access, why not simply do the whole thing in Access?
    LVL 17

    Accepted Solution

    You can't do what you want very easily with Excel, because a column in Excel has just one width.  In Word it's easier to create a table with different cell widths on each row.

    If you really HAVE to do this in Excel, one possible (though messy) approach would be to define each column with a 'lowest common denominator' width, in your examples say 10, then merge the number of cells you need on each row to get the desired 'column' widths.  What's tricky is then that you have to keep track of the correct cell to act as the start point for a column's data. (E.g. for your category 2 you would merge 16 cells, then the next 2 and the next 2 to achieve what you want, but the data would have to be inserted in cells 1, then 17, then 19 - this would differ from row to row.)

    Author Closing Comment

    Yep, that's what I figured. Thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now