EXCEL WORKSHOP: CREATING TABLES
OBJECTIVES
- Create and manage tables
- Manage table styles and options
- Filter and sort tables
- Pivot tables
An Excel table has functionality beyond that of a simple data range, including the ability to:
- Sort and filter columns
- Automatically apply formatting
- Quickly insert column totals or other mathematical results
PRACTICE TASKS
Open the workbook. On the Sales worksheet, do the following:
1. Convert the data range A2:M23 to a table that includes a header row and uses the default
table style.
- Click anywhere in the data range
- On the insert tab, in the Tables group, click Table.
- Verify that the cell range in the Where is the data for your table box is the cell range
you want to convert to a table.
- In the Create Table dialog box, click OK.
2. Move the July column so that it is between the June and August columns.
3. Move the Linda, Max, and Nancy rows at one time so that they are between the Kay and
Olivia rows.
4. Add a row to the table for a salesperson named Ryan, between the Quentin and Steve rows.
5. Add a row to the end of the table for a salesperson named William.
6. Add a column named Dec to the right end of the table.
7. Delete column M from the table.
➤ Display the Complete Sales worksheet, and do the following:
8. Add a total row to the table.
● clicking in any cell displays a list of functions for processing the numeric contents of
the table column. These include Average, Count, Count Numbers, Max, Min, Sum,
StdDev, Var
9. Modify the cells in the row to calculate the average sales for each month and for the year
➤ Display the Products worksheet and do the following:
10. Sort the data in ascending order by category and, within each category, by unit price.
11. Sort the data in descending order by category and, within each category, alphabetically by
product name.
12. Remove duplicates so that there is only one entry for each supplier.
13. Apply a filter to display only unit prices less than $20.
REFERENCING CELLS
To relatively reference the contents of a cell
● Enter the column letter followed by the row number, like this: A1
To absolutely reference the contents of a cell
● Precede the column letter and row number by dollar signs, like this: $A$1
● Enter the relative reference, click in or select the reference, and then press F4
A mixed reference refers absolutely to either the column or row and relatively to the other
● The mixed reference A$1 always refers to row 1, and $A1 always refers to column A.
PRACTICE
➤ Display the Multiplication Table worksheet and do the following:
● create a formula in cells B2:T20 to complete the multiplication table of the numbers 1
through 20.
PIVOT TABLES
Part 1 – Genre Popularity
1. Select range A1-J17.
2. Insert, Pivot Table (leftmost selection on the Insert tab on the Ribbon).
3. The range should be filled in, if not select the range A1 – J17. Select New Worksheet for the
Pivot table and hit OK.
4. Drag Genre to Row Labels box (this will be done on right hand side of screen).
5. Drag each of the Regions to Values box. Do this in the order North, South, Midwest, West.
6. Select spreadsheet range A3-E7.
7. Insert a 2D Clustered Column Chart.
8. Close the Pivot Table Field List window by clicking the x in the upper right corner.
9. Rename the sheet (bottom left hand corner) to GenreRegion.
10. Save the workbook.
11. Answer the following in the Questions sheet:
a. For each of the genres, which region are they the most popular in?
i. Blues
ii. Country
iii. Indie
Part 2 – Best Selling Release Year
1. Go to the Recording Data tab (RecordingsRaw).
2. Select range A1-J17.
3. Insert, Pivot Table (leftmost selection on the Insert Ribbon)
4. The range should be filled in. Select New Worksheet for the Pivot table and hit OK.
5. Drag Release to Row Labels box.
6. Drag CDs to Values box.
7. Eliminate 1974 by selecting the filter symbol next to the Row Labels heading (at about cell
A4) and clicking on 1974 in the years list. Then click OK.
8. Select range A5-B12 and insert a 2D Pie chart, then choose Chart Layout 1 on the Design tab on the
Ribbon. Change the chart title to Total Sales.
9. Reposition the chart so it doesn’t overlap the data.
10. The spreadsheet should look as follows:
How to become an Excel specialist:
https://www.microsoft.com/en-us/learning/exam-77-727.aspx
This exam measures your ability to accomplish the technical tasks listed below:
- Create and manage worksheets and workbooks
- Manage data cells and ranges
- Create tables
- Perform operations with formulas and functions
- Create charts and objects
