By Barry Moltz
Most business owners use only a small percentage of Microsoft Excel for business analysis. In addition, most of us never progress past the elementary level because we keep doing things the same way instead of learning new functions. It is time that all changed!
Here are my top 10 productivity tips with Excel spreadsheets (note: may vary by Excel version).
1. Audit tool bar. Many users go crazy ensuring that the right columns and rows are being used in any given formula. The audit tool bar traces graphically which cells are included in a mathematical formula for a given cell—a real lifesaver to catch embarrassing errors that inevitably happen.
2. Quickly sum up numbers. This is the most common mathematical function used in Excel. Simply highlight the cell where the result is to appear and press ALT plus +.
3. Auto fill series. Spreadsheets are traditionally about some type of series. Save a lot of time in the typing of months or any series by using auto fill—and that way you don’t skip anyone item in that series as well.
4. Freezing highlighted panes. This is helpful in any spreadsheet so cells remain visible while data is entered further down the page. It can be especially useful when comparing facts and figures throughout the sheet. Panes can be split into multiple worksheets, so one pane can be scrolled while information displayed within the other remains static. To keep column titles and information in sight while scrolling, just follow these instructions for Microsoft Excel 2010 or Microsoft Excel 2007.
5. Conditional formatting. This allows users to automatically change the appearance of cells to meet any specifically defined criteria. This conditional formatting technique identifies important data that is critical to many analyses. This is how to take advantage of this feature in Excel 2010 and Excel 2007.
What other readers have suggested:
6. Pivot Tables. These are by the far the best kept secret. They allow users to take raw data in any form and organize it in a myriad of different ways for sorting data into tables. It can be used to look at revenue data by client, by type of offering, by month booked, or by whatever descriptive fields are captured. — Angela Sherlock
7. Change the name of the “Sheet” page. Just right click on Sheet 1, and rename it. This is especially helpful when using multi-pages within one document. — Maria Marsala
8. Keyboard shortcuts like (F7 for Spell Check) are incredibly useful. (As are many others.) — Jen Portland
9. The new Sparkline feature, or sometimes called “micro charts,” illustrates trends within tables. It gives the reader a quick view of the trend and overall direction. — Rob Jager
10. Text to columns. This feature helps parse data into columns. In Excel 2010, this is found under the data tab. It can be used to cut and paste tables from the Internet into an Excel spreadsheet and split this data into columns, which can then be used as tables. It is particularly useful when pasting from a PDF document. — Preston Smith