5 Tips to Boost Your Excel Skills
Microsoft Excel is the most popular spreadsheet application in the world and used by most businesses to track all sorts of information. In fact, users are more comfortable creating spreadsheet databases when the more powerful MS Access Database Application would better suite their needs. However, this trend will continue since people find Excel’s spreadsheet format easy to use — just open a spreadsheet and start typing. What could be easier?
Most users open a workbook, start typing data, and add a few rudimentary formulas creating a spreadsheet that meets their immediate need but leaves much to be desired for ongoing use. This “spaghetti” format is hard to follow, a nightmare to update, and visually unappealing.
The following tips will help you design better and more useful spreadsheets:
- Plan all spreadsheet applications. Any workbook that you’ll use on an ongoing basis deserves a few minutes of design planning. Sketch the design on a piece of paper, clearly noting the sheets you’ll use, the information they will contain, and how they relate to other parts of your application. Better yet, enter this schematic into the first worksheet tab in your workbook as a visual map for yourself or anyone else who needs to update your application in the future.
- Use a separate tab for each type of information. For instance, one tab can contain the raw data in list form; another tab will house specialized lists for drop down boxes such as data validation, and a summary tab will draw data using formulas from the other tabs displaying information as desired. This will give your workbook a logical framework and make future revisions easier.
- Learn to use Excel’s built-in functions. You don’t have to be a VBA programming expert to create powerful and useful Excel applications. Excel contains potent built-in formulas called functions. Using them appropriately will aid you in your sheet’s design and functionality.
- Use Excel’s built-in Macro Recorder to add simple automation to your workbooks.
- Master Excel’s secret weapon for quickly summarizing data — Pivot Tables. Pivot Tables are a special feature of Excel that provide quick, easy, and powerful ways to display and summarize information from Excel lists.
There are many books on the market to help you create and design powerful Excel applications. Just go to Amazon.com and search on Excel.
In future issues of The Letter ‘X’, we will review many of Excel’s powerful features and show you how to apply them in your own applications. Make sure you read my Excel article, “Working With Text In Excel”!
