Practice Video: Formulas in Context

Loading...

From the course by Macquarie University

Excel Skills for Business: Essentials

1984 ratings

Macquarie University

Excel Skills for Business: Essentials

1984 ratings

Course 1 of 4 in the Specialization Excel Skills for Business

In this first course of the specialization Excel Skills for Business you will learn the Essentials of Microsoft Excel. Within six weeks, you will learn to expertly navigate the Excel user interface, perform basic calculations with formulas and functions, professionally format spreadsheets, and create visualizations of data through charts and graphs. Whether you are self-taught and want to fill in the gaps for better efficiency and productivity, or whether you have never used Excel before, this course will set you up with a solid foundation to become a confident user and develop more advanced skills in later courses. We have brought together a great teaching team that will be with you every step of the way. A broad range of practice quizzes and challenges will provide great opportunities to build up your skillset. Work through each new challenge with our team and in no time you will surprise yourself with how far you have come. Spreadsheet software is one of the most ubiquitous pieces of software used in workplaces across the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. At a time when digital skills jobs are growing much faster than non-digital jobs, make sure to position yourself ahead of the rest by adding Excel skills to your employment portfolio.

From the lesson

Performing calculations

The syntax of formulas is very important in Excel. In this module, you will get introduced to formulas and functions - learn how to write them, use them to perform calculations and understand the different cell references.

Practice Video: Formulas3:32

Practice Video: Formulas in Context4:18

Practice Video: Functions I: SUM and AUTOSUM4:19

Practice Video: Functions II: AVERAGE, MIN and MAX4:21

Discussion: Absolute Cell References4:09

Practice Video: Absolute Cell References3:51

Practice Video: Calculations across sheets4:51

Week 2 Wrap-up1:29

Meet the Instructors

Dr Yvonne Breyer
Director, Learning and Teaching
Faculty of Business and Economics

Shawn is about to face his biggest challenge yet.

He has been given the shipping data which is a large set of data

downloaded from the company database containing more than a thousand records.

His boss has asked him to complete all the calculations.

Let's see if we can help him do this.

The first thing we need to do is see the rest of the data.

So we're going to come down to the bottom of the spreadsheet,

click on our horizontal scroll bar and scroll to the right.

And where we see the blank columns,

that is where we have to complete the calculations.

The first calculation Shawn has to do is profit margin.

Profit margin is calculated by subtracting

cost price from retail price to work out our profit.

So this is going to be a simple subtraction.

Click in Q4 and start with your equals.

Click onto retail price,

type minus, click cost price and press enter.

Nice and easy. Let's do it one more time, so equals,

click retail price, minus cost price and enter.

Now we still have more than a thousand records to go,

so we're going to be here awhile.

Or are we? Remember the fill handle that we learned about in week one?

Well, the great news is we can use that for calculations as well.

So, click back on to Q5,

which is the last calculation you did,

come to the fill handle,

that little green box at the bottom right-hand corner, and double-click.

How easy was that?

All right, next calculation is our subtotal.

The subtotal is how much the customer is going to pay before discount and shipping.

So we're going to need to take the retail price and multiply it by the order quantity.

Again, start with your equals,

click retail price and now we need to multiply,

so remember that's our asterisk,

click on Order Quantity and press enter.

And once again, we can copy that down.

So back on to S4 and double-click your fill handle.

Now we need to work on our discount.

Discount is worked out as a percentage of the subtotal.

Percentages can make people a little nervous though.

The good news is in Excel,

they are rarely easy.

All you need to do is take the percentage amount and

multiply it by the value you want to get a percentage of.

Remember that six percent is not the same as six.

It's the same as six divided by 100 or if you prefer, 0.06.

So let's see how this works.

We're going to start with our equals, click our subtotal,

click our asterisks to multiply,

click on our six percent and we're expecting an answer of roughly five dollars.

Click enter and there we go.

Let's copy that down.

So back onto U4 and double-click your fill handle.

Finally, we need to calculate our order total.

This means taking our subtotal,

subtracting the discount, and adding in the shipping.

So this is a slightly more complex formula, but only slightly.

Start with your equals,

click on new subtotal,

type your minus, click on your discount amount,

type your plus and click on your shipping cost.

Now click enter.

There's our Order Total and again we can copy that down.

So, in under five minutes,

we have easily helped Shawn complete his shipping spreadsheet.

Why don't you give it a try on your own?

Explore our Catalog

Join for free and get personalized recommendations, updates and offers.

Coursera provides universal access to the world’s best education, partnering with top universities and organizations to offer courses online.

© 2018 Coursera Inc. All rights reserved.

Download on the App Store

Get it on Google Play