Simple Excel 2011 Question

[M-B-P]

Hey,

I just switched over from Number to Excel 2011. I have a simple question. How do I limit the number of rows and columns in my spread sheet. For instance, I want a sheet with 10 rows and 5 columns. By default a worksheet has ~1000000 rows.

 

[MisterMe]

Numbers and Excel are different applications. Excel works as you describe. It does not work like Numbers and cannot be forced to do so [in this regard].

 

[marc11]

In Excel you need to use the "Hide" function to hide rows or columns you do not want to see.

 

[Gregg2]

Tried that ^ for pages and pages of rows I don't need. The "Hide" function was greyed out and unavailable. I've used Hide to zap rows within the area that I'm working, but I haven't found a way to get rid of all those extra cells.

Bad Info (in my post) ^
Tried again with sheet unprotected.
It works!

 

[M-B-P]

Thank you Sir for informing me that Excel and Numbers are different applications and that Excel can't be "forced" to work like numbers.

marc11, thanks for your info. I tired that and it seems like a good temporary solution.

I used to have Excel for Windows, and i'm pretty sure that you could have actually right clicked and press "delete" on any of the rows and columns. I try that for the mac version and I get a "Not enough memory" dialog. Can someone try that and see if it actually deletes the rows /columns rather than just hiding them. I am only running safari and excel at the moment, so i'm sure there is enough ram.

And from what I learned, there is no way to specify how many rows and columns you want the sheet to be. Seems like a prettily simple thing to accomplish. Batch hiding/deleting all of those unwanted columns/rows seems like an annoying thing to do.

 

[M-B-P]

I actually found this under the Excel Help Center,


Select the rows or columns that you want to delete.
On the Edit menu, click Delete.
The other rows or columns shift up or to the left to fill the space left by the deleted row or column.
Note To delete the contents of the rows or columns, not the rows or columns themselves, press DELETE ⌦. If your keyboard doesn't have a DELETE ⌦ key, press FN+DELETE.

I tried that and am getting the "Not enough memory" dialog. Any idea why that is happening or how to fix that?

 

[reputationZed]

I think what MM was trying to say is that though Excel and Numbers are both spreadsheet programs they take different approaches to implementing the concept of a spreadsheet into a user interface. Excel follows a more traditional (VisiCalc, Lotus 123, Quattro Pro) approach in which formatting data takes a back seat to manipulating numeric data through functions. Numbers places much more emphasis on format while lacking many of the more advanced functions of Excel. In some ways Numbers is more of a table program than a spreadsheet program.

There may be an option in Excel Preferences to limit the spreadsheets size, but I don't have Office 2011 on this computer, so I can say for sure. The other possibility would be to limit the size using VBA.

One way to visually simulate a limited number of rows and columns would be to select the entire sheet, give it a background color, and remove its borders. Then go back and selet the rows and columns you want to show and remove the background color and add the borders back in. I say visually because it will look like a smaller sheet but it will still function like a 10,000 column spreadsheet.

Can't help you on the not enough memory issue.

 

[reputationZed]

Or you could try this
http://spreadsheets.about.com/od/excelformulas/ss/2011-05-14-excel-2010-limit-rows-tutorial.htm

 

[rc135tx]

Hey,

I just switched over from Number to Excel 2011. I have a simple question. How do I limit the number of rows and columns in my spread sheet. For instance, I want a sheet with 10 rows and 5 columns. By default a worksheet has ~1000000 rows.

I don't think you can actually limit the number of cells potentially available as you are working, but you can limit the number you see.

Under View, select "Page Layout" then adjust the row widths & cell heights until you get the look you want.

c.

 

[M-B-P]

Thank you for all of your suggestions. The page layout method by described by rc135tx worked fine.

However, with your suggestion reputationZed ,I decided that I need an application to store,organize, and sort non-numeric data for my purposes rather than manipulate numeric data. So I think that I am going to back to Numbers, as it provided a way to organize information for me in a much more sufficient and simpler way than I could with Excel. Thanks

 

[Gregg2]

There may be an option in Excel Preferences to limit the spreadsheets size,

There's not such an option in Office 2004.

Thank you for all of your suggestions. The page layout method by described by rc135tx worked fine.

But all the extra rows and columns are still there, aren't they?

(See edit to my post above. #4)