Dollar Plan Assignment

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 1 of 6

WEEK 3: DATA CLEANING &

ORGANIZING

Learning Objectives

By the end of this week, you should be able to:

• (Core) Structure datasets appropriately in Excel.

• (Core) Remove unwanted blank spaces within a data range.

• (Core) Rearrange rows and columns using the Transpose feature.

• (Core) Sort or filter data in a range.

Activity 1: Plan Data Structure

Activity

Download Lululemon’s annual income statement sheet from Canvas. Observe the raw file and think about

the following questions:

• Where are the elements of a skeletal income statement (NS, COGS, GM, OE, P/L)? Are they in rows or columns?

• What other supplemental information needs to be included? Are they in rows or in columns? Now you are going to make a report to present Lululemon’s key performances in the past ten years. You

will need to place key information on a table and make a chart. Now comes the new sets of questions:

• How would you organize your table? Do you place the skeletal P/L elements in rows or columns?

• How do you make a table from the raw data? To avoid irreversible errors that damage the raw data, you are recommended to start with a new Excel file

and paste the raw datasheet into the new file.

Knowledge Base

Types of Scale

Recall that Excel supports many types of data in cells, among which three fundamental ones are labels,

values, and formulas (Week 1). In market research and many other fields, data can be further divided into

two major sub-categories: qualitative and quantitative data.

• Qualitative Data: it is also called “categorical” because data that fall into this category is usually textual and descriptive.

o Nominal: nominal data measures type or classification. Sex, ethnicity, major, and party affiliation are the commonly seen nominal data types in questionnaires.

o Ordinal: ordinal data also uses labels to classify cases but places them into orders or ranks. For example, a five-star movie is rated better than a four-star movie. In this case,

movie rating is an ordinal data type.

• Quantitative: quantitative data deals with numbers. o Integer: integer (or discrete) data uses whole numbers that cannot be divided. For

example, a dog usually has four legs, but even with only three legs, it is still a dog, not ¾

of a dog.

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 2 of 6

o Continuous: continuous data uses numbers that can be measured on an infinite scale. For example, a more accurate weight measure can be 46.26782 lbs with many decimal places.

Data Structure in a Table

A two-dimensional table consists of rows and columns. Recall that in Excel, rows are labeled by

numbers (e.g., Row 1) while columns are labeled by letters (e.g., Column A). To take full advantage of

Excel, you may need to plan your data analyzing project by starting from a proper data structure.

• For a flat structured dataset, a column has the same meaning in every row. For example, if Column A contains a customer’s first name in Row 1, the column must contain a customer’s first

name in all rows. Also, it does not matter which column comes first, which is the next, and which

is the last. For example, while customer’s first names can be placed in Column A, they can be

placed in Column B, C, or D without changing the meaning. The same is true of rows. A

customer’s information can be stored in any row.

• Excel can also display summarized data in a tabular format or as a report. In these cases, you may see hidden, repeated, summarized, or reinterpreted data as a result of a higher-level analysis.

Some general rules for data organization are:

• Organize variables (or attributes) in columns and cases (or observations) in rows (see Activity 3). o Examples of variables include height, gender, sales, profit, contact number, etc. o Examples of cases include a person, a corporation, an entry of customer review, etc.

• Avoid blank rows and columns in a range (see Activity 2).

• Position critical data (e.g., the total line) above or below the range. Avoid placing critical data to the right or left or the range because the data might be hidden when filters are applied.

Activity 2: Delete Blank Spaces

Activity

Blank cells that do not carry information could be burdensome and hinder further analysis. Continuing

with the Lululemon case, the first step to clean the data is to remove blank columns between yearly data.

• One way to achieve this cleaning procedure is to manually select multiple columns and use the right-click delete menu to delete all selected columns (see Figure 1).

• Another way is to use the Go To Special menu and ask Excel to select all blank columns automatically.

Figure 1 Delete Menu

Knowledge Base

The manual way of selecting all blank areas and deleting them is time-consuming and prone to errors (not

to waste hours doing something manually). There is an alternative method in Excel that allows to

complete the task with ease. The Go To Special menu is a handy tool that allows users to find various

items on the worksheet.

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 3 of 6

Windows Users

1. Select the range of cells for the search. Note that if one cell is selected, Excel searches the whole worksheet, while when a range of cells are selected, Excel searches within the designated range.

2. Click the Find & Select button from the Home ribbon, select Go To Special. The Go To Special dialog box (see Figure 2) shall appear.

3. Select the option “Blanks” and click OK. Excel will select all blank cells within the range. 4. Use the right-click menu to open the Delete dialogue box. Delete rows or columns as needed.

Figure 2. Go To Special Menu & Dialog Box

Mac Users

If the magnifying glass icon (or a binocular icon) is not visible with your version of Excel, try the

workaround way:

1. Click the Edit button from the top menu bar (see Figure 3). 2. On the pop-out window, click “Special” (see Figure 3). 3. The “Go to Special” dialogue window shall appear.

Figure 3. Go To Special Option on Mac

Activity 3: Transpose Data

Activity

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 4 of 6

The next step is to reorganize the datasheet to have all variables in columns and cases in rows. Rotating

rows and columns is completed by the TRANSPOSE feature in Excel with just a click.

1. Create a new sheet “PL Statement” to place the rotated data. 2. Select and copy the entire data table from the raw sheet. 3. Use Paste Transpose to paste the data to the new sheet.

Figure 4 Transpose using the Right-Click & Paste Special Menu

Knowledge Base

You can either use the TRANSPOSE function1 or simply have it done by copying, pasting, and using the

Transpose Paste option from the right-click menu.

• If your original data contains formulas, Excel automatically updates them to match the new placement. Be aware of the relative (vs. absolute) references in the formulas.

• If you need to rotate your data frequently, consider creating a Pivot Table (will be introduced later in the semester)

Transpose Cell Content

1. Select the cell range you want to rearrange, including the row or column labels. Copy the data

(CTRL+C for Windows and CMD + C for macOS).

2. Choose a new location where you want to paste the transposed data. Make sure that there is plenty of room for the new table.

3. Right-click over the top-left of where you want to paste the new table, then choose Transpose from the PASTE OPTIONS list.

4. After rotating the data successfully, the original table can be deleted and the data in the new table will remain intact.

Resources

• Transpose (rotate) data from rows to columns or vice versa: https://support.microsoft.com/en- us/office/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-

d0f862209744

Activity 4: Data Sorting

1 Type “=TRANSPOSE(the data range)” and hit CTRL+SHIFT+ENTER.

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 5 of 6

Activity

Observing the new datasheet created in Activity 3 (the sheet that has variables in columns and years in

rows), you may notice that the earlier years are displayed at the bottom of the table while the most recent

years are displayed at the top. As you may add more content to the table in the future, reorganize the

datasheet from the earliest record to the most recent becomes especially useful. The task for this activity

is to sort the table ascendingly based on time (year).

1. Format the first column (Report Date) to make sure it is recognized as Time by Excel. Format the rest of the table and show all dollar figures as “Currency.”

2. Select the entire datasheet, including headers and numbers. 3. Open the Sort dialogue box to sort rows (top to bottom) by the first column (Report Date).

Knowledge Base

In Excel, there are some very basic ways to sort data in a range. The Data tab has several buttons that can

perform sorting task. The most commonly used one is the Sort button within the Sort and Filter group.

1. Select a cell range to sort. 2. On the Data tab, in the Sort & Filter group, click on Sort. 3. The Sort opens a Sort dialog box that allows more customization options:

• If the data range contains headers, check the “My data has headers” option.

• The Options dialog box allows you to choose between rows and columns to sort. The “Sort top to bottom” option allows rows reorganization while the “Sort left to right” option does

column reorganization.

• Under the Column menu, choose the column you want to sort by. When your cell range contains headers, the dropdown menu shows column headers. Otherwise, it displays the

column number (e.g., Column A).

• Under the Sort On menu, you may choose to sort by cell values or colors.

• Under the Order menu, the A-Z option sorts data from the lowest to the highest while the Z- A option does the opposite. Customized list is also possible if you would like to add a list ot

sort criterion.

• If you would like to add multiple sort criteria, use the “Add level” and “Delete level” buttons.

Resources

Sort data in a range or table: https://lef.microsoft.com/en-us/office/sort-data-in-a-range-or-table-

62d0b95d-2a90-4610-a6ae-2e545c4a4654#ID0EAADAAA=Web

Activity 5: Data Filtering

Activity

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Page 6 of 6

Add a filter button to the datasheet to allow more flexibility in sorting and filtering.

1. Choose the entire data range to be manipulated. 2. On the Data tab, in the Sort & Filter group, click on Filter. 3. Use the dropdown arrow in the header row for filtering and sorting.

Knowledge Base

Filter (also Sort) Data in a Table

The Filter button is a great tool to filter, sort, and/or temporarily hide some data in a table.

1. Select the data range or any cell in the range. 2. From the Data tab, select the Filter button. 3. There will be a tiny dropdown arrow in the header cell for each column in the range. 4. Click on the column header arrow for the column you want to filter or sort. 5. Choose one sort command to sort or check/uncheck boxes to filter data. 6. Click the OK button to confirm.

Supplemental Reading & Video

Filter in Excel: Tutorial from Microsoft