add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Preparing reports and performing data analysis are two of the main reasons people use Excel.
Excel offers strong and efficient data analysis tools. Select the Data menu to view the wide range of options Excel offers. You can sort, filter, insert subtotals, insert multilevel subtotals, consolidate data, and create incredible PivotTables from data contained in an Excel sheet.
In order to take advantage of the various features and techniques, you need to understand how Excel works. You need to learn to follow some clear and simple guidelines. Simplicity is the name of the game here, and Excel guarantees you results.
This chapter is one of the most important chapters of this book. Organizing data in the sheets of a workbook, according to Excel’s data management rules, allows you to use formulas to find data, call up data needed to prepare complex reports, and use the data analysis tools that are listed in the Data menu.
Guidelines for Organizing Data in Excel
© List structure (also called a database): Each list of data must start with column labels in the first row, and there can only be one row of column labels. All of the cells in the column labels row must contain unique labels. Every data column should have a non-blank entry in the heading row.
A data list does not include empty rows, subtotals, or totals at the bottom of each column. The columns in a data list are adjacent to each other.
The list has distinct borders: one blank row and one blank column between the list and any other data on the sheet. To check the borders of the data list, do the following: select a cell in the list, press Ctrl+* and check the region selected. The entire list should be in the selected region.
© Data fields: In a list, a vertical range of data (column) is referred to as a field. The text in the column label is the Field Name, and the Field Items are the data in the cells in the column beneath the column label. Each field (column) contains one specific type of information (dates in a date field, totals in a total field, or customer names in the customer field).
© Records: In a list, a horizontal range of data (row) is referred to as a Record. The cells in a record (row) do not have to contain data (for example, an invoice number is missing in an invoice number field).
Tip
Create only one data list in a sheet and place it in the corner of the sheet (in the first cell in the list, A1). The remaining cells in the sheet outside the data area are empty.
The figure on the next page provides an example of a data list in a sheet.
© The title above the database is separated from the database by an empty row.
© Row 3 (the column labels row with the names of the fields) is formatted with text wrapping in the cells (for text wrapping – select row 3, press Ctrl+1, and select the Alignment tab. Now, select Wrap text, and click OK).
© The field names are unique (the column labels, row 3).
© There are no spaces between rows and/or between columns.
Preparing the List to Sort, Filter and Insert Subtotals
You can add more tools to the data list you created in Excel (either manually or by importing data from external systems) to help you sort, filter, and analyze data.
Using formatting to set apart the column labels row (field names)
Remember that when you create a list in Excel, you cannot leave any empty space between the rows. Make the column labels row (field names) stand out visibly by formatting them with a bold font and/or by filling the cells in a different color.
Freezing column labels
It is easier to review the data if you keep the column labels visible at the top of your worksheet as you scroll down a list.
1. Select cell A4 (the first cell beneath the first column (field) label in the database).
2. From the Window menu, select Freeze Panes.
Entering Data into the List
Excel offers a number of different ways of entering data into a list.
© Importing the data from external systems. See Chapter 16, Importing Text Files.
© Entering data manually. See Chapter 2, Text.
© Using Forms to enter data.
Using forms to enter data
Using forms to enter data allows you to add new records, delete existing records, automatically copy formulas when new records are added, search by criteria and more. Select a cell in the data list and from the Data menu, select Form.
Take a look at the Form dialog box:
© The data form that appears has the same name as the sheet.
© You cannot enter data into fields (cells) that contain formulas.
© You can find data records based on criteria you specify.
Disadvantages
If you use a Form, you cannot use helpful tools such as AutoComplete, Select from List, and Validation.
Applying Color to Data Based on Criteria
Data lists contain a large variety of data representing different things. When the font color is black, the cells are white and the borders gray, and adding a color will make the data in the cells or rows stand out and will help you view data that meets certain criteria.
Example:
In an aging report, you may want to have data on customers with outstanding debts of over 60 days stand out. You might also want to use a different color to call attention to outstanding debts of over 90 days.
Later in this chapter we will discuss how to use Conditional Formatting to apply color to data based on criteria. See Chapter 18, Filtering and Chapter 19, Subtotals for additional techniques that can be used for coloring data based on criteria.
Using conditional formatting to color data
See the figure of a data list at the beginning of the chapter to Format data by using a formula:
1. Select cell A3, and press Ctrl+* to select the active region.
2. From the Format menu, select Conditional Formatting.
3. In the Condition 1 drop-down list, select Formula Is, and type =$E3=”ASIA”. Click Format, select the Font tab, select the color, and click OK.
4. In the Condition 2 drop-down list, select Formula Is, and type =$E3=”AFRICA”. Click Format, select the Font tab, select a different color than you selected for Condition 1, and click OK.
5. In the Condition 3 drop-down list, select Formula Is, and type =$E3=”USA”. Click Format, select the Font tab, select a different color than you selected for Condition 1, and click OK.
6. Click OK.
Explanation
The cell reference in the formula is made up of the absolute reference to the column and the relative reference of the row. Excel checks each cell in the list to see if the data in the same row in column E meets the criteria you selected in Conditional Formatting.
A formula in conditional formatting is similar to the initial argument in an IF formula, Logical_test. If the formula evaluates to a logical value of True, the cell will be formatted as set in the Font tab.
Applying color to maximum or minimum values
1. Select cell A3, and press Ctrl+* to select the active region.
2. From the Format menu, select Conditional Formatting. Select Formula Is for Conditions 1 and 2 according to the figure with the formulas Min and Max. Select the formatting you desire.
Note
¤ Select the first cell in the data list before selecting the entire list. Be sure to distinguish between absolute reference and relative reference when entering the formula.