Hiding Unused Rows And Columns in Microsoft Excel Worksheet.
Some worksheets are small and some are large. Knowing how to quickly get around your worksheet can help you work faster, easy and with less frustration. But has this ever happened to you? You hold down the Page Down key to quickly move to the bottom of your 40-page worksheet only to find that you scrolled too far past the end of your data?
Maybe you sometimes press the CTRL+END keys to go to the bottom-right corner of your worksheet but find that there are many blank rows below and blank columns to the right of your original data. Then you have to scroll back to your data.
Maybe you have discovered that holding down the CTRL key and pressing one of the arrow keys is a quick way to get to the end of a long range of data or, if you are starting from a blank cell, to the first non-blank cell in that direction. Have you evere pressed CTRL+right arrow and arrived at last column (column XFD)? Or pressed CTRL+down arrow and found yourself at last row (row 1,048,576)?
So, Here is the question, what can you do about it?
Well, here's an option some peoples may not have heard of.
Have you ever thinked why your worksheets have to be so big when they usually contain such a small amount of data?
Every excel user know how to hide rows and columns in MS Excel, but what if you could hide ALL of the unused columns to the right of your data and ALL the unused rows below your data?
What would that look like? You may be very surprised.
Some worksheets are small and some are large. Knowing how to quickly get around your worksheet can help you work faster, easy and with less frustration. But has this ever happened to you? You hold down the Page Down key to quickly move to the bottom of your 40-page worksheet only to find that you scrolled too far past the end of your data?
Maybe you sometimes press the CTRL+END keys to go to the bottom-right corner of your worksheet but find that there are many blank rows below and blank columns to the right of your original data. Then you have to scroll back to your data.
Maybe you have discovered that holding down the CTRL key and pressing one of the arrow keys is a quick way to get to the end of a long range of data or, if you are starting from a blank cell, to the first non-blank cell in that direction. Have you evere pressed CTRL+right arrow and arrived at last column (column XFD)? Or pressed CTRL+down arrow and found yourself at last row (row 1,048,576)?
So, Here is the question, what can you do about it?
Well, here's an option some peoples may not have heard of.
Have you ever thinked why your worksheets have to be so big when they usually contain such a small amount of data?
Every excel user know how to hide rows and columns in MS Excel, but what if you could hide ALL of the unused columns to the right of your data and ALL the unused rows below your data?
What would that look like? You may be very surprised.
 |
| Hide Unused Rows N Columns |
Limit access to only the visible area of your Microsoft Excel worksheet.
It may not be obvious how to hide all columns or rows beyond the range of cells that you are using, so here's how to do it...
To hide all columns to the right of column M, click the column N heading, hold down the CTRL and SHIFT keys and press the right arrow. This assumes that all cells to the right of the Active Cell (N1 in below example) are blank. You should now have all columns from N to the end of your worksheet selected. Right-click anywhere within the selected range and choose Hide.
To hide all rows below row 50, click the row 51 heading, hold down the CTRL and SHIFT keys and press the down arrow. This assumes that all cells below the Active Cell (A51 in this example) are blank. You should now have all rows from 51 to the end of your worksheet selected. Right-click anywhere within the selected range and choose Hide.
When you want to unhide the columns you have hidden to the right of your data, click the last column heading and drag your mouse to the right, release the mouse button, right-click the column heading and choose Unhide.
When you want to unhide the rows you have hidden below your data, click the last row heading and drag your mouse down, release the mouse button, right-click the row heading and choose Unhide.
You may not want to do this with all of your workbooks, but there may be advantages to doing it with some of them.
What Are the Advantages of Limiting The Visible Area of Your Worksheet?
Limiting the visible area of a woksheet...
1) ...allows you to use the CTRL+<arrow keys> and CTRL+SHIFT+<arrow keys> without accidentally zipping at warp speed to the far ends of your worksheet.
2) ...forces inexperienced (and experienced) Excel users to stay within the work area.
3) ...prevents accidentally creation of a 'last-used cell' far beyond your actual data, that can sometimes lead to printing problems and can unnecessarily increase the size of your workbook.
4) ...makes it look cleaner and focuses the users attention on the most relevant information.
5) ...makes it easier to set and reset the Print Area.
Be careful about hiding formulas and other data in this hidden area because, unless the workbook is protected, users may inadvertently delete the rows or columns that include the hidden information.
It may not be obvious how to hide all columns or rows beyond the range of cells that you are using, so here's how to do it...
To hide all columns to the right of column M, click the column N heading, hold down the CTRL and SHIFT keys and press the right arrow. This assumes that all cells to the right of the Active Cell (N1 in below example) are blank. You should now have all columns from N to the end of your worksheet selected. Right-click anywhere within the selected range and choose Hide.
To hide all rows below row 50, click the row 51 heading, hold down the CTRL and SHIFT keys and press the down arrow. This assumes that all cells below the Active Cell (A51 in this example) are blank. You should now have all rows from 51 to the end of your worksheet selected. Right-click anywhere within the selected range and choose Hide.
When you want to unhide the columns you have hidden to the right of your data, click the last column heading and drag your mouse to the right, release the mouse button, right-click the column heading and choose Unhide.
When you want to unhide the rows you have hidden below your data, click the last row heading and drag your mouse down, release the mouse button, right-click the row heading and choose Unhide.
You may not want to do this with all of your workbooks, but there may be advantages to doing it with some of them.
What Are the Advantages of Limiting The Visible Area of Your Worksheet?
Limiting the visible area of a woksheet...
1) ...allows you to use the CTRL+<arrow keys> and CTRL+SHIFT+<arrow keys> without accidentally zipping at warp speed to the far ends of your worksheet.
2) ...forces inexperienced (and experienced) Excel users to stay within the work area.
3) ...prevents accidentally creation of a 'last-used cell' far beyond your actual data, that can sometimes lead to printing problems and can unnecessarily increase the size of your workbook.
4) ...makes it look cleaner and focuses the users attention on the most relevant information.
5) ...makes it easier to set and reset the Print Area.
Be careful about hiding formulas and other data in this hidden area because, unless the workbook is protected, users may inadvertently delete the rows or columns that include the hidden information.
0 comments:
Post a Comment