Neon-komputadór

Application: Microsoft Excel

Microsoft Excel is an adaptable spreadsheet application which serves, quite adequately, most needs for data processing. If complex list processing tasks are necessary however, a database application, such as Microsoft Access is recommended instead. An important decision that should be made before starting any list processing is deciding on the complexity of the tasks required and whether a spreadsheet will be adequate or whether a database will be necessary. Unlike a Word Processing document, where the primary objective is to display text in a legible and coherent manner, the primary purpose of a spreadsheet is data processing. Excel is primarily a calculation and sorting application rather than a presentation tool.

As with the discussion on Word, this is primarily written from the viewpoint of Excel 2002 (also known as Excel 10). The basic operations, key features and common problems are described. New features in Excel 2002 include integration with the style of the Windows XP operating system, smart tags and task panes, expanded Function Wizards, and error checking for formulas. The Excel screen consist of a Title Bar, a Menu Bar, a Formula Bar, usually the Standard and Formatting Toolbars, a Name Box, a Task Pane (in Excel 2002), Worksheet Tabs (the default is three), Horizontal and Vertical Scrollbars, a Status Bar, and a Worksheet Window consisting of Rows, Columns, an Active Cell and a Cell Pointer.

The basic group of commands are displayed as drop-down menus underneath the status bar – File, Edit, View, Insert, Format, Tools, Data, Window and Help. To open a new Excel document go to File –> New. To open an existing document go to File –> Open. Excel can open a reasonably wide range of documents, including all previous versions of MS Excel, text files, webpages, MS Access databases, Lotus 1-2-3 files, Quattro Pro files, MS-Works, dBase, SYLK and Data Interchange Format. Excel assumes that you’re working with Excel files and to open files of a different format one must select what type of file one is trying to extract from the Open Window.

When one saves a file (File –> Save), Excel will assume that you want to save the file in the version of Excel that you’re using. Like MS-Word, this is not always the case, as you may want to send the file to people who aren’t using Excel or for that matter, the version of Excel that you are using. For example, if it is going to be distributed around the Ministry for using the local area network, one must save the file according the oldest version of Excel that is being used. MS-Excel-95 is a good default. If the spreadsheet is being distributed to someone who is not using Excel, if it is the data rather than formatting that is important, or there is limited bandwidth (for example, the spreadsheet is being sent via webmail), it is preferable to save the file as CSV (comma separated values). This format is readable by any spreadsheet or database program. Note however that separate worksheets must be saved individually with CSV and that formulae will not be saved - just the raw data.

Excel describes its files as workbooks, consisting of three sheets and columns (from A to IV) and rows (from 1 to 65536). The combination of rows and columns are referred to as cells (e.g., D45) which hold the data or formulas. Data in cells are described by specific formats (Format->Cells); General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text or Special. These formats determine the way that Excel will display the cell data and how it will conduct calculations and sorting to the data. Other formatting options include Alignment, Font, Border, and Patterns. With a spreadsheet it is the data, not the presentation that should be the priority of one's work, although some formatting options might make the data more legible. Necessary formatting options include the size of rows and columns - it's usually a good idea that you can view all your data in a cell! An easy way to achieve this is to highlight the entire text (by clicking on the top left of the worksheet) and then clicking on a column or row separator. The sheet will automatically adjust to the necessary size to view all the data. Also, Excel offers the option of Conditional Formatting (Format -> Conditional Formatting), which can certainly be a useful aid for legibility.

Data entry and modification of cells can be a highly repetitive task and fortunately Excel offers a number of tools to make this easier. Like with Word, there are the basic Undo, Copy, Cut, Paste and Delete options from the Edit menu. In addition however, there are also Fill (Edit-> Fill) and Clear (Edit -> Clear) options. By selecting a range within a column or row the former option repeats the data or formula in the base cell to all selected cells. For the latter option, the cells are cleared of the contents, formats, comments or all (equivalent of Delete). Further Excel can make a guess at sequence repetitions (Autofill). By selecting data in a row or column that is in a logical sequence (e.g., 1, 3, 5, 7, 9 etc), clicking on the right-bottom column of the final cell in the sequence and dragging down or across on the columns or rows Excel will automatically fill in the empty cells according to the sequence. Excel also offers Find and Replace options, the ability to Copy or Delete entire worksheets and the ability to move the cursor to a particular cell or otherwise marked point in the spreadsheet.

The various View options that Excel offers are not critical to the function of the application, although they may be useful to legibility of display. Pageview (View -> Pageview) will arrange the spreadsheet into pages according to the page setup options of the printer used. It will not function of course, if there is not a default printer. Like MS-Word, a large variety of optional Toolbars are available. Most of these are only useful if the spreadsheet includes the particular formats in significant levels. Usually the Standard and Formatting toolbars are all that's required, along with the Status and Formula Bars. In Excel, the Standard toolbar comes with Excel-specific functions, including an Autosum button, Ascending and Descending Sort buttons, and a Chart Wizard. As with other recent MS-Office applications, custom Toolbars can be created (Insert -> Toolbars -> Customize).

A common requirement in a spreadsheet is the need to add additional cells, rows and columns or in some cases, additional worksheets. This can all be achieved through the Insert menu options (Insert -> Cells, Insert -> Columns, Insert -> Rows, Insert -> Worksheet). A further option, which is useful for display purposes, is Insert Chart. By selecting a range of data across rows and columns and selecting Insert -> Chart a variety of display options is provided (column, bar, pie etc) with the options of adding chart headers and references. Charts provide a quick way for humans to interpret data in a heuristic manner.

Function inserts (Insert -> Function) are a workhorse of Excel. Excel can provide a variety of calculations to a range of cells by assigning a formula to an another cell. For example, the cell A10 can hold the sum of cells A1 to A9 with the formula =SUM (A1:A9). Other functions include the average of selected cells, =AVERAGE(cell range), the largest value =MAX(cell range), an estimated standard deviation, =STDEV(cell range) and so forth, including conditionals. Cell ranges are expressed in the format of comma separations for individual cells, for example, (A1, A2, A3, A4) or with colons for a range of cells, for example (A1:A4). When one is used to the way formulae are expressed the use of the formula bar can be quicker than using Insert -> Formula from the menu. Excel orders complex calculations in the accepted mathematical order, that is, bracketed equations first, percentages and exponentials, multiplication and division, addition and subtraction and finally comparisons.

The range of functions in Excel is so great that they are grouped into categories, with the default of the Insert -> Function command defaulting to the most recently used. Other categories include All (a very long list!), Financial (such as depreciation rates and interest returns), Date and Time, Math and Trig (rounding, conversions, functions etc), Statistical (averages, standard deviations etc), Lookup and Reference, Database (a small range of mainly mathematical calculations from a specified database), Text (various format to text conversions, find and replace etc), Logical (And, False, If, Not, Or, True) and Information (checks the result and type of cell values).

Also on the Insert menu there is an option for labelling cells or a cell range which is often very useful (Insert -> Name). For the user, it is a lot easier to remember a name that refers to a set of cells rather than the range itself - for example, if the cell range B5:Z5 represented a row of donation values, it is at lot easier to remember these by the name "DonorValues" rather than the range B5:Z5. The names ascribed to cell ranges appear as a menu item in the name box. Finally, in many circumstances, a user or reviewer may wish to draw attention to a particular cell, which can be achieved through Insert -> Comment, which marks the cell as commented on the upper right-hand corner. Other Insert functions (Picture, Diagram, Hyperlink) are self-explanatory and require no further elaboration here.

The Tools menu provides a range of options to check spelling, protection (sheet, workbook or range), sharing and error and auditing features. The error checking tool (Tool -> Error Checking) checks and highlights a sheet for syntax or mathematical errors. The Goal Seek tool adjusts the value of a cell to obtain a specific result for another cell. The Formula Auditing tool derives from the ability of Excel to track changes about changes to a worksheet and is useful when a worksheet is shared and a history of changes and revisions is needed. Tracer arrows indicate the precedent and dependent relationships between cells. Selections that should be implemented from the Options tool (Tool-> Options) include Autosave (every 10 or 15 minutes), security options (particularly high Macro security), expanding the recently used file list to the maximum, and all error checking rules except formulas referring to empty cells. Other options are according to personal taste, however the problems with U.S. English remarked upon in the section on MS-Word is also applicable here.

Data manipulation options include Sort, Filter, Form, Subtotals, Validation, and importing external data (Data ->Sort, Data -> Filter, Data -> Form etc). As the name indicates, Sort arranges selected column according to ascending or descending order, with blank cells always placed last. With an alphanumeric sort, Excel sorts character by character with numbers and special characters prior to letters, thus the cell value "A900" will be sorted after "A9" but before "A91". A Filter is used to sort data into subsets. A Filtered list only displays the criteria established by the user. Applying a Filter to a list (a series of worksheet rows with related data), allows Excel to find, for example, the largest or smallest numbers among the data, specific text, blank or nonblank cells, top and bottom numbers by percent. A data Form provides the means to enter or display a complete row of information at a single time. Data Validation is used to make sure that entries are within user-determined ranges. When a worksheet has complex and long lists, Pivot Tables and Pivot Charts display the information in an easy to read manner. By activating Data -> Pivot Tables and Charts, an Excel Wizard aids the user in the construction of the Table and, if desired, a Chart.

The Window Menu allows the user to switch between Excel workbooks or display multiple workbooks simultaneously. The Help menu is identical to that with MS-Word, with the exception that there is a special option for Lotus1-2-3 help rather than Word Perfect. As with the discussion on Word, use of the Office Assistant is not recommended. When the data and operations of a Worksheet is completed to the satisfaction of the user often a printed copy is desired. When printing a file (File -> Print) always conduct a preview beforehand (Print -> Print Preview) and modify any printing settings necessary through File -> Page Setup. A common alternative in Excel because of the layout of spreadsheets is to choose landscape, rather than portrait orientations.