Excel 2016 Tips and Tricks

Lesson – Working with Tables

Excel 2016

E XERCISE

W ORKING WITH T ABLES



T ASK Use table features.

1. Open TABLE1EX.XLSX . 2. Format the sales data as a table using a table style of your choice. 3. Change the table name to ProductSales . 4. Filter the table to display only information on the sales rep Terry Caracio . 5. Sort the list by Inv Num with the largest number at the top. 6. Add another filter to show only the Golf Balls and Gloves sold by Terry. 7. Clear all filtering and sorting. 8. Insert a new column in the table between Product and Inv Num . Then delete the new column. 9. Add a calculated column in column G by creating a formula in cell G5 that multiplies the Price Each figure by the Qty Sold figure. Change the column header to Sales . 10. Add emphasized formatting to the first and last columns. 11. Filter to show only sales of Footballs . 12. Display the Total Row. 13. Change the calculation for the Sales total to Max . 14. Add a total for the Qty Sold column to calculate the Average . 15. Clear the filter from the Product column. 16. Scroll as necessary and select cell G67. Press [Tab] and enter the following data:

Column Product

Data

Gloves

Inv Num

4230

Sales Rep

John Carpenter

Date Sold

7/23/2007

Price Each

12

Qty Sold

19

17. Add data validation to the Product column using the ProductList in column I . 18. Add a custom error message to the data validation for the Product column using the Stop style with the title Product Error and the message Select a valid Product from the drop-down list!

Page 64

OFFICEPRO, Inc.