Excel tips

Technology

Locate Phantom Links in Your Excel Workbooks

You may have encountered the infamous "phantom link" phenomenon. I've never known Excel to be wrong about identifying links, so there's an excellent chance that your workbook does contain one or more links -- but they are probably not formula links. Follow these steps to identify and eradicate any links in a workbook.Select Edit, Links. In many cases, this command may not be available. If it is available, the Links dialog box will tell you the name of the source file for the link. Click the Change Source button and change the link so it refers to the active file.
Technology

Navigating Numerous Sheets in Your Excel Workbook

Every Excel user knows that you can activate a different sheet in a workbook by clicking its sheet tab. Most users also know that you can press Ctrl-Page Up to activate the previous sheet, and Ctrl-Page Down to activate the next sheet.But if your workbook contains many sheets, and not all of the sheets' tabs fit on the screen, you may find it tedious to scroll through the tabs or use the keyboard to activate a distant sheet.

Rotating Text in Excel With an AutoShape

If you use Excel's AutoShapes, you've probably discovered a limitation: If you add text to an AutoShape, the text is not rotated when the AutoShape is rotated.Here's one way to circumvent that problem:Create your AutoShape, add text, and format it to your liking. Ctrl+Click the AutoShape to select it. Pressing Ctrl ensures that you select the AutoShape itself, not the text inside. Press Shift, and choose Edit - Copy Picture. This command is available only when the Shift key is pressed.

Use Fill-in-the-Blank Form to Enter Excel Spreadsheet Data

If you have already created a spreadsheet containing a list of information, and you need to make additional entries to the list, here's a quick and easy way to add to the information already entered on your spreadsheet.Before using this tool, make sure that the first row of your data contains header information, and that there is at least one row of data beneath the header row. There can be no blank rows in your data list.Position your mouse pointer in any occupied cell that is within the list area. Choose Data, Form from the Excel menu.

Alternate Row Shading Using Excel Conditional Formatting

One way to make your data legible is to apply cell shading to every other row in a range. Excel's Conditional Formatting feature makes this a simple task.Select the range that you want to format Choose Format, Conditional Formatting In the Conditional Formatting dialog box, select Formula Is from the drop-down list, and enter this formula: =MOD(ROW(),2)=0. Click the Format button, select the Patterns tab, and specify a color for the shaded rows. Click OK twice to return to your worksheet. The best part is that the row shading is dynamic.

Copy Formatting From One Excel Worksheet to Another

Have you ever taken the trouble to format a worksheet - adding bold to your titles, centering column headings, changing the number format to currency in some rows and comma format in others, changing the width of columns - and then wished you could just snap your fingers and take all that formatting over to another worksheet? The process is actually performed almost as quickly as snapping your fingers.

Create a Quick Loan Calculator in Excel

You can use Excel to help figure out what your loan payment would be should you decide to borrow money. Here's a quick way to set up a loan payment calculator in your Excel worksheet:Step 1: Enter the following titles in six consecutive rows:Cell A1 PriceCell A2 DownpaymentCell A3 PrincipalCell A4 InterestCell A5 YearsCell A6 PaymentStep 2: These titles will become the cell names for the cell to the right of each cell containing a title. Highlight the cells containing titles and the cell to the right of each of those cells (12 cells altogether).

Protect Cells in Excel Using Data Validation

Here's a creative way to protect cells in an Excel worksheet so that other users can't make changes to them: Select the cells you want to protect. (It's a good idea to make a note of the cells you protect in case you need to remove that protection later.) On the Data menu, click Validation, and then click the Settings tab. Set the following restrictions: In the Allow box, click Text Length; in the Data box, click between; in the Minimum box, type 10000; and in the Maximum box, type 50000. Click the Error Alert tab.

Shortcuts for Copying Cells in Your Excel Workbook

It's easy to copy the contents of a cell or a range of cells to another location on the same page or a different page of your Excel workbook. It's also easy to copy cell contents to another workbook. Use these quick-copy techniques to make sure your cell information gets to the right location.Copy using Toolbar Buttons. Select the cell or range of cells that you wish to copy by clicking on the individual cell or dragging over the range of cells to highlight them. Click the Copy button on the toolbar. Click in the cell to which you wish to copy.

Give a Consistent & Professional Look to Your Excel Workbooks

If you are a stickler for consistency and want to maintain a consistent and professional look in the documents your firm sends out, then you can use this simple macro that automatically formats your company workbooks with certain essentials. This tip below will help you create a macro you can use to insert a custom footer or header into your all of your firm's documents.To create the macro do the following: Open a new Excel workbook. From the Tools menu, point to Macro, and then click Record New Macro.
Technology

Alternate Row Shading Using Excel's Conditional Formatting

There is an easy way to make your data stand out for others to review. Excel's Conditional Formatting feature makes this a simple task.Select the range that you want to format.Choose Format, Conditional Formatting.In the Conditional Formatting dialog box, select Formula Is from the drop-down list, and enter this formula: =MOD(ROW(),2)=0. Click the Format button, select the Patterns tab, and specify a color for the shaded rows. Click OK to return to your worksheet. The best part is that the row shading is dynamic.

Accrual-to-Cash Excel Spreadsheet Resource

A question in the

Shortcuts for Printing Excel Worksheets

There are several ways to transfer your Excel worksheet to paper.

Insert Hyperlink Into Your Excel Worksheet

Do you need to provide quick access from an Excel worksheet to a network file or to a location on the Internet? Perhaps the instructions for filling out the worksheet reside in a network directory, or maybe you need to show the Excel user where timely information can be found on the Internet.You can provide a hyperlink right in your Excel worksheet that will connect the user with links to another file on your computer, a network file, or an Internet site.

Save Customized Excel Chart Styles

If you create charts in Excel, chances are good that you adjust the formatting of the standard chart style each time you create a new chart. You may change the size of the font, perhaps you add some background color, you might include your company logo on the chart, you may prefer the currency format for your numbers, or you may prefer a line graph to the default column style.

Tip: Using Excel's SUMPRODUCT Function

Excel provides a useful function called SUMPRODUCT which can be used to find summation of multi dimension data series. For example: You have number of cartons in cells A1:A5Number of units in each carton in cells B1:B5Price of each unit in cells C1:C5 You want to find the total cost of the stock. The answer is simple. You use the following formula: =SUMPRODUCT(A1:A5,B1:B5,C1:C5) You don't need to multiply A1*B1*C1 and then copy it down. This function seems to be quite handy and can be used for many other calculations.

Excel Tip: Wrap Text Within a Cell

Sometimes your text is too long for the width of a cell in your Excel spreadsheet. If the cell to the right of the cell containing text is empty, the lengthy text will spill over into the empty cell. But what happens when the cell on the right is occupied? Only a portion of the text appears.You can change the size of the font and you can change the width of the cell. Both of these techniques help to make text fit within a single cell. But what if you want to leave the font size and the cell size intact?

Excel Tip: Find Random Integer Within a Selected Range

Excel's random number generating function, RAND, normally produces a random number that is a five- or six-digit decimal greater than or equal to 0 and less than 1.

Excel Tip: Sort Excel Data List by Four Criteria

The Excel sort feature allows you to sort a list by three criteria. But what if you want to sort your list using four features? Here's a tip that will show you how to accomplish this task.For example, say you want to sort a data list that contains first and last names of employees, office locations, job descriptions, departments, month in which they were hired, and length of service.

Excel Tip: Change Formula to Value in a Jiffy

Here's a really quick tip for working with calculations in spreadsheets. Use this procedure to change any formula or contiguous group of formulas to values.Select a cell or a group of cells containing the formulas you wish to convert. The cells you select must be adjacent to one another.

Pages