ACCTG 333

Excel AssignmentACCTG 333 – Professor Perols
Assignment Overview
The exercises below are designed to increase your effectiveness and efficiency in using Excel to
analyze data. Excel is widely used in accounting and students need to master Excel (AICPA
2022). This is an individual assignment to be completed on your computer (PC or Mac,
although a PC is preferred). You are allowed to discuss the assignment with others in the class,
but you may not copy. When you have completed the assignment, you will upload your Excel
Workbook file to Canvas under “Excel Assignment Submission.”
To complete these exercises, consult online help such as https://support.office.com/enus/excel and https://exceljet.net/ and the help feature within Excel (F1). Keywords for each
exercise are provided to assist you. Note that if the keyword for an exercise states for example:
OR() then your answer must use the OR() function. Also, your formula in one exercise cannot
cell reference your answer from a previous exercise.
Getting Started
 Download the Northwind Excel Workbook from Canvas. Save the file to your computer and
rename it as your first initial and lastname, e.g., rperols.xlsx.

If you are a Mac user (and do not have access to a PC), stop and check for software updates
before proceeding. Help -> Check for Updates. Run/install updates before proceeding.

Unless stated in the instructions, no additional cells/data/formulas should be added beyond
the yellow highlighted cells in the Northwind Excel Workbook.
Practice Shortcut Keys
 First spend time practicing the shortcut keys below. Make sure to use these shortcut keys
whenever you work in Excel (many of them also work in other applications). Learning these
(and other) shortcuts will save you a lot of time and are more or less necessary for you to be
efficient when you work. For macOS users, most of these shortcuts work with the
command button instead of CTRL. In the future, if you are provided a Windows based PC
for in your internships and/or job, I highly recommend all students practice these shortcuts
on a Windows based PC in order to be efficient (an important skill).
o F1 Displays the Help task pane.
o CTRL+A Selects the entire table

Use CRTL+A+A selects the entire worksheet.
o Ctrl+F Opens the ‘find text’ dialog box.
o CTRL+S Saves the active file with its current file name, location, and file format.
o CTRL+C Copies the selected cells.
o CTRL+X Cuts the selected cells.
o CTRL+V Inserts the contents of the Clipboard at the insertion point and replaces any
selection. Available only after you cut or copied an object, text, or cell content.
Page 1 of 6
Excel Assignment
ACCTG 333 – Professor Perols
o CTRL+ALT+V displays the Paste Special dialog box. Available only after you have cut
or copied an object, text, or cell content on a worksheet or in another program.
o CTRL+Y Repeats the last command or action, if possible.
o CTRL+Z Uses the Undo command to reverse the last command or to delete the last
entry you typed.
o CTRL+ARROW KEY moves to the edge of the current data region (data region: a
range of cells that contains data and that is bounded by empty cells or datasheet
borders) in a worksheet.
o CTRL+SHIFT+ARROW KEY extends the selection of cells to the last nonblank cell in
the same column or row as the active cell, or if the next cell is blank, extends the
selection to the next nonblank cell.
o Shift + Spacebar Selects entire row.
o Ctrl + Spacebar Selects entire column.
o CTRL+HOME moves to the beginning of a worksheet (CTRL+SHIFT+HOME extends
the selection of cells to the beginning of the worksheet).
o CTRL+END moves to the last cell on a worksheet, in the lowest used row of the
rightmost used column (CTRL+SHIFT+END extends the selection of cells to the last
used cell on the worksheet).
o CTRL+PAGE DOWN moves to the next sheet in a workbook.
o CTRL+PAGE UP moves to the previous sheet in a workbook.
Exercises
1. First, practice a little formatting. Format the OrderDetails worksheet by formatting the
header row using top and bottom (but not side) borders, light grey fill color (the exact shade
of grey is not important), and bold and centered text. Also format the data using
appropriate data type format (determine what is “appropriate” by examining the content of
each column, e.g., Discount should be percentages and UnitPrice should be currency).
Expand all the columns so that all the data are visible (select the entire worksheet, i.e., use
CTRL+A+A, and double click on a line between two Excel Worksheet Column Headers, e.g.,
between A and B above the table headers. You do not need to do more formatting for the
assignment beyond this brief practice in exercise 1.
2. Before getting started on the formulas, take some time to understand the data. For
example, note that workbook contains two files, OrderHeaders and the OrderDetails, which
contain archive transaction information. Each row in the OrderHeaders table represents a
distinct order. Each order can, however, have many rows in the OrderDetails table because
a given order can be for multiple items. Each row in the OrderDetails table is associate with
a specific order header row and a specific item. Each row also shows the quantity, unit
price, and discount for the sale of the item. Remember that in the Systems Understanding
Page 2 of 6
Excel Assignment
ACCTG 333 – Professor Perols
Aid (SUA) assignment, the top part of orders contained OrderID, date, customer
information, and supplier information while the bottom part contained rows with
information about the actual items sold (this structure of storing the header portion and the
detail portion is very common for different accounting objects, e.g., purchase orders,
receiving reports, invoices, sales orders, and sales invoices). Also note that the other tables
contain master tables, e.g., Suppliers, Customers, and Products, where each row contains
information related to a single Supplier, Customer, Product, etc. No action/answer is
required for exercise 2 (just an overall understanding of the data).
3. Cell References and Calculations – In the OrderDetails worksheet, in cell F2, calculate the
LineItemTotal as UnitPrice * Quantity * (1-Discount) using cell references. Format this cell
as currency with two decimal places and copy down cell F2 to the bottom of the table, i.e.,
F2 through F2156, using short cut keys: CTRL+C, ARROW LEFT, CTRL+ARROW DOWN,
ARROW RIGHT, CTRL+SHIFT+ARROW UP, CTRL+V. Note that this is a very common
sequence of commands that you want to learn (do not memorize it, just practice it
throughout this assignment when you need to copy down a formula).
4. Absolute and Relative Cell References – In the OrderDetails worksheet, in the yellow
highlighted cell I2, enter 5%. In cell G2 calculate the LineItemTotal with Additional Discount
as UnitPrice * Quantity * (1-Discount-Additional Discount) where the additional discount is
held constant at the value in cell I2. Note that you need to use both relative and absolute
cell references for this formula to work correctly. There is also a shortcut (F4) for applying
absolute cell references. Copy down the formula in cell G2 to the bottom of the table, i.e.,
G2 through G2156, using short cut keys (for the rest of the assignment, assume that you
should copy down formulas if the formulas relate to each row in a table).
5. VLOOKUP() – First, in the OrderHeaders worksheet, in column C use a vlookup to display the
company name from the Customer worksheet for each order. Please see the short video I
posted on Canvas to help you through the first part of this exercise.
Second, in the Products worksheet, in Column D use a vlookup to display the country from
the Suppliers worksheet for each supplier.
6. COUNT(), AVG(), SUM(), MAX(), and MIN() – In the OrderDetails worksheet, in cells B2159,
C2159, D2159, E2159, and F2159 calculate the Total Number of Line Items (number of line
item rows), Average UnitPrice (average of unit price in column C) Total Quantity (sum of all
line item quantities in column D), HighestDiscount (maximum of all discounts in column E),
and Smallest LineItemTotal (minimum of all LiteItemTotals in column F).
7. COUNTIF(), AVERAGEIF(), and SUMIF() – In the OrderDetails worksheet, in cell A2162 enter
51. In cells B2162, C2162, and D2162 use COUNTIF() to calculate Number of Product 51
Sales, AVERAGEIF() to calculate Average Unit Price of Product 51, and SUMIF() to calculate
the Quantity Sold of Product 51 (use a cell reference in your formulas to the value in A2162
so that your count, average, and sum updates when the value in A2162 changes).
Also, in the Employees worksheet, in column M use SUMIF to show Total Sales (column O in
OrderHeaders) for each employee ID. Note each employee is associated with many orders.
Page 3 of 6
Excel Assignment
ACCTG 333 – Professor Perols
8. IF() – In the OrderDetails worksheet, in column K create an if statement that returns “Yes” if
the Quantity (values in column D) is above 40 (strictly greater than) and otherwise “No”.
9. AND() and OR() – In the OrderDetails worksheet, in columns L and M use IF statements with
an AND() and OR(), respectively, to return “Yes” if Quantity is between 30 and 40 (strictly
greater than 30 and equal to or less than 40), and otherwise “No”. The OR() requires more
thinking (see tips in the Check Figures document).
10. Nested IF() – In the OrderDetails worksheet, in column N use a nested IF statement to return
“Yes” if Quantity is between 30 and 40 (strictly greater than 30 and equal to or less than
40), and otherwise “No” (see tips in the Check Figures document).
11. Missing values – In the OrderHeaders worksheet, in column Q create an if statement that
returns “Yes” if the ShippedDate is missing (indicated inside the if statement as an empty
string, i.e., two quotation marks in a row without whitespace), and otherwise “No”.
12. Comparing Existing Dates – In the OrderHeaders worksheet, in column R create an IF
statement that returns “Yes” if the ShippedDate is on or after the RequiredDate, otherwise
“No”. Note that a date in Excel is stored as a number representing the number of days since
1/1/1900. The number is simply formatted to display as a date. Because of this you can
directly compare two dates that are already in an Excel worksheet (you are really comparing
two regular numbers). To demonstrate, change the formatting of the value in G2 to a
number. Notice that the cell is actually storing 42762 (the number of days between
1/27/2017 and 1/1/1900). Also note that the formatting will not change your answer for
exercise 12.
13. YEAR() and MONTH() – The YEAR() and MONTH() functions are used to get the year or
month of a date stored in Excel. In the OrderHeaders worksheet, in columns S and T use
YEAR() and MONTH() to return the OrderDate year and month, respectively. In column U,
use YEAR() inside an IF statement to return “Yes” if the OrderDate is in 2018 and otherwise
“No”. In column V, use YEAR(), MONTH(), and AND() inside an IF() statement to return
“Yes” if the OrderDate is in the first quarter of 2018, and otherwise “No”.
14. DATE() – It is not as easy to compare a date already in Excel to a date that we want to
specify. If we type in 1/12/2019, Excel will interpret this as 1 divided by 12 divided by 2019
and to compare 1/12/2019 to other dates in Excel we therefore first need to convert the
date to a number that represents the number of days since 1/1/2019. This can be done
using DATE().
In the OrderHeaders worksheet, in cell AB2 simply use DATE() or DATEVALUE() to find out
how many days there have been since 1/1/1900 and the due date of this assignment
11/30/2023. Change the cell formatting to a number if the results from DATE() are
formatted as a date.
Note that the function DATEVALUE() works the same way as DATE(), but converts a date
string, e.g., “11/2/2019”, rather than integers separated by commas, e.g., 2019,11,2, to the
number of days since 1/1/1900.
Page 4 of 6
Excel Assignment
ACCTG 333 – Professor Perols
In column W use DATE() and AND() inside an if statement to return “Yes” if the OrderDate is
in the first quarter of 2018, and otherwise “No”.
In column X, use DATE() only inside an if statement to return “Yes” if the order date is after
(strictly greater than) 10/15/2018, and otherwise “No”.
In column Y, use AND() and DATE() inside an if statement to return “Yes” if the order date is
before (strictly less than) 11/18/2018 and has not yet been shipped, and otherwise “No”.
15. SUMIFS – In the Employees worksheet, in column N use SUMIFS (not SUMIF) to show:
Total Sales (column O in OrderHeaders) for each employee but only include orders that
have been shipped (column G in OrderHeaders) in this calclation. Note that an order has
been shipped if ShippedDate is not empty, indicated using “” (note that there is nothing
to the right of as blank in Excel is indicated by nothing.
16. LEFT(), RIGHT(), FIND(), and LEN() – In the Customers worksheet, in columns O, P, Q, R, and S
use LEFT() to find the first eight characters of ContactName, use RIGHT() to find the last nine
characters of ContactName, FIND() to find the number of characters before space in
ContactName (you need to use FIND()-1 for this), LEN() to find the number of characters in
Contact Name, and LEN() and FIND() to find the number of characters after the space in
Contact Name.
In column D use LEFT() and FIND() to show the first name of the customer contact (shown in
column C). Note that you need to embed the FIND() inside the LEFT() functions and use
FIND to return the location of the character that separates the first name and the last name.
In column E, use RIGHT(), LEN(), and FIND() to show the last name of the customer contact
(and you again need to embed formulas)
Note that when embedding a formula inside another formula the behavior of the nested
formula does not change, e.g., FIND() finds the first occurrence of one string inside another
string and always searches from the left even when embedded within RIGHT().
Pivot Tables
Complete exercises 17-20 before comparing your answer to the Check Figures document.
17. Creating Pivot Tables
a. Use the Sales worksheet data to create a Pivot Table into a new worksheet. Name the
new worksheet SalesPivot. For Mac users especially, do not create the pivot table by
selecting the worksheet (data). Instead, from the Sales worksheet, simply Insert -> Pivot
Table and the data will be automatically selected.
b. Click and Drag ProductName and CustomerName to Rows, OrderDate to Columns (year
and quarter will also be added), and LineItemTotal to Values. For Mac users especially,
“drag” OrderDate to Columns to ensure that year and quarter will also be added.
c. Rearrange the Rows fields to show all customers and the products that they have
purchased (rather than all products and the customers that purchased those products).
Page 5 of 6
Excel Assignment
ACCTG 333 – Professor Perols
d. In the Column Labels, use the + button to expand the pivot table show Year and then
Quarter. If the + button is not available go to Pivot Tables tab -> Analyze -> Show -> +/Buttons. Note that the Pivot Table now groups the sales data based on CustomerName,
ProuctName, and the quarter of the OrderDate and then sums LineItemTotal.
18. Other Aggregate Functions in Pivot Tables
a. Use the – button to collapse the pivot table details back to the annual level (view the
data grouped by year rather than quarter).
b. Add Discount to Values. Calculate average discount (rather than sum) and change the
format to percentage with one decimal. To do this, use Value Field Settings (accessed
by double clicking or right clicking on the Sum of Discount header, or by opening the
drop down menu for the Sum of Discount in the Values field selector).
c. Add OrderID to Values and count how many line items are being grouped. Inside Value
Field Settings, change the format to number with zero decimals.
d. Inside Value Field Settings, change the format of Sum of LineItemTotal to currency with
zero decimals.
19. Formatting Pivot Tables
a. In Design  Subtotals, select to not show subtotals and Grandtotals (turn off for both
rows and columns).
b. In Design  Report Layout, select Show in Tabular Form
c. In Design  Report Layout, select Repeat All Item Labels
d. Change the name of the columns headers (you can make these changes directly in the
column headers or in Field Settings) to Customer Name, Product Name, Average
Discount, Number of Order Lines. (Note: do not change Sum of LineItemTotal).
e. Replace all empty cells with 0 (right click inside the pivot table, select Pivot Table
Options and set “For empty cells show:” to 0.
20. Filtering and Slicing Pivot Tables
a. Filter customer names to only show customers that begin with B by left clicking the filter
icon to show the filter drop down menu (the little triangle in the column header in the
same cell as the text Customer Name), selecting Labels Filters, and Begins With.
b. Filter product names to only show products that begin with letters between O-Z using
Labels Filters  Between…
c. Insert a Slicer using OrderDate and select Feb, May, and Aug.
21. Obtaining Details from Pivot Tables
a. For Customer Name and Product Name: Bon app’ and Pavlova, double left click the
Number of Order Lines for 2018 (double left click on the 2). Note that the details of the
two order lines will display in a new worksheet.
b. Change the new worksheet name to Bon App Pavlova 2018 Details.
Page 6 of 6