Question
Excel Tables If you can provide directions, pics, formula I'd appreciate it. I have no idea where to begin. The Sales Orders worksheet shows orders.
Excel Tables If you can provide directions, pics, formula I'd appreciate it. I have no idea where to begin.
The Sales Orders worksheet shows orders. Some research needs to be done to locate a group of information.
A. Create a table starting at J2 to look up sales tax. Central sales tax is 7%, East is 8%, West is 9%, and South is 5%. J1 put in text Tax Table, display the tax rates in K column as %
B. Insert a column before column H and call it Sales Tax Table
C. In Sales Tax column do VLOOKUP for the sales tax rate using values from column B. Set VLOOKUP as exact match. Define the range for the vlookup as absolute Page 2 of 2 reference.
D. Update the calculations in column H to include sales tax as part of the calculation
E. Convert the Orders dataset into an Excel Table (remember you only need to select one cell to create excel table)
F. Then, locate the records of the dataset that comply with all the following characteristics: 1. Region is East or West. 2. Rep is Parent.
G. Format the Cost and Total to be currency ($)
H. Copy total sales values column H for rep Parent in the East and West region and paste values into column P. Add a sum at the bottom of the values in column P to show total sales for Parent in the East and West Regions
I. Select columns A thru H and create a named range called Sales
J. Add a Total row and use the properties of the Total Row to calculate:
1. the sum of Total (column H).
2. the sum of units (column E)
K. Apply the Olive Green, Table Style Medium 11 to the table
Pivot Table 1 :
A. Use the SalesOrders worksheet and create a Pivot Table on new worksheet with a filter on Region, row of Rep, column of Item and sum of Units, Sum of Total
B. Filter the information so that only East region is displayed.
C. Format total to display in currency format
D. Which Rep has the most revenue? Answer in cell M1 of this sheet.
E. Rename the PivotTable's worksheet: SalesPivot.
Thats everything from the assignment I appreciate whatever help you can provide. Thank you.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started