1.1.
Loading workbooks
Before we dive into Excel, we first need to learn how to open existing workbooks. If
you lose progress or get stuck at any point in this course, you can load the
corresponding spreadsheet to re-initialize.
Instructions
Open the file 1_1_loading_workbooks.xlsx from the Workbooks folder on your
local computer.
On the worksheet Google Monthly Stock Price, select all values in column A.
How many rows are contained in column A including the
header?
• 121
• 120
• 5184804
• 43206.7
1.2. Working with data
You know how to open an existing workbook and highlight values in a column, in this
exercise we're going to add data into our spreadsheet manually and by
copying/pasting data from another Excel file.
Instructions
Create two new worksheets in our workbook, "Manual Input" and "Pasted Data".
Reminder: To rename your sheet, double-click it and enter your new description.
Navigate to the Manual Input sheet, and add the data from the table below. This
should span three columns and four rows. We'd recommend starting from cell A1.
Name Location Orders Placed
Alexis Belgium 52
Ciaran Ireland 17
Miles Canada 37
• Navigate to the other sheet you created earlier, Pasted Data.
• Open the file copying_data.xlsx from the Datasets folder. Do not close
any Excel workbook.
Copy all data (columns A to K) from the copying_data.xlsx file and paste as
values into your workbook.
The data looks messy; let's do some formatting to make the data easier to read.
• Set the column width for all columns to 15.
• Ensure all column headers begin with a capital letter and the rest of the column
name is lowercase.
What was the runtime for The Shawshank Redemption in minutes?
1.3. Creating first formula
Good job on familiarising yourself with Excel's user interface, you've even learned how
to add data to your workbook!
A common use case for Excel is to carry out mathematical operations, so in this
exercise, we'll be carrying some basic arithmetic operations on the Google Stock Price
data in our workbook.
Instructions
Navigate to the Google Monthly Stock Price sheet.
• Create a new column next to Volume with the header name "Open vs Close".
• On the next row, subtract the value in the Open column from
the Close column.
Copy the formula you created in the previous step to all rows in the sheet (to row 121).
Highlight all values in the column Open vs Close.
What was the average difference between the close and open price, rounded to
two decimal places?
1.4. Working with structured cell
references
Adventure Works is a global retail brand that sells bikes, bike parts, and accessories.
Your role is to work as an Analyst to support their management teams understanding
of their sales data.
In this exercise, we'll be working with their Sales table which contains all orders from
2017. We'll start by creating a new column that uses structured cell references to
perform a calculation.
Instructions
Open 1_4_working_with_structured_references.xlsx from the Workbooks
folder on your local computer.
Create a new column "OrderToDelivery"
between DeliveryDueDate and CustomerName columns.
Write a formula that calculates the difference
between OrderDate and DeliveryDate for each row.
Use a structured cell reference (column name instead of cell reference). You can
refer to a column name using the @ symbol.
That doesn't look quite right! It's defaulted to a date… let's change it by changing the
data type to "Number".
How many days on average does it take for Adventure Works to deliver once a
customer has placed an order? Round to the nearest whole number.
1.5. Filtering data
Look how far you've come in Excel! You can now create formulas, calculated columns
and utilise structured cell references. These concepts will become very useful
throughout the course.
In this exercise, we'll filter and sort our data in various ways to help Adventure Works
better understand its sales in a given period.
Instructions
Filter the table in the Sales worksheet to display orders from September 2017 only.
Now it's time to sort your orders by two columns in the following priority:
1. CustomerCountry (A-Z)
2. OrderDate (Oldest to Newest)
What is the order number for the first visible row in the table
after the data has been filtered and sorted? Include the `SO`
in your submission.
• SO44321
• SO44480
• SO44323
• SO44480
1.6. LAST STEP INSTRUCTIONS
Formatting tables
Now that we've familiarized ourselves with our data, we will apply rules to our columns
for data integrity. By default, most of our columns will default to the format "General".
In this exercise, we'll apply some column formatting.
Instructions
• Remove any previous filters applied from the last exercise, we want to see all
orders.
• Change the sort to only sort by OrderNo ascending.
Format the following columns as Currency: ItemCost and ItemPrice.
Format the following columns as Number with 0 decimal
places: SalesOrderLineKey, OrderQuantity, and OrderToDelivery.
Format all other columns as Text.
How many columns are formatted as text in our Sales table?
• 2
• 10
• 3
2.1. Naming ranges
Naming specific cell ranges within your data can be extremely useful. If there is a
particular column or row of information that you will use regularly for formulas or
calculations throughout your workbook, it can definitely be easier and save time if you
have given that particular range an easy reference to use.
We've reverted our dataset to before we applied our table styles, we'll now add some
names to some important ranges within our data.
Instructions
Open 2_1_naming_ranges.xlsx from the Workbooks folder.
Numerical variables, like ItemPrice, are more likely to be used in future calculations
and formulas.
• Select cells with values in them from the ItemPrice column.
Name the range "item_price" by using the _Define Name_ feature in Excel.
Make sure that the name refers to the data from cell E$2 to E$1202. This will ensure
the header isn't included in the range.
Repeat the range naming process for the OrderQuantity column, using the the
name "quantity". The range you want to be naming is C2:C1202.
• In cell R1, create a new header called "OrderLinePrice".
• In cell R2, create a new formula that multiplies our two
ranges: item_price and quantity.
What is the average Order Line price across the entire dataset? Round to 2
decimal places.
2.2. Sub-totals
Carrying out quick calculations of numerical values within our dataset is a great way
to sense-check that things are in order and ready for much deeper analysis later on.
We don't even need to start creating formulas by hand - Excel provides us with
features that make it easy for us to check our data.
The management team would like to understand whether there is a significant
difference between each country's order to the delivery period. In this exercise, we'll
use Sub-Totals to work this out.
Instructions
Remove the OrderLinePrice column you created in the previous exercise.
Duplicate the worksheet and rename it "Sales Subtotals".
We want to use the Subtotal feature in Excel, but before we do this we will need to
sort our data.
• Add filter options to each column.
• Sort the CustomerCountry by Ascending (A to Z) order.
Using the Subtotal feature, we'll create a new subtotal row based on the following
requirements:
• At each change in CustomerCountry
• Use function Average
• Add subtotal to: OrderToDelivery
• Include Summary below data
On the left-hand side of the page we should see the numbers 1,2,3 next to each other.
This indicates that some new groupings have been added to the page and shows you
the levels you can expand the dataset by.
• If you click on the 2 it should collapse the data so that we only see the average
order to delivery time for each country.
What is the average order to delivery time for customers in Germany?
2.3. Custom formats and validation
Excel allows users to format the data within sheets in many different ways. It even
allows us to create our own custom formats if we can't find the type we want from
Excels prepared formats. As well as formatting the data, we can also ensure the
integrity of the data within columns and rows. This helps when sharing workbooks with
others, or when required to enter new data that might be needed into existing columns
or rows.
Instructions
• We no longer require our Sales Subtotals worksheet, so delete this from
your workbook.
• Format all the data in your Sales worksheet as a table.
In our sheet, we have two columns that represent monetary values. But they are not
formatted this way. Let's fix that.
• Highlight the ItemCost column and navigate to the custom format window.
We'd like our ItemCost value to be formatted with a thousand seperator, zero
decimal places followed by a dollar sign. For example, something like 1,000 $.
• Go to the Custom section within the custom format window.
• In the Type section, set the custom format to "#,### $"
Great job! Now format the ItemPrice column in the same layout.
In the future, we might share this file with others who may add new roles. Let's add
some validation to ensure that OrderQuantity has a minimum value of 1.
• Select the column OrderQuantity and navigate to the Data
Validation settings menu
Using the Data Validation feature, create the following criteria for
the OrderQuantity column:
• Only allow whole numbers greater than the value 0.
• Display an error message that informs the user if the value they entered does
not meet the column criteria.
If you entered the value "0" in cell C1203, would you get an error message - Yes
or No?
2.4. Summarizing sales
By now you'll be familiar with the general structure of a formula and will have the
capabilities to perform basic arithmetic operations.
In this exercise, we'll start working with aggregate functions and summarize key
metrics on our data including the minimum, maximum and average price of our items.
Instructions
Write the following list of descriptions in the following cells:
• S2: "Lowest Item Price"
• S3: "Highest Item Price"
• S4: "Average Item Price"
• S5: "Total Sales"
• S6: "Total Profit"
• S7: "Profit Margin Ratio"
In cell T2, calculate the lowest item price across all sales.
In cell T3, calculate the highest item price across all sales.
In cell T4, calculate the average item price across all sales.
Format all three cells you've created to have the dollar currency to 2 decimal places.
What is the average item price for all sales at Adventure
Works?
• $3,216.59
• $3,578.27
• $669.10
2.5. Calculating sales and profit
In the previous exercise, we calculated some basic statistics about our data - good
job!
Now, we're looking to summarize our total sales and cost to calculate our overall profit
and profit margin ratio.
We'll be using a mix of aggregate function and arithmetic to achieve our desired result!
The output will enable Adventure Works to better understand their total profit and profit
margin.
Instructions
• In cell T5, calculate the total sales.
• Format with a dollar symbol to 0 decimal places.
Note: Since all our quantities are one, we can assume that ItemPrice will give us all
the information we need to calculate sales.
• In cell T6, calculate the total profit by subtracting the sum of total costs by the
sum of total sales.
• Format with a dollar symbol to 0 decimal places.
Note: Since all our quantities are one, we can assume that ItemCost will give us all
the information we need to calculate sales.
• In cell T7, calculate the profit margin ratio which divides total profit by total
sales.
• Format this value as a percentage to zero decimal places.
What is the profit margin ratio? Answer Format: XX%
3.1. Product profiles
Keep up the good work! Data doesn't always come formatted as we require, so here,
we'll focus on formatting a new column by applying a function to an existing column.
Additionally, we'll create a unique identifier that can be used for future analysis.
Instructions
• Create a new column between L and M called "Country".
• Write a formula that converts the customer country into uppercase.
• Create a new column between A and B called "OrderID".
• Write a formula that takes the last 5 characters from OrderNo.
• Make sure that the column data type is General.
For OrderID: 43698, what is the name of the customer?
• Cole Watson
• Sydney Wright
• Rachael Martinez
3.2. Profit performance
Date functions can be powerful when preparing and transforming data ready for
analysis, so we'll utilize functions such as YEAR() and MONTH() in this exercise.
Instructions
• Create a new column "OrderYear" next to OrderDate that returns the year of
the order.
• Make sure that the column data type is Number and formatted to 0 decimal
places.
• Create a new column "OrderMonth" next to OrderYear that returns the month
of an order.
• Make sure that the column data type is Number and formatted to 0 decimal
places.
What is the average order item cost for orders in September?
3.3. Formatting with functions
Finally, we'll finish this chapter by applying some rounding functions to format our cells
and ensure they are easy to read.
Instructions
Unfilter your data from the previous exercise, so we can see all orders.
Next to the summary statistics we created earlier, apply a round up function to 0 digits
in the cells to the right of the lowest and highest item price (Y2 and Y3).
Apply a standard rounding function to average item price to 1 digit in the cell to the
right (Y4).
What is the lowest item price when rounded up?
3.4. Visualizing categories
The operations team wants to understand which products perform best, so let's
visualize average sales across product categories. Excel has a variety of options to
represent categorical data effectively, so we'll use column or bar charts to convey this
information. These charts are powerful tools, enabling concise and visually impactful
data presentations.
Instructions
• Close any files that you've currently got open.
• Open the workbook called 3_4_visualizing_categories.xlsx from the
Workbooks folder on your local computer.
Navigate to the By ProductModel sheet.
From the Insert tab, create a 2D Column or 2D Bar chart to visualize Average
Sales by Product Model and Product Sub-Category.
Change the title of the chart to "Average Item Price".
Which bike model has the highest average price?
• Mountain-100
• Road-650
• Road-150
3.5. Capturing parts of a whole
The management team wants to explore how their orders vary from country to country
and state to state. We'll utilize a sunburst chart to visualize sub-categories within a
larger category. This powerful chart type allows us to visually represent the
interconnectedness and proportions of our data clearly and concisely. Sunburst charts
are a type of treemap.
Instructions
Navigate to the By Location sheet.
Create a Sunburst chart to
visualize Orders by CustomerCountry and CustomerState.
• Change the title of the chart to "Orders by Country and State".
• Re-size the chart to make it easier to read.
Which state in Australia has had the highest number of orders?
3.6. Capturing a trend
Visualizing data over time can help find trends in sales performance. To achieve this,
we will utilize line charts. However, a crucial step will be editing the data source, which
we'll explore during the exercise. Line charts offer a powerful means to represent
trends and fluctuations, providing valuable insights into sales patterns.
Instructions
Navigate to the By Date sheet.
Create a line chart to visualize Total Sales by OrderYear and OrderMonth. It
may not look right initially, but don't worry - we'll fix that in the next step.
Let's start by opening the Select Data pop-up.
Let's update the data source to:
• Total Sales as the Legend entries (series).
• Order Year and Order Month as the Horizontal (category) axis label. (Do
not select the column headers!)
• Change the title of the chart to "Total sales over time".
• Re-size the chart to make it easier to read.
Which month in 2017 had the highest amount of total sales?
• November
• July
• September