 Selecting Only the Visible Cells in a Worksheet
Summary: There are times when you want to only copy the visible data in a worksheet. Here is how to do this:
An example of this is when you need to copy SubTotals that have hidden rows and columns. A normal copy and paste will include the hidden data.
Here are the steps:
- Select the range of cells
- From the Home tab, in the Editing group, select Find & Select
- Click on Go To Special
- Select Visible cells only
- Select OK
- On the Home tab, in the Clipboard group, select Copy
- Activate the worksheet you want to copy the data to
- On the Home tab, in the Clipboard group, select Paste
Just the visible cells that you selected will be pasted into the worksheet.
|
Displaying Values as Thousands
|
Summary: Many times, it is often useful for display purposes, to show the numbers in a report, as if each number was divided by thousands. Here is how to do this:
- Select the desired cell/cells.
- Right-click on the selected cell/cells.
- Select Format Cells.
- Select the Number tab.
- Under Category select Custom.
- In the Type Box enter "0,".
- Select OK.
All of the numbers will now display and print as if they were divided by thousands. The key to this custom format is the comma after the 0. This is a very basic format. If you add $ signs and format to a number of decimals you use this custom format, but again put the "." at the end of the format.
The advantage to this custom format is that it retains the original number(s) so there will not be errors in calculations that involve cells where you are not dividing by thousands.
A bonus hint. If you want to divide by millions use two commas.
| |
Back to top
Summary: There are times when you just want to add the positive or negative numbers in a range of cells. Here is how to do this:
The trick is to use the SUMIF function. Let's assume that you have a mixture of positive and negative numbers in cells A1 through A40. You want to know what the sum is of just the positive numbers and just the negative numbers.
Select the cell where you want the sum of the positive numbers to show and enter the following formula:
=SUMIF(A1:A40,">0")
Press Enter. You will now have the sum of all the positive numbers in the range.
Now select the cell where you want the sum of the negative numbers enter the following formula:
=SUMIF(A1:A40,"<0")
Press Enter. You will now have the sum of all the negative numbers in the range.
|
Summary: Word's Format Painter is an often overlooked tool. Here's how to use it to expedite the formatting of your Word documents.
The Format Painter is the icon in the Home Tab that looks like a paint brush. Using it enables you to copy formatting from specific portions of your document and apply it to other portions of your document with a few clicks.
To use the Format Painter, follow these steps:
- Select a portion of your document containing the formatting you would like to apply.
- Click the Format Painter button (It is positioned on the Standard Toolbar and looks like a paint brush).
- Select the portion of your document where you would like to apply the formatting.
Word will apply the formatting to your document. The Format Painter is then deactivated, so if you select another part of your document, the formatting will not be applied again.
If you do want to apply the formatting to several areas of your document, double click the Format Painter button after selecting the portion of your document containing the formats you would like to apply. Then, individually select the portions of your document where you'd like to apply the text. When you're done, click the Format Painter button once to deactivate it.
A bonus tip. The Format Painter works exactly the same way in Excel.
|
