Skip to Content

Scenario:  There is a requirement to create a  workbook where multiple query outputs (analysis grids) are displayed in a single excel sheet and one below the other.

Issue: There is no control on the number of rows of these queries as for certain variable selections there may be just one row displayed and for other selections there may be multiple rows, so aligning the analysis grid one below another too close might lead to one query output overlapping the other and aligning the grids with lots of blank rows might lead to huge gaps between the query results.

Two query results overlapping:

     /wp-content/uploads/2016/08/1_1020912.jpg

Two query results with too many blank lines in between them:

/wp-content/uploads/2016/08/2_1020883.jpg


Solution: To get the excel behave dynamically according to the number of rows and overcome the rigidity VB approach could be adopted, but this demands a sound knowledge in VB. The workbook can also be rendered dynamic without VB coding by just using an excel formula and custom views. This paper intends to explain the same.


Step 1: Create a workbook with Analysis Grids in a single sheet one below another. Ensure to maintain a gap of 50 to 100 rows from one grid to another so that there is sufficient room for the analysis grid to stretch.


Step2:  To retain the actual blank rows which are necessary to maintain the spacing between two analysis grids or headers enter a formula in column “A” for these corresponding rows as =” ”.

/wp-content/uploads/2016/08/3_1020884.jpg


Step 3: Key in a formula to identify the blank rows (based on the value in column A) on a different column, which will be hidden.

/wp-content/uploads/2016/08/4_1020887.jpg


Step 4: Create a filter on the column with the formula. Filtering out the value “False” will provide all the rows which are non-blanks.

/wp-content/uploads/2016/08/5_1020888.jpg


Step 5: Hide the column with the formula (ie., column Z in this example) and create Custom View from the View tab on the ribbon as highlighted.

/wp-content/uploads/2016/08/6_1020896.jpg

Click on Add and name it as “Formatted”:

/wp-content/uploads/2016/08/7_1020897.jpg

Step 6: Now un-hide the column with formula (column Z) and filter on “True” and hide it back again.

/wp-content/uploads/2016/08/8_1020898.jpg

The output is formatted now with only blank rows:

/wp-content/uploads/2016/08/9_1020899.jpg

Step 7: Hide the column with formula again and create a new custom view.

/wp-content/uploads/2016/08/10_1020900.jpg

Step 8: Testing

Run the workbook to find the result display with the blank rows between query results post variable entry:/wp-content/uploads/2016/08/11_1020901.jpg

Click on “Custom Views” under tab – “View”. Double click on “Formatted” and the workbook with the blank rows trimmed should show up:  

/wp-content/uploads/2016/08/12_1020905.jpg

/wp-content/uploads/2016/08/13_1020906.jpg

Again click on “Custom Views” and choose “Unformatted” to return back to the initial view with blank rows.

Buttons could be inserted to assist the users with switching of the view and small macros can be recorded and assigned to these buttons, if done so the simple macro for each button appears as below:

/wp-content/uploads/2016/08/14_1020907.jpg

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply