Login | Create Account  
 
Support
Support Main
Help Desk
FAQ By Product
Numbering Assistant
Forms Assistant
Metadata Assistant
Bates Label Assistant
Tips by Product
Excel
Outlook
Word
PowerPoint

Tips for Microsoft Excel

Excel 2007: Quick Math

Excel has a built-in functionity to serve as a quick calculator - for more than just quick sums. Select cells that contain numbers only. For non-contiguous cells, press the Ctrl key and click to select. Look at the Status bar at the bottom of the window and you will see calculations. Alternate-click to change the type of calculation performed, alternate-click on the status bar and change the calculation to average, count, numerical count, minimum, maximum or sum. This was available in previous versions but not nearly as intuitive.

Excel 2007: Extend Selection and Add To

If you are working with someone who has limited mouse proficiency, or if you have a large worksheet where you need to select non-contiguous cells, you can use Excel's little known Extend and Add feature. First, click in any cell that should be part of the selection. Press F8, then select the last cell in the range to be part of the selection. Press Shift+F8 and click other cells to add individual cells to the selection. Press Esc to leave Extend and Add To mode. This was also available in previous versions of Excel.

Excel 2007: What does the # (number sign) mean when displayed in cells?

Most often, the reason for the # symbol displaying in a cell instead of the resulting value is that the column width is not large enough to display the contents. Resize the column and it should fix the problem. There is one other reason why the # symbol would show instead of numbers. If you have a formula that subtracts one time value from another and the result is a negative number, the number symbols will appear.

Excel 2007: A message appears when attempting to add rows or columns

Excel 2007 gives you a lot more real estate to work with. Typically you can insert rows and columns at will with one exception. If y ou are in the last available column (XFD) or row (1,048,576) you get a message box warning.

Excel 2007: More than 255 worksheets

You can have up to 255 worksheets in a new workbook. However, once the workbook is created, you can add more worksheets up to what the amount of your computer's memory can handle.

Excel 2007: That sheet is "History"

Can you insert a worksheet and name it history? You cannot, because this is a reserved name for a track changes or history feature in Excel. If you attempt to copy or add a worksheet with an existing sheet name, Excel will put a number next to the worksheet name.

Excel 2007: Turn of the gridline display"

On the View tab, in the Show/Hide group, uncheck the option for Gridlines. This affects the active worksheet only.

Excel 2007: Entering Fractions

If you type a fraction into a cell that has not been preformatted for fractions, you will get a date value instead of what you typed. For instance, type 1/2 in cell A1, press Enter, and Excel translates this into January 2. To return a fraction value, type 0 1/2 and press Enter. This is the same for previous versions of Excel.

Excel 2007: Merge Styles

Excel 2007 includes more style centric formatting. If you have styles in one workbook and you want to add them to another, you can use the Merge Styles command. First, open both workbooks and then move to the Destination workbook where the styles should be copied to. On the Home tab, in the Styles group, click Cell Styles, then Merge Styles. The Merge Styles dialog box displays where you can select the workbook from which to copy styles and click OK.

Excel 2007: What happened to the Function Wizard?

It's actually still there, it's now called the Insert Function dialog box now.

Excel 2007: Generate Random Numbers Between Two Stated Values

There are websites that will generate random numbers for you; however, you can use an Excel function for the same purpose. Click in any cell and type =RANDBETWEEN(1,50) and press Enter to generate a random number between these two values. Use AutoFill to add random numbers to other cells on the worksheet.

Quickly Clear All Spreadsheet Formatting

Press Ctrl+A to select all cells in the worksheet. From the Edit menu, choose Clear, Formats.

Insert a Return into an Excel Cell

To insert a hard return in a cell press ALT+Enter.

Shortcut to Insert Time/Date in Excel or Access

Use the following shortcut keys to insert the current time and date in a Microsoft Access table or Excel worksheet.

Note: When you insert the date and time using this tip, the information remains static. To update this information automatically, you must use the TODAY and NOW functions.

How can I center worksheet data on the printed page?

From the File menu choose Page Setup and click the Margins tab. To center the information left-to-right between the margins of the page, select Center on Page, Horizontally. To center the information top-to-bottom between the margins of the page, select Center on Page, Vertically. Click OK when finished.

Use opposite toolbar buttons to make room for more customized buttons

One of the more undocumented features in Excel is hidden toolbar opposites. Although not every button on the Standard and Formatting toolbar has an opposite, many do. How does it work? When you press the SHIFT key and click on a button that has an opposite, the image on the button changes. Release the Shift key and the original button image displays. The following is a list of the opposites for buttons on the Standard and Formatting toolbars:

Use 'Tab Leaders' in Excel

To fill in any remaining space that might exist in a cell (after text or numbers), try the following: Expand cell A1 to make it much wider and type your name in the cell. Press Enter to insert the text. Click back in cell A1 and from the Format menu, choose Cells. Select the Number tab and the Custom Category. Type @*. (period) or any symbol that you wish to repeat in the cell width. Click OK.

Print Row and Column Headers

You can use a setting in the Page Setup dialog box to enable the printing of column and row headers. From the File menu, choose Page Setup. Select the Sheet tab. Under Print, check the option next to Row and column headings and click OK.

Hide Contents of Cell

You can format cells so that the values contained within are not visible. Click the cell you wish to hide. From the Format menu, choose Cells. Under Category select Custom. In the Type box, type three semicolons (;;;) and click OK.

Note: While this trick hides the value in the cell itself, the Formula bar still displays the formula or contents of the cell.

Worksheet prints an extra, blank page. What is causing this?

When information is deleted from cells, Excel does not automatically delete the reference to the cells. To find out the last cell used in the spreadsheet and reset it to the correct area, try the following: From the Edit menu, choose Go To. Click the Special button. Select Last Cell and click OK. If the last cell is not accurate (beyond where it should be), select and delete the empty cells before the actual end of your data and the last cell, save and close the workbook, and reopen it. The last cell will reset to the correct location and the worksheet will print correctly.

When I type a number in a cell, Excel automatically adds a decimal point. For example, I type 196 and get 1.96

The option Fixed Decimal Places setting is probably enabled. From the Tools menu, choose Options and select the Edit tab. Clear the checkbox next to Fixed Decimal, and click OK.

I want to print my worksheet but hide all of the values that are under $500. Is this possible?

Yes. You can do this by applying conditional formatting. Select the entire worksheet (press Ctrl+A). From the Format menu, choose Conditional Formatting. Under Condition 1, set the following: Cell Value Is - Less Than - 500. Click the Format button. Click the drop-down arrow under Color and select White. Click OK twice to close the dialog boxes. All numerical values under 500 are formatted as white and will not show when printing unless your background is set to a darker color. In the event that you have color applied the worksheet, set the font color for the conditional the same as the background color.

Is it possible to color code sheet tabs?

Yes. Follow the steps below:

  1. Alternate-click the tab you wish to color code and choose Tab Color from the shortcut menu.
  2. Select the color you wish to apply and click OK.

Paste Anything as Picture

  1. Select a range of cells.
  2. From the Edit menu, choose Copy (or press Ctrl+C).
  3. Hold the Shift key and click the Edit menu.
  4. While holding Shift, choose Paste Picture.

Concatenate Cells (Combine Cell Data)

To join two or more cells, follow these steps:

  1. In cell A1, type your first name.
  2. Type your last name in cell A2.
  3. Click in Cell A3 and type =A1&" "&A2. Press Enter.

I have a template I have used for 3 years and it has bloomed to over 50MB. I'm not sure why it has gotten so big. Is there anything I can do to reduce the file size?

For each worksheet in the file, manually go to the bottom of the last used row and select the entire row. Hold down Ctrl+Shift and push the Down Arrow. This should select all unused rows. Then choose Edit>Clear>All. Repeat the same steps for columns (manually select the column after the last used one. Hold down CTRL+Shift and press the Right Arrow. Then choose Edit>Clear>All.)

Once you complete these steps on all worksheets, save the spreadsheet and you should notice a significant difference in the file size.


 
© PayneGroup, 2009, All Rights Reserved. Telephone: 206-344-8966 or 1-888-GoPayne| Site Credits