[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
Medium Priority
?
209 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
[X]
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
7 Comments
 
LVL 93

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 (Microsoft MVP / EE MVE) earned 1200 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 10

Assisted Solution

by:broro183
broro183 earned 800 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
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

656 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