iCheck Express Series - Microsoft Excel 2003

These articles allow you to further explore various computing topics and include a quiz for review.

Introduction Learn about how organizations use spreadsheet applications.

Directions Read the information below and apply what you learn to answer the questions. Check your work carefully, and click Check Answers.

Advanced Uses of Excel

You already know that organizations use Excel to store important data. Excel has built-in formulas, such as SUM, MIN, MAX, COUNT, and AVERAGE, that allow the user to perform various calculations quickly and accurately.

Excel’s features, however, go beyond built-in formulas. Many businesses expect their employees to use Excel’s more advanced features, such as filters and Web queries. Excel’s advanced features, although slightly more complex than its basic features, allow the user to analyze data in a variety of important ways. Some advanced Excel features and common applications of each are described in detail below.

• Filters. Since an Excel worksheet can contain up to 65,536 rows and 256 columns, finding specific records can be a difficult task. Excel’s filters enable the user to show or hide certain records. For example, if an employee needs to create a report about last summer’s sales figures, he or she can create a filter that only shows the records for June, July, and August.

• Subtotals. As you have probably noticed, the SUM function is mostly used to add all of the values in a column. Data can also be grouped so that subtotals can be calculated. If a company wants sales figures from each region, Excel records can be grouped by region and then a total for each region, as well as a grand total, can be calculated.

• Conditional Formatting. In order to quickly locate specific data without using a filter to hide other data, the user can apply conditional formatting. This feature changes the format of data that meet certain criteria. Conditional formatting is especially useful for inventories. For example, a clothing retailer could use conditional formatting so that each data entry below twenty items appears in red. The retailer will then know which items to order immediately.

• Document Protection. Oftentimes, companies post Excel data on their server. To prevent others from tampering with the data, the administrator can set a password that protects cells. This type of password prevents unauthorized users from changing the data. In order to prevent unauthorized users from viewing a workbook with sensitive information, such as employees’ salaries or social security numbers, the administrator can set a password that is required to open the file. Then only those who know the password will have access to the data.

• Web Queries. Web queries are used to bring tables from a Web page into Excel. You can choose which parts of a Web page you want to include in the Excel worksheet. An investor could pull data from a stock quotes Web page and perform various calculations in Excel.

• Templates. Instead of making the same formatting changes over and over again, the user can create a template that saves the formatting settings. Businesses often have Excel templates for timesheets, inventory lists, and personnel files.

1			What does an Excel filter do?
2			Identify the two types of Excel passwords.
3			Which Excel feature would you use to have certain data appear in boldface?
4			Name an additional way that businesses could use a customized Excel template.