Page 2
Where Excel really comes into its own is in analyzing rather than simply storing data. Obviously, as with any spreadsheet programme, Excel can handle mathematical calculations of any complexity – simply type =4.533/(3.4/4.998)*29^4 into a cell and Excel will find the answer instantly. (4712974, if you’re interested.) But it also has dozens upon dozens of Functions built in for specific tasks.
The most common of these, SUM, MAX, MIN, AVERAGE and suchlike can be calculated with a simple click of a button. But a quick dip behind the scenes shows over a hundred functions, grouped by category, each with help and guidance as needed.
For example, in the Financial category are numerous functions which will be invaluable to economists, accountants and budget-holders, as well as to those who simply want to track their home finances closely. Each function can be manually typed if you know the information required (for example the cells containing the values to be calculated), or if preferred, Excel can guide the user through the process, prompting for the required information and giving extra help where needed (see figure 2)
Figure 2 – Excel prompts for the required information
In the screenshot above, Excel is calculating the depreciation of an asset using one of a number of functions it has for doing this. Also within the financial category, Excel provides functions for calculating:
- Loan payments
- Interest rates
- Investment returns
- Cash flows
For any statistician, Excel provides an abundance of riches – there are over 80 functions available within the Statistical category alone. Some are fairly commonplace – mean, modal and median averages are all covered, as are standard deviations, ranking functions and the like. But Excel can also calculate “The inverse of the gamma cumulative distribution” or “The result of an F-test, the one-tailed probability that the variances in Array1 and Array2 are not significant.”
Now, it’s likely that this means absolutely nothing to you (it certainly does to me!) but the point is this – whatever your analysis need, however unusual it may be, and however specific to your particular line of work, you can be confident that Excel will have the tools to do what you need. It’s perhaps also worth mentioning that Excel can also be programmed with Visual Basic for Applications, so that if you discover that the function you need is not available, there’s nothing to stop you writing your own!
In addition to analyzing your data, Excel provides a number of very powerful tools for summarizing purposes. If you have perhaps 30000 rows of data, it can be almost impossible to find patterns and trends. Excel makes this much easier.
From the data menu, choosing Subtotals allows the user to gain a summary of the data which can be expanded to view the detail as appropriate. Excel puts subtotal information below each category specified, and can further break down these categories, or add averages, maximums and so on as necessary.
A yet more flexible and dynamic summary can be gained by using Pivot Tables. These allow the user to drag and drop fields into a grid, and automatically update to show the resultant calculations. Data can be filtered, expanded and collapsed, dragged and dropped and generally manipulated to fulfil whatever requirements the user may have (See figure 3)
Figure 3 – Pivot tables are an ideal way to summarize data
Excel can also bring in this data from multiple sources. It is able to import data from:
- Older versions of Excel
- Other Spreadsheets such as Lotus 1-2-3 and Paradox
- Databases such as Access and FoxPro
- XML data sources
- The Internet (from any page containing an HTML table, not just web pages set up for querying)
- Text files and CSV files
