Excel 2010 Superhero Course
4.6 (12 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.
154 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel 2010 Superhero Course to your Wishlist.

Add to Wishlist

Excel 2010 Superhero Course

Awesome skills and techniques to make you an Excel Superhero
4.6 (12 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.
154 students enrolled
Created by Alan Murray
Last updated 11/2013
Current price: $10 Original price: $70 Discount: 86% off
5 hours left at this price!
30-Day Money-Back Guarantee
  • 8 hours on-demand video
  • 22 Articles
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Learn some incredible Excel skills and techniques
  • Learn the most popular Excel formulas in business (over 30 covered in the course)
  • Be prepared to handle any Excel problem
View Curriculum
  • A basic understanding of Excel. This course begins from around mid-introduction level.

Become an Excel superhero. Learn the skills to be able to tackle any Excel problem.

This course starts with the basics of formulas and managing spreadsheets and quickly moves to advanced Excel techniques required for creating powerful dynamic and visually appealing spreadsheets.

It covers;

  • Using PivotTables to summarise large amounts of data.
  • Over 30 different Excel functions to handle any problem including INDEX, AGGREGATE, VLOOKUP, LEN, MID, SUMIF, MATCH, and INDIRECT.
  • Visualising results with charts.
  • Cleaning and preparing data for analysis.
  • Conditional Formatting to highlight data comparisons, progress and problems.
  • Solving formula errors.
  • And much much more.

You will receive;

  • Support from the Computergaga team
  • Lifetime access
  • Exercises throughout to re-enforce the content
  • Fun and no-nonsense lessons

Don't delay. Superhero's take action. Grab a coffee and let's get started. Your journey towards becoming an Excel superhero starts today.

Who is the target audience?
  • Those who want to be amazing at Excel and are willing to accept the responsibility of being an Excel superhero.
Compare to Other Excel Courses
Curriculum For This Course
97 Lectures
1 Lecture 00:45
Working with Windows
3 Lectures 16:11

In this lecture we look at the Freeze Panes feature to fix parts of your worksheet to ensure they are visible at all times. This is typically used for column headings so that you can see what data relates to as you scroll down the worksheet. This tutorial covers;

  • Freezing the column headings in a worksheet
  • Freezing rows and columns in a worksheet at the same time

Applying Freeze Panes is essential for large worksheets to make them easier to navigate and work with.

Preview 05:14

Splitting the panes of a worksheet allows you to reference different areas of the worksheet at the same time. The lecture covers;

  • Splitting panes to make comparing records in a large worksheet easier
  • Using a shortcut to split panes in one movement of the mouse
  • Using split panes as a predecessor to freezing panes for a more visual approach
Preview 05:48

If you need to work on multiple Excel files at the same time, you may benefit from making them all visible on screen at once. Excel provides features to easily do this and save it for long term use. This tutorial covers;

  • Arranging multiple windows on screen at the same time
  • Understanding the 4 different ways of viewing multiple workbooks on screen
  • How to return to the single window display
  • Saving the window arrangement as a workspace to use long term
Working with Multiple Windows
Sorting and Filtering Records
3 Lectures 24:07

Lists in Excel can be very large. In fact Excel worksheets can contain as many as 1,048,576 rows of data. Managing a list of this size is very important.

Sorting enables you to easily change the order of a list. A list could be sorted in date order, or alphabetical ,or even by the order amount as largest to smallest.

This tutorial covers;

  • The fastest way to sort a list in Excel
  • Sorting lists by different data types such as date, number or text
  • A common problem when sorting records
  • Sorting a list of records by multiple levels
Sorting Records

The primary way of managing and analysing a large list of data in Excel, is to filter the list. Filtering records in Excel is easy, yet incredibly powerful and versatile.

A list can be filtered to only show the necessary records ready for printing, exporting or to view aggregate totals.

This tutorial covers;

  • Filtering a list of records by a text column
  • Filtering a list of records by multiple options in a text column
  • Filtering a list by a date column
  • Filtering a list of records by a numerical column
  • How to instantly recognise a filtered list when opening a spreadsheet
  • Quickly removing all the filters from a table and resetting it to normal
  • Viewing totals for a filtered list such as the sum, count or average for a column
Filtering Records

In addition to sorting and filtering records in a list by value. You can also sort and filter by the colour of the cell or font.

This technique works awesomely well with Conditional Formatting (covered later in this course) as a way of identifying and then working with records that meet certain criteria.

Sorting and Filtering Records by Cell or Font Colour

Open the Sorting and Filtering.xlsx file from the previous tutorials and sort and filter the records to answer the following questions.

Sorting and Filtering
4 questions
Getting Started with Formulas
10 Lectures 54:26

Get started with learning to write formulas in Excel. Formulas are the foundation of Excel's incredible power. To become an Excel superhero, you must be confident in being able to learn and work with any formula.

This tutorial covers;

  • Writing formulas using basic arithmetic (addition, subtraction, multiplication and division)
  • Referencing cells and using constants in a formula
  • The rule of BODMAS that determines the order of calculation
Writing Formulas

When writing formulas it is common to need to calculate percentages. For example you may need to calculate a discount for a client, add VAT to a product price or calculate a 20% tax savings. This tutorial covers;

  • Calculate the % of a value
  • Increase a value by a certain percentage
  • Decrease a value by a certain percentage
  • Calculate what % one value is of another
Calculating Percentages

Excel has over 430 functions in its library. A function is a built-in formula. Knowing how to write your formula is a very important step on the way to becoming an Excel superhero, but most of the time you will be working with the built-in functions of Excel.

This tutorial covers;

  • The 5 aggregate functions of Excel (SUM, COUNT, AVERAGE, MAX and MIN)
  • Understanding function syntax making you ready for any function
Introduction to Functions

When referencing a cell from a formula you are using relative cell references. This ensures that when you copy the formula, the reference moves with you. Dollar signs are used to fix a cell reference, making it an absolute reference. This tutorial covers;

  • An example where absolute cell references are used
  • A shortcut to changing the cell reference type
  • An introduction to mixed references
Absolute Cell References

Creating a defined name can provide an easier way of referencing cells in an Excel workbook, especially when referencing across multiple sheets. It provides a meaningful name that is unique on that workbook.

It can be recalled quickly when writing formulas and also used to select ranges quickly, making it awesome with larger lists.

This tutorial covers;

  • The benefits of using defined names
  • How to create a defined name
  • Naming conventions and a common mistake when naming ranges
  • Using a defined name to quickly select a range
  • How to view, edit and delete existing defined names
  • Using a defined name when writing a formula
Defined Names

The COUNTA and COUNTBLANK functions are two variations of the COUNT function covered earlier in this course. The COUNT function only counts numbers within a range. COUNTA is used to count all the non-empty cells with a range, and the COUNTBLANK is used to count the empty ones.

This tutorial covers;

  • Ways of finding a function when needed in a given situation
  • An example of the COUNTA function being used
  • An example of the COUNTBLANK function being used

The Excel function wizard can be used to assist you in writing a formula. It provides explanations for the parts of the function, results for the information your providing and will also make minor corrections to mistakes.

This tutorial covers;

  • The COUNTIF function
  • Using the function wizard to help find the correct function to use in a given situation
  • Using the function wizard to troubleshoot and correct a mistake in our formula
The Excel Function Wizard

Exercise 1: Absolute and Mixed References

Exercise 2: Writing Formulas

Exercise 3: Using Functions
Using Logical Functions
5 Lectures 30:01

The IF function is used to test a condition and then take an alternative action for whether the condition is met or not. For example, if a customer orders over a £500 then apply a 10% discount, otherwise do not.

It is a logical function of Excel, and is sometimes referred to as a conditional function or a decision making function.

This tutorial covers;

  • How to write the IF function in Excel
  • Understanding the functions syntax
  • An example applying a discount to a customer if they order over a certain quantity
  • An example showing testing a cell contain text before applying a discount
  • An example that tests to see if a date has passed, and then takes a different action whether it has or not
The IF Function

The AND and OR logical functions of Excel are used to test multiple conditions. They are commonly used with various Excel features including the IF function to perform more advanced decision making.

Any Excel function can be nested within another for more powerful calculations.

This tutorial covers;

  • An explanation of the AND and OR logic and the syntax of the two functions
  • Examples of the AND and OR functions in use
  • An introduction to the art of nesting functions in Excel
Testing Multiple Conditions

As awesome as the The IF function is, it can only test one condition, and perform one action if the test is true and another if the test is false. If we need the function to perform a more advanced condition, or perform multiple actions we will need to embed other functions for these tasks. This is a technique known as nesting functions.

This tutorial covers;

  • Nesting the AVERAGE function within the IF function
  • Viewing and changing formula error checking options
  • Adding the AND function to the formula to test multiple conditions
  • Adding the OR function to the formula for more complex conditional testing
  • How you can test parts of a formula in isolation. Brilliant technique for troubleshooting formula errors and for understanding how a formula works
Nesting Functions to Test Multiple Conditions

Exercise 1: Using the IF Function

Exercise 2: Nesting Functions
Using Lookup Functions
8 Lectures 01:00:07

The VLOOKUP function is a lookup and reference function. It searches for a record of data in a list and returns information from that record. For example, it could search a list of orders and return the value of an order that you ask for.

VLOOKUP stands for a vertical lookup. It gets this name because it looks down columns.

This tutorial covers;

  • An explanation of the VLOOKUP function and when you would use it
  • Learning the syntax of the VLOOKUP function
  • Using the VLOOKUP function to return the price and units in stock for a particular product in a list
  • A recap on the use of an absolute cell reference
  • A demonstration of the 'Range Lookup' argument
The VLOOKUP Function

The VLOOKUP function is commonly used to find unique entries such as an order, employee or service and return information about that entry.

However, it can also be used to find data within a range. To do this the final argument of the VLOOKUP function known as 'Range Lookup' needs to be omitted or set to True.

This tutorial covers;

  • A detailed explanation of the 'Range Lookup' argument
  • An example of the VLOOKUP function returning exam grades by setting range lookup to True
  • Using the Function Wizard for assistance in understanding a function
VLOOKUP for the Closest Match

The HLOOKUP function is used to search for a record of data along a row and return information about that record from a different row. For example, it could search a list of employees and return the start date of the one you search for.

This tutorial covers;

  • An explanation of the HLOOKUP function and when you would use it
  • Learning the syntax of the HLOOKUP function
  • Using the HLOOKUP function to return the price and units in stock for a particular product in a list
The HLOOKUP Function

The MATCH function can be used to return the column index number for the VLOOKUP function. By nesting the MATCH function within the column index number of VLOOKUP, the function can automatically find the necessary column number, making the function more flexible and durable.

This tutorial covers;

  • An understanding of the MATCH function
  • The benefits of using it with VLOOKUP
  • How to use MATCH to return the column index number
VLOOKUP with Nested MATCH Function

The LOOKUP function is more versatile than VLOOKUP. It looks in and returns from ranges you select. It can be used to return data from a column to the left of the column we are searching in.

This tutorial covers;

  • The differences between LOOKUP and VLOOKUP
  • Understanding the LOOKUP syntax
  • Using the LOOKUP function to return data from a list
The LOOKUP Function

The INDEX and MATCH functions together provide an extremely flexible lookup formula. Look for a record or entry in any column of a table and return related information from a different column of that table. This formula solves some of the limitations of VLOOKUP.

This tutorial covers;

  • The purpose of the INDEX and MATCH functions individually
  • Combining the two functions together for a flexible lookup formula
  • Returning information about an order from a list of orders on a different worksheet
  • Using MATCH to find the record and find the column containing the data to return to create an extremely flexible formula that users can interact with
The INDEX and MATCH Functions

The #N/A error message is returned when functions such as VLOOKUP and MATCH cannot find what you asked them too. This message can be hidden if required, or an alternative action taken such as looking on a different sheet for it, or displaying different text such as “Not Found”.

This tutorial covers;

  • Using the ISNA function to detect the presence of a #N/A error
  • Combining this with the IF function to take the appropriate action
  • Displaying text in the cell instead of the error
  • Making the lookup cell empty is nobody has asked to look for anything
Handling the #N/A Error Message

Exercise 1: Lookup Functions
Protecting and Sharing Excel Files
4 Lectures 21:48

If a workbook is saved to a shared drive you may want to prevent unauthorised access to the file. Encrypt the file with a password to ensure that only authorised personnel may open the file.

Encrypt an Excel File

A workbook can be protected to lock the structure and/or windows of a workbook.

Protecting the structure locks sheet related activity such as users unhiding, renaming, deleting or inserting sheets. Protecting the windows prevents users from using features such as Freeze Panes and Arrange All.

This tutorial covers;

  • Hiding a worksheet
  • Applying workbook protection to prevent users from unhiding the worksheet
Workbook Protection

Worksheet protection can be used to protect different elements of a spreadsheet. It can be used to protect your formulas and prevent users from unhiding columns among other things.

This tutorial covers;

  • Unlocking the cells of a spreadsheet you do not want to protect
  • Setting the cells containing formulas to hidden
  • Protecting the worksheet with a password
  • A demonstration of the improved usability of a protected worksheet
Worksheet Protection

Excel spreadsheets have exclusive access by default. This means that only one person at a time can modify them. Sharing a workbook will allow multiple people to edit a spreadsheet at the same time.

This tutorial covers;

  • Sharing a workbook so more than one user can open it at the same time
  • Viewing what users have that spreadsheet open
  • Modifying the settings for workbook sharing
  • Setting a workbook back to exclusive access
Sharing a Workbook
Using Conditional Formatting
7 Lectures 32:29

Apply Conditional Formatting to your spreadsheets to format cells based on a condition. For example, you could format cells red if a deadline is missed, a task is due to start, or a target is achieved.

This tutorial covers;

  • The different types of rules you can create using Conditional Formatting
  • Formatting the cells that contain a date in the past
  • Adding a second conditional format to format the cells if the date contains is within the next 2 weeks
Applying Conditional Formatting

Data bars and icon sets are very visual indicators that can be applied to bring our attention to values that meet certain criteria.

This tutorial covers;

  • Applying data bars to a range to display a nice visual comparison of the values
  • Using colour scales on a range of cells to visualise the different between the values in that range
  • Creating a conditional format rule from scratch
  • Apply an icon set to display a green tick on all cells where the value is 65% or higher
Using Data Bars and Icon Sets

If you are going to be using conditional formatting on a spreadsheet you will need to be able to manage the rules. Excel makes it easy to be able to move, edit or delete conditional formatting rules that exist on a spreadsheet.

This tutorial covers;

  • Viewing all the conditional formatting rules on a worksheet
  • Editing an existing rule
  • The importance of the order that conditional formatting rules run
  • Clearing rules that are no longer required from the selected cells, or the entire sheet
  • Using the Format Painter to copy conditional formatting rules between cells
Managing your Conditional Formatting Rules

Applying conditional formatting to individual cells is fantastic, but if your data was quite wide, formatting the entire row will make it more visible.

To format the entire row we will need to create the rule a little bit different to the way we have been doing.

This tutorial covers;

  • The benefits of formatting the entire row
  • Using a formula for a conditional formatting rule
  • Using a mixed reference in the formula
Apply Conditional Formatting to the Entire Row

By using a formula for your conditional formatting rules, you can create some extremely powerful formatting rules. By writing formulas you can create rules that Excel has not provided for you already.

This tutorial covers;

  • Creating a conditional formatting rule using a formula
  • Using the AND function
  • Formatting the entire row
Use a Formula for your Rule

Exercise 1: Conditional Formatting

Exercise 2: Highlight Unmatched Items
Validating Data Entry
7 Lectures 22:12

If certain text entries need to be a specific number of characters, you can apply validation to a prevent entries that are not that particular text length.

This tutorial looks at validating data entry to be exactly 5 characters in length.

Validate the Text Length of a Data Entry

You can validate date entries in Excel to ensure that they are within a certain range or only a date that occurs this week etc.

This tutorial covers;

  • Setting a data validation rule to prevent the entry of future dates
  • Using the TODAY function for validating date entries
Validate Date Entries

Drop down lists provide an easy and user friendly way for people to enter text data. This ensures that the data is valid. Entries that do not appear in the list will not be allowed.

This tutorial covers;

  • Creating a data validation list by entering the values yourself
  • Creating a data validation list by referring to a range of cells on another sheet
  • Using the drop down list to enter data
Validate using a Drop Down List

Messages can be created to assist the user with their data entry. They can be used to warn the user of problems and explain common data entry issues.

This tutorial covers;

  • Setting up input messages to inform the user of how the data should be entered
  • Setting up error alerts to prevent incorrect data entry and explain where they may have gone wrong
  • Change the style of the error alert to warn the user, but not prevent them from entering the data
Create your own Input and Error Messages

You can create a drop down list that is dependent upon the selection made in another list. By doing this a large list can be shortened by only displaying the relevant options.

This tutorial covers;

  • Creating a drop down list of products which is dependent on what product category was chosen
  • Using the INDIRECT function
  • Using defined names in a workbook
Create Dependent Drop Down Lists

Exercise 1: Validating Entry of Dates

Exercise 2: Validating Entry of Values
Analysing Data
6 Lectures 23:04

The subtotals feature of Excel enables you to insert subtotal rows at particular points within a list. A selection of different aggregate functions can be used for the subtotal rows including sum, count and average.

This tutorial covers;

  • Applying subtotals to a list to sum values by each salesperson in the list
  • Using the outline buttons to view the totals at different levels of the outline
  • Applying multiple subtotals

The SUMIF function will enable you to sum all the values in a list dependent on a condition. For example, sum all the orders for a specific product, or for a particular month.

This is possibly the most popular analysis function of Excel. For counting and averaging, the COUNTIF and AVERAGEIF functions can be used respectively.

This tutorial covers;

  • Writing a SUMIF function to sum all the order amounts where the order was by a certain salesperson
  • Using a drop down list to make the formula interactive
The SUMIF Function

The SUMIFS and COUNTIFS functions can be used to sum and count values based on multiple conditions.

This tutorial covers;

  • Understanding the SUMIFS and COUNTIFS functions syntax
  • Using them to sum or count the order amounts for orders by a particular salesperson, and for a particular product category
The SUMIFS and COUNTIFS Functions

The SUBTOTAL function can be used to perform a function on a filtered list. The SUBTOTAL function will ignore the hidden rows on filtered lists. Functions that SUBTOTAL can perform include sum, count, average and more.

This tutorial covers;

  • Using the SUMIFS and COUNTIFS functions to find the total and count of orders in a list
  • Filtering the list of orders to see the functions working
The SUBTOTAL Function

Exercise 1: Applying Subtotals

Exercise 2: Using SUMIFS
7 More Sections
About the Instructor
Alan Murray
4.4 Average rating
498 Reviews
4,418 Students
5 Courses
Founder of Computergaga

Alan Murray is a Microsoft Office trainer and consultant. He has been training and consulting for the past 15 years for businesses around the world.

He is the founder of Computergaga and regularly writes on the Computergaga blog to share tips, tutorials and templates.

Alan uses a fun and relaxed style of training that gets to the point, and uses real world practical examples uncovered from his experience of training and developing software for businesses.

Files are provided to follow along and exercises used to recap on topics covered. Alan is always eager to help and will be there for you when needed. He will do his best to answer any question or query related to his courses within 48 hours.