# Excel tips

## 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.

## Excel Tip: Return to the Exact Place Where You Left Off

You've been working in Excel all afternoon, there are several worksheets open, your screen is arranged just the way you need it with all the information you are using visible, but it's time to go home for the day. Will you have to recreate this scene tomorrow morning before you can get back to work at the place where you left off? Not at all!Normally, when you are ready to close Excel, you probably choose File, Save or File, Save As, so that you can save your workbook file.

## More Excel Tips with Gail Perry

More Excel Tips!Presented by: Gail Perry, CPA,Managing Editor of AccountingWEB and author of Excel 2000 Answers!January 12, 2001Visit the AccountingWEB Workshop Calendar for upcoming workshop sessions.SummaryHere are some of the quick tips discussed in Gail's Excel workshop on January 12, 2001.

## Excel Tip: Combine Two Columns of First and Last Names

If you have a spreadsheet that contains a column of first names and a column of last names, and you would like to combine those names into one column, here's a quick trick that will get the job done.For this example, let's assume that Column A contains first names and Column B contains last names.

## More Excel Tips

In a recent workshop at AccountingWEB, author and AccountingWEB Managing Editor Gail Perry shared some of her favorite Excel tips.Workshop sponsored by National Payment CorporationHere are some more of the tips that were discussed in January's workshop event:Placement of To

## Excel Tip: Perform Fast Calculations in Excel

If you want to find the largest value in a series of cells, you can create a formula to do that (=MAX(cellrange)), but there is a an easier way to get the answer. To view the largest value in a range of cells: Select the cells to be included in the range, and you will see the sum of the range displayed on the status bar, which is the horizontal area below the worksheet window.If the status bar is not displayed, click Status Bar on the View menu. Right-click the status bar, and then click Max.
Technology

## More Excel Tips with Gail Perry

More Excel Tips!Presented by: Gail Perry, CPA,Managing Editor of AccountingWEB and author of Excel 2000 Answers!January 11, 2001Visit the AccountingWEB Workshop Calendar for upcoming workshop sessions.SummaryHere are some of the tips discussed in Gail's Excel workshop on January 12, 2001. Look for more of these tips in future stories on AccountingWEB.

## Excel Tip: Have Excel Save Your Files Automatically!

Have you ever wanted to automatically save your spreadsheets so you don't lose your work? With Excel 2000 this feature is included and will save all workbooks automatically at specified intervals, but it's not installed by default.

## Excel Tip: Great Shortcut for Data Entry

Here's a shortcut that will save time when you have a lot of data to enter in a block of columns and rows in your spreadsheet.If you are entering data in a block of cells, you can enter information across the first row of the block by pressing Tab to move from one cell to the next.When you are ready to enter information in the next row of cells, press Enter and you will be taken to the leftmost column of the block.For example, say you need to enter data in the cells B3 through Q12.
Technology

## Excel Tip: Quick Word and Excel Tips

Create an Excel Chart with the Push of a Button.To quickly create a chart, using only your keyboard, select the data range you want to create a chart for and then press F11. Excel automatically creates the chart for you. Select an Entire Range of Cells in Excel.In Excel, if you want to quickly select the entire range of cells you're working on, press CTRL+SHIFT+ASTERISK(*).

## Excel Tip: Copy an Entire Worksheet

Here's a timesaving tip for Excel users who frequently need to copy an entire worksheet (a page within a workbook) of information-such as a list of items for a monthly inventory-from one workbook (Excel file) to another.To copy an entire sheet to another workbook, follow these steps: Open the workbook into which you want to paste the copied sheet. Switch to the workbook that contains the sheet you want to copy. Right-click the Sheet tab of the sheet you want to copy.

## Excel Tip: Revising Protected Files Without the Password

A question was recently submitted by Barry Shrut, who wanted to know if there is a way he can revise a protected workbook when the password left with a departed employee. This tip may do the trick!Although you can't make changes to protected cells in a workbook when you don't have the password, you can copy the entire workbook to a new workbook file and the protection will not carry over to the new file.Select the occupied cells in the current worksheet.

Gail Perry, contributing editor of AccountingWEB and author of the Friday Excel Tips that appear in your weekly newswire, presented a workshop Thursday in which she shared some of her favorite tips.

## Excel Tip: Printing Dot Leaders Before Numbers

Occasionally you may want to produce a column of text and a column of numbers with dots separating the text and the numbers. Here are two methods for producing dot leaders in Excel - one of them may work for you.Method One: This method produces dots that go from the left edge of the cell to one space before the number. The dots are in the same cell as the number.

## Excel Tip: Assign a Macro to a Button on Your Toolbar

If you've created a macro that you plan to use frequently, you can save time by making a button for that macro and adding the button to your Excel toolbar.Follow these steps and you'll be clicking a macro button in no time:1. Choose Tools | Customize, from the menus. The Customize window will appear. Click the Commands tab if it is not already selected.2. Click Macros from the Category list at the left.

## Voice of the Editor

Even though any accounting auditor would tell you it seems like there are an awful lot of tax accountants out there, surely one-third of the country isn't made up of tax preparers, so it's rather startling news to learn that one-third of Americans like to do their taxes. Who knew?