Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

How can I send my excel file to people tha doesn't show all that empty rows at the bottom or the extra columns to the right?

I'm using office 2007.  I received very professional looking excel file where no extra empty rows at the bottom that can go on forever or extra empty column to the right on the sheet.  How can I do that too?  Thanks.
0
lapucca
Asked:
lapucca
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
Patrick MatthewsCommented:
Simply select the unused columns/rows, right click, and select Hide.
0
 
lapuccaAuthor Commented:
The problem is that when I select to delete and right click delete it does nothing.  When I tried to select the rows below to hide, it just keeps going.  I mean, there are endless rows to select.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Select the row below the last row of data, then do a Ctrl-Shift-downArrow to select all rows right through to the bottom. Then hide as per Patricks instructions.

Select the column to the right of your data, then do a Ctrl-Shift-rightArrow to select all remaining columns. Hide.

Deleting will not do anything. The spreadsheet will always have its maximum number of rows and columns. You cannot delete them. Just hide them.

cheers, teylyn

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
broro183Commented:
hi Lapucca,

If you re-read Patrick's comment you will see that he suggested "right click, and select Hide", not "right click delete".



I like using the keyboard & the following shortcut key combinations may be the fastest way for you to select the "endless rows" & columns. If you are not a keyboard person you can still follow the concepts with the mouse but I suspect it will be slower.

1) Select a cell on the desired sheet & press the keyboard key combination of [ctrl + End] to select what excel thinks is the "last cell" on the sheet. This first step assumes that you have no excess formatting etc which is dirtying extra cells beyond the end of your data.
2) Check which cell is selected & if necessary, adjust the active cell so that it is the last cell with data in it.
3) Press the [down arrow] to select a cell in the first empty row and then press [shift + spacebar] to select the whole row. Then press [ctrl + shift + down arrow] to select all blank rows to the bottom of the sheet.
4) Press the [right mouse button] key, on my keyboard this is the key that has an image of a small rectangle with lines & an arrow and it is the key that is two to the right of the spacebar. This will bring up the context menu that you have already seen, now "select Hide" :-)
5) You shouldn't be able to see the activecell now, but if you press the [right arrow] you'll see the cell address change at the top left of your screen and it should state an address that is in the first blank column to the right of your data.
6) Press [ctrl + spacebar] to select the whole column. Then press [ctrl + shift + right arrow] to select all blank columns to the right of the sheet.
7) Press the [right mouse button] key, on my keyboard this is the key that has an image of a small rectangle with lines & an arrow and it is the key that is two to the right of the spacebar. This will bring up the context menu that you have already seen, now "select Hide" :-)

Here are some brief comments on "delete":
- the [delete] key on the keyboard, actually "clears the contents" of a cell, it doesn't "delete" it.
- the "delete" option on the right click menu does really "delete" the cell or row BUT a spreadsheet will always have a fixed number of rows, so if you delete one, a blank row is added (for want of a better word) to the bottom of the spreadsheet. In other words, you would never be able to "delete" all the rows which is why we suggest "hide"*. In excel 2007 formatted files, the fixed number of rows is 1,048,576, whereas files that are compatible with/created in excel 2003 have the fixed number of 65,536 rows.

*Although we have suggested "hide" in response to your request for achieving a "very professional looking excel file", I often discourage hiding all the blank rows and columns for the following reasons (which are all very context specific to the sheet):
- a recipient may want to use some empty cells to add comments or do their own analysis. If the cells hidden, will/does the user know how to make them visible?
Or will they know how to rehide any extra cells they decide that they don't need to use?
- if new data is copied into the sheet (eg as a large block) and the number of pasted rows is greater than the visible rows, all data will be pasted (assuming it is less than the fixed number of rows (see above). However, a user may not realise that some of the data is hidden.
- if the sheet is distributed as a "finished product" and people are not expected or even meant/allowed to do any further data manipulation/analysis, then it may be better to distribute as a pdf file.

hth
Rob
0
 
broro183Commented:
Ooopps, I should refresh my page before posting!
Or perhaps just be less verbose in my answers ;-)

Teylyn, long time no chat - I hope all is well back in NZ :-)

Rob
__________________
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
No worries, Rob.

Go the All Blacks!!!
0
 
lapuccaAuthor Commented:
It doesn't matter delete or hide.  My problem is to select all the unused rows.  I was able to hide after following both of your direction.  Thank you.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now