Exercise One: Using Data Forms and Creating Reports
Background
Excel has database
capabilities that are suitable for small and simple tasks. Microsoft Office
includes a full database application called Access. Access can be used for
tasks that require more complex data management capabilities.
A data table consists
of one row of labels, called field names or headers, and several rows of data.
The headers row is always the first row, above the rows of data. The rows of
data should not contain any blank cells.
Tables should be
separated from other areas in the worksheet by a blank row and a blank column.
This helps Excel determine which cells are included in the database table. You
can select any cell in the data table to work with the entire set of data; you
do not have to select the entire table.
Download and save the file www.cob.sjsu.edu/bus91L/textbook2/BPTDATA.xls.
Open the file. Select
the DataSet worksheet.
Sort the
Table
You can use the A-Z or
Z-A icons on the Data tab to sort data in a table.
First click on any cell in a column that contains data. Then click on the icon.
Data in the selected column will be sorted. The other columns in the table will
be rearranged so each row of data stays intact. You can also use the sort icon
to sort by multiple criteria.
1.
Click in column
D. Click on the A-Z icon to sort the table based on majors.
2.
Click in column
E. Click on the Z-A icon to sort the table in reverse
order based on class.
3.
Click in column
B. From the Data tab, click the Sort icon.
4.
Set the “sort
by” option to last name.
5.
Set the “order
by” option to A to Z.
6.
Click on “add
level”.
7.
Set the “then
by” option to first name.
8.
Set the “order
by” option to A to Z.
9.
Click OK.
Create a
Filtered List
1. Click on any cell in the data table.
2. On the Data tab, Select Filter. Pull-down arrows will
appear next to the column headings.
3. Click on cell D2 and select the pull-down arrow. Click
on all to deselect all options. Select Marketing.
4. Click on cell E2 and select the pull-down arrow. Click
on all to deselect all options. Select Senior.
5. Click on the Office Button. Select Print and Print Preview
to view the report.
6. Close print Preview.
7.
Turn off the
AutoFilter. Click anywhere in the data table. From
the Data tab, Sort & Filter group, select Filter.
Add
Subtotals
1. Click in column D and sort the data in the table in
alphabetical order by Major, using the A-Z icon.
2. Click in any cell in the data range.
3. Click on the Subtotals icon on the Data tab, Outline
group.
4. In the dialog box, “At each change in” select Major.
5. Set the “Use Function” to Sum.
6. For the “Add subtotal to” boxes select the five
purchasing categories.
7. Of the three checkboxes, select only “Summary below
data.”
8. Click OK.
9. Your report will now include subtotals. Scroll down
to see them.
10. Click on a minus sign to hide the individual names
and details.
11. Click on a plus sign to show the names and details.
12. Click in the table.
13. Click on the Subtotals icon in the Outline group on
the Data tab.
14. In the dialog box, click on “Remove All.”
Pivot
Tables
Pivot tables give you tremendous flexibility in studying and manipulating large amounts of data. You can sort multiple fields of data using multiple criteria, and manipulate the data in several different ways. Pivot tables are particularly useful for analyzing marketing and manufacturing data involving multiple factors.
Create a Pivot Table Report
Click anywhere in the data table.
From
the Insert tab, Tables group, click the Pivot Table icon to open the Create
Pivot Table dialog box. Click OK.
The
pivot table layout template appears. To create the pivot table you choose
fields from the database. Fields containing text are used as filters in the
upper left corner, as row names along the left side of the report, and as
column names above the report. Fields
containing numbers are used in the data area. You can add more than one field
to any section.
The Pivot Table Field List
should be open. If it is not showing, right-click on the pivot table to open
it. You can right-click on any field name, or drag it, to add it to the report.
·
Add Major as a
Filter.
·
Add Class as a
Row Label.
·
Add M/F as a Row
Label.
·
Add Phone as a
Value.
·
Add Music as a
Value.
·
Add Clothes as a
Value.
·
Add Food as a
Value.
Modify the
Report
You
can filter data so only the information that matches your selections will
appear.
Click
on the pull-down box next to the Majors field (above the report) and select All to deselect the options. Select Accounting. Click Ok. Notice
how the numbers changed in the report. The numbers change each time you choose
a different option. Reselect All.
You
can move field labels between columns and rows. In the row
labels, right-click on M/F and select Move. Change M/F to a Columns
label. Notice how the report changes.
Now
click on the M/F column label and drag it back into the row labels area.
Filter
the data so only females are included. Click on the pull-down arrow next to
M/F. Deselect M and only F is selected. Click OK.
Click
on the pull-down arrow next to M/F. Choose Clear Filter.
You
can perform several different calculations on the numerical data. In the Pivot
Table Field List, find the Values box in the lower right corner. Click on Sum of Music. Choose Value Field Settings. Change
the setting to Average. Click OK.
The
Music column in the report now shows a long string of decimal places. Mouse
over those cells and format them as numbers with two decimal places.
Click outside the pivot table to close the Pivot Table Field List.
Combining and Splitting Text
You can use the
CONCATENATE function to combine short text strings into a longer text string.
Use the data tool “Text To Columns” to split a long
string into separate parts.
Download and save the file www.cob.sjsu.edu/bus91L/textbook2/BPTDATA.xls.
Open the file. Select
the TextData Worksheet.
Convert Text to Columns and Text to
Columns
1. In column C combine columns A & B
using the Concatenate function.
2. Paste column C values into column D using
Paste Special.
3.
Convert
column D back into 2 Columns using Text to Columns.
Using Data
Forms
Forms are a user-friendly method of maintaining a
data table. The Forms icon is not on a tab, but you can add it to the Quick
Access Toolbar.
·
Right-click on
the down arrow next to the Quick Access toolbar. Select More
Commands. In the Choose Commands From box, select All
Commands.
·
Find the Forms
icon in the list. Click on the icon. Click Add. Click OK.
Add a
Record to the Table
1.
Click on any
cell in the data table and click on the Form icon.
2.
Click New.
3.
Create a new
record using your personal data. Use the Tab key to move between fields. The
ID# will be 401. Expenses will be $55 for each category.
4.
Click Close to
add the record to the data table.
Find and
Edit a Record in the Table
1.
Click on any
cell in the data table and click on the Form icon.
2.
Click on
Criteria.
3.
Enter 106 for
the ID#.
4.
Change the
student’s class to Sophomore.
5.
Click close to
add the changes to the record.
Report 1
Use
the Filter icon to create a report showing all the Sophomore Women majoring in
Finance. Print the report.
Report 2
Sort
the table by class. Use the Subtotal icon to create a report showing average
phone bills for each class: Freshmen, Juniors,
Sophomores, and Seniors. Use the minus signs to hide the names. Only the five
subtotal lines should be showing. Format the numbers as currency. Print the
report.
Click
in the table. Click on the Subtotal icon. Click on Remove All.
Report 3
Create a pivot table report showing Average Gasoline Sales for Junior and
Senior Women Marketing Majors.
·
Add Marketing to
the row labels. Add M/F
top the report filter.
·
Add Class to the
column labels. Add
Gasoline Sales to values.
In the Pivot Table Field List,
find the Values box in the lower right corner. Click on Sum
of Gasoline. Choose Value Field Settings. Change the setting to Average.
Click OK.
You
should have two rows of data plus a totals row. Format the numbers as currency.
Print
the report.
Staple
the reports together. Write your name and section number on the top report.