May 21, 2004

Take What You Need

Copying and Pasting. These two features sure save a lot of time and can be found in all kinds of software (Shortcut keys are invariably Ctrl + C to copy and Ctrl + V to paste, use them for text, images and files). But there are special variants of Pasting that can make working with Excel even faster and easier.

The Paste Special Dialog box
The easiest way to open this dialog box is to right click where you want to paste (you can't use it unless there is something already copied to the clipboard). Alternatively you could try Alt, E and S if you prefer shortcut keys.

Apart from the All option which pastes everything, the other useful options will probably be Formulas, Values, Formats and Comments. Use them accordingly, they do what they say. (Note that for formulas used in Conditional Formatting, the Formats option should be used instead of the Functions option)

Paste Special Buttons
Another way to paste just what you want to is to use the Paste Formats and Paste Values buttons. Open the Customize dialog box in the Tools Menu and select Edit from the Commands tab. Scroll down till you find the buttons and drag them across to a toolbar of your choice.

Hmm, but what about Paste Formulas and Past Comments? For some reason these buttons are not available, but no problem! You can download my macros called PasteFormulas and PasteComments and import them via your Visual Basic Editor. (I recommend putting them in your Personal Workbook)

Open up your Visual Basic Editor (Alt + F11) and go the File menu and then select Import File. (Make sure you click where you want to import them first. For example, pick the Modules folder in your Personal Workbook if you want to import there)

Now they are ready to use but you still need a way to activate them easily. Go back to the Customize dialog box in the Tools menu and scroll down on the Commands tab until you find Macros and then drag a Custom Button to a toolbar. (For editing tips see my Customizing Toolbars tip dated May 2) Right click the button and choose Assign Macro to open the selection of the Macros available in the dialog box.

There you have it. Note that the PasteFormulas and PasteComments work the same way as the regular Paste Formats and Paste Values buttons, you can use them to paste indefinitely while something is copied on the Clipboard.

Here's a picture of my own customized PasteFunctions Custom button.

Hope they come in handy :)

May 19, 2004

Getting Set Up, Part 2

To the Header/Footer tab. (if you don't know what I'm talking about, please read my previous post)

You can make your own custom headers and footers, or you can choose from one of the preset headers and footers available in the drop down lists.

If you choose to make a custom header or footer, there are many things options available to use with your text...change the style, add page numbers, add the file path, file name or tab name, add the date or time (very useful for tracking!), or even add a picture (I think this last feature is not available in some of the earlier versions of Excel).

The Page Break Preview
Last time I talked about sizing your work to suit by using the Scaling options on the Page tab of the Page Setup dialog box. This is another way of changing the size of your work, which is by adjusting the page breaks to wherever is convenient for you.

First, select Print Review. In can be found on the right side of each tab in the Page Setup dialog box, or you can also access it by pushing the button on the Standard toolbar that looks like a magnifying glass (usually found at the top). Once in Print Preview mode, push the Page Break Review button and you should get something like this, assuming you've already selected your print area.

Page breaks have a default setting of range A1:I55. By the dragging the blue lines one the edges, you can either shrink a page...

or change the boundaries shared with other pages. Bear in mind your work will also shrink or enlarge depending how your you are resizing your sheets.

Once finished, return to the Print Review and push Normal View. The job is done.

May 18, 2004

Getting Set Up, Part 1

Nothing spoils the appearance of a spreadsheet more than a sloppy printing job. Here's how you should set yourself up... and not get arrested.

The Page Setup dialog box can be found in the File menu. If you use it often enough, you might well consider moving a button to a suitable toolbar by using the Customize dialog box in the Tools menu.

The Page Tab
Select your orientation, Portrait for printing vertically and Landscape for printing horizontally. Then think about the size of what you want to print. If it is not too large, you might get away with just using one sheet of paper. If this means that the text will be too small to read or what you print will only cover half the page, then it might be a good idea to use more than one page. In either case, use the Scaling features of the Page tab to select what best suits the job on hand.

The Margins Tab
Adjust the margins to suit and don't forget you can center your work by selecting the two checkboxes marked appropriately Horizontally and Vertically at the bottom of the dialog box.

The Sheet Tab
Yes, I know I skipped the Header/Footer tab, I'll cover that next time! This is more important, trust me! If you haven't already set up your print area, now is the time by the little red arrow in the Print area box. Watch it shrink, then drag over the area you actually want to print.

You'll also notice the selections 'Rows to repeat at top' and 'Columns to repeat at left'. These are very handy if you want to use the same titles for more than one sheet. For example, if you push the little red arrow on the 'Rows to repeat at top' and drag to select the row or rows your headers are in, they will automatically appear at the top of every page, 'just like what it says on the label'.

This page is powered by Blogger. Isn't yours?