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
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.
Question by:lapucca
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 92

Expert Comment

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

Author Comment

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.
LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

LVL 10

Assisted Solution

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.

LVL 10

Expert Comment

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 Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
LVL 50
ID: 36578086
No worries, Rob.

Go the All Blacks!!!

Author Closing Comment

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.

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

688 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