#335 TROUBLESHOOTING VLOOKUP PROBLEMS
4 quick tips for using Excel
Enhance your spreadsheets with these helpful hints
Published: January 11, 2005
You may use
Microsoft
Excel to create spreadsheets, but do you take advantage of its viewing
capabilities and create customized shortcuts or charts to let a picture
tell your story?
Like a sheet of graph paper, an Excel spreadsheet is divided into rows and
columns that intersect to create cells. Columns are labeled alphabetically
(A, B, C...); rows are labeled numerically (1, 2, 3...). Both alpha and
numeric data can be entered into the cells. Numeric data can be
calculated, charted, or graphed.
For instance, to set up a simple budget, type the word Budget in cell A1
and hit Enter. In cell A3, enter the word Expenses. In cells A5 to Ax
enter expense categories, such as mortgage or rent, phone, food, and
entertainment. Adjacent to the expense categories, in cells B5 to Bx,
enter projected expenses. In the cell below Bx (if x equals 25, this would
be B26), enter the formula to total your expenses: =SUM(B2:B25) or click
on the AutoSum symbol (Greek letter E) in the toolbar and hit Enter. Now
the magic begins. Change any number in cells B2 to Bx and watch your
expense total change.
Microsoft Office products can work together to help you save time
creating documents, presentations, and doing other tasks. Tips
from a document expert can show you how.
 Support Guides
|
Locking columns and rows
If you want to freeze a row of titles or categories so that you can keep
them in sight as you enter data further down your spreadsheet, Excel 2003
has a handy Freeze Panes function. "I keep the results budget projections
locked in one pane so I can see the bottom line impact of expense and
revenue forecasts I make in another pane," says Lyn Mason Green, founder
of CanadianActor Online, an information Web site for aspiring and veteran
actors.
To lock a pane, use your mouse to select the row below or the column to
the right of where you want to freeze. On the Window menu, click
Freeze Panes.
Forcing line breaks
If you enter a lot of data into one cell and then move to a new cell and
enter further data, you may notice that the original information gets
covered up. The solution is to force line breaks. To do this:
| • |
Click on a cell.
|
| • |
Type the first line.
|
| • |
Press Alt + Enter.
|
| • |
Type the second line.
|
| • |
Repeat step 3 to enter additional lines.
|
| • |
Press Enter when finished.
|
Creating a drop-down menu
Lynda Morris, president of NicLyn Consulting Corp, an Internet-based
computer and network service company, often finds herself entering the
same information or formulas in different parts of a spreadsheet. Instead
of typing data repeatedly, she creates drop-down menus that save typing
time. To create a drop-down menu:
| • |
Open a worksheet and label your menu (Days for example) in the top
cell of a column.
|
| • |
Enter data (days of the week, in this case) in the cells below the
label (such as A2 to A8).
|
| • |
Highlight the list, including the label.
|
| • |
Click the Insert menu, then click Name, and then
Define.
|
| • |
In the box under Names in workbook you will see the name of the
range (Days). In the Refers to box, it should list "=Sheet1!"
followed by the range (=Sheet1!A1:A8).
|
| • |
Click Add and then OK.
|
| • |
Select any cell or range of cells in which you want to enter data.
|
| • |
Click the Data menu and then Validation.
|
| • |
From the Allow drop-down menu, select List. In Source,
type "=" and the name of your range (=Days). Make sure that the
In-cell dropdown box is checked.
|
| • |
Click one of the cells you highlighted to see your drop-down menu.
Choose the item you want to enter in the active cell. Enter data from
your list, in any order, in each of the cells you selected.
|
Converting complex calculations into handy charts
Excel can also be used to convert calculations into charts. To create a
pie chart that shows how much of your income you project to spend on a
category-by-category basis, for example, enter spending categories in one
column (like A1 through A5) and your estimated expenditures for each
category in an adjacent column (B1 through B5).
| • |
Highlight the column of expenditures. On the Insert menu, click
Chart, then Pie, then Next, and then Next
again.
|
| • |
Select where you'd like the legend to display by clicking the
Titles tab. In the Chart title box, enter your title
(Family Budget). Then click the Data Labels tab, check the
Percentage box, and then click Next.
|
Insert the chart in a new sheet or as an object in your existing workbook.
Either way, whenever you update your data (as you add actual expenditures,
for instance) the chart will change and you will see how much you spend by
category and the percentage of your total budget that each category
represents.
Article written by Paul Lima and adapted from an original piece from Microsoft
Home Magazine.
