How To Understand Microsoft Excel

Often in business and personal projects, a list is needed. Then a detailed list is needed, which becomes a chart or table. Then a table becomes a system, or several pages of interactive data, and you decide what you need is a matrix or a spreadsheet. Whether you use the term list, chart, table, spreadsheet or matrix, Microsoft Excel is most likely to be the best shortcut for typing the data into the computer, updating your information, and for printing it in an attractive format. Once you input some initial template information into columns and rows, you will find yourself reusing your initial setup many times, while also producing many variations as needed for the occasion. When you use Microsoft Excel to manage any non-prose data, like numbers, phrases, names or lists, you will find that your data is adaptable to many situations.

Microsoft Excel is often used for numeric data, but it's also useful for phrases and names, where a chart might consist mostly of text instead of numbers. For example, my former boss had a complicated estate-planning situation, where each of a dozen accounts was guided by different legal documents. The chart I prepared for her had no numeric data, but the four phrases used, such as "guided by Lewellyn Trust," were repeated in various forms throughout the page. The Microsoft Excel chart reads like a question and answer page, and it was used often as a quick reference.

The List Manager function of Microsoft Excel pops up automatically when you prepare a table like this. List Manager helps by repeating the key phrases in the table, so that retyping the same phrase several times is not needed. The column headings across the top of the page included "account name," "attorney's name," etc. and a few dates were shown so that the latest writing of a document was included in the list. Having this list of which account was guided by which legal document probably saved her a mint in consulting fees, because she was able to look up the basic attributes of her estate plan, without digging through stacks of ancient files and lengthy documents. It's handy to remember that a Microsoft Excel spreadsheet format with mainly textual data can serve as a "quick reference" guide.

Perhaps you need a more data-oriented list, including equations. If so, using Microsoft Excel will save a great deal of time and prevent many headaches. Some people find that they need to sketch a table, or at least a list of column headings on paper, before typing, and this can be very useful. Think of the column headings as labels for each category. For example, a financial table I prepared several years ago always has four months of account values across the top of the page, so the headings during the first quarter will read as follows: January, February, March and April. The left column has a wider format, so that the name of the account has room to be typed in. The right column calculates the percent change from last month to this month, using an equation in Microsoft Excel.

The initial set up of this table was input several years ago, but I have used it every month, and improved it with colors, fill, footnotes, and several other options, as the years went by. When the boss had an eye surgery, I enlarged it by changing the printer settings. When accounts were closed, a row could be deleted, with a footnote added as to where the funds were moved. The same basic table in Microsoft Excel has served this function through the simple file menu command "Save As," with the name of the next month inserted as the file name. For example, for next month, the January column is deleted, and a column for May is inserted, next to April. A minor change to the "percent change" formula is input, so that May's account values are compared to April's rather than March's. Then the template is ready for next month, so that when our account statements are received, the skeleton spreadsheet is already primed for use.

The monthly accounts value table described above has automatic sums at the bottom of each column. These are easily programmed into Microsoft Excel, just by typing the "+" character. An editing box comes up on screen when the typist first uses the plus sign, then Excel assists the user in entering a range of cells from which the sum will be calculated. You can type the cell address, or just click on it, and the formula edit helper will provide cues. For example, January's data is in the "B" column, starting with cell B2 and ending with cell B34. Cell B35 contains the equation "SUM B2 through B34." You can enter this formula by clicking the mouse on the area to be added, or by typing the cell addresses to be added. Press enter when you are done editing your formula, and you go back to regular typing mode.

The cell containing the sum usually displays the result of the equation - a number - but does not display the equation, except when the user is in equation-editing mode. The numeric sum of the equation or formula is programmed easily using these principles, summing up all account values for January. Click the bottom of the February column, enter the plus sign, use the same equation, and repeat these steps for the other months too. To avoid accidentally deleting your programmed cells where formulas are hiding, I suggest using the cell protection setting in those locations of your table. In Microsoft Excel, formula cells can also be displayed in colored type to remind the user where NOT to enter data on top of an equation.

The other equation or formula in the monthly accounts table described above is the percent change column. Again, you could sketch your intent in pencil and paper before attempting this, if it seems complicated the first time you use Microsoft Excel. You want to know at a glance what the change was in each account value, from the end of last month (March, i.e.) to the end of the current month, or April. This idea is also used for quarterly tables, with different column headings to group the months together. You can start your equation again by typing the plus sign, thereby entering into the Microsoft Excel equation-editing function.

Then, you want to type cell "E2," or the top row of data for the April column, to be subtracted by the "D2" cell. This is the difference between April and March data, in a positive number for a gain in April, or a negative term if there was a loss. Do not risk embarrassment by entering the equation backwards, so that a loss shows as a gain; everyone is prone to making this mistake sooner or later. The next step is to divide the difference by the first month, or March, to get a decimal or percentage answer. Since this classic "delta" or "change" ratio equation has two steps, it calculates correctly when you enter it with parentheses, as follows: +(E2-D2)/D2. The  formula you entered is like a command that means in this cell, calculate the difference between April and March, and divide that by the value in March, to display the ratio as a decimal, or percentage.

Now you have the result of your monthly change calculation displayed in one cell of the right column. What about the rest of the cells in the column? This is where the use of Microsoft Excel offers many clever shortcuts. You can copy this formula, using the mouse, into the next cell down, and the next, and you will see that the formula is adapting appropriately to using the data from row 3, row 4, row 5, etc. Yes, formulas in Excel are relative, so they will adapt in this way unless you specifically command them to be absolute rather than relative, which I will save for another lesson. Using this method in Microsoft Excel, you can easily copy your percentage-of-change formula to fill all the cells in the right-hand row of this table.

With these basic formulas of "sum" and "delta," many basic financial lists can be made and periodically updated using Microsoft Excel. To add attractive formatting display features, you can set the decimal places to zero if you want to round to the nearest dollar, or if you want commas for larger figures. Use the format menu to choose how data appears in each cell. For the percent change column, use the percent format choice, but allow the decimal places to show, so that the column does not accidentally round everything to zero, since percent values tend to be less than one.

When you start listing data and analyzing it with spreadsheets, you can see a multitude of uses for Microsoft Excel. From a list of the names in a band, with their uniform sizes, and paid/unpaid status, to a spreadsheet where a few variables are typed in and the rest of the cells are formulas, there are many applications that can benefit from the short cuts offered by Microsoft Excel. I have used Microsoft Excel to make a simple list of pet medications and their expiration dates, but I have also used it to make a complicated database analyzing stock prices. Remember, a wordy list can be a table, but so can the number-crunching list, as described in the examples above.

Think of it this way: Any data that you would normally figure on a calculator might be better displayed as a chart. Whenever several equations are involved, like projecting how a price would change in a certain scenario, use the same table template with different starting data or "variables." Just remember to keep saving each scenario with a different file name, if you want to use it again later. When you move columns or insert new rows, go back and double-check the formula content and cell addresses regularly. Copying formulas is convenient, but you can get some hiccups if you have deleted the columns where the formula is seeking its data. It's fun to find uses for Microsoft Excel that nobody else has seen before, and everyone can adjust Excel slightly to make their own list, matrix or table.


Share this article!

Follow us!

Find more helpful articles: