Inserting Automatic Page Breaks
You can use the subtotal feature to add a page break after each change in a sort key.Use View Manager to Print Summary and Detail Reports from Same Worksheet
To print both summary and detail views of the same worksheet you can use Excel's automatic subtotals and outlining features. With the worksheet shown in detail view, use view manager to save the print settings as a named view. Use the outline feature to collapse the detail and create another named view of the summary totals. You can include both views in a report using report manager, or select a view and choose print from the file menu.Learn how to Assign Names to Ranges
Excel makes the task of assigning names to ranges less obvious than some of the other functions on the worksheet. But, if you take 10 minutes to learn the process of assigning names to ranges, then you can simplify formulas, use named ranges as input to pivot tables and assign ranges to series in charts.Here's what you need to do - look three levels deep in the menu Insert/Name/Define. Get to know how the Define Names dialog box works - you can add new range names, delete and update the addresses of existing names. When changing a name, you need to press OK and exit the dialog box, then call up the dialog again to change the next range. The names box on the formula toolbar is useful to assigning a new name and moving to an existing range. It cannot be used to change or delete a name.
Take a Picture of a Cell
Use the camera toolbar button or the (SHIFT+Edit)/Paste Picture Link menu option to create a picture that is linked to the value in a worksheet cell. You can use a linked picture to show the values of cells from one worksheet on another worksheet.. If the cells contain an embedded chart, a picture of the chart will appear in the linked picture box. The linked picture can be moved or sized like any other drawing object - just drag one of its sizing handles with the mouse.Use AutoFormat
Turn your gridlines off (Tools/Options/View/Gridlines) and use Autoformat for a professional look. Find AutoFormat in the format menu. Select a cell in a block of data, choose this option and Excel will automatically format the block of cells in one of 16 formats - Excel detects total levels and column headings.Personal.XLS - Your Own Personal Worksheet
Personal.XLS is a special name- when you include a Personal.XLS workbook in Excel's XLSTART directory, every time you open Excel, the Personal.XLS workbook will open automatically. If you use Windows/Hide to hide the window that contains Personal.XLS, then it will open quietly - it will be there but you won't see it - unless you choose Windows/Unhide.So...what's the good of having Personal.XLS quietly lurking in the background? Well, the first benefit is a place to store frequently used macros. The macro recorder has an option to store recorded macros in Personal.XLS - or you can unhide it and copy or type macros directly to it.
If you have a list of information you need to update or access, Personal. XLS may be the place. You can create worksheets in Personal.XLS and set up a macro to use the built in Data/Form to search and update the data. Anytime you are working in Excel, press the shortcut keys to run a macro that will make your Personal.XLS information available.
Use the Built In Data Form to Find Rows in an Excel List
The built in data form (Data/Form) has a slick criteria lookup you can use to locate rows in a long list of data. To use the data form, select a cell in the list of data and choose Data/Form. Excel will automatically determine the number of columns and rows in your list - and will even identify column headings.To make sure that Excel understands the layout of your list, name the list "SheetName!DataBase" Where SheetName is Sheet1, or whatever name appears on the worksheets sheet tab. Database is a special name recognized by Excel - with a range named "Database", Excel will automatically expand the range to include new rows of data that you add using the Data/ Form. Another trick to help Excel recognize column headings is to format them as bold --- or with some characteristic different than the data in the list.
A Range With a Name is Better that two Without
Get rid of all those letters and numbers from cell references in your formulas. Replace them with references to named ranges. Then your formulas wil be easier to read and will travel better if you need to move or copy them between worksheets.Try setting up a three different named ranges in three different ways
- do it this way first: Select a cell, choose Insert/Name/Define and type in a name.
Now, try it this way: Enter a name for a range into a cell on the worksheet, select the name and the cell under it, use Insert/Name/Create and choose "labels at top" to create range.
For a third try...do this: Select a cell on the worksheet and type a name in the names box on the formula toolbar (the names box is where the address of the cell appears).
Check out the three names you have created with Insert/Names/Define - this is the dialog box you will need to use to delete names and change the address of a range - although the Insert/Names/Create option will also redefine an existing name.
Check out the View and Report Managers
Two features that often go unnoticed by the average Excel user are the View and Report Manager Add-Ins. Check these out - you may become a frequent user of them. The view manager allows you to define multiple print ranges and settings for a single worksheet. First, you get a page set up for viewing and printing exactly as you want, then you use the view manager to save and name that setting. The report manager can string together a list of views (and scenarios) -and print them in sequence.It won't take you long to learn how views and reports work. Start with Tools/Add-ins and make the View and Report Manager add-ins available for use. Then select View Manager and Report Manager options in the View or File Menus - follow the directions, use a little bit of on-line help and you'll have it down.
Fast Data Entry
Select a block of cells - then begin entering data, pressing ENTER between each entry. Excel will automatically move the cursor to the next cell in the selected block, filling the values down each column. Enter the data even faster with the numeric keypad and its ENTER key .You CAN sort data based on subtotal values
If you use the automatic subtotal feature of Excel...or the Outlining features - then you can hide the detail rows so only the totals are showing and use Data/ Sort to sort based on subtotal values. All of the detail records will follow along with the subtotal, so if you sort the subtotals and expand the outline, the detail lines will be there right above the sorted subtotal. Use this feature to print a report sorted on product sales by leading product...or budget variance detail by highest variance category.Start Using Cell Notes!
Notes can be attached to individual cells in Excel 5.0 and Excel 7.0(Excel95). So what's the big deal? In Excel 5.0, it was easy to create a note - use the button on the Auditing Toolbar --- but all that displayed was a red dot in the corner of the cell. You had to select a cell and click a button or menu item to see a note. So, it wasn't easy to move around the worksheet and see notes that were stored in many cells.Excel 7.0 changes all of that. When you pause the cursor over a cell, the cell note pops up - just like the names of the toolbar buttons. Excel developers can use this feature to add a new level of communication to the Worksheet.
Can't see your sheet tabs?
One alternative is new eyewear...another is to increase the size of the tabs. Here is the procedure for Win95: From the control panel, select Display icon and choose Appearance. Select the item scrollbar and increase the size parameter. Your Excel sheet tabs will be larger and easier to read. As the tab size increases, so does the size of the text. Your scroll bars will be wider, too - an advantage if you are using the Office95 pop up toolbar and you need to scroll up or down, but not let the cursor trigger the Office toolbar by moving to the edge of the screen.Where did this number come from?
How does this calculation work? Which cells go into this formula? What formulas does this cell affect? You can answer these types of questions using the Tools/ Auditing features of Excel to trace precedent and dependent cells - or you can use this shortcut:Press Ctrl+ [ to highlight a cells direct precedents. Use Ctr +Shift+ [ to highlight all precedents.
Use Ctrl+] and Ctrl+Shift+ ] to see dependents.
![[HRule Image]](../images/div.gif){kind=small}