Solved

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?

Posted on 2011-09-21
7
183 Views
Last Modified: 2012-05-12
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
Comment
Question by:lapucca
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36577724
Simply select the unused columns/rows, right click, and select Hide.
0
 

Author Comment

by:lapucca
ID: 36577759
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
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 300 total points
ID: 36577871
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
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.

 
LVL 10

Assisted Solution

by:broro183
broro183 earned 200 total points
ID: 36578000
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
 
LVL 10

Expert Comment

by:broro183
ID: 36578021
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36578086
No worries, Rob.

Go the All Blacks!!!
0
 

Author Closing Comment

by:lapucca
ID: 36707911
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

790 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