Excel
Tips & Tricks 1 :
|
 |
 |
To SELECT a table/range quickly. Press
Ctrl - Asterisk (on the ten key pad) or Ctrl - Shift - Asterisk (the upper case 8 for
Touch typist.
Just make sure you are in a table of information in Excel.
If you select Automatic Page Breaks, you can see a broken
line showing the edge of your print area. So as you are adding or deleting rows/columns
you know what is still in the print area.
If you go into the File: Page Setup...
Click: Page (tab)
Change the Scaling - Adjust to:
To about 75% your spreadsheet broken lines will change accordingly.
Very nice way to see if the numbers and text are going to fix on one page.
Sorry the Fit to: want mark you sheet for you.
To be able to toggle your view so you can see all of your formulas, try this.
Select: Tools: Options...
Click: View (tab)
In the middle of the dialog box, click on "Formulas".
Now you can see all of the formulas on/in your spreadsheet.
To move quickly in your table. If you are at the top of
table - Double-click the bottom border of the active cell (with standard
pointer) it will move you to the bottom. If there are no blanks. This works in all 4
directions. If you add the Shift Key when you double-click it will
highlight as it goes. Great for selecting the headers when not all are showing on the
screen.
Also to move from sheet to sheet fast; Right click the navigation
buttons. The little arrow heads bottom left corner.
Select the sheet you want to GoTo or More
sheets.... if the one you want is not showing.
Then double click it.
- The AutoFill can do more: Right drag the AutoFill
handle to get a menu, (more selections)
- Select some labels/headings (most
individuals type the same labels to build a spreadsheet) then:
- Select: Tools: Options: Custom List:
- Click the Import button at bottom right and you
have a list you can duplicate with the AutoFill handle anytime. Just type any one of the
words that you had in the list and drag the AutoFill handle.
- Sorting is hard sometimes when you have a group of
items that you want in a certain order but not the standard way. (A-B-C, etc.)
- example: High Medium Low
- If you sort as they are you will get - High Low
Medium
- I want High Medium Low - in that order; so I can
ship out all of my High priorities first, etc.
- GoTo Tools: Options: Custom List:
- In the text box Type:
- High press [enter]
- Medium press [enter]
- Low press [enter]
- Click on the Add button on the
right
- Click OK
- Now select the column that has the high medium and
low listed (if you don't have one create it)
- GoTo Data: Sort:
- Click Options button
- Click the down arrow for First Key sort order
- Select High Medium Low from the list
- Click OK
- Click OK
- Now your list is in the correct order by all the
Highest first, Mediums second, Lows last.
Another Big question is how can I see two different sheet
at the same time, that are in the same workbook.
Select Window: New Window
Select Window: Arrange: Title (I like title myself)
=sum(a1:d1)&" this is how"
Don't forget the space after the first quote or the text will be right next to the
numbers.
=TEXT(A2,"M/D/YY")&" "&TEXT(DAY(A2),"dddd")
You get 2/28/98 Saturday
Both in one cell, but remember A2 is a typed in date. The date format in A2 will not
matter.
Have you build a chart for a 12 month period and only have data for the first 9 months.
The other 3 months data is not ready yet. So you plug in 9 months of data leave the other
3 cells for the other months blank. Build your chart and the last three months nose dive
to zero. Chart looks bad because you just wanted the line to go as far as the first 9
months and forget about trying to show a line for the last 3 months until you put numbers
in those cells.
Answer: Format the cells as follows; Select the three cells that are blank, Press Ctrl
+ 1 to bring up Format Cells. Select Custom under Category, and in the text box on the
right type: @NA
Now your chart will forget the last three cells. :-)
To create a link in your workbook, it's best done by using the mouse.
The key to creating a link is to remember, I start in the cell where I want the linked
figure to show up in.
To setup a link from another cell do the following:
Select the cell that you would like to see the figure from another location show
up in.
Type: =
Now using your mouse, (scroll over/down/up to the cell with the figure) click on the
figure.
At the top left of your screen you will see a green check mark, click it.
Now you have a link.
To setup a link from another sheet do the following:
Select the cell that you would like to see the figure from another location show
up in.
Type: =
Now using your mouse, click the sheet that has the figure that you would like to show up
on your original sheet. (where you typed the equal)
Now using your mouse, (scroll over/down/up to the cell with the figure) click on the
figure.
At the top left of your screen you will see a green check mark, click it.
Now you have a link.
To setup a link from another workbook do the following:
First make sure you have the second workbook open:
Make sure the first workbook that you want the figure to show up in is the current
workbook showing in Excel. (on top)
Select the cell that you would like to see the figure from the second workbook show up in.
Type: =
Now using your mouse, click the word Window on your Excel menu.
Select the second workbook. (the one that has the figure that you would like to see in the
first workbook)
When the second workbook opens (now the top workbook)
Click the sheet tap of the sheet that has the figure you would like to see in the first
workbook.
Now using your mouse, (scroll over/down/up to the cell with the figure) click on the
figure.
At the top left of your screen you will see a green check mark, click it.
Now you have a link.
| 
