Microsoft Excel - 11hrs - Beginner to Specialist certificate
4.7 (45 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
251 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Microsoft Excel - 11hrs - Beginner to Specialist certificate to your Wishlist.

Add to Wishlist

Microsoft Excel - 11hrs - Beginner to Specialist certificate

Excel exams 77-602 (2007), 77-882 (2010), 77-420 (2013), 77-727 (2016) certification. From basics to graphs + formulas.
4.7 (45 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
251 students enrolled
Created by Phillip Burton
Last updated 3/2017
English
Current price: $10 Original price: $50 Discount: 80% off
30-Day Money-Back Guarantee
Includes:
  • 11.5 hours on-demand video
  • 13 Articles
  • 29 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Create, configure and save worksheets and workbooks.
  • Navigate through and format worksheets and workbooks.
  • Customise options and views for worksheets and workbooks.
  • Configure worksheets and workbooks to print (e.g. page setup).
  • Insert data into and format cells and ranges, and create and modify tables.
  • Highlight, filter, sort and group cells, ranges and tables.
  • Create and format charts.
  • Insert and format objects, including modifying images.
  • Create formulas, including using conditional logic and modifying text.
View Curriculum
Requirements
  • Before beginning, you will need to know how to use a Windows computer, including a mouse.
  • It would be good if you have used Microsoft Excel before, but this is not essential.
  • You should have MS Excel (or Office) 2007, 2010, 2013, 2016 or 365 installed on your computer.
Description

Reviews:

"The teacher explains very well and at a good pace. Has real in-depth knowledge. The exercises are precise and well put together for the course. And the language sheets are invaluable to those that use other language than English version. I highly recommend this course. It goes thru most of the basic knowledge needed for Excel. Once again The teacher is excellent" -- Daniel Sandberg

"This is super helpful to me. I'm learning things I didn't know existed in Excel." - Jimmy Thrower

"I had to take an Excel proficiency test as a job interview requirement , and scored top 10% percentile (they called me immediately to schedule a face-to-face) , thanks to your great course for which I studied for two days straight to re-enforce my knowledge in Excel, and more importantly fill those gaps of knowledge on all its functionality capabilities . You really get the MOST out of Phillip's courses." - Rick Romero

--------------------

Most people who use Excel are up to Level 3 in some aspects, and Level 2 in others. Why not go all the way to Level 5?

In this 11.5 hour course, learn how to:

Create and manage worksheets and workbooks

  • Create and navigate in worksheets and workbooks,
  • Format worksheets and workbooks,
  • Customize options and views, and
  • Configure worksheets and workbooks for distribution.

Manage data cells and ranges

  • Insert data into and format cells and ranges, and
  • Summarise and organize data.

Create tables

  • Create and manage tables,
  • Manage table styles and options, and
  • Filter and sort a table.

Perform operations with formulas and functions

  • Summarize data,
  • Perform conditional operations, and
  • Format and modify text.
  • Formula such as COUNTIF, SUMIF, MIN etc. are translated into 15 different languages.

Create charts and objects

  • Create charts,
  • Format graphic elements, and
  • Insert and format objects.

This course teaches all the skills that Microsoft want you to know. Specifically, they are the skills required to undertake the Microsoft Certificate 77-602 (for Excel 2007), 77-882 (for Excel 2010), 77-420 (for Excel 2013) and 77-727 (for Excel 2016 Core) and will be useful if you wish to take the exam, or which to learn more about Microsoft Excel

The core skills which are taught are those tested by Microsoft in the exam. There are topics that it wants you to learn about Microsoft Excel, and this course teaches you all of them.

Each module is taught in order, and is divided in sub-topics, and generally each sub-topic will have an individual lecture lasting 5-7 minutes.

The course will take about 11 hours to complete, plus will you need additional time to test yourselves to ensure that you have learned the necessary skills.

You should take this course if:

  • you want to learn more about Microsoft Excel, or
  • you want to learn the skills you need to become a certified Microsoft Office Specialist.

Regardless whether you have Excel 2007, Excel 2010, Excel 2013, Excel 2016 or Excel 365, this course will help you get to a good level, and maybe even want you to get more!

Who is the target audience?
  • This course is for you if you want to develop your Microsoft Excel skills.
  • This course will use the latest version of Excel, but is ideal for you if you use any modern version - Excel 2007, 2010, 2013, 2016 or 365.
  • This course is also for you if you use an older version of Microsoft Excel, but want to learn the newer versions.
  • This course is for you if you want to become certified in Microsoft Excel, as by the end you should the knowledge to because certified as a Microsoft specialist, if you wish to do so.
  • You will cover the content tested in Microsoft Office Specialist Exams 77-602 (for Excel 2007), 77-882 (for Excel 2010), 77-420 (for Excel 2013) and 77-727 (for Excel 2016 Core)
  • This course may not be for you if you want to learn Microsoft Excel for Macintosh computers.
Curriculum For This Course
152 Lectures
11:38:30
+
Welcome
2 Lectures 12:04

Welcome. I'll introduce myself and the course.

Preview 01:57

Why I have selected the topics that we are going to look at in this course? What is Microsoft certification? And where should I start? Let's have a look.

Curriculum
10:07
+
Level 1, Section 1 - An introduction to Excel
6 Lectures 25:18

Let's start at the very beginning, and create a brand new workbook. But what is the difference between a workbook and a spreadsheet? Let's find out.

Preview 04:09

What sort of data can you enter? What is the difference between the Tab key and the right-hand arrow key. What characters should you beware about starting with? Let's explore.

Entering data
09:02

Very similar to their Word counterparts, you can change the fonts and add highlighting. Unlike Word, the highlighting is to the entire cell, and not just the contents. Let's find out how that works.

An introduction to fonts and highlighting
04:23

Now that you´ve entered your data, you need to save it so that you can come back to it later. Let's save it, and then close your workbook.

Saving and closing your workbook
04:59

Practice Activity Number 1
00:18

Practice Activity Number 1 - The Solution
02:27
+
Level 1, Section 2 - Create worksheets and workbooks
7 Lectures 41:55

You don't need to start each workbook from scratch. Online, there is a collection of designs which can help you create standard items, such as invoices, lists and other such documents. Let's see how we can use these templates to kick-start your work.

Creating new workbooks using templates
06:07

Let's have a quick look at the menus, and see that there are dialog boxes hidden away, buttons which are split in two, and entire menus which only appear when needed.

Menus and toolbars
07:16

At some point, you will have many workbooks open. Managing them - showing multiple workbooks at once, or switching between them - is different depending on your version of Excel. Let's see how to manage multiple workbooks in Excel 2013 and 2016.

Managing multiple workbooks - Excel 2013 and 2016
04:27

At some point, you will have many workbooks open. Managing them - showing multiple workbooks at once, or switching between them - is different depending on your version of Excel. Let's see how to manage multiple workbooks in Excel 2007 and 2010.

Managing multiple workbooks - Excel 2007 and 2010
07:05

Let's find out how to re-open spreadsheets. After that, the most common file type to import into Excel is a text file, but there are two kinds. Let's find out how to open them as well.

Importing files and Opening non-native files directly in Excel
07:59

We'll also find out how to delete spreadsheets, and to navigate through them - some of the options are more hidden in Excel 2013/2016 than in Excel 2007/2010, but the tool tips can give us a clue. Let's go through the options.

Adding worksheets to existing workbooks
03:38

Let's find out how to copy and move a single spreadsheets, then how to group them together, so that we can copy and/or move multiple spreadsheets at once.

Copying and moving worksheets, and changing spreadsheet order
05:23
+
Level 1, Section 3: Configure worksheets and workbooks to save
3 Lectures 15:52

You don' t just have to save your spreadsheet in Excel format. Let's see how you can see them in text format and to PDF.

Saving workbooks in alternate file formats
06:56

What happens if you need to send a file to someone using Excel 2003 or earlier? What about any new features that were added in Excel 2007 or later? Let's see what compatibility mode is all about.

Maintaining backward compatibility
05:41

What if you wanted to save to OneDrive or SharePoint or DropBox? Well, Excel, and particularly later versions, have you covered.

Saving files to remote locations
03:15
+
Level 1, Section 4: Basic formulas
7 Lectures 32:52

Let's start using formulas, and we'll start with multiplication, division, addition, subtraction, and power (Exponents).

Using basic operators
06:02

Which comes first - multiplication or addition? Please excuse my Dear Aunt Sally. I'll explain what I mean in this video.

Order of operations
05:51

Let's have a quick look at ways to enter numbers and dates.

How to enter numbers and dates
03:25

It's all very well, using Excel as a calculator, but one of the main reasons to use Excel is because calculations can refer to other cells, making these calculations dynamic. Let's find out how to refer to other cells as part of our calculations.

Referencing cell ranges in formulas
10:29

Here are the answers to the previous exercise.

Solution to the above exercise
00:28

Practice Activity Number 2
00:45

Practice Activity Number 2 - Solution
05:52
+
Level 2, Section 1: Navigate through worksheets and workbooks
4 Lectures 19:27

Workbooks can be pretty large, and it can be difficult to remember where all of your data is. Let's find out how to search for specific information.

Searching for data within a workbook
05:26

Part of the great things about the Internet is that you can click on links, and go to other websites. Let's find out how to add links so that you can open website, other documents, and go to other parts of your spreadsheet, like a Table of Contents.

Hyperlinks
04:43

You know where you want to go, but how do you get there? You could scroll your way there, but why not use a dialog box to immediately get there. Let's find out how to use the Go To dialog box, and the Name Box.

Using Go To and Using Name Box
05:23

We've been looking at shortcut keys in previous videos, but how do you find a comprehensive list of all shortcut keys - and do you really need to memorise all of them? Let's explore.

Hot Keys
03:55
+
Level 2, Section 2: Format worksheets and workbooks
8 Lectures 34:10

You don't have to stick with that boring color for your spreadsheet tabs. Let's find out how to change them, and I'll give you a few ideas why this might be useful.

Changing worksheet tab colour
05:25

You've entered some data - but now you find that you need to add extra columns and rows in the middle. Or maybe there are too many, and you want to remove one or two. There are lots of different ways of doing this - let's find out which work for you.

Inserting and deleting columns and rows
07:44

With the ability to insert and DELETE data, you need to know how to correct any mistakes (Especially in deleting!) Let's find out how to undo your latest action, and several actions before that, and then how to undo the undo (redo!), and when redo will not work.

Undo and Redo
04:09

But what if you want to undo an action from several hours ago? Here's some tips on File Management that can help when you are developing your spreadsheets.

File Management tips
04:24

It won't always be the case that the default row height and column width work for your data. Maybe you can't see all of the data because it's too small - or maybe there is wasted space, and it is too big. Let's adjust the row height and column width.

Adjusting row height and column width
04:43

What if you don't actually want to delete data, but just want it more hidden from view. Let's hide rows and columns, and allow the end user to concentrate on the remainder.

Hiding columns and rows
03:39

You don't have to do all these changes one column or row at a time. You can select multiple ranges and do changes to all of them at once. Let's see all the ways that you can select multiple ranges.

Selecting multiple ranges
03:42

Practice Activity Number 3
00:24
+
Level 2, Section 3: Customise options and views for worksheets and workbooks
13 Lectures 51:07

Hiding rows and columns isn't the only thing you can do - you can also hide entire sheets. Let's find out how to do, and also work out how you can discover if a workbook you have received has hidden sheets.

Hiding worksheets
03:31

There will be some commands that you will use more often than others. Let's customise your environment by adding buttons to a place which is always visible, the QAT (Quick Access Toolbar).

Customising the Quick Access toolbar
03:25

In addition to adding buttons to the QAT, you can also add them to the ribbon. You can add additional menus, or alter existing menus - and show the Developer menu. Let's find out how.

Customising the Ribbon
05:08

We've seen all of the standard shortcuts keys - what about commands which don't have shortcut keys? Let's find out how you can add shortcut keys to other commands.

Assigning shortcut keys
02:49

Macros are ways to expand the functionality of Excel. Let's record some simple macros and play them back.

Recording simple macros
04:53

We'll save our existing database, and find out why, for security purposes, we can't save them in the current format. We'll then re-open that workbook, and see what problems we run into. Then we'll have a look at macro security overall, and find out how to add entire directories to the safe list.

Managing macro security
07:55

There are three main views - normal, Page Break Preview, and Page Layout (though in a different order in Excel 2010). Let's see the strengths and weaknesses of each, and how to change between them. 

Changing workbook views
04:13

You aren't restricted to seeing everything at the standard zoom. Let's zoom in and out, note that it doesn't affect the print zoom, and find out how you can't always see all the data at other zooms.

Using zoom
03:00

Do you want to make sure that everyone knows that you are the author of your workbook? What about if you have custom notes to add that you don't want included on the spreadsheet. Let's see how to add information to your workbook properties, and how you can see them in Windows Explorer.

Adding values to workbook properties
05:23

As we have seen, you see formulas in the formula bar, and the results in the spreadsheet. But what if you want to see the formulas in the spreadsheet? Let's find out how we can do this, and how it is good to mass-check your formulas for auditing purposes.

Displaying formulas
02:32

Do you want to build a spreadsheet? If your spreadsheet is big, you may forget what's at the top of the spreadsheet when you are lower down. Let's find out how to keep the top of the spreadsheet visible while going elsewhere.

Freezing panes
04:35

There is another way of seeing two parts of the spreadsheet at once. Let's split the window, create two independent aspects of your spreadsheet, splitting your window into either 2 or 4 parts.

Splitting the window
03:09

Practice Activity Number 4
00:34
+
Level 2, Section 4: Configure worksheets and workbooks to print
11 Lectures 38:54

Now you've got your data ready - let's commit it to paper. Let's print a spreadsheet, and see that you can also print to PDF on some versions of Windows.

Printing individual worksheets
02:55

What about if you have several spreadsheets to print out at the same time? Let's group our spreadsheets, but also find the method to print the entire workbook at once. Then we'll find out how to only print a limited selection ad hoc.

Printing an entire workbook and printing a selection
02:22

You don't necessary want all of the spreadsheet to be printed. Let's find out how to get only part of a spreadsheet to be printable without actually hiding columns or rows.

Preview 04:10

Now that you've decided what is to be printed, let's look at how it is to printed. Let's have a look at the Page Setup dialog box, and we'll start with the page and margins tabs.

Page setup, Part 1 (first two tabs)
05:34

Now we've got the page and margin set up correctly, let's have a look at the top and bottom of each page, with your headers and footers.

Page setup, Part 2 (Repeating headers and footers)
02:40

Headers and footers needn't be static text. Let's add some text that responds to the title of the workbook or spreadsheet, or the date and time it is printed, or the page number.

Adding dynamic text to headers and footers
03:29

Sometimes you want a watermark that says "DRAFT". Let's find out how to add them and other graphics, using the Headers and Footers dialog box.

Inserting watermarks
02:58

The final tab has a lot of miscellaneous printing options. Let's have a look at them, then look at the Page Layout toolbar and see what you can do from there, and what is missing.

Page setup, Part 3 (Headings etc.)
06:59

Now that we can configure individual sheets to print, let's look at configuring entire workbooks to print. There's not much more work involved, but allows you to print the entire workbook at once

Configuring workbooks to print
02:53

There are a few things to look for prior to sending your workbook out. Let's inspect a workbook for hidden properties or personal information, inspect a workbook for accessibility issues, and inspect a workbook for compatibility issues.

Inspecting your workbook prior to distribution
04:27

Practice Activity Number 5
00:27
+
Level 2, Section 5: Utilise cell ranges and references in formulas and functions
3 Lectures 14:35

One of the important aspects of formulas is the ability to know when to use relative cell references, when to use mixed cell references, and when to use absolute. Let's find out the important differences between them, and how to tell Excel which one you are using.

Copying and pasting basics, and Utilising references (relative, mixed, absolute)
08:55

Now it's your turn to put mixed references in action. I'll start off a multiplication table, show you the end, and ask you to finish it off. Give it a go!

Practice Activity - multiplication table
02:33

Let's have a look at the answer to the multiplication table. Did you get it right?

Answer - multiplication table
03:07
14 More Sections
About the Instructor
Phillip Burton
4.4 Average rating
3,262 Reviews
45,999 Students
25 Courses
Best Selling Instructor - over 45,000 students so far

Phillip is a Computing Consultant providing expert services in the development of computer systems and data analysis. He is a Microsoft Certified Technology Specialist. He has also been certified as a Microsoft Certified Solutions Expert for Business Intelligence, Microsoft Office 2010 Master, and as a Microsoft Project 2013 Specialist.

He enjoys investigating data, which allows me to maintain up to date and pro-active systems to help control and monitor day-to-day activities. As part of the above, he also developed and maintained a Correspondence Database in Microsoft Access and SQL Server, for viewing job-related correspondence (110,000 pdfs in one job) by multiple consultants and solicitors.

He has also developed expertise and programmes to catalogue and process and control electronic data, large quantities of paper or electronic data for structured analysis and investigation.

He is one of 9 award winning Experts for Experts Exchange's 11th Annual Expert Awards and was one of Expert Exchange's top 10 experts for the first quarter of year 2015.

His interests are working with data, including Microsoft Excel, Access and SQL Server.