Go Back   SZONE.US Forums > Do it yourself (DIY) > Computer Tips > Computer Software

Computer Software operating systems, utilities, security, etc

Eight Handy Tools in Excel You Probably Don't Know About

Thread Tools Search this Thread Rate Thread
Unread 07.08.08, 03:08 PM
Steve Boren's Avatar
Steve Boren Steve Boren is online now
Join Date: 02.07
Location: Canoga Park, CA
Posts: 3,507
Blog Entries: 71
Images: 19663
Send a message via AIM to Steve Boren Send a message via MSN to Steve Boren Send a message via Yahoo to Steve Boren Send a message via twitter to Steve Boren
Eight Handy Tools in Excel You Probably Don't Know About

Eight Handy Tools in Excel You Probably Don't Know About


dThese simple features in Excel will smarten up your spreadsheets in no time.

by Neil J. Rubenking
Buzz up!on Yahoo!

If all you're doing with Excel is keeping simple spreadsheets, you might as well be using a ledger and a quill pen. Well, okay, the ledger won't calculate totals for you. But really, there are tons of valuable tools just below the surface that will help you with a wide variety of tasks. And for handy Word tools, see part one of this story. Check out these hot items.

1. Subtotals. When you've got reams of data organized by date, it may be hard to see the big picture. Excel's built-in subtotal feature can help. Click Subtotal on the Data ribbon in Excel 2007 or choose Subtotals from the Data menu in 2003. By default it offers a sum-type subtotal at each change in the leftmost[ column, but you can pick the column and operation you prefer. You can even create multiple subtotals, perhaps for month, quarter, and year. An outline strip at left lets you suppress detail data and just see the different levels of subtotals.

2. Automatic conditional formatting. Conditional formatting in Excel 2003 is a drag. Excel 2007's automatic conditional formatting really helps point out patterns in data, and it's simple to use. Highlight a group of cells and click Conditional Formatting on the Home ribbon. As you mouse over the choices, you see an immediate preview. You can give each cell a color that reflects its rank in the whole range of values, add a transparent data bar whose length reflects the cell's value, and more. It's way easier than tangling with Excel 2003's daunting Conditional Formatting dialog.

3. PivotTables. Excel's PivotTable feature offers quick and flexible data analysis. Want to see how many times each value in a long column occurs? Highlight the column, click PivotTable on the Insert tab (in Excel 2003, select PivotTable from the Data menu), and click Finish. Drag the column-head field name into the Row Labels box and into the Values box (in Excel 2003, the Drop Row Fields Here area and the Drop Data Items Here area). Instantly you get a sorted list of all unique values in the column, along with the number of times each value occurs. That's just one PivotTable trick—for more, see "PivotTable Magic".

4. Document Inspector. Your Excel 2007 documents contain a lot more information than just rows and columns of data. They may include tracked changes, comments, private properties, and more. To examine (and clean out) the hidden data in an Excel 2007 document, click the Office button at top left, point to Prepare, and click Inspect Document. It's very similar to the Document Inspector in Word, but instead of seeking hidden text it looks for hidden rows and columns, hidden worksheets, and objects formatted as invisible. Excel 2003 has no precise equivalent. The most it can do (which isn't much) is to remove personal information from file properties when you save files.

5. Page Break Preview. Excel will print your spreadsheet using as many pages as necessary to display all the data both vertically and horizontally. If the last column doesn't quite fit, printing the spreadsheet may take twice as many pages. It's even more wasteful if you have to toss those pages and try again. To avoid this annoyance, click Page Break Preview on the View ribbon (in Excel 2003 select Page Break Preview from the View menu). Now when you resize columns, change font sizes, or make other layout changes, you'll immediately see the effect on page breaks. It's also a quick way to find out just how many pages you'll be printing.

6. AutoSum. After you've entered a column of figures, nine times in ten you'll end the column with a total. Excel makes it easy to total up a row or column. Click a cell just after the row or column and click the AutoSum button from the Home ribbon in Excel 2007 or the toolbar in Excel 2003 (or simply press Alt-=). Excel sums the row or column automatically. Don't want a sum? Pull down the button's menu to choose among the average, minimum, maximum and other functions.

7. Excel lists/tables. Excel 2003 calls them lists, while 2007 changed the name to tables. Whatever you call it, this feature lets you define a range of cells and easily sort, filter, and total the columns and (hooray!) insert or append rows without screwing up your formulas. Excel 2007 also offers dozens of predefined table styles for easy formatting. In Excel 2003 highlight the cells and choose Data | Lists | Create List from the menu. In Excel 2007 click Table on the Insert ribbon. Done!

8. Get data from the Web. You've found a fantastic table of data on a Web page; go ahead and pull it into Excel! Click From Web in the Data ribbon (Excel 2003 users select Data | Import External Data | New Web Query from the menu). Copy/paste the Web page address from your browser to the New Web Query dialog. An arrow icon appears next to each table that Excel can import; click one or more of these and click the Import button. Presto, the data is in your worksheet. And it's semi-live—just click the Refresh Data button in the floating toolbar (2003) or the Refresh All button on the Data ribbon (2007) to update your worksheet with the latest version of the table.
Eight Handy Tools in Excel You Probably Don't Know About - Solutions by PC Magazine
Boren's Laws of the Bureaucracy: 1. When in doubt, mumble. 2. When in trouble, delegate. 3. When in charge, ponder.
Reply With Quote

excel, handy, tools

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread Thread Starter Forum Replies Last Post
NASA Invites Reporters to Tests of Moon Tools in Hawaii Nov. 13 @NASA N.A.S.A. 0 10.22.08 01:53 PM
NASA Challenges Students To Design Tools For Moon Rovers @NASA N.A.S.A. 0 09.30.08 11:36 AM
10 Essential Blogging Tools Steve Boren Computer Software 0 05.24.08 05:59 AM

All times are GMT -8. The time now is 09:07 AM.

Powered by vBulletin® Version 3.8.5
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2007 - 20017 SZONE.US All rights reserved