iCheck Express™ Microsoft® Excel 2007 Real World Applications

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

Introduction Learn about how organizations use advanced features of spreadsheet applications.

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

How Businesses Use Advanced Features of Microsoft Excel

You already know that organizations use Excel to store, manipulate, and analyze important data. Excel has built-in formulas, such as SUM, MIN, MAX, COUNT, and AVERAGE, which allow the user to perform various calculations quickly and accurately. In addition, you have learned how businesses use charts to visually organize data.

Excel’s features, however, go far beyond these useful tools. Many businesses expect their employees to use Excel’s more advanced features, such as data validation, advanced data analysis, and collaboration tools. Excel’s advanced features allow the user to analyze data in a variety of important ways.

Some advanced Excel features and the common applications of each are described in detail below:

• Advanced filters. Since an Excel worksheet can contain thousands of cells, finding specific records can be a difficult task. Excel’s filters enable the user to specify criteria that then are used to locate specific data. 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. The SUM function is mostly used to add all of the values in a column. Data also can be grouped so that subtotals can be calculated. If a company wants sales figures from each region, data can be grouped by region. The user can use a formula to calculate a subtotal for each region. Then, the user can use a formula to calculate the grand total.



• Advanced Data Analysis. Excel provides a number of advanced methods of analyzing data. These tools can help people see the “big picture” that is sometimes difficult to see when examining large quantities of data. One data analysis method involves using PivotTables. PivotTables allow the user to create concise reports summarizing extensive amounts of data. A second data analysis method is the What-If Analysis tool, which can be used to test possible outcomes when various data values are changed. Both of these methods are useful when making business decisions.
  


• Custom Formatting. The user can format data in specific ways depending on user needs. For example, some data may be in boldface or italics. Excel offers additional formatting options, called conditional formatting, that change depending on the data. One example of conditional formatting would be specifying that any negative numbers be displayed in a red font, while positive numbers are in a black font. This formatting makes values readily identifiable. As the calculations change, a value may change from red to black or from black to red.
  


• Modifying Graphics. Including graphic elements in an Excel spreadsheet can make the spreadsheet more visually appealing and help viewers to make connections between types of data. Images can be modified by resizing, cropping, increasing or decreasing brightness and contrast, and so forth. Charts can be modified in a variety of ways, including adding titles and axes labels and formatting legends.
  


• Document Protection. Companies often post Excel data on a server. To prevent others from tampering with data, a password can be set to protect worksheet cells. In order to prevent unauthorized users from viewing a workbook with sensitive information, such as employee salaries or social security numbers, a password can be set. Then only those who know the password will have access to the data. Other document protection options include allowing users to view data, but requiring a password to modify data.
  


• Collaborating with Others. Excel’s Review options can be used to keep track of any changes reviewers make to a spreadsheet. The original author then can review these suggestions to see whether or not to accept each one. These options make it easier for teams to share ideas. In addition, users can create shared workbooks so that more than one user can use a workbook at the same time.
  


• Templates. Instead of making the same formatting changes over and over again, the user can create a custom template that saves formatting settings for future use. Using templates can save a great deal of employee time. Templates also reduce the possibility of a critical error being introduced into a spreadsheet. Businesses often have Excel templates for timesheets, inventory lists, and personnel files.
  

Assessment Questions

1			What is the purpose of an Excel filter?
2			Give an example of a situation in which a business might want to apply a password to a spreadsheet file.
3			List two data analysis tools provided by Excel.
4			What is an important advantage to using a template?