Create excel worksheet with specified range?

Question

I am working in excel 97 and have set up a workbook with several sheets in it.

Each sheet contains formulas and lots of formatting. As there are going to be 52 sheets, the file size is huge. At the moment the formatting I am using covers every cell of every workbook.

I have seen worksheets where they only display a certain range when they are opened, for example, only the range a1:aa111, in each sheet. How do you acheive this without simply hiding the rows/columns? I have tried hiding the cells but it doesnt reduce the size of the file.

Any help would be appreciated.

Answer 1

Select "View" in the menu bar on top, and then "page break preview". Then drag the dotted line for the page breaks to the range you want to see displayed. Close the other pages for the range you do not want displayed. Good luck!

Answer 2

By default each worksheet in a work book has 65,536 rows and 256 columns. I don't think you can change this.



What you have probably seen is that the scroll area available has been limited thus making you think that the sheet size has been reduced. You can do this scroll limitation in VBA, goto tools>macro>visual basic editior. Or press alt + F11.



Another window should open and in the top left hand corner of the window it should show VBA project and then underneath list the sheets in your work book. Click on the sheet that you want to limit and in the bottom box in scroll area type the range you want the sheet to be limited to ie $A$1: $AA$111. Then click the save button.



If you have applied a format to every cell on a worksheet then the file size is going to be large. Just format the cells you are using, this will reduce the file size. Why use 52 sheets, why not split up one sheet into 52 named areas, or if its for 52 weeks of the year set up 12 sheets and then split these into weeks using named ranges.



Hope this helps.

Answer 3

I am not sure how to answer your question but note this:



- Excel has a smart way of letting you hide columns or rows - take a blank sheet, highlight columns G and H, go Data, then Group and Outline, then Group - you now see a minus sign above column I, click on it and G and H disappear with a + sign to bring them back - I use it all the time and it makes my sheets really neat. I use Excel 2002, I hope this exists in Excel 97



- However I am really troubled by the number of tabs (sheets) you will end up with. With respect this does not come across as an efficient workbook design. Is this 52 as in 52 weeks? I cannot give you more specific hints without more detail but if your 52 sheets are going to be very similar to each other then I will be damned if there is not a way of using far fewer sheets and defining the data differently. And your workbook could be much smaller too. By the way how big is it now?



I am not in IT but I have used Excel for many years and I pride myself on designing very clean and compact sheets and formulae, and using formatting in a smart way. I often end up with workbooks of 5-10MB, sometimes as big as 30-40MB. I think the worst I did was 140MB once. They contained an awful lot of data and calculations, but they could not be smaller, and they are perfectly usable. Depends on your machine too. Anyway If you have more questions, just ask!



Cheers

Answer 4

it is a thanks to cover particular rows or columns in excel: a million. commence Microsoft Excel and open an latest workbook that includes a column or row that you may opt for to cover. in case you'll desire, commence a sparkling workbook and enter archives right into a column or row that you may opt for to cover. 2 click in any cellular that lies interior the column or row that you may opt for to cover. The cellular could have a thick black line round it once you've chosen it, which signifies that you'll move on to the subsequent step. 3 opt for the "format" menu and click on "Column" in case you opt for to cover a column. in case you opt for to cover a row, click on "Row." 4 click "cover" in both the "Column" or "Row" submenu to cover the column or row that includes the cellular you've chosen. The column or row will now be hidden from view. you'll discover the hidden column letter or row huge type will be lacking out of your workbook.