Other Tip Collections
Adding a Chart Type
Do you have a special type of chart, with custom specifications about scale and such, that you use repeatedly in Excel 2000? You can save your chart definition as a Custom Chart Type that you can select from the Chart Wizard when you create new charts. (Unfortunately this saved definition doesn't preserve any resizing you might have applied to a chart embedded within a worksheet.)
Once you've created the chart, right-click on the Chart Area and choose Chart Type. Select the Custom Types tab. Next, select the radio button for User-defined. Use the Add... button to add this currently selected chart as a new type with a name of your choosing and any description you want to include. To change the definition you'll have to Delete the custom chart type and Add it again.
Also note that this Chart Type dialog offers a "Set as default chart" button whether you've selected a Standard or Custom chart type.
Decimal Alignment (With Varying Significant Digits)
A decimal tab in Word will let you align a column of numbers at the decimal point even when there are varying numbers of digits to the right and left of the decimal point. To do the same in Excel, Format | Cells using a Custom format of ????.??? with enough question marks on either side of the decimal to accommodate the most digits you have in any of your numbers.
Default File Location
To specify the default file location, the folder you're taken to by default when you open or save a file -- Tools | Options | General tab; type in the desired path in the "Default file location" field
Displaying Cell Formulas Rather Than Results
There are two ways to change Excel's cell display for formulas to display the actual formula rather than the formula results.
Editing with the Function Wizard
There are times when it's best to use the Function Wizard to edit an existing function. All you have to do is click the cell that contains the function and then either press Shift-F3 or click the Function Wizard icon (f of x) in the toolbar. This will open the Function Wizard for the last function used in your formula. To move the Function Wizard to other functions in your formula, click on the desired function within the display of your formula in the formula bar. Work through the Function Wizard(s) as normal and click Finish to close the wizard and apply your changes to the selected cell.
Is there a way to enter a date so that all four digits appear in the year--so that the year 2000 will appear as '2000' instead of '00'? You can't enter days to display that way, but you can format them to, as follows:
Have ever considered grouping the sheets in your workbook together? This function might prove dangerous if inadvertently left on but it can be useful temporarily while you complete some functions. For example, you could check the spelling in all the sheets of a workbook:
Hiding Cell Contents from the Printer
Need to produce a printout where certain cells (not an entire row or column, you could right-click and Hide them) in the print area need to be hidden? One trick is to set the font color in those cells to white.
High Resolution Chart Capture
Normal Copy | Paste techniques from Excel into a variety of programs may not give you the resolution you need to send an Excel chart out for publication. One technique to get around this problem is to use
[Alt][Print Screen] to capture an image of your currently active window (Excel chart window) to the clipboard. Then you just Paste that into a graphics program (IrfanView, PaintShop, Photoshop) and crop.
To get the resolution you need, set your desktop/screen resolution to something obscenely high -- 2048x1536 (when you may usually work at 1024x768). This will allow you to resize your graph to fill the screen and go from there.
To change your screen resolution -- Right-click your Desktop (background) and choose Properties | Settings and click and drag the screen resolution slider to the resolution you want.
Including the Full Path with Filename in a Header/Footer
NOTE: With Office XP/2002 this option is provided in the header/footer dropdown choices.
A Visual Basic for Applications (VBA) macro can make this a quick and automatic task. The subroutine below inserts the full path and file name into the left footer for every sheet in a workbook.
Sub CustomFooter() ' Inserts the file name and path into the page footer ' for each sheet in the active workbook For Each sht In ActiveWorkbook.Sheets sht.PageSetup.LeftFooter = ActiveWorkbook.FullName Next sht End SubFor more details see the full FAQ: http://webdata.forestry.oregonstate.edu/helpdesk/faq/answer.php?ID=899
Line Break Within a Cell
With text wrap formatting set in a cell (Format | Cells | Alignment | check Wrap Text), sometimes you want to force a line break at a certain point. Use Alt-Enter.
To move the contents of a cell or a block of cells to a new location, select the cell(s) and move the mouse over the edge of the selected cells until the cursor turns into a pointer. Now, use the mouse to grab the cell by one of the edges, click and hold down the mouse button while you drag the data or formula to a new location. When you reach the new location, release the mouse button.
Moving Sheet to Sheet, Window to Window
Is there a shortcut key for switching between sheets (and windows) in an Excel spreadsheet/workbook? Yes there is:
Pasting Column Widths
When you Copy and Paste cells in Excel (Ctrl-C, Ctrl-V) you can't automatically paste in the column widths that accommodate that data. But, you can paste in the column width by using Paste Special. After using a normal Paste to paste in the data and cell formats, right-click the new range of data you just pasted, choose Paste Special, and select the Column Widths radio button.
Selecting Only Visible Cells
When you've filtered data or hidden rows or columns, selecting a block of cells will also, disappointingly, select the cells that are not visible. There is a hidden command that will let you select only the visible cells. It's hidden in that you have to add this command to your toolbar in order to use it.
Setting Non-scrolling Rows/Columns
How do I get a couple rows (or columns) to always stay visible in my window even when I scroll far down (or to the right)?
Before giving the commands below, position your cell pointer to indicate rows/columns to lock. When you give the command, rows above your cell pointer and columns to the left of your cell pointer will be frozen or set aside as separate scrolling areas.
If you've chosen Freeze Panes your cell pointer will move out of the frozen pane area if you attempt to move beyond it.
SUM Formula, Quick
Hold down the [Alt] while you type the equal sign. Excel will automatically write a SUM formula for the cells above or to the left.
Taskbar display of Files/Worksheets
The option for controlling whether files/worksheets show up as individual items in the Windows Taskbar can be found by going to Tools | Options | View tab and checking "Windows in Taskbar".
Can I make text wrap to multiple lines in a cell rather than appearing as one long line of text? Sure, to set text wrapping, open a worksheet and click a cell you want to use for text. Now choose Format | Cells and click the Alignment tab. Under Text Control, select the Wrap Text check box, then click OK. Text entered into the selected cell automatically wraps to fit the cell. If you need to force a wrap at any given point, press Alt-Enter.
Transpose -- Rows to Columns, Columns to Rows
Want to transpose your columns to rows or rows to columns? The transpose feature in Office 97/2000 is in the Edit | Paste Special dialog box.
Can Excel be set up so that every new workbook created in Excel would start with certain formatting characteristics? It can be done. To create a workbook template for new workbooks:
Zooming Only Selected Cells
You can Zoom only selected cells rather than the whole sheet if you use the Selection option with the Zoom. This option allows you to select the cells you want to zoom in on (this option won't reduce a range) and chooses just the right percentage to see the selection as large as possible.
Simply select the range you want to enlarge and then either choose Selection from the Zoom drop down or choose View | Zoom | Fit Selection.
When you're done, select 100% (or the appropriate percentage) from the Zoom control in the Toolbar or choose Edit, Undo Zoom or press Ctrl-Z.
Warning: readfile(http://webdata.forestry.oregonstate.edu/helpdesk/problemcount.php): failed to open stream: HTTP request failed! HTTP/1.1 401 Unauthorized in /www/template/helpdesk/footer.php on line 69
disclaimer you may direct comments to the Forestry helpdesk