Excel Tips

    

Confessions of an Excel Junkie

Over the years I have become wholly dependant upon Microsoft Excel. Not only do I believe that Excel is the single most important tool for the professional CPA, I believe that my brain now thinks like an Excel worksheet. This phenomenon transcends my business life and affects my personal life as well.  Simple questions such as “where shall we go on vacation?”; “what movie would you like to see?”; or “what do you want for dinner?” result in a grid-like listing complete with A-B-C ratings and, sometimes, even cost estimates. If the data is too much for my mind to handle, I find myself gravitating towards the computer where I can lay it all out on my “thinking grid”.

I have found that the more you know about Excel, the more useful the tool becomes. If you would like to increase your knowledge and usability of Excel, I highly recommend that you try out the following ten Excel commands. In my opinion, these commands are among the best advanced Excel applications and commands that are especially well suited to the work of a CPA. Each of these applications or commands is briefly described below, and explained in detail with specific detailed examples at the following web site: 

www.exceladvisor.net 

1. The Subtotal Command
2. Pivot Tables
3. Publish to the Web
4. Web Queries
5. Sharing Workbooks and Tracking Changes
6. Custom Lists
7. AutoFilters
8. String Arithmetic
9. The =LEFT & =MID functions
10. Data Consolidation
     

The Subtotal Command

Excel has the ability to insert or remove dozens of subtotals with just a single click of a button. Consider a worksheet containing 10 columns and 100 rows of data. Most CPAs would spend 5 minutes or more inserting blank rows, writing formulas, copying formulas, and formatting the data to generate totals, subtotals, and grand totals, yet Excel’s DATA SUBTOTAL command will do all of these steps in less than 5 seconds. Check out my detailed example here:

http://www.exceladvisor.net/subtotals.html

Pivot Tables

Microsoft claims that they run their entire organization using Pivot Tables. Further, in the Advanced Excel classes we teach to CPAs, approximately 10% use Pivot Tables and indicate that they are the most important reporting tool they use. Given the enormous success of Microsoft, as well as the strong endorsement from CPAs who use heavily use Excel, perhaps we should pay more attention.

Pivot Tables may be one of the most powerful features of Excel. However the first time you use them, be prepared – they do require more than a few neurons in order to grasp the Pivoting concept.  Simply stated, PivotTables present multidimensional data views to the user.  To re-arrange the worksheet, users are allowed to drag and drop column headings to move data around.  PivotTables are a great data analysis tool for management.  Don’t get discouraged!  Save your information first, and give PivotTables a try. To help you get started, the following exercise will walk you through it:

http://www.exceladvisor.net/pivottable.html

Publish Excel Files to the Web

Any Excel file, or any part of an Excel file, can be saved and published to your web site in just a few seconds. This makes it possible for you to share your data with others, and even with yourself when you are at home or traveling on the road. The process is almost as simple as saving a file to your hard drive, except that you save the file to your web server instead. This involves you pointing to your web server address, and providing a login name and password when prompted to do so. Here is a page that walks you through this process from start to finish:

www.exceladvisor.net/webpage.html

Web Queries

If I only have time to demonstrate one feature in Excel, this is it - but only if it is between 9:20am, and 4:20pm EST. Excel has pre-designed queries that can create massive stock portfolios in less than 10 seconds. All you need is a connection to the Internet and some stock ticker symbols. Next select DATA - IMPORT EXTERNAL DATA – IMPORT DATA and walk through the wizard. In seconds, a complete up to date portfolio is displayed that is synchronized to the stock market’s changing stock prices. With each click of the Refresh button, the stock prices change before your eyes. Just add columns for the number of shares you own, and the total value of those shares – and this beats picking numbers out of the newspaper. You can follow this process in detail via the link below:

http://www.exceladvisor.net/webqueries.html

Sharing Workbooks and Tracking Changes

This command allows more than one person to work on the same worksheet at the same time – even across the Internet. However, that is not the really cool part. When you share a workbook, Excel keeps track of every change that is made by every person. Once the work is completed, you can run the review changes tool and simply accept or reject each change. With this tool you do not have to review the entire spreadsheet, only the changes are reviewed. Excel also documents who makes each change, and even keeps track of what was in each cell before and after the change was made. I find this to be a useful tool even for just one person – it allows me to later review the changes I’ve made – just to be sure I have not made a mistake. Check it out here:

            www.exceladvisor.net/web-shared.xls

Custom Lists

Custom lists are fast and easy to use. Just type in the word January, grab the corner of the cell, and pull it down across multiple cells. Excel will automatically fill in the remaining months for you. You can also create you own custom lists that include employee names, your chart of accounts, inventory items, locations etc. Give this a try – I use it all the time. (There is no downloadable example because custom lists are not embedded in Excel files. You will need to try this from scratch on your own computer.)

AutoFilters

Excel provides the capability to automatically filter large volumes of data by any column with just a couple of mouse clicks. For example, assume you had hundreds of rows of data for customer transactions and you want to see only those transactions greater than $500. Just click on AutoFilter, and then click the sales column’s filter pull down arrow and choose greater than $500 – Excel does the rest for you. Click below to walk through a detailed example.

http://www.exceladvisor.net/web-autofilter.xls

String Arithmetic

While most CPAs think of Excel as a tool for crunching data, it is also a great tool for crunching text. With string arithmetic, you can combine text together with a simple formula, and then copy that command down the worksheet thousands of times to crunch large volumes of data. For example if cell A1 contains the word “Smith” and cell B1 contains the word “Sam”, then a formula in cell C1 could be written as follows: =A1&” “&B1 which would produce the result “Sam Smith”. Click below to see more examples of how this works.

http://www.exceladvisor.net/string.xls

The =LEFT and =MID Functions

In much the same way that the “&” sign can be used to string text together, the =LEFT and =MID functions can be used to break text apart. For example assume that “Column A” contains thousands of rows of data similar to this phrase in cell A1: “XYZ Company (770) 555-6333 $780. Now assume that you wanted to extract the company names into column B, the phone numbers into column C and the dollar amounts into column D. These functions will do the trick, but you will need the help of the =FIND function as well. Click below to see how it works.

www.exceladvisor.net/excel/left.xls

Data Consolidation

The Data Consolidation command found on the DATA menu provides you with a dialog box which will consolidate one or more columns of data together automatically. Assume for a moment that you have just typed in several hundred rows of data representing each check your client wrote during the year. The Consolidate command will instantly consolidate that data into a P&L statement. For example, if your client wrote 17 checks for repairs during the year, Excel will combine those 17 items together and display them as one total amount for all 17 transactions. To better understand this command, click on the link below and take a look at our detailed example:

http://www.exceladvisor.net/consolidate.xls

Inspired Excel Examples

I use Excel to organize my whole life. Over the years’ I’ve maintained Excel-based web sites for my tennis team, church group, and even my son’s baseball and football teams. I have published password protected Excel pages containing my complete database of contact names and phone numbers. However, this is just a start. If you really want to gauge how truly inflicted I am with Excelitis, listed below are a few of the more off the wall creative Excel files I found lurking on my computer: (These files can be viewed and/or downloaded at the following URL: www.exceladvisor.net/creative.html).

• Landscaping Design  (shown to the right) – I used the drawing tools to lay out the landscaping for a new house.
• Window Treatment Measurements – Captured exact dimensions of all windows for the creation of window treatments.
• Baseball Fielding Positions – I used this template to position the baseball players from game to game.
• ID Badges – This is an example of how you can easily grab a logo off the Internet, and make an employee badge.
• Tournament Schedules – This template was used to layout the playoff tournament for little league baseball.
• Carpet Dimensions – This template was used to capture measurements for carpeting for our bedroom, including a border carpet around the edge.
• Foreign Language File – I carried this file with me on trips to Europe to help me brush up on my French, German, and Italian.
• Present Labels – When ever I can’t find gift labels when wrapping presents, this file works well.
• Sample Form – You can make any form with Excel, here is one that is intended to be a mockery of the government’s “Know Your Customer” program.
• Baseball Statistics – Of course Excel is great for compiling all of those baseball statistics.
• Evaluation Form – As a speaker, an evaluation form is a crucial tool for every presentation.
• To Do Lists – You can write a To-Do list anywhere, but Excel let’s you sort the list by priority and difficulty, as shown here.
• Accounting Software Comparison Tables – Mostly I use Excel to convey information, as in this example.
• Accounting Software Comparison Charts – Being a visual guy, I like to view data in the form of a chart as well.
• Life Size Picture of Me – I used this file to print out a life size picture of me. Once the pages are cut and pasted together, I can be in two places at once.

Of course most of the 4,000 Excel files on my computer are business related – basic financial statements, projections, portfolios, that kind of stuff. I got hooked on spreadsheets beginning with VisiCalc in 1982, and progressed to Lotus 1-2-3, Symphony and then Microsoft Excel. Back then I wrote forecast templates that were used on more than 300 bond issue projects totaling $3 billion. The power of my spreadsheet designs helped my department grow from 3 people to more than 30 people in just 2 years. I’ve been an Excel instructor for more than 15 years, and I have instructed more than 10,000 CPAs in the use of Microsoft Excel – I really enjoy this work. In 1992, I consulted with the Microsoft Excel Programming Team for two days and advised them on improving future versions of Excel. I think that this represents enough evidence to classify me as an Excel Junkie.

- END -