This section will have tips on Microsoft Excel. These tips work in Excel 2002 (XP), but it may also work in other versions.
Tip #1 - Add/Delete rows and columns
The first tip is a keyboard shortcut to delete and add columns/rows.
First select the entire column(s)/row(s) you want to add/delete by either clicking
on the letter (column across top) or number (rows on the left side). Then all
you need to do is press 'Ctrl' + '+' to add a column/row
and 'Ctrl' + '-' to delete a column/row. If you are
selecting multiple columns/rows, you will be adding/deleting multiple columns/rows.
Tip #2 - Keyboard shortcuts
Here are some basic shortcut keys in excel, using the Ctrl key
(doesn't have to be in caps):
Tip #3 - Autofit
This tip will help make it easier to autofit your numbers or
text inside a cell. We all have seen what happens when numbers/text doesn't
fit in a cell. We get ####. This is Excel's way of saying that the numbers/text
does not fit into the size of the cell. There is an quick and easy way to fix
this. Just move your mouse pointer between two columns (let's just say columns
A & B); in this case A should be the column you want to autofit. You will see
a different cursor. When you have that different cursor, double click and all
the cells should be resized to fit the largest amount of number/text you currently
have in the cells.
Tip #4 - Autosum in status bar
In Excel you might have noticed that when you highlight a bunch
of cells with numbers in them, Excel automatically totals it for you. You see
the total on the lower left side. There are other options besides summing up
cells when highlighted. On the bottom (lower left), you should see in a grey
box with the work SUM. You can right click on it
and choose COUNT. This will count the number of
cells you highlighted that contains text/numbers. Try out the other options
also.
Tip #5 - Select multiple cells
To select multiple cells that aren't in the same range just hold
down the Ctrl key, while clicking on the individual
cells. To select a range of cells, click the starting point (any cell you want)
and while holding down the Shift key click the ending
point (the cell that you want to end the range).
Tip #6 - Name Box
There's an easy way to go to a certain cell in Excel. On the
upper left hand corner there is a Name Box (you should see some cell name in
there already; ex: A1). Just click in the box and and type in the cell that
you want to go to.
Tip #7 - Cell Properties
To change how a number is displayed in a cell, just right click
on the cell you want changed and choose Properties.
Under Category, choose the format you want. For example,
let's suppose you have 123456 in a cell and you want it be to displayed as 123,456.
To do that right click on that cell choose Properties->under
Category choose Number. Check the box where it says to add commas and
click OK. That's it.
Tip #8 - Paste special
Ever copy and paste some range of cells and the format is the
same as the copied cells? Ex: You copy, say cell A1, which has a bold format
and a font size of 8. When you copy and paste this cell to another area, the
same formats (bold and font 8) is copied also. To remove the formatting, just
copy and right click on the cell and choose Paste Special.
Then choose Value, so that only the value of the cell
is copied.
Tip #9 - Move/Copy a worksheet
Ever wanted to create multiple copies of a worksheet? Most of
us would just do a Select All and then copy and paste
the information to a new worksheet. But, as usual, there is an easier solution.
To copy a worksheet, right click on the tab (name of tab - Sheet1 if not renamed)
and choose Move or Copy. Then choose where you want
to copy the worksheet (drop down list) and make sure to put a check mark on
the bottom to create a copy of the worksheet and not move it. Then click OK
and a copy of the worksheet will be in the other workbook you selected.
Tip #10 - Autoincrement numbers
Those of us who have used Microsoft Excel must have come across
problems like: listing numbers down a column (1,2,3,4,...), creating the months
in a year, incrementing (increasing) dates, etc. Let's say you created an inventory
list using Excel and you want to keep a list items. You will have to make a
huge list if you have a lot of items. Just right click on the lower right of
the cell (with the little box) and hold down the right mouse button. Then drag
the cell down (or across) and let go of the mouse button. Then choose one of
the options to use. If you chose to increment the cell values, all the values
will be increased in that pattern. If you chose to copy the cells, all the values
will be copied.
Tip #11 - Hyperlink
Sometimes you might want to use a link in your tables to refer
to a website. This is a very easy task. Just type in something on the cell you
want to create the hyperlink (link to a website). Like, News Update or whatever
you want to type. Then select the cell containing the text and go to Insert->Hyperlink
or Ctrl + K and that should create
a hyperlink. You can tell that it is created when it's blue and underlined.
Tip #12 - Excel File Organizer
If you have many excel files which are getting cluttered up, you can place them
within a folder called, for example, “Excel Files” and utilize one
excel workbook to access them all. All you have to do is create a new workbook,
name it whatever you want, “Excel Shortcuts” for example, then follow
these steps:
1. Go to cell A1 (you can start on any cell you
want, A1 is just a good place to start), click Insert->Hyperlink.
2. When the Insert Hyperlink dialogue box opens,
select the Browse for File button. Navigate to
your Excel Files folder and click the first excel file to be hyperlinked. Click
the OK button.
3. Within the Insert Hyperlink dialogue box there is the Text to display text
box which will have the complete address of the excel file that you are hyperlinking
to. Within this text box you can change the text of the hyperlink that will
be displayed. Note: Do not change the text in the Address text box, that will
change the actual address of the file that you are hyperlinking to and your
hyperlink will not work.
4. Repeat steps 1-3 for the other hyperlinks ensuring that you select a different
cell for each hyperlink.
5. To make the first worksheet of you workbook “Excel Shortcuts”
where you have the hyperlinks look better once the hyperlinks are emplaced you
can click Tools->Options->View then remove
the check mark in the Gridlines checkbox. Click the OK button. That will remove
the gridlines from your worksheet giving it a better presentation.
After you have created your hyperlink sheet, to access one of your files, open your Excel Shortcuts file then select the file that you want to view. To close it and open another one, close it with the Close window button, not the Close button. The close window button is the lower of the two X-buttons located on the upper-right hand side of your screen. Clicking the Close button will close the close the entire Excel program.
Tip #13 - Always Ensure that
the Printed Document is Centered on Page
To ensure that your excel document is always printed centered horizontally
and vertically, follow these steps:
1. Click File->Page Setup->Margins Tab.
2. Place a check mark in the Center on Page Horizontally
check box and one in the Center on Page Vertically
check box.
3. Click Print Preview to preview your document.
Note: If the document does not look centered in Print Preview mode, ensure you
do not have extra cells outside of the desired print cell range selected.
The document will print centered both ways. If desired, you can place a check mark in only one of them.
Copyright © 2003-2008 KRC
All Rights Reserved
Disclaimer