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
  • 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

teylyn 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

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

Expert Comment

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now