Excel tips | AccountingWEB

# Excel tips

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

## Excel Tip: Place Column and Row Titles on Multiple Pages

You can lock in column and row headings so that they repeat page after page and you won't have to reenter them at the top of each page. Follow these steps to set up repeating headings:First, choose file, Page Setup from the menus, and click on the Sheet tab.To assign rows that will repeat at the top of each page:Click in the Print titles area of the Page Setup/Sheet screen.Click on the row number of the row from your worksheet that contains the column titles that should repeat.

## Excel Tip: 10 Quick Range Name Tricks

If you've ever used a range name in your Excel worksheet, you already know what a time saver this device is. Here are some suggested uses for range names that may speed up the time you spend on your worksheet even more. Don’t worry if you don't know how to name a range - that's covered in the first tip! Name a range in a hurry by first selecting (highlighting) the range, then clicking in the Name Box in the upper left corner of your Excel screen, and typing the name for the selected range.

## Excel Tip: Sorting From Left to Right

You probably already know how to sort a vertical list in Excel (and here's a refresher if you don't), but what can you do if you want to put your column headings in alphabetical or chronological order? Can you sort your data from left to right?Sort Top to BottomTo quickly sort a column of data from top to bottom, you can click anywhere in the column and then click the A-Z button on the standard toolbar (or click Z-A if you want to sort backwards). The column will be sorted in alphabetical or chronological order.

## Excel Tip: Dealing With Rounding Errors

Have you ever experienced the frustration of displaying a worksheet without full decimal places and having Excel round the numbers for presentation purposes, but if you look closely at the calculations, the displayed numbers don't quite add up?For example, consider this calculation:23.4923.4946.98If you display your Excel spreadsheet without decimal places, the calculation is rewritten like this:232347The answer is technically correct, but who's going to believe it?One way in which you can solve this problem is to request that Excel present the w

## Excel Tip: Change Default Date Style

You may try to enter September 22, 2000 as 9/22/00, but Excel has other ideas about how the date should be displayed. Whether you prefer the style of 22.9.00, 9/22/2000, 9-22, or something completely different, you can leave specific instructions for how your dates should appear.The style that Excel uses to display your date is actually controlled outside of Excel, in the Windows Control Panel.

## Excel Tip: Pivot Tables

David Carter's series of PivotTable tutorials on our AccountingWEB-UK site have become an Internet legend. The first tutorial, Budgeting with PivotTables, has been accessed more than 9,600 times and was highlighted as a particularly valuable resource by the Internet Scout Report on business and finance.This page gives you an overview of what is possible with this relatively unsung feature within Microsoft's Excel spreadsheet program.

## Excel Tip: Change Formula to Actual Value

Is your formula your private property? Are there times when you provide spreadsheets for others to view but you don't want to display the underlying calculations? Once you've created a worksheet and performed all the calculations, you can switch the calculations to the calculated values so only the final numbers appear, both in the work area of spreadsheet and in the formula bar at the top of the spreadsheet.Change the contents of a cell or group of adjacent cells from formula to value by following these steps:1.

## Excel Tip: Ten Tips for Working With Multiple Sheets

There are several tricks and shortcuts you can employ when you want to use multiple sheets within one workbook. Here are a few of our favorites - feel free to add a comment and share your own tricks with the crowd!Insert new sheets.Are you unhappy because only three sheet tabs appear at the bottom of your screen? Choose Insert, Worksheet from the menu and add another (and another, and another…). To speed things up a bit, click on Sheet1, hold down Shift, click on Sheet3 and three sheets are selected.