Week 5 Discussion

Loading...

En provenance du cours de Macquarie University

Excel Skills for Business: Intermediate I

644 notes

Macquarie University

Excel Skills for Business: Intermediate I

644 notes

Cours 2 sur 4 dans Specialization Excel Skills for Business

Spreadsheet software remains 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. In the United States alone, millions of job advertisements requiring Excel skills are posted every day. Research by Burning Glass Technologies and Capital One shows that digitals skills lead to higher income and better employment opportunities at a time when digital skills job are growing much faster than non-digital jobs. In this second course of our Excel specialization Excel Skills for Business you will build on the strong foundations of the Essentials course. Intermediate Skills I will expand your Excel knowledge to new horizons. You are going to discover a whole range of skills and techniques that will become a standard component of your everyday use of Excel. In this course, you will build a solid layer of more advanced skills so you can manage large datasets and create meaningful reports. These key techniques and tools will allow you to add a sophisticated layer of automation and efficiency to your everyday tasks in Excel. Once again, we have brought together a great teaching team that will be with you every step of the way. Prashan and Nicky will guide you through each week (and I am even going to make a guest appearance in Week 5 to help you learn about my favourite tool in Excel - shh, no spoilers!). Work through each new challenge step-by-step and in no time you will surprise yourself by how far you have come. This time around, we are going to follow Uma's trials and tribulations as she is trying to find her feet in a new position in the fictitious company PushPin. For those of you who have done the Essentials course, you will already be familiar with the company. Working through her challenges which are all too common ones that we encounter everyday, will help you to more easily relate to the skills and techniques learned in each week and apply them to familiar and new contexts.

À partir de la leçon

Tables

Tables, tables, tables. Start with creating, formatting and managing tables and then move on to sorting and filtering tables to get the data you need. Finally, wrap up this module automating your tables to make your work more efficient.

Week 5 Introduction2:13

Week 5 Discussion4:24

Rencontrer les enseignants

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

Welcome to week 5.

Welcome Nicky.

>> Hi Prashan.

>> This week your going to be working our learners through tables.

>> Actually I'm not.

Yvonne really loves tables, so

we've kindly agreed to let her do the practice videos this week.

>> Okay. So tables.

We've been looking at Excel spreadsheets this whole time.

They look like tables to most learners.

What's the difference?

>> They do but

actually when we talk about a spreadsheet we refer to it as a range, not a table.

>> Okay. >> Tables are quite a new feature.

They came in in 2007.

And. The purpose of the table is to contain database-style data.

And that's because businesses have started to increasingly downloading

vast quantities of data out of their corporate database.

And we need Excel to recognize this data as not just being

disparate pieces of information but actually records.

A table is designed to hold database-style data.

>> And so just like a database,

a column represents a field that contains a particular type of information.

A record is information about one customer, one transaction, or a thing.

And the entire thing is one self-contained unit.

>> Exactly right.

>> So once you've created a table, what else are we going to do with it?

>> We're going to have a look at some of the formatting tools available.

And there's a fantastic array of options.

We're going to also have a look at working with data in the table

including removing duplicates, sorting and filtering.

All sounds amazing, what are the other benefits of a table then?

>> Well, they do give us some really efficient ways of doing formatting,

much quicker than you'd be able to do with a regular range.

The data is much easier to work with.

And you'll see that more in the practice videos.

But the main thing is tables are really powerful automation tools.

When you add data to the bottom of a table, the table automatically extends.

In other words, all your formulas,

your formatting, your conditional formatting, all copy down.

And it doesn't matter how you add the data.

You can type in one row or import.

So really, really powerful automation tool.

>> The automation extension that you mentioned happens in

the latest versions of Excel for regular ranges.

>> Yes, it does.

But only if you type the data in.

So if you copy paste or import, it won't work.

Not everybody has the latest version.

But actually, the main thing is that the named ranges

do not auto extend in a regular range, whereas in a table they do.

Now if you think about it, we've set it up so

that all of our formulas are using named ranges.

Now if they're sitting in the table,

the named ranges grow, the formulas automatically pick up the new values.

So that's where automation comes in.

>> All this automation gets really exciting, so

should we convert all our spreadsheets into tables then?

>> A lot of them, yes, but not all of them.

You're really looking for your database style data.

So where you have different calculations or different values going on or

summary reports, they will not lend themselves well to being tables.

There are also a few features that don't support tables.

One of them is shared workbooks which is kind of an old feature.

I'm not recommending you use it.

But another one is the subtotal feature.

>> Subtotal, that's something new that we haven't covered before.

>> No, we haven't.

And that's actually the last thing we're going to be looking at this week.

So we're going to look at how we can convert our table back to a regular range.

And then we're going to use a subtotal to basically summarize our data on

change of department, for example.

So you can easily get these really useful little subtotal summaries.

>> Thank you, Nicky.

So this week, in the practice videos, we have Yvonne working

through how to create a table, use a table as well as the subtotal feature in Excel.

Make sure you download the Excel workbooks so

that you can practice alongside Yvonne step by step.

Then take a break, come back and practice this again.

Because practice makes permanent.

Also make sure that you check out this weeks practice challenge, the toolbox,

as well as this week's ninja tip.

Now, it's over to you.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play