Tip # 44 Open Web pages right in Excel

If your users need to analyze data found on a Web site, they might be in for a bunch of tedious retyping. Under the right circumstances, however, Excel 97 can save them a lot of time by letting them open an HTML page in Excel directly from the Web.
The procedure is just a slight modification of the standard file-opening procedure:
1. Choose Open from the File menu.
2. In the File Name text box, type the URL of the Web file to open. Note: Be sure to include the prefix http://.
3. From the Files Of Type drop-down list, select HTML Documents (*.html, *.htm).
4. Click Open.
Excel opens HTML pages based on the HTML table structure of the page, so some Web design treatments may lead to some weird results in Excel. For pages that cleanly present data in a table format, however, this approach can be a real time-saver.

Tip # 43 Easy data-posting to the Web

If your organization is using an intranet to share public data—and whose organization isn't these days—your users will want an easy way to post this information. Excel 97 offers an easy, wizard-driven system to walk even the most apprehensive users through this process:
1. Select the cell range that contains the data to be published.
2. Select Save As HTML from the File menu.
3. Answer the questions posed by the Internet Assistant Wizard.
The wizard walks users through four steps that define how information will be converted for Web display. Various options include:
• Inserting the resulting HTML tables into an existing HTML page or creating an entirely new page.
• Specifying formatting options, such as rules between data.
• Saving the new HTML page as a freestanding file or inserting it directly into a FrontPage Web.

Tip # 42 See the big picture

If you’re working on a large sheet you might want to switch to Full Screen mode: simply click on View, Full Screen. Click on it again to return to a normal window.

Tip # 41 Find the currently active cell

If you’ve been scrolling around your spreadsheet and you lose your place, you can jump back to the currently active cell by pressing the [Ctrl] + [Backspace] keys.

Tip # 40 Enter a fixed time into Excel

If you want Excel to enter the current date or time and fix it at that point – for example, to show the last date the sheet was modified – click on a cell and press [Ctrl] + [;] for the date and [Ctrl] + [:] for the time.

Tip # 39 A quick way to enter the time

To enter the current date or time click on a cell and type =today() or =now().Excel updates the result every time you open the sheet, so it’s always current.

Tip # 38 Use the AutoCalculator

If you need to calculate a sum based on a row or a column of figures and you can’t be bothered typing in a function, just select your figures and glance down at the status bar – you’ll find the sum of the selected cells there.

What’s more, if you right-click on the sum a pop-up menu will appear offering additional quick calculation functions.

Tip # 37 Speed up calculation time

How, when, and what Excel calculates is a huge subject. In general, cell references and calculation operations are the main performance vampires. Reasonable formulas and even lots of data don't usually slow things down. Complex formulas and repetitive references are the real culprits. Here are a few basic guidelines that should help you avoid calculation bottlenecks:

• Avoid complex and array formulas. Use more rows and columns to store intermediate values and use fewer complex calculations.
• Reduce the number of references in each formula to the bare minimum. Copied formulas are notorious for repeating references and calculations. Move repeated calculations to a cell and reference that cell in the original formula. (See Tip #1 for an alternate suggestion.)
• Always use the most efficient function possible: Sort data before performing lookups; minimize the number of cells in SUM and SUMIF; replace a slow array with a user-defined function, and so on.
• Avoid volatile functions if possible. Excel recalculates these functions with each recalculation, even if nothing has changed. Too many volatile functions (RAND(), NOW(), TODAY(), and so on) can slow things down.

Tip # 36 Identify printed sheets

Printing a sheet is a common task. Some users find it useful to print the name of the workbook in the header or footer. In Excel 2003, you can accomplish this by choosing Page Setup from the File menu and clicking the Header/Footer tab. Then, choose the appropriate item from the Header control's drop-down list. Versions prior to 2003 can use the following VBA procedure to print the full file's pathname:

Sub FormatHeader() 
 With ThisWorkbook   
  ThisWorkbook.Worksheets(sheetname)PageSetup.LeftHeader = .FullName 
 End With 
End Sub

where sheetname is the sheet's name as a string value. To make the procedure more dynamic, use ActiveSheet.Name instead. That way you can run it against any sheet in the workbook.

Tip # 35 View formulas, or not, quickly

You probably know that you can view all the formulas in a sheet by choosing Options from the Tools menu and selecting Formulas on the View tab. Doing so displays formulas instead of their evaluated results. But there's a quicker way. Press Ctrl+~ (the tilde character to the left of the number 1 on your keyboard). The keyboard combination toggles between formulas and normal view. When you're finished viewing the formulas, simply press Ctrl+~ again to return to normal view.

Tip # 34 Hide everything but the working area

You usually hide a column or row to conceal or protect data and formulas. You can also hide unused regions of a sheet to keep users from exploiting unused areas or to help keep them on task by not allowing them to wander. By hiding unused rows and columns, you present a sheet that focuses on just the work area.

To hide unused rows, select the row beneath the sheet's last row. (Select the row header to select the entire row.) Next, press Ctrl+Shift+Down Arrow to select every row between the selected row and the bottom of the sheet. Then, choose Row from the Format menu and select Hide. Repeat this process to hide unused columns, only select the column header in the first empty column. Press Ctrl+Shift+Right Arrow and then choose Column from the Format menu instead of Row.

Before you hide anything, make sure you don't inadvertently hide an obscure area by pressing Ctrl+End to find the last cell in the sheet's used range. Unhide the rows and columns by selecting the entire sheet. Then, select Row or Column from the Format menu, and choose Unhide.

Tip # 33 Customize movement

By default, the cell pointer moves down when you press Enter. Selecting the cell immediately below the current one won't always be what you need. For instance, some people enter data from column to column. You could press the Right Arrow key instead of Enter, but out of habit, most of us reach for Enter. Even if you can retrain yourself (or users) to use the arrow keys, they're far enough away from the main keys to slow down data entry.

Fortunately, you can change the cell pointer's default direction. Chose Options from the Tools menu and then click the Edit tab. Select the Move Selection After Enter check box (if necessary) and then choose a direction from the option's drop-down list. For instance, to move from column to column, you might choose Right instead of Down.

While entering data, you can temporarily force the cell pointer to move in the opposite direction by holding down the Shift key while you press Enter.

Tip # 32 Create custom lists

Most of us work with sets of data that seem to repeat themselves throughout our projects. That means we can enter the same values in numerous spots. If you frequently enter the same dataset, consider creating a custom list. To do so, choose Options from the Tools menu and then click the Custom Lists tab. In the List Entries control, enter each item in the list, one entry per line, in the order in which you want it to appear. When you've completed the list, click Add. Excel will copy the list to the Custom Lists control. Click OK to close the Options dialog. To enter the list, select a cell and enter any name in the list. Then use the fill handle to complete the list.

If you want a partial list, enter the item you want to begin with and then pull down the fill handle. Excel will fill in the remaining names.

If the list already exists in the sheet, you don't have to retype it to create a custom list. Simply select the list before choosing Options from the Tools menu. Then, click Import on the Custom Lists tab.

Tip # 31 Enter repetitive data quickly

Tip # 29 show you how to copy existing data into noncontiguous cells. You can also use this technique to enter data into a series of noncontiguous cells. Hold down the Ctrl key and click all the cells into which you want to enter data. Then, type the text you want to enter and press Ctrl+Enter. Excel will enter the typed text into all of the cells in the noncontiguous selection.

Tip # 30 Customize defaults

Excel uses template files to control default settings in new workbooks and sheets. For most of us, the settings are adequate. However, if you find yourself resetting the same defaults for each new workbook or sheet, consider changing the defaults permanently.

To change default settings for a workbook, open Book.xlt, make changes, and then save the file. Don't change the file's name; you're just updating it. (It's a good idea to create a copy of the original Book.xlt so you can revert to Excel's original settings if necessary. Name the copy BookOriginalSettings.xlt or something similarly descriptive.) After changing Book.xlt, all new workbooks will use the custom settings you applied. To change a sheet's default settings, open Sheet.xlt, make the necessary changes, and save it.

If you don't have one or both files, simply create your own. Just be sure to save them in Excel's XLStart folder (\Program Files\Microsoft Office\XLStart).

Tip # 29 Quick copy to noncontiguous cells

Copying data or a formula is simple. You just drag the source cell's fill handle and Excel copies the data or formula from the source cell to the cells you select using the fill handle. But copying isn't always a nice, neat, contiguous package. Sometimes you need to copy data or a formula to a series of noncontiguous cells. You could paste the source data into each individual cell, but that's the hard way. Instead, you can copy data into a noncontiguous block.

First, copy the source data. Then, hold down the Ctrl key while you click each cell in the noncontiguous destination range. Once you've highlighted each target cell, press Ctrl+V, and Excel will copy the source data into each of the highlighted cells. Formulas copied this way obey referencing rules, in regard to absolute and relative addresses.

An alternate method is to right-click in the cell that contains data you want to copy and choose Copy from the resulting submenu. Then, right-click a destination cell and choose Paste. At this point, the source cell is still highlighted, which means you can copy the contents again. Right-click another destination cell and choose Paste. Continuing selecting destination cells until you've completed the copy task. Press Esc to clear the selection of the source cell.

Tip # 28 Exploit defined names

Defined names aren't just for ranges. You can use a defined name to define a constant value, such as a discount amount. Use the feature as you normally would, entering the literal value or expression that evaluates to the desired value into the named cell. For instance, select a cell and choose Name from the Insert menu. Then, select Define. Enter the descriptive name Discount and click OK. Now, in the same cell, enter the actual discount amount, say 3 percent (just enter .03). Now, you can use the defined name, Discount, in your formulas instead of entering the literal value .03. For instance, Excel would use .03 for Discount when evaluating the following formula:

=TotalPrice - (TotalPrice * Discount)

This quick tip has two benefits: It makes updating much simpler, as you can quickly change the value in Discount and Excel will automatically update all dependent formulas. And it eliminates data entry errors.

Tip # 27 Customize your autofills.

If you use the same list over and over in different worksheets, you might want to add it to your AutoFill list – this will save you heaps of time in future. Highlight your list, click on Tools, Options… and select the Custom Lists tab. Click on Import, then OK.

Tip # 26 Use the formula browser

Select a cell and click on the Paste Function button on the main tool bar.Pick the function you require from the list box and click on OK. Now highlight the cells on which you want the target to perform the function and click on OK.
The Paste Function button enables you to browse formulas and is the easiest way to enter Functions.

Tip #25 Access help for Lotus users

If you’ve converted from Lotus 1-2-3 and find Excel confusing, you can access help specific to your situation by clicking on Help, Lotus 1-2-3 Help…

Tip # 24 Use template worksheets

Templates can save you considerable time when you’re setting up a new worksheet. Click on File, New…, select the Spreadsheet solutions tab and choose a template from the list.

Tip # 23 Hide your data from prying eyes

If you want to hide from view any sensitive data, highlight the relevant cell and click on Format, Cells… Click on the Numbers tab, select Custom from the Category: list, double-click on the Type: input box and enter ;;;. Undo the operation to make your data visible again.

Tip # 22 Fit wide tables to the page width

To make your tables fit neatly on the page, click on File, Page Setup…, select the Page tab, click on the Fit to: radio button and pick 1 page wide. Click on the tall box and press [Delete], leaving the box empty.

Tip # 21 Remove hyperlinks from your work

If Excel has already converted your written URL into a hyperlink, you can cancel it by right-clicking on the offending address and selecting Hyperlink, Remove Hyperlink from the menu that pops up.

OR:

Type the url, press Enter key, and then Ctrl + Z. The text will remain but without a hyperlink.

OR:

When Excel automatically creates the hyperlink, click the small star and select 'Stop automatically creating hyperlinks'

Tip # 20 Calculate running totals

Enter the numbers to be added in column A, say A1 to A5, then enter =SUM($A$1:A1) into column B. Highlight the cells beside the ones with numbers in (in our example, B1 to B5) and go to Edit, Fill, Down. This places the running total of the figures in A1 to A5 in the adjacent column.

Filling Down – the easy way to perform
dynamic calculations.

Tip # 19 Enter URLs as text , not hyperlinks

To prevent Excel from converting written Internet addresses into hyperlinks, add an apostrophe to the beginning of the address, for example ‘www.excel-macros.blogspot.com.

Tip # 18 Calculate time between dates

Enter in a cell the formula =A2-A1,where A1 is the earlier date, and A2 the later one. Don’t forget to convert the target cell to number format – do this by highlighting the cell, clicking on Format, Cells…, picking on the Number tab and selecting Number from the Category: list.

Tip # 17 Convert rows to columns & viceversa

You can convert rows to columns (and columns to rows) by highlighting the cells you want to switch around, clicking on Edit, Copy, selecting a new cell and then going to Edit, Paste Special… Finally, place a tick in the Transpose box on the dialog box and click on OK.

Tip # 16 Double-click your way to fast formatting

By default, when you copy text from a different program (such as Microsoft Word, Microsoft PowerPoint®, or even Microsoft Internet Explorer) and paste it into an Excel cell, the original formatting comes along for the ride. Sometimes this leaves you with a tiny little cell with GIGANTIC FORMATTING that you need to fix by hand.

The Paste Options button that looks like this:

pops up, and you can use it to keep the original formatting or to match the destination formatting (that is, the formatting you've chosen for your cell). However, if you're like me (or want to be like me — aren't you sweet), that is just too many steps if you know that you want to match the formatting of the cell. This is especially true if you have a lot of cutting and pasting to do.

To paste text from another program and keep your cell's formatting

Select the text you want to pop into your Excel worksheet.

Press CTRL+C.

Switch back to Excel.

Double-click in the cell, and press CTRL+V.

It's like magic in a click (or two).

Tip # 15 A change of scenery: Move or copy a worksheet

You may know that you can just drag and drop a worksheet to a different spot in the workbook. But for you folks who get joy from dialog boxes (and truthfully, this one gives you more options), here you go:

To move or copy a worksheet

Right-click a tab, and then click Move or Copy on the shortcut menu.

In the Move or Copy dialog box, you have options: To move the worksheet to a spot within its own workbook, select a sheet in the Before sheet box.

To move the worksheet to another open workbook, select the workbook in the To book list and then click OK.

To keep a copy of the worksheet you're moving in this workbook (rather than just moving it), select the Create a copy box.

Tip # 14 To color all sheets in the workbook

To color all sheets in the workbook

Right-click any worksheet tab, and then click Select All Sheets on the shortcut menu.

Right-click the tab again, and then click Tab Color on the shortcut menu.

Again, go wild.

To rename a worksheet

What would Westerns and action films be like today if Marion Michael Morrison hadn't changed his name?

Right-click a tab, and then click Rename on the shortcut menu.

Tip # 13 Make a style statement with worksheet tabs

If you have a workbook with loads of worksheets and you're having trouble remembering which one is which, you can customize the little tab at the bottom of each worksheet to identify it in a special way, with a new name or color. It's like getting to design your own nametag instead of having to wear the standard, white "Hi My Name is....." tag.

Note You must be using Microsoft Office Excel 2003 or Excel 2002 to color a worksheet tab.

To color one sheet

Right-click the tab you want to color, and then click Tab Color on the shortcut menu.

Go wild.

Tip # 12 Stretch out: Insert a line break in a cell

In most other Office programs, to move the cursor to the next line, you press the ENTER key. However, in Excel — the program that dances to its own tune, and doesn't give a whit about lines or paragraphs — you end up in the cell below when you press ENTER. This is the cause of much frustration in my readers, so I thought I'd let you in on the not-so-secret secret.

To insert a line break in a cell

Press ALT+ENTER to start a new line while you're typing or editing data.

Ahhh...now you have room to breathe in that tiny cell.